Mysql查询

最新推荐文章于 2024-05-31 14:43:45 发布
最新推荐文章于 2024-05-31 14:43:45 发布
阅读量518 收藏
点赞数
分类专栏： JAVA技术栈 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinian_sinian/article/details/132341785
版权

第三章：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 NULL

SELECT 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_id
															FROM employees
															WHERE employees.last_name='Zlotkey'
															);

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT employees.employee_id,employees.last_name,employees.salary
FROM employees
WHERE employees.salary>(SELECT AVG(employees.salary) avgsal
												FROM employees
												);

#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name,job_id, salary

SELECT employees.last_name,employees.job_id,employees.salary
FROM employees
WHERE employees.salary> all (
												SELECT employees.salary
												FROM employees
												WHERE employees.job_id='SA_MAN'
												);

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employees.employee_id,employees.last_name
FROM employees
WHERE employees.last_name IN(
														SELECT employees.last_name
														FROM employees
														WHERE 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_id
															FROM departments
															WHERE 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, salary

SELECT 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) depavgsal
			FROM employees
			WHERE employees.salary IS NOT null
			GROUP BY employees.department_id
) depavgsals
WHERE depavgsals.depavgsal=(SELECT MIN(depavgsals.depavgsal)
FROM (
			SELECT employees.department_id,AVG(employees.salary) depavgsal
			FROM employees
			WHERE employees.salary IS NOT null
			GROUP 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) depavgsal
			FROM employees
			WHERE employees.salary IS NOT null
			GROUP BY employees.department_id
) depavgsals
WHERE depavgsals.depavgsal=(SELECT MIN(depavgsals.depavgsal)
FROM (
			SELECT employees.department_id,AVG(employees.salary) depavgsal
			FROM employees
			WHERE employees.salary IS NOT null
			GROUP 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_info

ALTER 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_info


CREATE 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 employees

ALTER TABLE employees
CHANGE birth birthday DATE

ALTER TABLE employees
MODIFY sex  CHAR(1)

ALTER TABLE employees
DROP note

ALTER 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=3

DELETE 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 employees

SELECT * FROM employee_vu

DESC 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 emp2

ALTER TABLE dept2
MODIFY id INT PRIMARY KEY

ALTER 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.dept02

ALTER TABLE test01_office.dept02
MODIFY department_id INT PRIMARY KEY

DESC 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))
BEGIN
			SELECT 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 employees

DELIMITER //
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，返回二者之和
sinian_四年
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql 查询根节点下所有叶子节点
06-19
1、SELECT * FROM ( SELECT d.id, ( SELECT count( 1 ) FROM t_tree dd WHERE dd.parent_uuid = d.id ) AS count FROM t_tree d ) a ... t3.id NOT IN ( SELECT t1.id FROM t_tree t1, t_tree t2 WHERE ...
mysql查询排名
lovebosom的博客
08-24 3877
在MySQL中，查询排名功能。
MySQL查询本月数据
瓜瓜嘟嘟
06-09 6041
函数返回的是当前服务器系统时间。函数获取当前日期的年份和月份，然后与数据表中的日期进行比较，从而筛选出本月的数据。函数将当前时间从 UTC+0 时区转换为 UTC+ 时区。可以根据实际情况进行调整。函数则用于获取当前月份的最后一天。这样就可以筛选出当前月份的所有数据。需要注意的是，以上 SQL 语句中的。函数用于获取当前月份的第一天，运算符来限定日期范围，其中。表示数据表中存储日期的列，等则表示需要查询的其他列。这个 SQL 语句中使用了。这个 SQL 语句中使用了。这个 SQL 语句中使用。
mysql查询_MySQL常用查询
weixin_32001071的博客
01-18 6603
单表查询①查询所有 *mysql> select * from student;②查询选中字段记录mysql> select s_name from student;③条件查询 wheremysql> select s_name from student where s_id<5;④查询后为字段重命名 asmysql> select s_...
MySQL查询的方法_MYSQL 查询方法
热门推荐
weixin_42352842的博客
02-02 1万+
mysql表格查询方法：查询：1.简单查询select * from Info --查所有数据select Code,Name from Info --查指定列的数据select Code as ‘代号‘,Name as ‘姓名‘ from Info --给列指定别名2.条件查询select * from Info where Code=‘p001‘select * from Info wher...
MySQL查询本年数据
瓜瓜嘟嘟
06-09 4174
需要注意的是，以上 SQL 语句使用的是系统默认的时区。如果需要查询的数据是依据不同时区的日期和时间存储的，可以使用 CONVERT_TZ 函数将其转换为系统默认时区的日期和时间后再进行查询。查询 MySQL 中的本年数据，可以使用 YEAR 函数来获取日期中的年份，然后使用 WHERE 子句来筛选该年的数据。子句筛选出该年的数据，就可以查询本年的数据了。函数获取当前日期的年份作为参数传递给。函数，即可获得当前所在的年份。函数可以返回日期中的年份，使用。在这个 SQL 语句中，
MySQL查询语法汇总
09-08
主要介绍了MySQL查询语法的汇总，帮助大家更好的理解和学习mysql，感兴趣的朋友可以了解下
shell简单处理mysql查询结果的方法
09-09
今天小编就为大家分享一篇shell简单处理mysql查询结果的方法，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
最全的mysql查询语句整理
09-10
主要介绍了最全的mysql查询语句整理,需要的朋友可以参考下
Mysql查询优化
06-04
Mysql查询优化，查询优化器，子查询，分页查询1)在执行计划1中，哪张表是驱动表？ 表的连接顺序是怎样的？每一步表的扫描类型是什么？ 2)在执行计划2中，表的执行顺序是怎样的？每一步表的扫描类型是什么？ 3)在...
mysql 数组查询_MySQL查询使用数组
weixin_39731107的博客
01-18 7851
我正在尝试使用数组查询MySQL数据库，但是遇到了麻烦！我有一个名为客户的表，我希望能够从“扇区”列等于$ sectorlink的所有行中选择“名称”。然后，我想将所有名称放入数组，以便执行下一个查询：从另一个表中选择所有行，这些表的“client”列等于从第一个查询返回的名称之一。我做错了，因为它返回了致命的SQL错误。我对所有变量感到困惑！$ sectorlink和$ connection是在...
MySqL查询更新
进阶之路
06-11 5770
对sql语句的查询更新
mysql大表的深度分页慢sql案例(跳页分页)-2
最新发布
s2008100262
05-31 189
深度分页往往会带来全表扫描查询慢的问题，我们一定要分析执行计划，要么利用连续分页特性解决问题、要么利用索引扫描来解决问题。
【MySQL】索引
cefler的博客
05-31 539
【MySQL】索引
【Java面试】四、MySQL篇（上）
llg___的博客
05-27 943
如果去维护一个类似二叉树的结构，再查age=45的数据，则直接从根节点开始⇒ 45 > 36，去右侧 ⇒ 45 < 48 ⇒去左侧 ⇒ 查找完毕，如此，查找效率提升，这即索引的思想。是覆盖索引，虽然select * ，但其where是根据id过滤的，即用的是主键索引、聚簇索引，索引的叶子节点存了整行数据，需要返回的字段，在索引中能够全部找到。给name字段加了非聚簇索引，因此，执行如上SQL，先根据name的非聚簇索引的B+树 ⇒ A小于L，走左边，到G和J，再走左边，找到Arm ⇒ 因为是。
mysql存储地理信息的方法
m0_72958694的博客
05-27 470
mysql存储地理信息的方法详细教程。
MySQL导入SQL脚本---超详细介绍
qq_38382365的博客
05-27 261
3.执行mysql -uroot -p123456 --default-character-set=utf8命令。需要注意的是--default-character-set=utf8，要不然可能会有报错。2.在mysql安装目录下cmd进入小黑窗。1.新建xxx数据库，字符集选对。执行完上面的命令等待结束就可以了。
java医院管理系统源码(springboot+vue+mysql)
sinat_26552841的博客
05-28 405
的主要使用者分为管理员功能，个人中心，医生管理，护士管理，病床类别管理，药品类别管理，科室信息管理，内部论坛管理，系统管理。医生功能，个人中心，病床管理，药房管理，病人信息管理，药方信息管理。护士功能修改个人中心，查看病床管理，查看药房管理，查看病人信息管理，查看药方信息管理。💕💕作者：风歌（风定落花生，歌声逐流水的风歌）💕💕个人简介：混迹在java圈十年有余，擅长Java、微信小程序、Python、Android等，大家有这一块的问题可以一起交流！💕💕各类成品java毕设。
MySQL 内存缓冲池 InnoDB Buffer Pool Sizes 深度解析
良月柒
05-27 716
InnoDB Buffer Pool是InnoDB存储引擎用于缓存表和索引数据的主要内存结构。它的主要功能是将经常访问的数据页保存在内存中，从而减少磁盘I/O操作，提高数据库的读写性能。InnoDB Buffer Pool是MySQL中提升性能的关键组件之一。合理配置和优化Buffer Pool大小及实例数量，可以显著提高数据库的读写性能。在实际应用中，应根据系统的内存资源、数据量和并发访问情况，动态调整Buffer Pool配置，并通过监控工具持续优化数据库性能。
mysql 查询
07-15
下面是一个简单的 MySQL 查询示例： 查询表中所有数据： ``` SELECT * FROM table_name; ``` 查询表中指定列的数据： ``` SELECT column1, column2, ... FROM table_name; ``` 根据条件查询表中数据： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值