創建資料庫和表的SQL腳本
-- -----------------------------------
-- 創建db10庫、emp表并插入記錄
-- -----------------------------------
-- 洗掉db10庫(如果存在)
drop database if exists db10;
-- 重新創建db10庫
create database db10 charset utf8;
-- 選擇db10庫
use db10;
-- 洗掉員工表(如果存在)
drop table if exists emp;
-- 創建員工表
create table emp(
id int primary key auto_increment, -- 員工編號
name varchar(50), -- 員工姓名
gender char(1), -- 員工性別
birthday date, -- 員工生日
dept varchar(50), -- 所屬部門
job varchar(50), -- 所任職位
sal double, -- 薪資
bonus double -- 獎金
);
-- 往員工表中插入記錄
INSERT INTO `emp` VALUES (null, '王海濤', '男', '1995-03-25', '培優部', '講師', 1800, 400);
INSERT INTO `emp` VALUES (null, '齊雷', '男', '1994-04-06', '培優部', '講師', 2500, 700);
INSERT INTO `emp` VALUES (null, '劉沛霞', '女', '1996-06-14', '培優部', '講師', 1400, 400);
INSERT INTO `emp` VALUES (null, '陳子樞', '男', '1991-05-18', '培優部', '總監', 4500, 600);
INSERT INTO `emp` VALUES (null, '劉昱江', '男', '1993-11-18', '培優部', '講師', 2600, 600);
INSERT INTO `emp` VALUES (null, '王克晶', '女', '1998-07-18', '就業部', '講師', 3700, 700);
INSERT INTO `emp` VALUES (null, '蒼老師', '男', '1995-08-18', '就業部', '總監', 4850, 500);
INSERT INTO `emp` VALUES (null, '范傳奇', '男', '1999-09-18', '就業部', '講師', 3200, 700);
INSERT INTO `emp` VALUES (null, '劉濤', '男', '1990-10-18', '就業部', '講師', 2700, 500);
INSERT INTO `emp` VALUES (null, '韓少云', '男', '1980-12-18', NULL, 'CEO', 5000, null);
INSERT INTO `emp` VALUES (null, '董長春', '男', '1988-02-05', '培優部', '講師', 3200, 300);
INSERT INTO `emp` VALUES (null, '張久軍', '男', '1989-01-11', '培優部', '講師', 4200, 500);
-- -----------------------------------
-- 創建db20庫、dept表、emp表并插入記錄
-- -----------------------------------
-- 洗掉db20庫(如果存在)
drop database if exists db20;
-- 重新創建db20庫
create database db20 charset utf8;
-- 選擇db20庫
use db20;
-- 洗掉部門表, 如果存在
drop table if exists dept;
-- 重新創建部門表, 要求id, name欄位
create table dept(
id int primary key auto_increment, -- 部門編號
name varchar(20) -- 部門名稱
);
-- 往部門表中插入記錄
insert into dept values(null, '財務部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '銷售部');
-- 洗掉員工表, 如果存在
drop table if exists emp;
-- 創建員工表, 要求id, name, dept_id
create table emp(
id int primary key auto_increment, -- 員工編號
name varchar(20), -- 員工姓名
dept_id int -- 部門編號
-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '張三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '趙六', 4);
insert into emp values(null, '劉能', 4);
-- -----------------------------------
-- 創建db30庫、dept表、emp表并插入記錄
-- -----------------------------------
-- 洗掉db30庫(如果存在)
drop database if exists db30;
-- 重新創建db30庫
create database db30 charset utf8;
-- 選擇db30庫
use db30;
-- 洗掉部門表, 如果存在
drop table if exists dept;
-- 重新創建部門表, 要求id, name欄位
create table dept(
id int primary key auto_increment, -- 部門編號
name varchar(20) -- 部門名稱
);
-- 往部門表中插入記錄
insert into dept values(null, '財務部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '銷售部');
-- 洗掉員工表, 如果存在
drop table if exists emp;
-- 創建員工表（員工編號、員工姓名、所在部門編號）
create table emp(
id int primary key auto_increment, -- 員工編號
name varchar(20), -- 員工姓名
dept_id int -- 部門編號
);
-- 往員工表中插入記錄
insert into emp values(null, '張三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '趙六', 5);
-- -----------------------------------
-- 創建db40庫、dept表、emp表并插入記錄
-- -----------------------------------
-- 洗掉db40庫(如果存在)
drop database if exists db40;
-- 重新創建db40庫
create database db40 charset utf8;
-- 選擇db40庫
use db40;
-- 創建部門表
create table dept( -- 創建部門表
id int primary key, -- 部門編號
name varchar(50), -- 部門名稱
loc varchar(50) -- 部門位置
);
-- 創建員工表
create table emp( -- 創建員工表
id int primary key, -- 員工編號
name varchar(50), -- 員工姓名
job varchar(50), -- 職位
topid int, -- 直屬上級
hdate date, -- 受雇日期
sal int, -- 薪資
bonus int, -- 獎金
dept_id int, -- 所在部門編號
foreign key(dept_id) references dept(id)
);
-- 往部門表中插入記錄
insert into dept values ('10', '培優部', '北京');
insert into dept values ('20', '就業部', '上海');
insert into dept values ('30', '大資料部', '廣州');
insert into dept values ('40', '銷售部', '深圳');
-- 往員工表中插入記錄
insert into emp values ('1001', '王克晶', '辦事員', '1007', '1990-12-17', '800', 500, '20');
insert into emp values ('1003', '齊雷', '分析員', '1011', '1991-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海濤', '推銷員', '1011', '1991-02-22', '2450', '600', '10');
insert into emp values ('1007', '劉蒼松', '經理', '1017', '1991-04-02', '3675', 700, '20');
insert into emp values ('1009', '張慎政', '推銷員', '1011', '1991-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陳子樞', '經理', '1017', '1991-05-01', '3450', 400, '10');
insert into emp values ('1013', '張久軍', '辦事員', '1011', '1991-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖紅', '分析員', '1007', '1997-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韓少云', '董事長', null, '1991-11-17', '5000', null, null);
insert into emp values ('1019', '劉沛霞', '推銷員', '1011', '1991-09-08', '1500', 500, '10');
insert into emp values ('1021', '范傳奇', '辦事員', '1007', '1997-05-23', '1100', 1000, '20');
insert into emp values ('1023', '趙棟', '經理', '1017', '1991-12-03', '950', null, '30');
insert into emp values ('1025', '樸乾', '分析員', '1023', '1991-12-03', '3000', 600, '30');
insert into emp values ('1027', '葉尚青', '辦事員', '1023', '1992-01-23', '1300', 400, '30');
-- ------------------- 執行完畢 -----------------------
基礎查詢
15、查詢emp表中的所有員工，顯示姓名，薪資，獎金
select name,sal,ifnull(bonus,0) from emp;
+--------+------+-----------------+
| name | sal | ifnull(bonus,0) |
+--------+------+-----------------+
| 王海濤 | 1800 | 400 |
| 齊雷 | 2500 | 700 |
| 劉沛霞 | 1400 | 400 |
| 陳子樞 | 4500 | 600 |
| 劉昱江 | 2600 | 600 |
| 王克晶 | 3700 | 700 |
| 蒼老師 | 4850 | 500 |
| 范傳奇 | 3200 | 700 |
| 劉濤 | 2700 | 500 |
| 韓少云 | 5000 | 0 |
| 董長春 | 3200 | 300 |
| 張久軍 | 4200 | 500 |
+--------+------+-----------------+16、查詢emp表中的所有部門和職位
select distinct dept,job from emp;
+--------+------+
| dept | job |
+--------+------+
| 培優部 | 講師 |
| 培優部 | 總監 |
| 就業部 | 講師 |
| 就業部 | 總監 |
| NULL | CEO |
+--------+------+WHERE子句查詢
17、查詢emp表中【薪資大于3000】的所有員工，顯示員工姓名、薪資
select name,sal from emp where sal>3000;
+--------+------+
| name | sal |
+--------+------+
| 陳子樞 | 4500 |
| 王克晶 | 3700 |
| 蒼老師 | 4850 |
| 范傳奇 | 3200 |
| 韓少云 | 5000 |
| 董長春 | 3200 |
| 張久軍 | 4200 |
+--------+------+18、查詢emp表中【總薪資(薪資+獎金)大于3500】的所有員工，顯示員工姓名、總薪資
select name,ifnull(bonus,0)+sal 總薪資 from emp where ifnull(bonus,0)+sal>3500;
+--------+--------+
| name | 總薪資 |
+--------+--------+
| 陳子樞 | 5100 |
| 王克晶 | 4400 |
| 蒼老師 | 5350 |
| 范傳奇 | 3900 |
| 韓少云 | 5000 |
| 張久軍 | 4700 |
+--------+--------+19、查詢emp表中【薪資在3000和4500之間】的員工，顯示員工姓名和薪資
select name, sal from emp where sal between 3000 and 4500;
+--------+------+
| name | sal |
+--------+------+
| 陳子樞 | 4500 |
| 王克晶 | 3700 |
| 范傳奇 | 3200 |
| 董長春 | 3200 |
| 張久軍 | 4200 |
+--------+------+20、查詢emp表中【薪資為 1400、1600、1800】的員工，顯示員工姓名和薪資
select name, sal from emp where sal in(1400,1600,1800);
+--------+------+
| name | sal |
+--------+------+
| 王海濤 | 1800 |
| 劉沛霞 | 1400 |
+--------+------+21、 查詢薪資不為1400、1600、1800的員工，顯示員工姓名和薪資
select name, sal from emp where sal not in(1400,1600,1800);
+--------+------+
| name | sal |
+--------+------+
| 齊雷 | 2500 |
| 陳子樞 | 4500 |
| 劉昱江 | 2600 |
| 王克晶 | 3700 |
| 蒼老師 | 4850 |
| 范傳奇 | 3200 |
| 劉濤 | 2700 |
| 韓少云 | 5000 |
| 董長春 | 3200 |
| 張久軍 | 4200 |
+--------+------+22、查詢emp表中薪資大于4000和薪資小于2000的員工，顯示員工姓名、薪資，
select name, sal from emp where sal>4000 or sal<2000;
+--------+------+
| name | sal |
+--------+------+
| 王海濤 | 1800 |
| 劉沛霞 | 1400 |
| 陳子樞 | 4500 |
| 蒼老師 | 4850 |
| 韓少云 | 5000 |
| 張久軍 | 4200 |
+--------+------+23、查詢emp表中薪資大于3000并且獎金小于600的員工，顯示員工姓名、薪資、獎金，
select name, sal, ifnull(bonus,0) 獎金 from emp where sal>3000 and ifnull(bonus,0)<600;
+--------+------+------+
| name | sal | 獎金 |
+--------+------+------+
| 蒼老師 | 4850 | 500 |
| 韓少云 | 5000 | 0 |
| 董長春 | 3200 | 300 |
| 張久軍 | 4200 | 500 |
+--------+------+------+24、查詢沒有部門的員工（即部門列為null值）
select name,ifnull(dept,'無') 部門 from emp where dept is null;
+--------+------+
| name | 部門 |
+--------+------+
| 韓少云 | 無 |
+--------+------+25、查詢有部門的員工（即部門列不為null值）
select name,ifnull(dept,'無') 部門 from emp where dept is not null;
+--------+--------+
| name | 部門 |
+--------+--------+
| 王海濤 | 培優部 |
| 齊雷 | 培優部 |
| 劉沛霞 | 培優部 |
| 陳子樞 | 培優部 |
| 劉昱江 | 培優部 |
| 王克晶 | 就業部 |
| 蒼老師 | 就業部 |
| 范傳奇 | 就業部 |
| 劉濤 | 就業部 |
| 董長春 | 培優部 |
| 張久軍 | 培優部 |
+--------+--------+模糊查詢
%:是通配符，可以表示0個或多個任意字符
_:是通配符，只能表示1個任意字符
26、查詢emp表中姓名中以"劉"字開頭的員工，顯示員工姓名，
select name from emp where name like '劉%';
+--------+
| name |
+--------+
| 劉沛霞 |
| 劉昱江 |
| 劉濤 |
+--------+27、列出emp表中姓名以'王'開頭的員工，顯示員工姓名
select name from emp where name like '王%';
+--------+
| name |
+--------+
| 王海濤 |
| 王克晶 |
+--------+28、 列出emp表中姓名以'濤'結尾的員工，顯示員工姓名
select name from emp where name like '%濤';
+--------+
| name |
+--------+
| 王海濤 |
| 劉濤 |
+--------+27、查詢emp表中姓名中包含"濤"字的員工，顯示員工姓名，
select name from emp where name like '%濤%';
+--------+
| name |
+--------+
| 王海濤 |
| 劉濤 |
+--------+28、查詢emp表中姓名以"劉"開頭，并且姓名為兩個字的員工，顯示員工姓名，
select name from emp where name like '劉_';
+------+
| name |
+------+
| 劉濤 |
+------+多行函式查詢
多行函式也叫聚合函式（聚集函式），常見的多行函式有：
(多行函式會默認過濾null值，即不統計null值)
count(列名 | *)：
count(列名)：表示統計當前列的值有多少個
count(*):以行為單位，統計查詢結果中有多少行記錄
29、統計emp表中薪資大于3000的員工個數
select count(name) 薪資大于3000人數 from emp where sal>3000;
+------------------+
| 薪資大于3000人數 |
+------------------+
| 7 |
+------------------+30、 統計emp表中的所有的男員工的人數，
select count(name)男員工數量 from emp where gender='男';
+------------+
| 男員工數量 |
+------------+
| 10 |
+------------+31、統計每個職位的人數, 顯示職位和對應人數
select job,count(name) 人數 from emp group by job;
+------+------+
| job | 人數 |
+------+------+
| CEO | 1 |
| 總監 | 2 |
| 講師 | 9 |
+------+------+30、求emp表中的最高薪資
select max(sal) 最高薪資 from emp;
+----------+
| 最高薪資 |
+----------+
| 5000 |
+----------+31、統計emp表中所有員工的薪資總和(不包含獎金)
select sum(sal) 薪資總和 from emp;
+----------+
| 薪資總和 |
+----------+
| 39650 |
+----------+32、統計emp表員工的平均薪資(不包含獎金)
select avg(sal) 平均薪資 from emp;
+--------------------+
| 平均薪資 |
+--------------------+
| 3304.1666666666665 |
+--------------------+33、統計emp表員工的平均總薪資（包含獎金）
select avg(sal + ifnull(bonus,0)) 平均薪資 from emp;
+--------------------+
| 平均薪資 |
+--------------------+
| 3795.8333333333335 |
+--------------------+34、統計emp表中所有員工獎金的平均值
select avg(ifnull(bonus,0)) 平均獎金 from emp;
+-------------------+
| 平均獎金 |
+-------------------+
| 491.6666666666667 |
+-------------------+35、按照員工年齡從小到大排序，顯示姓名、出生年月、總薪資（薪資+獎金）
select name,birthday ,sal+ifnull(bonus,0) 總薪資 from emp order by birthday desc;
+--------+------------+--------+
| name | birthday | 總薪資 |
+--------+------------+--------+
| 范傳奇 | 1999-09-18 | 3900 |
| 王克晶 | 1998-07-18 | 4400 |
| 劉沛霞 | 1996-06-14 | 1800 |
| 蒼老師 | 1995-08-18 | 5350 |
| 王海濤 | 1995-03-25 | 2200 |
| 齊雷 | 1994-04-06 | 3200 |
| 劉昱江 | 1993-11-18 | 3200 |
| 陳子樞 | 1991-05-18 | 5100 |
| 劉濤 | 1990-10-18 | 3200 |
| 張久軍 | 1989-01-11 | 4700 |
| 董長春 | 1988-02-05 | 3500 |
| 韓少云 | 1980-12-18 | 5000 |
+--------+------------+--------+36、 查詢下個月過生日的所有員工，顯示員工姓名和出生日期
select name,birthday from emp where month(birthday)=month(curdate())+1;
+--------+------------+
| name | birthday |
+--------+------------+
| 陳子樞 | 1991-05-18 |
+--------+------------+37、 求1995年入職的員工資訊
select * from emp where year(birthday)=1995;
+----+--------+--------+------------+--------+------+------+-------+
| id | name | gender | birthday | dept | job | sal | bonus |
+----+--------+--------+------------+--------+------+------+-------+
| 1 | 王海濤 | 男 | 1995-03-25 | 培優部 | 講師 | 1800 | 400 |
| 7 | 蒼老師 | 男 | 1995-08-18 | 就業部 | 總監 | 4850 | 500 |
+----+--------+--------+------------+--------+------+------+-------+分組查詢
語法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
33、對emp表，按照部門對員工進行分組，查看分組后效果
select ifnull(dept,'無') 部門, count(*) 人數 from emp group by dept;
+--------+------+
| 部門 | 人數 |
+--------+------+
| 無 | 1 |
| 培優部 | 7 |
| 就業部 | 4 |
+--------+------+34、對emp表按照職位進行分組，并統計每個職位的人數，顯示職位和對應人數
select job,count(job) 人數 from emp group by job;
+------+------+
| job | 人數 |
+------+------+
| CEO | 1 |
| 總監 | 2 |
| 講師 | 9 |
+------+------+35、對emp表按照部門進行分組，求每個部門的最高薪資(不包含獎金)，顯示部門名稱和最高薪資
select ifnull(dept,'CEO') 部門,max(sal) 最高薪資 from emp group by dept;
+--------+----------+
| 部門 | 最高薪資 |
+--------+----------+
| CEO | 5000 |
| 培優部 | 4500 |
| 就業部 | 4850 |
+--------+----------+36、對emp表中所有員工的薪資進行升序(從低到高)排序，顯示員工姓名、薪資，
select name,sal from emp order by sal asc;
+--------+------+
| name | sal |
+--------+------+
| 劉沛霞 | 1400 |
| 王海濤 | 1800 |
| 齊雷 | 2500 |
| 劉昱江 | 2600 |
| 劉濤 | 2700 |
| 董長春 | 3200 |
| 范傳奇 | 3200 |
| 王克晶 | 3700 |
| 張久軍 | 4200 |
| 陳子樞 | 4500 |
| 蒼老師 | 4850 |
| 韓少云 | 5000 |
+--------+------+37、對emp表中所有員工的獎金進行降序(從高到低)排序，顯示員工姓名、獎金，
select name,ifnull(bonus,0) 獎金 from emp order by ifnull(bonus,0) desc;
+--------+------+
| name | 獎金 |
+--------+------+
| 齊雷 | 700 |
| 王克晶 | 700 |
| 范傳奇 | 700 |
| 陳子樞 | 600 |
| 劉昱江 | 600 |
| 張久軍 | 500 |
| 劉濤 | 500 |
| 蒼老師 | 500 |
| 劉沛霞 | 400 |
| 王海濤 | 400 |
| 董長春 | 300 |
| 韓少云 | 0 |
+--------+------+分頁查詢
在mysql中，通過limit進行分頁查詢，查詢公式為： `limit (頁碼-1)*每頁顯示記錄數, 每頁顯示記錄數`
38、查詢emp表中的所有記錄，分頁顯示：每頁顯示3條記錄，回傳所有頁的資料
select * from emp limit 0,3;
+----+--------+--------+------------+--------+------+------+-------+
| id | name | gender | birthday | dept | job | sal | bonus |
+----+--------+--------+------------+--------+------+------+-------+
| 1 | 王海濤 | 男 | 1995-03-25 | 培優部 | 講師 | 1800 | 400 |
| 2 | 齊雷 | 男 | 1994-04-06 | 培優部 | 講師 | 2500 | 700 |
| 3 | 劉沛霞 | 女 | 1996-06-14 | 培優部 | 講師 | 1400 | 400 |
+----+--------+--------+------------+--------+------+------+-------+39、求emp表中薪資最高的前3名員工的資訊，顯示姓名和薪資
--根據薪資降序排序,每頁顯示3條,查詢第一頁就是薪資最高的前三名
select name,sal from emp order by sal desc limit 0,3;
+--------+------+
| name | sal |
+--------+------+
| 韓少云 | 5000 |
| 蒼老師 | 4850 |
| 陳子樞 | 4500 |
+--------+------+
其他函式
curdate() -- 獲取當前日期: 年月日
curtime() -- 獲取當前時間: 時分秒
sysdate()/now() -- 獲取當前日期+時間, 年月日 時分秒
year('2020-8-10'): 回傳日期中的年份, 2020
month('2020-8-10'): 回傳日期中的月份, 8
day('2020-8-10'): 回傳日期中的天數, 10
hour('2020-8-10 12:34:56'): 回傳時間中的小時, 12
minute('2020-8-10 12:34:56'): 回傳時間中的分鐘數, 34
second('2020-8-10 12:34:56'): 回傳時間中的秒值, 56
------------------------------------------
concat(s1,s2,...sn): 將 s1、s2、...sn 拼接在一起回傳
例如: name('王海濤'),birthday('1995-03-25'),sal(2450)
select concat('王海濤', '1995-03-25', 2450);
concat_ws(x,s1,s2,...sn):
將 s1、s2、...sn 拼接在一起,并且每兩個拼接時會通過x作為分隔符進行拼接,再回傳
select concat_ws(',' ,'王海濤', '1995-03-25', 2450);40、查詢emp表中所有【在1993和1995年之間出生】的員工，顯示姓名、出生日期，
select name,birthday from emp where year(birthday) between 1993 and 1995;
+--------+------------+
| name | birthday |
+--------+------------+
| 王海濤 | 1995-03-25 |
| 齊雷 | 1994-04-06 |
| 劉昱江 | 1993-11-18 |
| 蒼老師 | 1995-08-18 |
+--------+------------+41、查詢emp表中本月過生日的所有員工
select name,month(birthday) 出生月份 from emp where month(birthday)= month(curdate());
+------+----------+
| name | 出生月份 |
+------+----------+
| 齊雷 | 4 |
+------+----------+42、查詢emp表中員工的姓名和薪資（薪資格式為: xxx(元) ）
select name, concat(sal,'(元)') from emp;
+--------+--------------------+
| name | concat(sal,'(元)') |
+--------+--------------------+
| 王海濤 | 1800(元) |
| 齊雷 | 2500(元) |
| 劉沛霞 | 1400(元) |
| 陳子樞 | 4500(元) |
| 劉昱江 | 2600(元) |
| 王克晶 | 3700(元) |
| 蒼老師 | 4850(元) |
| 范傳奇 | 3200(元) |
| 劉濤 | 2700(元) |
| 韓少云 | 5000(元) |
| 董長春 | 3200(元) |
| 張久軍 | 4200(元) |
+--------+--------------------+43、查詢emp表中員工的姓名和薪資（薪資格式為: xxx/元 ）
select name, concat(sal,'/元') from emp;
+--------+-------------------+
| name | concat(sal,'/元') |
+--------+-------------------+
| 王海濤 | 1800/元 |
| 齊雷 | 2500/元 |
| 劉沛霞 | 1400/元 |
| 陳子樞 | 4500/元 |
| 劉昱江 | 2600/元 |
| 王克晶 | 3700/元 |
| 蒼老師 | 4850/元 |
| 范傳奇 | 3200/元 |
| 劉濤 | 2700/元 |
| 韓少云 | 5000/元 |
| 董長春 | 3200/元 |
| 張久軍 | 4200/元 |
+--------+-------------------+多表查詢
準備資料： 以下練習將使用db30庫中的表及表記錄，請先進入db30資料庫!!!
44、
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/274466.html
標籤：其他