MySQL学习小记2

2017-01-12 09:57:00来源:oschina作者:finndai人点击

MySQL数据库记录操作


INSERT


1.INSERT tbl_name [col_name] values(..,...,);


使用values(null/default,...,..)方法可以实现序列号的递增,便可以省列名


同时插入多条记录:valuses(),()中间用逗号隔开


2. insert tb1_name set col_name = {expr| DEFAULT}


与第一种方式的区别在于,此方法可以使用子查询


3. insert tbl_name [(col_name,...)] select ....


此方法可以将查询结果插入到指定数据表


insert test(username)SELECT username FROM users WHEREage >=30;

UPDATE


1.单表更新


updatetable_reference set col_name1={} ,col_name2 = {}[where where_condition


update users set age=age-id,sex=0; update users set age = age +10 where id % 2 = 0;

DELETE


删除记录


delete from tbl_name [where where_condition]


delete from users where id = 3;

SELECT


1.select version()select now()


2.每一个表达式表示想要的一列,必须有至少一个,多个列之间以逗号隔开


select id,age from users;

3.对多张表查寻,加上每张表名。users.name


4.别名


select id as userid,username as uname from users;

WHERE


条件表达式:对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。


在WHERE表达式中,可以使用MySQL支持的函数或运算符


GROUP BY


select sex from users group by sex;

HAVING分组条件


select sex from users group by sex HAVING count(id) >=2;

ORDER BY 对查询结果进行排序


select * from users order by id desc; select * from users order by id,age desc;

LIMIT 限制查询结果返回的数量


select * from users limit 2;

从序列号开始限制:


select * from users limit 2,2;select * from users order by id DESC LIMIT 2,2;

子查询 :出现在其他SQL语句内的SELECT子句


子查询嵌套在查询内部,且必须始终出现在圆括号内。


子查询可以包含多个关键字或条件,


如 DISTINCT,GROUP BY,ORDER BY,LIMIT 函数等


子查询的外层查询可以是:SELECT ,INSERT,UPDATE,SET或do


子查询返回值:子查询可以返回标量、一行、一列或子查询


比较运算符 :


select round(AVG(goods_price),2) from tdb_goods;
select goods_id,goods_name,goods_price from tdb_goods >= (select
-> round(AVG(goods_price),2) from tdb_goods);

ANY


SOME


ALL


NOT IN 与!=ALL或<>ALL等价


EXISTS:如果子查询返回任何行,返回TRUE,否则为FALSE


多表更新:


UPDATE table_reference {inter|cross join} table_reference on


update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name
set goods_cate = cate_id

多表更新之一步到位:CREATE...SELECT


连接的语法结构:


内连接:显示左表及右表符合连接条件的记录 INER JOIN


左外连接:显示左连接的全部以及右表符合连接条件的记录 LEFT JOIN


右外连接:RIGHT JOIN


多表连接:


SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
-> INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
-> INNER JOIN tdb_goods_brand AS b ON g.brand_id = b.brand_id;

无限分类的数据表设计:


同一数据表对其自身进行连接:


SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT
-> JOIN tdb_goods_types AS p
-> ON s.parent_id = p.type_id;SELECT p.type_id,p.type_name,s.type_name FROM
-> tdb_goods_types AS p LEFT JOIN
-> tdb_goods_types AS s ON s.parent_id = p.type_id;

多表删除:


1.查找重复记录


SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

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 ) AS t2 ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台