-- 插入 --INSERTINTO test01 VALUES(3,'小红',21,'郑州')-- 修改 --UPDATE test01 SET address='河南'WHERE id=3-- 删除 --DELETEFROM test01 WHERE id=2-- 查询 --SELECT name FROM test01 WHERE id=1SELECT*FROM-- 模糊查询SELECT*FROM test01 WHERE name LIKE'%牛%'-- 别名查询SELECT name AS n,address a FROM test01
-- 获取当前时间戳和版本号SELECT UNIX_TIMESTAMP()AS now,@@versionAS version,@@version-- 批量插入--INSERTINTO test01 VALUES(6,'小名',20,'开封'),(7,'小红',19,'通许'),(8,'牛叉',20,'商丘'),(9,'长虹',24,'周口'),(10,'次奥丰',26,'郑州')-- 蠕动复制 --,INSERTINTO test03 SELECT*FROM test01
INSERTINTO test02(id,name,age,address)SELECT id,name,age,address FROM test01
-- 考虑所有字段去重(不含逻辑主键) --SELECTDISTINCT name,age,address FROM test01
-- 多表数据源(笛卡尔积)SELECT*FROM test01,test02
SELECT t01.*,t02.name FROM test01 t01,test02 t02
-- 高级查询(运算符)SELECT*FROM test01 WHERE age >=20SELECT*FROM test01 WHERE age BETWEEN21AND25SELECT*FROM test01 WHERE age IN(17,18,19,20)SELECT*FROM test01 WHERE age ISNULLSELECT*FROM test01 WHERE age ISNOTNULL-- 分组 (数据统计,查出每个班级的人数)SELECTCOUNT(*),className FROM test01 GROUPBY className
-- 分组 先按大的分组再按小的分组(查出每个班级男女人数)SELECTCOUNT(*),className,sex FROM test01 GROUPBY className,sex
-- 查出每个班级人数并且都是谁SELECTCOUNT(*),className,GROUP_CONCAT(name)FROM test01 GROUPBY className
-- 查出每个班级总人数,并记录每个班级任意一个学生名字SELECTCOUNT(*),any_value(name),className FROM test01 GROUPBY className
-- 统计每个班级女生人数SELECTCOUNT(*),className FROM test01 WHERE sex='女'GROUPBY className
-- 回溯统计 (统计每个班级男女各有多少人分别都是谁,一共有多少人)SELECTCOUNT(*),className,sex,GROUP_CONCAT(name)FROM test01 GROUPBY className,sex WITH ROLLUP-- 分组排序 ASC升序 DESC降序序SELECTCOUNT(*),className,sex FROM test01 GROUPBY className,sex DESC-- 分组统计 having用在group by后(筛选出总人数在2人以上的班级)SELECTCOUNT(*),className FROM test01 GROUPBY className HAVINGCOUNT(*)>2-- order by排序 SELECT*FROM test01 ORDERBY age DESCSELECTCOUNT(*),className,sex,age FROM test01 GROUPBY className HAVINGCOUNT(*)>1ORDERBY sex DESC,age ASC-- limit 限制条件 实现分页查询(从第三条数据开始查询,查询三条数据)SELECT*FROM test01 LIMIT3,3-- limit子句批量更新(前三条数据年龄都加1)UPDATE test01 SET age=age+1LIMIT3-- 限制删除 LIMITDELETEFROM test01 WHERE age ISNULLLIMIT1-- 清空数据TRUNCATE test03
-- 联合查询(union默认去重,union all联合查询所有)SELECT*FROM test02 UNIONSELECT*FROM test03
SELECT*FROM test02 UNIONALLSELECT*FROM test03
-- union 优先级高于order by SELECT*FROM test02 UNIONSELECT*FROM test03 ORDERBY age DESC(SELECT*FROM test02 ORDERBY age descLIMIT99)UNIONALL(SELECT*FROM test03 ORDERBY age DESCLIMIT99)-- 交叉连接SELECT*FROM test01 CROSSJOIN test03
SELECT*FROM test01,test03
-- 内连接SELECT t.*,m.maj_name FROM test01 AS t INNERJOIN majorTest m ON t.majId=m.id
-- 外链接SELECT t.*,m.maj_name FROM test01 AS t LEFTJOIN majorTest m ON t.majId=m.id
SELECT t.*,m.maj_name FROM test01 AS t RIGHTJOIN majorTest m ON t.majId=m.id
-- 自然连接SELECT*FROM test01 NATURALJOIN majortest
-- using关键字SELECT*FROM test01 AS t LEFTJOIN test02 USING(name)-- 标量子查询 根据专业表的数据查询学生的数据SELECT*FROM test01 WHERE majId=(SELECT id FROM majortest WHERE maj_name='软件工程')-- 列子查询 (获取所有学生的专业信息)SELECT*FROM majortest WHERE id in(SELECTDISTINCT majId FROM test01 WHERE majId ISNOTNULL)-- 行子查询 (查询和某一个人相同年龄和地址的其他人)SELECT*FROM test01 WHERE(age,address)=(SELECT age,address FROM test02 WHERE name='花花')-- 表子查询 (获取表中每个班级年龄最大的学生信息(姓名、年龄、班级名字),然后按照年龄的降序排列)-- 尝试直接解决SELECT any_value(name),MAX(age) m_age,className FROM test01 GROUPBY className ORDERBY m_age DESC-- MYSQL7之后带上limitSELECT any_value(name),MAX(age) m_age,className FROM(SELECT name, age, className FROM test01 ORDERBY age DESCLIMIT9999)AS t GROUPBY className ORDERBY m_age DESCSELECT any_value(name),MAX(age) m_age,className FROM(SELECT name, age, className FROM test01 )AS t GROUPBY className ORDERBY m_age DESC-- exists子查询(获取所有学生的专业信息)SELECT*FROM majortest m WHEREEXISTS(SELECT id FROM test01 WHERE m.id=majId)