-- 创建一个数据库createdatabase python_test charset=utf8;-- 使用一个数据库
use python_test;
-- 显示使用的当前数据是哪个?selectdatabase();-- 创建一个数据表-- students表createtable students(
id int unsigned primarykey auto_increment notnull,
name varchar(20) default'',
age tinyint unsigned default0,
height decimal(5,2),
gender enum('男','女','中性','保密') default'保密',
cls_id int unsigned default0,
is_delete bitdefault0
);-- classes表createtable classes (
id int unsigned auto_increment primarykeynotnull,
name varchar(30) notnull
);
添加数据:
insert into students values(0,"小明",18,188,"男",1,0)
insert into classes values(0,一班)
...
-- 添加若干个
普通查询:
-- 查询所有:select * from students;-- ----------------------------------------- 查询指定:select id,name from classes;-- ----------------------------------------- 起别名:select name as 名字,age as 年龄 from classes;select students.name,students.age from students;select s.name,s.age from students as s;-- ----------------------------------------- 消除重复行:selectdistinct gender from students;
条件查询
-- 比较运算符(>,<,>=,<=,=,!=,<>):select * from students where age>18;select name from students where age=18;-- ----------------------------------------- 逻辑运算符(and,or,not):select * from students where age>18and age<28;select * from students where age>18or heigh>=170;select * from students wherenot (age>18and gender=2);select * from students where (not age>18) and gebder=2;-- ----------------------------------------- 模糊查询:-- like:-- %替换0个或多个-- _替换1个select * from students where name like"小%";select name from students where name like"__";-- relike正则:select name from students where name relike "^周.*";select name from students where name relike "^周.*伦";-- ----------------------------------------- 范围查询(in,not in,between...and...,not between...and...):select name,age from students age in (18,20,25);select name,age from students age notin (18,20,25);select name,age from students age between 18and30;select name,age from students age not between 18and30;-- ----------------------------------------- 空判断:select name,height from students height isnull;select name,height from students height isnotnull;
排序
-- 生序,从小到大(asc):select * from students where (age between 18and34) and gender=2orderby age asc;-- 降序,从大到小(desc):select * from students where (age between 18and34) and gender=2orderby age desc;-- ----------------------------------------- order by 多字段(第一个相同,按第二个...):select * from students where (age between 18and34) and gender=2orderby age desc,id desc,height asc;select * from students orderby age asc,height desc;
聚合函数
-- 总数count:selectcount(*) from students where gender=1;selectcount(*) as 男性人数 from students where gender=1;-- ----------------------------------------- 最大值max:selectmax(age) from students;selectmax(age) from students where gender=1;-- ----------------------------------------- 最小值min:selectmin(age) from students;-- ----------------------------------------- 求和sum:selectsum(age) from students;-- ----------------------------------------- 平均值avg:selectavg(age) from students;selectsum(age)/count(*) from students;-- ----------------------------------------- 四舍五入round(123.456,1):123.456保留1位小数,四舍五入select round(avg(age),2) from students;
分组
-- group by:-- 按性别分组:select gender from students groupby gender;-- 对分组结果计算数目:select gender,count(*) from students groupby gender;-- 对分组前的数据进行过滤(where):select gender,count(*) from students where gender=1groupby gender;-- ----------------------------------------- group_concat():-- 按gender分组后,各组name成员有谁:select gender,group_concat(name) from students groupby gender;-- 组合显示name,age,id:select gender,group_concat(name,age,id) from students groupby gender;-- 组合显示name,"_",age, ,id:select gender,group_concat(name,"_",age, ,id) from students groupby gender;-- ----------------------------------------- having对分组后的各组按条件过滤:select gender,group_concat(name),avg(age) from students groupby gender havingavg(age)>30;
分页
-- limit:-- 查询前五个数据:select * from students limit 5;-- 查询从第1个开始共5数据(计数从0开始):select * from students limit 0,5;-- 复杂代码limit位置在最后:select * from students where (age between 18and34) and gender=2orderby age desc,id desc,height asc limit 0,5;-- 使用limit分页(每页显示5个):-- 第一页:select * from students limit 0,5;-- 第二页:select * from students limit 5,5;-- 第三页:select * from students limit 10,5;-- 第四页:select * from students limit 15,5;-- 第五页:select * from students limit 20,5;-- 第六页:select * from students limit 25,5;
连接查询
内连接(只保留两表交集部分)
-- 内连接inner join ... on:-- select ... from 表A inner join 表B-- 将两个表连接在一起:select * from students innerjoin classes
-- 加入条件students.cls_id=classes.id过滤:
select * from students innerjoin classes on students.cls_id=classes.id;-- 对上述结果优化,不显示clesses.idselect students.*,clesses.name from students innerjoin classes on students.cls_id=classes.id;-- 只显示学生名字,班级名字:select students.name,clesses.name from students innerjoin classes on students.cls_id=classes.id;-- 起名字:select s.name,c.name from students as s innerjoin classes as c on students.cls_id=classes.id;-- 结果排序:select s.name,c.name from students as s innerjoin classes as c on students.cls_id=classes.id orderby c.name;
外连接(以偏向表为基础连接,无数据部分为null)
-- 左连接left join ... on:以students为基准select * from students leftjoin classes on students.cls_id=classes.id;-- 查看没有班级的学生:having和where都可以select * from students leftjoin classes on students.cls_id=classes.id having classes.name isnull;select * from students leftjoin classes on students.cls_id=classes.id where classes.name isnull;-- ----------------------------------------- 右连接right join ... on:以classes为基准select * from students rightjoin classes on students.cls_id=classes.id;
自连接(一张表的值关联的也是自己的表)
-- 省级联动-- 表结构:三个列为aid,atitle,pid;pid与aid关联-- 自关联查询:select * from areas as province innerjoin areas as city on province.aid=city.pid having province.atitle="河北省"
子查询
-- 下列命令:selectmax(height) from students;
select * from students where height=188;
-- 等价于:
select * from students where (selectmax(height) from students)