1.Sql的特點

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

create database test;

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

CREATE database test;

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

creaTE database test;

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

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

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

2.DDL

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

create database test;

-- 查看當前用戶權限內(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

);

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

show tables;

--查看表結構

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)建表中的自動增長約束

name varchar(4),

math int default 0,

minmax float

);

- 刪除表中的自動增長約束

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)建表中的默認約束

);

-- 刪除表中的默認約束

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);

-- 查看默認安全路徑

show variables like ‘%secure%'

-- 為 Monthly Indicator 表導入外部 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];

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

select * from Monthly Indicator;

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

select count(x) from Monthly Indicator;

-- 檢查表結構

desc Monthly Indicator;

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

create table department as

select depid,depname,peoplecount

from dept

where depid='p01';

-- 將 dept 表中編號為 p11 的部門名稱修改為后勤部

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

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

delete from dept where depid=p20;

-- 刪除所有的員工記錄

delete from dept;

truncate dept;

4.DQL

4.1單表查詢

-- 單獨使用 select

select 1;

-- 在表中使用 select

select 1 from emp;

-- 單獨使用 select

select 1+1

-- 在表中使用 select

select 1+1 from emp;

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

select 1+1=3

-- 表達式 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 設置別名

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

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

- 使用空格設置別名

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

-- emp 表中的領導崗位員工

select * from emp where job='manager';

-- 查詢 10 號部門和 20 號部門中 sal低于 2000 的員工信息

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

-- 寫法等價，返回相同的結果

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

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

select * from emp

where sal>=2000 and sal<=3000;

-- 寫法等價，返回相同的結果

select * from emp where sal between 2000 and 3000;

-- 找出 emp 表中的最高領導人 (沒有更高的上級員工)

select * from emp where mgr is null;

-- 空值與任何值進行運算，都只能返回空值

select * from emp where mgr = null;

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

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

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

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

- 查詢姓名中第二個字符為 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;

-- 只輸出上述結果的前 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)計全表的記錄數(shù)，與 * 效果一致

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

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

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

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

-- 忽略了 null，但是計算了 0

select count(comm) from emp;

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

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

-- 查詢公司內(nèi)部不同獎金檔位的獲得人數(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的絕對值

select abs(-20);

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

Select sqrt(-20);

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

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

-- 將三個字符串合并為一個長字符串

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

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

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

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

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

- 從字符串第 4 位開始，截取長度為 2個字符的字符串

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

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

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

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

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

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

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

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

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

-- 計算 2020年1月1日2個月后的日期

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

-- 計算員工的工齡 (雇傭日期與當前日期的差 )

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

-- 查詢各部門員工姓名

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

-- 查詢公司所有部門編號

select group_concat(distinct deptno order by deptno) 部門

from emp;

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

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

from emp;

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

-- 使用 if 函數(shù)

select ename,sal,

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

from emp;-- 使用 case 邏輯表達式

select ename,sal,

case when sal>=3000 then r高

when sal<=1500 then 低 else中 end 工資級別

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;

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

select *,sum(sal) over(partition by deptno order by hiredate) 累計工資 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;

-- 對表 t1 和表 t2 進行全連接

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

union

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

--查詢所有員工的姓名及直屬領導的姓名

select 員工表 .ename 員工姓名 ，領導表 .ename 領導姓名

from emp 員工表 left join emp 領導表 on 員工表 .mgr= 領導表empno;

查詢?nèi)肼毴掌谠缬谥睂兕I導的員工的姓名及所屬部門

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

from emp 員工表

left join emp 領導表 on 員工表 .mgr= 領導表 .empno

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

where 員工表 .hiredate< 領導表 .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 本身只是查找所有工資等于最大值的記錄，返回記錄順序是隨機的如果需要有序返回，可以使用 order by 子句

-- 也可以直接使用開窗函數(shù)對工資進行排名，查詢排名為 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;

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

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

-- 查詢 smith 的部門和職位

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

使用比較操作字符進行子查詢-

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);

查詢哪些人不是普通員工，即領導的工號

select distinct mgr from emp where mgr is not null;

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

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

-- 將領導的工號作為子查詢來篩選數(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 后接的子查詢并不對數(shù)據(jù)進行篩選，只決定外查詢執(zhí)不執(zhí)行-

select *

from emp

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

- 查詢 30 號部門所有員工的基本工資

select sal from emp where deptno=30;

-- 將 30 號部門所有員工的基本工資作為子查詢對記錄進行篩選

Select *

from emp

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

--查詢基本工資高于 30 號部門所有員工的員工信息

--將any 改為 all

select *

from emp

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

5.視圖

5.1創(chuàng)建視圖

-- 創(chuàng)建一個名為 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，為了避免重名情況增加員工編碼作為查詢結果

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;