数据库总结

IT自习小空间

已于 2022-03-19 13:27:39 修改

阅读量656

点赞数

分类专栏： Mysql 文章标签：数据库 mysql database

于 2022-01-08 19:35:56 首次发布

本文链接：https://blog.csdn.net/jiangnb520/article/details/122193364

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL数据库的下载配置、基本操作，包括创建、查看、删除数据库及表，以及CRUD语句的使用。此外，还涵盖了函数、查询加强、索引、事务、视图和用户管理等内容，深入解析了数据库管理和操作的关键知识点。

摘要由CSDN通过智能技术生成

文章目录

一、数据库简介
- 1、数据库下载和配置
- 2、Mysql结构
二、数据库操作
- 1、创建数据库
- 2、查看、删除数据库
- 3、备份、恢复数据库
三、表的操作
- 1、创建、删除表
- 2、修改表
四、CRUD语句
- 1、insert语句（添加数据）
- 2、update语句（更新数据）
- 3、delete语句（删除语句）
- 4、select语句（查找数据）
五、函数
- 1、统计函数
- 2、分组统计
- 3、字符串相关函数
- 4、数学相关函数
- 5、时间日期函数
- 6、流程控制语句
六、查询加强
- 1、使用where子句
- 2、使用order by子句
- 3、分页查询
- 4、增强group by
- 5、多表查询
- 6、自连接
- 7、子查询
- 8、自我复制
- 9、合并查询
- 10、外连接
七、Mysql约束
- 1、primary key（主键）
- 2、unique
- 3、foreign key（外键）
- 4、自增长
八、Myslq索引
- 1、添加索引
- 2、删除索引
九、Mysql事务
- 1、事务ACID特性
- 2、事务操作
- 3、隔离级别
十、视图
十一、用户管理

一、数据库简介

数据库(detabase)是一个以某种有组织的方式存储的数据集合。

人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，它是引起混淆的根源。确切地说，数据库软件应称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。

1、数据库下载和配置

① Mysql下载

进入下载界面，当前我们最常见的版本是5.7，所以点击下方红色方框的内容，查看早期版本。
在这里插入图片描述
②安装完成之后，在环境变量中系统变量的path里添加mysql的路径！

③接着在Mysql路径下添加一个my.ini的文件
在这里插入图片描述
my.ini文件内容如下

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置自己mysql的安装目录
basedir=D:\mysql-5.7.36-winx64\
# 设置mysql的数据目录
datadir=D:\mysql-5.7.36-winx64\\data\
port=3306
character_set_server=utf8
# 跳过安全检查
skip-grant-tables

④以管理员身份打开cmd，切换到D:\mysql-5.7.36-winx64\bin目录下，执行mysqld-install

⑤初始化数据库：mysqld --initialize-insecure --user=mysqld

执行成功后，会生成data目录

⑥启动mysql服务：net start mysql

⑦进入mysql：mysql -u root -p

⑧修改root用户密码为123

use mysql;
update user set authentication_string=password('123')where user='root' and Host='localhost;
flush privileges;

⑨修改my.ini内容

将这句注释掉
#skip-grant-tables

2、Mysql结构

在这里插入图片描述

二、数据库操作

1、创建数据库

创建一个使用utf8字符集，并带有排序规则的db01数据库

# CHARACTER SET: 指定字符集，如果不指定默认为utf8
# COLLATE: 指定字符集的校对规则（常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写](默认)）
CREATE DATABASE db01 CHARACTER SET utf8 COLLATE utf8_bin

2、查看、删除数据库

查看语句

# 查看当前数据库服务器中所有数据库
SHOW DATABASES
# 查看前面创建的db01数据库的定义信息
SHOW CREATE DATABASE db01

删除语句

DROP DATABASE db01

注意

# 在创建数据库，表的时候，为了规避关键字，可以使用反引号
CREATE DATABASE `CREATE`

3、备份、恢复数据库

# 备份数据库（在DOS执行）因为mysqldump在myslq的bin目录下
mysqldump -u root -p -B 数据库1 数据库2 ... > 文件名.sql

# 恢复数据库（在SQLyog执行）
Source 文件名.sql

# 备份db01中的users表 不要带-B，带上-B会以为db01和users都是数据库
mysqldump -u root -p db01 users > d:\\bak.sql

三、表的操作

1、创建、删除表

创建表
结构

CREATE TABLE table_name(
	field1 datatype,
	field2 datatype,
	field3 datatype,
)CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎;
# table_name为表名，field为列名，datatype为数据类型。

# 创建一个goos表
CREATE TABLE goods(
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE
);

Mysql常见数据类型

类型	数据类型
数值类型	BIT(M) M指定位数，默认为1，范围1-64 TINYINT[1字节] SMALLINT[2字节] MEDIUMINT[3字节] INT[4字节] BIGINT[8字节] FLOAT[4字节] DOUBLE[8字节] DECIMAL(M,D) M指定长度，D表示小数点的位数
文本、二进制类型	CHAR(SZIE) CHAR(20) 最大255 VARCHAR(SIZE) VARCHAR(20) 0-65535 BLOB 0-2^16-1 LONGBLOB 0-2^32-1 TEXT 0-2^16 LONGTEXT 0-2^32
日期类型	DATE/DATETIME/TIMESTAMP

注意：varchar和char

char(4)是定长也就是说存放’aa’也会分配4字节空间
varchar(4)是变长就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配，varchar本身还需要占用1-3字节来记录存放内容长度。

# date、datetime、timestamp
CREATE TABLE t1 (
  birthday DATE,    -- 生日
  jobtime DATETIME,   -- 记录年月日
  login_time TIMESTAMP 
	NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 登陆时间
) ;

# 如果没有写时间戳，会自动填写当前的时间戳
INSERT INTO t6 (birthday,jobtime,login_time)
  VALUES('2021-12-24','2021-12-24 9:17:50','2021-12-24 9:17:51');

删除表

DROP TABLE t1;

2、修改表

首先创建一个t1表

CREATE TABLE t1 (
	id INT,
	`name` VARCHAR(30),
	sex CHAR(3),
	brithday DATE,
	entry_date DATE,
	job VARCHAR(255),
	Salary FLOAT,
	`resume` TEXT);
 
INSERT INTO t8
  VALUES(
	001,
	'小姜',
	'男',
	'2000-06-01',
	'2022-08-20',
	'Java工程师',
	15000.80,
	'一个超级幸运的人！');
	
SELECT * FROM t1

①添加列

结构

ALTER TABLE tablename
	ADD field datatype [default expr],
	ADD field datatype [default expr],
	...;

# 在t1表中resume后面添加image 并且不为空 默认给'' 
ALTER TABLE t1
	ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER `resume`

显示表结构

DESC t1 -- 显示表结构

②修改列

# 修改job类型为60
ALTER TABLE t1
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''

③删除列

# 删除image
ALTER TABLE t8
DROP image

④修改表名

# 修改表名，将表名t1修改为employee
RENAME t1 TO employee

⑤修改字符集

# 修改字符集
ALTER TABLE t1 CHARACTER SET utf8

⑥修改表中name为user_name

# 修改t1表中name 为 user_name
ALTER TABLE t1 CHANGE `name` `user_name` VARCHAR(20) NOT NULL DEFAULT ''

四、CRUD语句

C[create]、R{read]、U[update]、D[delete]

1、insert语句（添加数据）

结构

INSERT INTO table_name (column, column, ...)
	values (value, value, ...),(value, value, ...),...;

注意：

字符和日期数据应包含在单引号中
列可以插入空值[前提是该字段允许为空]
如果是给表中所有字段添加数据，可以不写前面的字段名称
默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

首先创建一个数据库

CREATE TABLE goods(
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE
);

添加数据

INSERT INTO goods (id, goods_name, price)
	VALUES (10, '华为手机', 4000);

INSERT INTO goods
	VALUES (20, 'iphone手机', 5000);
	
# insert into table_name (列名...) values (),(),() 添加多条记录
INSERT INTO goods
	VALUES (1,'小米手机', 2300),(2,'vivo手机',2500),(3,'三星手机',3000)

2、update语句（更新数据）

结构

UPDATE table_name SET col_name=expr1 where row

set子句指示要修改哪些列和要给哪些值
where子句指定更新哪些行，如没有where子句，则更新所有的行

# 将goods表中id修改为1。[如果没有带where条件，会修改所有的记录]
UPDATE goods
SET id=1
where goods_name='华为手机'

3、delete语句（删除语句）

结构

DELETE FROM table_name
	WHERE row

delete不能删除某一列值，只能删除记录，不能删除整个表

# 删除小李记录 不加where会删除所有记录
DELETE FROM goods
WHERE goods_name = '三星手机'

4、select语句（查找数据）

结构

SELECT [DISTINCT] *|{column1,column2,column3..}
FROM table_name;

select 指定查询哪些列的数据
column指定列名
*代表查询所有列
from 指定查询那张表
distinct 可选，指显示结果时，是否去掉重复数据

五、函数

1、统计函数

①COUNT
count(*)和count(列)的区别

count(*) 返回满足条件的记录行数
count(列) 统计满足条件的某列有多少个，但是会排除null

# 查询学生表math大于80的同学个数
SELECT COUNT(*) FROM student
	WHERE math > 80;

②SUM

SELECT SUM(math) FROM student;

③MAX\MIN

SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;

④AVG

SELECT AVG(chinese) FROM student

2、分组统计

# 创建部门表
CREATE TABLE dept(
 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
 dname VARCHAR(20) NOT NULL DEFAULT "",
 loc VARCHAR(13) NOT NULL DEFAULT ""
);
# 部门表插入数据
INSERT INTO dept VALUES(10, 'ACCONTLNG', 'NEW YORK'),(20,'RESEARCH', 'DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

#创建表EMP雇员
CREATE TABLE emp(
 empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
 ename VARCHAR(20) NOT NULL DEFAULT "" , /*名字*/
 job VARCHAR(9) NOT NULL DEFAULT "", /*工作*/
 mgr MEDIUMINT UNSIGNED , /*上级编号*/
 hiredate DATE NOT NULL, /*入职时间*/
 sal DECIMAL(7,2) NOT NULL, /*薪水*/
 comm DECIMAL(7,2), /*红利*/
 deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
# emp插入数据
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK',7902,'1990-12-17',800.00,NULL, 20),
(7499, 'ALLEN', 'SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521, 'WARD', 'SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
(7566, 'JONES', 'MANAGER',7839,'1991-4-2',2975.00,NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE', 'MANAGER',7839, '1991-5-1',2850.00,NULL, 30),
(7782, 'CLARK', 'MANAGER',7839,'1991-6-9',2450.00,NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566,'1997-4-19',3000.0,NULL, 20),
(7839, 'KING', 'PRESTDENT', NULL, '1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN', 7698,'1991-9-8',1500.00,NULL, 30),
(7900, 'JAMES', 'CLERK', 7698, '1991-12-3',950.00,NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1991-12-3', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1992-1-23', 1300.00, NULL, 10);

# 创建薪水表
CREATE TABLE salgrade (
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
losal DECIMAL (17,2) NOT NULL, /*该级别的最低工资*/
hisal DECIMAL (17,2) NOT NULL /*该级别的最高工资*/
);
# 薪水表插入数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

# 按照部门分组查询
SELECT AVG(sal), MAX(sal), deptno
	FROM emp GROUP BY deptno;

# 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno, job
	FROM emp GROUP BY deptno, job;

# 显示平均工资低于2000的部门号和它的平均工资
SELECT AVG(sal), deptno
	FROM emp GROUP BY deptno
	HAVING AVG(sal) < 2000

3、字符串相关函数

# charset(str) 返回字符串字符集
SELECT CHARSET(ename) FROM emp

# concat(string2 [,...]) 连接字符串
SELECT CONCAT(ename, ' job is', job) FROM emp

# instr(string, substring) 返回substring在string中出现的位置，没有返回0
SELECT INSTR('i am lucky', 'lucky') FROM DUAL;

# ucase(string) 转换成大写
SELECT UCASE(ename) FROM emp;

# lcase(string) 转换成小写
SELECT LCASE(ename) FROM emp;

# left(string , length) 从string中的左边取length个字符
SELECT LEFT('lucky', 2)
SELECT RIGHT('lucky', 2)
SELECT LEFT('小姜', 1)

# length(string) 返回string的长度[字节返回]
SELECT LENGTH('小姜')

# replace(str, search_str, replace_str) 在str中用replace_str替换search_str
SELECT REPLACE('i am lucky', 'lucky', 'good luck');

# strcmp(string1, string2) 逐字符比较两字符大小
SELECT STRCMP('lucky','luck');

# substring(str, position, [length])
# 从str的position开始[从1开始计算]，取length个字符
# 从ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;

# ltrim(string) rtrim(string) trim(string)
# 去掉前端空格或者后端空格
SELECT LTRIM(' lucky');
SELECT RTRIM('lucky ');
SELECT TRIM(' lucky ');

4、数学相关函数

# abs(num) 绝对值
SELECT ABS(-10);

# bin(decimal_number) 十进制转二进制
SELECT BIN(10);

# ceiling(number2) 向上取整， 得到比num2大的最小整数
SELECT CEILING(-1.1);

# conv(number, from_base, to_base) 进制转换
# 十进制的8 转换成2进制
SELECT CONV(8,10,2);
SELECT CONV('a',16,2);

# floor(number) 向下取整，得到比num小的最大整数
SELECT FLOOR(1.1);

# format(number, decimal_places) 保留小数位数
SELECT FORMAT(78.123456, 2);

# hex(decimalNumber) 转十六进制
SELECT HEX(10);

# least(number, number1 ...) 求最小值
SELECT LEAST(12,3,2,4,2,25);

# mod(number, denominator) 求余
SELECT MOD(10,3);

# rand([seed]) rand([seed]) 返回随机数 范围为 0 <= v >= 1.0
#  如果使用rand()每次返回不同的随机数在0 <= v >= 1.0
#  如果使用rand(seed)返回随机数， 范围0 <= v >= 1.0，如果seed不变
#  随机数不变
SELECT RAND(3);

5、时间日期函数

# current_date() 当前日期
SELECT CURRENT_DATE();

# current_time() 当前时间
SELECT CURRENT_TIME();

# current_timestamp() 当前时间戳
SELECT CURRENT_TIMESTAMP();

# 创建测试表 信息表
CREATE TABLE mes(
	id INT,
	comtent VARCHAR(30),
	send_time DATETIME);
INSERT INTO mes
	VALUES(1,'新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2,'新闻', NOW());

SELECT * FROM mes;

# 显示所有新闻信息，发布日期只显示日期，不显示时间
SELECT id, comtent, DATE(send_time) FROM mes;

# 查询10分钟内发布的新闻
SELECT * FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();

# 在mysql的sql语句中求出2011-11-11 和 1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1');

# 请用mysql的sql语句求出你活了多少天
SELECT DATEDIFF(NOW(), '2000-06-01');

# 如果你能活80岁，求出你还能活多少天
# INTERVAL 80 YEAR : YEAR可以是年月日时分秒
SELECT DATEDIFF(DATE_ADD('2000-06-01',INTERVAL 80 YEAR), NOW());

SELECT TIMEDIFF('10:11:11','06:10:10');

# year|month|day|date(datetime)
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT YEAR('2021-10-27');

# unix_tiemstap(); 返回的是1970-1-1到现在毫秒数
SELECT UNIX_TIMESTAMP();

# from_unixtime(); 可以把一个unix_tiemstap() 秒数，转成指定格式
# 在开发中，可以存放一个整数，然后表示时间。
SELECT FROM_UNIXTIME(1640524903, '%Y-%m-%d');
SELECT FROM_UNIXTIME(1640524903, '%Y-%m-%d %H:%i:%s');
SELECT FROM_UNIXTIME(1640524903);

6、流程控制语句

# if(expr1,expr2,expr3) 如果expr1为true，则返回expr2 否则返回expr3
SELECT IF(TRUE, '北京', '上海');

# ifnull(expr1, expr2) 如果expr1 不为空，则返回expr1，否则返回expr2
SELECT IFNULL(NULL, 'xiaojiang');

# select case when expr1 then expr2 when expr3 then expr4 else expr5 end;
# 如果epxr1为true，则返回expr2，如果expr2 为true，返回expr4，否则返回expr5
SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END
# 查询emp表，如果comm是null，则显示0.0
SELECT ename, IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;

SELECT ename, (SELECT CASE
	WHEN job = 'CLERK' THEN '职员'
	WHEN job = 'MANAGER' THEN '经理'
	WHEN job = 'SALESMAN' THEN '销售人员'
	ELSE job END) AS job
	FROM emp;

六、查询加强

1、使用where子句

结构

# 根据条件进行查询
SELECT * FROM 表名
	WHERE 条件

# 查找1992.1.1后入职的员工
SELECT * FROM emp
	WHERE hiredate > '1992-01-01'
	
# 如何使用like操作符
# 	% : 表示0到多个任意字符 
#   _ : 表示单个任意字符
#	如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp
	WHERE ename LIKE 'S%'
#	如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp
	WHERE ename LIKE '__O%'
# 如何显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;

2、使用order by子句

结构

# 根据某一列显示升序和降序
SELECT * FROM 表名
	ORDER BY 列 [DESC降序]|[ASC升序]

# 如何按照工资的从低到高的顺序，显示雇员的信息
SELECT * FROM emp
	ORDER BY sal
# 按照部门号升序 雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
	ORDER BY  deptno ASC, sal DESC;

3、分页查询

结构

# 当数据过多，分页查询数据
# 公式
SELECT * FROM 表名
	ORDER BY 列
	limit 每页显示记录数 * (第几页-1), 每页显示记录数

# 从第一行显示，显示3行
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0, 3;

SELECT * FROM emp
	ORDER BY empno
	LIMIT 3, 3;
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6, 3;
SELECT * FROM emp
	ORDER BY empno
	LIMIT 9, 3;
SELECT * FROM emp
	ORDER BY empno
	LIMIT 12, 3;

4、增强group by

把具有相同的数据值的行放在同一组中。

结构

# 根据某列进行分组
SELECT *
	FROM 表名
	GROUP BY 列;

# 显示每种岗位的雇员总数，平均工资
SELECT COUNT(*), AVG(sal), job
	FROM emp
	GROUP BY job;

# 统计各个部门group by 的平均工资avg
# 并且是大于1000的having， 并且按照平均工资从高到低顺序
# 取出前两行记录
SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0, 2;

5、多表查询

# 查询雇员名，雇员工资以及所在部门的名字[笛卡尔]
SELECT ename, sal, dname
	FROM emp, dept

# 多表查询的条件不能少于 表个数-1 否则会出现笛卡尔积
SELECT ename, sal, dname, emp.`deptno`
	FROM emp, dept
	WHERE emp.`deptno` = dept.`deptno`

6、自连接

自连接特点

把同一张表当做两张表使用
需要给表取别名表名表别名
列名不明确可以指定列的别名列名as列的别名

# 显示公司员工名字和他上级的名字 emp as worker
SELECT worker.`ename`, boss.`ename` FROM emp worker, emp boss
	WHERE worker.`mgr` = boss.`empno`

7、子查询

将一个查询语句当做子查询

# 如何显示与smith同部门的所有员工
# 先查询smith的部门号 在把它当做一个子查询使用
SELECT deptno FROM emp
	WHERE ename = 'SMITH';
SELECT * FROM emp
	WHERE deptno = (
		SELECT deptno FROM emp
		WHERE ename = 'SMITH'
	);

8、自我复制

通过查询自己表中内容，然后将查找内容添加到自身表中。

# 把emp表的记录复制到my_table表中
INSERT INTO my_table (id, `name`, sal, job, deptno)
	SELECT empno, ename, sal, job, deptno FROM emp;

# 自我复制
INSERT INTO my_table
	SELECT * FROM emp;

删除一张表中重复记录

思路

先创建一张临时表my_tmp，该表的结构和my_tab01一样
把my_tmp的记录通过distinct关键字处理后把记录复制到my_tmp
清除掉my_tab01记录
把my_tmp表的记录复制到my_tab01
drop掉临时表my_tmp

CREATE TABLE my_tmp LIKE my_tab01;

INSERT INTO my_tmp
	SELECT DISTINCT * FROM my_tab01;

DELETE FROM my_tab01;

INSERT INTO my_tab01
	SELECT * FROM my_tmp;

DROP TABLE my_tmp;

9、合并查询

# union all就是将两个查询结果合并，不会去重
SELECT ename, sal, job FROM emp WHERE sal>2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job='MANAGER'
# 会自动去掉重复行
SELECT ename, sal, job FROM emp WHERE sal>2500
UNION
SELECT ename, sal, job FROM emp WHERE job='MANAGER'

10、外连接

① 左外连接（左侧的表完全显示）

# 创建一个表
CREATE TABLE stu (
	id INT,
	`name` VARCHAR(32)
);
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3,'kity'),(4,'nono');

CREATE TABLE exam (
	id INT,
	grade INT
);
INSERT INTO exam values(1, 56),(2,48),(11,78);	

# 显示姓名和id号，如果没有成绩，成绩显示为空
SELECT `name`, stu.`id`, grade
	FROM stu LEFT JOIN exam
	ON stu.`id` = exam.`id`;

② 右外连接（右侧的表完全显示）

# 查询成绩、姓名和id号,如果没有姓名，显示为空
SELECT `name`, exam.`id`, grade
	FROM stu RIGHT JOIN exam
	ON stu.`id` = exam.`id`;

七、Mysql约束

约束包括：not null、unique、primary key、foreign key。

1、primary key（主键）

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

primary key 不能重复而且不能为null
一张表最多只能有一个主键，但可以是复合主键
主键的指定方式有两种
直接在字段后指定：字段名 primary key
在表定义最后写 primary key(列名)
使用desc 表名，可以看到primary key的情况

CREATE TABLE t2 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32)
);
CREATE TABLE t2 (
	id INT,
	`name` VARCHAR(32),
	PRIMARY KEY(id, `name`) -- 复合主键
);

2、unique

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

CREATE TABLE t2 (
	id INT UNIQUE,
	`name` VARCHAR(32)
);

3、foreign key（外键）

结构

FOREIGN KEY(本表字段名) REFERENCES
主表名(主键名或unique字段名)

外键指向的表的字段，要求是primary key 或者是unique
表的类型是innodb,这样的表才支持外键
外键字段的类型要和主键字段的类型一致(长度可以不同)
外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
一旦建立主外键的关系，数据不能随意删除了.

示例

# 首先创建主表
CREATE TABLE my_calss (
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
);
# 创建 从表
CREATE TABLE my_stu (
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
	class_id int,
	FOREIGN KEY(calss_id) REFERENCES
my_calss(id)
);

4、自增长

一般自增长是和primary key配合使用
自增长也可以单独使用，需要配合一个unique
自增长修饰的字段为整数型的（虽然小数也可以但是非常少）
自增长默认从1开始，也可以通过命令修改alter table 表名 auto_increment=xxx

CREATE TABLE t3(
	id int PRIMARY KEY AUTO_INCREMENT
	`name` VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO t3
	VALUES(NULL, 'JACK');

八、Myslq索引

当表中数据过大时，在表中创建索引会大大降低查询所消耗的时间。
结构
创建索引后，只对创建了索引的列有效
索引类型

①主键索引，主键自动为主索引（类型primary key）
②唯一索引（unique）
③普通索引（index）
④全文索引（fulltext）

1、添加索引

# 查询表是否存在索引
SHOW INDEX FROM 表名;
# 添加唯一索引（如果列不重复）
CREATE UNIQUE INDEX 索引名称 ON 表名（列）：表示在表的某个列创建唯一索引
# 添加普通索引1
CREATE INDEX 索引名称 ON 表名（列）：表示在表的某个列创建索引
# 添加普通索引2
ALTER TABLE 表名 ADD INDEX 索引名称（列）

2、删除索引

DROP INDEX 索引名称 ON 表名
# 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY

九、Mysql事务

在执行sql语句时，一系列操作必须全部执行，而不能只执行一部分。这些操作的多条语句成为事务。

1、事务ACID特性

原子性(Atomic)
对于操作工作，要么全部执行，要么全部不执行
一致性(Consistent)
事务完成后，所有数据的状态都是一致的
隔离性(Isolation)
如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
持久性(Duration)
事务完成后，对数据库数据的修改被持久化存储。

2、事务操作

-- 开始事务
START TRANSACTION
-- 设置保存点
SAVEPOINT A
-- 回滚
ROLLBACK TO A
-- 直接回退到事务开始的状态
ROLLBACK
-- 提交事务，不能回退
COMMIT

3、隔离级别

多个客户端连接数据库并对其进行事务操作时，数据库系统套负责隔离操作，以保证各个连接在获取数据时的准确性

如果没有隔离，会引发如下问题

脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，发生不可重复读
幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，发生幻读

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交	√	√	√	不加锁
读已提交	×	√	√	不加锁
可重复读	×	×	×	不加锁
可串行化	×	×	×	加锁

设置隔离级别

-- 查看当前会话隔离级别
SELECT @TX_ISOLATION
-- 查看系统当前隔离级别
SELECT @global.TX_ISOLATION
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [级别]

Mysql表类型和存储引擎

Mysql表类型由存储引擎决定，主要包括MyISAM、innoDB、Memory等
Mysql数据表主要支持六种类型，分别是 CSV、Memory、ARCHIVE、MRG_MYISAM、InnoBDB

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

修改存储引擎

ALTER TABLE 表名 ENGIN = INNODB

十、视图

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表。
通过视图可以修改基表的数据
基表的改变，也会影响到视图的数据

1、视图的基本使用

-- 创建视图
CREATE VIEW 视图名 AS SELECT 语句
-- 修改视图
ALTER VIEW 视图名 AS SELECT 语句
-- 查看创建视图
SHOW CREATE VIEW 视图名
-- 删除视图
DROP VIEW 视图名1，视图名2

十一、用户管理

Mysql中的用户，都存储在系统的数据库mysql中user表中
其中user表的重要字段说明

1、host：允许登录的位置，localhost表示该用户只允许本机登录，也可以指定ip地址
2、user：用户名
3、authentication_string：密码，是通过mysql的passwrod()函数加密之后的密码

-- 创建用户
-- CREATE USER 用户名 @允许登录位置 IDENTIFIED BY 密码
CREATE USER 'jsw'@'localhost' IDENTIFIED BY '123'
-- 删除用户
DROP USER 用户名 @允许登录的位置

-- 修改自己的密码
SET PASSWORD = PASSWORD('123')
-- 修改别人的密码 需要同级别权限
SET PASSWORD FOR 'JSW'@'localhost' = PASSWORD('123')

用户权限管理

-- 增加权限 
-- *.* 代表系统中所有数据库中的所有对象
-- 库.* 表示某个数据库中所有数据对象
GRANT UPDATE
	ON t1.id
	TO 'sw'@'localhost'
	[identity by '密码'] # 可以省略
-- 回收用户权限
REVOKE ALL ON t1.id FROM 'sw'@'localhost'