数据库概述
数据库（DataBase，DB）
指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）
指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。
数据库
存储、维护和管理数据的集合
数据库的安装配置
停止mysql服务 net stop mysql 启动mysql服务 net start mysql
安装成功了打开cmd --> mysql -u root -p 密码
修改mysql root用户密码
- 停止mysql服务 运行输入services.msc 停止mysql服务 或者 cmd net
stop mysql- 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
- 新打开cmd 输入mysql -u root -p 不需要密码 use mysql; update user set password=password(‘abc’) WHERE User=‘root’;
- 关闭两个cmd窗口 在任务管理器结束mysqld 进程 5) 在服务管理页面 重启mysql 服务 密码修改完成
sql概述及分类
SQL：Structure Query Language。（结构化查询语言）
SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准。普通话
各数据库厂商在标准的基础上做了自己的扩展。方言
DDL（Data Definition Language）
数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）
数据操作语言，用来定义数据库记录（数据）；
DCL（Data Control Language）
数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）
数据查询语言，用来查询记录（数据）。
mysql语法知识点
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL
是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database
Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL
是开源免费的，并且方便扩展。
DDL操作数据库、表、列（增删改查）
使用的关键字：CREATE、 ALTER、 DROP
创建
Create database mydb1;
Create database mydb2 character set gbk;
Create database mydb3 character set gbk COLLATE gbk_chinese_ci;
查询
查看当前数据库服务器中的所有数据库
Show databases;
查看前面创建的mydb2数据库的定义信息
Show create database mydb2;
删除前面创建的mydb3数据库
Drop database mydb3;
修改
查看服务器中的数据库，并把mydb2的字符集修改为utf8;
alter database mydb2 character set utf8;
删除
Drop database mydb3;
其他
查看当前使用的数据库
Select database();
切换数据库
Use mydb2;
操作数据表
语法：
create table 表名(
字段1 字段类型,
字段2 字段类型,
…
字段n 字段类型
);
常用数据类型
int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型；当存储的数据长度差不多一致或者比较小时建议使用，产生的碎片少；
varchar：可变长度字符串类型； text：字符串类型；
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
当前数据库中的所有表
SHOW TABLES;
查看表的字段信息
DESC employee;
在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image blob;
修改job列，使其长度为60。
ALTER TABLE employee MODIFY job varchar(60);
删除image列,一次只能删一列。
ALTER TABLE employee DROP image;
表名改为user。
RENAME TABLE employee TO user;
查看表格的创建细节
SHOW CREATE TABLE user;
修改表的字符集为gbk
ALTER TABLE user CHARACTER SET gbk;
列名name修改为username
ALTER TABLE user CHANGE name username varchar(100);
删除表
DROP TABLE user ;
DML操作重要
查询表中的所有数据
SELECT * FROM 表名;
DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
INSERT 、UPDATE、 DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null
插入操作：INSERT
语法：
INSERT INTO 表名（列名1，列名2 …）VALUES(列值1，列值2…);
注意：列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
参不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。
练习：
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,‘zhangsan’,‘female’,‘1990-5-10’,10000,‘2015-5-5-’,‘good
girl’);
小知识：
查看数据库编码的具体信息
Show variables like ‘character%’;
临时更改客户端和服务器结果集的编码
Set character_set_client=gbk;
Set character_set_results=gbk;
修改操作 UPDATE
语法：
UPDATE 表名 SET 列名1=列值1，列名2=列值2 WHERE 列名=值
练习：
将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
UPDATE emp SET salary=4000,gender=‘female’ WHERE name=‘lisi’;
将wu的薪水在原有基础上增加1000元。
UPDATE emp SET salary=salary+1000 WHERE gender=‘male’;
删除操作 DELETE
语法 ：
DELETE 表名 【WHERE 列名=值】
练习 ：
删除表中名称为’zs’的记录。
DELETE FROM emp WHERE name=‘zs’;
DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
删除的数据不能找回。执行速度比DELETE快。
DQL操作（重要）查询
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。
查询关键字：SELECT
语法：
SELECT 列名 FROM表名
【WHERE --> BROUP BY -->HAVING–> ORDER BY】
语法：
SELECT selection_list /要查询的列名称/
FROM table_list /要查询的表名称/
WHERE condition /行条件/
GROUP BY grouping_columns /对结果分组/
HAVING condition /分组后的行条件/
ORDER BY sorting_columns /对结果分组/
LIMIT offset_start, row_count /结果限定/
1 基础查询
1.1 查询所有列
SELECT * FROM stu;
1.2 查询指定列
SELECT sid, sname, age FROM stu;
2 条件查询
2.1 条件查询介绍
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=； BETWEEN…AND； IN(set)； IS NULL； AND； OR； NOT；
2.2 查询性别为女，并且年龄50的记录
SELECT * FROM stu
WHERE gender=‘female’ AND ge<50;
2.3 查询学号为S_1001，或者姓名为liSi的记录
SELECT * FROM stu
WHERE sid =‘S_1001’ OR sname=‘liSi’;
2.4 查询学号为S_1001，S_1002，S_1003的记录
SELECT * FROM stu
WHERE sid IN (‘S_1001’,‘S_1002’,‘S_1003’);
2.5 查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM tab_student
WHERE s_number NOT IN (‘S_1001’,‘S_1002’,‘S_1003’);
2.6 查询年龄为null的记录
SELECT * FROM stu
WHERE age IS NULL;
2.7 查询年龄在20到40之间的学生记录
SELECT *
FROM stu
WHERE age>=20 AND age<=40;
或者
SELECT *
FROM stu
WHERE age BETWEEN 20 AND 40;
2.8 查询性别非男的学生记录
SELECT *
FROM stu
WHERE gender!=‘male’;
或者
SELECT *
FROM stu
WHERE gender<>‘male’;
或者
SELECT *
FROM stu
WHERE NOT gender=‘male’;
2.9 查询姓名不为null的学生记录
SELECT *
FROM stu
WHERE NOT sname IS NULL;
或者
SELECT *
FROM stu
WHERE sname IS NOT NULL;
3 模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
通配符:
_ 任意一个字母
%：任意0~n个字母
‘张%’
3.1 查询姓名由5个字母构成的学生记录
SELECT *
FROM stu
WHERE sname LIKE ‘_____’;
模糊查询必须使用LIKE关键字。其中 “”匹配任意一个字母，5个“”表示5个任意字母。
3.2 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT *
FROM stu
WHERE sname LIKE ‘____i’;
3.3 查询姓名以“z”开头的学生记录
SELECT *
FROM stu
WHERE sname LIKE ‘z%’;
其中“%”匹配0~n个任何字母。
3.4 查询姓名中第2个字母为“i”的学生记录
SELECT *
FROM stu
WHERE sname LIKE ‘_i%’;
3.5 查询姓名中包含“a”字母的学生记录
SELECT *
FROM stu
WHERE sname LIKE ‘%a%’;
4 字段控制查询
4.1 去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：
SELECT DISTINCT sal FROM emp;
4.2 查看雇员的月薪与佣金之和
因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。
SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：
SELECT *,sal+IFNULL(comm,0) FROM emp;
4.3 给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时，是可以省略AS关键字的：
SELECT *,sal+IFNULL(comm,0) total FROM emp;
5 排序
5.1 查询所有学生记录，按年龄升序排序
SELECT *
FROM stu
ORDER BY sage ASC;
或者
SELECT *
FROM stu
ORDER BY sage;
5.2 查询所有学生记录，按年龄降序排序
SELECT *
FROM stu
ORDER BY age DESC;
5.3 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;
6 聚合函数 sum avg max min count
聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数； MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
6.1 COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数：
SELECT COUNT(*) AS cnt FROM emp;
查询emp表中有佣金的人数：
SELECT COUNT(comm) cnt FROM emp;
注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。
查询emp表中月薪大于2500的人数：
SELECT COUNT(*) FROM emp
WHERE sal > 2500;
统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
查询有佣金的人数，以及有领导的人数：
SELECT COUNT(comm), COUNT(mgr) FROM emp;
6.2 SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和：
SELECT SUM(sal) FROM emp;
查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal), SUM(comm) FROM emp;
查询所有雇员月薪+佣金和：
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
统计所有员工平均工资：
SELECT AVG(sal) FROM emp;
6.3 MAX和MIN
查询最高工资和最低工资：
SELECT MAX(sal), MIN(sal) FROM emp;
7 分组查询
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注：凡和聚合函数同时出现的列名，则一定要写在group by 之后
7.1 分组查询
查询每个部门的部门编号和每个部门的工资和：
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*)
FROM emp
WHERE sal>1500
GROUP BY deptno;
7.2 HAVING子句
查询工资总和大于9000的部门编号以及工资和：
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
注：having与where的区别:
1.having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。
8 LIMIT
LIMIT用来限定查询结果的起始行，以及总行数。
8.1 查询5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
注意，起始行从0开始，即第一行开始！
8.2 查询10行记录，起始行从3开始
SELECT * FROM emp LIMIT 3, 10;
8.3 分页查询
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；
8.3 分页查询
查询语句书写顺序：
select – from- where- group by- having- order by-limit
查询语句执行顺序：
from - where -group by - having - select - order by-limit
数据的完整性
作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：
实体完整性:
域完整性:
引用完整性:
1 实体完整性
实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto_increment)
1.1主键约束（primary key）
注：每个表中要有一个主键。
特点：数据唯一，且不能为null
例：
第一种添加方式：
CREATE TABLE student(
id int primary key,
name varchar(50)
);
第二种添加方式：此种方式优势在于，可以创建联合主键
CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid，stuid)
);
第三种添加方式：
CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (id);
1.2唯一约束(unique)：
特点：数据不能重复。
CREATE TABLE student( Id int primary key,
Name varchar(50) unique
);
1.3自动增长列(auto_increment)
sqlserver数据库 (identity) oracle数据库( sequence)
给主键添加自动增长的数值，列只能是整数类型
CREATE TABLE student(
Id int primary key auto_increment,
Name varchar(50)
);
INSERT INTO student(name) values(‘tom’);
2 域完整性
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)
1.1 数据类型:（数值类型、日期类型、字符串类型）
1.2 非空约束：not null
CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);
1.3 默认值约束 default
CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10) default ‘男’
);
insert into student1 values(1,‘tom’,‘女’);
insert into student1 values(2,‘jerry’,default);
引用完整性（参照完整性）
外键约束：FOREIGN KEY
例：
CREATE TABLE student(
sid int pirmary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);
create table score(
id int,
score int,
sid int , – 外键列的数据类型一定要与主键的类型一致
CONSTRAINT fk_score_sid foreign key (sid) references student(id)
);
第二种添加外键方式。
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid)
REFERENCES stu(id);
表与表之间的关系
一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：
在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。
一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！
多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。
多表查询
多表查询有如下几种：
合并结果集；UNION 、 UNION ALL
连接查询
内连接 [INNER] JOIN ON
外连接 OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接（MySQL不支持）FULL JOIN
自然连接 NATURAL JOIN
子查询
1 合并结果集
作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
UNION：去除重复记录，例如：
SELECT * FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：
SELECT * FROM t1 UNION ALL SELECT * FROM t2。
要求：被合并的两个结果：列数、列类型必须相同。
2 连接查询 （非常重要）
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。
也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。
使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
还可以为表指定别名，然后在引用列时使用别名即可。
SELECT e.ename,e.sal,e.comm,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno;
2.1 内连接
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;
内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：
其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。
2.2 外连接（左连接、右连接）
外连接的特点：查询出的结果存在不满足条件的可能。
左连接：
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。
2.3 右连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;
连接查询心得：
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
3 自然连接
大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：
两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
4 子查询（非常重要）
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：
where后，作为条为被查询的一条件的一部分；
from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字：
any
all
子查询结果集的形式：
单行单列（用于条件）
单行多列（用于条件）
多行单列（用于条件）
多行多列（用于表）
练习：
工资高于JONES的员工。
分析：
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES'
第二步：查询高于甘宁工资的员工
SELECT * FROM emp WHERE sal > (${第一步})
结果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')
2、查询与SCOTT同一个部门的员工。
子查询作为条件
子查询形式为单行单列
3、工资高于30号部门所有人的员工信息
分析：
SELECT * FROM emp WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30);
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (${第一步})
结果：
SELECT * FROM emp WHERE
sal > ALL (SELECT sal FROM emp WHERE deptno=30)
子查询作为条件
子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）
4、查询工作和工资与MARTIN（马丁）完全相同的员工信息
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询
第一步：查询出MARTIN的工作和工资
SELECT job,sal FROM emp WHERE ename='MARTIN'
第二步：查询出与MARTIN工作和工资相同的人
SELECT * FROM emp WHERE (job,sal) IN (${第一步})
结果：
SELECT * FROM emp WHERE (job,sal)
IN (SELECT job,sal FROM emp WHERE ename='MARTIN')
5、有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp
GROUP BY mgr HAVING COUNT(mgr)>=2);
子查询作为条件
子查询形式为单行多列
5、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
分析：（无需子查询）
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
条件：员工编号为7788
第一步：去除多表，只查一张表，这里去除部门表，只查员工表
SELECT ename, sal FROM emp e WHERE empno=7788
第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno=d.deptno AND empno=7788
第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。
第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。
SELECT dname,loc,deptno FROM dept;
第四步：替换第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, (SELECT dname,loc,deptno FROM dept) d
WHERE e.deptno=d.deptno AND e.empno=7788
子查询作为表
子查询形式为多行多列
6、自连接:自己连接自己，起别名
求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno , e1.ename,e2.mgr,e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369;
练习：
求各个部门薪水最高的员工所有信息
select e.* from emp e,
–部门最高工资
(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal
mysql函数
数据库三大范式
第一范式：每个列都不可以再拆分（1NF）。 第二范式：在第一范式的基础上，非主键列完全依赖于候选键（2NF）。
第三范式：在第二范式的基础上，非主键列只依赖于主键且不存在传递依赖，不依赖于其他非主键（3NF）。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。
数据库引擎
MySQL存储引擎MyISAM与InnoDB区别
存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。
查看MySQL数据库使用的引擎
SHOW ENGINES;
查看数据库默认使用哪个引擎
SHOW VARIABLES LIKE ‘storage_engine’;
常用的存储引擎有以下：
Innodb引擎（Mysql默认引擎）：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
InnoDB存储引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：
1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE
TABLE，或者删除整个表（使用DROP TABLE）
存储引擎的选择
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
引擎转载地址：
https://www.jianshu.com/p/4bb9f78b4f6d
索引
索引本质是一种作用在数据库引擎上来实现快速查找的一种数据结构算法
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
下图是MyISAM索引的原理图：
这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary
key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary
key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
还是看人家的吧：里边有B树和B+树的简单介绍https://blog.csdn.net/suifeng3051/article/details/52669644
索引类型
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
MySQL支持的索引包括
INDEX、UNIQUE、PRIMARY KEY、FULLTEXT类型的索引。
普通索引INDEX
这是最基本的索引类型，而且它没有唯一性之类的限制，多行记录可以包含相同值。普通索引可以通过以下几种方式创建：
创建索引，例如
CREATE INDEX indexName ON mytable(username(length));
（如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。）
修改表，例如
ALTER mytable ADD INDEX [indexName] ON (username(length))
创建表的时候指定索引，例如
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT
NULL, INDEX [indexName] (username(length)) );
唯一性索引及组合索引
和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
唯一性索引可以用以下几种方式创建：
创建索引，例如
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如
CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) );
创建了一个 mytable表，来进一步说明下组合索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16)
NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
为了进一步提供MySQL的效率，就要考虑建立组合索引。如下语句将 name, city, age建到一个索引里：
ALTER TABLE mytable ADD INDEX name_city_age(username(10),city,age);
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
usernname,city,age ；usernname,city； usernname。
为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
SELECT * FROM mytable WHREE username=“admin” AND city=“郑州” SELECT
- FROM mytable WHREE username=“admin”
而下面几个则不会用到：
SELECT * FROM mytable WHREE age=20 AND city=“郑州” SELECT * FROM mytable WHREE city=“郑州”
主键索引
主键是一种特殊的唯一性索引，不允许有空值，但它必须指定为“PRIMARY KEY”。一般是在建表的时候同时创建主键索引，例如
CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) );
也可以通过修改表的方式加入主键，例如
ALTER TABLE tablename ADD PRIMARY KEY (列的列表);
注意：每个表只能有一个主键。
全文索引
全文索引FULLTEXT可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。
建立索引的时机
一般我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对
<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username
WHERE m.age=20 AND m.city=‘郑州’
此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：
SELECT * FROM mytable WHERE username like’admin%’
而下句就不会使用：
SELECT * FROM mytable WHERE Name like’%admin’
因此，在使用LIKE时应注意以上的区别。
索引的不足之处
上说了一些使用索引的好处，但索引也不是建立的越多、越长越好，过多的使用索引将会造成滥用。因此索引也会有它的缺点：
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件，对后续数据库的增删改都需由额外的操作来更新索引。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order
by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like
“aaa%”可以使用索引。5.索引列不能参与计算，保持列“干净”
比如select * from users where YEAR(adddate)<2007;
将在每个行上进行应用函数YEAR(adddate)运算后才能比较，这将导致索引失效而进行全表扫描，因此语句应该改成:select *
from users where adddate<‘2007-01-01’;6.不使用NOT IN和<>操作
7.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
8.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
索引优化策略
参考的文章：
https://blog.csdn.net/liuchangjie0112/article/details/90606916
https://blog.csdn.net/suifeng3051/article/details/52669644
https://blog.csdn.net/kennyrose/article/details/7532032 （数据库索引实现原理）
mysql Binlog
mysql数据库备份与恢复
1 生成SQL脚本 导出数据
在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。
mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径
现在可以在C盘下找到mydb1.sql文件了！
注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！
2 执行SQL脚本 恢复数据
前提：必须先创建数据库名
**执行SQL脚本需要登录mysql，**然后进入指定数据库，才可以执行SQL脚本！！！
执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！
SOURCE C:\mydb1.sql
注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！
还可以通过下面的方式来执行脚本文件：
mysql -u root -p 123 mydb1<c:\mydb1.sql
mysql –u用户名 –p密码 数据库<要执行脚本文件路径
这种方式无需登录mysql！
注意：在CMD下 命令不能加;
数据库锁
概述
数据库锁一般可以分为两类，一个是悲观锁，一个是乐观锁。
乐观锁一般是指用户自己实现的一种锁机制，假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观锁的实现方式一般包括使用版本号和时间戳。
悲观锁一般就是我们通常说的数据库锁机制，以下讨论都是基于悲观锁。
悲观锁主要表锁、行锁、页锁。在MyISAM中只用到表锁，不会有死锁的问题，锁的开销也很小，但是相应的并发能力很差。innodb实现了行级锁和表锁，锁的粒度变小了，并发能力变强，但是相应的锁的开销变大，很有可能出现死锁。同时inodb需要协调这两种锁，算法也变得复杂。InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
表锁和行锁都分为共享锁和排他锁（独占锁），而更新锁是为了解决行锁升级（共享锁升级为独占锁）的死锁问题。
innodb中表锁和行锁一起用，所以为了提高效率才会有意向锁（意向共享锁和意向排他锁）。
详解
为了表锁和行锁而存在的意向锁
①在mysql中有表锁，LOCK TABLE my_tabl_name READ; 用读锁锁表，会阻塞其他事务修改表数据。LOCK
TABLE my_table_name WRITe; 用写锁锁表，会阻塞其他事务读和写。
②Innodb引擎又支持行锁，行锁分为共享锁，一个事务对一行的共享只读锁。排它锁，一个事务对一行的排他读写锁。
③这两中类型的锁共存的问题考虑这个例子：事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。数据库要怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。
在意向锁存在的情况下，上面的判断可以改成
step1：不变
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。
行锁的细分
共享锁
加锁与解锁：当一个事务执行select语句时，数据库系统会为这个事务分配一把共享锁，来锁定被查询的数据。在默认情况下，数据被读取后，数据库系统立即解除共享锁。例如，当一个事务执行查询“SELECT* FROM accounts”语句时，数据库系统首先锁定第一行，读取之后，解除对第一行的锁定，然后锁定第二行。这样，在一个事务读操作过程中，允许其他事务同时更新accounts表中未锁定的行。
兼容性：如果数据资源上放置了共享锁，还能再放置共享锁和更新锁。并发性能：具有良好的并发性能，当数据被放置共享锁后，还可以再放置共享锁或更新锁。所以并发性能很好。
排他锁
加锁与解锁：当一个事务执行insert、update或delete语句时，数据库系统会自动对SQL语句操纵的数据资源使用独占锁。如果该数据资源已经有其他锁（任何锁）存在时，就无法对其再放置独占锁了。
兼容性：独占锁不能和其他锁兼容，如果数据资源上已经加了独占锁，就不能再放置其他的锁了。同样，如果数据资源上已经放置了其他锁，那么也就不能再放置独占锁了。
并发性能：最差。只允许一个事务访问锁定的数据，如果其他事务也需要访问该数据，就必须等待。
更新锁
更新锁在的初始化阶段用来锁定可能要被修改的资源，这可以避免使用共享锁造成的死锁现象。例如，对于以下的update语句：
UPDATE accounts SET balance=900 WHERE id=1
更新操作需要分两步：读取accounts表中id为1的记录 –> 执行更新操作。
如果在第一步使用共享锁，再第二步把锁升级为独占锁，就可能出现死锁现象。例如：两个事务都获取了同一数据资源的共享锁，然后都要把锁升级为独占锁，但需要等待另一个事务解除共享锁才能升级为独占锁，这就造成了死锁。
更新锁有如下特征：
加锁与解锁：当一个事务执行update语句时，数据库系统会先为事务分配一把更新锁。当读取数据完毕，执行更新操作时，会把更新锁升级为独占锁。
兼容性：更新锁与共享锁是兼容的，也就是说，一个资源可以同时放置更新锁和共享锁，但是最多放置一把更新锁。这样，当多个事务更新相同的数据时，只有一个事务能获得更新锁，然后再把更新锁升级为独占锁，其他事务必须等到前一个事务结束后，才能获取得更新锁，这就避免了死锁。
并发性能：允许多个事务同时读锁定的资源，但不允许其他事务修改它
数据库隔离级别
了解了数据的锁机制，数据库的隔离级别也就好理解多了。每一种隔离级别满足不同的数据要求，使用不同程度的锁。
Read Uncommitted，读写均不使用锁，数据的一致性最差，也会出现许多逻辑错误。
Read Committed，使用写锁，但是读会出现不一致，不可重复读。
Repeatable Read, 使用读锁和写锁，解决不可重复读的问题，但会有幻读。
Serializable, 使用事务串形化调度，避免出现因为插入数据没法加锁导致的不一致的情况。
参考链接
https://blog.csdn.net/C_J33/article/details/79487941
https://blog.csdn.net/aluomaidi/article/details/52460844
SQL语句执行顺序分析
执行顺序解析select、from、where、group by、having、order by、聚合函数
-- 建表语句 （数据库自己新建一个即可）
CREATE TABLE tb_info(
id INT PRIMARY KEY,
type VARCHAR(20) NOT NULL,
price FLOAT NOT NULL
)
-- 为了便于测试，生活用品的price范围是100-1000，学习资料的price范围是0-100（其中仅id=17的是个例外），交通工具的price范围是大于10000
INSERT INTO tb_info (id,type,price) VALUES (1,"生活用品",200);
INSERT INTO tb_info (id,type,price) VALUES (2,"学习资料",20);
INSERT INTO tb_info (id,type,price) VALUES (3,"生活用品",220);
INSERT INTO tb_info (id,type,price) VALUES (4,"交通工具",20000);
INSERT INTO tb_info (id,type,price) VALUES (5,"学习资料",40);
INSERT INTO tb_info (id,type,price) VALUES (6,"生活用品",600);
INSERT INTO tb_info (id,type,price) VALUES (7,"交通工具",1200);
INSERT INTO tb_info (id,type,price) VALUES (8,"生活用品",620);
INSERT INTO tb_info (id,type,price) VALUES (9,"学习资料",80);
INSERT INTO tb_info (id,type,price) VALUES (10,"交通工具",3320);
INSERT INTO tb_info (id,type,price) VALUES (11,"交通工具",10220);
INSERT INTO tb_info (id,type,price) VALUES (12,"学习资料",10);
INSERT INTO tb_info (id,type,price) VALUES (13,"交通工具",3320);
INSERT INTO tb_info (id,type,price) VALUES (14,"交通工具",10220);
INSERT INTO tb_info (id,type,price) VALUES (15,"学习资料",10);
INSERT INTO tb_info (id,type,price) VALUES (16,"生活用品",620);
INSERT INTO tb_info (id,type,price) VALUES (17,"学习资料",600);
select * from tb_info
-- 降序，不加desc默认升序
select id,type,price FROM tb_info WHERE type = "交通工具" ORDER BY price DESC;
-- 仅第十七条学习资料price大于100，来验证sql的条件和聚合函数都起作用了
SELECT AVG(price) avg_privice,type FROM tb_info where price>=100 GROUP BY type;
-- 下边SQL对比上一条SQL可以发现group by 的作用(下边的未分组默认全部>100的数据)
SELECT AVG(price) avg_privice,type FROM tb_info where price>=100
-- 加上GROUP BY type 之后查询的就不是全部了？
select id,type,price FROM tb_info WHERE price>=10 GROUP BY type HAVING price > 100 ORDER BY price DESC;
-- 上条疑惑解析，当group by 后边仅有一个type的时候，它将归属于一种type的合到一起了，如果再加一个id就没问题了
select id,type,price FROM tb_info WHERE price>=10 GROUP BY type,id HAVING price > 100 ORDER BY price DESC;
-- having 条件必须和group by 一起使用，having是分组后过滤，where是分组前筛选
select id,type,price FROM tb_info GROUP BY type HAVING MAX(price) ORDER BY price DESC;
-- 综合使用where、group by、having、order by、from、select、聚合函数
select type,MAX(price) max_price from tb_info where price is not null GROUP BY type having max_price >500 ORDER BY max_price
-- 以上为测试select、where、from、group by、having、聚合函数、order by的案例
-- 以下为测试左外连接、右外链接的测试案例
CREATE TABLE conn_info(
num INT PRIMARY KEY,
info VARCHAR(20) NOT NULL
)
-- drop TABLE conn_info
INSERT INTO conn_info (num,info) VALUES(1,"同");
INSERT INTO conn_info (num,info) VALUES(2,"同");
INSERT INTO conn_info (num,info) VALUES(5,"同");
INSERT INTO conn_info (num,info) VALUES(10,"同");
INSERT INTO conn_info (num,info) VALUES(22,"不同");
-- 左外连接
SELECT * FROM tb_info LEFT JOIN conn_info ON id = num
-- 右链接
SELECT * FROM tb_info RIGHT JOIN conn_info ON id = num
-- 全外连接
SELECT * FROM tb_info FULL JOIN conn_info ON id = num
-- 注意：MySQL是不支持全外的连接的
-- 内连接
SELECT * FROM tb_info INNER JOIN conn_info ON id = num
-- 隐式内连接
SELECT * FROM tb_info a,conn_info b where a.id = b.num
自己当时的困惑点，如果您没看明白运行一下就懂了
加上GROUP BY type 之后查询的就不是全部了？
select id,type,price FROM tb_info WHERE price>=10 GROUP BY type HAVING price > 100 ORDER BY price DESC;
上条疑惑解析，当group by 后边仅有一个type的时候，它将归属于一种type的合到一起了，如果再加一个id就没问题了
select id,type,price FROM tb_info WHERE price>=10 GROUP BY type,id HAVING price > 100 ORDER BY price DESC;
总结执行顺序为：
from–where–group by–聚合函数–having–select–order by
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
聚合函数：通过上面的分组来进行汇总
having:对上面已经分组的数据或汇总后的数据添加过滤条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据
内连接、外链接（左、右）：
左连接的定义：是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。
右连接定义：是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。
内连接定义：使用比较运算符根据每个表共有的列的值匹配两个表中的行
外链接内连接形象讲解
扩展：
注意having和where的用法区别：
1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。
2.where肯定在group by 之前，即也在having之前。
3.where后的条件表达式里不允许使用聚合函数，而having可以。
4.group by和select的一些总结：如果在有group by操作中，select后面接的结果集字段只有两种：要么就只有group by后出现的字段，要么就是group by后出现的字段＋聚合函数的组合（常用的五种聚合函数：min()求列中最小数值，max()求列中最大数值，avg()求平均值，sum()求列中字段对应数值的总和，count()求列的总条数）（不然加上也是没有意义的，一般显示的都是满足条件的第一个数据对查询结果并没有什么意义）
5.from后面的表关联，是自右向左解析的
而where条件的解析顺序是自下而上的。
也就是说，在写SQL的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表）， 而把能筛选出小量数据的条件放在where语句的最左边 （用小表去匹配大表）
使用count（列名）当某列出现null值的时候，count（*）仍然会计算，但是count(列名)不会。
6.索引
索引是单独的数据库对象，索引也需要被维护。
索引可以提高查询速度，但会降增删改的速度。 3.通过一定的查询触发，并不是越多越好。 什么时候不适合用索引？
当增删改的操作大于查询的操作时；查询的语句大于所有语句的三分之一时。
创建索引语法：create index 索引名 on 表名 （列名） 删除索引语法：drop index 索引名（如：CREATE UNIQUE INDEX NAME_UNINDEX ON individual(firstname,lastname) ）
7.
求和用累加sum(),求行的个数用累计count()
数据库中对空值的处理：sum()不计算，count()认为没有此项；
8.常用sql语句，代码如下：
1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
select distinct name from table where name not in (select distinct name from table where fenshu<=80)
select name from table group by name having min(fenshu)>80
select name from table group by name having count(kecheng)>=3 and min(fenshu)>=80
2. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息
ｄｅｌｅｔｅ ｆｒｏｍ 表名 ｗｈｅｒｅ 自动编号 ｎｏｔ ｉｎ （ｓｅｌｅｃｔ ｍｉｎ（自动编号） ｆｒｏｍ 表名 ｇｒｏｕｐ ｂｙ 学号，姓名，课程编号，课程名称，分数）
3. 表结构如下
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
答案：
select
years,
(select amount from equery m where months = 1 and m.years = equery.years) as m1,
(select amount from equery m where months = 2 and m.years = equery.years) as m2,
(select amount from equery m where months = 3 and m.years = equery.years) as m3,
(select amount from equery m where months = 4 and m.years = equery.years) as m4
from equery
GROUP BY years
如果不加 and m.years = equery.years报错如下：
[Err] 1242 - Subquery returns more than 1 row
４．拷贝表
将表1结构复制到表2
SELECT * INTO 表2 FROM 表1 WHERE 1=2;<br><br>将表1内容全部复制到表2 <br>SELECT * INTO 表2 FROM 表1;
参考链接：https://www.cnblogs.com/lyjing/p/8483742.html
５．查询表
有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
6、编写SQL语句
1) 创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话
Create table stu (学号 int ,
姓名 varchar(8),
年龄 int,
性别 varchar(4),
家庭地址 varchar(50),
联系电话 int
);
2) 修改学生表的结构，添加一列信息，学历
Alter table stu add 学历 varchar(6);
3) 修改学生表的结构，删除一列信息，家庭住址
Alter table stu drop column 家庭地址
4) 向学生表添加如下信息：
学号 姓名年龄性别联系电话学历
1A22男123456小学
2B21男119中学
3C23男110高中
4D18女114大学
Insert into stu values(1,’A’,22,’男’,123456,’小学’)
Insert into stu values(2,’B’,21,’男’,119,’中学’)
Insert into stu values(3,’C’,23,’男’,110,’高中’)
Insert into stu values(4,’D’,18,’女’,114,’大学’)
5) 修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”
Update stu set 学历=’大专’ where 联系电话 like ‘11%’
6) 删除学生表的数据，姓名以C开头，性别为‘男’的记录删除
Delect from stu where 性别=’男’ and 姓名 like ‘c%’
7) 查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来
Select 姓名,学号 from stu where 年龄<22 and 学历=’大专’
７．info表：
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写sql语句?
date win lose
2005-05-09 1 3
2005-05-10 1 2
select date, sum(case when result = "win" then 1 else 0 end) as "win",
sum(case when result = "lose" then 1 else 0 end) as "lose"
from info group by date
select date,count(case when result='win' then 1 end)as win,
count(case when result='lose' then 1 end)as lose
from info group by date
select a.date,a.result as win,b.result as lose
from (select date,count(result) as result from info where result='win' group by date)as a
join (select date,count(result) as result from info where result='lose' group by date)as b
on a.date=b.date
８．表中有A B C三列,用SQL语句实现：
当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
select (case when a>b then a else b end),(case when b>c then b else c end) from abc
参考博客
推荐博客：
比较全的mysql
如需转载，请注明出处。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
