MsSQL学习(三)

MsSQL学习(三)

昨日回顾

字符编码与配置文件

查看关键字：\s

统一字符编码：uftmb4

存储引擎

概念：处理数据的不同方式

查看关键字：show engines;

引擎：MyISAM，InnoDB，BlackHole，Memory

指定存储：create table t1(id int)engine=引擎;

创建表的完整语法

create table 表名(
	字段名1 字段类型(数字) 约束条件,
	字段名1 字段类型(数字) 约束条件,
  字段名1 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以有多个 空格隔开即可
4.最后一个字段所在行的结尾不能加逗号

字段类型

整形

tinyint\smallint\int\bigint
表示存储的数字范围
默认需要使用一个比特位存储正负号，如果需要移除，需要加上unsigned

浮点型

float\double\decimal
存储的小数精确度不一样,正常使用float即可，高精度可以使用decimal

字符型

char\varchar  # 定长与变长

类型	优点	缺点	使用场景
char	速度快	空间浪费	规模较小，数据量相对固定的字典
varchar	节省空间	速度较慢	规模较大，数据交杂的数据

枚举与集合

enum  # 枚举 多选一
set  # 集合 多选一到多

日期类型

date\datetime\time\year

字段约束条件

插入数据的方式
	方式1:insert into t1 values();
	方式2:insert into t1(name,id) values();
  # 1.not null非空
	# 2.default默认值
	# 3.unique唯一值
  # 4.primary key主键
  # 5.auto_increment自增

今日学习内容

自增特性

create table t1(
	id int primary key auto_increment,
  # 创建表 t1 id int 主键 自动递增
  name varchar(32)
);

insert into t1(name) values('1'),('2'),('3');
insert into t1(name) values('4');  # id=4
delete from t1 where id=4;
# 删除掉id为4的数据
insert into t1(name) values('5');  # id=5
'''自增不会随着数据的删除而回退'''
delete from t1;  # 删除数据但无法重置主键
insert into t1(name) values('1'),('2'),('3');
truncate t1;  # 删除数据并重置主键值
insert into t1(name) values('1'),('2'),('3');

外键简介

定义：如果一个字段X在一张表（表一）中是主关键字，而在另外一张表（表二）中不是主关键字，则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

主键表和外键表的理解

（1）以公共关键字作主键的表为主键表（父表，主表）

（2）以公共关键字作外键的表为外键表（从表，外表）

外键关系

一和多定义

一：从表，外表，以公共关键字作外键的表为外键表

多：父表，主表，以公共关键字作主键的表为主键表

一对多

在多方表中,创建一个新的字段,作为当前表的外键,指向一方表的主键
注意点: 一对多, 外键设置在多方

外键关联QL语句

父表必须在从表建立之后才可以建立，因为父表需要从表的主键作为外键，而从表不存在那么外键也就不存在；在

创建表字段的时候也可以给每个字段添加中文注释
创建从表：
create table dep(
	id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

创建父表：
create table emp(
	id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep(id));
	# 最重要的一段代码：使用从表的主键


# foreign key的约束效果：
1.创建表的时候 应该先创建被关联表(没有外键字段的表)
2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
	外键字段填入的值只能是被关联表中已经存在的值
  （类似于变量名函数必须先定义后使用）
3.修改、删除被关联表数据都会出现障碍
	关联之后，关联的数据不能修改
  
# 级联更新级联删除

建立从表：
create table dep1(
	id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

建立父表：
create table emp1(
	id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep1(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
  # 将数据关联起来，更新和删除自动
);

# 表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系

多对多关系

针对多对多关系 需要单独开设第三张表专门存储关系.

从表1：  
  create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(10,2)
);
从表2：
	create table author(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others')
);

主表1：专门用来专门存储关系；
create table book2author(
   id int primary key auto_increment,
   author_id int,
   book_id int,
   foreign key(author_id) references author(id) 
   on update cascade   # 级联更新
   on delete cascade,  # 级联删除
   foreign key(book_id) references book(id) 
   on update cascade  # 级联更新
   on delete cascade  # 级联删除
);

# 针对多对多表关系 
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

一对一关系

双方唯一与对方产生对应关联的关系，叫做一对一关系

就好比如一个人的基础信息表格对应的详细信息表格，他们是同一个人的那么就是一对一关系；他们不是同一个人的就是没有关系；

一对一关系的外键字段建立在任意一方都可以，但是推荐建筑查询频率较高的一方：

从表：
create table UserDetail(
	id int primary key auto_increment,
  phone bigint,
  age int
);

主表：
create table User(
	id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
  user_detail_id int unique,  # 加unique是为了让表只关联一次
  foreign key(user_detail_id) references UserDetail(id) 
  on update cascade   # 级联更新
  on delete cascade,  # 级联删除
);

表查询关键字

查询关键字之select与from

select用于指定查询的字段
from用于指定查询的表
select id,name from mysql.user;

查询关键字之where筛选

# 1.查询id大于等于3 小于等于6的数据
  select * from emp where id>=3 and id<=6;
  select * from emp where id between 3 and 6;
  
# 2.查询薪资是20000或者18000或者17000的数据
	select * from emp where salary=20000 or salary=18000 or salary=17000;
 
# 3.查询id小于3或者大于6的数据
	select * from emp where id not between 3 and 6;
  
# 4.查询薪资不在20000,18000,17000的数据
	select * from emp where salary not in (20000,18000,17000);
  
# 5.查询岗位描述为空的数据
	select * from emp where post_comment=null;  # 不可以
	'''针对null只能用is不能用等号'''
	select * from emp where post_comment is null;  # 可以
  
# 6.查询员工姓名中包含字母o的员工姓名和薪资
	"""
	查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
	关键字 				
			like:开启模糊查询的关键字
	关键符号
			%:匹配任意个数的任意字符	
			_:匹配单个个数的任意字符
	"""
  select name,salary from emp where name like '%o%';
  
# 7.查询员工姓名是由四个字符组成的数据
	select * from emp where name like '____';
  select * from emp where char_length(name)=4;

查询关键字之group by分组

# 什么是分组?
	按照指定的条件将单个单个的个体组织成一个个整体
  eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
# 为什么需要分组?
	分组的好处在于可以快速统计出某些数据
  eg:最大薪资 平均年龄 最小年龄 总人数
# 如何分组
	'''按照部分分组'''
	select * from emp group by post;
  """
  mysql5.7及以上版本默认自带sql_mode=only_full_group_by
  该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
  
  原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
  
  如果是MySQL5.6及以下版本 需要自己手动添加
  """
# 聚合函数
	专门用于分组之后的数据统计
  	max				统计最大值
    min				统计最小值
    sum				统计求和
    count			统计计数
    avg				统计平均值
  
ps:是否需要分组 我们可以在题目或者需求中发现
# 1.统计每个部门的最高薪资
  select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
	select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
	select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
	select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
	select post,min(age) from emp group by post;
 
"""间接获取分组以外其他字段的数据"""
# 1.统计每个部门下所有员工的姓名
	select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
	select post,group_concat(name,age) from emp group by post;
  select post,group_concat(name,'|',age) from emp group by post;
"""字段起别名"""
	select post,group_concat(name) as '姓名' from emp group by post; 
  select id as '序号',name as '姓名' from emp;
  # as关键字也可以不写 但是语义不明确 建议加上
  select id '序号',name '姓名' from emp;

作业

1.判断表关系
	# 班级表
    cid	caption
    # 学生表
    sid sname gender class_id
    一对多关系
    # 老师表
    tid	tname
		多对多关系
    # 课程表
    cid	cname	teacher_id
    多对多关系
    # 成绩表
    sid	student_id course_id number
    一对一关系
2.分组练习题
  1. 查询岗位名以及岗位包含的所有员工名字
  2. 查询岗位名以及各岗位内包含的员工个数
  3. 查询公司内男员工和女员工的个数
  4. 查询岗位名以及各岗位的平均薪资
  5. 查询岗位名以及各岗位的最高薪资
  6. 查询岗位名以及各岗位的最低薪资
  7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

1. 查询岗位名以及岗位包含的所有员工名字

   select post,group_concat(name) from emp group by post;

2. 查询岗位名以及各岗位内包含的员工个数

select post,count(id) from emp group by post;

3. 查询公司内男员工和女员工的个数

select sex,count(id) from emp group by sex;

4. 查询岗位名以及各岗位的平均薪资

select post,avg(salary) from emp group by post;

5. 查询岗位名以及各岗位的最高薪资

select post,max(salary) from emp group by post;

6. 查询岗位名以及各岗位的最低薪资

select post,min(salary) from emp group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

select sex,avg(salary) from emp group by sex;