MySQL学习小记2

最新推荐文章于 2024-10-03 09:02:12 发布

weixin_33910460

最新推荐文章于 2024-10-03 09:02:12 发布

阅读量94

点赞数

文章标签： python 数据库

原文链接：https://my.oschina.net/finndai/blog/823303

版权

2019独角兽企业重金招聘Python工程师标准>>>

MySQL数据库记录操作

INSERT

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

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

同时插入多条记录：valuses（）,（）中间用逗号隔开

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

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

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

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

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

UPDATE

1.单表更新

update table_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;

转载于:https://my.oschina.net/finndai/blog/823303