柚子快報(bào)邀請(qǐng)碼778899分享：MySQL筆記

Yandex.Market俄語外貿(mào)市綜合2025-09-02410

http://yzkb.51969.com/

MySQL筆記_MySQL的再探

呈: MySQL筆記_MySQL的小試

連接查詢

多張表聯(lián)合查詢,連接方式： 1. 內(nèi)連接：等值連接、非等值連接、自連接。2. 外連接：左（外）連接、右（外）連接、全連接。笛卡爾積：若兩張表進(jìn)行連接查詢無過濾條件則條數(shù) 為兩表?xiàng)l數(shù)乘積找出員工的部門名稱

mysql> select e.ename,d.dname from emp e ,dept d;

-- 上條更好且需要起別名易于分別且效率更高可讀性好

mysql> select ename,dname from emp,dept;

+--------+------------+

| ename | dname |

+--------+------------+

| SMITH | ACCOUNTING |

| SMITH | RESEARCH |

| SMITH | SALES |

| SMITH | OPERATIONS |

| TURNER | SALES |

| TURNER | OPERATIONS |

| ADAMS | ACCOUNTING |

| ADAMS | RESEARCH |

| ADAMS | SALES |

| ADAMS | OPERATIONS |

...

| MILLER | SALES |

| MILLER | OPERATIONS |

+--------+------------+

56 rows in set (0.01 sec) -- 56 = 14 * 4

ps:避免笛卡爾積現(xiàn)象,同時(shí)不會(huì)減少記錄的匹配次數(shù)，因而可以用來where,having加條件。

mysql> select e.ename,d.dname

-> from emp e , dept d

-> where

-> e.deptno = d.deptno;//92語法不用

+--------+------------+

| 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 rows in set (0.01 sec)

內(nèi)連接

內(nèi)連接 --> 等值連接條件是等量關(guān)系則可以分離條件

select e.ename, d.dname

from emp e

(inner)join dept d --inner 可以省略帶上更清晰

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 |

+--------+------------+

14 rows in set (0.00 sec)

語法

select

e.ename,d.dname

from

emp e

join

dept d

連接條件

where

過濾條件

內(nèi)連接 --> 非等值連接

select e.ename,e.sal,s.grade

from emp e

join salgrade s

on e.sal between s.losal and s.hisal;

+--------+---------+-------+

| ename | sal | grade |

+--------+---------+-------+

| SMITH | 800.00 | 1 |

| ALLEN | 1600.00 | 3 |

| WARD | 1250.00 | 2 |

| JONES | 2975.00 | 4 |

| MARTIN | 1250.00 | 2 |

| BLAKE | 2850.00 | 4 |

| CLARK | 2450.00 | 4 |

| SCOTT | 3000.00 | 4 |

| KING | 5000.00 | 5 |

| TURNER | 1500.00 | 3 |

| ADAMS | 1100.00 | 1 |

| JAMES | 950.00 | 1 |

| FORD | 3000.00 | 4 |

| MILLER | 1300.00 | 2 |

+--------+---------+-------+

14 rows in set (0.00 sec)

內(nèi)連接–>自連接

eg. 找到每個(gè)員工的上級(jí)領(lǐng)導(dǎo)（13條記錄忽略NULL）

select a.ename,b.ename

from emp a

join emp b

on a.mgr = b.empno;

+--------+-------+

| ename | ename |

+--------+-------+

| SMITH | FORD |

| ALLEN | BLAKE |

| WARD | BLAKE |

| JONES | KING |

| MARTIN | BLAKE |

| BLAKE | KING |

| CLARK | KING |

| SCOTT | JONES |

| TURNER | BLAKE |

| ADAMS | SCOTT |

| JAMES | BLAKE |

| FORD | JONES |

| MILLER | CLARK |

+--------+-------+

13 rows in set (0.00 sec)

-- 將兩張表看作一張表進(jìn)行自連接關(guān)鍵要找到合適的關(guān)系

-- 上表King無上級(jí)

外連接

區(qū)別：與內(nèi)鏈接 -能夠匹配記錄可以查詢出來就是內(nèi)鏈接且無主輔之分

外連接：分主次若副表無主表數(shù)據(jù) 模擬NULL與之匹配

左連接右連接指的是主表的位置可以相互替換不忽略匹配不上的情況。

mysql> select a.ename,b.ename

-> from emp a

-> left(outer) join emp b

-> on a.mgr = b.empno;

-- or

mysql> select a.ename,b.ename

-> from emp b

-> right(outer) join emp a

-> on a.mgr = b.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)

-- 主表數(shù)據(jù) 無條件全部查詢出來

左右連接

eg: 找出哪個(gè)部門沒有員工

mysql> select e.*[可以省略],d.*

-> from emp e

-> right join dept d

-> on e.deptno = d.deptno

-> where e.empno is NULL;

+-------+-------+------+------+----------+------+------+--

+-------+-------+------+------+----------+------+------+--

+-------+-------+------+------+----------+------+------+--

1 row in set (0.00 sec)

mysql> select d.*

-> from emp e

-> right join dept d

-> on e.deptno = d.deptno

-> where e.empno is NULL;

+--------+------------+--------+

| DEPTNO | DNAME | LOC |

+--------+------------+--------+

| 40 | OPERATIONS | BOSTON |

+--------+------------+--------+

1 row in set (0.00 sec)

三張表的連接 eg:找出每一個(gè)員工的部門名稱和薪資等級(jí) -- 找出每一個(gè)員工的部門名稱和薪資等級(jí)

...

-- A

-- join B

-- join C

-- A 先和B連接結(jié)果在和 C連接

select e.ename,d.dname,s.grade,e1.ename as authorname

from emp e

join dept d

on e.deptno = d.deptno

join salgrade s

on e.sal between s.losal and s.hisal

left join emp e1

on e.mgr = e1.empno;

+--------+------------+-------+

| ename | dname | grade |

+--------+------------+-------+

| SMITH | RESEARCH | 1 |

| ALLEN | SALES | 3 |

| WARD | SALES | 2 |

| JONES | RESEARCH | 4 |

| MARTIN | SALES | 2 |

| BLAKE | SALES | 4 |

| CLARK | ACCOUNTING | 4 |

| SCOTT | RESEARCH | 4 |

| KING | ACCOUNTING | 5 |

| TURNER | SALES | 3 |

| ADAMS | RESEARCH | 1 |

| JAMES | SALES | 1 |

| FORD | RESEARCH | 4 |

| MILLER | ACCOUNTING | 2 |

+--------+------------+-------+

-- ----------------------------------------加領(lǐng)導(dǎo)名

+--------+------------+-------+------------+

+--------+------------+-------+------------+

+--------+------------+-------+------------+

14 rows in set (0.00 sec)

from 后嵌套子查詢 eg:找出每個(gè)部門的平均薪水的薪資等級(jí) 1. 找出每個(gè)部門的平均薪水 (按照部門標(biāo)號(hào) 分組求平均薪資)

select deptno,avg(sal) from emp group by deptno;

+--------+-------------+

| deptno | avg(sal) |

+--------+-------------+

| 10 | 2916.666667 |

| 20 | 2175.000000 |

| 30 | 1566.666667 |

+--------+-------------+

mysql> select t.*,s.grade

-> from (select deptno,avg(sal) as avgsal from emp group by deptno) t

-> join salgrade s

-> on t.avgsal between s.losal and hisal;

+--------+-------------+-------+

| deptno | avgsal | grade |

+--------+-------------+-------+

| 30 | 1566.666667 | 3 |

| 10 | 2916.666667 | 4 |

| 20 | 2175.000000 | 4 |

+--------+-------------+-------+

找出每個(gè)部門平均的薪水等級(jí) mysql> select e.deptno,avg(s.grade)

> from emp e

> join salgrade s

> on e.sal between s.losal and s.hisal

> group by e.deptno;

在 select 后嵌套子查詢 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 |

+--------+------------+

14 rows in set (0.00 sec)

union 聯(lián)合查詢

union 可以將查詢結(jié)果集相加 -- 找出工作崗位是 salesman 和 manager 的員工

-- 第一種 or

mysql> select ename,job

-> from emp

-> where job = 'MANAGER' or job = 'SALESMAN';

-- 第二種 in

mysql> select ename,job

-> from emp

-> where job in('MANAGER','SALESMAN');

-- 第三種 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 限制查詢

往往用于分頁或排序查詢來限制結(jié)果的顯示方式

其中：

MySQL特有l(wèi)imit 查詢 ,Oracle 有類似機(jī)制 rownum limit 只取結(jié)果集中的部分?jǐn)?shù)據(jù) limit 命令格式：limit startIndex,length

ps:起始值 startIndex(從零開始,可以不寫) 長度length

limit 最后執(zhí)行

找出收入倒序排列的前5位

mysql> select ename,sal from emp order by sal desc limit 0,5;

+-------+---------+

| ename | sal |

+-------+---------+

| KING | 5000.00 |

| SCOTT | 3000.00 |

| FORD | 3000.00 |

| JONES | 2975.00 |

| BLAKE | 2850.00 |

+-------+---------+

5 rows in set (0.01 sec)

eg:找出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 |

+--------+---------+

6 rows in set (0.00 sec)

-- 通用翻頁每頁顯示 pageSize條記錄

-- 第 pageNo 頁 (PageNo - 1) *pageNo = pageSize

MySQL 常見數(shù)據(jù)類型

與 Java 數(shù)據(jù)類型對(duì)應(yīng)理解記憶, 分別有 int、 bigint 、long;float 、double; char、varchar(類似Java StringBuilder構(gòu)造的字符串 )、date(from java.sql.Date); BLOB二進(jìn)制大對(duì)象(存儲(chǔ)圖片、視頻等流媒體信息) Binary Large Object;CLOB字符大對(duì)象存儲(chǔ)較大文本可以存儲(chǔ) 4G 字符串 Character Large Object。

eg. id(int) name(varchar) playtime(date/char) seal(BLOB) - IO流 history(CLOB)

char 與 varchar 的區(qū)別

char 效率高不用判斷長度， char(6) 少補(bǔ) 多錯(cuò) varchar(6) 智能分配 char 字段數(shù)據(jù)長度是定長的 eg. 性別、生日 varchar eg.簡(jiǎn)介姓名

DQL 語句

建表語句

-- 語法

> create table 表名(

字段名：數(shù)據(jù)類型,

...

);

-- 一般數(shù)據(jù)庫創(chuàng)建表格時(shí) 建議以 t_/tbl_

-- t_movie 命名

eg.建立一個(gè) 擁有 “學(xué)號(hào) 姓名性別班級(jí)編號(hào) 生日” 的學(xué)生表格

-- 學(xué)號(hào) 姓名性別班級(jí)編號(hào) 生日

create table t_stu(

no bigint,

name varchar(255),

sex char(1),

classno varchar(255),

birth char(10)

);

Query OK, 0 rows affected (0.01 sec)

mysql> desc t_stu;

+---------+--------------+------+-----+-------+

+---------+--------------+------+-----+-------+

| no | bigint(20) | YES | | NULL | |

| sex | char(1) | YES | | NULL | |

+---------+--------------+------+-----+-------+

5 rows in set (0.00 sec)

插入數(shù)據(jù) insert

-- 語法：

insert into 表名(

字段名,

)

values

(

val1,

val2,

val3,

) ;

-- ps :值和字段的數(shù)量要相同且數(shù)據(jù)類型要對(duì)應(yīng)

插入方式，所有字段都賦值，或只給單個(gè)字段賦值。 insert into t_stu(

no,name,sex,classno,birth

)values(

1,'Zhangsan','f','gaosan1ban','1980-10-23'

);

-- 插入一個(gè)字段其他值自動(dòng) 賦值NULL 且順序可變

insert into t_student(name)values('Zhangsan');

Query OK, 1 row affected (0.01 sec)

刪表

drop table if exists t_stu;

create table t_student(

no bigint,

name varchar(255),

sex char(1) default 1,//指定默認(rèn)值

classno varchar(255),

birth char(10)

)

更新表

當(dāng) insert 執(zhí)行一次表中新產(chǎn)生一行忽略NULL 且不能再用insert 插入只能用 update 更新若按順序插入前方字段默認(rèn) 按插入順序和數(shù)量執(zhí)行

ps.如果沒有條件則全表更新

-- 語法格式：

update 表名 set

字段1=值1,

字段2=值2,

where conditon ;

-- 更新示例

-- 插入單行數(shù)據(jù)

insert into t_student values(

10,

'Zhangsan',

'gaoshan',

);

-- 插入多行數(shù)據(jù)

insert into t_student values(

20,

'Lisi',

'gaoshan',

),(

30,

'Wangwu',

'gaoshan',

);

update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

mysql> update dept1 set loc = 'x',dname = 'y';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from dept1;

+--------+-------+------+

| DEPTNO | DNAME | LOC |

+--------+-------+------+

| 10 | y | x |

| 20 | y | x |

| 30 | y | x |

+--------+-------+------+

4 rows in set (0.00 sec)

表的復(fù)制 create table 表名 as dql 語句;

create table emp1 as select ename,sal from emp;

-- 將查詢結(jié)果賦作新表

create table emp1 as select* from emp;

-- 將查詢結(jié)果插入一張表

刪除數(shù)據(jù) 不刪除表格

語法格式

-- 刪除可以回滾 rollback

mysql>delete from table0 where conditions

/*if no condition delete all data*/

-- 截?cái)?不可回滾

mysql> truncate table emp1;

Query OK, 0 rows affected (0.01 sec)

DDL 對(duì)表結(jié)構(gòu) 修改可以借助工具且很少發(fā)生

CRUD ---> R --> create retrieve update delete

約束(Constraint)

非空約束(not NULL) 確保數(shù)據(jù) 合法、有效、完整性

唯一性約束(unique) 用戶名方便管理不能重復(fù)

主鍵約束(primary key) ：不能為NULL 且不能重復(fù)（PK）

外鍵約束(foriegn key)（FK）

檢查約束(check) : Oracle 支持 check 約束 mysql 不支持該約束

非空約束 not NULL

drop table if exists t_user;

create table t_user(

id int,

username varchar(255) not NULL,

password varchar(255) not NULL,

);

insert into t_user(id,password) values(1,'123');

1364 ---> 缺默認(rèn)值

唯一性約束 unique

唯一約束修飾的字段具有唯一性不能重復(fù) 但可以為NULL

drop table if exists t_user;

create table t_user(

id int,

username varchar(255) unique -- 列級(jí)約束

);

insert into t_user values(1,'Zhangsan');

insert into t_user values(2,'Zhangsan');

同時(shí)加 unique

drop table if exists t_user;

create table t_user(

id int,

usercode varchar(255),

username varchar(255),

unique(usercode,username) -- 加一個(gè)約束表級(jí)約束

);

insert into t_user values(1,'111','Zhangsan');

insert into t_user values(2,'111','Lisi');

insert into t_user values(3,'222','Zhangsan');

select * from t_user;

mysql> select * from t_user;

+------+----------+----------+

| id | usercode | username |

+------+----------+----------+

| 1 | 111 | Zhangsan |

| 2 | 111 | Lisi |

| 3 | 222 | Zhangsan |

+------+----------+----------+

3 rows in set (0.00 sec)

------------------分別加約束----------------------

drop table if exists t_user;

create table t_user(

id int,

usercode varchar(255) unique,

username varchar(255) unique

);

insert into t_user values(1,'111','Zhangsan');

insert into t_user values(2,'111','Lisi');

主鍵約束

drop table if exists t_user;

create table t_user(

id int primary key,

username varchar(255)

-- primary key(id)

);

寫在最后

MySQL的探索還在路上，加油?。?/p>

柚子快報(bào)邀請(qǐng)碼778899分享：MySQL筆記

http://yzkb.51969.com/

參考閱讀

評(píng)論可見，查看隱藏內(nèi)容

本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。

轉(zhuǎn)載請(qǐng)注明，如有侵權(quán)，聯(lián)系刪除。

本文鏈接：http://gantiao.com.cn/post/19144854.html

發(fā)布評(píng)論

取消回復(fù)

您暫未設(shè)置收款碼

請(qǐng)?jiān)谥黝}配置——文章設(shè)置里上傳

金鑰匙跨境

掃描二維碼手機(jī)訪問

文章目錄

欧美free性护士vide0shd,老熟女,一区二区三区,久久久久夜夜夜精品国产,久久久久久综合网天天,欧美成人护士h版

柚子快報(bào)邀請(qǐng)碼778899分享：MySQL筆記

隨便看看

特朗普要求美國最高法院暫停執(zhí)行TikTok強(qiáng)制出售令

最新留言

您暫未設(shè)置收款碼