6.13数据库汇总

 

在学校学数据库有些囫囵吞枣从，学的很不精进，只知道一些基础的增删改查语句，多表联系的我还不懂，因此我打算重新学习，下面将我每天做的笔记一点一点发出。

DDL

约束

DML数据库操作

有一个函数now()，此函数是获取系统当前时间的。

insert into emp (username,password,creat_time,update_time) values ('wujing','123456',now(),now());

 

解释

 

DQL查询操作

语法

	select
	字段列表
	from
	表名列表
	where
	条件列表
	group by
	分组字段列表
	having
	分组后条件列表
	order by
	排序字段列表
	limit
	分页参数

 

解释

 

基本查询

条件查询的逻辑运算符

查询姓名为两个字的员工

select * from emp where name like '__';//两个占位符

 

解释

 

查询姓郭的员工

select * from emp where name like '张%';

 

解释

 

分组查询

聚合函数 不对null值进行运算

介绍：将一列数据作为一个整体，进行纵向计算

语法：

select 聚合函数（字段列表） from 表名;

 

解释

 

函数：

函数	功能
count	统计数量
最大值
min	最小值
平均值
sum	求和

分组查询返回的是分组字段和聚合函数

1.根据性别分组，统计男生女生数量--count（*）

select gender,count(*) from emp group by gender;

 

解释

 

2.先查询入职时间在‘2015-01-01’以前的员工，并对结果进行职位分组，获取员工数量大于2的职位

	select job,count(*) from emp where entrydata <= '2015-01-01'
	group by job
	having count(*) >=2;

 

解释

 

排序查询

oeder by 字段1 排序方式,字段二 排序方式……

 

解释

 

方式：ASC 升序（默认值）

​ DESC 降序

分页查询

limit 起始索引,查询记录数

 

解释

 

例子

	//查询第一页数据 5条
	select * from emp limit 0,5;
	//查询第二页数据 5条
	select * from emp limit 5,5;

 

解释

 

起始索引=（页码-1）*每页展示记录数

as 别名 中as可以省略，进行起别名的操作

处理(流程控制函数)

SQL中的if语句

if(条件表达式，true的取值，false的取值)

如果需要查询员工表中的性别信息统计，但是表中记录的是1：男性，2：女性，可以直接在后台sql进行处理

	select if(gender=1,'男性员工','女性员工'),count(*) from emp group by gender;
	还可以取个别名
	select if(gender=1,'男性员工','女性员工') 职位,count(*) from emp group by gender;

 

解释

 

SQL中的case语句

case 表达式 when 值1 then 结果1 when 值2 then 结果2 .. . else ...end；

完成对职位的信息统计 1：班主任 2：讲师 3：学工主管 4：教研主管

	select
	case job when 1 then '班主任' when 2 then '教师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end,
	count(*)
	from emp group by job;
	还可以取个别名
	select
	(case job when 1 then '班主任' when 2 then '教师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位,
	count(*)
	from emp group by job;

 

解释

 

多表操作（实际开发推荐，平常上课做的实验不这样做也行）

多表设计

1.一对多（多对一）

2.多对多

3.一对一

一对多--外键

创建表的时候

	create table 表名(
	[constraint] [外键名称] foreign key(外键字段名) references 主表 (字段名)
	);

 

解释

 

建完表后添加外键

alter 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名)

 

解释

 

此外键是物理外键，缺点影响增删改查的效率，仅适用于单节点数据库，不使用分布式、集群场景，引发数据库的死锁问题

一对一是特殊的一对多

多对多

案例：学生与课程的关系

关系：一个学生可以选择多门课程，一门课程也可以让多名学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键

多表查询

表用逗号隔开，查询结果相当于笛卡尔积，可以通过增加条件来消除无效的结果

select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

 

解释

 

连接查询

内连接：相当于查询交集部分的数据

外连接：

​ 左外链接：查询左表数据

​ 右外链接：查询右表数据

内连接

语法

	//隐式内链接
	select 字段列表 from 表一，表二 where 条件;
	//显式内连接
	select 字段列表 from 表一 [inner] join 表二 on 连接条件;

 

解释

 

例子

	# 查询员工姓名及其所属部门，隐式（经常）
	select tb_emp.name,tb_dept.name
	from tb_dept,tb_emp where tb_emp.dept_id=tb_dept.id;
	# 查询员工姓名及其所属部门，显式
	select tb_emp.name,tb_dept.name
	from tb_emp inner join tb_dept on tb_emp.dept_id=tb_dept.id;

 

解释

 

外连接

	左外连接：
	select 字段列表 from 表一 left [outer] join 表二 on 连接条件;
	右外连接：
	select 字段列表 from 表一 right [outer] join 表二 on 连接条件;

 

解释

 

	select tb_emp.name,tb_dept.name
	from tb_emp left join tb_dept on tb_emp.dept_id=tb_dept.id;
	select tb_emp.name,tb_dept.name
	from tb_dept right join tb_emp on tb_emp.dept_id=tb_dept.id;

 

解释

 

子查询

SQL语句中嵌套select语句，称为嵌套查询或者子查询，其外部语句可以是select/delete/insert/update等，最常用的外部语句是select

分类

标量子查询	子查询返回的结果为单个值
列子查询	子查询返回的值为一列
行子查询	子查询返回的值为一行
表子查询	子查询返回的值为多行多列

标量子查询

	# 标量子查询
	# 查询教研部的所有员工信息
	select * from tb_emp where dept_id=(select id from tb_dept where name ='教研部');
	# 查询在方东白入职之后的员工信息
	select *
	from tb_emp where entrydate > (select entrydate from tb_emp where name ='方东白');

 

解释

 

列子查询

	#列子查询
	# 查询教研部和咨询部的所有员工信息
	select *
	from tb_emp where dept_id in (select id from tb_dept where name='教研部' or name='咨询部');

 

解释

 

行子查询

	#行子查询
	#常见的操作符：=,<>,in,not in
	# 查询与韦一笑的入职日期及职位都相同的员工信息
	select *
	from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑');

 

解释

 

表子查询

	# 表子查询
	# 查询入职日期是2006-01-01之后的员工信息，及部门名称
	# 1.查询入职日期2006-01-01之后的员工信息
	select * from tb_emp where entrydate >'2006-01-01';
	# 2.查询这部分员工信息和部门
	select e.*,d.name from (select * from tb_emp where entrydate >'2006-01-01') e,tb_dept d where e.dept_id=d.id;

 

解释

 

事务

开启事务：start transaction； / begin；

提交事务：commit；

回滚事务：rollback；

索引

索引****是帮助数据库高校获取数据的数据结构**

语法：

	#创建索引
	create [unique] index 索引名 on 表名(字段名，...)
	#查看索引
	show index from 表名；
	#删除索引
	drop index 索引名 on 表名；

 

解释

 

注意事项：

主键字段，在建表的时候，会自动创建主键索引

添加唯一约束时，数据库实际上会添加唯一索引