【老杜】MySQL

文章目錄

- day02課堂筆記
- - 1、把查詢結果去除重復記錄【distinct】
- 10、連接查詢
- - 10.1、什么是連接查詢？
  - 10.2、連接查詢的分類？
  - 10.3、當兩張表進行連接查詢時，沒有任何條件的限制會發生什么現象？
  - 10.4、怎么避免笛卡爾積現象？
  - 10.5、內連接之等值連接。
  - 10.6、內連接之非等值連接
  - 10.7、內連接之自連接
  - 10.8、外連接
  - 10.9、三張表，四張表怎么連接？
- 11、子查詢？
- - - 11.1、什么是子查詢？
    - 11.2、子查詢都可以出現在哪里呢？
    - 11.3、where子句中的子查詢
    - 11.4、from子句中的子查詢
    - 11.5、select后面出現的子查詢（這個內容不需要掌握，了解即可！！！）
- 12、union合并查詢結果集
- 13、limit（非常重要）
- - 13.1、limit作用：將查詢結果集的一部分取出來。通常使用在分頁查詢當中。
  - 13.2、limit怎么用呢？
  - 13.3、注意：mysql當中limit在order by之后執行！！！！！！
  - 13.4、取出工資排名在[3-5]名的員工？
  - 13.5、取出工資排名在[5-9]名的員工？
  - 13.6、分頁
- 14、關于DQL語句的大總結：
- 15、表
- - - 15.1、建表的語法格式：(建表屬于DDL語句，DDL包括：create drop alter)
    - 15.2、關于mysql中的數據類型？
    - 15.3、創建一個學生表？
    - 15.4、插入數據insert （DML）
    - 15.5、insert插入日期
    - 15.6、date和datetime兩個類型的區別？
    - 15.7、修改update（DML）
    - 15.8、刪除數據 delete （DML）

本文章為學習動力節點的杜老師，視頻鏈接如下
https://www.bilibili.com/video/BV1Vy4y1z7EX
源碼+文檔+學習資料+安裝工具[點贊]都已經為大家準備好！！！！
鏈接：https://pan.baidu.com/s/1PTbdG-olm8mpEzz-zXe6bw
提取碼：m0bc

day02課堂筆記

1、把查詢結果去除重復記錄【distinct】

注意：原表數據不會被修改，只是查詢結果去重。

去重需要使用一個關鍵字：distinct

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

mysql> select ename,distinct job from emp;

// 這樣編寫是錯誤的，語法錯誤。

// distinct只能出現在所有字段的最前方。

// distinct出現在job,deptno兩個字段之前，表示兩個字段聯合起來去重。

mysql> select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

統計一下工作崗位的數量？

	select count(distinct job) from emp;+---------------------+| count(distinct job) |+---------------------+|                   5 |+---------------------+

10、連接查詢

10.1、什么是連接查詢？

從一張表中單獨查詢，稱為單表查詢。
emp表和dept表聯合起來查詢數據，從emp表中取員工名字，從dept表中取部門名字。
這種跨表查詢，多張表聯合起來查詢數據，被稱為連接查詢。

10.2、連接查詢的分類？

根據語法的年代分類：
SQL92：1992年的時候出現的語法
SQL99：1999年的時候出現的語法
我們這里重點學習SQL99.(這個過程中簡單演示一個SQL92的例子)

根據表連接的方式分類：
內連接：
等值連接
非等值連接
自連接

外連接：
左外連接（左連接）
右外連接（右連接）

全連接（不講）

10.3、當兩張表進行連接查詢時，沒有任何條件的限制會發生什么現象？

案例：查詢每個員工所在部門名稱？

	mysql> select ename,deptno from emp;+--------+--------+| ename  | deptno |+--------+--------+| SMITH  |     20 || ALLEN  |     30 || WARD   |     30 || JONES  |     20 || MARTIN |     30 || BLAKE  |     30 || CLARK  |     10 || SCOTT  |     20 || KING   |     10 || TURNER |     30 || ADAMS  |     20 || JAMES  |     30 || FORD   |     20 || MILLER |     10 |+--------+--------+

mysql> select * from dept;+--------+------------+----------+| DEPTNO | DNAME      | LOC      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+

兩張表連接沒有任何條件限制：

select ename,dname from emp, dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
...
56 rows in set (0.00 sec)

14 * 4 = 56

當兩張表進行連接查詢，沒有任何條件限制的時候，最終查詢結果條數，是
兩張表條數的乘積，這種現象被稱為：笛卡爾積現象。（笛卡爾發現的，這是
一個數學現象。）

10.4、怎么避免笛卡爾積現象？

連接時加條件，滿足這個條件的記錄被篩選出來！

select ename,dname 
from emp, dept
whereemp.deptno = dept.deptno;select emp.ename,dept.dname 
from emp, dept
whereemp.deptno = dept.deptno;

// 表起別名。很重要。效率問題。

select e.ename,d.dname 
from emp e, dept d
wheree.deptno = d.deptno; //SQL92語法。+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

思考：最終查詢的結果條數是14條，但是匹配的過程中，匹配的次數減少了嗎？
還是56次，只不過進行了四選一。次數沒有減少。

注意：通過笛卡爾積現象得出，表的連接次數越多效率越低，盡量避免表的
連接次數。

10.5、內連接之等值連接。

案例：查詢每個員工所在部門名稱，顯示員工名和部門名？
emp e和dept d表進行連接。條件是：e.deptno = d.deptno

SQL92語法：

select e.ename,d.dname
fromemp e, dept d
wheree.deptno = d.deptno;

sql92的缺點：結構不清晰，表的連接條件，和后期進一步篩選的條件，都放到了where后面。

SQL99語法：

select e.ename,d.dname
fromemp e
joindept d
one.deptno = d.deptno;//inner可以省略（帶著inner可讀性更好！！！一眼就能看出來是內連接）

select e.ename,d.dname
fromemp e
inner joindept d
one.deptno = d.deptno; // 條件是等量關系，所以被稱為等值連接。

sql99優點：表連接的條件是獨立的，連接之后，如果還需要進一步篩選，再往后繼續添加where

SQL99語法：
select
…
from
a
join
b
on
a和b的連接條件
where
篩選條件

10.6、內連接之非等值連接

案例：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

mysql> select * from emp; e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |

…

mysql> select * from salgrade; s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

select 
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 條件不是一個等量關系，稱為非等值連接。

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

10.7、內連接之自連接

案例：查詢員工的上級領導，要求顯示員工名和對應的領導名？

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

技巧：一張表看成兩張表。

emp a 員工表
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

emp b 領導表
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

select 
a.ename as '員工名', b.ename as '領導名'
from
emp a
join
emp b
on
a.mgr = b.empno; //員工的領導編號 = 領導的員工編號

+--------+--------+
| 員工名 | 領導名|
+--------+--------+
| 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條記錄，沒有KING。《內連接》

以上就是內連接中的：自連接，技巧：一張表看做兩張表。

10.8、外連接

mysql> select * from emp; e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 * from dept; d
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

內連接：（A和B連接，AB兩張表沒有主次關系。平等的。）

select 
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno; //內連接的特點：完成能夠匹配上這個條件的數據查詢出來。+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

外連接（右外連接）：

select 
e.ename,d.dname
from
emp e 
right join 
dept d
on
e.deptno = d.deptno;

// outer是可以省略的，帶著可讀性強。

select 
e.ename,d.dname
from
emp e 
right outer join 
dept d
on
e.deptno = d.deptno;

right代表什么：表示將join關鍵字右邊的這張表看成主表，主要是為了將
這張表的數據全部查詢出來，捎帶著關聯查詢左邊的表。
在外連接當中，兩張表連接，產生了主次關系。

外連接（左外連接）：

select 
e.ename,d.dname
from
dept d 
left join 
emp e
on
e.deptno = d.deptno;

// outer是可以省略的，帶著可讀性強。

select 
e.ename,d.dname
from
dept d 
left outer join 
emp e
on
e.deptno = d.deptno;

帶有right的是右外連接，又叫做右連接。
帶有left的是左外連接，又叫做左連接。
任何一個右連接都有左連接的寫法。
任何一個左連接都有右連接的寫法。

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

思考：外連接的查詢結果條數一定是 >= 內連接的查詢結果條數？
正確。

案例：查詢每個員工的上級領導，要求顯示所有員工的名字和領導名？

select a.ename as '員工名', b.ename as '領導名'
fromemp a
left joinemp b
ona.mgr = b.empno; +--------+--------+
| 員工名      | 領導名     |
+--------+--------+
| 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  |
+--------+--------+

10.9、三張表，四張表怎么連接？

語法：
select
…
from
a
join
b
on
a和b的連接條件
join
c
on
a和c的連接條件
right join
d
on
a和d的連接條件

一條SQL中內連接和外連接可以混合。都可以出現！

案例：找出每個員工的部門名稱以及工資等級，
要求顯示員工名、部門名、薪資、薪資等級？

select e.ename,e.sal,d.dname,s.grade
fromemp e
joindept d
on e.deptno = d.deptno
joinsalgrade s
one.sal between s.losal and s.hisal;+--------+---------+------------+-------+
| ename  | sal     | dname      | 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 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+

案例：找出每個員工的部門名稱以及工資等級，還有上級領導，
要求顯示員工名、領導名、部門名、薪資、薪資等級？

select e.ename,e.sal,d.dname,s.grade,l.ename
fromemp e
joindept d
on e.deptno = d.deptno
joinsalgrade s
one.sal between s.losal and s.hisal
left joinemp l
one.mgr = l.empno;+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+

11、子查詢？

11.1、什么是子查詢？

select語句中嵌套select語句，被嵌套的select語句稱為子查詢。

11.2、子查詢都可以出現在哪里呢？

select
…(select).
from
…(select).
where
…(select).

11.3、where子句中的子查詢

案例：找出比最低工資高的員工姓名和工資？

	select ename,salfromemp wheresal > min(sal);ERROR 1111 (HY000): Invalid use of group function

where子句中不能直接使用分組函數。

實現思路：
第一步：查詢最低工資是多少

select min(sal) from emp;+----------+| min(sal) |+----------+|   800.00 |+----------+

? 第二步：找出>800的m

select ename,sal from emp where sal > 800;

? 第三步：合并
?

select ename,sal from emp where sal > (select min(sal) from emp);
?		+--------+---------+
?		| ename  | sal     |
?		+--------+---------+
?		| ALLEN  | 1600.00 |
?		| WARD   | 1250.00 |
?		| JONES  | 2975.00 |
?		| MARTIN | 1250.00 |
?		| BLAKE  | 2850.00 |
?		| CLARK  | 2450.00 |
?		| SCOTT  | 3000.00 |
?		| KING   | 5000.00 |
?		| TURNER | 1500.00 |
?		| ADAMS  | 1100.00 |
?		| JAMES  |  950.00 |
?		| FORD   | 3000.00 |
?		| MILLER | 1300.00 |
?		+--------+---------+

11.4、from子句中的子查詢

注意：from后面的子查詢，可以將子查詢的查詢結果當做一張臨時表。（技巧）

案例：找出每個崗位的平均工資的薪資等級。

第一步：找出每個崗位的平均工資（按照崗位分組求平均值）

	select job,avg(sal) from emp group by job;+-----------+-------------+| job       | avgsal      |+-----------+-------------+| ANALYST   | 3000.000000 || CLERK     | 1037.500000 || MANAGER   | 2758.333333 || PRESIDENT | 5000.000000 || SALESMAN  | 1400.000000 |+-----------+-------------+t表

第二步：克服心理障礙，把以上的查詢結果就當做一張真實存在的表t。

mysql> select * from salgrade; s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

t表和s表進行表連接，條件：t表avg(sal) between s.losal and s.hisal;

select t.*, s.grade
from(select job,avg(sal) as avgsal from emp group by job) t
joinsalgrade s
ont.avgsal between s.losal and s.hisal;+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| ANALYST   | 3000.000000 |     4 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+

11.5、select后面出現的子查詢（這個內容不需要掌握，了解即可！！！）

案例：找出每個員工的部門名稱，要求顯示員工名，部門名？

select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

//錯誤：ERROR 1242 (21000): Subquery returns more than 1 row

select e.ename,e.deptno,(select dname from dept) as dname
fromemp e;

注意：對于select后面的子查詢來說，這個子查詢只能一次返回1條結果，
多于1條，就報錯了。！

12、union合并查詢結果集

案例：查詢工作崗位是MANAGER和SALESMAN的員工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

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

union的效率要高一些。對于表連接來說，每連接一次新表，
則匹配的次數滿足笛卡爾積，成倍的翻。。。
但是union可以減少匹配的次數。在減少匹配次數的情況下，
還可以完成兩個結果集的拼接。

a 連接 b 連接 c
a 10條記錄
b 10條記錄
c 10條記錄
匹配次數是：1000

a 連接 b一個結果：10 * 10 --> 100次
a 連接 c一個結果：10 * 10 --> 100次
使用union的話是：100次 + 100次 = 200次。（union把乘法變成了加法運算）

union在使用的時候有注意事項嗎？

//錯誤的：union在進行結果集合并的時候，要求兩個結果集的列數相同。

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';

// MYSQL可以，oracle語法嚴格，不可以，報錯。要求：結果集合并時列和列的數據類型也要一致。

select ename,job from emp where job = 'MANAGER'
union
select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+

13、limit（非常重要）

13.1、limit作用：將查詢結果集的一部分取出來。通常使用在分頁查詢當中。

百度默認：一頁顯示10條記錄。
分頁的作用是為了提高用戶的體驗，因為一次全部都查出來，用戶體驗差。
可以一頁一頁翻頁看。

13.2、limit怎么用呢？

完整用法：limit startIndex, length
startIndex是起始下標，length是長度。
起始下標從0開始。

缺省用法：limit 5; 這是取前5.

按照薪資降序，取出排名在前5名的員工？

select ename,sal
fromemp
order by sal desc
limit 5; //取前5select ename,sal
fromemp
order by sal desc
limit 0,5;+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

13.3、注意：mysql當中limit在order by之后執行！！！！！！

13.4、取出工資排名在[3-5]名的員工？

select ename,sal
fromemp
order bysal desc
limit2, 3;2表示起始位置從下標2開始，就是第三條記錄。
3表示長度。+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

13.5、取出工資排名在[5-9]名的員工？

select ename,sal
fromemp
order by sal desc
limit4, 5;+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

13.6、分頁

每頁顯示3條記錄
第1頁：limit 0,3 [0 1 2]
第2頁：limit 3,3 [3 4 5]
第3頁：limit 6,3 [6 7 8]
第4頁：limit 9,3 [9 10 11]

每頁顯示pageSize條記錄
第pageNo頁：limit (pageNo - 1) * pageSize , pageSize

public static void main(String[] args){// 用戶提交過來一個頁碼，以及每頁顯示的記錄條數int pageNo = 5; //第5頁int pageSize = 10; //每頁顯示10條int startIndex = (pageNo - 1) * pageSize;String sql = "select ...limit " + startIndex + ", " + pageSize;}

記公式：
limit (pageNo-1)*pageSize , pageSize

14、關于DQL語句的大總結：

select
…
from
…
where
…
group by
…
having
…
order by
…
limit
…

執行順序？
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit…

15、表

15.1、建表的語法格式：(建表屬于DDL語句，DDL包括：create drop alter)

create table 表名(字段名1 數據類型, 字段名2 數據類型, 字段名3 數據類型);

create table 表名(字段名1 數據類型, 字段名2 數據類型, 字段名3 數據類型
);

表名：建議以t_ 或者 tbl_開始，可讀性強。見名知意。
字段名：見名知意。
表名和字段名都屬于標識符。

15.2、關于mysql中的數據類型？

很多數據類型，我們只需要掌握一些常見的數據類型即可。

varchar(最長255)
可變長度的字符串
比較智能，節省空間。
會根據實際的數據長度動態分配空間。

? 優點：節省空間
? 缺點：需要動態分配空間，速度慢。

char(最長255)
定長字符串
不管實際的數據長度是多少。
分配固定長度的空間去存儲數據。
使用不恰當的時候，可能會導致空間的浪費。

? 優點：不需要動態分配空間，速度快。
? 缺點：使用不當可能會導致空間的浪費。

? varchar和char我們應該怎么選擇？
? 性別字段你選什么？因為性別是固定長度的字符串，所以選擇char。
? 姓名字段你選什么？每一個人的名字長度不同，所以選擇varchar。

int(最長11)
數字中的整數型。等同于java的int。

bigint
數字中的長整型。等同于java中的long。

float
單精度浮點型數據

double
雙精度浮點型數據

date
短日期類型

datetime
長日期類型

clob
字符大對象
最多可以存儲4G的字符串。
比如：存儲一篇文章，存儲一個說明。
超過255個字符的都要采用CLOB字符大對象來存儲。
Character Large OBject:CLOB

blob
二進制大對象
Binary Large OBject
專門用來存儲圖片、聲音、視頻等流媒體數據。
往BLOB類型的字段上插入數據的時候，例如插入一個圖片、視頻等，
你需要使用IO流才行。

t_movie 電影表（專門存儲電影信息的）

編號名字故事情節上映日期時長海報類型

no(bigint) name(varchar) history(clob) playtime(date) time(double) image(blob) type(char)

10000 哪吒 … 2019-10-11 2.5 … ‘1’
10001 林正英之娘娘 … 2019-11-11 1.5 … ‘2’
…

15.3、創建一個學生表？

學號、姓名、年齡、性別、郵箱地址

create table t_student(no int,name varchar(32),sex char(1),age int(3),email varchar(255)
);

刪除表：
drop table t_student; // 當這張表不存在的時候會報錯！

// 如果這張表存在的話，刪除
drop table if exists t_student;

15.4、插入數據insert （DML）

語法格式：
insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一對應。什么是一一對應？
數量要對應。數據類型要對應。

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);insert into t_student(no) values(3);+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
insert into t_student(name) values('wangwu');
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
| NULL | wangwu   | NULL | NULL | NULL             |
+------+----------+------+------+------------------+

注意：insert語句但凡是執行成功了，那么必然會多一條記錄。
沒有給其它字段指定值的話，默認值是NULL。

drop table if exists t_student;
create table t_student(no int,name varchar(32),sex char(1) default 'm',age int(3),email varchar(255)
);+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

insert into t_student(no) values(1);
mysql> select * from t_student;
+------+------+------+------+-------+
| no   | name | sex  | age  | email |
+------+------+------+------+-------+
|    1 | NULL | m    | NULL | NULL  |
+------+------+------+------+-------+

insert語句中的“字段名”可以省略嗎？可以

insert into t_student values(2); //錯誤的

// 注意：前面的字段名省略的話，等于都寫上了！所以值也要都寫上！
insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
+------+------+------+------+--------------+
| no   | name | sex  | age  | email        |
+------+------+------+------+--------------+
|    1 | NULL | m    | NULL | NULL         |
|    2 | lisi | f    |   20 | lisi@123.com |
+------+------+------+------+--------------+

15.5、insert插入日期

數字格式化：format

select ename,sal from emp;+--------+---------+| ename  | sal     |+--------+---------+| SMITH  |  800.00 || ALLEN  | 1600.00 || WARD   | 1250.00 || JONES  | 2975.00 || MARTIN | 1250.00 || BLAKE  | 2850.00 || CLARK  | 2450.00 || SCOTT  | 3000.00 || KING   | 5000.00 || TURNER | 1500.00 || ADAMS  | 1100.00 || JAMES  |  950.00 || FORD   | 3000.00 || MILLER | 1300.00 |+--------+---------+

格式化數字：format(數字, ‘格式’)

select ename,format(sal, '$999,999') as sal from emp;+--------+-------+| ename  | sal   |+--------+-------+| SMITH  | 800   || ALLEN  | 1,600 || WARD   | 1,250 || JONES  | 2,975 || MARTIN | 1,250 || BLAKE  | 2,850 || CLARK  | 2,450 || SCOTT  | 3,000 || KING   | 5,000 || TURNER | 1,500 || ADAMS  | 1,100 || JAMES  | 950   || FORD   | 3,000 || MILLER | 1,300 |+--------+-------+

str_to_date：將字符串varchar類型轉換成date類型
date_format：將date類型轉換成具有一定格式的varchar字符串類型。

drop table if exists t_user;
create table t_user(id int,name varchar(32),birth date // 生日也可以使用date日期類型
);

create table t_user(id int,name varchar(32),birth char(10) // 生日可以使用字符串，沒問題。
);

生日：1990-10-11 （10個字符）

注意：數據庫中的有一條命名規范：
所有的標識符都是全部小寫，單詞和單詞之間使用下劃線進行銜接。

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

插入數據？

insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990') // 1990年10月1日

? 出問題了：原因是類型不匹配。數據庫birth是date類型，這里給了一個字符串varchar。

怎么辦？可以使用str_to_date函數進行類型轉換。
str_to_date函數可以將字符串轉換成日期類型date？
語法格式：
str_to_date(‘字符串日期’, ‘日期格式’)

mysql的日期格式：
%Y 年
%m 月
%d 日
%h 時
%i 分
%s 秒

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

str_to_date函數可以把字符串varchar轉換成日期date類型數據，
通常使用在插入insert方面，因為插入的時候需要一個日期類型的數據，
需要通過該函數將字符串轉換成date。

好消息？
如果你提供的日期字符串是這個格式，str_to_date函數就不需要了！！！
%Y-%m-%d

	insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

查詢的時候可以以某個特定的日期格式展示嗎？
date_format
這個函數可以將日期類型轉換成特定格式的字符串。

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
|    2 | lisi     | 10/01/1990 |
+------+----------+------------+

date_format函數怎么用？
date_format(日期類型數據, ‘日期格式’)
這個函數通常使用在查詢日期方面。設置展示的日期格式。

mysql> select id,name,birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990-10-01 |
|    2 | lisi     | 1990-10-01 |
+------+----------+------------+

以上的SQL語句實際上是進行了默認的日期格式化，
自動將數據庫中的date類型轉換成varchar類型。
并且采用的格式是mysql默認的日期格式：’%Y-%m-%d’

select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;

java中的日期格式？
yyyy-MM-dd HH:mm:ss SSS

15.6、date和datetime兩個類型的區別？

date是短日期：只包括年月日信息。
datetime是長日期：包括年月日時分秒信息。

drop table if exists t_user;
create table t_user(id int,name varchar(32),birth date,create_time datetime
);

id是整數
name是字符串
birth是短日期
create_time是這條記錄的創建時間：長日期類型

mysql短日期默認格式：%Y-%m-%d
mysql長日期默認格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

在mysql當中怎么獲取系統當前時間？
now() 函數，并且獲取的時間帶有：時分秒信息！！！！是datetime類型的。

insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

15.7、修改update（DML）

語法格式：
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 條件;

注意：沒有條件限制會導致所有數據全部更新。

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
+------+----------+------------+---------------------+

update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

更新所有？

	update t_user set name = 'abc';

15.8、刪除數據 delete （DML）

語法格式？
delete from 表名 where 條件;

注意：沒有條件，整張表的數據會全部刪除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 刪除所有！

1、查詢每一個員工的所在部門名稱？要求顯示員工名和部門名。

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 * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

從emp表中取ename，從dept表中取dname，沒有條件限制最終查詢結果是？

ENAME DNAME

SMITH ACCOUNTING 無效記錄
SMITH RESEARCH 有效記錄
SMITH SALES 無效記錄
SMITH OPERATIONS 無效記錄

ALLEN ACCOUNTING
ALLEN RESEARCH
ALLEN SALES
ALLEN OPERATIONS

…
（14*4）條記錄。

【加個條件是為了達到4選1，也是為了篩查數據的有效記錄】

select e.ename,d.dname
fromemp e
joindept d
one.deptno = d.deptno;注意：加條件只是為了避免笛卡爾積現象，只是為了查詢出有效的組合記錄。匹配的次數一次		都沒有少，還是56次，和效率無關。

2、insert語句可以一次插入多條記錄嗎？【掌握】
可以的！

mysql> desc t_user;+-------------+-------------+------+-----+---------+-------+| Field       | Type        | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+-------+| id          | int(11)     | YES  |     | NULL    |       || name        | varchar(32) | YES  |     | NULL    |       || birth       | date        | YES  |     | NULL    |       || create_time | datetime    | YES  |     | NULL    |       |+-------------+-------------+------+-----+---------+-------+

一次可以插入多條記錄：

	insert into t_user(id,name,birth,create_time) values(1,'zs','1980-10-11',now()), (2,'lisi','1981-10-11',now()),(3,'wangwu','1982-10-11',now());

? 語法：insert into 表名(字段名1,字段名2) values(),(),(),()…;

mysql> select * from t_user;
+------+--------+------------+---------------------+
| id   | name   | birth      | create_time         |
+------+--------+------------+---------------------+
|    1 | zs     | 1980-10-11 | 2020-03-19 09:37:01 |
|    2 | lisi   | 1981-10-11 | 2020-03-19 09:37:01 |
|    3 | wangwu | 1982-10-11 | 2020-03-19 09:37:01 |
+------+--------+------------+---------------------+

3、快速創建表？【了解內容】

mysql> create table emp2 as select * from emp;

原理：
將一個查詢結果當做一張表新建！！！！！
這個可以完成表的快速復制！！！！
表創建出來，同時表中的數據也存在了！！！

create table mytable as select empno,ename from emp where job = 'MANAGER';

4、將查詢結果插入到一張表當中？insert相關的！！！【了解內容】

create table dept_bak as select * from dept;mysql> select * from dept_bak;+--------+------------+----------+| DEPTNO | DNAME      | LOC      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+

//很少用！查的結果剛好符合表的結構才能查

insert into dept_bak select * from dept; mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

5、快速刪除表中的數據？【truncate比較重要，必須掌握】

//刪除dept_bak表中的數據
//這種刪除數據的方式比較慢。

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

delete語句刪除數據的原理？（delete屬于DML語句！！！）
表中的數據被刪除了，但是這個數據在硬盤上的真實存儲空間不會被釋放！！！
這種刪除缺點是：刪除效率比較低。
這種刪除優點是：支持回滾，后悔了可以再恢復數據！！！

truncate語句刪除數據的原理？
這種刪除效率比較高，表被一次截斷，物理刪除。
這種刪除缺點：不支持回滾。
這種刪除優點：快速。

用法：truncate table 表名; （這種操作屬于DDL操作。）

大表非常大，上億條記錄？？？？
刪除的時候，使用delete，也許需要執行1個小時才能刪除完！效率較低。
可以選擇使用truncate刪除表中的數據。只需要不到1秒鐘的時間就刪除結束。效率較高。
但是使用truncate之前，必須仔細詢問客戶是否真的要刪除，并警告刪除之后不可恢復！

? truncate是刪除表中的數據，表還在！
? 刪除表操作？drop table 表名; // 這不是刪除表中的數據，而是把表刪除。

6、對表結構的增刪改？【非重點】

什么是對表結構的修改？
添加一個字段，刪除一個字段，修改一個字段！！！

對表結構的修改需要使用：alter（屬于DDL語句）

DDL包括：create drop alter

第一：在實際的開發中，需求一旦確定之后，表一旦設計好之后，很少的
進行表結構的修改。因為開發進行中的時候，修改表結構，成本比較高。
修改表的結構，對應的java代碼就需要進行大量的修改。成本是比較高的。
這個責任應該由設計人員來承擔！

第二：由于修改表結構的操作很少，所以我們不需要掌握，如果有一天
真的要修改表結構，你可以使用工具！！！！

修改表結構的操作是不需要寫到java程序中的。實際上也不是java程序員的范疇。

7、約束（非常重要，*****）

7.1、什么是約束？
約束對應的英語單詞：constraint
在創建表的時候，我們可以給表中的字段加上一些約束，來保證這個表中數據的
完整性、有效性！！！

約束的作用就是為了保證表中的數據有效！！

7.2、約束包括哪些？
非空約束：not null
唯一性約束: unique
主鍵約束: primary key（簡稱PK）
外鍵約束：foreign key（簡稱FK）
檢查約束：check（mysql不支持，oracle支持）

我們這里重點學習四個約束：
not null
unique
primary key
foreign key

7.3、非空約束：not null

非空約束not null約束的字段不能為NULL。

drop table if exists t_vip;
create table t_vip(id int,name varchar(255) not null  // not null只有列級約束，沒有表級約束！
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

小插曲：
xxxx.sql這種文件被稱為sql腳本文件。
sql腳本文件中編寫了大量的sql語句。
我們執行sql腳本文件的時候，該文件中所有的sql語句會全部執行！
批量的執行SQL語句，可以使用sql腳本文件。
在mysql當中怎么執行sql腳本呢？
mysql> source D:\course\03-MySQL\document\vip.sql

你在實際的工作中，第一天到了公司，項目經理會給你一個xxx.sql文件，
你執行這個腳本文件，你電腦上的數據庫數據就有了！

7.4、唯一性約束: unique

唯一性約束unique約束的字段不能重復，但是可以為NULL。

drop table if exists t_vip;
create table t_vip(id int,name varchar(255) unique,email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
select * from t_vip;insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+

name字段雖然被unique約束了，但是可以為NULL。

新需求：name和email兩個字段聯合起來具有唯一性！！！！

	drop table if exists t_vip;create table t_vip(id int,name varchar(255) unique,  // 約束直接添加到列后面的，叫做列級約束。email varchar(255) unique);

? 這張表這樣創建是不符合我以上“新需求”的。
? 這樣創建表示：name具有唯一性，email具有唯一性。各自唯一。

? 以下這樣的數據是符合我“新需求”的。
? 但如果采用以上方式創建表的話，肯定創建失敗，因為’zhangsan’和’zhangsan’重復了。
?

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

? 怎么創建這樣的表，才能符合新需求呢？
?

drop table if exists t_vip;create table t_vip(id int,name varchar(255),email varchar(255),unique(name,email) // 約束沒有添加在列的后面，這種約束被稱為表級約束。);insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');select * from t_vip;

? name和email兩個字段聯合起來唯一！！！
?

insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

? 什么時候使用表級約束呢？
? 需要給多個字段聯合起來添加某一個約束的時候，需要使用表級約束。

? unique 和not null可以聯合嗎？
?

drop table if exists t_vip;create table t_vip(id int,name varchar(255) not null unique);?	mysql> desc t_vip;
?	+-------+--------------+------+-----+---------+-------+
?	| Field | Type         | Null | Key | Default | Extra |
?	+-------+--------------+------+-----+---------+-------+
?	| id    | int(11)      | YES  |     | NULL    |       |
?	| name  | varchar(255) | NO   | PRI | NULL    |       |
?	+-------+--------------+------+-----+---------+-------+

? 在mysql當中，如果一個字段同時被not null和unique約束的話，
? 該字段自動變成主鍵字段。（注意：oracle中不一樣！）

insert into t_vip(id,name) values(1,'zhangsan');insert into t_vip(id,name) values(2,'zhangsan'); //錯誤了：name不能重復insert into t_vip(id) values(2); //錯誤了：name不能為NULL。

7.5、主鍵約束（primary key，簡稱PK）*****

單一主鍵
復合主鍵
表級約束
列級約束

主鍵約束的相關術語？
主鍵約束：就是一種約束。（PK）
主鍵字段：該字段上添加了主鍵約束，這樣的字段叫做：主鍵字段（ID）
主鍵值：主鍵字段中的每一個值都叫做：主鍵值。（1）

什么是主鍵？有啥用？
主鍵值是每一行記錄的唯一標識。
主鍵值是每一行記錄的身份證號！！！

記住：任何一張表都應該有主鍵，沒有主鍵，表無效！！

主鍵的特征：not null + unique（主鍵值不能是NULL，同時也不能重復！）

怎么給一張表添加主鍵約束呢？

drop table if exists t_vip;// 1個字段做主鍵，叫做：單一主鍵create table t_vip(id int primary key,  //列級約束name varchar(255));

insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');//錯誤：主鍵ID不能重復

insert into t_vip(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'//錯誤：主鍵ID不能為NULL

insert into t_vip(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

可以這樣添加主鍵嗎，使用表級約束？

drop table if exists t_vip;create table t_vip(id int,name varchar(255),primary key(id)  // 表級約束);

insert into t_vip(id,name) values(1,'zhangsan');//錯誤

insert into t_vip(id,name) values(1,'lisi');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

表級約束主要是給多個字段聯合起來添加約束？

drop table if exists t_vip;// id和name聯合起來做主鍵：復合主鍵！！！！create table t_vip(id int,name varchar(255),email varchar(255),primary key(id,name));

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');//錯誤：不能重復

insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

? 在實際開發中不建議使用：復合主鍵。建議使用單一主鍵！
? 因為主鍵值存在的意義就是這行記錄的身份證號，只要意義達到即可，單一主鍵可以做到。
? 復合主鍵比較復雜，不建議使用！！！

一個表中主鍵約束能加兩個嗎？

drop table if exists t_vip;create table t_vip(id int primary key,name varchar(255) primary key);
ERROR 1068 (42000): Multiple primary key defined

? 結論：一張表，主鍵約束只能添加1個。（主鍵只能有1個。）

主鍵值建議使用：
int
bigint
char
等類型。

? 不建議使用：varchar來做主鍵。主鍵值一般都是數字，一般都是定長的！

主鍵除了單一主鍵和復合主鍵之外，還可以這樣進行分類？
自然主鍵：主鍵值是一個自然數，和業務沒關系。
業務主鍵：主鍵值和業務緊密關聯，例如拿銀行卡賬號做主鍵值。這就是業務主鍵！

? 在實際開發中使用業務主鍵多，還是使用自然主鍵多一些？
? 自然主鍵使用比較多，因為主鍵只要做到不重復就行，不需要有意義。最好是一個自然數和任何數據都沒有關系
? 業務主鍵不好，因為主鍵一旦和業務掛鉤，那么當業務發生變動的時候，
? 可能會影響到主鍵值，所以業務主鍵不建議使用。盡量使用自然主鍵。

在mysql當中，有一種機制，可以幫助我們自動維護一個主鍵值？
主鍵值可以采用自增生成

drop table if exists t_vip;create table t_vip(id int primary key auto_increment, / /auto_increment表示自增，從1開始，以1遞增！name varchar(255));insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');insert into t_vip(name) values('zhangsan');

	select * from t_vip;?	+----+----------+
?	| id | name     |
?	+----+----------+
?	|  1 | zhangsan |
?	|  2 | zhangsan |
?	|  3 | zhangsan |
?	|  4 | zhangsan |
?	|  5 | zhangsan |
?	|  6 | zhangsan |
?	|  7 | zhangsan |
?	|  8 | zhangsan |
?	+----+----------+

7.6、外鍵約束（foreign key，簡稱FK）非常重要五顆星*****

外鍵約束涉及到的相關術語：
外鍵約束：一種約束（foreign key）
外鍵字段：該字段上添加了外鍵約束
外鍵值：外鍵字段當中的每一個值。

業務背景：
請設計數據庫表，來描述“班級和學生”的信息？

? 第一種方案：班級和學生存儲在一張表中？？？

? t_student

? no(pk) name classno classname

? 1 jack 100 北京市大興區亦莊鎮第二中學高三1班
? 2 lucy 100 北京市大興區亦莊鎮第二中學高三1班
? 3 lilei 100 北京市大興區亦莊鎮第二中學高三1班
? 4 hanmeimei 100 北京市大興區亦莊鎮第二中學高三1班
? 5 zhangsan 101 北京市大興區亦莊鎮第二中學高三2班
? 6 lisi 101 北京市大興區亦莊鎮第二中學高三2班
? 7 wangwu 101 北京市大興區亦莊鎮第二中學高三2班
? 8 zhaoliu 101 北京市大興區亦莊鎮第二中學高三2班

? 分析以上方案的缺點：
? 數據冗余，空間浪費！！！！（班級編號和班級名稱重復）
? 這個設計是比較失敗的！
?
? 第二種方案：班級一張表、學生一張表？？

? t_class 班級表

? classno(pk) classname

? 100 北京市大興區亦莊鎮第二中學高三1班
? 101 北京市大興區亦莊鎮第二中學高三1班

? t_student 學生表

? no(pk) name cno(FK引用t_class這張表的classno)

? 1 jack 100
? 2 lucy 100
? 3 lilei 100
? 4 hanmeimei 100
? 5 zhangsan 101
? 6 lisi 101
? 7 wangwu 101
? 8 zhaoliu 101

? 當cno字段沒有任何約束的時候，可能會導致數據無效。可能出現一個102，但是102班級不存在。
? 所以為了保證cno字段中的值都是100和101，需要給cno字段添加外鍵約束。
? 那么：cno字段就是外鍵字段。cno字段中的每一個值都是外鍵值。

? 注意：
? t_class是父表
? t_student是子表

? 刪除表的順序？
? 先刪子，再刪父。

? 創建表的順序？
? 先創建父，再創建子。

? 刪除數據的順序？
? 先刪子，再刪父。