第四藏：数据库-2

第四章：数据库-2

创建所需要的数据：

#--01-数据库进阶操作-材料准备

-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- students表
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
);
-- 如果自己有数据，可以运行如下语句，xxx替换成sql文件的名字，可以直接导入文件
source xxx.sql;

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);


-- 向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),
(0,'凌小小',28,180.00,2,1,0),
(0,'司马二狗',28,120.00,1,1,0);

-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期"),(8,'Python_03期');

---

简单的查询练习：

use python_test_1;
select name as "姓名" from students;
select students.name from students;
select s.name from student as s;
select gender from students;
# 去重
select distinct gender from students;

---

where语句查询：

· select * from TABLENAME where CONDITION;
· eg. select * from students where id=1;
· 运算符：=,>,>=,<,<=,!=或<>

use python_test_1;
-- age > 18
select * from students where age > 18;
-- others

---

逻辑运算符：
    and 
    or 
    not 

-- and
select * from students where age > 18 and age <28;
-- or
select * from students where age > 18 or height >= 180;
-- not
select * from students where not (age > 18 and gender='男');

---

模糊查询：
    like 
        % 表示任意多个字符
        _ 表示任意一个字符

-- %
select * from students where name like "小%";
select * from students where name like "%小%";
-- _
select * from students where name like "__";
select * from students where name like "__%";

---

范围查询：
    between-and 连续范围查询
    in 非连续范围查询

-- in
select name,age from students where age in (18,34);
select name,age from students where age not in (18,34);
-- between and
select * from students where id between 18 and 34;
select * from students where id not between 18 and 34;

---

空值判断：
    is null 空
    is not null 非空

-- height null
select * from students where height is null;
select * from students where height is not null;

---

排序查询：
    select * from TABLE order by 列1 asc|desc [, 列2 asc|desc]
    默认asc

-- male 18-34 height asc
select * from students 
where age between 18 and 34 and gender = 1
order by height asc;

---

聚合函数（组函数）：
    count(字段) 计算总行数
    max(字段) 此字段最大值
    min(字段) 此字段最小值
    sum(字段) 此字段之和
    avg(字段) 此字段平均值
    · 每个组函数接受一个参数
    · 忽略NULL记录
    · 不允许嵌套sum(max(xxx))

-- count
select count(*) from students where gender = 1;
-- max
select max(age) from students;
-- min
select min(height) from students;
-- sum
select max(age) from students;
-- avg
select avg(age) from students;
-- 四舍五入
select round(avg(age),2) from students;

---

分组查询：

· 划分区域，分成几个小组

---

group by
group_concat(……) 想要显示数据就必须得用这个函数

-- 正确写法
select gender from students group by gender; 
-- 错误写法
-- select name,gender from students group by gender; 
-- 可用聚合函数
select gender,count(*) from students group by gender; 
--
select group_concat(name),gender from students group by gender; 
-- 
select avg(age),gender from students group by gender;

---

having --注意一般是与group by连用
with rollup --进行汇总,类似统计总数，用在group by后

select group_concat(name),gender 
from students 
group by gender 
having avg(age)>30;
--
select count(*),gender 
from students 
group by gender
with rollup;

---

分页查询：

· 数据过大，不进行分页，很可能系统崩溃

---

limit 起始记录,记录数 
    -- 要写在sql语句的最后

-- 展示五个数据
select * from students
limit 0,5;
-- 展示第一页
select * from students
limit 0,2;
-- 直接展示第二页
select * from students
limit 2,2;
-- 按年龄排序的分页查询
select * from students
order by age asc
limit 6,2;

---

连接查询：

· 使用场景：数据来源于多张表时，需要进行连接

---

内连接查询：
    两个表的交集
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
    · 内连接会涉及到两个表的连接
    · 但是表连接的顺序会影响到语句的执行速度

--
select * from students inner join classes;
--
select * 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; 

---

外连接查询：
    左连接，右连接 
        -- 保留交集的左边或者右边，就是无匹配的话也会展示数据

-- left join
select *
from students
left join classes
on students.cls_id = classes.id; 
-- right join
select *
from classes
left join students
on students.cls_id = classes.id;

---

自连接查询：
    · 同一个表进行连接
select * from table as t1
inner join table as t2
on t1.xxx = t2.xxx
where t1.xxx = xxx;

---

子查询：
select * 
from (select * 
      from t2);

select *
from (select * from classes
               where id in (1,2,3)) t1;