-- 注释
-- 创建数据库
create database python_test_1 charset=utf8;
-- 查看数据库
show databases;
-- 使用数据库
use python_test_1;
-- 显示使用的数据库是哪个
select database();
-- student表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5, 2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
-- bit 相当于 bit(1), 还可以bit(2)
-- 查看数据库创建语句
show create table students;
-- classes表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
-- 查看数据表
show tables;
-- 向students表中插入数据
insert into students values
(0, '小明', 18, 180.00, 2, 1, 0),
(0, '小月月', 18, 180.00, 2, 2, 1),
(0, '彭于晏', 29, 185.00, 1, 1, 0),
(0, '刘德华', 59, 175.00, 1, 2, 1),
(0, '黄蓉', 38, 160.00, 2, 1, 0),
(0, '凤姐', 28, 150.00, 4, 2, 1),
(0, '王祖贤', 18, 172.00, 2, 1, 1),
(0, '周杰伦', 36, null, 1, 1, 0),
(0, '程坤', 27, 181.00, 1, 2, 0),
(0, '刘亦菲', 25, 166.00, 2, 2, 0),
(0, '金星', 33, 162.00, 3, 3, 1),
(0, '静香', 12, 180.00, 2, 4, 0),
(0, '郭靖', 12, 170.00, 1, 4, 0),
(0, '周杰', 34, 176.00, 2, 5, 0);
-- 向classes表中插入数据
insert into classes values(0, 'python_01期'), (0, 'python_02期');
-- 查询
-- 查询所有字段
-- select * from 表名;
select * from students;
select * from classes;
-- 查询指定字段
-- select 列, 列 ... from 表名;
select age, name from students;
-- 使用 as 给字段起别名
select name as 姓名, age as 年龄 from students;
-- select 表名.字段 ... from 表名;
select students.name, students.age from students;
-- 可以通过 as 给表起别名
select s.name, s.age from students as s;
-- 不可以 select students.name, s.age from students as s;
-- 消除重复行
-- distinct 字段
select distinct gender from students;
-- 条件查询
-- 比较运算符
-- select ... from 表名 where ...
-- > 查询大于18岁的信息
select * from students where age > 18;
-- < 查询小于18岁的信息
select * from students where age < 18;
-- >= <=
select * from students where age >= 18;
-- =
select * from students where age = 18;
-- != 或者 <>
select * from students where age != 18;
-- 逻辑运算符
-- and
select * from students where age > 18 and age < 28;
select * from students where age > 18 and gender='女';
select * from students where age > 18 and gender=2;
-- or
select * from students where age > 18 or height >= 180;
-- not
-- 不在 18岁以上的女性
-- 错误 select * from students where not age > 18 and gender=2;
select * from students where not (age > 18 and gender=2);
-- 年龄不是小于等18 并且是女性
select * from students where (not age <= 18) and gender=2;
-- 模糊查询
-- like 性能低
-- % 替换零个，一个或多个
-- _ 替换一个
-- 查询名字中 以"小"开始的名字
select name from students where name like "小%";
-- 查询姓名中有 "小"的名字
select name from students where name like "%小%";
-- 查询有2个字的名字
select name from students where name like "__";
-- 查询至少2个字的名字
select name from students where name like "__%";
-- rlike 正则
-- 查询 以"周"开始的姓名
select name from students where name rlike "^周.*";
-- 查询 以"周"开始，"伦"结尾的姓名
select name from students where name rlike "^周.*伦$";
-- 范围查询
-- in (1, 3, 8) 表示在一个非连续的范围内
select name, age from students where age = 18 or age = 34 or age = 12;
select name, age from students where age in(18, 34, 12);
-- not in ()
select name, age from students where age not in(18, 34, 12);
-- between .. and ..
-- [18, 34]
select name, age from students where age between 18 and 34;
-- not between .. and ..
select name, age from students where age not between 18 and 34;
select name, age from students where not age between 18 and 34;
-- 语法错误 select name, age from students where age not (begin 18 and 34);
-- 判断空
-- is null
select * from students where height is null;
-- is not null
select * from students where height is not null;
-- 排序
-- order by 字段
-- asc 从小到大 即升序
-- desc 从大到小 即降序
select * from students where age between 18 and 34 and gender=1 order by age;
select * from students where (age between 18 and 34) and gender=1 order by age; -- 默认asc
select * from students where (age between 18 and 34) and gender=1 order by age asc;
-- 如果有相同的情况下，默认按主键排，从小到大排
select * from students where (age between 18 and 34) and gender=2 order by height desc, id desc;
select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc, id desc;
select * from students order by age asc, height desc;
-- 聚合函数
-- count 总数
select * from students where gender=1;
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
-- max 最大值
select age from students;
select max(age) from students;
select max(height) from students where gender=2;
-- min 最小值
-- sum 求和
select sum(age) from students;
-- avg 平均值
select avg(age) from students;
-- 平均年龄
select sum(age)/count(*) from students;
-- 四舍五入 round(123.00, 1) 保留1位小数
select round(sum(age)/count(*), 2) from students;
-- 分组
-- group by
-- 按照性别分组，查询所有性别
-- 先分组，再从组里取数据
select gender from students group by gender;
-- 错误 select name from students group by gender;
-- 错误 select * from students group by gender;
select gender, count(*) from students group by gender;
select gender, max(age) from students group by gender;
select gender, group_concat(name) from students group by gender; -- 各性别，都有谁
select gender, group_concat(name, age) from students group by gender; -- 各性别，都有谁
select gender, group_concat(name, '_', age) from students group by gender; -- 各性别，都有谁
-- 计算男性的人数
select gender, count(*) from students where gender=1 group by gender;
select gender, group_concat(name, '_', age) from students where gender=1 group by gender; -- 各性别，都有谁
-- having
-- 查询平均年龄超过30的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;
-- where 对源表过滤， having 对组过滤
-- 分页
-- limit start, count
-- 限制查询出来的个数
select * from students where gender=1 limit 2;
select * from students where gender=1 limit 0, 2; -- 0=0*2=(1-1)*2
select * from students where gender=1 limit 2, 2; -- 2=1*2=(2-1)*2 => (n-1)*count
-- 不能用表达式，需要计算结果出来 select * from students limit 2*(6-1), 2;
-- limit 放在最后
select * from students order by gender limit 10, 2;
-- 错误 select * from students limit 10, 2 order by gender
select * from students where gender=2 order by height desc limit 0,2;
-- 连接查询
-- 内连接，取交集
-- inner join .. on
-- select .. from 表A inner join 表B
select * from students inner join classes;
-- 新表数据总数: m * n
-- 查询 有能过对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;
-- 新表数据总数 <= m * n
-- 按照要求显示姓名、班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
--
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name, s.id;
-- 左连接
-- left join
select s.*, c.name from students as s left join classes as c on s.cls_id=c.id;
select s.*, c.name from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select s.*, c.name from students as s left join classes as c on s.cls_id=c.id where c.id is null;
-- 右连接
-- right join可以用left join实现
-- 自关联
-- id name id name c_id
-- 1 北京市 1 朝阳区 1
-- 2 上海 2 浦东 2
-- |
-- v
-- id name p_id
-- 1 北京市 null
-- 2 上海 null
-- 3 朝阳区 1
-- 4 浦东 2
-- 5 三林镇 4
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
-- mysql -uroot -p
show databases;
use python_test_1;
-- source areas.sql;
-- 省的地级市
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="上海";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="上海";
-- 子查询 (慢点)
-- 标量子查询
-- 查询最高的男生信息
select * from students where height = (select max(height) from students);
select * from areas where pid = (select aid from areas where atitle="上海");
