mysql 常用sql语句整理

-- 查看所有数据库

show DATABASES;

-- 切换到xxx库：
use fat_jinjiedao;

-- 查看当前库中所有的表
show TABLES;

-- 查看xxx表的表结构：
desc bind_bank;

-- 数据库创建
create DATABASE fat_test default character set='utf8'

-- 删除数据库 慎用
DROP DATABASE fat_test

-- 创建表
CREATE TABLE `lcx_test` (
`id` int(11) NOT NULL AUTO_INCREMENT, #自动递增
`open_id` varchar(50) DEFAULT NULL COMMENT '用户openId',
`question` varchar(200) COMMENT '用户提问的问题',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COMMENT='用户提问的问题'; #ENGINE=InnoDB数据库引擎，可以省略

-- 删除表结构
drop TABLE lcx_test;

-- 插入记录 单条新增
INSERT into lcx_test(open_id,create_time) VALUES('asdfasdfd0001',NOW());

-- #全表插入 所有字段
INSERT into lcx_test VALUES(35i'dfasdfd00034','what are you doing',NOW());

-- 批量新增
INSERT into lcx_test(open_id,question)
VALUES('asdfasdfd0001','what are you doing'),
('asdfasdfd0001','what are you doing'),
('asdfasdfd0001','what are you doing');

-- 更新表字段
update lcx_test set open_id='test0001',question='l am testing' where id in (35,36,37)

-- 删除表里的数据，可加条件
DELETE FROM lcx_test;
DELETE FROM lcx_test where id=33;

-- mysql 字段判空,用is,不用“=”和"<>、!="
select * from lcx_test where question is NULL;
select * from lcx_test where question is not null;

-- 范围查询 BETWEEN...AND...,in,not in
select * from lcx_test where id BETWEEN 34 and 37;

-- 去重
SELECT DISTINCT(question) from lcx_test;

-- 排序order by ... asc/desc, 默认asc
SELECT * from lcx_test

-- 限制、分页 获取41-60的记录
select * from lcx_test LIMIT 40,20;

-- 分组(多属性分组，用”,”分隔) GROUP BY ... , ...
-- 分组后过滤 HAVING,用法GROUP BY ... HAVING...

-- 执行顺序
FROM ...WHERE...GROUP BY...HAVING...SELECT...ORDER BY

-- 等值/内连接(两表的交集) INNER JOIN.. on ..
select * from user u INNER JOIN morder m on u.id=m.id;

-- 左连接（左表全部，右表交叉数据）
select * from user u LEFT JOIN morder m on u.id=m.id;

-- 右连接（左表全部，右表交叉数据）
select * from user u RIGHT JOIN morder m on u.id=m.id;

-- 嵌套/子查询(子查询可在select,where,from后)
select sid sname from xs where sid in (select sid from cj GROUP BY sid HAVING COUNT(sid)=1)

CREATE TABLE `lcx_sc` (
`id` int(11) NOT NULL AUTO_INCREMENT, #自动递增
`name` varchar(50),
`lesson` varchar(100),
`score` int(11),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; #ENGINE=InnoDB数据库引擎，可以省略
select * from lcx_sc

-- 查询每门课成绩都高于80分的同学
select name,min(score) from lcx_sc GROUP BY name HAVING min(score)>80

-- 查询从来没查过定单的学生，两种方式
SELECT name from
(select u.name,m.id user u LEFT JOIN morder m on u.id=m.id) tmp
where tmp.id is NULL;

SELECT name from user where id not in
(SELECT id from morder);

select u.name,m.id,m.order_price,m.region,a.region_avg_price
from morder m
INNER JOIN (select region,avg(order_price)as region_avg_price from morder group by region) a on m.region=a.region
INNER JOIN user u on m.id =u.id
WHERE m.order_price<a.region_avg_price;

-- 聚合函数 max、min、avg、sum、count
select xs.cid,kc.name,max(xs.csore),min(xs.csore),avg(xs.csore) from xs LEFT JOIN kc on xs.cid=kc.cid GROUP BY xs.cid
select cid,count(sid) from cj group by cid
SELECT cid,cscore from cj WHERE cscore<60 ORDER BY cid DESC
select * from cj GROUP BY cid HAVING cscore<60 ORDER BY cid DESC

select sid sname from xs where sid in (select sid from cj GROUP BY sid HAVING COUNT(sid)=1)
select sid,sname from xs GROUP BY sid HAVING avg(CSORE)>60
SELECT SID,SNAME FROM XS GROUP BY SID HAVING AVG(CSORE)>60