SQL常用语句

-- 插入 --
INSERT INTO test01 VALUES(3,'小红',21,'郑州')
-- 修改 --
UPDATE test01 SET address='河南' WHERE id=3
-- 删除 --
DELETE FROM test01 WHERE id=2
-- 查询 --
SELECT name FROM test01 WHERE id=1
SELECT * FROM 
-- 模糊查询
SELECT * FROM test01 WHERE name LIKE '%牛%'
-- 别名查询
SELECT name AS n,address a FROM test01
-- 获取当前时间戳和版本号
SELECT UNIX_TIMESTAMP()AS now,@@version AS version,@@version
-- 批量插入--
INSERT INTO test01 VALUES(6,'小名',20,'开封'),(7,'小红',19,'通许'),(8,'牛叉',20,'商丘'),(9,'长虹',24,'周口'),(10,'次奥丰',26,'郑州')
-- 蠕动复制 --，
INSERT INTO test03 SELECT * FROM test01
INSERT INTO test02(id,name,age,address) SELECT id,name,age,address FROM test01
-- 考虑所有字段去重(不含逻辑主键) --
SELECT DISTINCT name,age,address FROM test01
-- 多表数据源(笛卡尔积)
SELECT * FROM test01,test02
SELECT t01.*,t02.name  FROM test01 t01,test02 t02
-- 高级查询（运算符）
SELECT * FROM test01 WHERE age >= 20
SELECT * FROM test01 WHERE age BETWEEN 21 AND 25
SELECT * FROM test01 WHERE age IN(17,18,19,20)
SELECT * FROM test01 WHERE age IS NULL
SELECT * FROM test01 WHERE age IS NOT NULL
-- 分组 (数据统计，查出每个班级的人数)
SELECT COUNT(*),className FROM test01 GROUP BY className  
-- 分组 先按大的分组再按小的分组(查出每个班级男女人数)
SELECT COUNT(*),className,sex FROM test01 GROUP BY className,sex
-- 查出每个班级人数并且都是谁
SELECT COUNT(*),className,GROUP_CONCAT(name) FROM test01 GROUP BY className
-- 查出每个班级总人数，并记录每个班级任意一个学生名字
SELECT COUNT(*),any_value(name),className FROM test01 GROUP BY className
-- 统计每个班级女生人数
SELECT COUNT(*),className FROM test01 WHERE sex='女' GROUP BY className  
-- 回溯统计 （统计每个班级男女各有多少人分别都是谁，一共有多少人）
SELECT COUNT(*),className,sex,GROUP_CONCAT(name)FROM test01 GROUP BY className,sex WITH ROLLUP
-- 分组排序 ASC升序 DESC降序序
SELECT COUNT(*),className,sex FROM test01 GROUP BY className,sex DESC
-- 分组统计 having用在group by后（筛选出总人数在2人以上的班级）
SELECT COUNT(*),className FROM test01 GROUP BY className HAVING COUNT(*)>2
-- order by排序 
SELECT * FROM test01 ORDER BY age DESC
SELECT COUNT(*),className,sex,age FROM test01 GROUP BY className HAVING COUNT(*)>1 ORDER BY sex DESC,age ASC
-- limit 限制条件 实现分页查询（从第三条数据开始查询，查询三条数据）
SELECT * FROM test01 LIMIT 3,3
-- limit子句批量更新（前三条数据年龄都加1）
UPDATE test01 SET age=age+1 LIMIT 3
-- 限制删除 LIMIT
DELETE FROM test01 WHERE age IS NULL LIMIT 1
-- 清空数据
TRUNCATE test03
-- 联合查询（union默认去重，union all联合查询所有）
SELECT * FROM test02 UNION SELECT * FROM test03
SELECT * FROM test02 UNION ALL SELECT * FROM test03
-- union 优先级高于order by 
SELECT * FROM test02 UNION SELECT * FROM test03 ORDER BY age DESC

(SELECT * FROM test02 ORDER BY age desc LIMIT 99)
UNION ALL(SELECT * FROM test03 ORDER BY age DESC LIMIT 99)
-- 交叉连接
SELECT * FROM test01 CROSS JOIN test03
SELECT * FROM test01,test03
-- 内连接
SELECT t.*,m.maj_name FROM test01 AS t INNER JOIN majorTest m ON t.majId=m.id
-- 外链接
SELECT t.*,m.maj_name FROM test01 AS t LEFT JOIN majorTest m ON t.majId=m.id
SELECT t.*,m.maj_name FROM test01 AS t RIGHT JOIN majorTest m ON t.majId=m.id
-- 自然连接
SELECT * FROM test01 NATURAL JOIN majortest
-- using关键字
SELECT * FROM test01 AS t LEFT JOIN test02 USING(name)
-- 标量子查询 根据专业表的数据查询学生的数据
SELECT * FROM test01 WHERE majId=(SELECT id FROM majortest WHERE maj_name='软件工程')
-- 列子查询 (获取所有学生的专业信息)
SELECT * FROM majortest WHERE id in(SELECT DISTINCT majId FROM test01 WHERE majId IS NOT NULL)
-- 行子查询 （查询和某一个人相同年龄和地址的其他人）
SELECT * FROM test01 WHERE (age,address)=(SELECT age,address FROM test02 WHERE name='花花')
-- 表子查询 （获取表中每个班级年龄最大的学生信息（姓名、年龄、班级名字），然后按照年龄的降序排列）
-- 尝试直接解决
SELECT any_value(name),MAX(age) m_age,className FROM test01 GROUP BY className ORDER BY m_age DESC
-- MYSQL7之后带上limit
SELECT any_value(name),MAX(age) m_age,className FROM 
(SELECT name, age, className FROM test01 ORDER BY age DESC LIMIT 9999)
AS t GROUP BY className ORDER BY m_age DESC

SELECT any_value(name),MAX(age) m_age,className FROM 
(SELECT name, age, className FROM test01 )
AS t GROUP BY className ORDER BY m_age DESC
-- exists子查询（获取所有学生的专业信息）
SELECT * FROM majortest m WHERE EXISTS(SELECT id FROM test01 WHERE m.id=majId)