SQL概述
SQL，一般发音为sequel，SQL的全称Structured Query Language),SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。但是每一个数据库都有自己的特性别的数据库没有，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了(90%以上的SQL都是通用的)
什么是数据库
数据库，通常是一个或一组文件，保存了一些符合特定规格的数据，数据库对应的英语单词是DataBase,简称DB，数据库软件称为数据库管理系统(DBMS)，全称为DataBase Management System,如：Oracle、SQL Server、MySQL、Sybase、Informix、 DB2、interbase 、PostgreSql
MySql概述
MySQL最初是由”MySQL AB”公司开发的一套关系型数据库管理系统(RDBMS-Relation Database Management System).MySQL不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过7万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。
MySQL
查询建表语句：
show create table 表名;
show create table draw_company_result;
CREATE TABLE
draw_company_result(
draw_idvarchar(30) NOT NULL,
game_codevarchar(10) NOT NULL DEFAULT ‘’,
companyvarchar(30) NOT NULL,
resultvarchar(255) DEFAULT ‘’,
operatorvarchar(255) DEFAULT NULL,
operatortimedatetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (draw_id,game_code,company)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
查询表的单个字段
user openfire;
select username from ofUser;
select username, name, email from ofUser;
查询重命名表名，并不能修改真正的修改表名
mysql> select username, encryptedPassword from ofUser;
+———-+————————————————–+
| username | encryptedPassword |
+———-+————————————————–+
| admin | 61a46ea4fb839834cc6e88519e28a5e353a96d402957e094 |
| gang | 269d01e7a82a1cd51642e1e574357bf30e4ad174f3f26bde |
| jia | 248be0a8c9a505221bf7a0a7523521119cfaa2bfa288a106 |
| jian | 65b416288a78bac5732531ff33565860e71a203d9974de4c |
| pei | 4242b7cb67849778199f189d97010b24291c03d8cab70a41 |
+———-+————————————————–+
5 rows in set (0.00 sec)
mysql> select username,encryptedPassword as passwd from ofUser;
+———-+————————————————–+
| username | passwd |
+———-+————————————————–+
| admin | 61a46ea4fb839834cc6e88519e28a5e353a96d402957e094 |
| gang | 269d01e7a82a1cd51642e1e574357bf30e4ad174f3f26bde |
| jia | 248be0a8c9a505221bf7a0a7523521119cfaa2bfa288a106 |
| jian | 65b416288a78bac5732531ff33565860e71a203d9974de4c |
| pei | 4242b7cb67849778199f189d97010b24291c03d8cab70a41 |
+———-+————————————————–+
5 rows in set (0.00 sec)
注意：可以写成 select username,encryptedPassword passwd from ofUser;
省去as关键字也是可以的
注意：字符串用单引号
条件查询:
条件查询需要用到where语句，where必须放到from语句表的后面
支持如下运算符
运算符 说明
= 等于
<>或!= 不等于
< 小于
<= 小于等于
大于= 大于等于
between … and … 两个值之间,等同于>= and <=
is null 为null(is not null 不为空)
and 并且
or 或者
in 包含，相当于多个or(not in不在这个范围中)
not not可以取非，主要用在is 或者in中
like like 称为模糊查询，支持%或下划线匹配
str_to_date 将字符串转换日期
date_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
Ifnull 可以将null转换成一个具体值
mysql> select username,name,creationDate as crtDate from ofUser where email=’123@qq.com’;
+———-+——+—————–+
| username | name | crtDate |
+———-+——+—————–+
| gang | gang | 001560644570688 |
+———-+——+—————–+
1 row in set (0.00 sec)
找出工资大于1800的员工，要求部门编号是20或者30的。
select
empno,enmae,sal,deptno
from
emp
where
sal > 1800 and (deptno = 20 or deptno =30);
mysql> SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE SAL>1800 AND (DEPTNO =20 OR DEPTNO = 30);
+——-+——-+———+——–+
| EMPNO | ENAME | SAL | DEPTNO |
+——-+——-+———+——–+
| 7566 | JONES | 2975.00 | 20 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7782 | CLARK | 2450.00 | 20 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7902 | FORD | 3000.00 | 20 |
+——-+——-+———+——–+
5 rows in set (0.00 sec)
查找出某个字段对应的数据中T字母结尾的：
mysql> select ENAME FROM EMP WHERE ENAME LIKE ‘%T’;
+——-+
| ENAME |
+——-+
| SCOTT |
+——-+
1 row in set (0.00 sec)
查找出某个字段对应的数据中第二个字母是A的：
mysql> select ENAME FROM EMP WHERE ENAME LIKE ‘_A%’;
+——–+
| ENAME |
+——–+
| WARD |
| MARTIN |
| JAMES |
+——–+
3 rows in set (0.00 sec)
查找出某个字段对应的数据中里面含有A的：
mysql> SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%A%’;
+——–+
| ENAME |
+——–+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| CLARK |
| ADAMS |
| JAMES |
+——–+
7 rows in set (0.00 sec)
注意：%代表任意多个字符，_代表任意单个字符
注意：Null为空，但不是空串
排序 order by
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL;
+——–+———+
| ENAME | SAL |
+——–+———+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+——–+———+
14 rows in set (0.00 sec)
默认升序排序
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL asc;
+——–+———+
| ENAME | SAL |
+——–+———+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+——–+———+
14 rows in set (0.00 sec)
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC;
+——–+———+
| ENAME | SAL |
+——–+———+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+——–+———+
14 rows in set (0.00 sec)
DESC: 降序排序
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC,ENAME ASC;
+——–+———+
| ENAME | SAL |
+——–+———+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+——–+———+
14 rows in set (0.00 sec)
单一字段排序
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句，那么order by
必须放到where语句的后面
- 按照薪水由小到大排序(系统默认由小到大)
mysql> SELECT * FROM EMP ORDER BY SAL;
+——-+——–+———–+——+————+———+———+——–+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+——-+——–+———–+——+————+———+———+——–+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+——-+——–+———–+——+————+———+———+——–+
14 rows in set (0.00 sec)
使用字段的位置来排序
mysql> SELECT ENAME,SAL FROM EMP ORDER BY 2;
+——–+———+
| ENAME | SAL |
+——–+———+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+——–+———+
mysql> SELECT ENAME,SAL FROM EMP ORDER BY 1;
+——–+———+
| ENAME | SAL |
+——–+———+
| ADAMS | 1100.00 |
| ALLEN | 1600.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| FORD | 3000.00 |
| JAMES | 950.00 |
| JONES | 2975.00 |
| KING | 5000.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| SCOTT | 3000.00 |
| SMITH | 800.00 |
| TURNER | 1500.00 |
| WARD | 1250.00 |
+——–+———+
14 rows in set (0.00 sec)
数据处理函数/单行处理函数
Lower 转换小写
upper 转换大写
substr 取字串(substr(被截取的字符串，起始下标，截取的长度))
length 取长度
trim 去空格
将查询结果显示小写
mysql> SELECT lower(ENAME) as ename from EMP;
+——–+
| ename |
+——–+
| smith |
| allen |
| ward |
| jones |
| martin |
| blake |
| clark |
| scott |
| king |
| turner |
| adams |
| james |
| ford |
| miller |
+——–+
14 rows in set (0.00 sec)
mysql> SELECT upper(ENAME) as ename from EMP;
+——–+
| ename |
+——–+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+——–+
14 rows in set (0.00 sec)
mysql> select username from ofUser;
+———-+
| username |
+———-+
| admin |
| pei |
| gang |
| jian |
| jia |
+———-+
5 rows in set (0.00 sec)
mysql> select upper(username) from ofUser;
+—————–+
| upper(username) |
+—————–+
| ADMIN |
| PEI |
| GANG |
| JIAN |
| JIA |
+—————–+
5 rows in set (0.00 sec)
mysql> select upper(username) as username from ofUser;
+———-+
| username |
+———-+
| ADMIN |
| PEI |
| GANG |
| JIAN |
| JIA |
+———-+
5 rows in set (0.00 sec)
substr(args1,args2,args3)
args1参数为字段名，
args2参数为起始下标，
args3参数为截取的长度
查找表emp中字段ename中第二个字母为A的：
之前的方法：
mysql> SELECT ENAME FROM EMP WHERE ENAME LIKE ‘_A%’;
+——–+
| ENAME |
+——–+
| WARD |
| MARTIN |
| JAMES |
+——–+
3 rows in set (0.00 sec)
substr()函数的方法：
mysql> SELECT ENAME FROM EMP WHERE substr(ENAME,2,1) = ‘A’;
+——–+
| ENAME |
+——–+
| WARD |
| MARTIN |
| JAMES |
+——–+
3 rows in set (0.00 sec)
length(args1)
select length(ename) from emp;
trim 去除查询中输入的空格
mysql> SELECT ENAME FROM EMP WHERE substr(ENAME,2,1) = ‘A’;
+——–+
| ENAME |
+——–+
| WARD |
| MARTIN |
| JAMES |
+——–+
3 rows in set (0.00 sec)
mysql> SELECT ENAME FROM EMP WHERE ENAME=’ SMITH ‘;
Empty set (0.00 sec)
mysql> SELECT ENAME FROM EMP WHERE ENAME = trim(‘ SMITH ‘);
+——-+
| ENAME |
+——-+
| SMITH |
+——-+
1 row in set (0.00 sec)
补助为空的显示为0
mysql> SELECT ENAME,ifnull(comm,0) as comm from EMP;
+——–+———+
| ENAME | comm |
+——–+———+
| SMITH | 0.00 |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | 0.00 |
| MARTIN | 1400.00 |
| BLAKE | 0.00 |
| CLARK | 0.00 |
| SCOTT | 0.00 |
| KING | 0.00 |
| TURNER | 0.00 |
| ADAMS | 0.00 |
| JAMES | 0.00 |
| FORD | 0.00 |
| MILLER | 0.00 |
+——–+———+
14 rows in set (0.00 sec)
mysql> SELECT ENAME,(SAL+ifnull(comm,0))*12 as yearsal from EMP;
+——–+———-+
| ENAME | yearsal |
+——–+———-+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+——–+———-+
14 rows in set (0.00 sec)
0 先了解一下数据库中的数据类型：
int 整数型
varchar 可变长度字符串
char 定长字符串
date 日期类型
double 浮点类型
关于MYSQL中的日期处理
1.1 每一个数据库处理日期的时候，采用的机制都是不同的，日期处理都有自己的一套机制。
所有在实际的开发中，表中的字段定义为DATE类型，这种情况很少。因为一旦使用日期类型，那么java程序将不能够通用。在实际开发中，一般使用日期字符串来表示日期
1.2 日期是数据库本身的特色，也是数据库本身机制中的一个重要内容，所以还是需要掌握。- str_to_date - date_format str_to_date: 该函数的作用是：将“日期字符串”转换成“日期类型”的数据 【varchar ----> date】 该函数的执行结果是DATE类型 该函数的使用格式： str_to_date('日期字符串','日期格式') 关于MYSQL中的日期格式： 回顾java中的日期格式： yyyy 年 MM 月 dd 日 HH 时 mm 分 ss 秒 SSS 毫秒 java中将字符串转换成日期类型： SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = sdf.parse("1970-10-10"); MySQL的日期格式： %Y 年 %m 月 %d 日 %H 时 %i 分 %s 秒
MYSQL默认的日期格式： %Y-%m-%d
案例： 查询出1980-12-17入职的员工
mysql> SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE = '1980-12-17';
+-------+------------+
| ENAME | HIREDATE |
+-------+------------+
| SMITH | 1980-12-17 |
+-------+------------+
1 row in set (0.01 sec)
等同如下SQL命令:
mysql> SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE = str_to_date('1980-12-17','%Y-%m-%d');
+-------+------------+
| ENAME | HIREDATE |
+-------+------------+
| SMITH | 1980-12-17 |
+-------+------------+
1 row in set (0.00 sec)
MYSQL默认的日期格式： %Y-%m-%d，以上的日期字符串'1980-12-17'正好和默认的日期格式一样，存在了自动类型转换，自动将日期字符串转换成了日期类型，所以以上查询可以查询出结果。
mysql> SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE='12-17-1980';
Empty set, 1 warning (0.00 sec)
'12-17-1980'日期字符串和mysql默认的日期格式不同
hiredate是date类型，'12-17-1980'是一个字符串varchar类型，类型不匹配.
所以无法查询结果，并且有警告方法。
纠正以上SQL语句的错误：
select ENAME, HIREDATE from EMP where HIREDATE = str_to_date('12-17-1980',%d-%m-%Y);
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26 |
+-----------+
1 row in set (0.00 sec)
mysql> select ENAME, HIREDATE from EMP where HIREDATE = str_to_date('12-17-1980',%d-%m-%Y);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%d-%m-%Y)' at line 1
在5.7.26中没有测试通过
3.6 案例：
创建学生表: 【create 语句，以后详细讲】
create table t_student(
id int(10),
name varchar(32),
birth date
);
插入数据：【insert语句，以后详细讲】
insert into t_student(id,name,birth) values(1,'jack','1980-10-11');
以上可以执行，因为'1980-10-11'虽然是一个varchar类型，但是由于格式和MYSQL数据库默认的日期格式相同，所以存在自动类型转换.
mysql>insert into t_student(id,name,birth) values(2,'zhangsan','10-11-1970');
ERROR 1292(22007):Incorrect date value:'10-11-1970' for column 'birth' at row 1 以上发生错误：
第一：'10-11-1970'日期字符串的格式和mysql默认的格式不同
第二：'10-11-1970'是varchar类型，birth字段需要date类型，类型不匹配.
纠正以上错误：
insert into t_student(id,name,birth) values(2,'zhangsan',str_to_date('10-11-1970','%m-%d-%Y'));
str_to_date函数通常使用在插入操作中，字段是date类型，不接收字符串varchar类型。
需要通过该函数将varchar变成date再插入数据，才能成功。
date_format:
该函数的作用是：将"日期类型date"转换成具有特定格式的日期字符串varchar【date--->varchar】
该函数的运算结果是:varchar类型【具备特定格式的】
该函数的语法格式：
date_format(日期类型数据,'日期格式')
回顾java中的日期格式化：
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date nowTime = new Date();
String strTime = sdf.format(newTime);
案例：查询员工的入职日期，以'10-12-1980'的格式显示到窗口中。
mysql> SELECT ENAME, date_format(hiredate,'%m-%d-%Y') as HIREDATE from EMP;
+--------+------------+
| ENAME | HIREDATE |
+--------+------------+
| SMITH | 12-17-1980 |
| ALLEN | 02-20-1981 |
| WARD | 02-22-1981 |
| JONES | 04-02-1981 |
| MARTIN | 09-28-1981 |
| BLAKE | 05-01-1981 |
| CLARK | 06-09-1981 |
| SCOTT | 04-19-1987 |
| KING | 11-17-1981 |
| TURNER | 09-08-1981 |
| ADAMS | 05-23-1987 |
| JAMES | 12-03-1981 |
| FORD | 12-03-1981 |
| MILLER | 01-23-1982 |
+--------+------------+
14 rows in set (0.00 sec)
以下两个DQL语句的执行结果相同：
select ename,hiredate from emp; 【hiredate自动转换成varchar类型，并且采用 %Y-%m-%d 格式】
mysql> SELECT ENAME, HIREDATE FROM EMP;
+--------+------------+
| ENAME | HIREDATE |
+--------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| JONES | 1981-04-02 |
| MARTIN | 1981-09-28 |
| BLAKE | 1981-05-01 |
| CLARK | 1981-06-09 |
| SCOTT | 1987-04-19 |
| KING | 1981-11-17 |
| TURNER | 1981-09-08 |
| ADAMS | 1987-05-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
| MILLER | 1982-01-23 |
+--------+------------+
14 rows in set (0.00 sec)
select ename,date_format(hiredate,'%Y-%m-%d') as hiredate from emp;
mysql> SELECT ENAME,date_format(HIREDATE,'%Y-%m-%d') as HIREDATE FROM EMP;
+--------+------------+
| ENAME | HIREDATE |
+--------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| JONES | 1981-04-02 |
| MARTIN | 1981-09-28 |
| BLAKE | 1981-05-01 |
| CLARK | 1981-06-09 |
| SCOTT | 1987-04-19 |
| KING | 1981-11-17 |
| TURNER | 1981-09-08 |
| ADAMS | 1987-05-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
| MILLER | 1982-01-23 |
+--------+------------+
14 rows in set (0.00 sec)
结论：date_format函数主要使用在查询操作中，客户需要日期以特定格式展示的时候，需要用到该函数。分组函数/聚合函数/多行处理函数
注意：分组函数不能直接使用在where子句中
count 取得记录数
sum 求和
avg 取平均
max 取最大的数
min 取最小的数
注意：分组函数自动忽略空值，不需要手动的加where条件排除控制
select count(*) from emp where xxx;符合条件的所有记录总数
select count(comm) from emp; comm这个字段中不为空的元素总数
注意：分组函数不能直接使用在where关键字后面
distinct关键字 去除重复记录
注意：distinct只能放到所有字段最前面
mysql> SELECT JOB FROM EMP;
+———–+
| JOB |
+———–+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+———–+
14 rows in set (0.00 sec)
mysql> SELECT distinct JOB FROM EMP;
+-----------+
| JOB |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
mysql> SELECT DEPTNO,JOB FROM EMP;
+--------+-----------+
| DEPTNO | JOB |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | SALESMAN |
| 30 | MANAGER |
| 20 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | SALESMAN |
| 20 | CLERK |
| 30 | CLERK |
| 20 | ANALYST |
| 10 | CLERK |
+--------+-----------+
14 rows in set (0.00 sec)
mysql> SELECT distinct DEPTNO,JOB FROM EMP;
+--------+-----------+
| DEPTNO | JOB |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
8 rows in set (0.00 sec)select count(distinct job) from emp;
mysql> SELECT count(distinct JOB) from EMP;
+———————+
| count(distinct JOB) |
+———————+
| 5 |
+———————+
1 row in set (0.00 sec)
分组查询：
分组查询主要涉及两个子句，分别是：group by和 having
group by 分组查询
order by 【表示通过哪个或者哪些字段进行排序】
group by 【表示通过哪个或者哪些字段进行分组】
2.2 案例：找出每个工作岗位的最高薪水 【先按照工作岗位分组，使用max求每一组的最高薪水】
select
max(sal)
from
emp
group by
job;
mysql> SELECT max(SAL) FROM EMP GROUP BY JOB;
+———-+
| max(SAL) |
+———-+
| 3000.00 |
| 1300.00 |
| 2975.00 |
| 5000.00 |
| 1600.00 |
+———-+
5 rows in set (0.00 sec)
以上的SQL语句表示，先按照job分组，然后对每一组使用max(sal)求最高薪水。
以上查询结果是5条记录。
工作岗位可以写到select后面吗？
可以，因为正好工作岗位job也是5个不同的值。
select
job,max(sal)
from
emp
group by
job;
mysql> SELECT JOB,max(SAL) FROM EMP GROUP BY JOB;
+-----------+----------+
| JOB | max(SAL) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
| MANAGER | 2975.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)
ename可以编写到select后面吗？
- mysql中，可以，语法在这方面比较松散，不严格，但是查询结果没有意义
- oracle中，不可以，执行的时候报错。
select
ename,job,max(sal)
from
emp
group by
job;
mysql> select ENAME,JOB,MAX(SAL) FROM EMP GROUP BY JOB;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.EMP.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> SELECT ENAME,JOB,max(SAL) from EMP group by JOB;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.EMP.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
*重点：若一条SQL语句当中有group by子句，那么select关键子后面只能跟参与分组的字段和分组的函数。*
2.3 案例：计算每个部门的平均薪水【按照部门编号分组，对每一组求平均薪水】
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
mysql> SELECT DEPTNO,avg(SAL) as avgsal FROM EMP GROUP BY DEPTNO;
+--------+-------------+
| DEPTNO | avgsal |
+--------+-------------+
| 10 | 3150.000000 |
| 20 | 2220.833333 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM DEPT;
+--------+------------+-----------+
| DEPTNO | DNAME | LOC |
+--------+------------+-----------+
| 10 | ACCOUNTING | NEW YOURK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
2.4 案例：计算不同部门中的不同岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job; //deptno,job两个字段联合起来分组。
mysql> SELECT DEPTNO, JOB,max(SAL) from EMP group by DEPTNO,JOB;
+--------+-----------+----------+
| DEPTNO | JOB | max(SAL) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
8 rows in set (0.00 sec)
2.5 找出每个工作岗位的最高薪水，除MANAGER之外
mysql> select job,max(sal) from emp group by job;
mysql> SELECT JOB,max(SAL) from EMP group by JOB;
+-----------+----------+
| JOB | max(SAL) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
| MANAGER | 2975.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)
mysql> select job,max(sal) from emp where job<>'MANAGER' group by job;
mysql>
mysql> SELECT JOB,max(SAL) from EMP where JOB<>'MANAGER' GROUP BY JOB;
+-----------+----------+
| JOB | max(SAL) |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 1300.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1600.00 |
+-----------+----------+
4 rows in set (0.00 sec)
2.6 找出每个工作岗位的平均薪水，要求显示平均薪水大于1500.
select job,avg(sal) from emp group by job;
select job,avg(sal) from emp where avg(sal)>1500 group by job;
ERROR 1111(HY00):Invalid user of group function
where关键字后面不能使用分组函数.
分组函数必须在分组完成后执行，而分组需要group by,而group by在where后面执行。
注意：下面这个SQL语句执行会报错误：Group function is not allowed here
找出公司中哪些部门的平均薪水大于6000的列出来：
select
department_id, avg(salary)
from
employees
where
avg(salary)>6000
group by department_id
#having avg(salary) >6000
;
原因：
sql语句的执行顺序为：
from 子句
where 子句
group by 子句
having 子句
order by 子句
select 子句
首先得知道分组/聚合函数是对结果集运算的，当在where子句使用聚合函数时，此时根据group by分割结果集的子句还没有执行，此时只有from后的结果集。
所以无法在where子句中使用聚合函数。having
having 和 where功能都是为了完成数据的过滤 where和having后面都是添加条件 *where 在group by之前完成过滤* *having 在group by之后完成过滤* 案例：找出每个工作岗位的平均薪水，要求显示平均薪水大于1500 select job,avg(sal) from emp group by job having avg(sal)>1500; mysql> SELECT JOB,avg(SAL) FROM EMP group by JOB having avg(SAL) >1500; +-----------+-------------+ | JOB | avg(SAL) | +-----------+-------------+ | ANALYST | 3000.000000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | +-----------+-------------+ 3 rows in set (0.00 sec)
原则：
尽量在where中过滤，无法过滤的数据，通常都是需要先分组之后再过滤的，这个时候可以选择使用having。
效率的问题一个完整的DQL语句的总结
select...from
...where
...group by
...having
...order by
...第一：以上的关键字顺序不能变，严格遵守
第二：执行顺序1.from 从某张表中检索数据 2.where 经过某条件进行过滤 3.group by 然后分组 4.having 分组之后不满意再过滤 5.select 查询出来 6.order by 排序输出
连接查询
1 什么是连接查询？
- 查询的时候只从一张表检索数据，被称为单表查询
- 在实际的开发中，数据并不是存储在一张表中的，是同时存储在多张表中
这些表和表之间存在关系，我们在检索的时候通常是需要将多张表联合起来取得有效数据，
这种多表查询被称为连接查询或者叫做跨表查询。
2 连接查询根据出现年代分类：
- SQL92【1992】
- SQL99【1999：更新的语法，主要掌握这种语法】
DBA: DataBase Administrator【数据库管理员，薪水很高】
老的DBA可能还在编写SQL92语法，所以SQL92语法也要会，起码能看懂。
3 连接查询根据连接方式可以分为：
- 内连接
* 等值连接
* 非等值连接
* 自连接
- 外链接
* 左外连接（左连接）
* 右外连接(右连接)
- 全连接【不讲，使用很少】
4 当多张表进行连接查询，若没有任何条件进行限制，会发生什么现象？
案例：查询每一个员工所在的部门名称，要求最终显示员工名和对应的部门名。
SELECT ENAME,DEPTNO FROM EMP;<emp是员工表>
mysql> SELECT ENAME,DEPTNO FROM EMP;
+--------+--------+
| ENAME | DEPTNO |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 20 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
14 rows in set (0.00 sec)
SELECT DEPTNO,DNAME FROM DEPT;<dept是部门表>
mysql> SELECT DEPTNO,DNAME FROM DEPT;
+--------+------------+
| DEPTNO | DNAME |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
主要分析：多张表连接查询，若没有任何条件限制，会发生什么？
小知识点：在进行多表连接查询的时候，尽量给表起别名，这样效率高，可读性高。
mysql> select e.ENAME,d.DNAME from EMP e,DEPT d;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)
结论：若两张表进行连接查询的时候没有任何条件限制，最终的查询结果总数是两张表记录条数乘积，这种现象被称为笛卡尔积现象。为了避免笛卡尔积现象的发生，必须在进行表连接的时候添加限制条件。
5 案例：查询每一个员工所在的部门名称，要求最终显示员工名和对应的部门名。
mysql> SELECT ENAME,DEPTNO FROM EMP; <EMP是员工表>
+--------+--------+
| ENAME | DEPTNO |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 20 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
14 rows in set (0.00 sec)
mysql> SELECT DEPTNO,DNAME FROM DEPT;<DEPT是部门表>
+--------+------------+
| DEPTNO | DNAME |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
注意：在连接查询的时候虽然使用了限制条件，但是匹配的次数没有减少，还是56次，只不过这一次的结果都是有效记录。
SQL92语法：内连接中的等值连接
mysql> SELECT e.ENAME,d.DNAME FROM EMP e, DEPT d where e.DEPTNO = d.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
SQL99语法：内连接中的等值连接
SQL99语法的优点：表连接独立出来了，结构更清晰，对表连接不满意的话，可以再追加where进行过滤。
mysql> SELECT e.ENAME,d.DNAME FROM EMP e join DEPT d on e.DEPTNO = d.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
mysql> SELECT e.ENAME,d.DNAME FROM EMP e inner join DEPT d on e.DEPTNO = d.DEPTNO; //inner可以省略
mysql> SELECT e.ENAME,d.DNAME FROM EMP e inner join DEPT d on e.DEPTNO = d.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
6. 案例：找出每一个员工对应的工资等级，要求显示员工名、工资等级。
mysql> SELECT * FROM SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> SELECT * from EMP E, SALGRADE S WHERE E.SAL between S.LOSAL AND S.HISAL;
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | GRADE | LOSAL | HISAL |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 1 | 700 | 1200 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 3 | 1401 | 2000 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 2 | 1201 | 1400 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 2 | 1201 | 1400 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 4 | 2001 | 3000 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 5 | 3001 | 9999 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 1 | 700 | 1200 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 1 | 700 | 1200 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 2 | 1201 | 1400 |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
14 rows in set (0.00 sec)
mysql> SELECT E.ENAME,E.SAL,S.GRADE from EMP E, SALGRADE S WHERE E.SAL between S.LOSAL AND S.HISAL;
+--------+---------+-------+
| ENAME | SAL | GRADE |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
SQL99语法：内连接中的非等值连接
SELECT e.ENAME,e.SAL,s.GRADE FROM EMP e inner join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;//inner可以省略
mysql> SELECT e.ENAME,e.SAL,s.GRADE FROM EMP e inner join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;
+--------+---------+-------+
| ENAME | SAL | GRADE |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
mysql> SELECT e.ENAME,e.SAL,s.GRADE FROM EMP e join SALGRADE s on e.SAL between s.LOSAL and s.HISAL;
+--------+---------+-------+
| ENAME | SAL | GRADE |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
SQL92语法：内连接中的非等值连接
mysql> SELECT e.ENAME,e.SAL,s.GRADE FROM EMP e, SALGRADE s where e.SAL between s.LOSAL and s.HISAL;
+--------+---------+-------+
| ENAME | SAL | GRADE |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
7. 案例：找出每一个员工的上级领导，要求显示员工名以及对应的领导名
SQL92语法：内连接中的自连接
select a.ename empname,b.ename leadername from emp a, emp b where a.mgr = b.empno;
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> SELECT A.ENAME EMPNAME,B.ENAME LEADERNAME FROM EMP A,EMP B WHERE A.MGR = B.EMPNO;
+---------+------------+
| EMPNAME | LEADERNAME |
+---------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+------------+
13 rows in set (0.00 sec)
SQL99语法：内连接中的自连接
select a.ename empname,b.ename leadername from emp a inner *join* emp b *on* a.mgr=b.empno;//inner可以省略
mysql> SELECT A.ENAME EMPNAME,B.ENAME LEADERNAME FROM EMP A INNER JOIN EMP B ON A.MGR = B.EMPNO;
+---------+------------+
| EMPNAME | LEADERNAME |
+---------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+------------+
13 rows in set (0.00 sec)
select a.ename empname,b.ename leadername from emp a join emp b on a.mgr=b.empno;
mysql> SELECT A.ENAME EMPNAME,B.ENAME LEADERNAME FROM EMP A JOIN EMP B ON A.MGR=B.EMPNO;
+---------+------------+
| EMPNAME | LEADERNAME |
+---------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+------------+
13 rows in set (0.00 sec)
8、案例：找出每一个员工对应的部门名称,要求显示所有部门
内连接：
A表和B表能够完全匹配的记录查询出来，被称为内连接
外连接：
A表和B表能够完全匹配的记录查询出来之外，将其中一张表的记录无条件的完全查询出来，对方表没有匹配的记录，会自动模拟出NULL与之匹配，这种查询被称为外连接。
外连接的查询结果条数 >= 内连接的查询结果条数
select e.ename, d.dname from emp e join dept d on e.deptno=d.deptno;
mysql> SELECT * FROM DEPT;
+--------+------------+-----------+
| DEPTNO | DNAME | LOC |
+--------+------------+-----------+
| 10 | ACCOUNTING | NEW YOURK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+-----------+
4 rows in set (0.00 sec)
有四个部门
mysql> SELECT E.ENAME,D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
14条记录只显示了三个部门
SQL99语法：外连接中的右外连接[右外连接]
select e.ename, d.dname from emp e right join dept d on e.deptno=d.deptno;
mysql> SELECT E.ENAME,D.DNAME FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
15条记录显示了，所有4个部门
select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno; //outer可以省略
mysql> SELECT E.ENAME,D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
SQL99语法：外连接中的左外连接[左外连接]
select e.ename, d.dname from dept d left join emp e on e.deptno=d.deptno;
mysql> SELECT E.ENAME,D.DNAME FROM DEPT D LEFT JOIN EMP E ON E.DEPTNO=D.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
select e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno; //outer可以省略
mysql> SELECT E.ENAME,D.DNAME FROM DEPT D LEFT OUTER JOIN EMP E ON E.DEPTNO=D.DEPTNO;
+--------+------------+
| ENAME | DNAME |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
注意：任何一个右外连接都可以写成左外连接，任何一个左外连接也同样可以写成右外连接
为什么inner和outter可以省略，加上去有什么好处？
- 可以省略，因为区分内连接和外连接依靠的不是这些关键字，而是看SQL语句中是否存在left/right
若存在，表示一定是一个外连接，其它都是内连接。
- 加上去的好处是增强可读性。
9. 案例：找出每一个员工对应的领导名，要求显示所有的员工
select A.ENAME EMPNAME,B.ENAME LEADERNAME from EMP A left join EMP B on A.mgr=B.empno;
SELECT B.ENAME LEADERNAME, A.ENAME EMPNAME FROM EMP B RIGHT OUTER JOIN EMP A ON A.MGR=B.EMPNO;
mysql> SELECT B.ENAME LEADERNAME, A.ENAME EMPNAME FROM EMP B RIGHT OUTER JOIN EMP A ON A.MGR=B.EMPNO;
+------------+---------+
| LEADERNAME | EMPNAME |
+------------+---------+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| NULL | KING |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
+------------+---------+
14 rows in set (0.00 sec)
mysql> select A.ENAME EMPNAME,B.ENAME LEADERNAME from EMP A left OUTER join EMP B on A.mgr=B.empno;
+---------+------------+
| EMPNAME | LEADERNAME |
+---------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+------------+
14 rows in set (0.01 sec)10 案例：找出每一个员工对应的部门名称，以及该员工对应的工资等级。要求显示员工名、部门名、工资等级
*多张表进行表连接的语法格式*
select
xxx
from
a
join
b
on
条件
join
c
on
条件
原理：a和b表进行表连接
1 先找出每一个员工对应的部门名称，显示员工名、部门名
mysql> SELECT A.ENAME EMPNAME ,B.DNAME DEPTNAME FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO ;
+---------+------------+
| EMPNAME | DEPTNAME |
+---------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+---------+------------+
14 rows in set (0.00 sec)
mysql> SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO ;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+-----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+-----------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YOURK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YOURK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+-----------+
14 rows in set (0.00 sec)
mysql> SELECT A.ENAME EMPNAME ,A.SAL EMPSAL ,B.DNAME DEPTNAME,C.GRADE GRADE FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO JOIN SALGRADE C ON A.SAL>C.LOSAL AND A.SAL<C.HISAL ;
+---------+------------+-------+
| EMPNAME | DEPTNAME | GRADE |
+---------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| MILLER | ACCOUNTING | 2 |
+---------+------------+-------+
12 rows in set (0.00 sec
mysql> SELECT A.ENAME EMPNAME ,A.SAL EMPSAL ,B.DNAME DEPTNAME,C.GRADE GRADE FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO JOIN SALGRADE C ON A.SAL>C.LOSAL AND A.SAL<C.HISAL ;
+---------+---------+------------+-------+
| EMPNAME | EMPSAL | DEPTNAME | GRADE |
+---------+---------+------------+-------+
| SMITH | 800.00 | RESEARCH | 1 |
| ALLEN | 1600.00 | SALES | 3 |
| WARD | 1250.00 | SALES | 2 |
| JONES | 2975.00 | RESEARCH | 4 |
| MARTIN | 1250.00 | SALES | 2 |
| BLAKE | 2850.00 | SALES | 4 |
| CLARK | 2450.00 | RESEARCH | 4 |
| KING | 5000.00 | ACCOUNTING | 5 |
| TURNER | 1500.00 | SALES | 3 |
| ADAMS | 1100.00 | RESEARCH | 1 |
| JAMES | 950.00 | SALES | 1 |
| MILLER | 1300.00 | ACCOUNTING | 2 |
+---------+---------+------------+-------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> SELECT A.ENAME EMPNAME ,B.DNAME DEPTNAME,C.GRADE GRADE FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO JOIN SALGRADE C ON A.SAL BETWEEN C.LOSAL AND HISAL ;
+---------+------------+-------+
| EMPNAME | DEPTNAME | GRADE |
+---------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | RESEARCH | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+---------+------------+-------+
14 rows in set (0.00 sec)子查询
子查询就是嵌套的select语句，可以理解为子查询是一张表
在where语句中使用子查询，也就是在where语句中加入select语句
1、什么是子查询？
- select 语句嵌套select语句
2、子查询可以出现在哪？
select …(select)
from…(select)
where…(select)
WHERE后面使用子查询
3、案例：找出薪水比公司平均薪水高的员工，显示员工名和薪水
select ENAME,SAL from EMP where SAL >avg(SAL);
以上语句执行报错，分组函数不能直接使用在where后面
第一步： 找出公司的平均薪水
mysql> select avg(SAL) from EMP;
+-------------+
| avg(SAL) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
第二步：找出薪水大于平均薪水的员工信息：
mysql> SELECT ENAME,SAL FROM EMP WHERE SAL > (select avg(SAL) from EMP);
+-------+---------+
| ENAME | SAL |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)4.from后面使用子查询【小窍门：将查询语句当作临时表】
案例： 找出每一个部门的平均薪水，并且要求显示平均薪水的薪水等级。
第一步：找出每个部门的平均薪水
select DEPTNO,avg(SAL) from EMP group by DEPTNO;
mysql> select DEPTNO,avg(SAL) avgsal from EMP group by DEPTNO;
+——–+————-+
| DEPTNO | avgsal |
+——–+————-+
| 10 | 3150.000000 |
| 20 | 2220.833333 |
| 30 | 1566.666667 |
+——–+————-+
3 rows in set (0.00 sec)
第二步：将上面查询结果当作临时表t,t表和salgrade s表进行连接，条件：t.avgsal between s.losal and s.hisal;
mysql> select a.avgsal , b.GRADE from (select DEPTNO,avg(SAL) avgsal from EMP group by DEPTNO) a join SALGRADE b on a.avgsal between b.LOSAL and b.HISAL ;
+-------------+-------+
| avgsal | GRADE |
+-------------+-------+
| 3150.000000 | 5 |
| 2220.833333 | 4 |
| 1566.666667 | 3 |
+-------------+-------+
3 rows in set (0.00 sec)5 在select后面使用子查询【了解】
mysql> SELECT E.ENAME,(SELECT D.DNAME FROM DEPT D WHERE E.DEPTNO=D.DEPTNO) AS DNAME FROM EMP E;
+——–+————+
| ENAME | DNAME |
+——–+————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | RESEARCH |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+——–+————+
14 rows in set (0.00 sec)
union
mysql> SELECT ENAME,JOB FROM EMP WHERE JOB = 'MANAGER' OR JOB='SALESMAN';
+--------+----------+
| ENAME | JOB |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
mysql> SELECT ENAME,JOB FROM EMP WHERE JOB='MANAGER';
+-------+---------+
| ENAME | JOB |
+-------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
3 rows in set (0.01 sec)
mysql> SELECT ENAME,JOB FROM EMP WHERE JOB='SALESMAN';
+--------+----------+
| ENAME | JOB |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
4 rows in set (0.00 sec)
mysql> SELECT ENAME,JOB FROM EMP WHERE JOB='MANAGER'
-> UNION
-> SELECT ENAME,JOB FROM EMP WHERE JOB='SALESMAN';
+--------+----------+
| ENAME | JOB |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)LIMIT
1 limit用来获取一张表中的某部分数据
2 limit只有在MYSQL数据库中存在，不通用，是MYSQL数据库管理系统的特色。
3 案例：找出员工表前5条记录
mysql> SELECT ENAME FROM EMP limit 5;
+--------+
| ENAME |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
+--------+
5 rows in set (0.00 sec)
以上SQL语句的"limit 5"中的5表示从表中记录下表0开始，取5条等同于下面的SQL语句：
mysql> select ENAME FROM EMP LIMIT 0,5;
+--------+
| ENAME |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
+--------+
5 rows in set (0.00 sec)
LIMIT的使用语法格式：
limit起始下标，长度
起始下标没有指定，默认从0开始，0表示表中第一条记录。
4 案例：找出公司中工资排名在前5名的员工
思路：按照工资降序排列取前5个
mysql>
mysql> select ENAME,SAL FROM EMP ORDER BY SAL DESC;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.01 sec)
mysql> select ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5;
+-------+---------+
| ENAME | SAL |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
5 案例：找出工资排名【3-9】名的员工
mysql> select ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 3,9;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
+--------+---------+
9 rows in set (0.00 sec)
mysql> select ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 2,8;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
+--------+---------+
8 rows in set (0.00 sec)
6 MYSQL中通用的分页SQL语句：
每页显示3条记录
第一页：0,3
第二页：3,3
第三页：6,3
第四页：9,3
每页显示pageSize条记录
第pageNo页：( pageNo - 1 ) * pageSize , pageSize
SELECT
t.*
FROM
t
ORDER BY
t.x DESC/ASC
LIMIT ( pageNo - 1 ) * pageSize , pageSize创建表 DDL()
语法格式：
create table tableName(
columnName dataType(length),
columnName dataType(length),
);
set character_set_results=’utf-8’;表：
1 表格(table),用来存储数据，表格是一种结构化文件。
2 表格行被称为记录(表中的数据)，表格列被称为字段。
3 表格的字段属性包括：字段名称、字段数据类型、字段长度、字段约束4 创建表的语法：
CREATE TABLE tablename( columnname1 dataType(length), columnname2 dataType(length), );5 关于MYSQL数据库中的数据类型？
- VARCHAR * 可变长度字符串 * VARCHAR(3) 表示存储的数据不能超过3个字符长度 - CHAR * 定长字符串 * VARCHAR和CHAR对比： - 都是字符串类型 - VARCHAR比较智能，可以根据实际的数据长度分配空间，比较节省空间， 但是在分配的时候需要执行相关的判断程序，效率较低 - CHAR不需要动态分配空间，所以执行效率很高，但是可能会导致空间浪费。 - 若字段中的数据布局配伸缩性，建议采用CHAR类型存储 - 若字段中的数据具备很强伸缩性，建议采用VARCHAR类型存储。 * CHAR(3)：表示存储的数据不能超过3个字符长度 - INT * 整数型 * INT(3) 表示最大可以存储999(老师课堂讲解有误) INT(3) 3表示的是可以显示的有效长度(添加补零功能后，比较明显) CREATE TABLE test01( with_int_length int(3), without_int_length int ) insert into test01(with_int_length,without_int_length) values(1111,1111); insert into test01(with_int_length,without_int_length) values(11111,11111); commit; mysql> select * from test01; +-----------------+--------------------+ | with_int_length | without_int_length | +-----------------+--------------------+ | 1111 | 1111 | | 11111 | 11111 | +-----------------+--------------------+ 2 rows in set (0.00 sec) CREATE TABLE test02 ( with_length int(3) ZEROFILL, without_length int ) ENGINE=innodb, CHARSET=utf8 insert into test02 (with_length, without_length) VALUE (11, 11); select * from test02; mysql> select * from test02; +-------------+----------------+ | with_length | without_length | +-------------+----------------+ | 011 | 11 | +-------------+----------------+ 1 row in set (0.01 sec) - BIGINT * 长整型 * 对应Java程序中的long类型 - FLOAT * 浮点型单精度 - DOUBLE * 浮点型双精度 * DOUBLE(7,2) 表示7个有效数字，2个小数位 - DATE * 日期类型 * 在实际开发中为了通用，所以日期类型一般不使用，采用字符串代替日期比较多。 - BLOB * Binary Large OBject 二进制大对象 * 专门存储图片声音视频等数据 * 数据库表中存储一个图片是很常见的，但是存储一个比较大的视频是很少见的。 一般都是提供一个视频的链接地址 - CLOB * Character Large OBject 二进制大对象 * 可以存储比较打的文本，4G+ - 其它
6 创建表格[学生表]
设计：建立学生信息表，字段包括：
学号 【INT】
姓名 【VARCHAR】
性别 【CHAR】
出生日期【DATA】
邮箱地址【VARCHAR】
建表语句：
CREATE TABLE t_student(
NO INT(10),
NAME VARCHAR(32),
SEX CHAR(1),
BIRTH DATE,
EMAIL VARCHAR(128)
);
注意：
* 表格的名字最好以t_ 或者 tb1_开始，增强可读性
* VARCHAR长度最好是2的倍数，方便存储中文
删除表格：
drop table t_student;【这种删除格式，若数据库没有这个表格，会报错】
drop table if exists t_student;【最好采用这种方式删除，mysql数据库管理系统的特色语法】
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| NO | int(10) | YES | | NULL | |
| NAME | varchar(32) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| BIRTH | date | YES | | NULL | |
| EMAIL | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
7. 向t_student表格中插入数据
7.1 向表格插入数据必须使用insert语句，这属于DML语句
7.2 DML语句包括：insert,update,delete【增删改表中的数据，和表结构无关】
7.3 insert语句的语法格式：
INSERT INTO tablename(columnname1,columnname2,columnname3,...) values(value1,value2,value3,...)字段和值必须一一对应，个数必须相同，数据类型必须一致
7.4 向t_student表格中插入数据
INSERT INTO t_student(NO,NAME,SEX,BIRTH,EMAIL) values(1,'cntsp','m','1948-11-51','cntsp@beijingpowernode.com');
INSERT INTO t_student(NO,NAME,SEX,BIRTH,EMAIL) values(2,'zhongjie','m','1958-11-21','zhongjie@beijingpowernode.com');
mysql> select * from t_student;
+------+----------+------+------------+-------------------------------+
| NO | NAME | SEX | BIRTH | EMAIL |
+------+----------+------+------------+-------------------------------+
| 2 | zhongjie | m | 1958-11-21 | zhongjie@beijingpowernode.com |
| 1 | cntsp | m | 1948-11-01 | cntsp@beijingpowernode.com |
+------+----------+------+------------+-------------------------------+
7.5 可以这样执行insert语句吗？
INSERT INTO t_student(name,email) VALUES('wangwu','wangwu@bjpowernode.com');
mysql> INSERT INTO t_student(name,email) VALUES('wangwu','wangwu@bjpowernode.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------------+-------------------------------+
| NO | NAME | SEX | BIRTH | EMAIL |
+------+----------+------+------------+-------------------------------+
| 2 | zhongjie | m | 1958-11-21 | zhongjie@beijingpowernode.com |
| 1 | cntsp | m | 1948-11-01 | cntsp@beijingpowernode.com |
| NULL | wangwu | NULL | NULL | wangwu@bjpowernode.com |
+------+----------+------+------------+-------------------------------+
3 rows in set (0.00 sec)
默认情况下：当一张表被创建之后，没有指定约束的话，可以为NULL，并且没有指定任何默认值的话，默认值为NULL，这里的默认值NLL表示：若插入数据的时候没有给该字段指定任何数据，默认插入NULL值。
我们可以再向NO,SEX,BIRTH字段中插入值吗？
INSERT INTO t_student(NO,SEX,BIRTH) VALUES(3,'m','1990-11-12');
mysql> INSERT INTO t_student(NO,SEX,BIRTH) VALUES(3,'m','1990-11-12');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------------+-------------------------------+
| NO | NAME | SEX | BIRTH | EMAIL |
+------+----------+------+------------+-------------------------------+
| 2 | zhongjie | m | 1958-11-21 | zhongjie@beijingpowernode.com |
| 1 | cntsp | m | 1948-11-01 | cntsp@beijingpowernode.com |
| NULL | wangwu | NULL | NULL | wangwu@bjpowernode.com |
| 3 | NULL | m | 1990-11-12 | NULL |
+------+----------+------+------------+-------------------------------+
4 rows in set (0.00 sec)
以上执行insert语句只能导致底层数据库表中新增一条记录。
若希望NULL变成其它数据，需要使用UPDAT语句进行更新操作。
7.6 INSERT语句中字段的名字可以省略吗？
mysql> INSERT INTO t_student VALUES(4,'zhaoliu','m','1960-12-16','zhaoliu@163.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------------+-------------------------------+
| NO | NAME | SEX | BIRTH | EMAIL |
+------+----------+------+------------+-------------------------------+
| 2 | zhongjie | m | 1958-11-21 | zhongjie@beijingpowernode.com |
| 1 | cntsp | m | 1948-11-01 | cntsp@beijingpowernode.com |
| NULL | wangwu | NULL | NULL | wangwu@bjpowernode.com |
| 3 | NULL | m | 1990-11-12 | NULL |
| 4 | zhaoliu | m | 1960-12-16 | zhaoliu@163.com |
+------+----------+------+------------+-------------------------------+
5 rows in set (0.00 sec)
INSERT INTO t_student VALUES(5,'LTIANLI','m','1990-05-18');
mysql> INSERT INTO t_student VALUES(5,'LTIANLI','m','1990-05-18');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
结论：建立不要将前面的字段省略，如省略就表示将表中所有字段全部加上。省略之后程序不健壮，无法修改表结构，修改表结构，可能导致insert语句执行失败。
7.7 插入中文试试？【在DOS窗口中】
mysql> show create table t_student;+———–+——————————————————————————————————————————————————————————————————————————–+
| Table | Create Table |
+———–+——————————————————————————————————————————————————————————————————————————–+
| t_student | CREATE TABLE t_student (
NO int(10) DEFAULT NULL,
NAME varchar(32) DEFAULT NULL,
SEX char(1) DEFAULT NULL,
BIRTH date DEFAULT NULL,
EMAIL varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+———–+——————————————————————————————————————————————————————————————————————————–+
1 row in set (0.00 sec)
编码方式为：utf8
DOS窗口操作数据库
set character_set_result = ‘GBK’;
插入失败，原因是DOS窗口是GBK的编码方式，但是数据库表只能接受UTF8
怎么解决？
使用MYSQL Front工具插入数据【MYSQL的客户端软件，在实际开发中使用较多】
使用mysql front 工具插入数据成功，在DOS窗口中使用select语句查询的时候出现乱码，怎么解决？
修改查询结果集的显示编码方式，这里修改的不是DOS窗口的编码：
mysql> set character_set_results = ‘GBK’;【临时有效，当前窗口有效】
查看MYSQL的相关字符编码方式：show variables like '%char%';
8 关于创建表格的时候给字段添加默认值：
drop table if exists t_student;
create table t_student(
no int(10),
name varchar(32),
sex char(1) default 'm'
);
insert into t_student(no,name) values(1,'jack');
insert into t_student(no,name) values(2,'lucy');
commit;
mysql> select * from t_student;
+------+------+------+
| no | name | sex |
+------+------+------+
| 1 | jack | m |
| 2 | lucy | m |
+------+------+------+
2 rows in set (0.00 sec)
mysql> desc t_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int(10) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | m | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
关于SQL脚本：
你是怎么看SQL脚本的？
- 该文件是一个普通的文本文件，后缀名.sql，被称为SQL脚本
- 在SQL脚本中有大量的SQL语句，想批量的执行SQL语句，可以将这些SQL语句写入SQL脚本文件中，
直接使用source执行这个脚本，可以执行大量的SQL语句。
- 若项目中期就职，可能数据库表，以及表中的记录都已经准备好了，可能Team leader就给你一个SQL脚本，直接执行这个SQL脚本将底层数据库中的数据完成初始化，就可以开发了
9 获取系统当前时间
mysql> select now()
-> ;
+---------------------+
| now() |
+---------------------+
| 2019-06-22 15:55:37 |
+---------------------+
1 row in set (0.00 sec)
创建表：机构表
create table t_organization(
code char(20),
name varchar(32),
createTime date
);
insert into t_organization(code,name,createTime) values('11111','国家外汇局',now());
insert into t_organization(code,name,createTime) values('11112','河北省外汇局',now());
insert into t_organization(code,name,createTime) values('11112','河南省外汇局',now());
insert into t_organization(code,name,createTime) values('11112','山东省外汇局',now());
10 表的复制【快速复制】
mysql>create table EMP1 as select * from EMP;
mysql> create table EMP1 as select * from EMP;
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| DEPT |
| EMP |
| EMP1 |
| SALGRADE |
| t_student |
+-----------------------+
5 rows in set (0.00 sec)
mysql> select * from EMP1;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7396 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> create table EMP2 as select EMPNO,ENAME,SAL FROM EMP;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from EMP2;
+-------+--------+---------+
| EMPNO | ENAME | SAL |
+-------+--------+---------+
| 7396 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
14 rows in set (0.01 sec)
语法结构：
CREATE TABLE tablename AS SELECT COLUMNNAME,... FROM tablename;
11 将查询结果插入到某张表中
insert into EMP2 select * from EMP2 where sal=3000;
mysql> insert into EMP2 select * from EMP2 where sal=3000;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM EMP2;
+-------+--------+---------+
| EMPNO | ENAME | SAL |
+-------+--------+---------+
| 7396 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+--------+---------+
16 rows in set (0.00 sec)
12 增/删/改 表结构【DDL语句】
drop table if exists t_student;
create table t_student{
NAME VARCHAR(10),
SEX CHAR(1)
};
INSERT INTO t_student(NAME,SEX) values('JACK','M');
INSERT INTO t_student(NAME,SEX) VALUES('MARY','F');
commit;
DROP TABLE IF EXISTS runoob_tb1;
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加字段
如：需求发生改变，需要向runoob_tbl中加入联系作者字段，字段名称为：author类型为varchar(100)
alter table runoob_tbl add author varchar(100) not null default '',ALGORITHM=INPLACE;
给runoob_tbl表格添加一个作者字段
alter table runoob_tbl add author varchar(100) not null default '',ALGORITHM=INPLACE;
mysql> desc runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| runoob_title | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
| author | varchar(100) | NO | | | |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
将ru表格中的tel字段长度扩展到120个长度【改】
ALTER TABLE runoob_tbl MODIFY author VARCHAR(120);
mysql> desc runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| runoob_title | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
| author | varchar(100) | NO | | | |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE runoob_tbl MODIFY author VARCHAR(120);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| runoob_title | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
| author | varchar(120) | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
将runoob_tbl 表格中的author字段删除【删】
mysql> desc runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| runoob_title | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
| author | varchar(120) | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE runoob_tbl DROP author;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc runoob_tbl;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| runoob_title | varchar(100) | NO | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
13 增/删/改 表中的数据【insert, update,delete 属于DML语句】
update
update 语句的语法格式
UPDATE tablename SET 字段名=字段值 ,字段名=字段值 where 条件;
注意： update语句没有条件，会将一张表所有的数据都更新
delete 语法格式：
DELETE FROM tablename where 条件;
注意： 若没有条件限制，会将这张表的数据全部删除
约束和非空约束
1 什么是约束，为什么使用约束？
- 约束对应的英语单词：constraint
- 约束实际上就是表中数据的限制条件
- 表的设计的时候加入约束的目的就是为了保证表中的记录完整和有效
2 约束包括哪些呢？
- 非空约束 (not null)
- 唯一性约束 (unique)
- 主键约束 (primary key) 简称 PK
- 外键约束 (foreign key) 简称 FK
- 检查约束 (MYSQL不支持，oracle支持)
3 非空约束
- not null约束的字段，不能为NULL值，必须给定具体的数据
- 创建表，给字段添加非空约束【创建用户表，用户名不能为空】
4 唯一性约束
- unique约束的字段具有唯一性，不可重复
- 创建用户，保证邮箱地址唯一
drop table if exists t_user;
//列级约束
create table t_user(
id int(10),
name varchar(32) not null,
email varchar(128) unique
);
insert into t_user(id,name,email) values(1,'jack','jack@bjpowernode.com');//成功
insert into t_user(id,name,email) values(2,'abc''jack@bjpowernode.com');//失败
EEROR 1062 (23000): Duplicate entry 'jack@bjpowernode.com' for key 'emial'
insert into t_user(id,name) value(2,'abc');//成功
insert into t_user(id,name) values(3,'def');//成功
unique约束的字段不能重复，但是可以为NULL
drop table if exists t_user;
//表级约束
create table t_user{
id int(10),
name varchar(32) not null,
email varcher(128),
unique(email)
};
//表级约束给多个字段联合添加约束【一下程序表示name和email连个字段联合唯一】
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32) not null,
email varchar(128),
unique(name,email)
);
//表级约束还可以给约束起名字
//为什么要起名字？因为以后要通过这个名字来删除这个约束
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32) not null,
email varchar(128),
constraint t_user_email_unique unique(email)
);
//查询约束的名字
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.00 sec)
mysql> desc COLLATIONs;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(32) | NO | | | |
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| ID | bigint(11) | NO | | 0 | |
| IS_DEFAULT | varchar(3) | NO | | | |
| IS_COMPILED | varchar(3) | NO | | | |
| SORTLEN | bigint(3) | NO | | 0 | |
+--------------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> DESC TABLE_CONSTRAINTS;【该表格专门存储约束信息的】
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO | | | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| CONSTRAINT_TYPE | varchar(64) | NO | | | |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select CONSTRAINT_NAME from TABLE_CONSTRAINTS where TABLE_NAME='t_user';
5 not null和unique可以联合使用吗？
- 可以联合使用
- 被not null unique 约束的字段，既不能为空，也不能重复
- 例子：
6 主键约束-primary key 简称:PK
6.1 主键涉及到的术语:
- 主键约束
- 主键字段
- 主键值
6.2 以上的主键约束、主键字段、主键值的关系？
- 表中某个字段添加主键约束之后
该字段被称为主键字段，主键字段中
出现的每一个数据能被称为主键值
6.3 给某个字段添加主键约束之后，该字段不能重复，并且也不能为空
效果和 not null unique 约束相同，但是本质不同，主键约束除了可以做到"not null unique"之外，主键字段还会默认添加"索引-index"
6.4 一张表应该有主键字段，若没有，表示这张表是无效的。
"主键值"是当前行数据的唯一标识。"主键值"是当前行数据的身份证号。
即使表中的两行记录相关的数据是相同的，但是由于主键值不同，我们认为这是两行完全不同的数据。
6.5 给一个字段添加主键约束，被称为单一主键。
//单一主键
//列级约束
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT(5) UNSIGNED PRIMARY KEY,
name VARCHAR(20)
);
//单一主键
//表级约束
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT(5) UNSIGNED,
name VARCHAR(20),
PRIMARY KEY(id)
);
//单一主键
//表级约束
//起名
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT(3) UNSIGNED,
name VARCHAR(32),
email VARCHAR(40),
CONSTRAINT t_user_id_pk PRIMARY KEY(id)
);
6.6 给多个字段联合添加一个主键约束，被称为复合主键。
//复合主键
//表级约束
//起名
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT(2) UNSIGNED,
name VARCHAR(10),
CONSTRAINT t_user_id_pk PRIMARY KEY(id,name)
);
INSERT INTO t_user(id,name) values(10000,'jack');
INSERT INTO t_user(id,name) values(10000,'cntsp');
INSERT INTO t_user(id,name) values(5456,'cntsp');
COMMIT;
SELECT * FROM t_user;
6.7 无论是单一主键还是复合主键，一张表主键约束只能有一个。
6.8 主键根据性质分类：
- 自然主键
* 主键值若是一个自然数，这个自然数和当前表的业务没有任何关系，这种主键叫做自然主键
- 业务主键
* 主键值若和当前表中业务紧密相关的，那么这种主键值被称为业务主键，当业务数据发生改变的时候，主键值通常受到影响。
所以业务主键使用较少。大部分都是使用自然主键
6.9 在MYSQL数据库管理系统中提供了一个自增的数字，专们用来自动生成主键值。
主键值不需要用户维护，也不需要用户提供了，自动生成的，这个自增的数字默认从1开始，以1递增：
1,2,3,4,5,6,7,8.....
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id INT(10) UNSIGNED PRIMARY KEY auto_increment,
name VARCHAR(32),
email VARCHAR(40)
);
INSERT INTO t_user(name,email) values('jack','123@qq.com');
INSERT INTO t_user(name,email) values('jack','123@qq.com');
COMMIT;
7 外键约束foreign key-简称FK
7.1 外键涉及到的术语：
- 外键约束
- 外键字段
- 外键值
7.2 外键约束、外键字段、外键值之间的的关系？
某个字段添加外键约束之后，该字段称为外键字段
外键字段中的每一个数据都是外键值
7.3 外键也分为：单一外键【给一个字段添加外键约束】和复合外键【给多个字段联合添加一个外键约束】
7.4 一张表中可以有多个外键字段
7.5 分析场景：
请设计数据库表用来存储学生和班级信息，给出两种解决方案
学生信息和班级信息之间的关系，一个班级对应多个学生，这是典型的一对多的关系
第一种涉及方案：将学生信息和班级信息存储到一张表中。
学生信息表t_student
sno(pk) sname classno cname
---------------------------------------------------------
1 jack 100 北京海定区...高三1班
2 lucy 100 北京海定区...高三1班
3 zhangsan 100 北京海定区...高三1班
4 ford 200 北京海定区...高三2班
5 king 200 北京海定区...高三2班
6 allen 300 北京海定区...高三3班
7 lisi 300 北京海定区...高三3班
以上设计的缺点：数据臃肿
第二种解决方案：将学生信息和班级信息分开两张表存储，学生表 + 班级表
学生表t_student
sno(pk) sname classno(fk)
-----------------------------------
1 jack 100
2 lucy 100
3 zhangsan 100
4 ford 200
5 king 200
6 allen 300
7 lisi 300
班级表t_class
cno(pk) cname
------------------------------------------------
100 北京海淀区....高三1班
200 北京海淀区....高三2班
300 北京海淀区....高三3班
结论：为了保证t_student表中的class字段中的数据必须来自于t_class表中cno字段中的数据，有必要给t_studnet
表中的classno字段添加外键约束，classno字段被称为外键字段，该字段中的100 200 300被称为外键值。classno这里是一个单一外键字段
注： 外键值可以为NULL
注： 外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束。
注： 有了外键引用之后，表分为父表和子表，以上父表是：班级表，子表是：学生表
创建表先创建父表，再创建子表，删除数据的时候先删除子表中的数据，再删除父表中的数据，插入父表中的数据，再插入子表中的数据。
-------------------------------------SQL文----------------------------------------------------------------
DROP TABLE IF EXISTS t_student;
DROP TABLE IF EXISTS t_class;
CREATE TABLE t_class(
cno int(3) unsigned primary key,
cname varchar(120) not null unique
);
CREATE TABLE t_student(
sno int(3) unsigned primary key,
sname varchar(32) not null,
classno int(3) unsigned,
constraint t_student_classno_fk foreign key(classno) references t_class(cno)
);
insert into t_class(cno,cname) values(100,'高三1班');
insert into t_class(cno,cname) values(200,'高三2班');
insert into t_class(cno,cname) values(300,'高三3班');
insert into t_student(sno,sname,classno) values(1,'jack',100);
insert into t_student(sno,sname,classno) values(2,'lucy',100);
insert into t_student(sno,sname,classno) values(3,'zhangsan',100);
insert into t_student(sno,sname,classno) values(4,'ford',200);
insert into t_student(sno,sname,classno) values(5,'king',200);
insert into t_student(sno,sname,classno) values(6,'allen',300);
insert into t_student(sno,sname,classno) values(7,'lisi',300);
select * from t_student;
select * from t_class;
commit;
insert into t_student(sno,sname,classno) values(8,'lisi',500);
-------------------------------------SQL文-------------------------------------------------------------------
找出每个学生的班级名称
mysql> SELECT s.sname,c.cname from t_student s join t_class c on s.classno = c.cno;
+----------+------------+
| sname | cname |
+----------+------------+
| jack | 高三1班 |
| lucy | 高三1班 |
| zhangsan | 高三1班 |
| ford | 高三2班 |
| king | 高三2班 |
| allen | 高三3班 |
| lisi | 高三3班 |
+----------+------------+
7 rows in set (0.00 sec)
重点：典型的一对多，在多的一方加外键
8 级联更新和级联删除
- 添加级联更新和级联删除的时候要在外键约束后面添加
- 在删除父表中数据的时候，级联删除子表中的数据
* 删除外键约束
alter table t_student drop foreign key t_student_classno_fk;
* 添加外键的约束
alert table t_student add
constraint t_student_classno_fk
foreign key(classno) t_class(cno) on delete cascade;
- 在更新父表中数据的时候，级联更新子表中的数据
* 删除外键约束
alter table t_student drop foreign key t_student_classno_fk;
* 添加外键的约束
alert table t_student add
constraint t_student_classno_fk
foreign key(classno) t_class(cno) on update cascade ;
- 以上的级联更新和级联删除谨慎使用，因为级联操作会将数据改变或者删除【数据无价】
15 存储引擎
存储引擎的使用
数据库中的各表均被(在创建表时)指定的存储引擎来处理。
服务器可用的引擎依赖于以下因素：
> * MySQL的版本
> * 服务器在开发时如何被配置
> * 启动选项
为了了解当前服务器中有哪些存储引擎可用，可使用SHOW ENGINES语句：
mysql> SHOW ENGINES\G;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
15.2.1 MyISAM存储引擎
> * MyISAM 存储引擎是MySQL最常用的引擎
> * 它管理的表具有以下特征：
- 使用三个文件表示每个表:
> * 使用三个文件表示每个表：
* 格式文件 -存储表结构的定义(mytable.frm)
* 数据文件 -存储表行的内容(mytable.MYD)
* 索引文件 -存储表上索引(mytable.MYI)
- 灵活的AUTO_INCREMENT字段处理
- 可被转换为压缩、只读表来节省空间
15.2.2 InnoDB存储引擎
> * InnoDB存储引擎是MySQL的缺省引擎。
> * 它管理的表具有以下主要特征：
- 每个InooDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
- 提供全ACID兼容
- 在MySQL服务器崩溃后提供自动恢复
- 多版本(MVCC)和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
MEMORY存储引擎
* 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快
* MEMORY存储引擎管理的表具有一下特征：
- 在数据库目录内，每个表均以.frm格式的文件表示
- 表数据及索引被存储在内存中
- 表级锁机制
- 不能包含TEXT或BLOB字段
* MEMORY存储引擎以前被称为HEAP引擎。
15.3 选择合适的存储引擎
* MyISAM 表最适合于大量的数据读而少量数据更新的混合操作，MyISAM表的另一种使用情形是使用压缩的只读表。
* 如果查询中包含较多的数据更新操作，应使用InnoDB,其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
* 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。
事务：
1 什么是事务？
- 事务对应的英语但是：Transaction
- 事务：一个最小的不可再分的工作单元。
- 通常一个事务对应一个完整的业务。 【例如：银行账户转账业务，该业务就是一个最小的工作单元】
- 而一个完整的业务需要批量的DML语句(insert,update,delete)共同联合完成。
- 事务只和DML语句有关系，或者说DML语句才有事务
- 以上所描述的"批量的DML语句"共有多少条DML语句，这个和业务逻辑有关系
业务逻辑不同DML语句的个数不同。
2. 关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分。也就是说银行账户转账是一个事务。
t_act 账户表
actno balance
-----------------------------------------
act-001 50000.0
act-002 10000.0
执行转账操作(10000):
update t_act set balance=40000.0 where actno='act-001';
update t_act set balance=20000.0 where actno='act-002';
以上的两条DML语句要求必须同时成功或者同时失败，最小单元，不可再分。当第一条DML语句执行成功之后，并不能将底层数据库中第一个账户的数据修改。只是将操作记录一下，这个记录是在内存中完成的，当第二条DML语句执行失败，情况所有的历史操作记录。要完成以上的功能，必须借助事务。
3 事务的四个特性：
- 原子性(A)【Atomicity,或称不可分割性】
* 事务是最小的工作单元，不可再分
- 一致性(C)【Consistency】
* 事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
- 隔离性(I)【isolation】
* 事务A和事务B之间具有隔离
- 持久性(D)【Durablity】
* 是事务的保证，事务终结的标志【内存中的数据持久到硬盘文件中】
4 关于一些术语：
- 开启事务: Start Transaction
- 事务结束：End Transaction
- 提交事务: Commit Transacton
- 回滚事务: Rollback Transaction
5 和事务有关的两条重要的SQL语句【TCL】
commit: 提交 成功的结束，将所有的DML语句操作历史记录和底层硬盘文件中的数据来一次同步。
rollback: 回滚 失败的结束，将所有的DML语句操作历史记录全部清空
6 事务开启的标志是什么？事务结束的标志是什么？
- 开启的标志
- 结束的标志
7 重点：
在事务进行过程中，未结束之前，DML语句是不会更改底层数据库文件中的数据。只是将历史操作记录一下，在内存中完成记录。
只有在事务结束的时候，而且是成功的结束的时候才会修改底层硬盘文件中的数据。
8 在MYSQL数据库管理系统中，事务的提交和回滚的演示
- 在MYSQL数据库管理系统中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句，开启了事务，并且提交了事务。
- 这种自动提交机制是可以关闭的: 【关闭的第一种方式】
start transaction; 手动开启事务
DML语句...
DML语句...
DML语句...
commit; 手动提交事务【事务成功的结束】
start transaction; 手动开启事务
DML语句...
DML语句...
DML语句...
DML语句...
DML语句...
rollback; 手动回滚事务【事务失败的结束】
- 如果禁用自动提交，事务可以跨越多条语句。
- 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。
* 自动提交模式下可以通过服务器变量AUTOCOMMIT来控制
例如：
关闭自动提交模式
mysql>SET AUTOCOMMIT = OFF;
或
mysql>SET SESSION AUTOCOMMIT = OFF;
打开自动提交模式
mysql>SET AUTOCOMMIT = ON;
或
mysql>SET SESSION AUTOCOMMIT = ON;
只对当前事务有效
mysql>show variables like '%auto%';查看变量状态
9 事务四个特性之一：隔离性(isolation)
9.1 事务A和事务B之间具有一定的隔离性
9.2 隔离性有隔离级别(4个)：
- read uncommitted 读未提交
- read committed 读已提交
- repeatable read 可重复性
- serializable 串行化
9.3 read uncommitted 读未提交
- 事务A和事务B，事务A未提交的数据，事务B可以读取到
- 这里读取到的数据可以叫做"脏数据"或者叫做“Dirty Read”
- 这种隔离级别是最低级别，这种级别一般都是在理论上存在，数据库默认的隔离级别一般都是高于该隔离界别的。
9.4 read committed 读已提交
- 事务A和事务B，事务A提交的数据，事务B才能读取到。
- 这种隔离级别高于上面的读未提交
- 换句话说：对方事务提交之后的数据，我当前事务才能过读取到。
- 这种隔离级别可以避免脏数据。
- 这种隔离界别会导致：“不可重复读取”
- Oracle数据库管理系统默认的隔离级别就是：读已提交
9.5 repeatable read 可重复读
- 事务A和事务B，事务A提交之后的数据，事务B读取不到。
- 事务B是可重复读取数据的
- 这种隔离级别高于读取数据的。
- 换句话说：对方提交之后的数据我还是读取不到.
- 这种隔离级别可以避免“不可重复读取”，达到可重复读取
- MYSQL数据库管理系统默认的隔离级别就是：可重复读
- 虽然可以达到“可重复读”的效果，但是会导致：“幻象读”
9.6 serializable 串行化
- 事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待。
- 这种事务隔离级别一般很少使用，吞吐量太低，用户体验不好。
- 这种隔离级别可以避免“幻象读”，每一次读取的都是数据库表中真实记录。
- 事务A和事务B不再并发。
10. 隔离级别与一致性问题的关系
隔离级别 脏读取 不可重复读取
设置事务的隔离级别：
第一种方式：修改my.ini配置文件
可选值：
- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ
- SERIALIZABLE
----------------my.ini----------------
[mysqld]
transaction-ioslation = READ-COMMITTED
第二种方式：使用命令动态设置事务的隔离级别
> * 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句
> * 语法模式为： SET|GLOBAL|SESSION|TRANSACTION|ISOLATIONLEVEL <isolation-level>
其中的<isolation-level>可以是：
- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ
- SERIALIZABLE
例如：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
隔离级别的作用范围：
事务隔离级别的作用范围分为两种：
- 全局及：对所有的会话有效
- 会话级：只对当前的会话有效
>* 例如：设置会话级隔离级别为READ COMMITTED；
SET TRANACTION ISOLATION LEVEL READCOMMITTED;
或者：
SET SESSION TRANACTION ISOLATION LEVEL READ COMMITTED;
设置全局级隔离级别为READ COMMITTED
SET GLOBAL TRANACTION ISOLATION LEVEL READ COMMITTED;
16.4.6 查看隔离级别
服务器变量tx_isolation(包含会话级和全局级两个变量)中保存着当前的会话隔离级别。
为了查看当前隔离级别，可访问tx_isolation变量；
- 查看会话级的当前隔离级别；
mysql> SELECT @@tx_isolation;
或：
mysql> SELECT @@session.tx_isolation;
查看全局级的当前隔离级别；
mysql>SELECT @@global.tx_isolation
12 并发事务与隔离级别示例
12.1 读未提交 read uncommitted——-会话1———————————————————————————–会话2——————-
set global transacton isolation level read uncommitted;
退出DOS窗口
user bjpowernode; user bjpowernode;
start transaction; start transacton;
insert into t_user(name) values(‘wangwu’); select * from t_user;
select * from t_us
12.2 可重复读 read committed——-会话1———————————————————————————–会话2——————-
set global transacton isolation level read committed;
退出DOS窗口
user bjpowernode; user bjpowernode;
start transaction; start transacton;
insert into t_user(name) values(‘ford’); select * from t_user;
commit; select * from t_user;
12.3 可重复读 repeatable read——-会话1———————————————————————————–会话2——————-
set global transacton isolation level repeatable read;
select @@tx_isolation;
|@@tx_isolation |
+—————+
|READ-COMMITTED |
退出DOS窗口
user bjpowernode; user bjpowernode;
start transaction; start transacton;
select * from t_user; select * from t_user;
insert into t_user(name) values(‘cntsp’); select * from t_user;
commit; select * from t_user;
索引：
1 什么是索引？
- 索引对应的英语单词是：index
- 索引相当于一本字典的目录，索引的作用是提高程序的检索【查询】效率。
2 主键自动添加索引，所以能够通过主键查询尽量通过主键查询，效率较高。
3 索引和表相同，都是一个对象，表是存储在硬盘文件中的，那么索引是表的一部分，索引也是存储在硬盘文件中。
4 在MYSQL数据库管理系统中，对表中记录进行检索的时候，通常包括几种检索方式：
第一种方式：全表扫描【效率较低】
假设有一张表：emp员工表
select * from emp where ename='KING';
若ename字段上没有添加索引，那么在通过ename字段过滤数据的时候
ename字段会被全表扫描
假设有一张表：dept部门表
select * from dept where dname='ACCOUNTING';
若dname字段上没有索引，那么在通过dname字段过滤数据的时候，dname字段会被全部扫描
第二种方式：通过索引进行检索【提高查询效率】
5. 一张表中有多个字段，每一个字段都是可以添加索引的。
6. 什么情况下适合给表中的某个字段添加索引呢？
- 该字段数据量庞大
- 该字段很少的DML操作【DML操作很多的话，索引也是需要不断的维护，效率反而降低】
- 该字段经常出现在where子句中
索引原理
索引被用来快速找出在一个列某一特定值的行，没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表知道它找出相关的行。表越大，花费时间越多，对于一个有序字段，可以运用二分查找(BinarySearch)，这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是B+Tree作为索引结构。
(主键：unique都会默认的添加索引)
7 怎么创建索引?
create index dept_dname index on dept(dname);
create unique index dept_dname_index on dept(dname);//添加unique表示dept表中的dname字段添加一个唯一性约束
8 删除索引
drop index dept_dname_index on dept;
19 DBA命令
19.1 新建命令
CREATE USER username IDENTIFIED BY 'password';
说明username --你将创建的用户名，password --该用户的登录密码：密码可以为空，如果为空则该用户可以不需要密码登录服务器
192. 授权
命令详解
mysql>grant all privileges on dbname.tbname to username@'loginip' identified by 'passwd' with grant option;
dbname=* 表示所有数据库
taname=* 表示所有表
19 导出
导出整个数据库
在windows 的DOS命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
导出指定库下的指定表
在window的dos命令窗口中执行：mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123
19.4.2 导入
mysql>source D:\bjpowernode.sql数据库设计三范式：
1、 数据库设计三范式：
设计数据库表的时候所依据的规范，共三个规范
第一范式：
要求有主键，并且要求每一个字段原子性不可再分
数据库表中不能出现重复记录，每个字段是原子性的不能再分
不符合第一范式的示例
------------------------------------------------------------
学生编号 学生姓名 联系方式
------------------------------------------------------------
1001 张三 zs@gamil.com.13599999999
------------------------------------------------------------
1002 李四 ls@gmail.com.13699999999
------------------------------------------------------------
1001 王五 ww@163.com.134888888888
------------------------------------------------------------
存在的问题：
** 最后一条记录和第一条重复(不唯一，没有主键) **
** 联系方式字段可以再分，不是原子性的 **
修改后为：
--------------------------------------------------------------------
学生编号 学生姓名 邮箱地址 电话号码
--------------------------------------------------------------------
1001 张三 zs@gamil.com 13599999999
--------------------------------------------------------------------
1002 李四 ls@gmail.com 13699999999
--------------------------------------------------------------------
1003 王五 ww@163.com 134888888888
--------------------------------------------------------------------
**关于第一范式,每一行必须唯一，也就是每个表必须有主键，这是我们数据库涉及的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定，如联系方式，为了开发上的便利可能就采用一个字段了**
第二范式：
要求所有非主键字段完全依赖主键，不能产生部分依赖
第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖
示例：
--------------------------------------------------------------------
学生编号 学生姓名 教师编号 电话号码
--------------------------------------------------------------------
1001 张三 001 王老师
--------------------------------------------------------------------
1002 李四 002 赵老师
--------------------------------------------------------------------
1003 王五 001 王老师
--------------------------------------------------------------------
1001 张三 002 赵老师
--------------------------------------------------------------------
确定主键：
--------------------------------------------------------------------
学生编号(PK) 教师编号(PK) 学生姓名 教师姓名
--------------------------------------------------------------------
1001 001 张三 王老师
--------------------------------------------------------------------
1002 002 李四 赵老师
--------------------------------------------------------------------
1003 001 王五 王老师
--------------------------------------------------------------------
1001 002 张三 赵老师
--------------------------------------------------------------------
以上虽然确定了主键，但此表会出现大量的沉余，主要涉及到的沉余字段为“学生姓名”和“教师姓名”，出现沉余的
原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。
解决方案如下：
学生信息表：
------------------------------------------------
学生编号(PK) 学生姓名
------------------------------------------------
1001 张三
------------------------------------------------
1002 李四
------------------------------------------------
1003 王五
------------------------------------------------
教师信息表
------------------------------------------------
教师编号(PK) 教师姓名
------------------------------------------------
001 王老师
------------------------------------------------
002 赵老师
------------------------------------------------
教师和学生的关系表
-------------------------------------------------------------------------
学生编号(PK) fk->学生表的学生编号 教师编号(PK)fk->教师表的教师编号
-------------------------------------------------------------------------
1001 001
-------------------------------------------------------------------------
1002 002
-------------------------------------------------------------------------
1003 001
-------------------------------------------------------------------------
1001 002
-------------------------------------------------------------------------
如果一个表是单一主键，那么它就是复合第二范式，部分依赖和主键有关系
以上是一种典型的“多对多”的涉及
第三范式：
所有非主键字段和主键字段之间不能产生传递依赖
建立在第二范式基础上的，非主键字段不能传递依赖与主键字段(不要产生传递依赖)
--------------------------------------------------------------------------
学生编号(PK) 学生姓名 班级编号 班级名称
--------------------------------------------------------------------------
1001 张三 01 一年一班
--------------------------------------------------------------------------
1002 李四 02 一年二班
--------------------------------------------------------------------------
1003 王五 03 一年三班
--------------------------------------------------------------------------
1004 赵六 03 一年三班
--------------------------------------------------------------------------
从上表可以看出，班级名称字段存在沉余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖与班级编号，
班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将沉余字段单独拿出来建立表，如：
学生信息表：
-----------------------------------------------------
学生编号(PK) 学生姓名 班级编号(FK)
-----------------------------------------------------
1001 张三 01
-----------------------------------------------------
1002 李四 02
-----------------------------------------------------
1003 王五 03
-----------------------------------------------------
1004 赵六 03
-----------------------------------------------------
班级信息表
-----------------------------------------------------
班级编号(PK) 班级名称
-----------------------------------------------------
01 一年一班
02 一年二班
-----------------------------------------------------
03 一年三班
-----------------------------------------------------
以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一对一
2、 几个比较经典的范式：
一对一：
第一种方案： 分两张表存储，共享主键
t_husband
hno(pk) hname
--------------------
1 张三
2 王五
3 赵六
--------------------
t_wife
wno(pk) wname 【wno同时也是fk，引用t_husband中的hno】
-----------------------------------
3 a
2 b
1 c
----------------------------------
第二种方案： 分两张表存储，外键唯一
t_husband
hno(pk) hname wifeno
----------------------------------
1 张三 100
2 王五 300
3 赵六 200
----------------------------------
t_wife
wno(pk) wname 【wno同时也是fk，引用t_husband中的hno】
-----------------------------------
100 a
300 b
200 c
----------------------------------
一对多：分两张表存储，在多的一方添加外键，这个外键字段引用一对一放中的主键字段
多对多：分三张表存储，在学生表中存储学生信息，在课程表中存储学生和课程信息
6 在实际开发中是怎样的？
数据库设计尽量遵循三范式，
但是还是根据实际情况进行取舍
有时可能会拿沉余换速度
最终目的要满足客户需求
