1.Sql的特點(diǎn)

-- 換行不會(huì)影響代碼執(zhí)行

create database test;

-- 不同關(guān)鍵詞可以采用不同大小寫，不會(huì)影響代碼執(zhí)行

CREATE database test;

-- 單個(gè)關(guān)鍵詞內(nèi)部大小寫混用不會(huì)影響代碼執(zhí)行

creaTE database test;

-- 創(chuàng)建單行注釋

# 創(chuàng)建單行注釋

/*創(chuàng)建多行注釋 */

2.DDL

-- 創(chuàng)建一個(gè)名為 test 的數(shù)據(jù)庫

create database test;

-- 查看當(dāng)前用戶權(quán)限內(nèi)的所有數(shù)據(jù)庫

show databases;

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

|Database |

|information schema |

|test |

|performance schema |

|Sys|

-- 使用創(chuàng)建好的 test 數(shù)據(jù)庫

use test;

-- 刪除創(chuàng)建好的 test 數(shù)據(jù)庫

drop database test;

-- 1。使用 test 數(shù)據(jù)庫

use test;

-- 2. 創(chuàng)建部門信息表

create table dept(

depid char(3)，

depname varchar(20),

peoplecount int

);

-- 查看當(dāng)前數(shù)據(jù)庫所有的表

show tables;

--查看表結(jié)構(gòu)

describe test.dept;

-- 將 dept 表名改為 department

alter table dept rename department;

-- 修改字段名 depid為 depno

alter table dept change depid depno char(3);

-- 修改 depid 的字段類型為 varchar(5)

alter table dept modify depid varchar(5);

-- 在 dept 表中添加新字段 city

alter table dept add city varchar(10);

-- 修改字段 city 的排列位置

alter table dept modify city varchar(10) after depid;

-- 刪除字段 city

alter table dept drop city;

-- 刪除 dpet 表

drop table dept;

create table dept (

-- 單字段主鍵

depid char(3) ,primary key,

depname varchar(20),

peoplecount int

);

create table dept(

depid char(3)，

depname varchar(20),

peoplecount int,

多字段聯(lián)合主鍵

primary key(depname,depid)

);

-- 刪除表中的主鍵約束

alter table dept drop primary key;

create table dept(

depid char(3) primary key,

--創(chuàng)建表中的唯一約束

depname varchar(20)

uniquerpeoplecount int

-- 刪除表中的唯一約束

alter table dept drop index depname;

create table example(

e_id int primary key auto increment，-- 創(chuàng)建表中的自動(dòng)增長(zhǎng)約束

name varchar(4),

math int default 0,

minmax float

);

- 刪除表中的自動(dòng)增長(zhǎng)約束

alter table example modify e id int;

create table dept(

depid char(3) primary keyr

depname varchar(20),

peoplecount int not nul1,-- 創(chuàng)建表中的非空約束

);

-- 刪除表中的非空約束

alter table dept modify peoplecount int;

create table dept(

depid char(3) primary keyr

depname varchar(20),

peoplecount int default 0 -- 創(chuàng)建表中的默認(rèn)約束

);

-- 刪除表中的默認(rèn)約束

alter table dept modify peoplecount int;

3.DML

-- 指定字段名添加

insert into dept(depid,depname,peoplecount)values('p10’，’人力部’，,15)，(‘p20’,’研發(fā)部’,50);

-- 不指定字段名添加

insert into dept values

('p10’,’人力部’,15)，(‘p20',’研發(fā)部’,50);

-- 查看默認(rèn)安全路徑

show variables like ‘%secure%'

-- 為 Monthly Indicator 表導(dǎo)入外部 txt 文件

load data infile ‘c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/xx.txt'

into table Monthly Indicator

fields terminated by ‘\ t’

[ignore l lines];

-- 檢查導(dǎo)入內(nèi)容 Monthly Indicator

select * from Monthly Indicator;

-- 檢查導(dǎo)入數(shù)據(jù)總行數(shù) Monthly Indicator

select count(x) from Monthly Indicator;

-- 檢查表結(jié)構(gòu)

desc Monthly Indicator;

--將部門 ID為 p01的信息單獨(dú)保存到 department 表中

create table department as

select depid,depname,peoplecount

from dept

where depid='p01';

-- 將 dept 表中編號(hào)為 p11 的部門名稱修改為后勤部

update dept set depname='后勤部 where depid='p11';

-- 刪除 p20 部門的員工記錄

delete from dept where depid=p20;

-- 刪除所有的員工記錄

delete from dept;

truncate dept;

4.DQL

4.1單表查詢

-- 單獨(dú)使用 select

select 1;

-- 在表中使用 select

select 1 from emp;

-- 單獨(dú)使用 select

select 1+1

-- 在表中使用 select

select 1+1 from emp;

-- 單獨(dú)使用 select，表達(dá)式 1+1=3 不成立，返回0

select 1+1=3

-- 表達(dá)式 1+1=2 成立，返回 (真值 )1

select 1+1=2 from emp;

-- 查詢 emp 表中員工及其工資

select empno,ename,sal from emp;

-- 查詢 emp 表的所有字段

select * from emp;

-- 查詢 emp 表中的部門

select distinct deptno from emp;

-- 查詢 emp 表中不同的部門有哪些職位

select distinct deptno,job from emp;

- 查詢每位員工調(diào)整后的薪資(基本工資 +1000)

-- 使用 as 設(shè)置別名

select *,sal+1000 as 調(diào)薪 from emp;

-- 查詢每位員工調(diào)整后的薪資 (基本工資 +1000)

- 使用空格設(shè)置別名

select *,sal+1000 ’調(diào) 薪’ from emp;

-- emp 表中的領(lǐng)導(dǎo)崗位員工

select * from emp where job='manager';

-- 查詢 10 號(hào)部門和 20 號(hào)部門中 sal低于 2000 的員工信息

select * from emp where (deptno=10 or deptno=20) and sal<2000;

-- 寫法等價(jià)，返回相同的結(jié)果

select * from emp where deptno in (10,20) and sal<2000;

-- 查詢基本工資大于或等于 2000 且小于或等于 3000 的員工信息

select * from emp

where sal>=2000 and sal<=3000;

-- 寫法等價(jià)，返回相同的結(jié)果

select * from emp where sal between 2000 and 3000;

-- 找出 emp 表中的最高領(lǐng)導(dǎo)人 (沒有更高的上級(jí)員工)

select * from emp where mgr is null;

-- 空值與任何值進(jìn)行運(yùn)算，都只能返回空值

select * from emp where mgr = null;

-- 查詢姓名不以 a 開頭的員工信息

select * from emp where ename not like 'a%';

-- 查詢姓名中包含 a 的員工信息

Select * from emp where ename like ‘%a%’;

- 查詢姓名中第二個(gè)字符為 a 的員工信息

select * from emp where ename like ‘_a%’;

-- 查詢部門 10 的員工信息并按 sal 降序顯示

select *

from emp where deptno = 10 order by sal desc;

-- 查詢所有員工信息并按 deptno 升序、sal 降序顯示

select * from emp order by deptno,sal desc;

-- 查詢基本工資最高的前 5 位員工

select * from emp order by sal desc limit 5;

-- 查詢基本工資從高到低順序第 6 行~第 10 行的員工

select * from emp order by sal desc limit 5,5;

- 根據(jù)職位字母順序排序 emp 表

select * from emp order by job;

-- 只輸出上述結(jié)果的前 4 行

select * from emp order by job limit 0,4;

聚合函數(shù)

-- 查詢 emp 表中員工的最高工資、最低工資、平均工資及工資總和

select max(sal) 最高工資,min(sal) 最低工資,avg(sal) 平均工資，sum(sal) 工資總和 from emp;

-- 返回 emp 表的員工總數(shù)

select count(x) 員工總數(shù) from emp;- 本質(zhì)也是統(tǒng)計(jì)全表的記錄數(shù)，與 * 效果一致

select count(1) 員工總數(shù) from emp;

-- 返回 emp 表的部門總數(shù)

Select count(distinct deptno) 部門總數(shù)from emp;

-- 統(tǒng)計(jì)有獎(jiǎng)金的員工人數(shù)

-- 忽略了 null，但是計(jì)算了 0

select count(comm) from emp;

-- 從業(yè)務(wù)邏輯上來講，獎(jiǎng)金金額為 0 的員工不應(yīng)該視為有獎(jiǎng)金的員工

select count(comm) from emp where comm!=0;

-- 查詢公司內(nèi)部不同獎(jiǎng)金檔位的獲得人數(shù)

select comm,count(*) from emp group by comm;

-- 查詢各部門不同職位的平均工資

select deptno,job,avg(sal) as 平均工資from emp group by deptno，job;

-- 查詢各部門 clerk 的平均工資-

-- 用 having 子句篩選

select deptno,job,avg(sal) 平均工資 from emp group by deptno,job having job='clerk';

-- 用 where 子句篩選

select deptno,job,avg(sal) 平均工資 from emp where job= clerk' group by deptno,job;

-- 查詢平均工資大于 2000 的部門

select deptno,avg(sal) 平均工資from emp group by deptno

having avg(sal)>2000;

4.2函數(shù)

-- 返回 -20的絕對(duì)值

select abs(-20);

-- 返回 -20 的平方根，負(fù)數(shù)沒有平方根所以返回空值

Select sqrt(-20);

-- 查詢 emp 表中收入最高的員工的工資是平均工資的多少倍，保留 2 位小數(shù)

Select round(max(sal)/avg(sal)，2) 倍數(shù) from emp;

-- 將三個(gè)字符串合并為一個(gè)長(zhǎng)字符串

select concat('CDA'，'數(shù)據(jù)，分析);

-- 參數(shù)中包含空值，返回 null

select concat('CDA',null，，’分析’);

-- 參數(shù)中包含空白值，不影響結(jié)果

select concat('CDA',’’,’分析’);

- 從字符串第 4 位開始，截取長(zhǎng)度為 2個(gè)字符的字符串

select substring('CDA 數(shù)據(jù)分析，4,2);

-- 從字符串第 4 位開始，截取到字符串結(jié)束

select substring('CDA 數(shù)據(jù)分析，4);

- 將emp 表中 job 字段中的 manager 替換為 director

select job,replace(job，’manager','director') from emp;

-- 獲取日期中的年月日 (日期時(shí)間、字符串或者數(shù)值均可被識(shí)別 )

select year('2020-01-30') 年份,month('20200130') 月份day(20200130)日;

按照日月年的格式返回日期

select date format('20-01-30 12:00:00'，%d-%m-%y') 日月年;

-- 計(jì)算 2020年1月1日2個(gè)月后的日期

select date add("2020-01-01",interval 2 month);

-- 計(jì)算員工的工齡 (雇傭日期與當(dāng)前日期的差 )

select ename,floor(datediff(curdate(),hiredate)/365) as工齡 from emp;

-- 查詢各部門員工姓名

select deptno,qroup_concat(ename order by ename separator/) 員工姓名列表 from emp group by deptno;

-- 查詢公司所有部門編號(hào)

select group_concat(distinct deptno order by deptno) 部門

from emp;

-- 查詢每位員工的實(shí)發(fā)工資 (基本工資 + 獎(jiǎng)金)

select ename,sal+ifnull(comm，0) 實(shí)發(fā)工資

from emp;

查詢員工的工資級(jí)別，3000 及以上為高，1500 及以下為低，其余為中

-- 使用 if 函數(shù)

select ename,sal,

if(sal>=3000，'高,if(sal<=1500，低,中')) 工資級(jí)別

from emp;-- 使用 case 邏輯表達(dá)式

select ename,sal,

case when sal>=3000 then r高

when sal<=1500 then 低 else中 end 工資級(jí)別

from emp;

-- 聚合函數(shù)，得到公司所有員工的平均工資

select avg(sal) 平均工資 from emp;

- 開窗函數(shù)，查詢每位員工與公司所有員工的平均工資之間的情況

select *,avg(sal) over() 平均工資 from emp;

-- 聚合函數(shù)，查詢各部門的平均工資

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

- 開窗函數(shù)，查詢每位員工與所屬部門平均工資之間的情況

select *,avg(sal) over(partition by deptno) 平均工資from

emp;

-- 按入職日期查詢各部門的累計(jì)工資

select *,sum(sal) over(partition by deptno order by hiredate) 累計(jì)工資 from emp;

查詢各部門員工的工資排名

select *，

row number() over(order by sal desc) 排名1,

dense rank() over(order by sal desc) 排名2，

rank() over(order by sal desc) 排名 3

from emp;

4.3多表查詢

--union

select *from t1

union

select *from t2;

-- union all，其中(a，12) 出現(xiàn)了2次

select * from t1

union all

select * from t2;

- 查詢 manager 的姓名、所屬部門名稱和入職日期

-- 使用 on 聲明連接條件

select ename,dname,job,hiredate

from emp

inner join dept on emp.deptno=dept.deptno

where job='manager';

-- 使用 using 聲明連接條件

select ename,dname,job,hiredate

from emp

inner join dept using (deptno)

where job='manager';

查詢每位員工的 ename、dname、sal

-- 使用 on 聲明連接條件

select ename,dname,sal from emp

left join dept on emp.deptno=dept.deptno;

-- 使用 using 聲明連接條件

select ename,dname,sal

from emp

left join dept using(deptno);

查詢各地區(qū)的員工數(shù)量--

-- 使用 on 聲明連接條件

select loc,count(empno) 員工數(shù)

from emp right join dept on emp.deptno=dept.deptno

group by loc;

-- 使用 using 聲明連接條件

select loc,count(empno) 員工數(shù)

from emp

right join dept using (deptno)

group by loc;

-- 對(duì)表 t1 和表 t2 進(jìn)行全連接

select * from tl left join t2 on t1.公共字段 1=t2。公共字段2

union

select * from tl right join t2 on t1.公共字段1=t2.公共字段2;

--查詢所有員工的姓名及直屬領(lǐng)導(dǎo)的姓名

select 員工表 .ename 員工姓名 ，領(lǐng)導(dǎo)表 .ename 領(lǐng)導(dǎo)姓名

from emp 員工表 left join emp 領(lǐng)導(dǎo)表 on 員工表 .mgr= 領(lǐng)導(dǎo)表empno;

查詢?nèi)肼毴掌谠缬谥睂兕I(lǐng)導(dǎo)的員工的姓名及所屬部門

select 員工表 .empno，員工表 .ename,dname

from emp 員工表

left join emp 領(lǐng)導(dǎo)表 on 員工表 .mgr= 領(lǐng)導(dǎo)表 .empno

left join dept on 員工表 .deptno=dept.deptno

where 員工表 .hiredate< 領(lǐng)導(dǎo)表 .hiredate;

-- 使用 from 子句

select * from t1,t2;

-- 使用 cross join

select * from t1 cross join t2;

-- 表 t1和表 t2 中，均包含 4 條記錄，最后返回 4*4，即 16 條記錄

根據(jù)分組邏輯創(chuàng)建表--

create table salgrade(grade int,losal int,hisal int);

insert into salgrade values('低收入人群,700,1500)， ('中等收入人群，,1501,3000)， ('高收入人群，,3001,9999);

使用 on 聲明不等值的連接條件

select empno,ename,sal,grade

from emp left join salgrade

on sal between losal and hisal;

-- 使用 where 聲明不等值的連接條件

select empno,ename,sal,grade

from emp,salgrade

where sal between losal and hisal;

4.4子查詢

-- 查詢各部門員工人數(shù)占比

-- 查詢整體員工數(shù)量

select count(*) from emp;

將整體員工數(shù)量作為子查詢來查詢各部門員工人數(shù)占比

select deptno,count(x) 員工人數(shù)

count(*)/(select count(x) from emp) 員工人數(shù)占比

from emp group by deptno;

-- 查詢各部門最高工資

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

將各部門最高工資作為子查詢，找出所有部門內(nèi)部員工工資等于最高工資的員工

select empno,ename,sal,emp.deptno

from emp

left join (select deptno,max(sal) as最高工資from emp group by deptno) as t

on emp.deptno=t.deptno

where sal= 最高工資 ;

--where 本身只是查找所有工資等于最大值的記錄，返回記錄順序是隨機(jī)的如果需要有序返回，可以使用 order by 子句

-- 也可以直接使用開窗函數(shù)對(duì)工資進(jìn)行排名，查詢排名為 1的員工

select empno,ename,sal,deptno

from(select *,dense rank() over(partition by deptno order by sal desc) 工資排名 from emp) t

where 工資排名 =1;

-- 查詢公司所有員工的平均工資

select avg(sal) from emp;

-- 使用比較操作字符將員工工資與子查詢返回的平均工資進(jìn)行比較

select * from emp where sal>(select avg(sal) from emp);

-- 查詢 smith 的部門和職位

select deptno,job from emp where ename='smith';

使用比較操作字符進(jìn)行子查詢-

select empno,ename,job,deptno

from emp

where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

查詢各部門最高工資的員工

select empno,ename,sal,deptno

from emp

where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

查詢哪些人不是普通員工，即領(lǐng)導(dǎo)的工號(hào)

select distinct mgr from emp where mgr is not null;

上述代碼的查詢結(jié)果如圖3-103所示

將第一步作為子查詢來篩選數(shù)據(jù)，具體整合后的代碼如下

-- 將領(lǐng)導(dǎo)的工號(hào)作為子查詢來篩選數(shù)據(jù)

select empno,ename,sal

from emp

where empno not in (select distinct mgr from emp where mgr is not null);

-- 查詢公司部門情況，若公司有 20 的部門，則返回該部門的員工情況

select *

from emp

where exists (select *from dept where deptno=20) and deptno =20;

--exists 后接的子查詢并不對(duì)數(shù)據(jù)進(jìn)行篩選，只決定外查詢執(zhí)不執(zhí)行-

select *

from emp

where exists (select * from dept where deptno=20);

- 查詢 30 號(hào)部門所有員工的基本工資

select sal from emp where deptno=30;

-- 將 30 號(hào)部門所有員工的基本工資作為子查詢對(duì)記錄進(jìn)行篩選

Select *

from emp

where sal>any(select sal from emp where deptno=30) and deptno<>30;

--查詢基本工資高于 30 號(hào)部門所有員工的員工信息

--將any 改為 all

select *

from emp

where sal>all(select sal from emp where deptno=30);

5.視圖

5.1創(chuàng)建視圖

-- 創(chuàng)建一個(gè)名為 job_minsal_view，用來查看不同職位的最低工資及從事該工作的員工姓名和職位的視圖

create view job_minsal_view as

(select ename,t.job，最低工資 from(select job,min(sal) 最低工資 from emp group by job) t

left join emp on t.job=emp.job where sal= 最低工資);

5.2修改視圖

-- 修改視圖 job_minsal_view，為了避免重名情況增加員工編碼作為查詢結(jié)果

alter view job minsal view as

select empno,ename,t.job，最低工資

from

(select job,min(sal) 最低工資 from emp group by job) t

left join emp on t.job= emp.job

where sal= 最低工資;

5.3刪除視圖

刪除視圖 job_minsal_view

drop view job_minsal_view;