一、数据库的基础知识

1.1数据库作用

数据库（database） 保存有组织的数据的容器 

1
2
1.实现数据的完整化
2.便于操作和管理

人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，确切地说，数据库软件应称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。数据库可以是保存在硬设备上的文件，但也可以不是。在很大程度上说，数据库究竟是文件还是别的什么东西并不重要，因为你并不直接访问数据库；你使用的是DBMS，它替你访问数据库

1.2DB、DBMS、SQL关系

1
2
3
4
1.数据库 (Database):存储数据的仓库，保存一系列有组织的数据
2.数据库管理系统（Database Management System）：数据库是通过DBMS创建和操作的，常见的数据库管理系统有MySQL、Oracle、DB2、SqlServer
3.结构化查询语言（Structure Query Language）:专门用来和数据库通信的语言，大部分DBMS都支持SQL
			 DBA    —————SQL语言—————     DBMS    —————SQL语言———————    DB

数据库管理员（Database Administrator，简称DBA）

1.3数据库存储数据的特点

1
2
3
4
5
1.数据先放进表里，表再放进数据库
2.一个数据库可能有多个表，每个表有唯一名字，用来标识自己
3.表有一些特性，这些特性定义数据如何在表中存储，类似Java中的类
4.表由列组成，我们也成为字段，所有的表都是由一个或多个列组成，每一列类似JAVA中的属性
5.表中的数据按行存储的，每一行类似java中的对象

1.4MySQL特点

1
成本低，开源、免费、性能高、简单安装和使用

1.5DBMS分为两类

1
2
1.基于共享文件系统的DBMS(微软的Access)   不需要安装服务端
2.基于客户机/服务器（C/S）的DBMS（MySQL、Oracle、SqlServer）：需要安装客户端和服务端，数据存储在服务端，一般指安装服务端。

1.6MySQL服务的启动和停止

1
2
1.手动到我的电脑-管理-服务和应用程序-MySQL57停止。
2.cmd以管理员身份运行。	net start/stop mysql57

1.7MySQL服务的登录和退出

1
2
3
4
5
MySQL服务必须开启！
1.可以用软件自带的客户端进去，但是只能自己访问（不推荐）
2.可以用命令行	mysql （-h localhost -P 3306） -u root -proot
				host:主机名称	P:端口号 	password后边不能有空格，其他的可以有
退出：exit/Ctrl+c

如果不行应该是没配置环境变量，path里边加入MySQL安装路径，到bin目录

1.8 MySQL的命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
查看所有数据库:	  show databases;		
打开指定库:			use test; 
查看当前库的所有表:	 show tables;  
看其他库的所有表 	  show tables from mysql;
查看当前位置:		   select database();		
看表的结构：		 desc 表名字
查看表的所有索引	show index from 表名
查看数据库版本：select version();		退出去之后，msaql --version

看数据：	 	 select * from sss;
插入数据：    	 insert into sss (id,name) values(2,'rose');
修改				update sss set name='llliiii' where id=1;
删除				delete form sss where id=1;
建表：		   		 creat table +表名字（列名 列类型，列名 列类型）		
					例：creat table sss(id int, name varchar(20));

1.9语法规范

1
2
3
4
5
6
7
8
9
10
11
1.不区分大小写，建议关键字大些，表名、列名小写
2.每条命令分号结尾
3.命令比较长，可以缩进换行
		建议	 SELECT
			  *
			  FROM
			  sss
4.注释
	单行注释：#注释文字
	单行注释：--注释文字
	多行注释：/*文字*/

1.10MySQL图形化客户端

1
2
root@locallhost  表示通过root链接到localhost
询问保存的文件的格式是 .sql

MySQL库内容

1
2
3
4
5
6
+--------------------+
| information_schema |	原数据信息
| mysql              |	保存用户信息
| performance_schema |	收集性能信息参数
| sys                |	测试数据，空的，可以建表，删除其他三个不要动 
+--------------------+

1.11主键

主键（primary key） 一列（或一组列），其值能够唯一区分表中每个行

唯一标识表中每行的这个列（或这组列）称为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行

表中的任何列都可以作为主键，只要它满足以下条件：

1
2
1.任意两行都不具有相同的主键值；
2.每个行都必须具有一个主键值（主键列不允许NULL值）。  
1
2
3
4
主键的最好习惯 除MySQL强制实施的规则外，应该坚持的几个普遍认可的最好习惯为：
1.不更新主键列中的值；
2.不重用主键列的值；
3.不在主键列中使用可能会更改的值。（例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。）

二、DQL语言

(Data Query Language 数据查询语言) 用于查询数据

2.1 select查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
	1.查询字段：
		select 查询列表（逗号隔开）
		from	表名；	
											select * from sss     *代表所有的字段  
	2.查询常量值：
		select 100；
	3.查询表达式：
		select 100*9；
	4.查询函数：
		select VERSION();
		
limit：限制条数，放在最后
		limit 5 ，只输出5行 ， linit 5,5  ，从第6行开始输出5行（limit从0开始）
distinct：去重
		查询去重： SELECT DISTINCT 查询列表 from sss；
		
特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格

2.2 起别名

起别名==单引号双引号都行==,建议双引号,而且有特殊符号必须要加引号，并且可以解决查询字段有重名的情况，对于别名中冲突关键字的用双引号（AS） 或者省略

1
2
SELECT 100*99 AS 结果；			
select last_name AS 姓 from sss

2.3 concat连接

1
2
3
4
5
6
7
连接字段 CONCAT('A','B') AS  结果	
	如果是null+任意，结果都是null
	concat 可以有ifnull
	mysql的+只做加法运算功能
	如果是字符+数字，字符能转换成整数的，转换成整数加，不能则是0+数字。

判断空  IFNULL（要判断的字段，要取代的值）
1
2
3
4
5
1.要先打开库  USE myemployees
2.对与不想成为关键字的要用~~抱住，着重号
3.执行的时候，选中谁执行谁
4.F12格式化
5.单引号代表字符，对于别名中冲突关键字的用双引号

2.4条件查询

2.4.1 where筛选
1
2
3
4
5
6
7
语法
	select 查询列表     执行顺序： 3	
	from   表明				  1
	where  	筛选条件；			2
	
条件运算符筛选：>    <   =     !=  <>不等     >=  <=  
逻辑表达式筛选： &&  || ！    推荐  and  or not
2.4.2 模糊查询like
1
2
3
4
5
6
7
like
	WHERE  last_name LIKE '%a%'；包含a的字段
	like一般和通配符使用
	%通配符 任意多个字符，包含0个字符
	_通配符 任意单个字符  可以多个连用  比如    '_ _ n _ 1'没有空格
	转义字符：  	如何像查询% 或者_的字段，用转移字符 加\     如 \%
	  		    推荐用WHERE  last_name LIKE '_$_%' ESCAPE '$'，指用任意的$代表转义字符

==ESCAPE ‘$’== 必须% _ 这样的才可以escape 其他 一律\

2.4.3 between and筛选
1
2
3
4
5
6
7
8
between and 语法
	select 
		* 
	from 
	where  ...  between  100 and 200    等价于 >=100  and <=200
1.包含临界值
2.提高简洁
3.不能换顺序
2.4.4 in筛选
1
2
3
4
5
6
7
in 语法
	判断是否属于
	select 
		* 
	from 
	where  ...  in('100','200')    等价于 =100  and =200
2.4.5 ifnull（a,9）判断空
1
select  ifnull(asss,0)  如果ASSS是null，则为0
2.4.6 is null和 isnull（a）
1
2
3
4
5
6
is (not) null 语法     不能直接whrere  ...=null
	select 
		* 
	from 
	where  ...  is null    判断是否为null
isnull(dddd) 判断是否null  是dddd = 1，否0
1
2
3
4
<=>安全等于
	=或者!= 或者<> 不能判断null	
	安全等于既可以判断是否等于，也可以判断是否null，但可读性低
is null则只能判断null，可读性较高

2.5 排序查询order by

1
2
3
4
5
6
7
select 			3	
from			1
whrer			2
order by 排序列表【asc|desc】  不写默认是升序		4
          排序列表可以是函数，别名，表达式，也可以是多个排序方式 
        多个排序： ordey by aaa ASC ， bbb DESC；
       order by 一般在最后面，除了Linit子句

2.6 常见函数

类似JAVA中的方法，隐藏了实现细节，提高代码重用性

1
2
3
4
5
6
7
调用方式：
	select 函数名（实参列表） 【from表】;
分类
	1.单行函数    返回一个值
		如concat length ifnull;  
	2.分组函数（统计使用，又称统计函数，聚合函数，组函数）
		返回一组值
2.6.1 单行函数
1.字符函数

① length函数 获取参数值字节个数

1
length('ddd')   3	  length('啊啊')   6

② 显示所用字符集 

1
show variables like '%char%'

UTF-8 汉字3个字节 GBK 汉字2个字节

③ concat函数 拼接字符串

1
select concat(last_name,'_',first_name) 姓名 from employees;

④upper、lower函数 变大写小写

1
SELECT UPPER('asdf')

⑤ substr\substring 截取字符串 sql中索引都是从1开始

1
2
substr('asdffd',4);	截取第四个（包括）后边字符
substr('asdffd',1,2); 截取第一个(包括)后边2个字符(字符,不是字节)

⑥ instr 函数 返回子字符串出现的第一次所以，如果找不到，返回0

1
instr('acccc','cccc');    2

⑦ trim 函数 去空格/其他的东西

1
2
trim('   cc   ')    cc
trim('a' from 'aa啊aa卡aa' )  啊aa卡   去前后，不去中间

⑦ lpad 函数 用指定的字符左填充到指定长度，超过右边截断

1
2
lpad('aab',2,'*')   aa
lpad('ac',9,'*')  *******ac

⑨ rpad 函数 用指定的字符右填充到指定长度，超过左边截断

⑩ replace 函数 替换

1
replace('啊我啊我嗷呜','啊','1')  1我1我嗷呜

⑩count（subject） 函数，统计个数多少个subject

1
count(1) ,  count(*)
2.数学函数

① round函数 四舍五入

1
2
round(-1.55)   -2
round(1.456,2)  1.46

② ceil 函数 向上取整

1
ceil(-1.02) -1

③ floor 向x取整

④ truncate 直接截断 

1
truncate(1.699999,1)  1

⑤ mod 取余数 和被除数符号一致

1
2
mod(10，-3) 1        被除数为正，余数为正，被除数负数，余数为负     
				    算法mod(a,b)  a-a/b*b;
3.日期函数

① now 返回现在的日期+时间

② curdate 返回当前系统日期，不包含时间

③ curtime 返回当前时间，不包含日期

④ 可以获取指定的部分，年月日，时分秒
year month(monthname英文名) day hour minuit second 

1
select year(now()) as 年

⑤ datediff 返回两个日期相差的天数

1
SELECT DATEDIFF(NOW(),'2021-09-04');

⑥ str_to_date 将字符转换成指定格式的日期

1
str_to_date('9-13-1999','%m-%d-%Y')

⑦ date_format 将日期转换成字符

1
2
3
4
5
6
7
8
9
10
date_format('2018/6/6','%Y年%m月%d日')  2018年6月6日
		%Y   四位年份
		%y 	 2位年份
		%m	月份（01，02）
		%c  月份（1，2）
		%d   日（01，02）
		%H 	小时（24小时）
		%h	12小时
		%i	分钟（00，01）
		%s  秒（00.01）
4.其他函数

①version（） 版本

②database() 当前位置

③user() 查询当前用户

④password(‘字符’) 加密sql8.0之后弃用

​ MD5(字符) 返回字符MD5加密的结果

5.流程控制函数

① if函数 if（10>5,1,2） 1 三运运算符

② case函数 可以无else ，不能无end

1
2
3
4
5
6
7
8
9
10
11
12
	case 要判断的量
	when 常量1  then   ...
	when 常量3  then   ...
	when 常量2  then   ...
	else   ...
	end 
或者
	case
		when  条件1   then   ...
		when  条件2   then   ...
		else   ....
		end
2.6.2 分组函数

功能：统计使用，又称为聚合函数或者统计函数、组函数 有 sum、avg、max、min、count

sun（工资） 求的是一列的工资总和 count 统计的是非空的个数

①参数类型

1
2
avg,sum,一般就用数值型，
max，min，count可任何类型，比如名字排序，日期

②是否忽略null 

1
2
null + 任何==null
sum avg max,min count  全部忽略null

④都可以和distinct搭配 

1
sum（distinct  salary）

⑤count函数

1
2
3
4
 	count(*) 统计所有行        count(1)    加了一列1 统计1的个数，即统计行
效率
    	MYISAM存储引擎下，COUNT(*)效率高   以前的存储引擎
    	INNODB存储引擎下，count(*)和count(1)差不多，比count(字段)要高

2.7 分组查询 group by

和分组函数一同查询的字段，要求是group by后出现的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
分组查询格式:
 select     分组函数，分组后的字段		  5
 from       表						1		
【where     筛选条件】			     2		
 group by    分组的字段				  3
【having      分组后筛选】				4	
【order by    排序列表】			     6
 
 group by  也可以是函数表达式.
 group by  可以是多个字段分组,逗号隔开

 分组查询
 分组前筛选      原始表      group by 子句前边，  whrer
 分组后筛选		分组后的结果集		 group by 子句后边   having
1 分组函数做条件放在having子句中
2 能用分组前筛选就用分组前where

比如查询有奖金的每个领导手下的最高工资 这里领导要分组

1
2
3
select max(工资),领导部门
from  员工
group by 领导部门

2.8 连接查询

多表查询时,查询的字段来自多个表时,会用到连接查询.

如果还是直接查询,会发生笛卡尔乘积,结果n*m行

如何解决：添加有效的连接条件

1
2
3
4
5
6
按年代分类,有sql92标准   仅支持内连接  也支持一部分外连接（用于oracle,sqlserver,mysql不支持）
		   sql99标准	内连接,外连接(左外，右外，全外（mysql不支持全外）),交叉连接
按功能
    内连接:  等值连接,非等值连接,自连接
    外连接:  左外连接,右外连接,全外连接
    交叉连接
2.8.1sql92标准
1.等值连接:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
格式
        select  查询列表
        from 表1 别名，表2 别名
        where 表1.key = 表2.key
        【and 筛选条件】
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
 		
 注意可以为表起别名，但是起别名之后原名不能在使用了.
 			两个表的顺序可以调换
 			可以加筛选  用and
 			可以加分组
 			可以加排序
 			可以多表连接
 	多表等值连接的结果为多表的交集
 	n个表连接,需要至少N-1个条件
 	多表的顺序无要求
 	可以搭配排序,分组,筛选使用
2.非等值连接
1
2
3
4
5
6
7
8
格式
        select  查询列表
        from 表1 别名，表2 别名
        where 非等值连接条件             例如 between and  
        【and 筛选条件】
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
3.自连接
1
2
3
4
5
6
7
8
格式
        select  查询列表
        from 表 别名1，表 别名2		同一个表
        where 等值连接条件             一般是   
        【and 筛选条件】
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
1
2
3
4
比如查询员工的上级,是根据员工的上级ID来匹配新的员工表,一张表当成两张用
           select e.employee_id,e.last_name,  m.employee_id,m.last_name
           from employees e , employees m
           where e.manager_id = m.employee_id
2.8.2 sql99语法

内连接、外连接（左外、右外、全外(mysql不支持)）、交叉连接

1.内连接inner
1
2
3
4
5
6
7
8
9
inner（可以省略）			查询两表共有的    交集
格式
        select  查询列表						
        from 表A									
        【inner】join 表2  on 连接条件						n个表连接,需要至少N-1个条件
        【where  筛选条件】
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
2.左外连接left
1
2
3
4
5
6
7
8
格式							查询的是A的内容,包括a和b的交集	
        select  查询列表					
        from 表A						A是主表，从表不匹配显示Null
        left join 表B  on 连接条件				a.key=b.key
        【where  筛选条件】			要查询A但不含交集，where b.key is null
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
3.右外链接
1
2
3
4
5
6
7
8
格式							查询的是B的内容
        select  查询列表			
        from 表A						B是主表
        right join 表B  on 连接条件			a.key=b.key
        【where  筛选条件】				要查询B但不含交集，where a.key is null
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】

特点：
1.外连接查询的结果为主表中所有的记录
2.如果表中没有和他匹配的，显示NULL
3.如果匹配则显示匹配的值。

4.全外连接

内连接的结果+第一个有第二个没有的+第二个有第一个没有的

1
2
3
4
5
6
7
8
格式							查询的是A+B的内容
        select  查询列表			
        from 表A						
        full join 表B  on 连接条件			a.key=b.key
        【where  筛选条件】				要查询A+B但不含交集，where a.key/b.key is null
        【group by 分组字段】
        【having 分组后的筛选】
        【order by 排序字段 】
5.交叉连接
1
2
3
4
5
6
7
select  查询列表
from 表A		
cross join 表B 
【where  筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段 】
2.8.3 sql92 和sql99对比
1
sql99支持较多，可读性较高，sql99实现连接条件和筛选条件的分离
2.9子查询
1
2
出现在其他语句中的select语句，称为子查询或内查询。
内部嵌套其他select语句的查询，成为主查询或者外查询。
2.9.1 子查询的分类

按子查询出现的位置分类：

1
2
3
4
5
6
7
8
			select后面                    仅支持标量子查询
	from后面		                支持表子查询
	where或having后面		       标量子查询  列子查询  行子查询
	exists（叫做相关子查询）后面    表子查询
          
         
  返回1 有，0无，不管几行几列，都可以，查询是不有值
表子查询

按结果集的行列数不同分类：

1
2
3
4
标量子查询（ 结果只有一行一列）    一般搭配着单行操作符使用 >  <  >=  <=  =  <>
列子查询（结果集只有一列多行）		一般搭配多行操作符使用， in amy/some all
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
2.9.2 子查询在where或having后面
1
2
3
4
5
6
标量子查询  列子查询  行子查询
特点：
1.子查询都会放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符使用    >   <   >=   <=   =   <>   
			标量子查询只要子查询不是一行一列，都是非法，没结果也是非法

1.多行子查询，返回多行，使用多行比较操作符

1
2
3
in/not in 		等于列表中的任意一个
any | some		和子查询返回的耨一个值比较
all     		和子查询返回的所有值比较

例子

1
2
3
4
5
6
7
8
9
10
11
12
1.查询location_id在1400-1700
            select distinct department_id
            from departments
            where location_id in(1400,1700);
 2.查询员工姓名，要求部门号是1中列表某一个
 		select lase_name
 		from employees
 		where department_id in(
 		            select distinct department_id
        		    from departments
           			where location_id in(1400,1700)
 		);

2.行子查询，多个字段查询（结果是一行多列，或者多行多列）不常用

1
2
3
4
5
6
7
8
例子：  查询工资最少并且销售额最高的员工信息

	select *
	from employees
	where (employee_id,salary)=(
		select min(employee_id),max(salary)
		from employees
	)
2.9.3子查询在select后面

仅仅支持标量子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
例如，查询每个部门的员工个数
select d.*,(
		select count(*)
		from employee e
		where e.department_id = d.department_id
		)  个数
from departments d;

也可以用左外连接
	SELECT d.*,IF(e.`employee_id`,COUNT(d.department_id),0)
	FROM departments d
	LEFT JOIN employees e ON d.`department_id` = e.department_id
	GROUP BY d.department_id;
注意if对空的判断，该操作是先把有部门的员工取出来，然后根据主表来分组，在计数，这个时候，因为分组之后count的计数，实际是对主表的每个部门的计数，因为每个部门都有记录，所以即使有的部门没有员工，但是由于count的是部门id，主表中一定有记录，但是该部门没有员工，所以会记录成1.
我们需要在这时判断没有有员工，没有直接给他清0
为什么不能where，是因为where直接过滤掉了部门没有员工的
2.9.4 子查询在from后面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
例如：查询每个部门的平均工资的工资等级
1.查询每个部门的平均工资
	select avg(salary),department_id
	from employees
	group by department_id
2.连接1的结果和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
	select ag_dep.*,g.grade_level
	from (
            select avg(salary),department_id
            from employees
            group by department_id
	) ag_dep									/*注意必须起别名*/
	inner join job_grades g 
	on ag_dep.ag between lowest_sal and highest_sal;
2.9.5 exists后面（相关子查询）
1
select exists(完成的查询语句)   结果1/0       先执行著查询，到了位置执行子查询
2.10 分页查询

​ 应用场景，对于显示数据，一页显示不全，需要分页提交sql请求

语法： 放在查询最后

1
2
3
4
5
6
7
8
9
select 查询列表					  7
from 表							1	
【jion type】 jion 表2				2
on  连接条件					  3
【where 筛选条件】				4
group by 分组字段			   	  5
having 分组后的筛选				6
order by 排序字段				  8
limit【offset】, size;	offset要显示条目的起始索引（从0索引开始） size显示条目个数

例如

1
2
显示前5条信息
	select * from employees limit 0,5       0可以省略

分页公式

1
linit (page-1)*size,size		page 要分的页数，  size 每页的条数
2.11 联合查询 union

应用场景：查询的结果来自多个表，但是多个表没有联系，而需要查询的信息是一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
语法：
        查询语句1
        union
        查询语句2
        union
        .....
        select
       select * from employees where email like '%a%' or department_id>90
====   select * from employees where email like '%a%'
	   union  from employees where  department_id>90
	   
1.将查询条件拆分出来，其查询目的都是一样，就是把筛选条件分开了
2.将不同表查询目的一样的联合起来，其表名和第一个表一致

注意：
	1.联合查询中多个联合的列数要一致
	2.union关键联合多个表时，会自动将一样的去重，如果不想去重，使用 union all	
	3.查询的字段名，默认是第一个列的名字

三、DML语言

数据操作语言DML

1
2
3
插入 insert
修改 update
删除 delete

3.1 插入语句 insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
方式一：
insert into 表名（列名，...）values(值1，...)；
1.插入的值的类型要与列的类型一致或兼容
2.不可以为null的列必须插入值，可以为null的值，可以不写列明插入
3.列的顺序可以调换
4.列数和值的个数必须一致
5.可以省略列明，默认是所有的列，都需要有值

方式二：
insert into 表明
set 列名=值，列名=值....

方式一可以插入多行，方式二不支持
方式一支持子查询，方式二不支持   可以把查询结果当插入值

3.2修改语句 update

1.修改单表的记录

1
2
3
4
语法
        update 表名1  别名
        set 列=新值，列=新值，....
        where 筛选条件

2.修改多表的记录

92语法

1
2
3
4
update 表1  别名，表2  别名
set  列=值，....
where 连接条件
and 筛选条件

==99语法==

1
2
3
4
5
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件

例子

1
2
3
4
5
修改张无忌女朋友的手机号为114
update boys bo
innner join beauty b on bo.id = b.boyfriend_id
set b.phone = 114
where bo.boyName = '张无忌'

3.3 删除语句 delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
方式一：
1.单表删除
delete from 表名 【where 筛选条件】   【limit  条目数】
2.多表删除
        92语法
            delete 别名
            from 表1，别名，表2，别名
            where 连接条件
            and 筛选条件；
		
		99语法
            delete 表1 别名 ，表2 别名		删除谁写谁，两个都删除都写
            from 表1 别名			
            inner|left|right join 表2 别名 on 连接条件
            where 筛选条件
		
方式二：truncate
truncate table 表名
truncate  也叫清空表，不能用where

两种方式对比
   1. truncate 效率高
   2. 要删除的表中有自增长列，再插入delete从断点开始，truncate从1开始
   3. delete 删除有返回值，truncate 无返回值
   4. truncate删除，不能回滚，delete可以回滚

四、DDL语言

数据定义语言 Data Define Language

4.1库的管理和操作：创建、修改、删除
1
2
3
创建：create
修改：alter   修改表的结构
删除：drop
4.1.1 库的创建
1
2
3
4
语法：
	create database 库名  【character set 字符集】；也可以加存储引擎等
	重复的话不能在创建了，会报错
	推荐使用  creat database （if not exists） 库名 【character set 字符集】；
4.1.2 库的修改（不建议）
1
2
3
4
5
rename database books to 新库名；（废弃了）	
要修改库名可以直接改文件夹名子  要关库改，之后重启库

更改字符集
alter database books character set gbk  ,默认utf-8
4.1.3 库的删除
1
2
drop database books 			不存在删除会报错
drop database if exists books   存在的时候删除
4.2 表的创建、修改、删除
1
2
3
创建：creat
修改：alter   
删除：drop
4.2.1 表的创建
1
2
3
4
5
6
7
8
语法：	
	create table (if not exists)表名(
		列名  类型的类型【（长度）约束】，
		列名  类型的类型【（长度）约束】，
		列名  类型的类型【（长度）约束】，
		....
		列名  类型的类型【（长度）约束】
	);
4.2.2 表的修改

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】

  1. 修改列名
1
alter table book(表名) change {column可省略} aa（旧列名） bb（新列名） datetime（类型要带上）
  1. 修改列的类型或约束
1
alter table 表名 modify column 列名 新类型(timestamp)
  1. 添加新列
1
alter table 表名 add column 列名 类型 【first|after 字段名】;  添加的位置可选
  1. 删除列
1
alter table 表名 drop column 列名；（不能判断是否存在，不能if exists）
  1. 修改表名
1
alter table 表名 rename to 新表名
4.2.3 表的删除
1
2
3
4
5
6
7
8
9
drop table 表名；
drop table if exists 表名；
通用的写法
删除库
drop database if exists 旧库名
create database 新库名
删除表
drop table if exists 旧表名
create tabke 表名（）;
4.2.4 表的复制
1
2
3
4
5
 create table 新表名 like  要复制的表名，  这个只复制结构，不复制值
 create table 新表名 select * from 要复制的表名    复制全部内容
 
1. 也可以只复制部分列    
2. 只复制某些表的结构，不要内容，可以加一个where 0
4.3 数据类型
4.3.1 数值型
  1. 整型
1
2
3
4
5
6
整型						字节
Tinyint						1	有符号-128~127  符号0-255
Smallint 					2	-32768~32767  0~65536
Mediumint					3	百万
Int\integer					4	十亿
Bigint						8

如何设置无符号和有符号 int unsigned 

1
2
3
4
5
1.不设置无符号还是有符号，默认有符号
2.插入值超出整型范围，会报out of range 异常，并且插入的是临界值
3.如果不设置长度，会有默认的长度 也可以设置长度， 长度由类型决定
4. int(7)7是显示宽度， 但是想要显示0，还需要用到 zerofill,
 		int(7) zerofill  左边填充0 ，并且自动成为无符号型
  1. 小数

浮点型

1
2
3
				   字节
float				4			
double				8

定点数类型 （精度更高）

1
2
3
定点数类型			字节					范围
DEC（M,D）			M+2		范围和double相同，给定decimal的有效取值范围由M和D决定
DECIMAL（M,D）

使用方法

1
2
3
4
5
6
7
8
9
10
11
12
13
1.浮点型
float(m,d)		M：整数部分+小数部分  ， D小数点后位数
double(m,d)			少的话，小数一定，整数99溢出
2.定点型			
dec(m,d)
decimal(m,d)			

M,D都可以省略，如果是decimal，则M默认是10，D默认0
如果是float 和double，则会根据插入的数值的精度来决定精度
定点型精度较高，如果要求插入数值的精度较高如货币运算等可以考虑使用

使用法则：
所选择的类型越简单越好，能保存的数值的类型越小越好
4.3.2 字符型
1
2
3
4
5
6
7
8
9
10
11
12
1.较短的文本
char
varchar
2.较长的文本
text
blob(较大的二进制)

char(M)	M可省，默认1  最多字符数，不是字节，a是一个字符    M为0-255
varchar(M)		M不可省略					   可变长度0-65535
区别：char的M代表固定长度字符，varchar M代表可变长度
		效率：      char高	      varchar低
		空间耗费	char耗费		varchar节省
4.3.3 位类型
1
2
binary 和varbinary
类似char和varchar ，他们包含二进制字符串，而不包含非二进制字符串
4.3.4 Enum类型
1
2
3
4
5
枚举类型，要求插入的值必须属于列表中指定的值之一
列表成员为1-255，需要1个字节存储，
列表成员为255-65535，需要两个字节存储
最多需要65535个成员
枚举不区分大小写，插入大小写都可以 ，可以插入一个枚举，也可以插入多个枚举
1
2
3
4
例如：
creat table tab_char(
	c1 enum('a','b','c')
)

set 用于保存集合 和枚举一样不区分大小写

1
2
3
4
5
6
7
例如：
creat table tab_set(
	c1 set('a','b','c')
)
insert into tab_set values('a');
insert into tab_set values('a','b');
insert into tab_set values('a','b','c');
4.3.5 日期型
1
2
3
4
5
6
7
8
9
10
	  		字节 		    最小值
date       	   4         1000-01-01      
datetime   	   8 		1000-01-01   00：00：00
timestamp	   4		19700101080001					2038年
time 		  3          -838:59:59
year           1   		1901   
show variables like 'time_zone'   显示时区

timestamp，记录时区，反应时区信息 ，收mysql版本和sqmode影响大
datetime    不受时区影响，只记录当地时区
4.4 常见约束

一种限制，用于限制表中的数据，为了保证表中的数据准确性和可靠性 比如姓名、学号

添加约束的时机

1
2
1.创建表的时候
2.修改表的时候

约束的分类

1
2
3
4
5
6
7
8
列级约束：六大约束都支持，外键约束没有效果
表级约束：除了非空、默认，其他的都支持

creat table 表名（
	字段名 字段类型 列级约束，
	字段名 字段类型 列级约束，
	表级约束
）

六大约束

1
2
3
4
5
6
7
1. not null :非空，保证该字段的值不能为空
2. default  :默认，用于保证该字段有默认值
3. primary key :主键，用于保证该字段的值具有唯一性，并且非空
4. unique   ：唯一，用于保证该字段的值具有唯一性，可以为空
5. check    : 检查约束【mysql中不支持，不报错但没效果】
6. foreign key： 外键约束，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值   不能作为列级约束，不生效

1.建表时添加列级约束

支持持：默认、非空、主键、唯一

1
2
3
4
5
6
7
8
9
10
11
12
13
create table stuinfo(
	id int primary key ,   #主键
	stuName vachar(20) not null，       非空
	gender char(1) check(gender = '男' or gender = '女')，  #检查
	seat int unique，       唯一
	age int default 18，    默认约束
	majorId int  references major(id)   外键     注意不能加foreign key ,
										  sql8.0也不支持references
	)
creat table major(
	id int primary key,
	majorName varchar(20)
	)

查看表的所有索引 show index from 表名 可以查看主键外键

  1. 建表时添加表级约束

【constraint 约束名】 约束类型（字段名） 不起名会有默认名字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table stuinfo(
	id int,
	stuname varchar(20),
	gender char(1),
	seat int,
	age int,
	majorid int,
	
	constraint pk primary key(id),    主键
	constraint uo primary key(id),	  唯一键
	constraint ck check(gender = ‘男’ or dender = ’女‘)   检查   没效果
	constraint ff freign key(majorid) references major(id)
)
主键就算改名子，他也是primary

通用的写法：默认、非空、主键、唯一列级约束，外键表级约束

1
2
3
4
5
6
7
8
9
10
11
12
create table if not exists stuinfo(
	id int primary key,				主键
	stuname varchar(20) not null,	非空
	gender char(1),					
	seat int unique,				唯一
	age int default 18,				默认
	majorid int,
	
	constraint ck_stuinfo_major foreign key (majorid) references major(id) 
)
可以一下设置多个
stuname varchar(20) not null unique,	非空 唯一

主键约束和唯一对比

1
2
3
4
5
			保证唯一性	 是否允许空		一个表中有几个    是否允许组合
主键			是			否				最多1个		是，不推荐
唯一			是			是				多个			是，不推荐

组合constraint pk primary key(id，stuname),  两个同同时一样才算一样

外键的特点

1
2
3
4
5
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3.主表中的关联列必须是一个key(一般是主键或唯一)
4.插入数据是时，应该先插入主表，在插入从表
删除数据时，先删除从表，在删除主表
  1. 修改表时添加约束
1
2
3
4
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
2.添加表级约束
alter table 表名 add 【constraint 约束名】约束类型（字段名）【外键的引用】；
1
2
3
4
5
6
7
8
9
10
11
12
1.添加非空约束
alter table 表名 modify column 列名 varchar(20) not null
2.添加默认约束
alter table stuinfo modify column age int default 18
3.添加主键
alter table stuinfo modify column id int primary key   列级约束
alter table stuinfo add  primary key(id)		表级约束
4.添加唯一键
alter table stuinfo modify column seat int unique   列级约束
alter table stuinfo add  unique(seat)		表级约束
5.添加Y键
alter table stuinfo add [constraint 新名字] foreign key(majorid) referencts major(id);
  1. 修改表时删除约束
1
2
3
4
5
6
7
8
9
10
1.删除非空约束
alter table 表名 modify column 列名 varchar(20)  null 
2.删除默认约束
alter table stuinfo modify column age int
3.删除主键
alter table stuinfo drop  primary key  
4.删除唯一键
alter table stuinfo drop index seat   列级约束
5.删除外键
alter table stuinfo drop  foreign key 新名字;     constraint起的新名字
1
2
3
			位置				支持的约束类型				是否能起约束名
列级约束	列的后面		语法都支持，但外键没有效果			否
表级约束	所有列的下面		默认和非空不支持，其他支持		可以，但是主键没效果
4.5标识列（自增长列）

可以不用手动的插入值，系统提供默认的序列值

1
2
用法：
id int unique auto_increment

特点

1
2
3
4
5
1.标识列不一定和主键搭配，但要求必须时一个key
2.一个表最多一个增长列
3.类型只能时数值型
4.标识列可以通过set auto_increment_increment = 2 ,设置步长
	可以通过手动插入值来设置初始值

修改表时设置表示列

1
2
3
4
添加标识列
alter table 表名 modify column 列名 int primary key auto_increment;
删除标识列
alter table 表名 modify column 列名 int primary key ;

五、TCL语言

5.1 事务控制语言（ transaction control language ）
1
2
有一个或者一组sql语句组成一个执行单元，这个执行单元要么全部执行，要不全部不执行。
在这个单元中，每个mysql语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果某单元中某条sql语句一旦执行失败或者产生错误，整个单元将会回滚，所有受到影响的数据将返回到事务开始以前的状态，如果单元中的所有sql语句均执行成功，则事务被顺利执行。

事务的ACID(acid)属性

1
2
3
4
5
6
7
8
9
1.原子性（Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2.一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态，就是事务执行后，仍然是一个整体
3.隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他的事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
4.持久性（Durability）
持久性是指 一个事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响，执行后不能回退

5.2 存储引擎
1
2
3
1.在mysql中的数据用各种不同的技术存储在文件（或内存）中
2.通过show engines 来查看mysql支持的存储引擎
3.在mysql中用的最多的存储引擎有：innode myisam memory 。其中innode支持事务，而myisam、memory 等不支持事务
5.3 事务的创建

隐式的事务：

1
2
3
隐式的事务：事务没有明显的开启和结束的标记
比如：insert、update、delete语句    一句就代表一个事务
delete from 表 where id = 1；

显式事务：事务具有明显的开启和结束的标记

比如两个insert语句要合成一个事务，但前提：必须先设置自动提交功能为禁用

1
2
SHOW VARIABLES LIKE 'autocommit'; 自动提交功能默认开启
set autocommit = 0;   只针对当前

开启事务步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1.先禁用自动提交功能
	set autocommit = 0;  
	start transaction;   开启事务，可以省略
2.编写事务sql语句（一般是select、insert、update、delete）
	语句1；
	语句2；
	.....
3.结束事务
	commit;提交事务
	rollback;回滚事务    
例如：
 	开启事务
        set autocommit = 0;  	
        start transaction;   开启事务，可以省略
    一组事务的语句
        update account set balance = 500 where user_name = "aaa"
        update account set balance = 1500 where user_name = "bbb"
     结束事务
     	commit/rollback;   结束/回滚
5.4 数据并发问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

1
2
3
1.脏读：对于两个事务t1,t2，t1读取了已经被t2更新但是还没有提交的字段之后，若t2回滚，t1读取的内容就是临时并且无效的
2.不可重复度：对于两个事务t1,t2，t1读取了一个字段，然后t2更新了该字段之后，t1再次读取同一个字段，值就不同了
3.幻读：对于两个事务t1,t2，t1从一个表中读取了一个字段，然后t2在该表中插入了一些新的行之后，如果t1再次读取同一个表，就会多出几行

==数据库事务的隔离性==：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题

一个事务与其他事务隔离的程度称为==隔离级别== 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱

数据库提供的4种隔离级别

1
2
3
4
5
6
7
8
隔离级别
read uncommitted 	允许事务读取未被其他事务提交的变更，脏读，不可重复和幻读的问题都会出现
（读未提交数据）
read commited   	只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻
（读已提交数据）   读问题仍可能出现
repeatable read		确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事物对这
（可重复读）		个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在
serializable		确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他的事务对该表执	（串行化）  	    行插入，更新和删除操作，所有并发问题都可以避免，但性能十分低下

Oracle支持的2种事务隔离级别：read commited，serializable。 Oracle默认的事务隔离级别为：read commited

mysql支持4种事务隔离级别，mysql默认的隔离级别：repeatable read 

1
2
3
4
5
6
mysql查看默认事务隔离级别
select @@tx_isolation    sql8.0用的是   select @@transaction_isolation 
更改事务隔离级别
set （session马上生效） transaction isolation level read uncommitted;
更改全局的隔离级别，重启生效
set global transaction isolation level read uncommitted;
5.5 savepoint的使用

需要搭配rollback使用

1
2
3
4
5
6
set autocommit = 0；
start transaction;
delete from account where id = 25;
savepoint a;    # 设置保存点
delete from account where id = 29;
rollback to a；  #回滚到保存点a

题

image-20210821155220641

conca可以用于like模糊判定中的连接来用

image-20210821155254544

image-20210821155316135

注意CASE的两种用法， CASE when 条件，then else end

​ case 判定的值 when 常量1 then

​ when 常量2 then else end

image-20210821161055512

注意CASE 的嵌套用法， end结尾

image-20210821161427422

排序，某种特定的东西需要放在最后或者前边，

image-20210821225751104