MySQL DAY 02 查询&多表联查入门

MySQL DAY 02 重点知识总结 查询&多表联查入门

一 查询

上一篇文章简述了简单的查询方式 这里总结以下并且添加完整

1. 简单查询[全查询]
   select * from user; # * 代表的是左右列 即全部列

2. 带where字句 即判断
   select * from user where age = 14;
   select * from user where age>10 and age<14;
   select * from user where age between 14 and 16;
   select * from user where age in (10,12,14);
   select * from user where name like ‘马%’ # 以马开头的所有名字皆可 不限长
   select * from user where name like ‘马_’ # 以马开头的两字姓名
   select * from user where name like ‘_马%’ # 以马为第二个字的名字的所有
   以此类推

3. as 起别名
   select name as 姓名,age as 年龄 from user;
   select name 姓名,age 年龄 from user; # 结果和上述相同
   显示的数据列标题更改 但是不建议使用中文

4. distinct 去重
   select distinct age,name from user;

5. 排序
   select * from user order by age desc; #降序
   select * from user order by age asc; # 升序
   select * from user order by name desc; # 字符串排序根据字符串进行排序 不会报错
   select * from user order by name desc ,age asc; # 这里显示的是以name为主的降序, 如果name相同 则以age为主进行升序排列

7.聚合函数
1# count(): 统计不为null的所有数据的条数
select count(*) from user; # 总共有几条数据
select count(name) from user; # 总共有几个姓名 (非null)
select sum(age) from user # 求和
select max(age) from user # 最大值
select min(age) from user #最小值
select avg(age) from user # 平均值
select max(age),min(age) from user;

8.分组

-- # 员工表
create table emp(empno int primary key, enname varchar(20), job varchar(20), mgr int, hiredate date, sal double, comm double, deptno int);

# 添加数据
insert into emp values(7369,'smith','clark',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','managen',7839,'1981-04-02',2975,null,30);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manageer',7839,'1980-06-17',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-02-20',3000,null,20);
insert into emp values(7839,'king','president',null,'1987-02-20',5000,null,10);

1. 以此为基础的分组操作

   1. 查询各个部门的人数
      select deptno,count(*),avg(sal) from emp group by deptno;
      select deptno,avg(sal) from emp group by deptno having svg(sal)>1500; # (这里涉及到一个having 后面会详细叙述)
   2. 查询每个部门的编号和本部门工资的平均值
      select deptno,avg(sal) from emp where sal>1500 group by deptno having avg(sal)>=3000;
   3. 查询每一个部门编号和每一个部门工资大于1500的人数
      select deptno,count(*) from emp where sal>1500 group by deptno;

2. 这里不难看出分组的操作:

   1. 分组的操作group having
      group 指的是使用某一个列的信息进行分组 分组的标准为改组的相同的数字或者字符串为一组 比如同一个班级序号 同一个姓名 等等 having 则类似于where 是一个条件的判断 但是区别有两点
   2. having 和 where 的区别
      1. having是分组之后进行过滤 但是where是在分组前进行的过滤
      2. having后面可以使用聚合函数(比如sum(),max()等 但是where后面不能使用 一般使用like 之类进行模糊判断
      3. having不能单独出现 必须在分组之后(group by) 而where 可以单独出现

3. 分页查询
   select * from emp;

   1. 从0行开始取 一共取三行
      select * from emp limit 0,3;
      select * from emp limit 3; # 只写一个数字默认从第0行开始取
   2. 跳过第一个数据 一共取三行
      select * from emp limit 1,3;
      tips: 后面的一个数值可以堪称是取的最大行数 如果总共的行数并没有需要取的那么多 也不会报错 会显示目前拥有的所有行

4. 综合查询
   查询顺序: select from where group by having order by limit
   查询时代码执行顺序:
   from where group by having order by select limit
   1# 这里可以理解为
   from where group by having order by limit select

二 约束

约束类型可以分为以下:
主键约束 [primary key]
唯一约束[unique]
自动增长列[auto_increment]

1. 主键约束
   特点: 数据唯一 并且不能为null
   主关键字可以是表中的一个字段或者多个字段 它的值用来唯一标识表中的一条记录
   tips:

   1. 在设置主键的时候 如果使用本方式:
      create table user(id int primary key, name varchar(30));
      如果设置第二个主键 与id同样的方式在后面添加primary key 就会报错 如果需要设置两个主键 可以这样设置
      create table user(id int,name varchar(30),primary key(id,name));
   2. 由于主键有唯一性(即不能加入主键相同的两个数据) 如果使用两个主键的时候 如果插入的时候同时满足两个主键均相同 则会无法加入 否则可以加入 比如打个比方 id和name 设置为主键 如果插入的数据的id 和name 与表内的某一个均相同 则会导致无法加入并报错 如果其中id与某一个数据相同 但是name却不相同 则可以加入 不会报错

2. 唯一约束 [unique]
   作用: 在非主键中不能输入重复的值
   tips: primary key 和 unique之间的区别

   1. 两者香调的都是唯一性
   2. 在同一个表中 一般只设置一个primary key, 但是可以设置很多个unique(当然 primary key 也可以设置很多个,但是我们一般只设置一个主键)
   3. primary key 是不允许为null的 但是unique是可以的

3. 自动增长列 [auto_increment]
   给主键设置自动增长性 只能是整数类型 每行自动增长1
   场景: 一般添加给主键
   如: create table user(id int primary key auto_increment,name varchar(30));

4. 非空约束 [not null]
   如果某个字段设置为not null, 插入数据的时候该字段一旦出现null 则会自己报错
   如: create table user(id int primary key auto_increment,name varchar(30) unique not null);
   这里设置的时候name的字段1. unique 所以唯一 不能有重复 有重复则会报错 2. not null 表示 name不能为null 如果为null则会立马报错

5. 默认值约束[default]
   默认值的设置顾名思义 如果是没有输入 则会默认为default设置的值 如果是有输入 则会插入主动输入的值
   如: create table user(id int primary key auto_increment,name varchar(30) unique not null, adress varchar(30) default ‘Beijing’);
   这里设置adress的默认值为Beijing 如果用户输入时未输入值 则会默认输入Beijing 而非null

6. 外键约束[foreign key]
   注意 添加外键的时候首先必须有主键 主键和外键的类型必须保持一致

#创建表
#学生表
mysql> create table student(
 -> 		stuid varchar(10) primary key,
 -> 		stuname varchar(50)
 -> );

#成绩表
mysql> create table score(
	-> 		stuid varchar(10),
 -> 		score int,
 -> 		courseid int
 -> );

#插入数据
insert into student values('1001','zhangsan');
insert into student values('1002','xiaoming');
insert into student values('1003','jack');
insert into student values('1004','tom');

insert into score values('1001',98,1);
insert into score values('1002',95,1);
insert into score values('1003',67,2);
insert into score values('1004',83,2);
insert into score values('1004',70,1);

1. 两种方式可以建立外链:
   1. 方式1: 在创建第二个表的时候进行关联
      create table score(score int, courseid int, stuid varchar(10),constraint stu_sco foreign key(stuid) references student(stuid) );
   2. 方式2: 在创建了表格之后进行关联
      alter table score add constraint stu_sco foreign key(stuid) references student(stuid);
      注意 主键和外键的类型必须保持一致

三 多表查询

1. 表与表之间的关系

   1. 一对一 通过外联一对一
   2. 一对多 添加外链 实际上和一对一操作相似
   3. 多对多 单独创建一张新的表格 对应到两个不同的表格中
      什么叫多对多: 比如我们用户表格和电影表格 我们可以看电影1 电影2 电影3 … 第二个用户也可以 而电影也分别给我们用户1 用户2 … 看 这就是典型的多对多类型

2. 合并 union
   两个操作:

   1. union 会去除重复的值 不一样的数据不会去除 比如 a=10 和a=20 就不会当作重复被去掉
      select * from A union select * from B;
   2. union all 直接所有值相加 并且不会去除完全相同的值
      select * from A union all select * from B;

   这个是建立在表A 和 表B 字段完全相同的基础之上 如果不相同 可以使用类似于这样的方法
   select * from A union select name,score from C;