mysql数据库查询总结

查询语句的顺序:select +筛选列名(从表里拿[查出的表,级联的表])+[聚合]+from +表名 +[筛选条件]+[分组]+[筛选条件(having)] +[ 排序];

-- where 字句构造的筛选是分组以前的筛选-- 如果希望对分组以后的数据进行筛选,就要写having字句而不是where字句-- 经验:在分组后使用 order by null 来避免默认的排序操作提升查询性能

-- 连接查询
把难直接比较的条件查出来,连入一个表中,然后就可以比较了
先用连表条件连表,有了一张合并表之后再在里面筛选(有了表之后才能筛选),各个表的成员列,相当于在一个表上

 

-- SQL(Structured Query Language): 结构化查询语言(关系型数据库的编程语言)
-- DDL(数据定义语言): create(创建) / drop(删除) / alter(修改)
-- DML(数据操纵语言): insert(插入) / delete(删除) / update(更新) 
-- DQL(数据查询语言): select(选择)
-- DCL(数据控制语言): grant(授予权限) / revoke(召回权限) / begin(开启事务) / commit(提交事务) / rollback(回滚事务)
​
-- 关系型数据库中数据完整性指的是什么
-- 1. 实体完整性: 每条记录都是独一无二的(主键/唯一约束/唯一索引)
-- 2. 参照完整性: 表中的数据要参照其他表已有的数据(外键)
-- 3. 域完整性: 数据是有效的(数据类型/非空约束/默认值约束/检查约束)
​
-- 创建数据库,create database 库名 default 编码
drop database if exists grade;
create database grade default charset utf8;
# 切换到数据库
use grade;
​
-- 建表
-- 用二维表组织数据    - 表(table):实体
--                  - 行(line):记录
--                  -  列(row):字段
-- 关系型数据库用二维表组织数据
drop table if exists tb_grade;
-- 主键(primary key - 能够表识唯一一条记录得列
--
create table tb_grade(
id int not null auto_increment comment '编号',
grade_name varchar(20) not null comment '班级名称',
grade_boss carchar(20) not null comment '班主任姓名',
primary key (id)
);
​
-- 增删改
alter table tb_grade add column student_count int ;
​
alter table tb_grade drop column grade_boss varchar(30);
​
-- 插入记录
​
insert into tb_grade values 
(1,'python',30),
(2,'java',40),
(3,'php',25);
​
drop table if exists tb_student;
​
-- 创建学生表
-- 主键(primary key): 能够标识唯一一条记录的一个或多个列
create table tb_student
(
stuid int not null comment '学号',
sname varchar(10) not null comment '姓名',
ssex bit default 1 comment '性别',
stel char(11) comment '联系电话',
sbirth date comment '出生日期',
primary key (stuid)
);
-- 创建课程表
create table tb_course
(
courseid int not null comment '课程编号' primary key,
cname varchar(20) not null comment '课程名称',
ccredit int not null comment '学分'
);
-- 创建学生选课表
-- 可以通过中间表来建立学生和课程之间的多对多关系
-- 在实际开发中不建议使用复合主键而且尽可能使用跟业务无关的列做主键
-- int类型的主键可以通过auto_increment设置为自增主键
create table tb_sc
(
scid int not null auto_increment comment '选课编号' primary key,  -- 设置主键
sid int not null comment '学生编号',
cid int not null comment '课程编号',
score float comment '考试成绩',
foreign key(sid) references tb_student(stuid) -- 关联外键
);
-- 实体与实体表 外键约束+唯一性约束 = 一对一约束 
--            外键约束 = 一对多约束
--            一对多   外键加在多的表内
--            多对多   建立第三表,关联外键
-- 外键约束,建立表与表之间的联系,pid的值设置来自父级表的perid的值, fk_idcard_pid是自己取的约束的名称
alter table tb_sc add constraint fk_sc_sid
foreign key(cid) references tb_course (courseid);
​
-- foreign key(user_id) references app_users(id) ,
-- foreign key(goods_id) references app_goods(id) 

 

-- 查询
-- 查询所有
select * from tb_student,tb_course; -- 做了一个笛卡尔积
-- 投影和别名(as): 将查询出来的字段作为什么名称展示
-- 可以对查上来的列做算术运算,或者转换类型再做算术运算
select sname as name from tb_student;
select sname as 姓名,if(ssex,'男','女') as 性别 from tb_student;
-- 将查出来的列,做条件映射
select case ssex when 1 then '男' else '女' end as '性别' from tb_student;
select if(ssex,'男','女') as '性别' from tb_student;
​
-- 筛选
select sname as name from tb_student 
where ssex=0;
​
-- 范围筛选
select sname,ssex,sbirth from tb_student
where '1980-1-1' <= sbirth <= '1990-1-1';
​
-- 模糊查询 ['% 匹配多个的通配符','_匹配一个的通配符','正则']
select * from tb_student
where sname like '%王%';
​
select * from tb_student
where sname like '王_';
​
select * from tb_student
where sname regexp '.+[三蓉].*';
​
-- 多条件和控制处理 
-- exists ,not exists,distinct,in :判断是否存在,去重
-- 判断是否为空值** is null / ** is not null,不能用=或者!=
select * from tb_student
where sbirth is null;
​
-- 去重 distinct
select distinct saddr from tb_student
where saddr is not null;
​
-- 排序 
-- 按照第一个排列条件,排不出来再按照第二个筛选条件排序
select * from tb_student
order by sname asc,sbirth desc;
​
-- 聚合函数: 聚合的操作都是对一列进行运算
-- 聚合函数是对查询上来的结果做聚合
-- max() / min() / sum() / avg() / count() -- 自动排除空值行
select min(sbirth) from tb_student;
-- where 字句构造的筛选是分组以前的筛选
-- 如果希望对分组以后的数据进行筛选,就要写having字句而不是where字句
-- 经验:在分组后使用 order by null 来避免默认的排序操作提升查询性能
​
-- 这种分组没有意义,分组最好和聚合函数一起用
select sid ,cid from tb_sc
group by sid order by null;
​
-- 子查询
-- 查询一个值/表,用这个值/表做筛选条件
select sname,sbirth from tb_student
where sbirth=(select max(sbirth) from tb_student);
-- 查出一张表,用表做筛选
select sname from tb_student
where stuid in (
select sid from tb_sc group by sid having count(sid)>2)
and ssex=1;
-- 连接查询,按照条件连接,连接成一张新表
-- 左外连接: 左表不满足连表条件的记录也要查询出来
-- t1 left out join t2 on 连表条件
-- 右外连接: 右表不满足连表条件的记录也要查询出来
-- t1 right out join t2 on 连表条件
-- 内连接: 查询满足连表条件的记录
-- t1 inner out join t2 on 连表条件
select sname,cname,score from tb_sc,tb_student,tb_course
where sid=stuid and cid=courseid; -- 连接条件
​
select sname,avg(score) from tb_student,tb_sc
where sid=stuid
group by sid having avg(score)>60;
​
-- 把几张表按条件连接,然后再按条件筛选
-- 查出一张表,然后用这张表再做连接
select sname,cc from tb_student,
(select sid as id,count(cid) as cc from tb_sc
group by sid ) t1
where stuid=id;
-- 左外连接
select sname,total from tb_student t1
left outer join
(select sid,count(cid) as total from tb_sc
group by sid  having count(cid)>2) t2
on stuid=sid;
-- 内连接
select ename,sal,acgsal from TbEmp t1
inner join
(select avg(sal) as acgsal from tbemp ) t2
on sal>acgsal;
-- 内连接与分页查询
select sname,cname,score from tb_sc
inner join tb_student 
on tb_sc.sid=tb_student.stuid
inner join tb_course 
on tb_sc.cid=tb_course.courseid
-- limit 5        只查5条记录,默认从第一条开始
-- limit 0,5      只查5条记录,从第一条开始
limit 5 offset 10; -- 只查5条记录,从第11条开始
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​
​