-- 一，	創(chuàng)建數(shù)據(jù)庫SchoolDB
-- 1，刪除數(shù)據(jù)庫
	DROP DATABASE IF EXISTS SchoolDB;
-- 2，創(chuàng)建數(shù)據(jù)庫
	CREATE DATABASE SchoolDB;
-- 3，修改數(shù)據(jù)庫編碼方式和字符集排列順序
	ALTER DATABASE SchoolDB CHARACTER SET utf8 COLLATE utf8_bin;
-- 4，使用數(shù)據(jù)庫
	USE SchoolDB;
-- 二，	在數(shù)據(jù)庫SchoolDB中創(chuàng)建3張表
-- 1，	角色信息表（t_role）
DROP TABLE IF EXISTS t_role;
CREATE TABLE t_role(
r_id INT PRIMARY KEY AUTO_INCREMENT,   -- 角色編號
r_name_EN VARCHAR(20) NOT NULL UNIQUE,  -- 角色名（英文）
r_name_CH VARCHAR(20) NOT NULL UNIQUE,  -- 角色名（中文）
r_remark NVARCHAR(500) NOT NULL DEFAULT '男'  -- 說明簡介
);
-- 2，	班級信息表（t_class）
DROP TABLE IF EXISTS t_class;
CREATE TABLE t_class(
c_id INT PRIMARY KEY AUTO_INCREMENT,     -- 班級編號
c_classname VARCHAR(20) NOT NULL UNIQUE  -- 班級名
);
-- 3，	用戶信息表（t_user）
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
u_id INT PRIMARY KEY AUTO_INCREMENT,   -- 用戶編號
u_username VARCHAR(20) NOT NULL UNIQUE,  -- 用戶登陸名
u_password VARCHAR(20) NOT NULL,          -- 用戶登陸密碼
u_name VARCHAR(20) NOT NULL,              -- 用戶名
u_sex CHAR(2) NOT NULL DEFAULT '男',     -- 性別
u_age INT NOT NULL,                      -- 年齡
u_phoneNum VARCHAR(11) NOT NULL,         -- 電話號碼
u_address VARCHAR(50),                   -- 家庭住址
u_type INT NOT NULL DEFAULT 1,           -- 狀態(tài)是否禁用
r_id INT NOT NULL,                       -- 角色，外鍵
c_id INT,                       -- 班級，外鍵
FOREIGN KEY (r_id) REFERENCES t_role(r_id),
FOREIGN KEY (c_id) REFERENCES t_class(c_id)
);
-- 三添加數(shù)據(jù)
-- 1，添加角色信息表（t_role）數(shù)據(jù)		
INSERT INTO t_role VALUES(DEFAULT,'boss','超級管理員','可以管理所有數(shù)據(jù)操作');
INSERT INTO t_role VALUES(DEFAULT,'admin','管理員','可以管理大部分數(shù)據(jù)操作');
INSERT INTO t_role VALUES(DEFAULT,'stu','學生','說明');
INSERT INTO t_role VALUES(DEFAULT,'teacher','老師','說明');
SELECT * FROM t_role;
-- 2,添加班級信息表（t_class）數(shù)據(jù)
INSERT INTO t_class VALUES(DEFAULT,'物聯(lián)網(wǎng)201401班');
INSERT INTO t_class VALUES(DEFAULT,'物聯(lián)網(wǎng)201402班');
INSERT INTO t_class VALUES(DEFAULT,'物聯(lián)網(wǎng)201403班');
INSERT INTO t_class VALUES(DEFAULT,'物聯(lián)網(wǎng)201404班');
INSERT INTO t_class VALUES(DEFAULT,'物聯(lián)網(wǎng)201405班');
SELECT * FROM  t_class;
-- 3，添加用戶信息表（t_user）數(shù)據(jù)
INSERT INTO t_user VALUES(1001,'abc','abc','abc','男',70,110,'武漢湖北科職',1,1,NULL);
INSERT INTO t_user VALUES(1002,'admin','admin','admin','男',0,120,'武漢湖北科職',1,2,NULL);
INSERT INTO t_user VALUES(1003,'huangba','huang66ba','黃八','男',20,15122535477,'北京東城',0,3,3);
INSERT INTO t_user VALUES(1004,'jack','jklove','杰克','男',19,15011982675,'湖南長沙',1,3,4);
INSERT INTO t_user VALUES(1005,'lisi',123456,'李四','男',20,15002726555,'湖北武漢',1,3,1);
INSERT INTO t_user VALUES(1006,'tom','tomandjack','湯姆','女',20,13002766545,'湖南韶山',1,3,5);
INSERT INTO t_user VALUES(1007,'wangwu','wangwu520','王五','男',34,13645267654,'湖北黃岡',1,4,NULL);
INSERT INTO t_user VALUES(1008,'yanqi','yanyan112','燕七','女',28,13288625642,'北京海淀',1,4,NULL);
INSERT INTO t_user VALUES(1009,'zhangsan',123,'張三','女',19,13017656783,'湖北武漢',1,3,2);
INSERT INTO t_user VALUES(10010,'zhaoliu','01234567...','趙六','女',54,15524786722,'湖北黃岡',0,4,1);
SELECT * FROM  t_user;
-- 查詢用戶信息表 如下圖4所示： 要求：給字段起別名，顯示用戶信息表
SELECT u_id AS '用戶編號', 
       u_username AS '用戶名',
       u_name AS '姓名', 
       u_sex AS '性別', 
       u_age AS '年齡', 
       u_phoneNum AS '電話', 
       u_address AS '地址', 
       CASE u_type WHEN 0 THEN '禁用' ELSE '正常' END AS '狀態(tài)', 
       b.r_name_CH AS '角色名稱',
       c.c_classname AS '班級名稱'
FROM t_user a
INNER JOIN t_role b ON a.r_id = b.r_id
LEFT JOIN t_class c ON a.c_id = c.c_id;
-- 四，完成如下查詢題目
-- 3、按照下列需求完成所有條件查詢：
-- -------- 1，過濾重復記錄(distinct) -------------- 
-- 過濾用戶表重復的地址
SELECT DISTINCT u_address 家庭住址 FROM t_user;
-- 過濾用戶表重復的年齡
SELECT DISTINCT u_age 年齡 FROM t_user
-- --- 2，顯示若干條(top)---------------
-- 顯示班級表前3條記錄
SELECT * FROM t_class LIMIT 3;
-- 顯示用戶表前5條記錄
SELECT * FROM t_user LIMIT 5;
-- --- 3，簡單條件查詢(where)---------------
-- 顯示用戶表所有"男"性的信息
SELECT * FROM t_user WHERE u_sex = '男';
-- 顯示用戶表中是 學生的信息
SELECT * FROM t_user WHERE u_type = 1;
-- 顯示用戶表中年齡在24到60歲之間的人的信息(提示:between and)
SELECT * FROM t_user WHERE u_age BETWEEN 24 AND 60;
-- 顯示用戶表中年齡不在24到60歲之間的人的信息
SELECT * FROM t_user WHERE u_age NOT BETWEEN 24 AND 60;
-- 顯示角色表中 角色名(中文)為"管理員'、"學生"、"老師"的信息
SELECT * FROM t_role WHERE r_name_CH IN ('管理員', '學生', '老師');
-- 顯示角色表中 角色名(中文)為"超級管理員"的信息
SELECT * FROM t_role WHERE r_name_CH = '超級管理員';
-- 顯示用戶表 班級為空的信息
SELECT * FROM t_user WHERE c_id IS NULL;
-- 顯示用戶表 班級不為空的信息
SELECT * FROM t_user WHERE c_id IS NOT NULL;
-- 4，模糊杳詢(like /not like)
-- 查詢用戶表 電話號碼是以15開頭的所有人信息
SELECT * FROM t_user WHERE u_phoneNum LIKE '15%';
-- 查詢用戶表 地址是湖北或湖南的所有人信息
SELECT * FROM t_user WHERE u_address LIKE '%湖北%' OR u_address LIKE '%湖南%';
-- 查詢用戶表 地址除湖北或湖南之外的所有人信息
SELECT * FROM t_user WHERE u_address NOT LIKE '%湖北%' AND u_address NOT LIKE '%湖南%';
-- -----5，杳詢排序(order by)
-- 對用戶表 用戶年齡升序排序
SELECT * FROM t_user ORDER BY u_age ASC;
-- 對用戶表 用戶年齡降序排序
SELECT * FROM t_user ORDER BY u_age DESC;
-- -----6，對查詢結果進行計算(求和sum 求平均數(shù)avg統(tǒng)計行數(shù)count)---------------
-- 求用戶表用戶年齡的總和
SELECT SUM(u_age) 年齡的總和 FROM t_user;
-- 求用戶表 用戶年齡的平均數(shù)
SELECT AVG(u_age) 年齡的平均數(shù) FROM t_user;
-- 求用戶表 有多少個用戶
SELECT COUNT(*) 用戶人數(shù) FROM t_user;
-- 求用戶表 有多少個老師
SELECT COUNT(*) 老師人數(shù) FROM t_user WHERE r_id=4;
-- ---7，分組(group by/having)	------	
-- 求各個角色的人數(shù)
SELECT r_name_CH 角色名（中文）,COUNT(*) 人數(shù) FROM t_user INNER JOIN t_role ON t_user.r_id = t_role.r_id GROUP BY t_role.r_id;
-- 求各個角色中 所有人總年齡超過100的角色有哪些
SELECT r_name_CH 角色名（中文） FROM t_role WHERE r_id IN (SELECT DISTINCT r_id FROM t_user GROUP BY r_id HAVING SUM(u_age) > 100);
-- 4、按照下列需求完成所有鏈接查詢：
-- ---8，聯(lián)接查詢----
-- (1)內連接(inner join)
-- 查詢所有用戶信息顯示用戶信息及對應的角色名(中文)
SELECT t_user.*,r_name_CH 角色名（中文） FROM t_user INNER JOIN t_role ON t_user.r_id = t_role.r_id;
-- 查詢所有用戶信息顯示用戶信息及對應的班級名稱
SELECT t_user.*,c_classname 班級名稱 FROM t_user INNER JOIN t_class ON t_user.c_id = t_class.c_id;
-- 查詢所有用戶信息顯示用戶信息及對應的角色名(中文)和班級名稱
SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班級名稱
FROM t_user
INNER JOIN t_role ON t_user.r_id = t_role.r_id
INNER JOIN t_class ON t_user.c_id = t_class.c_id;
-- 查詢所有"男，用戶并且年齡在10到24的信息顯示用戶信息及對應的角色名(中文)和班級名稱
SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班級名稱
FROM t_user
INNER JOIN t_role ON t_user.r_id = t_role.r_id
INNER JOIN t_class ON t_user.c_id = t_class.c_id
WHERE u_sex= '男' AND u_age BETWEEN 10 AND 24;
-- (2)內聯(lián)接的where寫法
-- 查詢所有"男'用戶并且年齡在10到24的信息顯示用戶信息及對應的角色名(中文)和班級名稱
SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班級名稱
FROM t_user
INNER JOIN t_role ON t_user.r_id = t_role.r_id
INNER JOIN t_class ON t_user.c_id = t_class.c_id
WHERE u_sex= '男' AND u_age BETWEEN 10 AND 24;
-- (3)外聯(lián)接(left/right [outer] join)
-- 查詢所有用戶信息顯示用戶信息及對應的角色名(中文)和班級名稱，以主表為主
SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班級名稱
FROM t_user 
LEFT JOIN t_role ON t_user.r_id = t_role.r_id 
LEFT JOIN t_class ON t_user.c_id = t_class.c_id;
-- 查詢所有用戶信息顯示用戶信息及對應的角色名(中文)和班級名稱，以副表為主
SELECT t_user.*,r_name_CH,c_classname
FROM t_role 
RIGHT JOIN t_user ON t_user.r_id = t_role.r_id 
LEFT JOIN t_class ON t_user.c_id = t_class.c_id;