MySQL基础知识

第一章 数据库简介

• 数据库(Database，DB)
  是按照数据结构来组织，存储和管理数据的仓库。
• 典型特征：
  数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性。
• 关系型数据库：
  使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。
• 主流的关系型数据库产品：
  Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。
• 数据表：
  数据表是关系数据库的基本存储结构，二维数据表有行(Row),和列(Column)组成，也叫作记录(行)和字段(列)。

第二章 MySQL数据类型

2.1 数据类型是什么

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，
它决定了数据的存储格式，代表了不同的信息类型

2.2 常见数据类型

MySQL支持多种类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串(字符)类型

• 数值型

• 浮点型

• 日期型：表示时间值的日期和时间类型
  

• 字符型：字符串类型指CHAR、VARCHAR

第三章 SQL语句分类

• Sql语句简介

1、SQL : Structured Query Language ,即结构化查询语言
2、SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言
3、可以用来查询数据，操纵数据，定义数据，控制数据

• Sql语句分类

数据定义语言(DDL)：Data Definition Language
数据操纵语言(DML)：Data Manipulation Language
数据查询语言(DQL)：Data Query Language
数据控制语言(DCL)：Data Control Language
事务控制语言(TCL)：Transaction Control Language

3.1 数据定义语言—DDL

• DDL的关键字：create,alter,drop

create （database/tavble） 创建库 创建表
alter (table) 添加、删除、修改数据表字段（列）
drop (database/table) 删除库 删除表

• 操作库相关的DDL

1.创建数据库

CREATE DATABASE mybase;

2.创建数据库并指定字符集

CREATE DATABASE mybase CHARACTER SET UTF8;

3.查看所有数据库

SHOW DATABASES;

4.查看当前使用的数据库

SELECT DATABASE();

5.修改数据库编码

ALTER DATABASE mybase CHARACTER SET UTF8;

6.删除数据库

DROP DATABASE mybase;

7 切换数据库
USE mybase;

• 操作表相关DDL

1.创建表

create table exam(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
english INT,
chinese INT,
math int
);

2.查看数据库中所有表

show TABLES;

3.查看表结构

desc exam;

4.表的删除

drop table exam;

• 表的修改相关的DDL

6.添加列

ALTER TABLE exam ADD history INT NOT NULL;

7.修改列的类型、长度、约束

ALTER TABLE exam MODIFY history DOUBLE(7,2);

8.修改表的列名

ALTER TABLE exam CHANGE history physics INT NOT NULL;

9.修改表名

RENAME TABLE exam TO score;

10.修改表的字符集

ALTER TABLE score CHARACTER SET UTF8;

11.删除列

ALTER TABLE score DROP physics;

3.2 数据操作语言—DML

• DML的关键字： update,insert,delete

1、update ：修改表记录
2、insert：插入表记录
3、delete：删除表记录

• DML基础操作语句

1.插入部分列

INSERT 
INTO score(id,name,english,chinese,math) 
VALUE(1,'Hudie',90,90,90);

INSERT 
INTO score(id,name,english,chinese) 
VALUE(NULL,'diedie',91,91);

2.插入所有列

INSERT INTO score VALUES(3,‘Shu’,80,80,80);

3.修改记录

全表修改
UPDATE score set chinese=99; 
根据条件修改
UPDATE score SET math=100 WHERE id=1;

4.删除记录

删除全部数据
DELETE FROM score;
根据条件删除
DELETE FROM score WHERE id=2;

3.3 数据控制语言—DCL

• DCL的关键字：grant,revoke

grant ： 授予权限
revoke ：撤销权限

• 基础DCL语句用法

1.创建用户
格式：
CREATE USER 用户名@ip IDENTIFIED BY 密码;

create user Fox@localhost identified by ‘123456’;

2.给用户授权
格式：
grank 权限1,权限2,…,权限n ON 数据库名.* TO 用户名@IP;

grant select,drop on mysql.* to Fox@localhost;

3.撤销权限
格式：
REVOKE 权限1,权限2,…,权限n ON 数据库名.* FROM 用户名@IP;

revoke select on mysql.* from Fox@localhost;

4.查看用户的权限
格式：
SHOW GRANTS FOR 用户名@IP;

show grants for Fox@localhost;

5.删除用户
格式：
DROP USER 用户名@IP;

drop user Fox@localhost;

6.登录:
格式：
mysql -u 用户名-p 密码

mysql -uroot -proot

7.退出登录

exit;

3.4 数据查询语言—DQL

• DQL关键字：select

select :查询

创建数据库
create table exam(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	english INT,
	chinese INT,
	math int
);

插入数据
INSERT INTO exam VALUES(NULL,'张三',112,100,88);
INSERT INTO exam VALUES(NULL,'李四',90,90,97);

1.全表查询
格式：
SELECT * FROM 表;

SELECT * FROM exam;

2.查询部分字段
格式：
SELECT 字段1,字段2,字段3… FROM 表;

SELECT name,english,math FROM exam;

3.过滤重复字段行
格式：
SELECT [DISTINCT] |列名 FROM 表;

SELECT DISTINCT math FROM exam;
SELECT DISTINCT name,math FROM exam;

4.查询字段起别名
SELECT 字段 AS 新字段名,字段 新字段名 FROM 表;

SELECT name,english AS english_score 
FROM exam;

5.查询指定字段

SELECT name,english,chinese 
FROM exam 
WHERE name=‘张三’;

6.使用表达式+、-、 *  、/

将所有人的英语成绩减去20分
SELECT id,name,english-20 AS _english FROM exam;
求出英语、数学和语文的总成绩
SELECT name,english+math+chinese FROM exam;

7.模糊查询 _ 和 %

下划线的用法：
其中一个 _ 下划线表示单个任意字符
两个 _ 下划线表示两个任意字符

%号码的用法
%可以在字符前，也可以在后，%表示任意一个或多个字符
如：
'张%' :表示匹配以张开头的所有数据
'%火' :表示匹配最后一个字是火的所有数据
'%刘%'：表示匹配所有带刘的数据

SELECT * FROM exam WHERE name LIKE ‘张_’;
SELECT * FROM exam WHERE name LIKE ‘%%’;

8.使用and,or

and并且
or或者

SELECT *
FROM exam 
WHERE english > 90 AND chinese >90;

SELECT *
FROM exam 
WHERE english < 90 or math >99;

9.使用in,not in

in等价与or，在范围内
not in 相反，表示不在范围内

SELECT * FROM exam WHERE id=2 OR id=3 OR id=4;
SELECT * FROM exam where id IN(2,3,4);
SELECT * FROM exam where id not IN(2,3,4);

10.使用between…and

between...and 在期间

SELECT *
FROM exam 
WHERE English BETWEEN 90 AND 100;

11.非空判断：is null,is not null

 is null ： 是空
 is not null ：不为空  非空

INSERT INTO exam(id,NAME) VALUES(NULL,NULL);
SELECT * FROM exam WHERE NAME IS NULL;
SELECT * FROM exam WHERE NAME IS not NULL;

12.排序查询

ASC 升序查询
DESC 降序查询

SELECT * FROM exam ORDER BY chinese ASC;
SELECT * FROM exam ORDER BY chinese DESC;

<-- 如果英语成绩相同,按照汉语成绩降序排列 -->
SELECT *
FROM exam 
ORDER BY english DESC,chinese DESC; 

SELECT * 
FROM exam 
WHERE name LIKE ‘张%’ ORDER BY english ASC;

13.聚合函数

sum() 求和函数
count() 计数函数
max() 求最大值函数
min() 求最小值函数
avg() 求平均值函数

SELECT SUM(english+math+chinese) FROM exam;
SELECT COUNT(id) FROM exam WHERE name IS NOT NULL;
SELECT MAX(english) FROM exam;
SELECT MIN(english) FROM exam;
SELECT AVG(english) FROM exam ;

3.5 分组查询与分页查询

1 创建表

CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL
);

2、插入数据

INSERT INTO `emp` VALUES ('1002', '白展堂', 'clerk', '1001', '1983-05-09', '7000.00', '200.00', '10');
INSERT INTO `emp` VALUES ('1003', '李大嘴', 'clerk', '1002', '1980-07-08', '8000.00', '100.00', '10');
INSERT INTO `emp` VALUES ('1004', '吕秀才', 'clerk', '1002', '1985-11-12', '4000.00', null, '10');
INSERT INTO `emp` VALUES ('1005', '郭芙蓉', 'clerk', '1002', '1985-03-04', '4000.00', null, '10');
INSERT INTO `emp` VALUES ('2001', '胡一菲', 'leader', null, '1994-03-04', '15000.00', null, '20');
INSERT INTO `emp` VALUES ('2002', '陈美嘉', 'manger', '2001', '1993-05-24', '10000.00', '300.00', '20');
INSERT INTO `emp` VALUES ('2003', '吕子乔', 'clerk', '2002', '1995-05-19', '7300.00', '100.00', '20');
INSERT INTO `emp` VALUES ('2004', '张伟', 'clerk', '2002', '1994-10-12', '8000.00', '500.00', '20');
INSERT INTO `emp` VALUES ('2005', '曾小贤', 'clerk', '2002', '1993-05-10', '9000.00', '700.00', '20');
INSERT INTO `emp` VALUES ('3001', '刘梅', 'leader', null, '1968-08-08', '13000.00', null, '30');
INSERT INTO `emp` VALUES ('3002', '夏冬梅', 'manger', '3001', '1968-09-21', '10000.00', '600.00', '30');
INSERT INTO `emp` VALUES ('3003', '夏雪', 'clerk', '3002', '1989-09-21', '8000.00', '300.00', '30');
INSERT INTO `emp` VALUES ('3004', '张一山', 'clerk', '3002', '1991-06-16', '88000.00', '200.00', '30');

3 分组查询

1.查询每个部门的平均工资

SELECT deptno,AVG(sal) 
FROM emp 
GROUP BY deptno;

2.查询每个职位的最高工资和最低工资

SELECT job,MAX(sal),MIN(sal) 
FROM emp 
GROUP BY job;

3.查询每个部门每种职位的最高工资

SELECT deptno,job,MAX(sal) 
FROM emp 
GROUP BY deptno,job;

4.查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示

SELECT deptno,MAX(sal)AS max_sal 
FROM emp 
GROUP BY deptno 
HAVING max_sal>=15000;

5.查询每个部门的平均工资

SELECT deptno,AVG(sal) 
FROM emp 
GROUP BY deptno 
HAVING AVG(sal)>9000;

4 分页查询

1.从第0条开始，显示3条数据
 (数据库表记录的下标从0开始)

SELECT * FROM emp LIMIT 0,3;

2.查看工资最高的前十个职员信息

SELECT *
FROM emp 
ORDER BY sal DESC LIMIT 0,10;

第四章 完整性约束

4.1 约束分类

• 主键约束：primary key (默认就是唯一非空的)
• 唯一约束：unique
• 非空约束：not null
• 外键约束: 用于在两个表之间建立关系，需要指定引用主表的哪一列。
  • 如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表).
  • 外键是用来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败

4.2 关联emp表

1、创建表

CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);

2、插入数据

INSERT INTO dept VALUES
(10,'餐饮部','上海'),
(20,'销售部','浙江'),
(30,'财务部','北京'),
(40,'技术部','深圳');

3、为从表emp加外键

ALTER TABLE emp 
ADD FOREIGN KEY (deptno) 
REFERENCES dept(deptno);

第五章 多表关联查询

5.1 笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员.

现在，我们有两个集合A和B。

A = {0,1}     B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’

5.2 内连接

内连接:
内连接合并具有同一列(同一列：指的是A的外键是B的主键，即相同字段)的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

语法1：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON A.key = B.key;

语法2：
SELECT 字段列表
FROM A表,B表
WHERE A.key = B.key;

案例：查询emp表和dept表中相关联的数据

方式一：
SELECT * FROM
emp INNER JOIN dept
ON emp.deptno = dept.deptno;

方式二：
SELECT * 
FROM emp,dept
WHERE emp.deptno = dept.deptno;

5.3 左外连接

两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行 ，
这种连接称为左外连接。在右表中没有匹配的行时, 结果表中相应的列为空NULL

语法：
SELECT * FROM A LEFT OUTER JOIN B ON A.key = b.key;

案例：
查询所有员工对应的员工和部门信息，如果没有部门则显示为null

SELECT *
FROM emp LEFT 
OUTER JOIN dept ON dept.deptno=emp.deptno;

5.4 右外连接

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行 ，
这种连接称为右外外连接。在左表中没有匹配的行时, 结果表中相应的列为空NULL

语法：
SELECT * FROM A right OUTER JOIN B ON A.key = b.key;

案例：
查询所有部门对应的员工，显示员工和部门信息，如果部门没有对应的员工信息就显示为null

SELECT *
FROM emp 
RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;

5.5 全外连接

全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据

由于mysql不支持FULL JOIN，于是我们需要用 LEFT JOIN UNION RIGHT JOIN代替

UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重

语法：
SELECT * FROM A LEFT JOIN B ON A.key = B.key
UNION
SELECT * FROM A RIGHT JOIN B ON A.key = B.key;

案例：查询获取两张表中的所有数据，即：A∪B

SELECT *
FROM emp 
LEFT OUTER JOIN dept ON dept.deptno=emp.deptno
UNION
SELECT *
FROM emp 
RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;

案例：查询不在A表同时不在B表中的数据，即：A∩B

SELECT *
FROM emp 
LEFT OUTER JOIN dept ON dept.deptno=emp.deptno
WHERE dept.deptno IS NULL
UNION
SELECT *
FROM emp 
RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno
WHERE emp.deptno IS NULL;

5.7 子查询

• 1、为什么会使用子查询

虽然可以通过连接查询来实现多表查询数据记录，但不建议使用，因为连接查询的性能很差。为什么呢？
接下来进行分析，例如 我们要查询部门表dept和员工表emp中的数据记录，一般可能会写成：

select * 
from emp e,dept d
where emp.deptno = dept.deptno;

第一步：对于这条SQL语句，在数据库执行的时候，会先对两个表进行笛卡尔积操作，
第二步：然后再选取符合条件 e.deptno=d.deptno的数据记录。
原理:由于笛卡尔积时是将两个表中的记录数做乘积生成新的记录，
	  如果当两个表中的数据记录都比较多时，进行乘积操作时性能将会很差，
	  甚至造成死机。为了解决该问题，我们可以使用子查询来实现多表查询。

• 2、什么是子查询

1、子查询就是在一个查询中嵌套了其他若干查询，
2、即在一个SELECT查询语句的FROM或WHERE字句中包含另一个SELECT查询语句，
3、在这种嵌套的查询语句中，外层的SELECT查询语句称为主查询，
4、WHERE或FROM中的查询语句称为子查询，也叫嵌套查询。
5、通过子查询可以实现多表查询，子查询经常出现在WHERE或FROM字句中。

• 3、子查询的分类
  • where 子句的子查询
  • form 子句的子查询

WHERE子句中的子查询：
	该位置的子查询一般返回单行单列，多行单列。
	就是返回能够作为WHERE子句查询条件的值。
	(子查询返回的值作为主查询的查询条件)

FROM子句中的子查询：
	该位置的子查询一般返回多行多列数据，
	相当于是返回一张临时表，符合FROM子句后面是表的规则，
	就是通过这种方式来实现多表查询的。

• 4、where子查询：单行单列 和 多行单列情况
• 单行单列—把子查询单行单列的结果作为主查询的条件

工作地点在上海的员工

SELECT *
FROM emp 
WHERE deptno =
(SELECT deptno FROM dept WHERE loc ='上海');

• 多行单列—把子查询多行单列的结果作为主查询的条件

工作地点不在上海的员工

SELECT *
FROM emp 
WHERE deptno in
(SELECT deptno FROM dept WHERE loc <> '上海');

• 5、form子查询：多行多列情况

FROM子句后的子查询返回的结果为多行多列的数据记录，
就类似一个虚拟的表，可以使用该种方式实现多表查询。

5.8 多表查询案例

1.查看每个员工的名字以及其所在部门的名字

SELECT emp.ename,dept.dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;

2.查看工作地点在北京的员工有哪些

SELECT *
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE dept.loc ='北京';

3.查看每个城市员工的平均工资

SELECT dept.loc,ifnull(avg(sal),0)
FROM emp right join dept
on emp.deptno =dept.deptno
GROUP BY dept.loc;

4.查看工作地点在上海的员工有哪些

SELECT emp.ename,dept.dname,dept.loc
FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
WHERE dept.loc='上海';

5.查找和曾小贤同职位的员工

SELECT *
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='曾小贤');

6.查找薪水比整个机构平均水平高的员工

SELECT *
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);

7.查询出部门中有clerk但职位不是clerk的员工的信息

SELECT *
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno 
FROM emp 
WHERE job='clerk') AND job!='clerk';

8.查看每个城市员工的平均工资

SELECT dept.loc,AVG(sal)
FROM emp INNER JOIN dept 
ON emp.deptno=dept.deptno 
GROUP BY dept.loc;

9.查询出最低薪水高于部门20的最低薪水的部门信息

SELECT deptno,MIN(sal) AS MIN_sal
FROM emp
GROUP BY deptno
HAVING min_sal > 
(SELECT MIN(sal) FROM emp WHERE deptno=20);

10.列出所有员工的姓名及其直接上级的姓名

SELECT e1.ename,e2.ename
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;

第六章 注意事项

• 6.1 delete与truncate的区别

truncate table 删除表的记录:将整个表删除掉,
				重新创建一个新的表,属于DDL.
delete from 删除表的记录:一条一条进行删除

• 6.2 delete、truncate、drop的区别

delete、truncate、只是删除表的记录,
drop会直接删除表.

• 6.3 Havaing子句与where子句的区别

(1)where是用来过滤记录的,HAVING是用来过滤分组的
(2)过滤的时机不相同,先过滤Where后过滤Having.
(3)WHERE是在查询表时逐行过滤以选取满足条件的记录
(4)having是在数据查询后并且分完组后对分组进行过滤的
(5)having必须跟在group BY
(6)查询语句执行顺序:
  1   from 
  2   on 
  3   join 
  4   where 
  5   group by
  6   avg,sum.... 
  7   having 
  8   select 
  9   distinct 
  10  order by
  11  limit 

第七章 三大范式

• 第一范式（1NF）
  - 强调的是列的原子性，列不可再分割
• 第二范式（2NF）
  • 满足第一范式的条件下。确保表中每列都和主键相关 ，除了主键以外其它列，都依赖该主键
• 第三范式（3NF）
  • 满足第二范式的条件下：非主键必须直接依赖于主键，不能存在传递依赖

第八章 数据库事务

8.1 数据库事务概念

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行成功，要么都不执行（回滚）

8.2 事物四大特性(ACID)

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。