MySQL從入門到入魔(02)丨【綻放吧！數據庫】

###練習:

創建數據庫mydb4 字符集utf8 并使用

create database mydb4 character set utf8; use mydb4;

1

2

在數據庫中創建員工表emp 字段:id,name,sal,deptId(部門id) 字符集utf8

create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;

1

創建部門表dept 字段:id,name,loc(部門地址) 字符集utf8

create table dept(id int,name varchar(10),loc varchar(20))charset=utf8;

1

部門表插入以下數據: 1 神仙部 天庭 2 妖怪部 盤絲洞

insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盤絲洞');

1

員工表插入一下數據: 1 悟空 5000 1 , 2 八戒 2000 1 , 3 蜘蛛精 8000 2, 4 白骨精 9000 2

insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);

1

查詢工資6000以下的員工姓名和工資

select name,sal from emp where sal<6000;

1

修改神仙部的名字為取經部

update dept set name='取經部' where id=1;

1

給員工添加獎金comm字段

alter table emp add comm int;

1

修改部門id為1的部門獎金為500

update emp set comm=500 where deptId=1;

1

把取經部的地址改成五臺山

update dept set loc='五臺山' where id=1;

1

刪除兩個表

drop table emp; drop table dept;

1

###主鍵約束 primary key

主鍵: 表示數據唯一性的字段稱為主鍵

約束: 約束是創建表時給表字段添加的限制條件

主鍵約束: 限制主鍵的值唯一且非空

測試:

create table t1(id int primary key,name varchar(10)); insert into t1 values(1,'aaa'); insert into t1 values(1,'bbb'); //報錯 主鍵值不能重復 Duplicate entry '1' for key 'PRIMARY' insert into t1 values(null,'ccc');//報錯 主鍵值不能為null Column 'id' cannot be null

1

2

3

4

###主鍵+自增 auto_increment

自增規則:從歷史最大值+1

測試:

create table t2(id int primary key auto_increment,name varchar(10)); insert into t2 values(null,'aaa'); 1 insert into t2 values(null,'bbb'); 2 insert into t2 values(10,'ccc'); 10 insert into t2 values(null,'ddd'); 11 delete from t2 where id>=10; insert into t2 values(null,'eee'); 12

1

2

3

4

5

6

7

###導入*.sql文件

把下載的emp.sql 放到d盤根目錄

source d:/emp.sql;

如果導入后 執行select * from emp 發現有亂碼 執行 set names gbk;

###去重distinct

查詢員工表中有哪些不同的工作

select distinct job from emp;

1

###is null 和 is not null

判斷某個字段的值為null時不能使用=

查詢沒有上級領導的員工信息

select * from emp where mgr is null;

1

查詢有上級領導的員工姓名和上級領導編號

select ename,mgr from emp where mgr is not null;

1

###比較運算符 > < >= <= = !=和<>

16. 查詢員工工資小于等于3000的員工姓名和工資

select ename,sal from emp where sal<=3000;

1

查詢工作不是程序員的員工姓名和工作(兩種寫法)

select ename,job from emp where job!='程序員'; select ename,job from emp where job<>'程序員';

1

2

and 和 or

如果查詢數據時使用了多個條件,多個條件同時滿足使用and, 多個條件滿足一個就可以使用or

and類似java中的&& , or類似java中的||

查詢1號部門工資大于1500的員工信息

select * from emp where deptno=1 and sal>1500;

1

查詢工作是人事或者工資大于3000的員工姓名,工作和工資.

select ename,job,sal from emp where job='人事' or sal>3000;

1

###in(x,y,z)

當查詢某個字段的值為多個的時候使用in關鍵字

查詢工資為1500,3000,5000的員工信息

select * from emp where sal=1500 or sal=3000 or sal=5000; select * from emp where sal in(1500,3000,5000);

1

2

###between x and y

查詢工資在1000到2000之間的員工信息(包括1000和2000)

select * from emp where sal>=1000 and sal<=2000; select * from emp where sal between 1000 and 2000;

1

2

查詢工資在1000到2000以外的員工信息

select * from emp where sal not between 1000 and 2000;

1

查詢工資不等于5000,3000,800的員工信息

select * from emp where sal not in(800,3000,5000);

1

###綜合練習

查詢有上級領導并且是3號部門的員工信息

select * from emp where mgr is not null and deptno=3;

1

查詢2號部門工資在1000到2000之間的員工姓名 工資和部門編號

select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;

1

查詢1號部門工資為800和1600的員工信息

select * from emp where deptno=1 and sal in(800,1600);

1

查詢1號和2號部門工資高于2000的員工信息

select * from emp where deptno in(1,2) and sal>2000;

1

查詢員工表中出現的部門編號有哪幾個

select distinct deptno from emp;

1

###模糊查詢like

_:代表1個未知字符

%:代表0或多個未知字符

舉例:

以x開頭 x%

以x結尾 %x

包含x %x%

第二個字符是x _x%

倒數第三個是x %x__

以x開頭 倒數第二個是y x%y_

查詢員工表中姓孫的員工姓名

select ename from emp where ename like '孫%';

1

查詢名字以精結尾的員工信息

select * from emp where ename like '%精';

1

查詢工作中包含銷售的員工姓名和工作

select ename,job from emp where job like '%銷售%';

1

###排序 order by

格式: order by 字段 asc(默認升序)/desc降序 寫在SQL語句的后面

查詢工資高于2000的員工信息,按照工資升序排序

select * from emp where sal>2000 order by sal;

1

查詢每個員工的姓名,工資和部門編號,按照部門編號降序排序

select ename,sal,deptno from emp order by deptno desc;

1

多字段排序,在order by后面寫多個字段 通過逗號分隔

查詢每個員工的姓名,工資和部門編號,按照部門編號降序排序,如果部門編號一致則按照工資降序排序

select ename,sal,deptno from emp order by deptno desc,sal desc;

1

查詢1號和2號部門的員工信息按照部門編號升序排序,如果部門編號一致則按照工資降序排序

select * from emp where deptno in(1,2) order by deptno, sal desc;

1

###分頁查詢

格式: limit 跳過的條數,請求條數(也代表每頁條數) , 寫在SQL語句的最后

請求員工表中按照工資降序排序前3條數據(請求第一頁的3條數據)

select * from emp order by sal desc limit 0,3;

1

查詢員工表中工資降序排序的第4 5 6條數據

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

1

查詢員工表中第三頁的2條數據(請求第5和第6條數據)

select * from emp limit 4,2;

1

查詢工資最低的員工信息

select * from emp order by sal limit 0,1;

1

查詢員工表中工資升序第四頁的2條數據

select * from emp order by sal limit 6,2;

1

###綜合練習題

查詢員工表中名字里第二個字是八的員工姓名和工資

select ename,sal from emp where ename like '_八%';

1

查詢工作中包含售字的員工姓名和工作

select ename,job from emp where job like '%售%';

1

查詢工資高于1000塊錢的員工姓名和工資,按照工資降序排序,查詢第二頁的3條數據

select ename,sal from emp where sal>1000 order by sal desc limit 3,3;

1

###數值計算+ - * /

查詢每個員工的姓名,工資和年終獎(3個月的工資)

select ename,sal,sal*3 from emp;

1

- 別名,可以對查詢的字段起別名

1

select ename as '名字',sal as '工資',sal*3 as '年終獎' from emp; select ename '名字',sal '工資',sal*3 '年終獎' from emp; select ename 名字,sal 工資,sal*3 年終獎 from emp;

1

2

3

查詢每個員工姓名,工資和漲薪5塊錢之后的工資

select ename,sal,sal+5 漲薪后 from emp;

1

###聚合函數

聚合函數是對查詢的多條數據進行統計查詢,包括:求平均值,最大值,最小值,求和,計數

平均值avg

查詢1號部門的平均工資

select avg(sal) from emp where deptno=1;

1

查詢程序員的平均工資

select avg(sal) from emp where job='程序員';

1

最大值max

查詢3號部門的最高工資

select max(sal) from emp where deptno=3;

1

最小值min

查詢銷售的最低工資

select min(sal) from emp where job='銷售';

1

求和sum

查詢2號部門的工資總和

select sum(sal) from emp where deptno=2;

1

計數count

查詢工資高于2000的員工人數

select count(*) from emp where sal>2000;

1

查詢2號部門的人數

select count(*) from emp where deptno=2;

1

###練習題

查詢員工表中工資高于2000的員工姓名和工資,按照工資升序排序,查詢第二頁的2條數據

select ename,sal from emp where sal>2000 order by sal limit 2,2;

1

查詢和銷售相關的工作的工資總和

select sum(sal) from emp where job like '%銷售%'; 6625

1

查詢程序員人數

select count(*) from emp where job='程序員'; 2

1

查詢1號部門中有領導的員工中的最高工資

select max(sal) from emp where deptno=1 and mgr is not null;

1

查詢2號部門的最高工資和最低工資 起別名

select max(sal) 最高工資,min(sal) 最低工資 from emp where deptno=2;

1

查詢1號部門里面名字中包含空字的員工姓名

select ename from emp where deptno=1 and ename like '%空%';

1

###分組查詢

題目需求中每個xx 就以xx作為分組的字段

查詢員工表的平均工資

select avg(sal) from emp;

1

查詢1號部門的平均工資

select avg(sal) from emp where deptno=1;

1

查詢每個部門的平均工資

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

1

查詢每個工作的最高工資

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

1

查詢每個部門的人數

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

1

查詢每種工作的工資總和

select job,sum(sal) from emp group by job;

1

查詢每個部門工資高于1000塊錢的員工人數

select deptno,count(*) from emp where sal>1000 group by deptno;

1

查詢1號和2號部門的最高工資

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

1

查詢1號和2號部門中每種工作的工資總和

select job,sum(sal) from emp where deptno in(1,2) group by job;

1

###having

where后面只能寫普通字段條件,聚合函數不能寫在where后面

having后面專門寫聚合函數的條件,而且是和分組查詢結合使用

各個關鍵字的順序: select …from 表名 where 普通字段條件 group by 分組字段名 having 聚合函數條件 order by 排序字段名 limit …;

查詢每個部門的平均工資,只查詢平均工資高于2000的信息

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

1

查詢每種工作的人數,只查詢人數為1的信息

select job,count(*) from emp group by job having count(*)=1;

1

查詢每個部門的平均工資,只查詢工資在1000到3000之間的,并且過濾掉平均工資低于2000的信息.

select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000;

1

###綜合練習

查詢沒有上級領導的員工編號empno，姓名，工資

select empno,ename,sal from emp where mgr is null;

查詢有獎金的員工姓名和獎金

select ename,comm from emp where comm>0;

查詢名字中包含精的員工姓名和工資

select ename,sal from emp where ename like '%精%';

查詢名字中第二個字是八的員工信息

select * from emp where ename like '_八%';

查詢1號部門工資大于2000的員工信息

select * from emp where deptno=1 and sal>2000;

查詢2號部門或者工資低于1500的員工信息

select * from emp where deptno=2 or sal<1500;

查詢工資為3000，1500，5000的員工信息按照工資升序排序

select * from emp where sal in(3000,1500,5000) order by sal;

查詢3號部門的工資總和

select sum(sal) from emp where deptno=3;

查詢每個部門工資大于1000的員工人數，按照人數升序排序

select deptno,count() from emp where sal>1000 group by deptno order by count();

查詢每種工作中有領導的員工人數按照人數降序排序

select job,count() from emp where mgr is not null group by job order by count() desc;

查詢所有員工信息，按照部門編號升序排序，如果部門編號一致則工資降序

select * from emp order by deptno,sal desc;

查詢有領導的員工，每個部門的編號和最高工資

select deptno,max(sal) from emp where mgr is not null group by deptno;

查詢有領導的員工，按照工資升序排序，第3頁的2條數據

select * from emp where mgr is not null order by sal limit 4,2;

查詢每個部門的工資總和，只查詢有上級領導的員工并且要求工資總和大于5400，最后按照工資總和降序排序,只查詢結果中的第一條數據

deptno,sum(sal) from emp where mgr is not null group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;

>【綻放吧！數據庫】有獎征文火熱進行中：https://bbs.huaweicloud.com/blogs/285617

Mysql 開發者

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。