程序员最近都爱上了这个网站  程序员们快来瞅瞅吧!  it98k网:it98k.com

本站消息

站长简介/公众号

  出租广告位,需要合作请联系站长


+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

SQL之语句分类和用法

发布于2021-06-12 14:22     阅读(484)     评论(0)     点赞(4)     收藏(0)


参考:https://blog.csdn.net/mmake1994/article/details/85944438

Mysql的一些常用命令行

使用MySQL命令行客户端来装载数据库。(这些不是SQL语句,属于MySQL的命令。)

  1. 连接MySql

  2. 创建“study”数据库

mysql> create database study;
  1. 选择数据库
mysql> use study
  1. 导入数据
mysql>source  sql脚本路径

5)查看当前使用的数据库中有哪些表?

mysql>show tables; (这个不是SQL语句,属于MySQL的命令。)

6)查看有哪些数据库

show databases; 

7)查询当前使用的数据库

select  database();
  1. 删除数据库
drop database study;
  1. 查看表结构:
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |		部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       |		部门名称
| LOC    | varchar(13) | YES  |     | NULL    |       |		部门位置
+--------+-------------+------+-----+---------+-------+
  1. 查看创建表的语句:
show create table emp;

SQL语句

结构化查询语言(Structured Query Language)简称SQL,结构化查询语言是一种数据库查询和程序设计语言,用于存放数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统,可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。

SQL语句是通用的,但是不同的数据库系统有自己的一些命令查看数据库,创建数据库等等。

table是database的基本组成单元

数据查询语言(DQL)

数据查询语言(Data Query Language, DQL)是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有FROM,WHERE,GROUP BY,HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

简单的查询语句(DQL)

语法格式:

select field1, field2, field3,....  from table_name;

字段可以参与数学运算:

select field1, field2 * 12 from table_name;

给查询结果的列重命名?

select field1, field2 * 12 as new_name from table_name;

别名中有中文?

select field1, field2 * 12 as 中文 from table_name; // 错误
select field1, field2 * 12 as ‘中文’ from table_name;  // 正确

注意:标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号,尽量别用。

条件查询(DQL)

执行顺序:先from,然后where,最后select

语法格式:

select 
	field1, field2...
from
	table_name
where
	conditions(条件);

其中条件基本上都是对字段进行判断运算,比较运算符和逻辑运算符

比如其中比较运算符=,<>,< ,<= ,> ,>= ,!=之类的运算符就是判断字段值是否满足条件。

 select field1, field2 from emp where field1 >= 3000;
 select field1, field2 from emp where field2 != 'King';

and :并且(&&),or :或者(||)都是用于两个条件的拼接

select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30); 

between … and …. 两个值之间,等同于 >= and <=

select ename,sal from emp where sal between 1100 and 3000; // between...and...是闭区间 [1100 ~ 3000],数字好理解
select ename from emp where ename between 'A' and 'C';		// 按照字典序列比较大小,'A' and 'C' == ['A','C']

is null (is not null 不为空,字段值可以为null,null代表什么都不是,null不是0)

select ename,sal,comm from emp where comm is null;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+

in 包含,相当于多个or(not in不在这个范围中),表示字段值在其中的意思

select ename,job from emp where sal in(800, 5000); // in后面的值不是区间,是具体的值。
+-------+-----------+
| ename | job       |
+-------+-----------+
| SMITH | CLERK     |
| KING  | PRESIDENT |
+-------+-----------+

not可以取非,主要用在is 或in中,也可以对任何比较语句取非

select ename,job from emp where sal not in(800, 5000);
select ename,job,sal from EMP where not (sal < 1000);
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| JONES  | MANAGER   | 2975.00 |
| MARTIN | SALESMAN  | 1250.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 1500.00 |
| ADAMS  | CLERK     | 1100.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+

like 称为模糊查询,支持%或下划线匹配,%匹配任意个字符,_下划线,一个下划线只匹配一个字符,可以使用转义字符。

找出名字中第二个字母是A的?

select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

排序(升序、降序)(DQL)

默认为升序

select 
	ename,sal 
from 
	emp 
order by
	field1,field2;

注意:越靠前的字段越能起到主导作用。前面的字段排序完成之后,相同的值才按照后面的字段排序

select 
	ename,sal 
from 
	emp 
order by
	field1 desc,field2 asc;		// desc是降序,asc是升序,并且先按照field1降序,如果有相同的值,才按照field2升序
mysql> select EMPNO,sal from EMP order by sal desc , EMPNO asc;

±------±--------+
| EMPNO | sal |
±------±--------+
| 7839 | 5000.00 |
| 7788 | 3000.00 |
| 7902 | 3000.00 |
| 7566 | 2975.00 |
| 7698 | 2850.00 |
| 7782 | 2450.00 |
| 7499 | 1600.00 |
| 7844 | 1500.00 |
| 7934 | 1300.00 |
| 7521 | 1250.00 |
| 7654 | 1250.00 |
| 7876 | 1100.00 |
| 7900 | 950.00 |
| 7369 | 800.00 |
±------±--------+

执行顺序

select 
	字段						3
from
	表名						1
where
	条件						2
order by
	....						4

order by是最后执行的。

分组函数(多对一函数)(DQL)

sql中的五种分类函数

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

使用分组函数的注意点:

  1. 所有的分组函数都是对“某一组”数据进行操作的,也就是多行输入,在没有分组的前提下,只输出一行。
  2. 对某一字段进行聚合。
  3. 分组函数自动忽略NULL。
  4. SQL语句当中有一个语法规则,分组函数不可直接使用在where子句当中。这是因为分组函数要在group by之后执行,而group by在where之后执行
基本语法:
select		5
	..			
from			1
	..
where			2
	..
group by		3
	..
having		4
	..
order by		6

小案例:

mysql> select * from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 sum(sal) from EMP;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

count()和count(具体的某个字段),他们有什么区别?
count(
):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关) count(comm):
表示统计comm字段中不为NULL的数据总数量。

联系子查询,分组函数返回的一个数据可以作为条件筛选的判断值:

找出工资高于平均工资的员工?SELECT AVG(SAL) FROM EMP;可以作为一个值作为条件判断的标准。
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 |
+-------+---------+

单行处理函数(DQL)

什么是单行处理函数?
输入一行,输出一行。

ifnull() 空处理函数?
ifnull(可能为NULL的数据,被当做什么处理) : 属于单行处理函数。

comm字段含有null,但是数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。所以需要转换。
select ename,(sal+comm)*12 as yearsal from emp;	
		重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
		使用ifnull函数:
		select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

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 |
+--------+----------+

group by 和 having(DQL)

having : having是对分组之后的数据进行再次过滤。
group by : 按照某个字段或者某些字段进行分组。

mysql> select * from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
案例:找出每个工作岗位的最高薪资。
mysql> select max(sal),job from EMP group by job;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |
|  1600.00 | SALESMAN  |
+----------+-----------+

分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。 并且任何一个分组函数(count sum avg max
min)都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话,整张表的数据会自成一组。

select ename,max(sal),job from emp group by job;
在之前的mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错。语法错误。Oracle的语法规则比MySQL语法规则严谨。现在mysql也不支持这种写法。

记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。

mysql> select * from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

案例:找出每个部门不同工作岗位的最高薪资。
mysql> select deptno,job,max(sal) from EMP group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.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 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

找出每个部门的最高薪资,要求显示薪资大于2900的数据。
mysql> select max(sal),deptno from EMP group by deptno having max(sal) > 2900;		// 效率低,因为分组浪费时间,应该在分组之前就减少数据条数
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
+----------+--------+

mysql> select max(sal),deptno from EMP where sal > 2900 group by deptno;	// 但是,这种方式不是万能的
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
+----------+--------+


找出每个部门的平均薪资,要求显示薪资大于2000的数据。
mysql> select deptno,avg(sal) from EMP group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
+--------+-------------+

DQL语句写法和顺序(DQL)

总结一个完整的DQL语句怎么写?

select		5
	..
from			1	
	..
where			2
	..
group by		3
	..
having		4
	..
order by		6
	..

查询结果去重(DQL)

// 统计不通部门的不通工作一共有多少种
// distinct关键字去除重复记录,并将后面的字段当成一组
mysql> select distinct deptno,job from EMP;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+
9 rows in set (0.00 sec)
mysql> select ename,distinct job from EMP;
// 以上的sql语句是错误的。
// 记住:distinct只能出现在所有字段的最前面。
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 'distinct job from EMP' at line 1
mysql> select count(distinct job) from EMP;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

连接查询(DQL)

在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。
在实际开发中,一般一个业务都会对应多张表,比如:学生和班级,起码两张表。

stuno		stuname			classno		classname
-----------------------------------------------------------------------------------
1			zs					1		北京大兴区亦庄经济技术开发区第二中学高三12			ls					1		北京大兴区亦庄经济技术开发区第二中学高三1

学生和班级信息存储到一张表中,结果就像上面一样,数据会存在大量的重复,导致数据的冗余,所以需要一个存储班级信息的表。

连接查询分类(DQL)

连接查询的分类?
根据语法出现的年代来划分的话,包括:

SQL92(一些老的DBA可能还在使用这种语法。DBA:DataBase Administrator,数据库管理员)
SQL99(比较新的语法)

根据表的连接方式来划分
内连接:
等值连接 非等值连接 自连接
外连接:
左外连接(左连接) 右外连接(右连接)

笛卡尔积现象

笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。

也就是说连表查询的时候,每一条记录都会和另一张表进行匹配。

避免这种现象,就要对笛卡尔积进行过滤。

mysql> select 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 dname from DEPT;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

//连接查询
mysql> select ename,dname from EMP,DEPT;
+--------+------------+
| 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)

内连接(等值连接)

内连接的连接结果仅包含符合连接条件的行,参与连接的两个表都必须符合连接条件;

语法:
		...
			A
		join
			B
		on
			连接条件
		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  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

内连接(非等值连接)

最大的特点是:连接条件中的关系是非等量关系。

mysql> SELECT * FROM SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
mysql> SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL ORDER BY GRADE;
+--------+---------+-------+
| ENAME  | SAL     | GRADE |
+--------+---------+-------+
| JAMES  |  950.00 |     1 |
| SMITH  |  800.00 |     1 |
| ADAMS  | 1100.00 |     1 |
| MARTIN | 1250.00 |     2 |
| MILLER | 1300.00 |     2 |
| WARD   | 1250.00 |     2 |
| TURNER | 1500.00 |     3 |
| ALLEN  | 1600.00 |     3 |
| JONES  | 2975.00 |     4 |
| FORD   | 3000.00 |     4 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
+--------+---------+-------+

内连接(自连接)

自连接:最大的特点是:一张表看做两张表。自己连接自己。

案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。

mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。表一共十四条,查出结果13条是因为内连接只匹配符合的一行数据,不符合的自动排出
mysql> SELECT * FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO;		// 中间结果
+-------+--------+----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
mysql> SELECT E1.ENAME AS EMPLOY,E2.ENAME AS BOSS FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO;
+--------+-------+
| EMPLOY | BOSS  |
+--------+-------+
| 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 |
+--------+-------+

外连接

外连接?

什么是外连接,和内连接有什么区别?

内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的。

外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中
的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。

外连接的分类?
左外连接(左连接):表示左边的这张表是主表。
右外连接(右连接):表示右边的这张表是主表。

左连接有右连接的写法,右连接也会有对应的左连接的写法。

外连接(左连接,右连接)

外连接最重要的特点是:主表的数据无条件的全部查询出来。

案例:找出每个员工的上级领导?(所有员工必须全部查询出来。)
mysql> SELECT * FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO;		// 中间结果有14条,就算没有符合条件的,自动填充NULL作为一条数据
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
**|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |  NULL | NULL  | NULL      | NULL | NULL       |    NULL | NULL |   NULL |**
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+-------+-------+-----------+------+------------+---------+------+--------+
14 rows in set (0.01 sec)

mysql> SELECT E1.ENAME,E2.ENAME FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO;
+--------+-------+
| ENAME  | ENAME |
+--------+-------+
| 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.00 sec)

案例:找出哪个部门没有员工?
mysql> SELECT D1.DEPTNO,D1.DNAME,D1.LOC FROM EMP E1 RIGHT JOIN DEPT D1 ON E1.DEPTNO = D1.DEPTNO WHERE E1.DEPTNO IS NULL;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

三张表查询

多张表查询也是采用连接查询这种思路,语法结构如下:…

	A
join
	B
on
...
join
	C
on
...
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 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 E1.ENAME,D1.DEPTNO,S1.GRADE FROM EMP E1 JOIN DEPT D1 ON E1.DEPTNO=D1.DEPTNO JOIN SALGRADE S1 ON E1.SAL BETWEEN S1.LOSAL AND HISAL;
+--------+--------+-------+
| ENAME  | DEPTNO | GRADE |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+

子查询(WHERE嵌套)

什么是子查询?子查询都可以出现在哪里?
select语句当中嵌套select语句,被嵌套的select语句是子查询。

子查询可以出现在哪里?

select
	..(select).
from
	..(select).
where
	..(select).
案例:找出高于平均薪资的员工信息。
mysql> SELECT ENAME,JOB,SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP) ORDER BY SAL;
+-------+-----------+---------+
| ENAME | JOB       | SAL     |
+-------+-----------+---------+
| CLARK | MANAGER   | 2450.00 |
| BLAKE | MANAGER   | 2850.00 |
| JONES | MANAGER   | 2975.00 |
| SCOTT | ANALYST   | 3000.00 |
| FORD  | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
+-------+-----------+---------+

子查询(FROM嵌套)

案例:找出每个部门平均薪水的等级。

mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> SELECT DEPTNO,AVGS,GRADE FROM (SELECT DEPTNO,AVG(SAL) AS AVGS FROM EMP GROUP BY DEPTNO) AS E JOIN SALGRADE S ON  E.AVGS BETWEEN S.LOSAL AND S.HISAL;
+--------+-------------+-------+
| DEPTNO | AVGS        | GRADE |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

子查询(SELECT嵌套)

案例:找出每个员工所在的部门名称,要求显示员工名和部门名。

第一种实现方式
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  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

使用select嵌套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  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

拼接(union)

拼接的数据必须两两相对应。不能一张表是一个数据,另一张表是两个数据,这样无法拼接!

案例:找出工作岗位是SALESMAN和MANAGER的员工? 使用union来做。

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,mysql特有)

语法机制:
	limit startIndex, length
		startIndex表示起始位置,从0开始,0表示第一条数据。
		length表示取几个
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
案例:取出工资前5名的员工(思路:降序取前5个)
select ename,sal from emp order by sal desc;
取前5个:
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 0, 5;
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 |
+-------+---------+
案例:找出工资排名在第4到第9名的员工?
mysql> SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 3, 6;
+--------+---------+
| ENAME  | SAL     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

查询语句执行顺序

limitsql语句最后执行的一个环节:
	select		5
		...
	from			1
		...		
	where			2
		...	
	group by		3
		...
	having		4
		...
	order by		6
		...
	limit			7
		...;

数据定义语言(DDL)

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中,负责数据结构定义与数据库对象定义的语言,由CREATE、ALTER与DROP三个语法所组成,最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始,现在被纳入 SQL 指令中作为其中一个子集。

mysql的数据类型

关于MySQL当中字段的数据类型?以下只说常见的 int 整数型(java中的int) bigint 长整型(java中的long)
float 浮点型(java中的float double) char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder) date 日期类型
(对应Java中的java.sql.Date类型) BLOB 二进制大对象(存储图片、视频等流媒体信息) Binary Large
OBject (对应java中的Object) CLOB 字符大对象(存储较大文本,比如,可以存储4G的字符串。) Character
Large OBject(对应java中的Object)

char和varchar怎么选择?
在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。
当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar。
对 char 来说,最多能存放的字符个数 255,和编码无关。
而 varchar 呢,最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

构建学生表(create语句)

mysql中可能会出现``来包含字段名,这是mysql特有的,不符合标准sql。

建表语句的语法格式:
	create table 表名(
		字段名1 数据类型,
		字段名2 数据类型,
		字段名3 数据类型,
		....
	);
create table t_student(
	no bigint,
	name varchar(255),		// 数字代表最多能容纳多少字符,但是varchar是动态分配空间
	sex char(1),
	classno varchar(255),		
	birth char(10)		// 数字代表最多能容纳多少字符,但是char是固定占据空间
);

表的复制(create语句)

create table 表名 as select语句;

将查询结果当做表创建出来。

表结构修改

对于表结构的修改,使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之前的设计进行了否定,即使需要修改表结构,我们也可以直接使用工具操作。

修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括:insert delete update select(这些都是表中的数据操作。)

约束

什么是约束?常见的约束有哪些呢?
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢?

非空约束(not null):约束的字段不能为NULL
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)
外键约束(foreign key):…(简称FK)
检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。

非空约束( not null)

字段不能为空值,not null约束只有列级约束,没有表级约束。

drop table if exists t_user;		// 删除表
create table t_user(
	id int,
	username varchar(255) not null,		// 字段下不能为空
	password varchar(255)
);
mysql> insert into t_user(id,password) values(1,"123456");
ERROR 1364 (HY000): Field 'username' doesn't have a default value  //编译错误,约束username字段不能为空!
mysql> insert into t_user(id,username,password) values(1,'lisi','123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | lisi     | 123      |
+------+----------+----------+

唯一性约束(unique)

唯一性约束修饰的字段具有唯一性,不能重复。但可以为null。
 案例:给某一列添加unique
drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255) unique  //列级约束
);
insert into t_user values(1,'zhangsan');		
insert into t_user values(2,'zhangsan');  //出现编译错误,唯一性约束,该字段与上一行字段重复,但可以为null!
ERROR 1062 (23000) : Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);
mysql> select * from t_user;
+------+----------+
| id   | username |
+------+----------+
|    1 | zhangsan |
|    2 | NULL     |
|    3 | NULL     |
|    4 | NULL     |
+------+----------+

案例:给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username)  //多个字段联合起来添加一个约束unique 【表级约束】,多个字段值相同就就不满足条件
);

insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
mysql> select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | zs       |
|    2 | 111      | ls       |
|    3 | 222      | zs       |
+------+----------+----------+
insert into t_user values(4,'111','zs');  //出现编译错误
ERROR 1062 (23000) : Duplicate entry '111-zs' for key 'usercode'

drop table if exists t_user;
create table t_suer(		// 同上
	id int,
	usercode varchar(255) unique,
	username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
ERROR 1062 (23000) : Duplicate entry '111' for key 'usercode'

主键约束(primary key)

主键约束既不能为null也不能重复

怎么给一张表添加主键约束呢?
drop table if exists t_user;
create table t_user(
    id int primary key,  //列级约束
	username varchar(255),
	email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
+-----------------------------+
| id | username | email       |
+-----------------------------+
|  1 | zs       | zs@123.com  |
|  2 | ls       | ls@123.com  |
|  3 | ww       | ww@123.com  |
+----+----------+-------------+

mysql> insert into t_user(id,username,email) values(1,'jack','jack@123.com'); 	 //出现编译错误,主键约束,不能为null也不能重复!
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

主键有什么作用?
根据主键字段的字段数量来划分:
单一主键 (推荐的,常用的。)
复合主键(多个字段联合起来添加一个主键约束) (复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
自然主键 :主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的) 业务主键 : 主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键、拿着身份证号做为主键。(不推荐使用)
最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候,主键也可能需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键重复。
一张表的主键约束只能有1个。(必须记住)

 mysql提供主键值自增:(非常重要。)
      drop table if exists t_user;
      create table t_user(
          id int primary key auto_increment,  //id字段自动维护一个自增的数字,从1开始,以1递增。
	  username varchar(255)
        );
      insert into t_user(username) values('a'); 
      insert into t_user(username) values('b');
      insert into t_user(username) values('c');
      insert into t_user(username) values('d');
      insert into t_user(username) values('e');
      insert into t_user(username) values('f');
     mysql> select  * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | a        |
	|  2 | b        |
	|  3 | c        |
	|  4 | d        |
	|  5 | e        |
	|  6 | f        |
	+----+----------+

外键约束

外键就是将A表中的某个字段约束到B表中的某个字段,A表被称为子表,B表被称为父表。

例如:

t_class 班级表
cno(pk)         cname
-------------------------------------------------------------
 101           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 
 102           yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 

 t_student 学生表
 sno(pk)        sname          classno(该字段添加外键约束fk)
 -----------------------------------------------------------
  1              zs1              101
  2              zs2              101
  3              zs3              102
  4              zs4              102
  5              zs5              102
mysql>  create table t_class(
    ->        cno int,
    ->        cname varchar(255),
    ->        primary key(cno)
    ->      );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql>     create table t_student(
    ->         sno int,
    -> sname varchar(255),
    -> classno int,
    -> primary key(sno),
    -> foreign key(classno) references t_class(cno)
    ->       );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student values(1,'zs1',101);
Query OK, 1 row affected (0.00 sec)

mysql>     insert into t_student values(2,'zs2',101);
Query OK, 1 row affected (0.00 sec)

mysql>     insert into t_student values(3,'zs3',102);
Query OK, 1 row affected (0.00 sec)

mysql>     insert into t_student values(4,'zs4',102);
Query OK, 1 row affected (0.01 sec)

mysql>     insert into t_student values(5,'zs5',102);
Query OK, 1 row affected (0.00 sec)

mysql>     insert into t_student values(6,'zs6',102);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_class;
+-----+-----------------------------------+
| cno | cname                             |
+-----+-----------------------------------+
| 101 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy |
+-----+-----------------------------------+
2 rows in set (0.00 sec)

mysql>     select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   5 | zs5   |     102 |
|   6 | zs6   |     102 |
+-----+-------+---------+
6 rows in set (0.00 sec)

mysql> insert into t_student values(7,'lisi',103);		// 外键限制了classno只能是101或者102
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`study`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

键值可以为NULL?
外键可以为null。

外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
注意:被引用的字段不一定是主键,但至少是具有unique约束,具有唯一性,不可重复!

索引

mysql底层所以是用B+树实现的,从B+树数据结构思考就行。

MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:
在这里插入图片描述
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。但是总体结构都是B+树。
在这里插入图片描述

添加索引的条件

  • 数据量庞大。(根据客户的需求,根据线上的环境)
  • 该字段很少的DML操作。(因为字段进行修改操作,索引也需要维护)
  • 该字段经常出现在where子句中。(经常根据哪个字段维护)

添加索引

在表上创建一个简单的索引。允许使用重复的值:

CREATE INDEX index_name ON table_name (column_name)
注释:"column_name" 规定需要索引的列。
注释:"index_name" 规定需要索引的名称。

SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。字段不能出现相同的值。

CREATE UNIQUE INDEX index_name ON table_name (column_name)
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 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 |     10 |
|  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> explain select ename,sal from EMP where sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMP   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index emp_sal_index on EMP(sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> mysql> explain select ename,sal from EMP where sal = 5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMP   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

视图(view)

什么是视图?
站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待),本事是一条查询语句的结果产生的虚拟表。
通过视图影响原表数据,不是直接操作的原表, 可以对试图进行CRUD操作。

创建视图

怎么创建视图?怎么删除视图?
create view myview as select empno,ename from emp;
drop view myview;

视图的作用

试图的作用?
试图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。

导入导出数据

shell命令,不是在mysql下执行

mysqldump -u userName -p  dabaseName  > fileName.sql

mysqldump -u userName -p  dabaseName tableName > fileName.sql 

注意fileName.sql要有路径名

数据操纵语言(DML)

数据操纵语言(Data Manipulation Language, DML)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除。’

insert语句插入数据

insert into 表名(字段名1,字段名2,字段名3,....) values(1,2,3,....)		// 字段名可以和表名不是一个顺序,但是必须相对应
insert into 表名 values(1,2,3,....)		// 字段可以省略不写,但是后面的value对数量和顺序都有要求。如果不符合表中的数量和要求就会报错。
insert into 表名(字段名1,字段名2,字段名3,....) values(1,2,3,....),(1,2,3,....)....;	//一次插入多行数据
insert into 表名(字段名1) values();		// 如果没有写其它字段值,则为NULL

insert语句将查询结果插入到一张表中?

mysql> CREATE TABLE `DEPT1`( 
 `DEPTNO` int(2) NOT NULL, 
  `DNAME` varchar(14) DEFAULT NULL, 
   `LOC` varchar(13) DEFAULT NULL,   
   PRIMARY KEY (`DEPTNO`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   
mysql>  insert into DEPT1 select * from DEPT;		// 查询结果插入到一张表
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  insert into DEPT1 select * from DEPT;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

修改数据:update

注意:没有条件整张表数据全部更新。

语法格式:
	update 表名 set 字段名1=1,字段名2=2... where 条件;

案例:将部门10的LOC修改为SHANGHAI,将部门名称修改为RENSHIBU

mysql> UPDATE DEPT1 SET LOC='SHANGHAI',DNAME='RENSHIBU' WHERE DEPTNO = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM DEPT1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

// 更新所有记录
mysql> UPDATE DEPT1 SET LOC='X',DNAME='Y'
    -> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> SELECT * FROM DEPT1;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | Y     | X    |
|     20 | Y     | X    |
|     30 | Y     | X    |
|     40 | Y     | X    |
+--------+-------+------+
4 rows in set (0.00 sec)

删除数据

语法格式:
	delete from 表名 where 条件;
注意:没有条件全部删除。

删除10部门数据?
mysql> DELETE FROM DEPT1 WHERE DEPTNO=10;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM DEPT1;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     20 | Y     | X    |
|     30 | Y     | X    |
|     40 | Y     | X    |
+--------+-------+------+
3 rows in set (0.00 sec)
	
删除所有记录?
mysql> delete from DEPT1;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM DEPT1;
Empty set (0.00 sec)
	
怎么删除大表?(重点)
truncate table 表名; // 表被截断,不可回滚。永久丢失。

数据控制语言(DCL)

数据控制语言 (Data Control Language) 在SQL语言中,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。

指针控制语言(CCL)

它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

事务处理语言(TPL或者TCL)

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK。

事务

事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性,一个事务中的一系列的操作要么全部成功,要么一个都不做。
事务在sql中就是一系列sql操作。

事务的存在是为了保证数据的完整性,安全性。

而且在sql中事务主要针对的是DML语句。

事务的特性

A:原子性:事务是最小的工作单元,不可再分。
B:一致性:事务必须保证多条DML语句同时成功或者同时失败。sql逻辑也必须正确。
C:隔离性:事务A与事务B之间具有隔离。
D:持久性:持久性说的是最终数据必须持久化到硬盘中,事务才算成功结束。

事务的隔离级别

两个事务之间的隔离级别如下:

第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read) 现象:表示读到了脏数据。
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
读已提交存在的问题是:不可重复读。(不可以重复读取事务开始之前的数据,只要有事务开始提交,就可以读到改变)
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读
解决了所有问题。
效率低,需要事务排队。
演示第1级别:读未提交

    set global transaction isolation level read uncommitted;
演示第二级别;读已提交
    set global transaction isolation level read committed;
演示第三级别:可重复读
    set global transaction isolation level repeatable read;

mysql中的事务

mysql中的事务是支持自动提交的,只要执行一条DML语句,则提交一次。

怎么关闭默认提交?
start transaction;
怎么回滚事务?
rollback;
怎么手动提交事务?
commit;


mysql>           drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql>    create table t_user(
    ->        id int primary key auto_increment,
    ->        username varchar(255)
    ->      );
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values("lisi");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | lisi     |
+----+----------+
1 row in set (0.00 sec)

mysql> insert into t_user(username) values("wangwu");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | lisi     |
|  2 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
commit表示已经的提交事务,不能回滚
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values("wangwu
Query OK, 1 row affected (0.00 sec)

mysql>  insert into t_user(username) values("objec
Query OK, 1 row affected (0.00 sec)

mysql>  insert into t_user(username) values("joke"
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username) values("xiaozh
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  3 | wangwu       |
|  4 | object       |
|  5 | joke         |
|  6 | xiaozhaozhao |
+----+--------------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  3 | wangwu       |
|  4 | object       |
|  5 | joke         |
|  6 | xiaozhaozhao |
+----+--------------+
4 rows in set (0.01 sec)

mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  3 | wangwu       |
|  4 | object       |
|  5 | joke         |
|  6 | xiaozhaozhao |
+----+--------------+
4 rows in set (0.00 sec)

原文链接:https://blog.csdn.net/liutao43/article/details/117263806



所属网站分类: 技术文章 > 博客

作者:狗蛋来了

链接:http://www.javaheidong.com/blog/article/222153/07f7c4b43bebcb02f46f/

来源:java黑洞网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

4 0
收藏该文
已收藏

评论内容:(最多支持255个字符)