MySQL补充

mysql

DBMS,SQL,DBS三者之间的关系：

DBMS—>SQL---->DB,数据库管理系统通过执行sql语句管理数据库

1.数据库的基本查询：

mysql limit和offset用法

limit和offset用法

mysql里分页一般用limit来实现

1. select* from article LIMIT 1,3

   2.select * from article LIMIT 3 OFFSET 1

上面两种写法都表示取2,3,4三条条数据

当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如

select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

当 limit后面跟一个参数的时候，该参数表示要取的数据的数量

例如 select* from article LIMIT 3 表示直接取前三条数据，类似sqlserver里的top语法。

当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

limit是在order by之后执行的，分页公式：limit （pageNo-1）*pageSize，pageSize

mysql多表联查：

1.内连接：返回多个表中的公共数据

• 用法1：如果查询的字段不是公共字段就不会显示结果

  -- SQL92版本的内连接(等值连接)
  select * from employees,dept_emp where employees.emp_no=dept_emp.emp_no;
  

• 用法2：内连接，只有两表中含有公共字段的时候才会返回查询结果

  -- SQL99版本的内连接（等值连接）
  select * from employees join dept_emp on employees.emp_no=dept_emp.emp_no;
  select * from employees inner join dept_emp on employees.emp_no=dept_emp.emp_no;
  -- 非等值连接（就是连接的条件不是一个特定的值，而是一个范围）
  select * from employees inner join dept_emp on employees.sal between 1000 and 2000;
  

• 补充：如何实现三表联查：

  select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段;
  

2.左外连接：以左边表为标准，如果右边表中没有对应的记录那么就会以null显示：

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段;

3.右外连接以右边表为标准，如果左边表中没有对应的记录那么就会以null显示：

select * from 表1 right join 表2 表1.公共字段=表2.公共字段;

规则：如果在进行外连接的时候，就拿左外连接来说，如果右边表中没有与左边对应的数据，那么会以null显示，并且如果是用的公共字段进行连接，那么左外连接和右外连接查询出来的结果相同，只是顺序不一样而已。

自连接：就是在同一张表中，比如员工表中，包括所有员工的信息，也包括领导和主席，在员工表中，每一个员工对应着一个编号，每个员工都有一个领导编号，但是除了主席之外都有上级，这时候如果查询所有员工的上级的话，就应该考虑使用自连接，自连接的思想就是将一张表看成是两张表，一张作为员工表，另一张作为领导表，然后给这两张表起两个别名，然后就可以类似与内连接的形式查询一张表，从而达到两张表的效果，记住这个例子：员工的领导编号等于领导的员工编号

4.交叉连接：返回笛卡尔积

select * from 表1 cross join 表2;

5.自然连接：自动判断条件连接，判断依据是同名字段

• 自然内连接：

select * from 表1 natural join 表2;

• 自然左外连接：

  select * from 表1 natural left join 表2； 
  

• 自然右外连接：

  select * from 表1 natural right join 表2；
  

  规则：1.自然连接是通过表中的同名字段进行连接的

  ​ 2.如果没有同名字段则返回笛卡尔积

  ​ 3.同名的连接字段只显示一个并且放在表的最前面

  6.using用来指定连接字段

  select * from 表1 inner join 表2 using(公共字段);
  

  如果指定的字段是公共字段那么效果就相当于内连接，如果查询的字段不是公共字段，那么将会报错

关键字all，any，some的使用规则：

All：对所有数据都满足条件，整个条件才成立；
Any：只要有一条数据满足条件，整个条件成立；
Some的作用和Any一样 .

外连接和内连接的选取原则：

如果想要查询的多张表中没有主次之分，只是查询这多张表中关联的字段的数据，那么就考虑使用内连接

如果想要查询的多张表中有主次之分，并且是想要查询一张表的全部内容的顺带查询其他的表的时候，这时候应该考虑使用外连接

多表联查语法（大于两张表的情况）

-- 多表联查，大于两张表的情况
select ...
from a
join b
on  a和b关联的条件
join c
on  a和c关联的条件
join d
on  a和d关联的条件

where和having的区别

having子句可以让我们筛选成组后（需要和group by搭配使用）的各种数据，where子句先于聚合函数前筛选记录，也就是作用与having和group by子句前，而having作用于group by分组之后

SQL实例：

select emp_no，sum(salary*12) as 年薪 from emp group by emp_no having 年薪>200000;

上面的例子表示的就是根据emp_no分组之后，然后在分组之后的结果中查询年薪大于200000的了员工的编号

having和where的区别：

• 作用的对象不同，where子句作用域表和视图，having子句作用域组（group by 分组之后的结果）
• 作用时间不同。where在分组和聚合函数之前选取输入行（因此它控制哪些行进入聚合计算），having是在分组和聚合之后寻去分组的行。所以，where子句和having子句可以同时在一个sql语句中被使用，where子句不能包含聚合函数，而having子句总是跟着聚合函数一起使用。（但是having可以不跟聚合函数一起使用）
• 注意，group by可以单独使用，但是having必须在group by之后使用，不能单独使用

##2.数据库的删除：

• drop database  数据库名
  

##3.数据库中对表的修改：

• 重命名一张表：

  alter table 原表名 rename 新名
  alter table 原表名 rename to 新表名
  rename table 原名 to 新名
  

• 删除一张表：

  drop table 表名
  

• 对一列进行修改：

  • 修改列名：这种方式不仅可以修改列名，还能修改列的数据类型和约束

  alter table 表名 change 原列名  新列名 数据类型 约束
  

  • 新加一列：

    alter table 表名 add （column） 即将添加的列的名字  数据类型 约束
    --- 可以指定添加的列的顺序：
    --- 比如将新列添加到某一列之后：
    alter table 表名 add 新列名 数据类型 约束 after 某一列
    --- 也可以将新列放在表的第一列;
    alter table 表名 add 新列名 数据类型 约束 first
    

  • 快速复制数据库：

  • create table demo1 as select * from demo
    

  • 删除一列：

    alter table 表名 drop (column) 列名 
    

  • 修改列的数据类型

    alter table 表名 modify 列名  数据类型
    

  4.对表的内容进行修改：

  • ​ 修改某一列的内容：

    update 表名 set 列1=值1，列2=值2 where 条件
    

  • 删除某一行内容:

    delete from 表名 where 条件
    

##4.索引

4.1.什么是索引

1.概述：索引就相当于数组的下标，索引是在数据库上的字段上添加的，是为了提高查询效率而存在的一种机制，一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引，索引就相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

2.用法：

• 对一张表的某个列建立索引：

  • alter table 表名 add index 索引名(列名)
    create index 索引名 on 表名(列名)
    

  • 查看索引：

    show index from 表名
    

3.注意：

• select * from t_user where name='jack';
  

  以上sql语句会去name字段上进行扫描，因为查询的条件是：name=‘jack’，如果name字段上没有添加索引，或者说没有给name字段创建索引，mysql会全盘扫描，会将name字段上的每一个值都比对一遍，效率比较低

• 在mysql数据库中索引也是需要排序的，并且这个排序和treeset数据结构相同，TreeSet（TreeMap）底层是一个自平衡的二叉树，在mysql当中索引是一个

• 在任何数据当中主键和有unique约束的字段上都会默认添加索引对象

• 什么情况下我们需要考虑给字段添加索引呢？

  • 数据量庞大
  • 该字段经常出现在where后面，以条件的形式存在，也就是这个字段总是被扫描
  • 该字段很少的DML语句（insert，update，delete）操作，因为DML之后，索引会重新排序
  • 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能，通过主键查询，建议使用unique字段进行查询，这样效率反而是很高的

4.2.索引失效

1. 当where条件后面的条件使用的是模糊查询：select * from emp where ename like ‘%t’ ,这时候即使enam添加了索引，索引也会失效，因为首字母是以%开头的，不知道第一个字母具体表示的是什么

2. 使用or的时候会导致索引失效，当or两边的字段其中有一个字段没有添加索引的时候，就会导致索引失效

3. 使用复合索引的时候，在where子句中没有全部使用这些被复合索引修饰的字段的话，会导致索引失效

   1. create index emp_job_sal_index on  emp(job,sal);
      select * from emp where job='MANAGER';//因为这里没有使用全部被复合索引修饰的字段，只使用了job没使用sal
      

4. where子句中被索引修饰的字段参加了运算，索引失效

5. 在where子句中被索引修饰的字段使用了函数，索引失效

##5.视图

1.概述:视图是一张虚拟的表，是在原表的基础上查询出来用来展示给用户的一张表，但是要注意的是当原表的内容发生改变的时候

视图也会随之改变，用户可以在视图上查询数据，但是不能通过视图来修改原表的数据。

2.用法：

• 创建视图：

  create view 视图名（列1），（列2），（列3）as select 列1，列2，列3 from 表名
  

##6.导入文件

1，概述：可以将已经存在于特定位置的sql文件导入到数据库中

2.用法:

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;

注意这个文件必须存在才可以导入

7.导出：

1.概述：导出和导入是相反的过程，导出就是把数据库中的数据导出成文件

2.用法：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;

##8.备份：

1.概述：备份是将数据库中的数据包括数据的结构，约束，索引，视图都保存起来，而导出指的是将数据库中的数据保存起来。

2.用法：

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

##9.用户授权

9.1用户具有的权限：

• select
• insert
• delete
• update

10.约束

10.1.外键约束

外键约束就是一张表引用另一张表的某个字段，就是这两张表产生了关联，当被引用的表的这个字段发生改变的时候，引用这张表的相应字段就会随之改变，避免了字段被随意修改，并且这样可以减少数据的冗余

举例：现在创建两张表，一张是学生表，另一张是班级表，学生表引用班级表的班级编号字段

-- 创建学生表（子表）和班级表（）两张表
-- 删除表（先删子表，后删父表）
drop table if exists t_student;
drop table if exists t_class;
-- 创建这两张表（先创建父表，再创建子表）
create table t_class(
		cnumber int primary key auto_increment,
		cname varchar(255)
);
create table t_student(
		sno int primary key auto_increment,
		sname varchar(255),
		cno int,
		foreign key (cno) references t_class(cnumber)
);

-- 向表中插入数据
insert into t_class(cname) values('白河高级中学高二6班');
insert into t_class(cname) values('白河高级中学高三4班');
select * from t_class;
-- 注意这里的班级号不可以随便写，因为在t_class表中只插入了两个班级号，而在学生表中引用了班级表的内容，所以班级表的cno字段受到
-- t_class表的cnumber的约束，t_class中有什么，t_student的这个属性才能写什么
insert into t_student(sname,cno) values('刘凯武',1);
insert into t_student(sname,cno) values('邱克坤',1);
insert into t_student(sname,cno) values('曾凡菲',2);
insert into t_student(sname,cno) values('谢培成',2);
select * from t_student;

注意：t_class是父表

​ t_student是子表

删除表的顺序：先删子表，再删父表

删除数据的顺序：先删子，再删父

插入数据的顺序：先插入父，再插入子

注意：子表中的外键引用的父表中的某个字段，被引用的这个字段不一定必须是主键，但是至少具有unique约束

外界约束可以为null

11.sql语句的分类

1. DQL：数据查询语言（凡是带有select关键字的都是数据查询语言）
2. DML：数据操作语言（凡是对表中的数据进行增删改的都是数据操作语言）
   1. insert，update，delete
   2. 这个语言操作的是表中的数据
3. DDL：数据定义语言（凡是带有create，drop，alert的都是DDL），DDL主要操作的是表的结构，不是表中的数据
   1. create：新建，等同于增
   2. drop：删除
   3. alert：修改
4. TCL：事务控制语言：包括数据的提交（commit），事务回滚（rollback）
5. DCL：数据控制语言
   1. 授权（grant），撤销授权（revoke）

12.补充学习

导入提前写好的sql脚本

使用source+路径。。。.sql:使用的时候只需要将cmd窗口打开，然后，输入source，最后将保存好的那个sql脚本拖进来然后就可以执行了，但是需要注意的地方是路径中不能出现中文

12.1.DQL

• 查看数据库的版本：

  • select version（）

• 查看当前正在使用的数据库

  • select database( )

• 结束sql语句（\c），就和ctrl+c停止cmd窗口正在执行的命令一样，因为数据库是不减分号不结束，所以可以使用\c来结束当前命令的执行

• 在mysql中字符串可以使用双引号，但是在oracle中只能使用单引号，因此以后写sql语句的时候统一都使用单引号，在数据库中字符串都是单引号的，只是在mysql中允许使用双引号而已，但是双引号不标准，建议全部都使用双引号，在给表中的列起别名的时候，如果是英文字母的话可以直接写，如果是中文的话，使用单引号引起来

• 不等于的表示方式

  • <>或者！=

• between……and是闭区间

• 数据库中的null不可以使用等号进行衡量，因为在数据库中null表示的是什么也没有，只能使用is null和is not null

• and和or同时出现的时候，and的优先级较高，如果想要先执行or中的条件，可以用小括号括起来

• in表示包含，in可以看作是一个函数，里面可以传参，参数值可以是多个，表示的是包含在这些参数值里面的数据

  • 例：查询工作岗位是MANAGER和SALSMAN的员工
  • select * from emp where job in(‘MANAGER’，‘SALSMAN’)
  • 就等同于select * from emp where job=‘MANAGER’ or job=‘SALSMAN’;
  • 注意in表示的不是一个区间，而是包含在这里面的条件

• 模糊查询like

  • -- like表示模糊查询，支持%或下划线匹配
    -- %匹配任意多个字符
    -- 下划线匹配任意一个字符（%是特殊符号，_也是特殊符号）
    -- 查询名字中包含凯的学生
    select * from t_student where sname like '%凯%';
    -- 查询姓刘的学生
    select * from t_student where sname like '刘%';
    -- 查询名字以武结尾的学生
    select * from t_student where sname like '%武';
    -- 查询第姓名中第二个字母是u的球员
    select * from player where pname like '_u%';
    -- 查询姓名中包含_的球员，这里_是一个特殊符号，所以不能直接使用，需要用\进行转义
    select * from player where pname like '%\_%'; 
    

• 多个字段的排序问题

  • 例：将员工按照薪资升序排列，如果薪资相同的情况下，再按照姓名降序排列

  • select * from emp
    order by
    sal asc,ename desc;
    -- 上面这条语句是多个字段的排序问题，主导条件是第一个，当第一个相等也就是薪资相等的时候，才会去执行后面的按照姓名降序排列
    

• select语句的执行顺序

  1. from
  2. where
  3. select
  4. order by

• 处理函数

  • 单行处理函数，一个输入对应一个输出

    • lower

    • upper

    • substr（起始位置，终止位置，截取目标）注意：这里的起始位置是从1开始的

    • length：获取字符串长度

    • concat（str1，str2）：拼接字符串

    • trim：去除前后空格

    • round(小数,保留到第几位)：如果是0表示保留到小数点后0位，其实就是保留整数，如果是1表示保留小数点后1位，如果是-1表示保留到小数点后-1位，那其实就是十位，-2是百位……

    • rand：生成0-1之间的随机小数，和java一样

    • ifnull（值为null的变量，被当作什么来处理）：这句话的意思是如果一个变量值为null，那么这个值就会被当作什么来处理，因为在数据库中，null参与任何数学运算最终的值都是null，因此需要对null进行空处理

    • case……when……then……when……else……end

      • -- 当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%，其他正常
        select
        	ename,
        	job,
        	sal as oldsal
        	(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as       newsal
        	from 
        		emp;
        

• 多行处理函数(分组函数)：多个输入对应一个输出

  • 多行处理函数的特点：输入多行，输出一行
  • 5个
    • count：计数
    • sum：求和
    • avg：平均值
    • max：最大值
    • min：最小值
  • 注意：分组函数在是用的时候必须先进行分组，然后才可以使用，如果没有对数据进行分组，整张表默认为一张表
  • select sum（sal）as totalSal from emp;//这里虽然没有使用group by，但是默认是将整张表最为一张表进行分组的
  • 分组函数会自动忽略null，因此不需要使用ifnull函数
  • count（具体字段）和count（*）的区别
    • count（具体字段）：是统计表中具体字段值不为null的行数
    • count（*）：表示统计所有行数，只要这一行上面有一个数据，count都会加1
  • 分组函数不能直接在where子句中使用
  • 分组函数可以嵌套使用

• 分组查询：group by子句

  • 书写顺序：

    • select……
      from……
      where……
      group by……
      order by……
      

  • 执行顺序：

    • from...
      where...
      group by...
      select...
      order by...
      

  • 为什么分组函数不能直接用在where子句中呢？

    • 因为执行顺序的问题。因为分组函数在使用的时候必须先分组，也就是group by子句，但是group by是在where子句之后才执行的，在使用分组函数的时候，还没有进行分组。所以分组函数不能直接在where子句后面使用

  • 重点结论：在一条select语句中，如果有group by 语句的话，select后面只能跟：参加分组的字段以及分组函数，其他的一律都不能跟，因为如果跟上了别的字段，就没有意义，并且在别的数据库比如oracle中会出错

  • 分组查询后面可以根据多个字段进行分组，字段与字段之间用逗号隔开即可

• having 子句：可以对分组查询之后的结果再进行一遍筛选，但是需要注意的是having必须和group by子句联合使用，having跟在group by子句的后面，作用相当于where，但是不同点是where后面不能跟分组函数，但是having可以,where和having可以联合使用

• 单表查询总结

  • sql语句书写顺序

    • select ...
      from ...
      group by...
      having...
      order by...
      

  • sql语句的执行顺序

    • from
      where
      group by
      having 
      select 
      order by
      

• 去重查询：distinct

  • 注意：distinct只能用在所有字段的最前面，可以为所有字段去重，distinct也可以用在分组函数中

• 子查询：

  • 子查询的思路就是将select语句查询出来的结果作为一张新的表，然后再通过合并查出来最终结果，在select子句后面的select语句查询出来的结果只能有一条，不然会出错，这个很好理解，就是说select语句每次查出来的都是一条数据，如果所以select里面嵌套的select语句一次不可以查出来多条语句
  • 子查询可以用在select，from，where后面

• union完成结果集的拼接

  • union的效率更高一些，对于表连接的时候，每连接一次新表，则匹配的次数满足笛卡尔积，但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接

  • 例：

    -- a连接b，b连接c，a，b，c分别有10条记录
    -- a连接b一个结果：10*10=100次
    -- a连接c一个结果：10*10=100次
    -- 总次数是100*100=10000次
    --但是使用union的话，只需要100+100=200次（union把乘法变成了加法）
    

  • union在结果集合并的时候，需要注意的是查询出来的结果集的列数要相等才可以，在oracle中，查出来的结果集的的列的数据类型要一致，但是在mysql中只要求列数相同就可以，不需要要求数据类型一致

• 关于DQL语句大总结：

  • select...
    from...
    where...
    group by...
    having...
    order by...
    limit...
    

  • 执行顺序

    • 执行顺序
      from
      where
      group by
      having 
      select
      order by
      limit
      

12.2.DDL语句

12.2.1.数据类型

1.varchar和char的区别：

• varchar：可变长字符串：长度最长为255，用该数据类型定义的变量会根据存入变量的值的实际长度动态分配内存空间，但是效率低
• char：定长字符串，最长可存储255个字符，用该数据类型定义的变量无论存入到变量中的值的实际长度为多少，都会使用提前定义好的长度来存储变量，如果使用不当会造成资源的浪费

2.int的长度最长为11位整型数据

3.bigInt长整型，可以相当于java中的long

4.clob（字符大对象）：但凡是字符长度大于255，就只能使用clob类型的变量来存储数据，clob可以存储4g大小的数据，一篇文章，一个博客都可以使用clob存储

5.blob：二进制大对象：图片，音频，视频等流文件需要使用blob来存储，当往blob类型的字段上插入图片，音频，视频的时候需要使用IO流来

6.date日期转换

1. str_to_date函数可以把字符串对象varchar转换成日期date类型数据，通常使用在插入insert方面，因为插入的时候需要插入一个日期类型的数据，需要通过该函数将字符串转换成date

   1. mysql的日期格式：

      1. %Y 年
      2. %m 月
      3. %d 日
      4. %h 时
      5. %i 分
      6. %s 秒

   2. 但是当某个字段的数据类型是date的时候，如果在向表中插入数据的时候，当插入的数据是满足日期格式的字符串的时候，不需要使用str_to_date函数就可以将字符串类型的数据转换成日期类型的数据

   3. -- 当插入的数据不满足日期格式的时候，需要使用str_to_date函数
      insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990'));
      -- 当插入的数据满足日期格式的时候,不需要使用该函数
      insert into t_user(id,name,birth) values(2,'lisi','1990-01-10');
      

2. date_format函数可以将日期类型转换成指定格式的字符串

   1. select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
      

   2. date_format函数的用法：date_format(日期类型数据，‘日期格式’)

   3. 但是发现在从数据库中取数据的时候，如果没有使用这个函数，也可以取出来，但是是以mysql默认的格式来展示数据的，’%Y-%m-%d’这种形式，上述过程自动将数据库中的date类型转换成varchar类型

   4. 在向表中插入数据的时候，如果是按照‘%Y-%m-%d’的格式存储数据的时候，就可以插入成功，那么插入的这个字符串就会自动转换成日期类型，就不用使用str_to_date函数来进行转换

3. date和datetime类型的区别

   1. date是短日期类型，datetime是长日期类型，前者不包含时分秒，后者包含

   2. date默认的日期格式：’%Y-%m-%d’,datetime的默认格式：‘Y-%m-%d %h:%i:%s’

      1. -- date类型的用法
         insert into t_user(id,name,birth) values(1,'zhangsan','2001-12-12');
         -- datetime类型的用法
         insert into t_user(id,name,birth) values(2,'lisi','2002-11-27 12:00:00')
         

   3. 获取系统当前时间的函数now（）

4. 在创建表的时候可以使用default关键字指定当使用insert语句插入数据的时候，如果该字段没有数据，那么就会显示设置好的默认值

   1. sex char(1) default'm'   就表示当没有向性别这一栏插入数据的时候，默认都是m
      

12.2.2.update语句

• 语法格式：update 表名 set 字段名1=值1，字段名2=值2……where条件，如果这个语句中没有加上where进行条件限制的话，就会导致所有的数据都会被改变

12.2.3.delete语句

• 语法格式：delete from 表名 where条件，如果这里没有加上where条件限制的话，就会导致整张表的数据都会被修改

12.3.4.insert语句

• insert语句一次性可以插入单条数据，也可以一次性插入多条数据

• -- 一次性插入一条数据
  insert into t_user(name,sex) values('zs','m');
  -- 一次性插入多条数据
  insert into t_user(name,sex) values('ls','m'),('ww','m'),('zl','w');
  

12.3.5.快速创建表（复制表）（了解即可）

create table emp2 as select * from emp;

原理：将一个查询结果当作成一张新表创建，可以实现表的快速复制，表创建出来了，同时表中的数据也存在了

-- 将查询出来的两列作为一张新表创建出来
create table emp2 as select username,password from t_user;

同时insert语句也有这样的用法

12.3.6.快速删除表中数据

-- 这种方式删除数据的时候效率很低，但是事务支持回滚，就是已经删除的数据可以恢复
delete from t_user;
-- 这种方式删除数据时效率高，但是一旦删除就不能恢复
truncate table t_user
-- 以上两种方式只是删除表中的数据，但是不会将表结构删除
-- 一下这种方式会将表的结构和数据一并删除
drop table 表名

12.3.7.约束

1.什么是约束

约束就是在创建表结构的时候，可以给表中的字段添加一些约束，来保证这个表中的数据的完整性，有效性

2.需要掌握的约束

1.非空约束：not null

被修饰的字段不能为空，非空约束只能是列级约束，不能是表级约束

2.唯一约束：unique

被修饰的字段值不能重复，但是可以为null

表级约束：没有用在列的后面对这个字段进行的约束，表级约束一般用在多个表关联字段的时候使用

create table t_user(
	id int primary key auto_increment,
	name varchar(255),
	password varchar(255),
	-- 添加表级约束
	unique('name','password');
    -- 表示给两个字段联合起来添加同一个约束，当前面的字段值相等的时候，会继续判断后面字段值是否相等
);

列级约束：加在列的后面对列进行修饰

3.主键约束：primary key

主键约束=非空约束+唯一约束

主键约束就是表中一行数据的唯一标识，在给某个字段加上了主键约束之后，主键字段的值不能为空，也不能重复

一张表中如果没有添加主键约束实际上是一张无效的表，还可以给多个字段同时加上主键约束，这叫做复合主键，两个都作为主键，当插入的数据前一个值相同的时候也没有关系，它会去判断下一个关联的字段的值是否相等，如果还是相等则报错，如果不相等则插入成功，和上面的unique是一样的

但是需要注意的是，一张表只能有一个主键，上面说的复合主键实际上只是一个主键，因为添加的是表级约束，所以也是一个主键

主键值建议使用的类型是int，bigint，char这种定长的数据类型

4.外键约束：foreign key

12.3.8.存储引擎

1.什么是存储引擎？存储引擎有什么作用？

1.存储引擎就是在数据库表中，数据的存储方式

2.在创建表的时候可以在最后的")"的后面加上这么一句：engine=InnoDB default charset=utf8，这句话表示存储引擎为InnoDB，默认的字符编码方式为为utf8，当然即使你不写，mysql也会自动添加上这么一句，那么就说明mysql的默认存储引擎是InnoDB

2.查看mysql支持多少种存储引擎

命令：show engines \g 可以显示mysql支持的存储引擎的情况

注意：mysql支持9中存储引擎，但是具体的支持情况与版本有关系

12.3.事务

12.3.1.事务概要

1.什么是事务

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，一个事务要么全部成功，要么全部失败，不可再分

2.事务的应用场景

事务只能使用在 DML语句中（insert，delete，update），其他语句均不支持事务，事务本质上就是多条DML语句的组合

3.事务是如何做到多条DML语句同时成功和同时失败的呢？

在InnoDB存储引擎中，提供了一组用来记录事务性活动的日志文件

在事务的执行过程中，每一条 DML语句都会记录到"事务性活动的日志文件"中，在事务执行过程中，我们可以提交事务，可以回滚事务

提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，提交事务标志着事务的结束，并且是一种全部成功的结束

回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件，回滚事务标志着事务的结束，并且是一种全部失败的结束

4.怎么提交事务？怎么回滚事务？

提交事务：commit语句

回滚事务：rollback语句（回滚永远都是只能回滚到上一次的提交点）

事务对应的英语单词是：transaction

mysql默认的事务行为是自动提交，自动提交就是没执行一条DML语句就提交一次，这种自动提交实际上不符合开发习惯，因为一个业务通常是由多条DML语句共同执行完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条，提交一条

那么如何关闭自动提交呢？执行这个命令：start transaction；这个命令的作用就是开启事务，然后之后编写的DML语句没执行一次不会自动提交，那么如果出现错误就可以回滚，当执行commit命令的时候，就是将整个事务手动提交，提交成功之后就不支持回滚了

使用rollback可以回滚到上一次commit的那个点上

5.事务的四大特性（ACID）

A：原子性：说明事务是最小的工作单元，不可再分

C：一致性：所有的事务要求，在同一个事务中，所有操作必须同时成功或者同时失败，以保证数据的一致性

I：隔离性：A事务和B事务之间具有一定的隔离，就是当事务之间是并发执行的时候，一个事务的执行不会影响到另一个事务，就是说不同的事务之间拥有其独立的操作空间，不会收到其他事务的干扰

D：持久性：事务结束的一个保障，事务提交，就相当于将没有保存到硬盘中的数据保存到硬盘上

12.4.视图

12.4.1.什么是视图

视图就是站在不同角度去看待同一份数据

12.4.2.怎么创建视图

create view emp_view as select * from emp;

12.4.3.用视图做什么

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作，就像是java中的引用，可以把视图看成是java中的引用，引用改变了，会影响到对象的改变

假设有一条非常复杂的sql语句，而这条sql语句需要在不同的位置上反复使用，每一次使用这个sql语句的时候都需要重新编写，那么就可以在需要编写这条sql语句的位置直接使用视图对象，可以大大简化开发，并且对于后期的维护，因为修改的时候也只需要修改一个位置即可，只需要修改视图对象所映射的sql语句

使用视图对象的时候可以像使用table一样，可以对视图进行增删改查操作，视图不是在内存当中的，视图对象也是存储在硬盘上的，不会消失

注意：视图对应的语句只能是DQL语句

但是视图对象创建完成之后，可以对视图进行增删改查等操作

增删改查：（CRUD）

• C：create（增）
• R：retrieve（查：检索）
• U：update
• D：delete

12.4.4.删除视图

drop view 视图名

12.4.5.注意

只有DQL语句才能作为视图被创建，就是：

create view emp_view as 后面必须跟上select语句才能创建视图

12.5.数据库设计三范式

12.5.1.什么是数据库设计范式

数据库的设计依据，教你怎么进行数据库表的设计

12.5.2.数据库设计范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分，所有表的设计都必须满足

第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不能产生部分依赖，学生老师关系表

多对多表怎么设计：三张表：关系表 两个外键，一对多，两张表，多的表加外键，一对一外键唯一

第三范式：建立在第二范式的基础之上，要求所有的非主键字段直接以来主键，不要产生传递依赖

设计数据库表的时候，如果按照以上的范式进行，可以避免表中数据的荣誉，空间的浪费