MySQL数据库

MySQL数据库

SQL语言分类(重点)

1. 数据库查询语言(DQL-data query language)

用于使用查询语句(select,where等)

2. 数据库操纵语言(DML-data manipulation language)

用于使用增删改语句(insert,delete,update等)

3. 数据库定义语言(DDL-data definition language)

用于使用创建与删除数据库/表语句(create,drop,alter)

4. 数据库控制语言(DCL-data control language)

用于设置用户等操作(grant,revoke等)

5. 事务控制语言(TCL-transactional control language)

用于开启事务后提交与回滚语句(commit,rollback)

导入数据库数据(熟悉)

导入数据分为两种方式

1. 使用数据库语句导入,
2. 赋值sql数据文件内容直接执行

方式一

1. 创建数据库

创建数据库命令:create database 数据库名称;数据库名称随意,但一般使用db_开头,多个单词使用下划线分隔

mysql> create database db_powernode;

2. 选择刚刚创建好的数据库

选择数据库命令:use 数据库名称;

mysql> use db_powernode;

3. 导入数据库

导入数据库命令:source SQL数据库文件位置;会导入到已选择的数据库中

mysql> source D:\***.sql;

4. 查看导入后的表数据

查看当前数据库的所有表命令:show tables;

mysql> show tables;

方式二

1. 右键使用记事本或其他文本工具打开.sql文件

2. 选择一个数据库,复制所有代码到Navicat中直接执行

MySQL常用命令(熟悉)

1. 查看数据库版本

查看命令:mysql -V;(大写V)
查看命令:mysql -version;

2. 创建数据库命令

创建命令:create database 数据库名称;

3. 选择数据库命令

选择命令:use 数据库名称;

4. 终止命令

终止命令:\c终止某条SQL 语句的输入

5. 退出命令

一般在Linux中使用

1. 命令1:exit
2. 命令2:quit
3. 命令3:\q

数据库的查看与使用语句

1. 查看数据库

查看命令:show databases;固定命令

2. 选择数据库命令

选择命令:use 数据库名称;

3. 查看当前选择的数据库

查看当前选择数据库命令:select database();固定命令

4. 查看数据库中的所有表

查看数据库表命令:show tables;固定命令

5. 查看其他数据库中的表

查看其他数据库表命令:show tables from 数据库名称;

6. 查看表结构

查看表结构命令:desc 表名;

7. 查看建表语句

查看建表语句命令:show create table 表名;

数据表的简单查询语句

1. 数据表查询

查询语句命令:select 字段名1,字段名2,字段名n... from 表名;

字段名:列名称,允许有多个,使用逗号分隔即可

如果是查询所有字段可以使用星号*代替,但仅限于测试使用,后期工作千万不要使用星号*
MySQL中大小写是不严格的,在使用时一般情况不区分大小写
查询测试

2. 使用算数符号

查询时可以使用加减乘除等运算符来运算结果,同样也可以加入到表中运算
运算符号命令:select 数字(算数符)数字;
表中使用命令:select 字段名称(运算符)数字 from 表名;
运算符使用

mysql> select 8*8;
+-----+
| 8*8 |
+-----+
|  64 |
+-----+

3. 查询数据是显示别名

用于给表字段或表名起别名,别名代表称呼称号,是临时的
别名格式1:select 字段名 as 别名 from 表名 as 别名;
别名格式2:select 字段名 别名 from 表名 别名;
起别名时as可以使用空格代替
表名如果起别名了则使用字段时需要加上表名的别名.字段名–>表名别名.字段名

mysql> select ename as 姓名,sal*12 as 年薪 from emp;

数据表条件查询语句

以上查询都是直接查询表中所有数据,但实际开发中会根据不同的需求或要求查询不同的数据内容,例如:在学生表中查找某一个学生的信息等需求,此时就需要使用条件查询
条件查询语句格式:select 字段名 from 表名 where 条件(字段名=值) [and|or] 条件(字段名=值);

条件:查询条件,运算符看下面

字段名:需要查询的字段名称
值:是需要查询的内容
注意:如果查询的是字符串数据则需要加上单引号或双引号

1. 条件运算符(重点)

1. =:判断是否等于某值

mysql> select ename,sal from emp where sal=3000;

2. <>或!=:判断是否不等于某值

mysql> select ename from emp where ename!='king';

3. <或>或<=或>=:用于判断数据大小

mysql> select ename,sal from emp where sal>=3000;

4. or:或者,用于判断两者满足条件,会将所有满足条件的全部查询

mysql> select ename,sal from emp where sal=3000 or sal=1250;

5. and:和/与/并且,用于满足两个条件

mysql> select ename,sal from emp where sal=3000 and sal=1250;

6. between...and...:两者之间,用于判断两者之间内容,等同于查询结果为sal>=1250 and sal<=5000

语法格式:select 字段 from 表名 where 字段 between 值1 and 值2;

mysql> select ename,sal from emp where sal between 1250 and 5000;

7. is null:用于判断值是否为null空值,但是注意不能使用!=或=符号

# 错误使用方法
mysql> select ename,comm from emp where comm!=null;
Empty set (0.00 sec)
mysql> select ename,comm from emp where comm!=NULL;
Empty set (0.00 sec)

# 正确使用方法
mysql> select ename,comm from emp where comm is NULL;
mysql> select ename,comm from emp where comm is null;

8. in:在什么什么之中(包含什么什么东西),等同于sal=3000 or sal=1250这种写法

语法格式:select 字段 from 表名 where 字段 in(值1,值2,值n...);

mysql> select ename,sal from emp where sal in(1250,3000);

9. not:非,用于取反,not用于in/is这两个命令,例如:not in,is not null

# 查询不等于null数据
mysql> select ename,comm from emp where comm is not null;

# 查询不包含1250和3000的数据
mysql> select ename,sal from emp where sal not in(1250,3000);

10. like:喜欢喜好,MySQL中代表模糊查询,用于模糊查找数据,支持的符号为1. _下划线,匹配一次, 2. %百分号,匹配任意次数

mysql> select ename,sal from emp where ename like 's%';
mysql> select ename,sal from emp where ename like '%n';
mysql> select ename,sal from emp where ename like '____n';
mysql> select ename,sal from emp where ename like '_____n';

排序查询

排序查询试讲查询到的结果集升序或降序排序,排序可以使用多个字段,多个字段使用逗号分隔,排序时会以第一个字段排序,如果数据相同则向后推(推到第二个字段第三个字段)
排序查询不是条件,而是最终结果,单独查询则不需要加where关键字
排序查询格式:order by 字段名1,字段名2,字段名n... [asc|desc];

asc:升序,默认
desc:倒序
排序案例

mysql> select ename,sal,hiredate from emp order by sal desc;
mysql> select ename,sal,hiredate from emp order by sal desc;
mysql> select ename,sal,hiredate from emp order by sal desc,hiredate asc;

# 按照指定列排序,不建议使用
mysql> select * from emp order by 6;

分组查询/聚合函数/多行处理函数

聚合函数是计算平均值最值等等的函数功能
分组查询是在查询结果后再次分组
多行处理函数是将多条数据综合

1. 聚合函数

聚合函数是指将所有结果返回成一条数据整合–>求和,求最值,求平均值等功能

1. count(字段名):统计行数(统计记录),统计时不包含null值

由于不同null值所以正常在统计时需要选择非null列

mysql> select count(comm) from emp;
mysql> select count(empno) from emp;

2. sum(字段名):求和

计算总和

mysql> select sum(sal) from emp;

3. avg(字段名):求平均值

计算平均值

mysql> select avg(sal) from emp;

4. min(字段名):最小值

获取最小值

mysql> select min(sal) from emp;

5. max(字段名):最大值

获取最大值

mysql> select max(sal) from emp;

6. ifnull(字段名,默认值):如果字段值为null则替换默认值

替换null数据

mysql> select ename,ifnull(comm,0.0) from emp;
mysql> select ename,ifnull(comm,'此人无奖金') from emp;

7. str_to_date(字段名,格式):将字符串日期转为日期格式计算

str_to_date(字段名,格式)

字段名:需要将字符串转为日期的字段
格式:将字符串转为日期后显示的格式:%Y-%m-%d

mysql> select max(str_to_date(hiredate,'%Y-%m-%d')) from emp;

8. 组合聚合函数使用

聚合函数可以共同使用

mysql> select sum(sal),avg(sal),min(sal),max(sal) from emp;

mysql> select sum(sal) 总薪资,avg(sal) 平均薪资,min(sal) 最低薪资,max(sal) 最高薪资 from emp;

2. 分组查询

通过查询结果进行分组,例如按照部门或按照值为分组查询,或者查询每个部门的总薪资,平均薪资等数据就可以使用分组查询
分组查询不是条件,而是最终结果,所以如果没有条件则不需要使用where关键字
分组查询格式:group by 字段名;
分组查询代码

1. 分组查询

mysql> select job from emp group by job;
mysql> select count(empno),job from emp group by job;
mysql> select count(empno),sum(sal),job from emp group by job;
mysql> select deptno from emp group by deptno;

2. 分组查询+排序查询

分组查询和排序查询他们都不属于是查询条件,而是最终显示结果,依照结果操作,所以有顺序要求
分组排序顺序是先分组后排序,并且不能乱

mysql> select deptno from emp group by deptno;
mysql> select deptno from emp group by deptno order by deptno;

3. having条件

having与where相同,他们都是条件查询,但是区别在于where是在group by之前使用,而having是在group by之后使用,以结果当做虚表继续使用条件查询
分组查询后的条件,以结果为虚表再查询

1. having查询

按照job分组,查询函计算平均薪资和总薪资,再加上条件部门总薪资大于5000的数据

mysql> select job,sum(sal),avg(sal) from emp group by job having sum(sal)>5000;
mysql> select sal from emp where sal>=2000 having sal=2850;
mysql> select sal from emp having sal=2850;

select查询语法总结

一条查询语句的执行顺序,固定顺序有些可以不写,但是不能打乱

select 字段名... from 表名 where 条件... group by 分组字段... having 条件... 
		order by 排序字段... [asc|desc];

以上是SQL语句的执行顺序一定不能乱

select 字段名... from 表名:查询必加,并且是开头
where 条件...:查询条件,如果存在则必须是在第一段,不能加到其他位置
group by 分组字段...:分组查询,不是条件如果没有查询条件则直接使用如果存在查询条件则在where后面
having 条件...:以结果为查询条件,如果有分组查询则在分组查询的后面,如果没有分组查询则在where条件的后面,如果也没有where则可以单独使用
order by 排序字段... [asc|desc]:排序查询,不是条件查询,如果没有条件则可以不加where直接使用,asc升序正序,desc降序倒序

多表联合查询

1. 多表联合查询概述

之前的操作都是一张表的查询,大部分的SQL查询语句都是多表联合查询,两张表有相对应关系进行查询
多表联合查询时可能会出现的问题
笛卡尔积现象–>例如A表B表,两张表查询时不给任何的条件,最终的结果为A表总记录数乘B表总记录数

例如A表:4条记录,B表:6条记录–>A乘B=4*6=最终等于24条记录
多表联查常用方式分为两种:
SQL92语法
SQL99语法
不管是SQL92还是SQL99,代表的是年份,出现年分,SQL99是对于SQL92的升级,支持了更多语法

2. SQL92

1. SQL92连接查询概述

等值查询,非等值查询,自连接等查询方式
特点:SQL92是在where子句中写入连接条件(多张表的查询条件),多个表中如果存在重名则必须在列名前面加上表名作为前缀,或使用别名作为前缀
如果重复字段不加表名前缀则会出现如下错误

mysql> select ename,job,deptno,dname,loc from emp,dept where deptno=deptno;

2. SQL92语法

# 语法1
select 表名1.字段,表名2.字段 from 表名1,表名2 where 表名1.字段 = 表名2.字段
# 语法2
select 别名1.字段,别名2.字段 from 表名1 别名1,表名2 别名2 where 别名1.字段 = 别名2.字段

表名1:表1
表名2:表2
表名.字段:如果字段重复则必须使用这种方式

3. SQL92查询案例

1. 等值查询

在查询时两张表使用=连接

mysql> select ename,job,dept.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno;

mysql> select e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

mysql> select e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.deptno=30;

mysql> select e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='chicago';

mysql> select e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and e.ename='scott';

2. 非等值查询

非等值查询在查询时不适用=符号,而是使用!=|<|>|<=|>=

mysql> select e.ename,e.sal,s.grade,s.losal,s.hisal from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

3. 自连接查询

同一张表的判断查询,同一张表中有两个字段是有关联的,此时则可以使用自连接查询
插叙emp表中的员工领导

mysql> select e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.empno 领导编号 from emp e1,emp e2 where e1.mgr = e2.empno;

3. SQL99

1. SQL99连接查询概述

SQL99是对于SQL92语法的扩充,升级版本,解决一些小的问题
SQL99修整了SQL92的缺点,将联合条件和过滤条件分开,添加了很多新式语法
SQL99新语法

交叉连接(了解)
自然连接(了解)
Using子句连接(了解)
on子句连接(了解)
连接查询(掌握)

内连接inner join
左连接left join
右链接right join

2. SQL99语法

select 字段名称... from 表名1
[cross join 表名2] # 交叉连接
[natural join 表名2] # 自然连接
[join 表名2 using(字段名)] # using子句
[join 表名2 on(表名1.字段名 = 表名2.字段名)] # on子句
[(left|right|inner) join 表名2 on(表名1.字段名 = 表名2.字段名)] # 内连接/左连接/右链接

3. SQL99查询案例

1. 交叉连接

cross join交叉连接会产生笛卡尔积现象,效果等于两张表总记录数相乘的结果,连接时不适用where子句限定条件

mysql> select * from emp,dept;

2. 自然连接

natural join自然连接基于两张表中的全部同名列建立连接
从两张表中选出同名列的值均对应相等的所有行
如果两张表中同名列的数据类型不同则会出错
不允许在参参照列使用表名或别名作为前缀
自然连接的结果不保留重复的属性

mysql> select empno,ename,sal,deptno,dname,loc from emp natural join dept;

3. using子句

如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足需求,可以在连接时使用using子句来设置用于等值连接的列名称(参照列)
using子句引用的列在SQL任何地方都不能退使用表名或别名做前缀

mysql> select e.empno,e.ename,e.sal,deptno,d.dname from emp e join dept d using(deptno) where deptno=30;
+-------+--------+---------+--------+-------+
| empno | ename  | sal     | deptno | dname |
+-------+--------+---------+--------+-------+
|  7499 | ALLEN  | 1600.00 |     30 | SALES |
|  7521 | WARD   | 1250.00 |     30 | SALES |
|  7654 | MARTIN | 1250.00 |     30 | SALES |
|  7698 | BLAKE  | 2850.00 |     30 | SALES |
|  7844 | TURNER | 1500.00 |     30 | SALES |
|  7900 | JAMES  |  950.00 |     30 | SALES |
+-------+--------+---------+--------+-------+
6 rows in set (0.00 sec)

4. on子句

自然连接的条件是基于表中所有同名列的等值连接,为了设置任意的链接条件或指定连接的列,则需要使用on子句
连接条件与其他的查询条件需要分开写
使用on子句是查询语句更容易理解

mysql> select empno,ename,d.deptno,dname,loc from emp e inner join dept d on e.deptno = d.deptno where e.deptno=20;

5. 外连接查询

1. 左外连接

   两张表在连接过程中除返回满足连接条件行以外,还返回左表中不满足条件的行,这种连接被称为左外连接

2. 右外连接

   两张表在连接过程中除返回满足连接条件行以外,还返回右表中不满足条件的行,这种连接被称为右外连接

3. 满外连接

   两张表在连接过程中除返回满足条件的行以外,还返回两张表中不满足条件的所有行,这种连接被称为满外连接

内连接:在SQL99规范中内连接只返回满足两表连接条件的数据
外连接案例

1. 左外连接

mysql> select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc from dept d left join emp e on e.deptno=d.deptno;

mysql> select e.empno,e.ename,e.job,e.sal,deptno,d.dname,d.loc from dept d left join emp e using(deptno);

2. 右外连接

mysql> select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc from dept d right join emp e on e.deptno=d.deptno;


mysql> select e.empno,e.ename,e.job,e.sal,deptno,d.dname,d.loc from dept d right join emp e using(deptno);

3. 满外连接

mysql> select empno,ename,sal,deptno,dname,loc from emp full join dept using(deptno);

子查询

1. 子查询概述

子查询一般用于查询最高薪资最低薪资非常有优势,一般用于单独查询结果(最终查询结果为一条记录)则可以使用子查询
例如:查询员工薪资最高和最低的人是谁

mysql> select * from emp where sal = (select max(sal) from emp);

以上就是子查询,将(select max(sal) from emp)当做查询条件继续查询

# 这种当做条件的子查询结果不允许多行多列
mysql> select * from emp where sal = (select max(sal),min(sal) from emp);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select * from emp where sal = (select sal from emp);
ERROR 1242 (21000): Subquery returns more than 1 row

2. 子查询语法格式

select 字段名... from 表名... where 字段名=(子查询条件)

3. 子查询特点

子查询在主查询之前会执行一次
主查询使用子查询的结果当做条件
子查询必须在括号内使用(代表优先执行)
子查询使用比较运算符在右侧当做条件
如果子查询返回结果为单行单列则可以当做参数
如果子查询返回结果为多行或多列则会出现错误,则不允许出现在等值以及非等等判断中,但是允许出现in中使用

4. 单行子查询

单行子查询只返回一行记录,对单行子查询能够使用的符号为比较运算符:< > <= >= = !=

1. 查询案例

查询薪资比CLARK高的人的姓名和薪资

mysql> select ename,sal from emp where sal>(select sal from emp where ename='clark');

5. 多行子查询

多行子查询返回多行记录
对多行子查询只能使用多行记录比较运算符
all和子查询返回的所有值比较
any和子查询返回的任意一个比较
in等于列表中的任何一个值

1. 查询案例

查询工资等于CLARK人的任意一个员工的信息
查询工资比所有的CLERK值为的工资都高的员工编号等信息
查询部门编号20中的职位与部门编号10的员工相同的员工信息

# 查询工资等于`CLARK`人的任意一个员工的信息
mysql> select empno,ename,sal from emp where sal<any(select sal from emp where ename='clark');
# 查询工资比所有的`CLERK`值为的工资都高的员工编号等信息
mysql> select empno,ename,sal from emp where sal > all(select sal from emp where job = 'clerk');

# 查询部门编号20中的职位与部门编号10的员工相同的员工信息
mysql> select empno,ename,job from emp where job in(select job from emp where deptno=10) and deptno=20;

2. 查询案例

查询emp表中所有的领导

mysql> select empno,ename,job from emp where empno in(select mgr from emp group by mgr);

查询每个部门的平均薪资水平等级

mysql> select e.deptno,s.* from salgrade s,(select deptno,avg(sal) avg from emp group by deptno) e where e.avg>=s.losal and e.avg<=s.hisal order by e.deptno;

上面使用方式是将(select deptno,avg(sal) avg from emp group by deptno)这条语句的查询结果当做一张虚拟表(不是真实存在的表使用),通过这张虚拟表进行查询,这样可以查询比较复杂的SQL语句

limit分页查询

1. limit概述

limit是MySQL的分页查询,也是MySQL的方言(只有MySQL中可以使用),是MySQL中提供的分页查询技术,如果有多条数据或数据量过大时可以使用分页查询
例如:京东,淘宝,百度

2. 分页格式

分页查询格式:select 字段名... from 表名... [where 条件] limit index,count;

index:记录从第几条开始查询,如果查询从第一条开始显示则从0开始,不是页数
count:查询条数(记录数量)显示的数量
limit分页计算公式:(页数-1)*显示条数

3. 分页案例

select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;

使用分页实现查询emp表中最高薪资的人是谁

mysql> select * from emp order by sal desc limit 0,1;

注意:分页查询是最终查询,一定是所有结果出来之后进行分页

union

讲解国际合并(上下相加)查询
查询job中包含SALESMAN,CLERK职位的员工
不是union实现方式

mysql> select * from emp where job='salesman' or job='clerk';

mysql> select * from emp where job in ('salesman','clerk');

mysql> select * from emp where job='salesman'
    -> union
    -> select * from emp where job='clerk';

使用union可以跟任意表关联查询,但是需要保证列数相同,否则无法查询,并且查询时是查询多次

数据表操作

1. 创建表(掌握)

创建表是在数据库中新建表
创建表语法格式

create table 表名称(
    `字段名称` 数据类型(数据长度) 字段约束,
    `字段名称` 数据类型(数据长度) 字段约束
)[character set="字符编码"];

表名称:数据表的名称,可以随意写但是有规范(规范:小写名称,一般开头以t_或tb_,如果多个单词使用下划线分隔)
字段名称:一般会使用反引号引起来(有可能出现MySQL中的关键字),小写名称,多个单词使用下划线分隔
数据类型(数据长度):当前字段的类型,长度代表给字段的使用长度

char:定长字符
varchar:可变长度字符(常用)
text:可变长度字符,更长
double:浮点数
float:浮点数
int:整数
bigint:大整数
date:日期
datetime:日期+时间
blob:二进制大对象
clob:字符大对象
字段约束:用于设置字段中的约束项(例如:主键约束,非空约束,唯一约束等等)
字符编码:设置数据表的字符编码(国内常用的UTF-8,GBK,ISO8859-1等)

2. 表结构的增删改操作(熟悉)

1. 表结构添加

如果数据表中缺少字段则可以后期添加
语法格式:alter table 表名 add 字段名 数据类型(长度);

2. 表结构删除

用于删除表结构
语法格式:alter table 表名 drop 字段名;

3. 表结构修改

用于修改表接口的字段名称或数据类型
语法格式1:alter table 表名 modify 字段名 数据类型(长度)[字段约束];
语法格式2:alter table 表名 change 旧字段名称 新字段名称 数据类型(长度)[字段约束];

4. 代码案例

#### 1. 创建表
create table `tb_user`(
	`id` int(111),
	`username` varchar(20),
	`password` varchar(20)
);

create table tb_student(
	id int(111),
	name varchar(20),
	age varchar(20)
);

desc tb_user;

##### 1. 表结构添加
alter table tb_user add create_date datetime;

##### 2. 表结构删除
alter table tb_student drop age;

##### 3. 表结构修改
alter table tb_student modify name char(4);

alter table tb_student change name sname varchar(20);

22. 表数据增删改(重点)

表数据的增删改是用于修改表数据

1. 表数据的增加

向表中添加一条记录(一条数据)
语法格式:insert into 表名[(字段名称1,字段名称2,字段名称n...)] values(值1,值2,值n...);

表名:数据表名称
字段名称:数据表中的字段名称,如果添加时是所有字段添加则可以不使用字段名,如果是只加个别字段数据则必须使用
值:值需要与字段顺序保持一致,如果是没写字段则按照表数据中的字段顺序添加
inset into固定,values()固定
添加方式1

# 表数据增加
-- insert into tb_student values('菜虚鲲',1);错误格式
insert into tb_student values(1,"菜虚鲲");
insert into tb_student values(2,'菜虚鲲');

添加方式2

insert into tb_student(sname) values('基尼太美');
insert into tb_student(sname,id) values('罗志祥',4);

添加方式3

insert into tb_student values(5,'签哥'),(6,'号吴签'),(7,'字畜生'),(8,'乔碧萝');

2. 表数据的删除

删除表中数据
语法格式:delete from 表名 [where 条件...];

delete from 固定
表名:数据表名称
where 条件:可有可无,如果没有则删除表中所有数据
代码案例

# 表数据删除
delete from tb_student where sname='乔碧萝';
delete from tb_student where sname='菜虚鲲';
delete from tb_student;

3. 表数据的修改

修改表中数据
语法格式:update 表名 set 字段名1=值1,字段名2=值2,字段名n...=值n... [where 条件...];
update固定set固定

表名:数据表名称
字段名=值:字段名称与数据,赋值结果
where 条件:可有可无,如果没有则全表修改
代码案例

# 表数据修改
update tb_student set sname='艾比' where id=2;
update tb_student set sname='艾比' where id=1 and sname='菜虚鲲';
update tb_student set sname='朱茵';

表结构约束

1. 表约束概述

用于约束数据结构数据例如:非空约束代表字段中不允许出现null值,并且约束类型很多,例如:非空,唯一等等

2. 表约束类型

1. 非空约束:not null:代表此字段不允许出现null值
2. 唯一约束:unique:代表此字段不允许出现相同数据
3. 主键约束:primary key:代表表中主要字段,主键约束,并且主键约束附带唯一约束与非空约束
4. 外键约束:foreign key:代表表A与表B的关联项(关联字段),并且表A为主表(需要有主键)表B为辅表(需要有外键)并且表B外键连接表A主键
5. 自定义检查约束:check:MySQL目前为止还不支持
6. 主键自增:AUTO_INCREMENT:用于设置主键的自增数据只能设置主键并且只能是整数字段

3. 代码案例

1. 非空约束

# 非空约束
create table `tb_student`(
	`id` int,
	`name` varchar(20) not null
);

insert into tb_student(id) values(123);
insert into tb_student values(123,null);

2. 唯一约束

# 唯一约束
create table `tb_student`(
	`id` int unique,
	`name` varchar(20) not null
);

insert into tb_student values(1,'小明');

3. 主键约束

主键约束是每张表中都应该有的,并且一般设置的都是id,并且只有一个字段使用主键约束,固定整数类型
主键约束语法格式:constraint 注解名称 primary key(字段名称)

# 主键约束
create table `tb_student`(
	`id` int primary key,
	`name` varchar(20)
);

create table `tb_student`(
	`id` int,
	`name` varchar(20),
	constraint key_student_id primary key(id)
);
desc tb_student
insert into tb_student(id) values(1)

4. 外键约束

外键约束现在很少使用了,原因是会造成数据库的效率低,以及数据库操作会受到影响
外键约束用于跟主键约束相关联,有相关联后则可以将他们理解成是一张表,例如emp与dept两张表中的deptno字段就是有关联的
外键约束语法格式:constraint 外键名称 foreign key(外键字段) references 主键表(主键约束字段)
外键不可以设置注解没有的值,外键一旦引用主键了则主键不可以修改和删除(如果想删除则必须先删除外键引用)

# 外键约束
# 创建主键约束表
create table `tb_student`(
	`id` int,
	`name` varchar(20),
	constraint key_student_id primary key(id)
);
# 外键关联表
create table `tb_teacher`(
	`id` int,
	`name` varchar(20),
	`sid` int,
	constraint key_student_id primary key(id),
	constraint fk_sid foreign key(sid) references tb_student(id)
);
desc tb_teacher;

5. 主键自增

主键自增为了解决主键约束的问题(无法确保数据是否重复以及不能为null)
主键自增就是用于设置主键并且只能使用整数类型,设置后可以不再给主键设置数据,自增会默认递增设置
主键自增语法格式:AUTO_INCREMENT
字段格式:字段名称 数据类型 AUTO_INCREMENT

# 主键自增
create table `tb_student`(
	`id` int primary key auto_increment,
	`name` varchar(20)
);

主键自增如果出现SQL添加错误则会出现断续,或者删除数据后也会出现断续

级联更新/删除

1. 级联更新/删除概述

主外键删除更新会有影响,级联更新删除就是用于解决主外键关联项的
在删除主键时会级联删除外键数据,更新时会级联更新外键数据

2. 级联更新

当主键修改时会连通外键一并修改
语法格式:on update cascade

1. 代码案例

# 创建主键约束表
create table `tb_student`(
	`id` int,
	`name` varchar(20),
	constraint key_student_id primary key(id)
);
# 外键关联表
create table `tb_teacher`(
	`id` int,
	`name` varchar(20),
	`sid` int,
	constraint key_student_id primary key(id),
	constraint fk_sid foreign key(sid) references tb_student(id) on update cascade
);

级联更新时更新主键数据外键会一并更新,但是不能更新外键数据

2. 级联删除

当主键删除时会连通外键一并删除
语法格式:on delete cascade

1. 代码案例

# 级联删除
# 创建主键约束表
create table `tb_student`(
	`id` int,
	`name` varchar(20),
	constraint key_student_id primary key(id)
);
# 外键关联表
create table `tb_teacher`(
	`id` int,
	`name` varchar(20),
	`sid` int,
	constraint key_student_id primary key(id),
	constraint fk_sid foreign key(sid) references tb_student(id) on delete cascade
);

事务

1. 事务概述

事务所做事是确保多个操作的原子性,要么全部成功,要么全部失败,对于数据库事务是批量处理DML(增删改)语句,用于确保数据的完整性
事务具有四个特征(ACID)

2. 事务的ACID特征(重点)

1. A(原子性:Atomicity)

整个事务中的所有操作,必须作为一个单元全部完成,要么全部成功,要么全部失败

2. C(一致性:Consistency)

在事务的开始之前与结束之后数据中的数据必须保持一致的状态

3. I(隔离性:Isolation)

一个事务的操作不会受到其他事务操作的影响

4. D(持久性:Durability)

在事务执行完毕后该事务对数据库的所有的操作都必须持久化存储到数据库中(提交事务),并且事务数据不能回滚

3. MySQL事务的使用

在MySQL数据库中事务是默认不开启的(自动提交数据),需要手动开启事务才能实现以上所述
每一个数据库的事务都是不一样的,有的是默认开启,有的是默认关闭
事务(Transaction):一批操作(一组DML语句(增删改操作))

1. MySQL开启事务

开启方式1:set autocommit = on 或 off
开启方式2:set autocommit = 0 或 1
查看事务开启状态:show variables like '%auto%'

2. MySQL提交事务

确保事务的操作完成后则可以提交事务
提交事务:commit

3. MySQL回滚事务

如果事务的操作出现问题,则可以回滚事务(恢复数据)
回滚事务:rollback

4. MySQL事务注意事项

MySQL默认的事务是不开启的,事务开启其实就是设置关闭自动提交,每次执行时都需要设置关闭自动提交

5. MySQL事务代码案例

1. 窗口1

开启事务

mysql> use db_lian_xi;
mysql> show tables;
mysql> select * from tb_student;
mysql> show variables like '%auto%';
+----------------------------------------------+-------+
| Variable_name                                | Value |
+----------------------------------------------+-------+
| auto_generate_certs                          | ON    |
| auto_increment_increment                     | 1     |
| auto_increment_offset                        | 1     |
| autocommit                                   | ON    |
| automatic_sp_privileges                      | ON    |
| binlog_expire_logs_auto_purge                | ON    |
| caching_sha2_password_auto_generate_rsa_keys | ON    |
| innodb_autoextend_increment                  | 64    |
| innodb_autoinc_lock_mode                     | 2     |
| innodb_stats_auto_recalc                     | ON    |
| sha256_password_auto_generate_rsa_keys       | ON    |
| sql_auto_is_null                             | OFF   |
+----------------------------------------------+-------+
12 rows in set, 1 warning (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%auto%';
+----------------------------------------------+-------+
| Variable_name                                | Value |
+----------------------------------------------+-------+
| auto_generate_certs                          | ON    |
| auto_increment_increment                     | 1     |
| auto_increment_offset                        | 1     |
| autocommit                                   | OFF   |
| automatic_sp_privileges                      | ON    |
| binlog_expire_logs_auto_purge                | ON    |
| caching_sha2_password_auto_generate_rsa_keys | ON    |
| innodb_autoextend_increment                  | 64    |
| innodb_autoinc_lock_mode                     | 2     |
| innodb_stats_auto_recalc                     | ON    |
| sha256_password_auto_generate_rsa_keys       | ON    |
| sql_auto_is_null                             | OFF   |
+----------------------------------------------+-------+
12 rows in set, 1 warning (0.00 sec)

# 先执行窗口2的新增,新增后查看
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  1 | as   |
+----+------+
2 rows in set (0.00 sec)

# 执行新增但是不提交,在窗口2查看数据
mysql> insert into tb_student values(null,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

# 窗口二查看数据后再提交,提交后再查看窗口2数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# 新增数据窗口2查看,窗口2查看后回滚数据
mysql> insert into tb_student values(null,'abcd');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
| 10 | abcd |
|  1 | as   |
+----+------+
4 rows in set (0.00 sec)

# 窗口2查看数据后混滚,混滚后再次查看窗口2数据
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

1. 窗口2

不开启事务

mysql> use db_lian_xi;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_lian_xi |
+----------------------+
| tb_student           |
| tb_teacher           |
| tb_user              |
+----------------------+
3 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
+----+------+
1 row in set (0.00 sec)

mysql> show variables like '%auto%';
+----------------------------------------------+-------+
| Variable_name                                | Value |
+----------------------------------------------+-------+
| auto_generate_certs                          | ON    |
| auto_increment_increment                     | 1     |
| auto_increment_offset                        | 1     |
| autocommit                                   | ON    |
| automatic_sp_privileges                      | ON    |
| binlog_expire_logs_auto_purge                | ON    |
| caching_sha2_password_auto_generate_rsa_keys | ON    |
| innodb_autoextend_increment                  | 64    |
| innodb_autoinc_lock_mode                     | 2     |
| innodb_stats_auto_recalc                     | ON    |
| sha256_password_auto_generate_rsa_keys       | ON    |
| sql_auto_is_null                             | OFF   |
+----------------------------------------------+-------+
12 rows in set, 1 warning (0.00 sec)

mysql> insert into tb_student values(null,'ab');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  1 | as   |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

mysql>

事务的隔离级别

1. 事务的隔离级别概述

事务的隔离级别决定了事务之间的可见性
事务的隔离级别的设置会导致多个客户端(多个事务)并发访问时的状态

2. 事务的隔离级别出现的问题

事务再设置不同的隔离级别时事务的展示也不同
隔离级别会出现的问题

1. 脏读(Dirty Read)

当一个事务读取了某行数据时,另一个事务已经对此数据做了更新但未提交,这样就产生了脏读
一个事务读取了其他事务未提交的数据

2. 不可重复读(Non-Repeatable Read)

在同一个事务,同一个读操作对同一张表的前后两次读取数据产生了不同的结果,这就是不可重复读

3. 幻读(phantom Read)

幻读是指在同一个事务中之前没有的记录,由于其他事务的提交而出现新纪录

3. 事务的隔离级别设置

MySQL中事务的隔离级别配置分为四种

1. 读未提交(Read Uncommitted)

允许事务中读取其他事务未提交的数据

2. 读已提交(Read Committed)

允许事务中读取其他事务已经提交的数据,但是未提交的看不到

3. 可重复读(Repeatable Read)

确保在一个事务中执行两次select语句,结果是相同的,不管其他事务是否提交了数据是否更新了数据
可重复读是MySQL默认的隔离级别

4. 串行化(Serializable)

将一个事务与其他事务完全隔离(最安全的,但是效率也是最低的)

4. MySQL中的隔离级别设置

在MySQL中可以通过代码设置事务的隔离级别也可以通过配置文件配置,配置文件配置需要重启

1. MySQL中的四种隔离级别

1. read-uncommitted
2. read-committed–>Oracle默认
3. repeatable-read–>MySQL默认
4. serializable

2. 隔离级别设置与查询格式

1. 配置隔离级别

配置文件格式–>MySQL安装目录中的my.ini文件,如果是linux则是my.conf文件

transaction-isolation=隔离级别
代码格式:set [global|session] transaction isolation level 隔离级别
golabl与session是代表不同的范围,一般设置global即可

2. 查看隔离级别

1. MySQL8.0
   1. select @@transaction_isolation;
   2. select @@global.transaction_isolation;
   3. select @@session.transaction_isolation;
2. MySQL8.0内
   1. select @@tx_isolation;
   2. select @@global.tx_isolation;
   3. select @@session.tx_isolation;

3. 隔离级别

5. MySQL隔离级别的演示案例

MySQL隔离级别演示需要开启两个事务(两个客户端CMD)
隔离级别配置后需要重新开启客户端CMD

1. 演示读未提交(Read Uncommitted)

查询事务隔离级别,然后配置事务隔离级别

# 查看隔离级别
select @@transaction_isolation;
# 设置隔离级别
set transaction isolation level read uncommitted;
set global transaction isolation level read uncommitted;
# 配置后重重新查看隔离级别

事务A

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
# 事务A和B全都先查看一下
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

# 事务A添加数据后查看,在未提交或未回滚状态查看事务B
mysql> insert into tb_student values(null,'a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 11 | a    |
+----+------+
4 rows in set (0.00 sec)

# 回滚后在查看事务B
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
+----+------+
3 rows in set (0.00 sec)

事务B

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  8 | ab   |
|  9 | abc  |
|  1 | as   |
+----+------+
3 rows in set (0.00 sec)

# 事务A插入数据但未提交也未回滚查询
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 11 | a    |
+----+------+
4 rows in set (0.00 sec)

# 事务A插入数据后回滚后查询
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
+----+------+
3 rows in set (0.00 sec)

2. 演示读已提交(Read Committed)

查询事务隔离级别,然后配置事务隔离级别

# 查看隔离级别
select @@transaction_isolation;
# 设置隔离级别
set transaction isolation level read committed;
set global transaction isolation level read committed;
# 配置后重重新查看隔离级别
select @@transaction_isolation;

事务A

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
+----+------+
3 rows in set (0.00 sec)

# 事务A添加数据后不提交,然后在事务B中查询
mysql> insert into tb_student values(null,'哈');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
+----+------+
4 rows in set (0.00 sec)

# 事务A提交数据,提交后在事务B再次查询
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务B

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
+----+------+
3 rows in set (0.00 sec)

# 事务A添加数据后提交数据前查询
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
+----+------+
3 rows in set (0.00 sec)

# 事务A添加数据并提交后查询
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
+----+------+
4 rows in set (0.00 sec)

3. 演示可重复读(Repeatable Read)

查询事务隔离级别,然后配置事务隔离级别

# 查看隔离级别
select @@transaction_isolation;
# 设置隔离级别
set transaction isolation level repeatable read;
set global transaction isolation level repeatable read;
# 配置后重重新查看隔离级别
select @@transaction_isolation;

事务A

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
+----+------+
4 rows in set (0.00 sec)

# 事务A新增后在未提交和为回滚时查看事务B
mysql> insert into tb_student values(null,'驾');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
| 14 | 驾   |
+----+------+
6 rows in set (0.00 sec)

# 事务A提交后再次查看事务B
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
| 14 | 驾   |
+----+------+
6 rows in set (0.00 sec)

事务B

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> use db_lian_xi;
Database changed
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
+----+------+
5 rows in set (0.00 sec)

# 事务A添加数据但未提交时查询结果
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
+----+------+
5 rows in set (0.00 sec)

# 事务A添加数据并提交后查询结果
mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
+----+------+
5 rows in set (0.00 sec)

# 事务B只有commit提交后才可以查看到事务A提交的数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_student;
+----+------+
| id | name |
+----+------+
|  1 | as   |
|  8 | ab   |
|  9 | abc  |
| 12 | 哈   |
| 13 | 驾   |
| 14 | 驾   |
+----+------+
6 rows in set (0.00 sec)

6. 事务隔离级别与一致性问题

上图就是配置隔离级别显示的最终结果

索引

1. 索引概述

索引在数据库中起到非常重要的点,如果没有索引在数据量非常庞大的情况下搜索(查询)结果会非常的缓慢,索引类似于字典(目录),使用索引后查找则会指定准确的查找不会全表扫描(全表扫描是将数据从1到最终全部查看甚至所有字段挨个查)
索引的优点:查询速度明显提升
索引的缺点:如果添加索引的列需要频繁更新(增删改)则不建议设置索引,每次修改都会重新配置全表索引
常用的就是主键约束(也是一种索引)

2. 查询测试

使用索引查询格式:explain 查询语句
未使用索引查询显示

mysql> explain select * from tb_student;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tb_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

type字段显示的就是是否以索引方式查询,ALL代表未使用索引
使用索引查询显示

mysql> explain select id from tb_student;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_student | NULL       | index | NULL          | PRIMARY | 4       | NULL |    6 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

type字段显示的就是是否以索引方式查询,index代表使用索引查询
以上查询结果可以看到第五列type字段,使用索引与不使用索引的结果为ALL和index,其中ALL代表未使用索引,index代表使用索引

3. 创建与使用索引

创建索引后需要给表中的字段配置
默认带有索引的格式:primary key主键默认带有索引,unique唯一约束默认带有索引
创建索引格式:create index 索引名称 on 表名(字段名);
修改索引格式:alter table 表名 add index 索引名称(字段名);
查看索引格式:show index from 表名;
创建索引实例:

# 未使用索引查询
explain select * from tb_student
# 使用索引查询
explain select id from tb_student
# 创建索引
create index user_index on tb_user(username);
create index user_index1 on tb_user(password);
# 测试查询
explain select * from tb_user;
explain select username from tb_user;
explain select username,password from tb_user;
# 查看索引
show index from tb_user;

4. 删除索引

将现有的索引删除
格式1:drop index 索引名称 on 表名;
格式2:alter table 表名 drop index 索引名称;
格式3:alter table 表名 drop primary key;
格式4:alter table 表名 drop unique;
删除索引

# 删除索引
drop index user_index1 on tb_user;

视图

1. 视图概述

视图是在MySQL中特殊的存在,他集合一个查询语句而生(通过查询语句创建一个视图,从而之后的复杂查询可以直接使用视图),一般用于给客户查看数据,这样可有安全的保护客户的隐私,并且视图是虚表(非真是存在)
视图的有点

1. 视图让访问数据更加的简单(视图是使用查询语句创建的)
2. 可有用于不同用户展示不同数据使用
3. 能够提高检索效率
4. 隐藏表的实现细节(隐藏部分字段)

2. 创建视图

创建视图语法格式:create view 视图名称 as 查询语句;

# 创建视图
create view e_d_view as select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno order by e.deptno;

# 查询视图数据
select * from e_d_view;

3. 修改视图

修改视图语法格式:alter view 视图名称 as 查询语句;

# 修改视图
alter view e_d_view as select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno order by e.deptno desc;
# 查询视图数据
select * from e_d_view;

4. 删除视图

删除视图语法格式:drop view [if exists] 视图名称;

if exists:删除是判断视图是否存在,如果存在则删除如果不存在则不删除

# 删除视图
drop view if exists abcd;
drop view if exists e_d_view;
# 查询视图数据
select * from e_d_view;

5. 视图的注意事项

视图创建后可以修改数据,修改数据会同步更新到视图主表中,视图主表中删除数据也会同步到视图中

# 视图数据注意事项
create view ed_view as select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno order by e.deptno;
# 修改视图,去除排序
alter view ed_view as select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno; 

注意事项:

1. 如果视图语句中带有排序则无法修改内容
2. 视图内的内容不允许添加
3. 视图中不允许删除数据
4. 视图引用的主表允许删除修改以及添加数据

数据库设计的三范式

1. 数据库设计三范式概述

数据库设计三范式是针对数据库设计出现的规范,通过设计规范三范式设计出来的数据库可扩展性维护性更加强壮,让数据库更加简单(查询)

2. 数据库设计三范式实例

1. 数据库设计第一范式

数据库表中不能出现重复的字段,每个字段的原子性不能再分

1. 不符合第一范式的实例

以上表中的id数据重复了,id可以理解为学生编号,编号一旦重复代表学生信息不安全,这种情况就改设置学生id为唯一(最好是主键)

2. 符合第一范式的实例

解决方案将id列设置为主键或唯一即可

2. 数据库设计第二范式

数据库第二范式是在第一范式的基础上添加约束,要求所有非主键字段应该完全依赖于主键,不能产生部分依赖

1. 不符合第二范式的实例

以上数据库设计中存在了相同的编号,姓名,手机字段,表中不应该出现重复编号,此时需要将数据表中的重复字段单独分隔出一张表,用于单独创建
将教师与学生表分开存储,并创建新表将学生与教师关联起来

2. 符合第二范式的实例

解决方案将学生和教师分开,并创建关联表

上图就是将学生与教师表单独创建,并且使用中间表作为关联,将学生与教师表关联,查询时可以通过关联表查询所有数据,比较经典的多对多设计模式

3. 数据库设计第三范式

第三范式是建立在第二范式的基础之上,只能在主键上传递依赖(主外键关联关系),去除冗余(重复)部分

1. 不符合第三范式的实例

以上是不符合第三范式设计的数据库,当中学生班级可能会出现冗余数据,数据的唯一性已经不确定,此时可以将班级与学生分开,在学生表中使用班级主键(外键班级主键),让其关联起来

2. 符合第三范式的实例

将班级与学生分离出来,创建主外键关联,例如:emp与dept表相同

通过主外键建立两张表的关联性

3. 数据库设计三范式区别

1. 第一范式有主键,具有原子性(主键字段不可重复),并且字段不可再分割(学生编号和学生姓名不能分离)
2. 第二范式是建立在第一范式的基础上,表1和表2没有直接依赖,而是通过第三张表传递间接依赖(使用第三张表连接表1和表2),第三张表具有完整依赖(并且具有多对多关系)
3. 第三范式没有任何依赖传递,表1直接依赖于表2(将表1与表2使用主外键建立关联)
4. 数据库中的实际设计一定要遵循数据库设计规范,否则有可能出现数据库中的数据存在大量冗余数据,以及冗余字段等,会导致后期的数据库维护困难

删库删表操作

删库一时爽,铁窗泪两行:删库删表操作是不可逆的(尽管有可能找回但是数据也会存在丢失的),尤其是在工作中针对于上线的项目千万千万不要乱用,并且到公司后基本也不会给数据库删除权限

1. 删库

删库格式:drop database [if exists] 数据库名称;

[if exists]:可有可无,用于判断数据库是否存在,存在则删除,不存在则不操作,如果不存在也不会报错

# 删库语句
drop database if exists sdf;
drop database if exists 数据库;

2. 删表

删表格式:drop table [if exists] 数据表名称1,数据表名称2,数据表名称n...;

[if exists]:可有可无,用于判断数据表是否存在,存在则删除,不存在则不操作,如果不存在也不会报错

# 删表语句
drop table if exists sdf;
drop table if exists tb_teacher;
drop table if exists tb_student,tb_user;

数据库DBA命令(了解)

1. 数据库DBA命令概述

用于数据库中创建删除用户以及给用户赋予特定权限(例如:删库删表,增删改查等功能)
后期进入公司时给的权限可能是没有删库删表的权限,此命令只能使用root调用,一般情况公司不会给你这个root用户的
一般使用的较少,除非到了公司你就是领导你就会使用这些命令

2. 数据库DBA操作

1. 创建用户

创建用户时默认是%,代表所有ip均可连接
格式:create user 用户名 identified by '密码';

2. 用户授权

授权时需要根据用户名称以及ip设置,并且ip需要保持一致
赋予权限可以增删改查等权限
格式:grant 权限 on 数据库.数据表 to 用户名@登录IP identified by 密码 with grant 赋予用户创建权限;

权限:增删改查等功能
数据库.数据表:可以使用星号*代替,代表是所有

3. 撤销授权

如果某些用户权限过大则可以收回权限
格式:revoke 权限 on 数据库.数据表 from 用户

权限:增删改查等功能
数据库.数据表:可以使用星号*代替,代表是所有

3. 数据库DBA操作案例

# 创建用于
create user liang identified by 'liang';
# 用户授权
grant select,update on db_powernode.emp to 'liang'@'%';
# 撤销授权
revoke update on db_powernode.emp from 'liang';