第三章：select 語句

SELECT  employees.employee_id,employees.department_id
FROM employees
WHERE employees.employee_id=176;
DESC departments;SELECT * FROM departments;

第四章：運算符使用

SELECT employees.last_name,employees.salary
FROM employees
WHERE employees.salary  NOT BETWEEN 5000 AND 12000;#注意：不是IS NOT BETWEEN...AND...  SELECT employees.employee_id,employees.department_id
FROM employees
WHERE employees.department_id=20 OR employees.department_id=50;#employees.department_id IN(20,50)SELECT employees.employee_id,employees.job_id
FROM employees
WHERE employees.manager_id<=>NULL;# employees.manager_id IS NULL / IS NOT NULLSELECT employees.last_name,employees.salary,job_grades.grade_level
FROM employees,job_grades
WHERE employees.salary<=job_grades.highest_sal && employees.salary>=job_grades.lowest_sal;SELECT employees.last_name
FROM employees
WHERE employees.last_name LIKE '__a%';SELECT employees.last_name
FROM employees
WHERE employees.last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';# OR 兩邊主謂賓寫完 SELECT employees.first_name
FROM employees
WHERE employees.first_name REGEXP 'e$'; #注意：WHERE first_name LIKE '%e';SELECT department_id,last_name,job_id
FROM employees
#where department_id in (80,90,100);
WHERE department_id BETWEEN 80 AND 100;SELECT employees.last_name,employees.salary*(1+IFNULL(0,employees.commission_pct)*12),employees.manager_id
FROM employees
WHERE employees.manager_id IN (100,101,110);

第五章：排序與分頁

SELECT employees.last_name,employees.salary*(1+IFNULL(0,employees.commission_pct))*12 "annual_sal "
FROM employees
ORDER BY employees.salary*(1+IFNULL(0,employees.commission_pct))*12 DESC,employees.last_name ASC;SELECT employees.last_name,employees.salary
FROM employees
WHERE employees.salary NOT BETWEEN 8000 AND 17000
ORDER BY employees.salary DESC
LIMIT 20,20;SELECT employees.last_name,employees.email,employees.department_id
FROM employees
WHERE employees.email REGEXP '[e]' #WHERE employees.email LIKE '%e%';
ORDER BY LENGTH(employees.email) DESC,employees.department_id ASC;

第六章：建表語句

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO t_dept(deptName,address) VALUES('華山','華山');
INSERT INTO t_dept(deptName,address) VALUES('丐幫','洛陽');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武當','武當山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明頂');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('風清揚',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐沖',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('喬峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('滅絕師太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張三豐',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('張無忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韋小寶',18,null,100010);

第七章：單行函數

# 1.顯示系統時間(注：日期+時間)
SELECT NOW(),CURRENT_DATE(),CURRENT_TIME()
FROM DUAL;	# 2.查詢員工號，姓名，工資，以及工資提高百分之20%后的結果（new salary）
SELECT employees.employee_id,employees.last_name,employees.salary,employees.salary*1.2 "new salary",RPAD(salary,10,' ')
FROM employees;# 3.將員工的姓名按首字母排序，并寫出姓名的長度（length）
SELECT employees.last_name,CHAR_LENGTH(employees.last_name) "name lenghth"
FROM employees
ORDER BY employees.last_name DESC;# 4.查詢員工id,last_name,salary，并作為一個列輸出，別名為OUT_PUT
SELECT CONCAT(employees.employee_id,' ',employees.last_name,' ',employees.salary) "OUT_PUT"
FROM employees;# 5.查詢公司各員工工作的年數、工作的天數，并按工作年數的降序排序
SELECT  DATEDIFF(CURRENT_DATE(),employees.hire_date) "work_days"
FROM employees
ORDER BY work_days ASC;# 6.查詢員工姓名，hire_date , department_id，滿足以下條件：雇用時間在1997年之后，department_id為80 或 90 或110, commission_pct不為空
SELECT employees.last_name,employees.hire_date,employees.department_id
FROM employees
WHERE DATE_FORMAT(employees.hire_date,'%Y')>='1997'
&& employees.department_id IN(80,90,110)
&& employees.commission_pct IS NOT NULL;# 7.查詢公司中入職超過10000天的員工姓名、入職時間
SELECT employees.last_name,employees.hire_date,DATEDIFF(CURDATE(),employees.hire_date)
FROM employees
WHERE DATEDIFF(CURDATE(),employees.hire_date)>10000;# 8.做一個查詢，產生下面的結果<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(employees.last_name,' earns ',employees.salary,' monthly but wants',employees.salary*3)
FROM employees;SELECT last_name Last_name, job_id Job_id, CASE job_id 
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F'
END "grade"
FROM employees;SELECT employees.last_name,employees.job_id,
CASE employees.job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN'  THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP'  THEN 'D'
WHEN 'ST_CLERK'THEN 'E'
ELSE 'F' END "grade"
FROM employees;

第八章：多表查詢

#1、內連接 等值連接
#SELECT DISTINCT employees.job_id,departments.location_id
#FROM employees,departments
#WHERE employees.department_id=departments.department_id;#2、外連接 非等值連接
#SELECT employees.last_name,employees.salary,job_grades.grade_level
#FROM employees,job_grades
#WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;#3、自鏈接
SELECT CONCAT(employee.last_name,' work for ',manager.last_name)
FROM employees employee,employees manager
WHERE employee.manager_id=manager.employee_id;   #4、內連接(兩個表共有部分)
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees JOIN  departments
on employees.department_id=departments.department_id
ORDER BY employees.employee_id ASC;#5、外連接 右外連接
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees RIGHT JOIN  departments
on employees.department_id=departments.department_id
ORDER BY employees.employee_id ASC;
#LIMIT 20,20;#6、外連接 左外連接
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees LEFT  JOIN  departments
on employees.department_id=departments.department_id
ORDER BY employees.employee_id ASC;
#LIMIT 20,20;#7、外連接  左連接除去共有部分
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees LEFT JOIN  departments
on employees.department_id=departments.department_id
WHERE departments.department_id IS NULL
ORDER BY employees.employee_id ASC;#排序要放在最后面#8、外連接  右連接除去共有部分
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees RIGHT  JOIN  departments
on employees.department_id=departments.department_id
WHERE employees.department_id IS NULL
ORDER BY employees.employee_id ASC;#排序要放在最后面#9、外連接  左連接并上右連接除去共有部分
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees LEFT  JOIN  departments
on employees.department_id=departments.department_id
UNION
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees RIGHT  JOIN  departments
on employees.department_id=departments.department_id
WHERE employees.department_id IS NULL;#10 除去共有部分進行連接
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees LEFT JOIN  departments
on employees.department_id=departments.department_id
WHERE departments.department_id IS NULL
UNION ALL
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees RIGHT  JOIN  departments
on employees.department_id=departments.department_id
WHERE employees.department_id IS NULL;
#ORDER BY employees.employee_id ASC;#報錯#新特性 使用natural JOIN
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees NATURAL JOIN departments;#新特性 使用using
SELECT employees.employee_id,employees.last_name,departments.department_name
FROM employees JOIN departments
USING (department_id);
-------------------------------------------------------------------------------------------------------------------------------------------------
# 1.顯示所有員工的姓名，部門號和部門名稱。
SELECT employees.employee_id,employees.department_id,departments.department_name
FROM employees JOIN departments
ON employees.department_id=departments.department_id;# 2.查詢90號部門員工的job_id和90號部門的location_id
SELECT employees.job_id,departments.location_id
FROM employees JOIN departments
ON employees.department_id=departments.department_id;# 3.選擇所有有獎金的員工的 last_name , department_name , location_id , city
SELECT employees.last_name,departments.department_name,departments.location_id,locations.city
FROM employees 
JOIN departments ON employees.department_id=departments.department_id
JOIN locations   ON departments.location_id=locations.location_id 
WHERE employees.commission_pct IS NOT NULL;# 4.選擇city在Toronto工作的員工的 last_name , job_id , department_id , department_name
SELECT employees.last_name,employees.job_id,employees.department_id,departments.department_name,locations.city
FROM employees
JOIN departments ON employees.department_id=departments.department_id
JOIN locations ON departments.location_id=locations.location_id
WHERE locations.city='Toronto';# 5.查詢員工所在的部門名稱、部門地址、姓名、工作、工資，其中員工所在部門的部門名稱為’Executive’
SELECT departments.department_name,locations.street_address,employees.last_name,employees.job_id,employees.salary
FROM employees
JOIN departments USING(department_id)
JOIN locations USING(location_id)
WHERE departments.department_name='Executive';# 5.查詢員工所在的部門名稱、部門地址、姓名、工作、工資，其中員工所在部門的部門名稱為’Executive’
SELECT departments.department_name,locations.street_address,employees.last_name,employees.job_id,employees.salary
FROM employees
NATURAL JOIN departments 
NATURAL JOIN locations
WHERE departments.department_name='Executive';# 6.選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結果類似于下面的格式
SELECT emp.employee_id Emp,emp.last_name employees,mgr.employee_id Mgr,mgr.last_name manager
FROM employees emp
JOIN employees mgr ON emp.manager_id=mgr.employee_id;SELECT departments.department_id,departments.department_name
FROM departments
NATURAL LEFT JOIN employees
WHERE employees.department_id IS NULL;# 8. 查詢哪個城市沒有部門
SELECT locations.city
FROM locations
NATURAL LEFT JOIN departments
WHERE departments.location_id IS NULL;#查詢部門名為 Sales 或 IT 的員工信息
SELECT *
FROM employees
NATURAL JOIN departments
WHERE departments.department_name IN ('Sales','IT');

第九章：子查詢

#1.查詢和Zlotkey相同部門的員工姓名和工資
SELECT employees.last_name,employees.salary
FROM employees
WHERE employees.department_id=(SELECT employees.department_idFROM employeesWHERE employees.last_name='Zlotkey');#2.查詢工資比公司平均工資高的員工的員工號，姓名和工資
SELECT employees.employee_id,employees.last_name,employees.salary
FROM employees
WHERE employees.salary>(SELECT AVG(employees.salary) avgsalFROM employees);#3.選擇工資大于所有JOB_ID = 'SA_MAN'的員工的工資的員工的last_name,job_id, salarySELECT employees.last_name,employees.job_id,employees.salary
FROM employees
WHERE employees.salary> all (SELECT employees.salaryFROM employeesWHERE employees.job_id='SA_MAN');#4.查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
SELECT employees.employee_id,employees.last_name
FROM employees
WHERE employees.last_name IN(SELECT employees.last_nameFROM employeesWHERE employees.last_name LIKE '%u%');
#5.查詢在部門的location_id為1700的部門工作的員工的員工號SELECT employees.last_name,employees.employee_id
FROM employees
WHERE employees.department_id IN (SELECT departments.department_idFROM departmentsWHERE departments.location_id=1700);#6.查詢管理者是King的員工姓名和工資
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
);#7. 查詢工資最低的員工信息: last_name, salarySELECT employees.last_name,employees.salary
FROM employees
WHERE employees.salary = (SELECT MIN(employees.salary)FROM employees);#8. 查詢平均工資最低的部門信息SELECT *
FROM departments
WHERE departments.department_id=
(
SELECT depavgsals.department_id
FROM (SELECT employees.department_id,AVG(employees.salary) depavgsalFROM employeesWHERE employees.salary IS NOT nullGROUP BY employees.department_id
) depavgsals
WHERE depavgsals.depavgsal=(SELECT MIN(depavgsals.depavgsal)
FROM (SELECT employees.department_id,AVG(employees.salary) depavgsalFROM employeesWHERE employees.salary IS NOT nullGROUP BY employees.department_id) depavgsals
)
);#9.查詢平均工資最低的部門信息和該部門的平均工資（相關子查詢）SELECT *,(SELECT AVG(salary) FROM employees WHERE employees.department_id = departments.department_id)
FROM departments 
WHERE departments.department_id=
(
SELECT depavgsals.department_id
FROM (SELECT employees.department_id,AVG(employees.salary) depavgsalFROM employeesWHERE employees.salary IS NOT nullGROUP BY employees.department_id
) depavgsals
WHERE depavgsals.depavgsal=(SELECT MIN(depavgsals.depavgsal)
FROM (SELECT employees.department_id,AVG(employees.salary) depavgsalFROM employeesWHERE employees.salary IS NOT nullGROUP BY employees.department_id) depavgsals
)
);#10. 查詢平均工資最高的 job 信息SELECT *
FROM jobs
WHERE jobs.job_id=(SELECT avgsals.job_id
FROM (
SELECT employees.job_id,AVG(employees.salary) avgsal
FROM employees
GROUP BY employees.job_id
)avgsals
WHERE avgsals.avgsal=(SELECT MAX(avgsals.avgsal)
FROM (
SELECT employees.job_id,AVG(employees.salary) avgsal
FROM employees
GROUP BY employees.job_id
)avgsals));#11. 查詢平均工資高于公司平均工資的部門有哪些?SELECT departments.department_id,departments.department_name
FROM departments
WHERE departments.department_id IN (SELECT depsals.department_id
FROM (
SELECT employees.department_id,AVG(employees.salary) depsal
FROM employees
GROUP BY employees.department_id
)depsals
WHERE depsals.depsal > (SELECT AVG(employees.salary)FROM employees));# 12. 查詢出公司中所有 manager 的詳細信息SELECT DISTINCT IFNULL(employees.manager_id,employees.employee_id)
FROM employees;SELECT *
FROM employees
WHERE employees.employee_id IN (SELECT DISTINCT IFNULL(employees.manager_id,employees.employee_id)
FROM employees);SELECT employee_id, last_name, salary
FROM employees e1
WHERE EXISTS ( SELECT *
FROM employees e2
WHERE e2.manager_id = e1.employee_id);#15. 查詢部門的部門號，其中不包括job_id是"ST_CLERK"的部門號
SELECT DISTINCT employees.department_id
FROM employees
WHERE department_id NOT IN(SELECT DISTINCT employees.department_id
FROM employees
WHERE employees.job_id='ST_CLERK');

第十章：管理與創建表

#1. 創建數據庫test01_office,指明字符集為utf8。并在此數據庫下執行下述操作
CREATE DATABASE  IF NOT EXISTS test01_office CHARACTER SET 'utf8';USE test01_office;#2. 創建表dept01
/*
字段 類型
id INT(7)
NAME VARCHAR(25)
*/CREATE TABLE dept01(
id INT(7),
NAME VARCHAR(25)
);#3. 將表departments中的數據插入新表dept02中CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.departments; #4. 創建表emp01
/*
字段 類型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT (7)
);
DESC emp01
#5. 將列last_name的長度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);#6. 根據表employees創建emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees;
desc emp02
#7. 刪除表emp01
DROP TABLE emp01;
#8. 將表emp02重命名為emp01
ALTER TABLE emp02
RENAME TO emp01  
#9.在表dept02和emp01中添加新列test_column，并檢查所作的操作
ALTER TABLE dept02
ADD test_column VARCHAR(15)#10.直接刪除表emp01中的列 department_id
DESC emp01
ALTER TABLE emp01
DROP COLUMN department_id# 1、創建數據庫 test02_market
CREATE DATABASE test02_market;
USE test02_market# 2、創建數據表 customers
CREATE TABLE customers(
c_num INT(10),
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city  VARCHAR(50),
c_birth DATE
)SHOW TABLES
FROM test02_market# 3、將 c_contact 字段移動到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth
DESC customers# 4、將 c_name 字段數據類型改為 varchar(70)
USE test02_market
ALTER TABLE customers
MODIFY c_name VARCHAR(70)
DESC customers# 5、將c_contact字段改名為c_phone6
ALTER TABLE customers
CHANGE c_contact c_phone6 VARCHAR(50)# 6、增加c_gender字段到c_name后面，數據類型為char(1)
ALTER TABLE customers
ADD c_gender VARCHAR(50) AFTER c_name
# 7、將表名改為customers_info
RENAME TABLE customers 
TO customers_info
# 8、刪除字段c_city
DESC customers_infoALTER TABLE customers_info
DROP c_city# 1、創建數據庫test03_company
# 2、創建表offices
# 3、創建表employees
# 4、將表employees的mobile字段修改到code字段后面
# 5、將表employees的birth字段改名為birthday
# 6、修改sex字段，數據類型為char(1)
# 7、刪除字段note
# 8、增加字段名favoriate_activity，數據類型為varchar(100)
# 9、將表employees的名稱修改為 employees_infoCREATE DATABASE IF NOT EXISTS test03_company
USE test03_company
CREATE TABLE IF NOT EXISTS offices(
officeCode  INT,
city  VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
)CREATE TABLE IF NOT EXISTS employees
(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT ,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
)ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER code
DESC employeesALTER TABLE employees
CHANGE birth birthday DATEALTER TABLE employees
MODIFY sex  CHAR(1)ALTER TABLE employees
DROP noteALTER TABLE employees
ADD favoriate_activity VARCHAR (100)RENAME TABLE employees
TO employees_info

第十一章：數據處理之增刪改查

#1. 創建數據庫dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8'
USE dbtest11
#2. 運行以下腳本創建表my_employees
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);
DESC my_employees
#4. 向my_employees表中插入下列數據
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
SELECT * FROM my_employees
#5. 向users表中插入數據
INSERT INTO users VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40)
SELECT * FROM users
#6. 將3號員工的last_name修改為“drelxer”
UPDATE my_employees
SET last_name='drelxer'
WHERE my_employees.id=3DELETE FROM my_employees
WHERE id=1#11. 清空表my_employees
TRUNCATE TABLE my_employees

第十二章：視圖

#1. 使用表employees創建視圖employee_vu，其中包括姓名（LAST_NAME），員工號（EMPLOYEE_ID），部門號(DEPARTMENT_ID)
#2. 顯示視圖的結構
#3. 查詢視圖中的全部內容
#4. 將視圖中的數據限定在部門號是80的范圍內CREATE OR REPLACE VIEW employee_vu
AS
SELECT employees.last_name,employees.employee_id,employees.department_id
FROM employeesSELECT * FROM employee_vuDESC employee_vu
CREATE OR REPLACE VIEW employee_vu
AS
SELECT employees.last_name,employees.employee_id,employees.department_id
FROM employees
WHERE employees.department_id=80

第十三章：約束

CREATE DATABASE test04_emp;
use test04_emp;
CREATE TABLE emp2(
id INT,
emp_name VARCHAR(15)
);
CREATE TABLE dept2(
id INT,
dept_name VARCHAR(15)
);
#1.向表emp2的id列中添加PRIMARY KEY約束
#2. 向表dept2的id列中添加PRIMARY KEY約束
#3. 向表emp2中添加列dept_id，并在其中定義FOREIGN KEY約束，與之相關聯的列是dept2表中的id列。
ALTER TABLE emp2
MODIFY id INT PRIMARY KEY
DESC emp2ALTER TABLE dept2
MODIFY id INT PRIMARY KEYALTER TABLE emp2
ADD  COLUMN dept_id INT ALTER TABLE emp2 
ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES dept2(id);# 1、創建數據庫test01_library
# 2、創建表 books，表結構如下CREATE DATABASE IF NOT EXISTS test01_library
USE test01_library
CREATE TABLE IF NOT EXISTS books(
id INT,
name VARCHAR(25),
authors VARCHAR(25),
price FLOAT,
pubdate YEAR,
nate VARCHAR(100),
num INT
)# 3、使用ALTER語句給books按如下要求增加相應的約束
#1.向表emp2的id列中添加PRIMARY KEY約束ALTER TABLE books
MODIFY id INT  PRIMARY KEY#2. 向表dept2的id列中添加PRIMARY KEY約束DESC test01_office.dept02ALTER TABLE test01_office.dept02
MODIFY department_id INT PRIMARY KEYDESC test04_emp.emp2

第十四章：存儲過程與函數

#0.準備工作
#1. 創建存儲過程insert_user(),實現傳入用戶名和密碼，插入到admin表中
CREATE DATABASE test15_pro_func;
USE test15_pro_func;
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);
#2. 創建存儲過程get_phone(),實現傳入女神編號，返回女神姓名和女神電話
CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孫燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('鄧紫棋','17843283452','1991-11-12'),
('劉若英','18635575464','1989-05-18'),
('楊超越','13761238755','1994-05-11');
SELECT * FROM beauty;DELIMITER //
CREATE PROCEDURE get_phone(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name ,b.phone INTO NAME,phone
FROM beauty b
WHERE b.id = id;
END //
DELIMITER ;CALL get_phone(1,@phone,@name)
SELECT @name,@phone#3. 創建存儲過程date_diff()，實現傳入兩個女神生日，返回日期間隔大小DELIMITER //
CREATE PROCEDURE date_diff(IN data1 DATETIME,IN data2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(data1,data2) INTO result;
END //
DELIMITER ;CALL date_diff('1997-12-1','1997-10-1',@result)
SELECT @result#4. 創建存儲過程format_date(),實現傳入一個日期，格式化成xx年xx月xx日并返回DELIMITER //
CREATE PROCEDURE format_date(IN date DATETIME,OUT strade VARCHAR(50))
BEGINSELECT DATE_FORMAT(date,'%y年%m月%d日') INTO strade;
END //
DELIMITER ;CALL format_date('1997-10-12',@strade)
SELECT @strade#5. 創建存儲過程beauty_limit()，根據傳入的起始索引和條目數，查詢女神表的記錄
DELIMITER //
CREATE PROCEDURE beauty_limit(IN startIndex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startIndex,size;
END //
DELIMITER ;CALL beauty_limit(1,3)
DROP PROCEDURE beauty_limit;
SHOW PROCEDURE STATUS LIKE 'date_diff'#0. 準備工作USE test15_pro_func;
CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
#無參有返回
#1. 創建函數get_count(),返回公司的員工個數
#有參有返回
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION get_count() RETURNS INT
BEGIN 
RETURN (SELECT COUNT(*) FROM employees);
END //
DELIMITER ;
SELECT get_count();#2. 創建函數ename_salary(),根據員工姓名，返回它的工資DESC employeesDELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(20)) RETURNS  DOUBLE
BEGIN
RETURN(
SELECT employees.salary FROM employees
WHERE employees.last_name= emp_name
);
END //
DELIMITER ;SELECT ename_salary('Abel')#3. 創建函數dept_sal() ,根據部門名，返回該部門的平均工資
#4. 創建函數add_float()，實現傳入兩個float，返回二者之和