Mysql查询

第三章：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，返回二者之和