38.javaEE-mysql数据库(一)-基础语法

1.数据库概述

1.1.什么是数据库?
数据库：英文为Database，简称DB
数据库就是存储数据的仓库,其实数据也是存储在电脑上的.只是我们用了一个数据库管理软件来管理电脑内存.
数据库包括了数据库硬件也就是计算机,也包括数据库软件.通常我们说的mysql数据库,oracle数据库指的是mysql数据库管理软件管理的数据库.

1.2.数据库分类:
目前占市场主流的是关系型数据库。当然还有非关系(NoSQL)型数据库（键值对数据库，例如：MongoDB、Redis）等其他类型的数据库;

1.3.什么是关系型数据库
底层是以二维表的及其之间的关系所组成的数据库，即是关系型数据库。例如：

1.4.常见的关系型数据库
SQL Server 微软提供（收费、Java中使用不多）
Oracle 甲骨文公司（收费、功能强大、性能优异，Java中使用者很多）
DB2 IBM（收费、中型/大型、银行/电信等企业）
MySQL 瑞典MySQL AB（免费开源、小型、性能也不差、适用于中小型项目、可集群）
SQLite 迷你数据库，嵌入式设备中

1.5.什么是SQL语言?
Structured Query Language：结构化的查询语言
SQL是操作所有关系型数据库的通用的语言
SQL语言的分类：
a) DDL – 数据库定义语言，指CREATE、ALTER、DROP等操作（ 即创建、删除、修改数据库和数据表 ）
b) DML – 数据操作语言，指INSERT、UPDATE、DELETE等操作（ 即数据表中数据的增、删、改操作 ）
c) DQL – 数据查询语言（指SELECT操作，即数据表中数据的查询操作）
…

2.MySQL数据类型及字段约束

2.1数值类型

tinyint：占用1个字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
其次是浮点类型即：
float和double类型 float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double

2.2字符串类型
1、char(n) 定长字符串，最长255个字符。n表示字符数，
2、varchar(n) 变长字符串，最长不超过 65535个字节，n表示字符数，一般超过255个字节，会使用text类型，
所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）

3、大文本（长文本）类型
text最长65535个字节，一般超过255个字符列的会使用text。
text也分多种，其中bigtext存储数据的长度约为4GB。

• 面试题: char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：
  (1)char(n)在保存数据时, 如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。
  因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
  (2)varchar(n)保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用，因此varchar不会浪费空间。
  因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
  (3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

2.3日期类型
1、date：年月日
2、time：时分秒
3、datetime：年月日 时分秒
4、timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。
timestamp最大表示2038年，而datetime范围是1000~9999
timestamp在插入数、修改数据时，可以自动更新成系统当前时间(后面用到时再做讲解)

2.4字段(列)约束
创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。
1、主键(primary key)
主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号, 例如:
创建user表,指定id为主键, 作为用户的唯一标识
create table stu(
id int primary key,
…
);
主键的特点： 唯一且不能为空！！！

2、唯一(unique)
保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。
示例：创建user表, 指定用户名不能重复
create table user(
id int primary key auto_increament,
username varchar(50) unique,
…
);
唯一约束特点是不能重复！！(允许为空)

3、非空(not null)
保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。
创建user表, 指定密码不能为空
create table user(
id int primary key auto_increament,
username varchar(50) unique,
password varchar(50) not null,
…
);
非空约束的特点是： 不能为空值， 即插入数据时该列必须得有值！！

4、外键约束
外键是用于表和表之间关系的列，后面在表关系中讲解

3.MySQL建库、建表 -DDL

事物:什么是事务?
事务代表着我们对数据库操作的最小单元
事务的四个特性:原子性,一致性,隔离性,持久性

1.原子性:我们的事务操作是对数据库操作的最小的单元不可拆分
2.一致性:在事务发生的前后,总和不会发生变化.
3.隔离性:事务之间,相互不影响.相互独立.
4.持久性:事务一旦提交,就会持久化到我们的磁盘中

3.1查看数据库、查看数据表
1、查看mysql服务器中所有数据库

show databases;

2、进入某一数据库

use test;

提示，查看已进入的库：select database();

3、查看当前库中的所有表

show tables;

提示：mysql数据库不区分大小写

3.2创建数据库、创建数据表
1、创建数据库
– 删除mydb1库(如果存在)

drop database if exists mydb1;

– 重新创建mydb1库

create database mydb1 charset utf8;

– 查看、进入mydb1库

show databases;
use mydb1;

提示：创建库时，要记得指定编码

2、创建数据表
– 删除stu学生表(如果存在)

drop table if exists stu;

– 创建stu学生表

create table stu(
	id int,				-- 学生编号
	name varchar(20),	-- 学生姓名
	gender char(1),	-- 学生性别
	birthday date,		-- 出生年月
	score double		-- 考试成绩
);

– 查看表结构

desc stu;

提示：# 和 – 是sql语句的注释符号。例如：
– 这是注释内容(要注意的是–和注释之间要有空格)
#这是注释内容

4更新表记录（insert/update/delete）-DML

4.1insert–插入表记录

插入记录的语法：
insert into 表名(列1,列2…) values(值1, 值2…);
– insert用于向指定的表中插入哪些列, 以及给予哪些值
如果要为所有的列插入值, 可以省略表名后面的列列表，例如：
insert into 表名 values(值1, 值2…);

注意:
(1)只能在给所有列插入值时, 才可以省略列列表, 否则会报错
(2)在插入值时, 如果没有省略列列表,那么列列表和值列表要一一对应
(3)在插入值时, 如果省略了列列表, 那么值列表中值的顺序要和声明列的顺序一致
(4)在插入字符串和日期时,字符串和日期要用单引号引起来。

– 例如: 往学生表(stu)中插入记录

insert into stu(id,name,gender,birthday,score) values(1, '王海涛','男', '1993-1-10', 78);
insert into stu values(2, '齐雷','男', '1995-4-8', 78);

常见问题（FAQ）：在CMD中插入记录时如果报如下错误，
因为这里存在中文的乱码问题。
解决方式：先设置编码GBK “set names gbk”, 再插入记录,因此，在通过CMD访问数据库时，最好一登录就设置 “set names gbk”，即可避免在插入数据时或者查询数据时数据乱码

4.2 update–修改表记录
– 修改stu表中王海涛的成绩，加10分特长分。

update stu set score=score+10 where name='王海涛';

– 查询stu表中王海涛的信息

select * from stu where name='王海涛';

若没有where子句, 则默认修改所有员工
– 修改stu表中所有学生的成绩，加10分特长分。

-- update stu set score=ifnull(score, 0)+10;

注意: null值和任何值计算结果还是null, 因此, 可以通过ifnull函数将null置为零对待.

4.3delete–删除表记录
– 删除stu表中的王海涛信息

delete from stu where name='王海涛';

若没有where子句, 则默认删除所有记录

5查询表记录（select）DQL

5.1基础查询
– 查询emp表中的所有员工，显示员工姓名、薪资、奖金
select name,sal,bonus from emp;
– 查询emp表中的所有员工，显示所有列。
select * from emp;

提示: distinct关键字，用于剔除指定列中的重复值，例如：
– 查询emp表中的所有部门，剔除重复的部门
select distinct dept from emp;

试一试: 查询emp表中的所有奖金, 剔除重复的值
5.2where子句查询
对表中的所有记录进行筛选、过滤使用where子句
下面的运算符可以在 WHERE 子句中使用：

SQL练习：
– 查询emp表中薪资大于3000的所有员工，显示员工姓名、薪资
select name,sal from emp where sal>3000;

– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资
select name, sal+bonus from emp where (sal+bonus)>3000;
– 或
select name, sal+bonus as 总薪资 from emp where (sal+bonus)>3000;
提示：(1)as用于定义别名(仅在查询的结果中作为列的表头显示)，也可以省略as
(2)where子句中不能使用列别名（但是可以使用表别名）

– 查询emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资
select name,sal from emp where sal between 3000 and 4500;

– 查询emp表中姓名中以"刘"开头的员工，显示员工姓名。
select name,sal from emp where name like ‘刘%’;
– 查询emp表中姓名以"刘"开头，字数为两个字的员工，显示员工姓名。
select * from emp where name like ‘刘_’;

– 查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select * from emp where name like ‘%涛%’;
提示："%" 表示任意0或多个字符。"_" 表示任意一个字符

– 查询emp表中薪资为1400、1600、1800的员工，显示员工姓名和薪资
select name,sal from emp where sal in(1400,1600,1800);

– 查询emp表中薪资小于2000和薪资大于4000的员工，显示员工姓名、薪资。
select name,sal from emp where sal<2000 or sal >4000;

– 查询emp表中薪资大于3000并且奖金小于600的员工，显示姓名、薪资、奖金。
select name,sal,bonus from emp where sal>3000 and bonus<600;

5.3排序查询
对查询的结果进行排序使用 order by关键字。
order by 排序的列 asc 升序 ↑
order by 排序的列 desc 降序 ↓
– 对emp表中所有员工的薪资进行升序(从低到高)排序，显示姓名、薪资。
select name,sal from emp order by sal asc;

– 对emp表中所有员工的总薪资进行降序(从高到低)排序，显示姓名、总薪资。
select name, sal+bonus as 总薪资 from emp order by (sal+bonus) desc;
5.4分组查询
对所查询的记录可以根据某一列进行分组, 分组使用group by。
– 将员工按照部门进行分组
select * from emp group by dept;

– 对emp表按照部门进行分组, 并统计每个部门的人数, 显示部门和对应人数
select dept 部门名称, count(*) 部门人数 from emp group by dept;

– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)
select max(sal) 总薪资 from emp group by dept;

注意：分组之前使用聚合函数表示对查询的所有记录进行统计计算
分组之后使用聚合函数表示对每一个组中的所有记录进行统计计算。

5.5聚合函数查询
1、max(列)或min(列) – 返回某列的最大值或最小值
2、count(*|列) – 返回某列的行数
3、sum(列) – 返回某列值之和
4、avg(列) – 返回某列的平均值

– 查询emp表中最高薪资
select max(sal) as 最高薪资 from emp;
– 查询emp表中最高总薪资(薪资加奖金)
select max(sal+bonus) as 最高薪资 from emp;

– 统计emp表中薪资大于3000的员工人数
select count(*) from emp where sal>3000;

– 统计emp表中所有员工的总薪资(不包含奖金)
select sum(sal) as 员工总薪资 from emp;

– 统计emp表员工的平均薪资(不包含奖金)
select avg(sal) as 员工总薪资 from emp;

!!重要提示：
a) 可以使用count()统计记录行数
b) 多个聚合函数可以一起查询
– 例如:根据部门进行分组，统计每个部门员工人数和平均薪资
select dept, count() 员工人数, avg(sal) 平均薪资 from emp group by dept;

c) 聚合函数不能用在where子句中
d) 在没有分组的情况下，聚合函数不能和其他普通字段一起查询
– 例如: 查询emp表中薪资最高的员工姓名, 下面的写法是错的:
select name, max(sal) from emp;–结果是错的
– 正确的查询：
select name, sal from emp where sal=(
select max(sal) from emp
);-- 子查询
5.6其他函数
1、数值函数
(1)ceil(数值) – 向上取整
(2)floor(数值) – 向下取整
(3)round(数值) – 向下取整
(4)rand(数值) – 随机数
– emp表中所有员工薪资上涨15.47%, 向上取整。
select name,sal, ceil(sal*1.1547) from emp;

2、日期函数

(1)curdate() – 返回当前日期(年月日)
(2)curtime() – 返回当前时间(时分秒)
(3)now() – 返回当前日期+时间(年月日 时分秒)
(4)date_add()、date_sub() – 增加/减少日期
(5)year()、month()、day()、hour()、minute()、second()，分别用来获取日期中的年、月、日、时、分、秒

– 查询系统当前时间。
select now();
– 查询emp表中所有员工的年龄，显示姓名、年龄。
select name,year(curdate()) - year(birthday) 年龄 from emp;

– 查询emp表中所有在1993和1995年出生的，显示姓名、出生日期。
select name,birthday from emp where year(birthday) between 1993 and 1995;

6外键和表关系

6.1外键
外键：唯一标识其他表中的一条记录，用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。

例如：员工表的部门id列（dept_id）和部门表的id列具有一 一对应的关系, 其中dept_id就是外键。
外键作用: 确保数据库数据的完整性和一致性
添加外键: 例如：foreign key(dept_id) references dept(id)

• 准备数据：执行《SQL脚本-db20库.txt》中的脚本，创建db20库，创建dept和emp表，并往两表中插入记录。
  练习：
  (1)执行上面的SQL语句，创建两张表（dept和emp），在创建时先不指定dept_id为外键，尝试删除部门表中的某一个部门。（ ）
  (2)将dept和emp表删除重建，在创建时指定dept_id为外键，再次尝试删除部门表中的某一个部门。
  6.2表关系
  6.2.1一对多
  一对多，反过来就是多对一，以班级和学生为例：
  (1)一个班级中可能会有多个学生(1~)
  (2)一个学生只能属于一个班级(1_{1)，两者合并结果还是1}
  因此，班级表和学生表是一对多的关系
  对于一对多的两张表，可以在多的一方添加列，保存一的一方的主键，从而保存两张表之间的关系

6.2.2一对一
以班级和教室为例：
(1)一个班级对应一个教室(1~1)
(2)一个教室也只对应一个班级(1_{1)，两者合并结果还是1}1
因此，班级表和教室表是一对一的关系
对于一对一关系的两张表，可以在任意一张表中添加列，保存另一张表的主键，从而保存两张表之间的关系

6.2.3多对多
以学生和老师为例：
(1)一个学生对应多个老师(1~)
(2)一个老师也对应多个学生(1_{*)，两者合并结果是*}
因此，学生表和老师表是多对多的关系
对于多对多的关系，可以拆分成两张一对多的关系，无法在两张表中添加列保存关系，但我们可以添加一张第三方的表（专门保存两张表的关系），保存两张表的主键，从而保存两张表的关系。

7多表连接查询
7.1连接查询

• 准备数据：执行《SQL脚本-db30库.txt》中的脚本，创建db30库，创建dept和emp表，并往两表中插入记录。

连接查询：将两张或者两张以上的表，按照指定条件查询，将结果显示在一张表中。
多张表查询的语法：
select…
from A, B…
where…

如果表名过长，可以为表添加别名以方便书写
select…
from A a, B b…
where…
上面小写的a和b就是A和B表的别名:

– 查询部门和员工两张表
select *
from dept,emp;
上面查询的结果中存在大量错误的数据, 如果想正确显示部门及部门对应的员工，可以通过where子句从中筛选正确的数据.
– 查询部门和部门下的员工。
select *
from dept d,emp e
where d.id=e.dept_id;
或
select *
from dept d inner join emp e on d.id=e.dept_id;
上面的查询(inner join…on…)方式也叫做内连接查询
7.2外连接查询
7.2.1左外连接查询
显示左侧表中的所有记录，如果在右侧表中没有对应的记录，则显示为null
语法：
select …
from a left join b on (a.id=b.xid)

– 查询所有部门和部门下的员工，如果部门下没有员工，显示null
select *
from dept d left join emp e on d.id=e.dept_id;
以上结果会显示（左侧表）所有部门，如果某部门下没有员工，（右侧表）则显示为null
7.2.2右外连接查询
显示右侧表中的所有记录，如果在左侧表中没有对应的记录，则显示为null
语法：
select …
from a right join b on (a.id=b.xid)

– 查询部门和所有员工，如果员工没有所属部门，显示null
select *
from dept d right join emp e on d.id=e.dept_id;
以上结果会显示（右侧表）所有员工，如果员工没有所属部门，（左侧表）则显示为null
7.3子查询

• 准备数据：执行《SQL脚本-db40库.txt》中的脚本，创建db40库，创建dept和emp表，并往两表中插入记录

所谓的子查询，其实就是将一个查询得出的结果，作为另外一个查询的条件。
格式：
select…
from…
where…(select…from…)

1、列出薪资比’王海涛’高的所有员工，显示姓名、薪资
– 先查询出’王海涛’的薪资
select sal from emp where name=‘王海涛’;
– 再查询比王海涛薪资(2450)高的员工
select name, sal
from emp
where sal>( select sal from emp where name=‘王海涛’);

2、列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位、部门。
– 先关联, 查询员工及员工对应的部门
select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id;
– 再查询’刘沛霞’的职位
select name, job from emp where name=‘刘沛霞’;
– 最后筛选, 筛选出和’刘沛霞’相同职位的员工
select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id and job=(select job from emp where name=‘刘沛霞’);

3、列出薪资高于在’大数据部’(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。
– 查询出’大数据部’的最高薪资
select max(sal) from emp where dept_id=30;
– 关联查询, 查询员工的姓名,薪资, 部门名称
select e.name, e.sal, d.name from emp e, dept d where e.dept_id=d.id and sal>(select max(sal) from emp where dept_id=30);
7.4多表查询
1、（左外连接）列出所有部门和部门下的员工，如果部门下没有员工, 显示为null。
select d.id, d.name, e.name, e.dept_id
from dept d left join emp e on e.dept_id=d.id;

2、（关联查询）列出在’培优部’任职的员工，假定不知道’培优部’的部门编号。
– 先查询员工及员工所属部门
select e.name, e.dept_id, d.id, d.name
from emp e, dept d
where e.dept_id=d.id;
– 再筛选过滤，查询部门名称为’培优部’的员工
select e.name, d.id, d.name
from emp e, dept d
where e.dept_id=d.id and d.name=‘培优部’;

3、（自连接查询）列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
– 先查询员工表（emp e1）
select name, topid from emp e1;
– 再查询上级表（还是员工表，emp e2）
select id, name from emp e2;
– 最后查询员工及其员工的直接上级
select e1.name,e1.topid, e2.id, e2.name
from emp e1, emp e2
where e1.topid = e2.id;

4、（分组、聚合函数）列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资
– 先查询出各种职位的最低薪资
select job, min(sal) 最低薪资
from emp
group by job;
提示：对分组后的记录筛选过滤请使用having替换where，并且having书写在最后
– 再查询出最低薪资>1500的职位
select job, min(sal) 最低薪资
from emp
group by job
having min(sal)>1500;

5、（分组、聚合函数查询）列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
select dept_id, count(*) 员工数量, avg(sal) 平均薪资
from emp
group by dept_id;

6、（分组、关联、聚合函数查询）查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
– 先关联查询, 查询出员工和员工对应的部门
select d.id, d.name, d.loc, e.name
from emp e, dept d
where e.dept_id=d.id;
– 再根据部门进行分组, 统计每个部门的员工数量
select d.id, d.name, d.loc, count(*) 员工数量
from emp e, dept d
where e.dept_id=d.id group by e.dept_id;

7、（自连接查询）列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
– 关联查询
select e1.id, e1.name, d.name
from emp e1, emp e2, dept d
where e1.dept_id=d.id and e1.topid=e2.id and e1.hdate < e2.hdate;

7.4 join和union的区别

1.join是做关联查询时使用,表结构不同,但是有关联,所以做关联查询.
2.union是表结构完全相同,但是记录不同.引用场景为:我们一张表的数据已经太多了,需要横向拆分,将表的记录放在不同的表中,所以用union.
union和union all的区别
union是排除重复的记录,只保留两张表中不同的记录,而union all是不做重复处理.所以union all的效率要高于union.

union的使用细节: 例如:
select id,name from sys_depts union select id,username from sys_users

1.这是union联合两个完全相同的表,所以查询字段数目及类型一定要对应,不然会报错.
2.union是合并并去重,union all是直接合并.
3.union是联合同样的记录.

最后
join是对应于表竖向切分之后,合并查询的技术.
union是对应于表横向拆分之后,合并查询的技术.

8扩展内容

8.1使用CMD连接MySQL服务器
通过命令行工具可以登录MySQL客户端，连接MySQL服务器，从而访问服务器中的数据。
1、登录mysql客户端命令：mysql -uroot -proot
-u：后面的root是用户名，这里使用的是超级管理员root；
-p：(小写的p)后面的root是密码，这是在安装MySQL时就已经指定的密码；

2、指定主机和端口连接: mysql -uroot -proot -h127.0.0.1 -P3306
-h：后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；
-P：(大写的P)后面的3306是连接端口，可以省略，默认连接3306端口；

3、退出客户端命令：quit或exit或 \q

FAQ：常见问题:

解决方法：复制mysql安装目录下的bin目录, 将bin目录的路径添加到path环境变量中!!
8.2使用Navicat连接MySQL服务器
1、运行navicat客户端，按照下面的图示连接mysql服务器

2、通过navicat发送SQL到mysql，打开所要操作的库（或任意打开一个库），再点击"查询" —> “新建查询”

3、在弹出的窗口中，直接书写sql运行。

4、如果要运行指定的SQL，使用鼠标选中执行的SQL语句，右键–>“运行已选择的”

8.3备份和恢复数据库
1、打开CMD窗口(不要登录)，通过命令备份mydb1数据库
备份命令: mysqldump -u用户名 -p 数据库名字 > 数据文件的位置
例如: mysqldump -uroot -p mydb1 > d:/mydb1.sql

输入密码, 如果没有提示错误, 即备份成功, 查询d盘的mydb1.sql文件
提示: (1)备份数据库的命令后面不要添加分号(;)
(2)备份数据库只是备份数据库中的表, 不会备份数据库本身

2、登录到MySQL客户端，删除mydb1库
– 删除mydb1库
drop database mydb1;
– 查询所有库, 是否还存在mydb1库
show databases;

由于备份时, 没有备份数据库本身, 所以在恢复库中的数据前, 需要先创建好要恢复的库
-- 创建mydb1数据库
create database mydb1 charset utf8;
提示: 此时的mydb1库是空的, 没有任何表

3、在CMD窗口中(不要登录)，通过命令恢复mydb1数据库
方法一:-- 在CMD窗口中(不要登录)
恢复命令: mysql -u用户名 -p 数据库名字(这里可以是任意库) < 数据文件的位置,这个表示把该数据库文件导入到其他数据库中去.
例如: mysql -uroot -p mydb100 < d:/mydb1.sql 输入密码, 如果没有提示错误, 即恢复成功, 下面进行验证

方法二:进入到数据库中,并且进入到某一个确定的数据库
Source d:/mydb1.sql;
显示query OK,就表示成功了,这里其实可以用来做很多事情,相当于导入该文件中的sql语句.并且该文件可以是txt格式的.

-- 在登录状态下, 选择mydb1库, 查询其中的表是否恢复了回来
use mydb1;
show tables;

8.4SQL中的特殊字符
8.4.1SQL语句的注释符号

在MySQL客户端中，-- 和 # 是注释符号
– 注释内容（注意–后面的空格不能省略）
#注释内容

8.4.2取消SQL语句的执行—\c
取消当前SQL语句的执行，例如：

当在cmd中书写了SQL语句，又想取消执行，在SQL语句的后面添加一个 \c 可以取消当前SQL语句的执行!!
8.4.3SQL语句无法结束执行的问题
有时在cmd中编写的SQL，后面结尾添加了分号，但是当回车时却怎么也执行不了。

仔细查看，才发现是SQL语句中的引号没有结束的原因。解决方法：
在下面敲一个单引号’和\c，回车即可结束

8.5修改表操作
现创建学生表：
use test; – 进入test库
drop table if exists stu; – 删除学生表(如果存在)
create table stu( – 创建学生表
id int, – 学生id
name varchar(20), – 学生姓名
gender char(1), – 学生性别
birthday date – 出生年月
);
8.5.1新增列
语法：ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];
1、往stu表中添加score列，double类型
alter table stu add score double;
8.5.2修改列
语法：ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]…);
1、修改id列，将id设置为主键
alter table stu modify id int primary key;

2、修改id列，将id主键设置为自动增长
alter table stu modify id int auto_increment;
8.5.3删除列
语法：ALTER TABLE tabname DROP [COLUMN] col_name;
1、删除stu表中的score列
alter table stu drop score;

8.6添加或删除主键及自增长
思考：a) 在建表时，如何为id指定主键约束和自增?
b) 建好的表，如何通过修改添加主键约束和自增?
c) 如何删除表中的主键约束和自增?
1、创建stu学生表, 不添加主键自增, 查看表结果
use mydb1; – 切换到mydb1库
drop table if exists stu; – 删除stu学生表(如果存在)
create table stu( – 重建stu学生表, 没有主键自增
id int,
name varchar(20),
gender char(1),
birthday date
);
desc stu; – 查看表结构
表结构如下: 没有主键约束和自增。

2、如果表没有创建, 或者要删除重建, 在创建时可以指定主键或主键自增
drop table if exists stu; – 删除stu表
create table stu( – 重新创建stu表时, 指定主键自增
id int primary key auto_increment,
name varchar(20),
gender char(1),
birthday date
);
desc stu; – 查看表结构
表结构如下: 已经添加了主键约束和自增。

3、如果不想删除重建表，也可以通过修改表添加主键或主键自增
再次执行第1步, 创建stu学生表, 不添加主键自增, 查看表结果
– 例如: 将stu学生表中的id设置为主键和自动增长
alter table stu modify id int primary key auto_increment;
desc stu; – 查看表结构

如果只添加主键约束, 不设置自增
alter table stu modify id int primary key;

如果已经添加主键约束, 仅仅设置自增，但需注意:
(1)如果没有设置主键, 不可添加自增
(2)只有当主键是数值时, 才可以添加自增
alter table stu modify id int auto_increment;

4、如果想删除主键自增
– 删除主键自增时, 要先删除自增
alter table stu modify id int;
– 再删除主键约束
alter table stu drop primary key;
desc stu; – 查看表结构

8.7添加或删除外键约束
8.7.1添加外键方式一：建表时添加外键
现有部门表如下：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
);
要求创建员工表，并在员工表中添加外键关联部门主键
– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int, – 部门编号
foreign key(dept_id) references dept(id) – 指定dept_id为外键
);
8.7.2添加外键方式二：建表后添加外键
现有部门表和员工表：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
);

– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int – 部门编号
);
如果表已存在，可以使用下面这种方式：
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 add constraint fk_dept_id 表示新增列，列名为fk_dept_id (名字由自己定义)
foreign key(dept_id)中的dept_id为外键

8.7.3删除外键
1、首先通过 “show create table 表名”语法，查询含有外键表的建表语句，例如：
show create table emp;
显示结果如下：

其中，emp_ibfk_1是在创建表时，数据库为外键指定的一个名字，删除这个名字即可删除外键关系，例如：
alter table emp drop foreign key emp_ibfk_1;

外键删除成功！

8.8where中不能使用列别名
SQL语句的书写顺序:
select * | 列名 – 确定要查询的列有哪些
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤, 剔除不符合条件的记录
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤, 剔除不符合条件的记录
group by 分组的列 – 指定根据哪一列进行分组
select * | 列名 列别名 – 确定要查询的列有哪些,
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount

** 关于where中不能使用列别名但是可以使用表别名?
是因为, 表别名是声明在from中, from先于where执行, 先声明再使用没有问题, 但是列别名是声明在select中, where先于select执行, 如果先使用列别名, 再声明, 这样执行会报错!!

mysql的事务

1.支持事务的引擎

mysql默认的引擎为innodb,innodb是支持事务的,mylsam是不支持事务的.

2.事务的特性

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

3.事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A中 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了修改并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读和幻读:

相同点:
都是事务A中多次的读取操作,显现出的数据不一致.
不同点:
不可重复读:侧重的是,其他事务对读取的记录做了修改更新的操作.
幻读:侧重的是,其他事物增加或者删除了记录.
原理:
解决不可重复读,只需要对查出的行记录,加上行锁,而幻读更多的是需要上表锁.

4.mysql的隔离级别

(1) 所有的隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable	否	否	否

(2) mysql的默认隔离级别
mysql的默认隔离级别为可重复读（repeatable-read）;

(3) 隔离级别操作语句

查看隔离级别
SELECT @@tx_isolation;

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

(4) mysql隔离级别底层实现
mysql通过两种方式解决脏读/不可重复读/幻读等.
4.1 MVCC
数据库的两种读，每种读读的数据版本不一样，所以也称为MVCC，即多版本并发控制

a) 快照读
select * from where xxx
这种形式的都是快照读,读的是对应版本下的数据,可能是历史数据,也可能是当前数据.
b) 当前读
update , insert ,delete ,select xx from xx for update , in share mode >都去最新的数据,如果当前数据被加锁了,则等待解锁完毕才会读取数据，不会返回数据的历史版本

4.2 加锁
即通过加锁的方式解决脏读/不可重复读/幻读.这种效率较低,但是能够保证任何时候的数据一致性.