Mysql基础

MySQL

如何连接到MYSQL

登录前一定要保证他是运行状态

net stop mysql
net start mysql

并且是在管理员下的使用，进入bin文件进行使用。

连接到MySQL服务的指令

mysql –h 主机Ip -P 端口 -u 用户名 -p密码

mysql -u root -p147015

注意

1. -p密码后面不要有空格
2. -p后面如果不输入密码，回车会要求你输入密码
3. 如果没有写-h主机，则默认就是本机
4. 如果没有写-P端口 ，默认就是3306
5. 在实际工作中，3306一般进行修改

数据库三层结构

1. 所谓安装Mysql数据库，就是在主机安装一个数据库管理系统（DBMS）,这个管理程序可以管理多个数据库。DBMS（database manage system）
2. 一个数据库可以创建多个表，以保存信息
3. 数据库管理系统（DBMS），数据库和表的关系如下图所示

DBMS对应着mysqld，因为mysqld在3306监听，所以可以认为mysqld是DBMS最核心的部分

数据库则是在data目录下的文件

表则是在data目录下的数据库里面存储的

Mysql数据库-普通表的本质仍然是文件

数据在数据库中的存储方式以表为主

表的一行称之为一条记录，在Java程序中对应为一个对象

SQL语句分类

DDL：数据定义语句[create表，库]

DML：数据操作语句[增减insert 修改 update 删除delete]

DQL：数据查询语句[select]

DCL：数据控制语句[管理数据库的，比如用户权限]

数据库的操作

创建数据库

#演示创建数据库的操作
#要求
#创建一个名称为shy_db01的数据库
CREATE DATABASE shy_db001
#对于刚刚写好的数据库进行删除
DROP DATABASE shy_db001
#创建一个使用utf8字符集的shy_dbo2的数据库
CREATE DATABASE shy_db02 CHARACTER SET utf8
#创建一个属于utf8字符集，并且带有校对规则的shy_hspdb03数据库
CREATE DATABASE shy_db03 CHARACTER SET utf8 COLLATE utf8_bin

校对规则：utf8_bin 区分大小写 默认情况下为utf8_general_ci 不区分大小写

CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，则默认为utf8

COLLATE:指定数据库字符集的校对规则

查看数据库，删除数据库

显示数据库语句

SHOW DATABASES

显示数据库创建语句

SHOW CREATE DATABASE db_name

数据库删除语句

DROP DATABASE [IF EXISTS] db_name

案例

1. 查看当前数据库服务器在的所有数据库
2. 查看前面创建的shy_db02数据库的定义信息
3. 删除前面创建的shy_db02数据库

#1. 查看当前数据库服务器在的所有数据库
SHOW DATABASES
#2. 查看前面创建的shy_db02数据库的定义信息
SHOW CREATE DATABASE shy_db02
#在创建数据库表的时候，为了规避关键字，可以使用反引号解决
CREATE DATABASE `CREATE`
DROP DATABASE `CREATE`
#3. 删除前面创建的shy_db02数据库
DROP DATABASE shy_db02

备份数据库（在DOS执行）

备份数据库

mysqldump -u 用户名 -p-B 数据库1 数据库2 数据库n >d:\\文件名.sql

这后面跟的是文件路径。

恢复数据库（进入Mysql命令行再执行）

Source 文件名.sql

第二种恢复的方法：直接将保存备份下来的文件放到查询编辑器中执行即可

备份恢复数据库的表

mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

表的操作

表的创建

character set字符集 collate 校对规则 engine 引擎 field 指定列名 dadatype指定列字段（字段类型）

character set，如果不进行指定，则为所在数据库字符集

collate，如果不执行则为数据库的校对规则

CREATE TABLE `user`(
		id INT,
    	`name` VARCHAR(225),
    	`password` VACHAR(225),
    	`birthday` DATE)
    	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

MYSQL数据库常用的数据类型（列类型）

数值类型

整形：tinyint 占用一个字节 smallint 占用两个字节 mediumint 占用三个字节 int占用四个字节 bigint 占用八个字节

小数类型：float 单精度占用四个字节 double双精度 八个字节 decimal[M,D]m代表长度，D代表小数点精确多少位，这个大小不确定，看M和D是多少

如何定义一个无符号整数

只需要在他后面加上一个unsigned即可如

create table t1(id tinyint unsigned);

bit类型的使用

bit(m) m在1-64。

bit字段显示的时候按照 位 的方式显示

查询的时候仍然可以用使用 添加的数值

如果一个值只有0,1 可以考虑使用bit（1）来节约空间

为类型，M指定位数，默认为1，范围是1-64

小数类型的使用

decimal[M,D]，M最大是64，D最大是30 如果D被省略，默认是0，如果M被省略则默认是10。

文本类型

char（0-255）

varchar（0-65535）
可变长度字符串最大65532字节（utf8编码，最大为21844字符，1-3个字节用来记录大小）

text（0-2^16-1）

longtext（0-2^32-1）

字符串使用细节

1. char（4） 这个4表示字符数，不管是中文还是英文都是4个，按照个数计算
2. varchar（4）也这样表示
3. char（4）是定长，也就是说如果你只输入了aa两个字符，也会占用分配4个字符得到空间
4. varchar（4）是变长，就是说，当你插入了aa，实际占用空间大小并不是4个字符，而是按照实际的需要去分配（varchar本身还需要占据1-3个字节来记录存放内容的长度）
5. 当一个数据的长度是确定的时候使用char，不确定的时候使用varchar
6. 在查询速度上char>varchar

二进制数据类型

blob（0-2^16-1）

longblob（0-2^32-1）

日期类型

date(日期 年月日)

time（时分秒）

datetime（年月日 时分秒）

year（年）

timestamp（时间戳）

基本使用

`db01`
CREATE TABLE birthady6(
	t1 DATE,
	t2 DATETIME,
	t3 TIMESTAMP 
	NOT NULL DEFAULT CURRENT_TIMESTAMP
	 ON UPDATE CURRENT_TIMESTAMP);
	 
SELECT * FROM birthady6;
INSERT INTO birthady6(birthday ,job_time)VALUES('2004-11-14','2022-11-14 10:10:10');
SELECT * FROM birthady6;

时间戳不填空着的话，会自动填入当前的时间。

练习

创建一个员工表emp，选用适当的数据类型，id name sex birthday entry_date job Salary resume

创建成功！！！

数据插入成功。

表的修改

添加列

	ALTER TABLE tablename 
		ADD column datatype [DEFAULT expr]
           [,colum datatype]……

修改列

    ALTER TABLE tablename       
    MODIFY column datatype [DEFAULT expr]        
        [,colum datatype]……

删除列

ALTER TABLE tablename
	DROP  column
查看表的结构： desc 表名

修改表名 Rename table 表名 to 新表名;

修改表字符集：alter table 表名 character set 字符集;

应用实例

在表上添加一个image列，vrachar类型（要求跟在resume后面）

修改 job列，使其长度为60

删除sex列

表名修改为employee

修改表的字符集为utf8

列名name修改为user_name

`db01``db01`
`emp`
SELECT * FROM emp;
INSERT INTO `emp`
	VALUES(100,'jack','man','2004-11-14','2023-10-14',100000,'dadwda dadda');
	SELECT * FROM emp;
	
`db01``db01``emp`
ALTER TABLE emp
	ADD image VARCHAR(32) NOT NULL DEFAULT  ''这一句的意思是，这个值不能为空，初始值为‘ ’
	AFTER RESUME #指在resume之后添加
ALTER TABLE emp 
	MODIFY job_Salary VARCHAR(60) NOT NULL DEFAULT ''
ALTER TABLE emp DROP sex
RENAME TABLE emp TO employee
ALTER TABLE employee CHARACTER SET utf8
ALTER TABLE employee CHANGE  `name` user_name VARCHAR(32) NOT NULL DEFAULT ''
DESC employee

表的删除

DROP TABLENAME

表的CRUD语句

Insert语句（添加数据）

INSERT INTO TABLENAME VALUES(value)

案例

创建一张商品表（id int，goods_name varchar(10)，添加两条记录

CREATE TABLE good(id INT,
		goods_name VARCHAR(20),
		price DOUBLE);
		
INSERT INTO good (id goods_name,price) VALUES(1,'cholocate',5.5);
INSERT INTO good VALUES(2,'apple',2.5);
SELECT * FROM good

使用细节

1. 插入的数据应与字段的数据类型相同
2. 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符加入到长度为40的列中。
3. 在valuse中列出的数据位置必须与加入的列和排列位置相对应。
4. 字符和日期型数据应保安在单引号中。
5. 列可以插入空值（前提是该字段允许为空），insert into table value（null）
6. insert into table_name（列名） valuse （），（），（）形式添加多条记录
7. 如果是给表中的所以字段添加数据，可以不写前面的字段名称
8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

Update语句（更新数据）

UPDATE table_name SET COL_name=expr1[where where_definition]
//如果where条件不写，相当于给所有的都进行修改

案例

在上面创建的employee表中修改记录

将所有员工的薪水修改为5000元

将名jack的薪水修改为3000元

UPDATE employee 
	SET job_Salary=5000

UPDATE employee 
	SET job_Salary=3000 
	WHERE user_name=' jack'
	
SELECT * FROM employee

使用细节

1. UPDATE 语法可以用新值更新原有表中的各列
2. SET子句指示要修改 那些值和要给予哪些值
3. WHERE子句指定应更新哪些行，如果没有WHERE子句，则更新所有的行
4. 如果要修改多个字段，可以通过set 字段1=值1，字段2=值2……

Delete语句（删除数据）

DELETE FROM * table_name WHERE ……

如果不加where限定，就会把他全部给删了。

Select语句（查找数据）

SELECT DISTINCT *|{column1，column2，column3} FROM table_name

这个DISTINCT写上之后可以将重复的元素去除。

SELECT DISTINCT `name` FROM employee 

这样就会返回employee 中的name

案例

比如说现在有一个成绩表里面有Chinese English math的成绩，我现在要统计他们的成绩总和

SELECT `name`AS`姓名`,(Chinese +English+ math) AS total_score FROM student

AS在这块的作用是将name替换为姓名输出，然后将后面的进行相加使用total_score 进行输出。

在where子句中经常使用的运算符

<小于 >大于 <= 小于等于 >=大于等于 =等于 <> | !=不等于

BETWEEN AND 显示在某一区间的值

IN（set）显示在in列表中的值，例：in（100,200），in不是区间，是和里面的数字匹配

LIKE ‘ ’ NOT LIKE‘ ’ 都是模糊查询

逻辑运算符中的 and or not 都可以是使用

SELECT * FROM student WHERE `name`=.....
SELECt * FROM student WHERE (Chinese +English+ math )>200//查找总分大于200
SELECT * FROM student WHERE `name`='韩%'//这是表示名字开头只要是韩就进行查询，为模糊查询

order by子句

order by子句使用来排序的

SELECT column1,column2,column3
	FROM table
	order by column asc|desc,

1. order by指定排列的序列，排序的列可以是表中的排名，也可以是select语句后指定的排名
2. asc升序（默认），desc降序
3. order by 子句应该位于select语句的结尾

案例

1. 对于数学成绩排序后输出（升序）
2. 对总分从高到低进行输出
3. 对姓李的学生成绩进行升序输出

SELECT * FROM student ORDER BY math 
SELECT * FROM student ORDER BY (Chinese +English+ math) DESC
SELECT * FROM student WHERE `name`='李%' ORDER BY(Chinese +English+ math)

having子句

having子句是对分组后的结果进行过滤的

案例

假如说现在我有一个部门表，我要显示每个部门的最高工资和平均工资小于2000的

SELECT AVG(sal),MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG（sal）<2000

COUNT（）

概念：
count()是MySQL中用来统计表中记录的一个函数，返回条件的行数

用法：
count(*)
返回表中的记录数(包括所有列)，相当于统计表的行数(不会忽略列值为NULL的记录)

count(1)
忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替(不会忽略列值为NULL的记录)

count(列名)
返回列名指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内

count(distinct 列名)
只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

字符串相关函数

CHARSET(str) 返回字串字符集

CONCAT(string2[……]) 连接字串

INSTR(string ,substring) 返回substring在string中出现的位置，没有返回0

UCASE(string2) 转换为大写

LCASE(String2) 转换为小写

LEFT(string 2,length) 从String2中的左边开始取length个字符

LENGTH(string) string长度（按照字节计算）

REPLACE(str,search_str,replace_str) 在str中用replace_str替换search_str

STRCMP(string1 string2) 逐字符比较俩字串大小

SUBTRING(str ,position) 从str的position开始（从1开始计算），取length个字符

SUBTRING(str ,1,1)  取第一个字符

LTRIM(string2)RTRIM(string2)trim 去除前段空格或者后端空格

案例使用

`db01``employee`
SELECT CHARSET (user_name) FROM employee

SELECT CONCAT(user_name,'  resume is  ',RESUME) FROM employee

SELECT INSTR('jack','j')FROM DUAL
#dual 是一个亚元表，当没表可以使用的时候，她可以作为一个默认的表来使用

数学相关函数

ABS（num） 绝对值

BIN（decimal_number) 十进制转二进制

CEILING(number2) 向上取整，得到num2大的最小整数

CONV(number2,from_base,to_base) 进制转换

SELECT CONV(8,10,2)
他的含义是将8当做十进制来看待然后转为2进制

FlOOR(number2) 向下取整，得到比num2小的最大整数

FORMAT(number,decimal_places) 保留小数位数

HEX(DecimalNumber) 转十六进制

LEAST(number,number2) 求最小值

MOD(numerator,denominator) 求余

RAND([seed]) 其范围为[0,1]闭区间

AVG（）算平均数

时间日期相关函数

CURRENT_DATE() 当前日期

CURRENT_TIME() 当前时间

CURRENT_TIMESTAMP() 当前时间戳

DATE（datetime) 返回datetime的日期部分

DATE_ADD(date2,INTERVAL d_value d_type) 给date2加上日期或者时间
d_type是表示什么时间类型的，通常这个函数可以使用在where语句中，比如要查询十分钟内发布的信息什么的。

DATE_SUB(date2,INTERVAL d_value d_type) 给datee减去日期或者时间

DATEDIFF(date1,date2) 两个日期差（结果返回天）（前减后）

TIMEDIFF(date1,date2) 两个时间差（多少小时多少分钟多少秒）

NOW() 当前时间

YEAR|MONTH|DATE(datetime) 取出日期的年月日

FROM_UNIXTIME() 可以将一个unix_time秒数转换为指定格式的日期

加密和系统函数

USER() 查询用户

DATABASE() 数据库名称

MD5(str) 为字符串算出一个MD5 32的字符串，（用户密码）加密

PASSWORD(str) 从原文密码str计算并返回密码字符串，通常用于对于mysql数据库的用户密码加密

当我们想要在数据库中查找的时候，可以不进行切换数据库，直接使用数据库.表进行定位。比如

SELECT * FROM 数据库名.表名

流程控制函数

IF(expr1,expr2,expr3) 如果expr1为True则返回expr2，否则返回expr3

IFNULL(expr1,expr2) 如果expr1不为空null，则返回expr1，否则返回expr2

SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END； 如果expr1为T，就返回expr2，如果expr3为T，就返回expr，否则返回expr5。

判断是否为空不能使用==，要是有is null来判断

分页查询

SELECT LIMIT start，rows，表示从start+1开始取，取到第rows句，start从0开始计算。

使用group by对于相同column进行分组然后having进行筛选，最后使用order by进行排序左后使用limit进行分页输出。

多表查询

说明

多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求。

多表查询练习

多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

比如说我们现在存在两个表一个是emp一个是dept

emp是雇员表，dept是部门表，其中雇员表的部门是以数字表示的，然后在部门表中，一个数字对应一个部门

我们现在要显示雇员的姓名，雇员工资和所在部门的名字

分析

1. 雇员名和雇员工资来自于emp表，部门的名称来自于dept表

2. 需要对这两张表进行查询

   我们如果直接

   SELECT * FROM emp,dept
   

   对这两张表同时进行查询

   默认情况下：

   1. 从第一张表中取出一行，和第二张表的每一行进行拼接，返回结果
   2. 然后以这种形态一直执行下去知道第一张表结束。

   这样的多表查询默认处理方式被称为笛卡尔集

3. 解决这种多表的关键就是写出来过滤条件

SELECT user_name,job_Salary,dname
	FROM emp,dept
	WHERE emp.deptno=dept.deptno

当两个编号相同的时候进行输出，使用表名.列名是因为两张表都有相同的列名，需要进行指定。

自连接

自连接是指在同一张表的连接查询

比如显示员工的名字和他上级的名字，说明：在每一行中，上级使用他的id来代替，可以通过id来查询到他的姓名

SELECT *
	FROM emp,emp

当我们这样输出的时候，会发现报错，他要求我们使用表的别名来进行编程

我们可以改为

SELECT *
	FROM emp woker,emp boss

下来我们给他进行筛选即可

SELECT worker.name,boss,name
	FROM emp woker,emp boss
	WHERE worker.bossid=boss.id

这样返回出来的表比较怪，因为两个列都是name，我们可以使用AS给他换个名字

SELECT worker.name AS ‘员工名’,boss,name AS ‘上级名’
	FROM emp woker,emp boss
	WHERE worker.bossid=boss.id

子查询

说明

子查询就是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询

单行子查询是指只返回一行数据的子查询语句

多行子查询

多行子查询返回多行数据的子查询，使用关键字in

练习

1. 显示和jack同一部门的所有员工

分析：首先先得知道jack的部门号，然后将这个select语句当做一个子查询来使用

SELECT * FROM emp
	WHERE deptno=(
        SELECT deptno 
        FROM emp 
        WHERE user_name='jack')

2. 查询10号部门工作相同的雇员的名字，岗位，工资，部门号，但是不包含10号部门自己的员工

分析：查询到10号部门有哪些工作岗位，然后WHERE给下一个select语句找出来这些岗位的人，然后筛选去掉10号部门的

SELECT user_name，job，job_Salary,deptno
	FROM emp
	WHERE job IN(SELECT DISTINCT job
         		FROM emp
         		WHERE deptno=10)		
         	   AND deptno!=10 

这里使用in是因为job含有很多种，只要符合一种即可

在子查询中使用all和any操作符

ALL

比如我现在要显示工资比部门编号为30的所以员工工资高的员工的姓名和工资和部门号

SELECT  use_name,job_Salary,deptno
	FROM emp
	WHERE  job_Salary>all(SELECT job_Salary 
                          FROM emp
                         WHERE deptno=30)

all表示sal比后面所有的sal都要高的元素。也可以使用SELECT MAX（job_Salary） FROM emp WHERE deptno=30

ANY

any是高一个就返回就是和最低的比较，我不再演示，他的操作和ALL相同。

将子查询的结果当做临时表来使用

比如说我们现在显示各个部门的最高工资的员工姓名和工资

我们先要得到每个部门的最高工资。然后才能进行输出姓名和工资，我们可以使用分组，将每个部门进行分组，然后去比较

SELECT use_name,MAX(job_Salary)
	FROM emp
	GROUP BY deptno

这样我们就得到了一张每个部门最高工资的表，然后我们将这个表当中临时表进行查询

SELECT emp.use_name,emp.job_Salary
	FROM(SELECT use_name,MAX(job_Salary)
		FROM emp
		GROUP BY deptno)temp,emp
	WHERE temp.use_name=emp.use_name

这个temp是给临时表的名称。

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

比如我现在要查询jack同一个部门和岗位的人

SELECT user_name
	FROM emp
	WHERE (deptno,job)=(
     		SELECT deptno,job
    		FROM emp
    		WHERE user_name='jack')
    		AND user_name!='jack'

WHERE(…,……)=(……,…….)

这俩个区域内的元素要完全相同

表复制和去重

表复制是自我复制数据也叫蠕虫复制

有时候为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

案例

CREATE TABLE my_table(
		id INT,
		`name` VARCHAR(32),
		sal DOUBLE,
		job VARCHAR(32),
		deptno INT)

创建一个表

下来演示如何自我复制，首先将emp的数据复制都my_table当中

INSERT INTO my_table
		(id,`name`,sal,job,deptno)
		SELECT id,user_name,job_Salary,job,deptno 
		FROM emp

自我复制就是

INSERT INTO my_table
		SELECT * FROM my_table

这个就是将my_table的所有数值插入到my_table当中，进行复制

删除一张表的重复记录

1. 首先创建一个表my_table02
2. 让my_table02有重复记录

CREATE TABLE my_table02 LIKE emp

这个意思就是创建my_table02让他的列信息和他相同

INSERT INTO my_table
		SELECT * FROM emp
INSERT INTO my_table
		SELECT * FROM emp

把emp的数据插入这个表两次，会出现重复的记录

3. 去重思路：先创建一张临时表，该表的结构和my_table02相同，然后将my_table02的记录使用DISTINCT关键字处理之后复制到临时表当中，最后清除掉my_table02的所有记录，然后将临时表的记录复制到my_table02，然后drop掉临时表

CREATE TABLE my_temp LIKE my_table02
INSERT INTO my_temp DISTINCT FROM * my_table02
DELETE FROM my_table02
INSERT INTO my_table02  FROM * my_temp
DROP TABLE my_temp

合并查询

有时候在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all

1. union all
   该操作符用于取得两个结果集的并集，当使用该操作符时，不会取消重复行

SELECT name,sal,job FROM emp WHERE sal>2500
SELECT name,sal,job FROM emp WHERE job='MANAGER'

我们需要合并的时候可以使用union all

SELECT name,sal,job FROM emp WHERE sal>2500
union all 
SELECT name,sal,job FROM emp WHERE job='MANAGER'

2. union 是合并去重的，操作方法和union all相同

mysq的外连接

前面我们是利用where子句对两张表或者多张表，形成的笛卡尔表进行筛选，根据关联关系进行筛选

比如说有4个部门，有一个部门没人，我们现在要求列出部门名称和这些部门员工的名称和工作，同时要显示那些没有员工的部门。

我们之前是根据关联关系，关联不上的不进行输出，但是我们部门没有员工，所以无法进行关联，就没办法输出，这个时候我们就进行外连接来解决这个问题

外连接

左外连接：左侧的表完全显示

右外连接：右侧的表完全显示

左外连接

select ….from 表一 left join 表2 on条件

表一完全输出，表二根据条件输出

右外连接

select ….from 表一 right join 表2 on条件

表二完全输出，表一根据条件输出

mysql约束

基本介绍

约束用于数据库的数据满足特定的商业规则

在mysql中，约束包含not null，unique ，primary key，foreign key和check五种

primary key 主键

字段名 字段类型 primary key

用于唯一的标识表行的数据，当定义主键约束后，该列不能重复

CREATE TABLE T1(
		id INT PRIMARY KEY,
		`name` VARCHAR(32),
		email VARCHAR(32))

这就表示id这一列为主键，而且他的值不能重复。

细节说明

1. primary key不能重复并且不能为null
2. 一张表最多一个主键，但是可以是复合主键
   复合主键，可以将id和name和起来看成一个主键，直接用括号括起来然后给后面添加primary key即可。第四条指定的时候可以primary key（列名1，列名2）即可
   复合主键添加的时候，必须id和name都相同才会报错，一个相同一个不同不会报错。
3. 直接在字段后指定
4. 在表定义的最后指定 primary key（列名）
5. 使用desc表名可以看到primary key的情况

not null 非空

如果在列上定义了not null 则表示插入数据时不能为空

unique 唯一

当定义了唯一约束后，该列值是不能重复的

细节：

1. 如果没有指定not null，则unique字段可以有多个null
2. 一张表可以有多个unique字段

演示

CREATE TABLE T1(
		id INT UNIQUE,
		`name` VARCHAR(32),
		email VARCHAR(32))

foreign key 外键

用于定义主表和从表之间的关系，外键约束要定义在从表上，主表必须具有主键约束或者是unique约束，当定义外键约束之后，要求外键列数据必须在主表的主键列存在或者为null

使用细节

FOREIGN KEY 本表字段名 REFERENCES 主表名（主键名或者是unique字段名）

为什么必须连接有约束关系的是因为，万一有数据重复，外键就不知道连接哪一条数据了

CREATE TABLE class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32))
CREATE TABLE stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	class_id INT,
	FOREIGN KEY (class_id )REFERENCES class(id))//指定外键关系

当我们在class添加100号班级和200号班级后，当我们给stu添加成员的时候，如果个哦class_id赋300就会报错，因为300不存在

1. 外键指向的表的字段，要求是primary key或者是unique
2. 表的类型是innodb（引擎），这样的表才会支持外键
3. 外键字段类型要和主键字段的类型一致
4. 外键字段的值，必须在主键字段中出现过，或者为null，前提是外键字段运行为null
5. 一旦建立了主外键关系，数据不能随意删除

check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间，如果不在那个其中就会报错

列名 类型 check（check类型）

自增长

在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始自动增长

字段名 整形 primary key atuo_increment

设置之后添加有三种方式

1. insert into …….(字段1，字段2…….)values (null,值1，值2………)
2. insert into …….(字段2…….)values (值1，值2………)
3. insert into …….values (null,值1，值2………)

细节

1. 一般来说自增长是和primary key配合使用的
2. 自增长也可以单独使用但是需要配合一个unique
3. 自增长修饰的字段为整数型，小数也可以，只是比较少
4. 自增长默认从1开始，也可以通过如下命令修改
   alter table 表名 atuo_increment=XXX；
5. 如果添加数据值，给自增长的值指定了一个值，则以指定的值为准。下一个自增长从指定+1开始

6. Mysql索引

案例入门

说起来提高数据库的性能，索引是最物美价廉的东西，下面我们来说明索引的好处。

比如我们构建了一张海量表有800000，要找一个jack名字

创建索引可以大大提高程序的运行时间，如下

CREATE INDEX id_index ON emp (id)

这表示在emp表中的id创建了索引，经过实际操作，未创建索引前的运行时间为4秒，创建后的为0.04秒。

说明原理

没有索引为什么会慢

当我们没有索引时，会进行全表扫描进行查找工作

使用索引为什么会快

使用了二叉法进行查询，所谓二叉法就是，比如查询五个数，他会找到最中间的数，比如2，然后2进行分，分为1和3，然后3再去查4，这就是二叉法

使用索引的代价

会创建新空间来存放索引，空间大了但是速度很快

索引的类型

1. 主键索引，主键自动的为主索引，主键自动的就是一个索引
2. 唯一索引（UNIQUE），
   id int unique， id是唯一的，同时也是索引，称为UNIQUE索引
3. 普通索引（INDEX）
4. 全文索引（FULLTEXT）适用于MYISAM
   开发中考虑使用全文搜索Solr和ElasticSearch

索引使用

添加索引

CREATE TABLE t20(
	id INT,
	`name` VARCHAR(32))
SHOW INDEXES FROM t20
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t20(id)
-- 添加普通索引方式1
CREATE  INDEX id_index ON t20(id)
-- 如何选择
-- 如果某列的值是不会重复的，优先考虑使用unique，否则为普通
-- 添加普通索引的方式2
ALTER TABLE t20 ADD INDEX id_index (id)
-- 添加主键索引
ALTER TABLE t20 ADD PRIMARY KEY (id)
-- 或者直接在创建表的时候对他进行主键指定

删除索引

-- 删除索引
DROP INDEX id_index ON t20
-- 删除主键索引
ALTER TABLE t20 DROP PRIMARY KEY

查询索引

-- 查询索引方式1
SHOW INDEX FROM t20
-- 查询索引方式2
SHOW INDEXES FROM t20
-- 查询索引方式3
SHOW KEYS FROM t20
-- 查询索引方式4
DESC t20

MySQL事务

什么是事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组dml语句要么全部成功，要么全部失败

事务和锁

当执行事务操作时（dml语句），mysql会在表上加锁，防止被其他用户改表的数据，这对于用户来讲是非常重要的

事务操作解释

事务进行操作的时候通过创建保存点来进行，然后进行一系列操作之后再设置一个保存点进行下一步操作，当操作失误，可以回到上一个保存点重新开始，这个称之为回滚。

重要操作

1. start transaction 保存点名–设置保存点 开始一个事务
2. savepoint 保存点名–设置保存点
3. rollback to 保存点名–回退事务
4. rollback 回退所有事务
5. commit 提交事务，所有的操作生效，不能回退

CREATE TABLE t21(
	id INT,
	`name` VARCHAR(32))
-- 开始事务
START TRANSACTION
-- 设置保存点
SAVEPOINT a
-- 开始dml操作
INSERT INTO t21 VALUE (100,'jack')
SELECT * FROM t21
 -- 设置保存点
 SAVEPOINT b
 -- 进行dml操作
 INSERT INTO t21 VALUE (200,'tom')
 
SELECT * FROM t21
-- 回到b点
ROLLBACK TO b
-- 回到a点
ROLLBACK TO a

当我们执行了commit，我们就不能进行回滚操作

注意事项

1. 如果不开始事务，默认情况下，dml操作是自动操作的，不能回滚
2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回退到你事务开始的状态
3. 你也可以在这个事务中（还没有提交时），创建多个保存点
4. 你可以在事务没有提交前，选择回退到哪个保存点
5. Mysql的事务机制需要innodb的储存引擎
6. 开始一个事务 start transaction ，set autocommit=off；两种方式

事务隔离级别

概念

Mysql定义了事务与事务之间的隔离程度

1. 多个链接开启各自事务操作数据库中的数据时，数据库要负责隔离操作，以保证各个链接在获取数据时的准确性

2. 如果不考虑隔离性，可能会引发下面的问题

   • 脏读
   • 不可重复读
   • 幻读

3. 存在四种隔离级别

   1. 读未提交 Read uncommitted 三种问题均能引发 加读锁
   2. 读已提交 Read committed 引发不可重复读和幻读 不加锁
   3. 可重复读 Repeatable read 不引发问题 不加锁
   4. 可串行化 Serializable 不引发问题 加锁
      加锁的话，当一个事务在操作这个数据库还没提交的话，另外一个控制台要去访问这个数据库，是不能进行访问的，和多线程的锁相似

4. 修改隔离级别的指令

   SET SESSION TRANSACTION ISOLATION LEVEL ~~~~~
   

脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读，就是俩控制台去看一个表，一个控制台还没entre，只是写了一个eml语句，还没有进行提交操作，但是另外一个控制台就已经能看见这个修改的结果了。

不可重复读（nonrepeatable read）同一查询在同一事务中多次进行，由于他提交事务所做的修改或删除，每次返回不同的结果集，此时发送不可重复读

幻读（phantom read）同一查询在同一事务中多次进行，由于其他提交事务所的插入操作，每次返回不同的结果集，此时发生幻读

操作

1. 查看当前会话隔离级别

   select @@tx_isolation
   

2. 查看系统当前隔离级别

   select @@gobla.tx_isolation
   

3. 设置当前会话隔离级别

   set session transaction isolation level repeatable read
   

4. 设置系统当前隔离级别

   set global transaction isolation level repeatable read
   

5. mysql默认的事务隔离级别是 Repeatable read ，一般情况下，没有特殊要求可以不进行修改。

全局修改隔离需求的话，可以在mysql.ini配置文件上修改

事务的acid特性

1. 原子性
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2. 一致性
   事务必须使数据库从一个一致性状态转换为另一个一致性状态
3. 隔离性
   事物的隔离性是多个用户并发访问数据库的时候，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
4. 持久性
   持久性是指一个事务一旦被提交，他对数据库的任何数据的改变是永久的，即使数据库发送故障也没有任何的影响

MySQL表类型和存储引擎

基本介绍

1. MySQL的表类型由存储引擎决定，主要包含MylSAM，innoDB，Memory等
2. MySQL数据表主要支持六种类型，分别是CSV，Memory，ARCHIVE，MGB_MYISAM，MYISAM，InnoBDB
3. 这六种又分为两类，一类是事务安全型，第二类称为非事务安全型

细节说明

1. MyISAM不支持事务，也不支持外键，但其访问速度极快，对事务完整性没有要求
2. InnoDB储存引擎提供了具有提交，回滚，崩溃恢复能力的事务安全，但是比起来MYISAM存储引擎，InnoDB写的处理效率差一些，并且需要更多的空间来存储数据和索引
3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际上对应一个磁盘文件。MEMORY类型的表访问非常的快，因为他的数据时存放在内存中的，而且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失，表的结构还在。

视图

基本介绍

1. 视图是一个虚拟表，其内容由查询来定义，和真实的表一样，视图包含列，其数据来自于对应的真实表
2. 通过视图可以修改基表的数据

视图的基本使用

1. creat view 视图名 AS select 语句（创建视图）
2. alter view 视图名 AS select 语句（更新视图）
3. show create view 视图名（查看创建视图的指令）
4. drop view 视图名1，视图名2（删除视图）

CREATE VIEW emp_01 AS SELECT job_Salary FROM employee
DESC emp_01
SELECT * FROM emp_01

细节

1. 创建视图之后，到数据库去看，对应视图只有一个视图结构文件
2. 视图中可以再用视图

MySQL管理

MySQL用户管理

当我们做项目开发时，可以根据不同的开发人员，赋给他们相应的操作权限

1. 创建一个新用户

CREATE USER 'jack' @'localhost' IDENTIFIED BY '123456'

‘jack’ 和’localhost’为他的用户名和登录的ip地址，后面的123456是密码

存放的信息是加密存放的

2. 删除一个用户

DROP USER 'jack' @'localhost'

3. 登录
   登录不同的使用用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象都不一样。

4. 修改密码

当你要修改自己的密码直接 set password =password（“密码”）即可

当你要修改其他人的密码必须得有操作权限
set password for ‘用户名’ @‘登录位置’=password（“密码”）

MySQL中的权限管理

基本语法

赋予权限：

grant 权限列表 on 库.对象名 to ‘用户名’@‘登录位置’ 【identified by ‘密码’】

回收权限：

revoke 权限列表 on 库.对象名 to ‘用户名’@‘登录位置’

如果权限没有生效，可以执行下面的命令（刷新指令）

FLUSH PRIVILEGES

说明

1. 权限列表，多个权限使用逗号分开

2. *.* 表示本系统中的私有数据库的所有对象  库.*表示某个数据库中的所有对象
   

3. identified by可写可不写

细节

1. 在创建用户的时候，如果没有指定host，则为%。%指所有ip都有权限连接这个用户
2. 在删除用户时，如果host不是%，要明确指定‘用户’@‘host’