-
·插入记录： INSERT [INTO] tbl_name [(col_name,…)] VALUES(val,…) ·查看记录： SELECT * FROM tbl_name查看全部
-
显示当前服务器版本 SELECT VERSION(); 显示当前日期 SELECT NOW(); 显示当前用户 SELECT USER();查看全部
-
关键字、函数名大写 数据库名称、表名称、字段名称小写 以分号结尾查看全部
-
修改MySQL提示符： ·连接客户端时通过参数指定： shell>mysql -u -p --prompt 提示符 ·连接上客户端后，通过prompt mysql>prompt 提示符 提示符可以包括： \D 完整的日期 \d 当前数据库 \h 服务器名称 \u 当前用户查看全部
-
查找名字重复的记录： SELECT goods_id,goods_name ,COUNT(goods_name)from tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) >=2 删除名字重复的记录（用自身连接）： DELETE t1 from tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name from tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>=2) t2 on t1.goods_name=t2.goods_name WHERE t1.goods_id>t2.goods_id;查看全部
-
查找显示父级id对应的名称 select s.type_id ,s.type_name,p.type_name As parent_id from tdb_goods_types s left join tdb_goods_types p on s.parent_id=p.type_id; 查找子级对应的名称 select p.type_id ,p.type_name,s.type_name from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id; 查找有多少子级 select p.type_id ,p.type_name,COUNT(s.type_name) from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id GROUP BY p.type_name ORDER BY p.type_id;查看全部
-
观察数据表的命名及设置查看全部
-
join \ cross join \ inner join 是等价的 内连接为 交集公共的部分。仅显示符合连接条件的记录 select goods_id,goods_name,cate_name from tdb_goods AS g inner join tdb_goods_cates AS c on g.cate_id=c.cate_id;查看全部
-
连接： table_reference {[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER] JOIN} table_reference on conditional_expr查看全部
-
创建表格并直接插入已有的数据：使用create select create table tdb_goods_brands( brand_id smallint unsigned primary key auto_increment, brand_name varchar(40) not null) select brand_name from tdb_goods group by brand_name; 修改记录，根据已有表批量修改： update tdb_goods AS g inner join tdb_goods_brands AS b on g.brand_name=b.brand_name set g.brand_name=b.brand_id; 修改字段类型： alter table tdb_goods change goods_cate cate_id smallint unsigned not null, change brand_name brand_id smallint unsigned not null;查看全部
-
各种存储引擎的特点查看全部
-
update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;查看全部
-
CREATER TABLE IF NOT EXISTS tdb_goods_cates( cate_id smallint unsigned primary key auto_increment ,cate_name varchar(40) not null); select goods_cate from tdb_goods group by goods_cate; insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;查看全部
-
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price !=ALL (SELECT goods_price from tdb_goods WHERE goods_cate='笔记本'); =any 与 in 等效 !=all或<>all 与 not in 等效查看全部
-
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > all (SELECT goods_price from tdb_goods WHERE goods_cate='笔记本');查看全部
举报
0/150
提交
取消