devbox
小蓝xlanll
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL之增删改查基础，多例子_数据库增删改查语句基础例题

作者：小蓝xlanll | 2024-04-10 08:49:35

赞

踩

数据库增删改查语句基础例题

目录

1. 基本的SELECT语句

1.1. SELECT...

1.2. SELECT ... FROM

1.3. 列的别名:以在列名和别名之间加入关键字AS，别名可以使用双引号，AS关键字可以省略(建议不省略，增加可读性)

1.4. 在SELECT语句中使用关键字DISTINCT去除重复行

1.5. 运算符

1.6. 排序和分页 ORDER BY ,LIMIT

2. 多表查询

2.1多表查询的分类

3. 函数

3.1 聚合函数

3.2. 流程控制

4. 子查询

5. 增删改操作 INSERT DELETE/DROP UPDATE

5.1. 添加数据

5.2. 更新数据UPDATE ... SET...WHERE...

5.3. 删除数据 DELETE FROM...WHERE...

6. view 视图就是虚拟表 自身不具备数据

SQL语言在功能上主要分为如下3大类： DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索 引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。 主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记 录，并检查数据完整性。 主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和 安全级别。 主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

创建数据库

  1. CREATE DATABASE test1 CHARACTER SET 'utf8';
  2. OR
  3. CREATE DATABASE  IF NOT EXISTS  test2 CHARACTER SET 'utf8';

如何创建数据表

USE test2; #任何操作前先选择对应的库

创建表

  1. CREATE TABLE IF NOT EXISTS myempl(
  2. id INT,
  3. emp_name VARCHAR(18),
  4. hire_date DATE
  5. );

基于现有表去创建并且复制表记录

  1. CREATE TABLE empl1
  2. AS
  3. SELECT * FROM employees;
修改表 ---->ALTER TABLE 
  1. # 添加一个字段
  2. ALTER TABLE empl1 ADD salary(10,2);#默认添加到最后一个字段的位置
COMMIT / ROLLBACK
  • COMMIT 提交数据，一旦提交数据就被永久的保存在了数据库中，不可逆转
  • ROLLBACK 回滚数据,可以将数据回滚到上一次COMMIT之后

举例核心表介绍

  • employees 员工信息表

  • departments 部门信息表

  • locations 地址信息表

1. 基本的SELECT语句

1.1. SELECT...

  1. SELECT 1; #没有任何子句 
  2. SELECT 9/2; #没有任何子句

1.2. SELECT ... FROM

  1. SELECT 选择列 FROM table_name #从表中读取某些列
  2. SELECT * FROM table_name;  ##从表中读取全部列

1.3. 列的别名:以在列名和别名之间加入关键字AS，别名可以使用双引号，AS关键字可以省略(建议不省略，增加可读性)

SELECT last_name AS name, commission_pct comm FROM employees;

1.4. 在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT last_name AS name, commission_pct comm FROM employees;

1.5. 运算符

算数运算符 ：+ - * / % 
  1. SELECT 100 AS "加",100+50 AS "减",100*10 AS "乘法",100/10 AS "除法",100%10 AS "模" FROM DUAL; # DUAL是虚表的意思 % = MOD  = DIV
  2.  
  3. # 例：查询员工ID为偶数的员工信息
  4.  
  5. SELECT employees.employee_id,employees.last_name,employees.department_id 
  6. FROM employees WHERE employees.department_id % 2 = 0;
基础比较运算符 = ，<=> ,!= , < , <= , > , >= 
  1. 例：查询员工提成系数为空的员工薪资信息
  2.  
  3. SELECT last_name,salary,commission_pct FROM employees
  4. WHERE commission_pct = NULL; #查询为0条记录 NULL的比较都为NULL
  5. OR
  6. SELECT last_name,salary,commission_pct FROM employees
  7. WHERE commission_pct IS NULL;
  8. OR
  9. SELECT last_name,salary,commission_pct FROM employees
  10. WHERE commission_pct IS NOT NULL;
  11. OR
  12. SELECT last_name,salary,commission_pct FROM employees
  13. WHERE commission_pct <=> NULL;
LEAST(value1,value2,...) 返回最小的值
GREATEST(value1,value2,...) 返回最大的值 
SELECT LEAST(1,1,2,3),GREATEST(1,1,3,4);
BETWEEN AND 满足条件1 和 条件 2 之间的 
  1. #例：查询员工工资在6000-8000的工资
  2.  
  3. SELECT employee_id,last_name,salary FROM employees
  4. WHERE salary BETWEEN 6000 AND 8000;
  5. OR
  6. SELECT employee_id,last_name,salary FROM employees
  7. WHERE salary >= 6000 AND salary <= 8000;
  8.  
  9. #相反
  10.  
  11. SELECT employee_id,last_name,salary FROM employees
  12. WHERE salary NOT BETWEEN 6000 AND 8000;
  13. OR
  14. SELECT employee_id,last_name,salary FROM employees
  15. WHERE salary < 6000 OR salary > 8000;
IN , NOT IN 离散值之间的查询 
  1. 例：查询部门号为10，20，30部门的员工信息
  2.  
  3. SELECT employee_id,last_name,salary,department_id FROM
  4. employees WHERE department_id IN(10,20,30);
  5. OR
  6. SELECT employee_id,last_name,salary,department_id FROM
  7. employees WHERE department_id =10 OR department_id =20 OR department_id=30;
  8.  
  9. #反之
  10.  
  11. SELECT employee_id,last_name,salary,department_id FROM
  12. employees WHERE department_id NOT IN(10,20,30);
  13. OR
  14. SELECT employee_id,last_name,salary,department_id FROM
  15. employees WHERE department_id !=10 AND department_id !=20 AND department_id != 30;
LIKE 模糊查询
  1. % 不确定的个数字符
  2. _ 代表一个不确定字符
  3. ...
  4. ...
  1. 例：查询名字包含字母a的员工
  2.  
  3. SELECT last_name FROM employees WHERE last_name LIKE '%a%';
  4.  
  5. 例：查询名字包含字母a和e的员工
  6.  
  7. SELECT last_name FROM employees WHERE last_name LIKE '%a%' 
  8. AND last_name LIKE '%e%';
  9.  
  10. SELECT last_name FROM employees WHERE last_name LIKE '%a%e%' 
  11. OR last_name LIKE '%e%a%';

------------ 练习------------

  1. # 工资不在5000 和 12000之间的员工
  2.  
  3. SELECT last_name,salary FROM employees
  4. WHERE salary NOT BETWEEN 5000 AND 12000;
  5. OR
  6. SELECT last_name,salary FROM employees
  7. WHERE salary <5000 OR salary >12000;
  8.  
  9. # 选择在20或者50号部门工作的员工姓名和部门号
  10.  
  11. SELECT last_name,department_id FROM employees
  12. WHERE department_id = 20 OR department_id = 50;
  13. OR
  14. SELECT last_name,department_id FROM employees
  15. WHERE department_id IN (20,50);
  16.  
  17. # 选择公司没有管理者的员工姓名以及job_id
  18.  
  19. SELECT last_name,job_id,manager_id FROM employees
  20. WHERE manager_id IS NULL;
  21. OR
  22. SELECT last_name,job_id,manager_id FROM employees
  23. WHERE  ISNULL(manager_id);
  24. OR
  25. SELECT last_name,job_id,manager_id FROM employees
  26. WHERE manager_id <=> NULL;
  27.  
  28. # 选择公司中有奖金的员工姓名，工资和奖金级别
  29.  
  30. SELECT last_name,salary,commission_pct FROM employees
  31. WHERE commission_pct IS NOT NULL;
  32. OR
  33. SELECT last_name,salary,commission_pct FROM employees
  34. WHERE NOT commission_pct <=> NULL;
  35.  
  36. # 选择员工姓名的第三个字母是a或者k的员工姓名
  37.  
  38. SELECT last_name FROM employees
  39. WHERE last_name LIKE '__a%' OR last_name LIKE '__k%';

1.6. 排序和分页 ORDER BY ,LIMIT

ORDER BY
  • ASC 升序 默认升序
  1. # 按照salary排序显示
  2.  
  3. SELECT employee_id,last_name,salary FROM employees
  4. ORDER BY salary ASC;
  • DESC 降序
  1. # 按照salary排序显示
  2.  
  3. SELECT employee_id,last_name,salary FROM employees
  4. ORDER BY salary DESC;
  5.  
  6. ## note：SELECT * FROM employees; #默认是记录添加的顺序显示
  • 使用列的别名排序
  1. SELECT employee_id,salary,salary*12 AS annual_sal FROM employees
  2. ORDER BY annual_sal DESC;
  3.  
  4. SELECT employee_id,salary,salary*12 AS annual_sal FROM employees
  5. WHERE annual_sal >0; ##错误
  6. #解释：代码执行逻辑出错
  7. #执行逻辑：先执行FROM employees再执行WHERE annual_sal >0最后再SELECT employee_id,salary,salary*12 AS annual_sal 所以别名再没有创建之前就被使用，所以报错
  8. #总节：代码是先执行 找表操作(FROM) 再进行过滤操作(WHERE) 再是取字段(SELECT) 最后排序(ORDER BY)
  • 二级排序.多级排序后续加字段就可
  1. # 显示员工信息，按照department_id降序排列，相同的department_id数据再按照升序排列
  2.  
  3. SELECT department_id,last_name,salary FROM employees
  4. ORDER BY department_id DESC,salary ASC;
分页 LIMIT 
  1. # 每页显示20条数据，显示第一页
  2.  
  3. SELECT employee_id,last_name,salary FROM employees
  4. LIMIT 0,20;
  5.  
  6. # 每页显示20条数据，显示第二页
  7.  
  8. SELECT employee_id,last_name,salary FROM employees
  9. LIMIT 20,20;#（想显示的页数-1）*每页显示条数，每页显示条数
扩展执行顺序 先FROM-->WHERE-->SELECT-->ORDER BY-->LIMIT 
  1. # 例：
  2. SELECT employee_id,last_name,salary 
  3. FROM employees
  4. WHERE salary > 5000 
  5. ORDER BY salary DESC 
  6. LIMIT 0,10;
  7.  
  8. # 例：显示第5条数据和第6条数据
  9.  
  10. SELECT employee_id,last_name,salary 
  11. FROM employees
  12. WHERE salary > 5000
  13. LIMIT 4,2;
  14.  
  15. # MYSQL8.0新特性 LIMIT...OFFSET...
  16.  
  17. SELECT employee_id,last_name,salary 
  18. FROM employees
  19. WHERE salary > 5000
  20. LIMIT 2 OFFSET 4;
  21.  
  22. # 例：查询最高工资的员工信息
  23.  
  24. SELECT employee_id,last_name,salary 
  25. FROM employees
  26. ORDER BY salary DESC
  27. LIMIT 1;

2. 多表查询

  1. # 例：查询员工"Able"在那个城市工作
  2.  
  3. SELECT t1.last_name,t1.department_id,t2.department_name,t2.location_id,t3.city
  4. FROM employees AS t1,departments AS t2,locations AS t3 
  5. WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id
  6. AND t1.last_name ='Abel';
  7. OR
  8. SELECT t1.last_name,t1.department_id,t2.department_name,t2.location_id,t3.city
  9. FROM employees AS t1 
  10. JOIN departments AS t2 ON t1.department_id = t2.department_id
  11. JOIN locations AS t3 ON t2.location_id = t3.location_id
  12. WHERE t1.last_name = 'Abel';
  13.  
  14. # 子查询方式
  15.  
  16. SELECT * FROM locations
  17. WHERE location_id =(
  18. SELECT location_id FROM departments
  19. WHERE department_id =(
  20. SELECT department_id FROM employees
  21. WHERE last_name = 'Abel')); # 注意 子查询中的 SELECT后面字段只能和上一级的相同，不能多字段

2.1多表查询的分类

  • 等值连接，非等值连接
  • 自连接，非自连接
  • 内连接，外连接
  1. #非等值连接
  2.  
  3. 例：SELECT emp.last_name,emp.salary,job.grade_level 
  4. FROM employees AS emp,job_grades AS job
  5. WHERE emp.salary BETWEEN job.lowest_sal AND job.highest_sal;
  6.  
  7. #自连接，非自连接
  8.  
  9. 例：查询员工ID，员工姓名及其管理员的ID姓名
  10.  
  11. SELECT t1.manager_id,t1.last_name,t2.department_id,t2.last_name 
  12. FROM employees AS t1,employees t2
  13. WHERE t1.manager_id = t2.employee_id;
  14.  
  15. #内连接，外连接
  16. -- 内连接只满足多表数中据键值相匹配的记录
  17. -- 外连接分为左外连接，右外连接，满外连接
  18.  
  19. 例：查询所有员工的last_name，department_name信息
  20.  
  21. SELECT * FROM employees; #107条记录
  22.  
  23. SELECT * FROM departments;#27条记录
  24.  
  25. SELECT * FROM employees AS emp INNER JOIN departments AS dep
  26. ON emp.department_id = dep.department_id; #内连接 #106条记录
  27.  
  28. SELECT * FROM employees AS emp INNER JOIN departments AS dep
  29. USING(department_id);
  30.  
  31. SELECT * FROM employees AS emp LEFT OUTER JOIN departments AS dep
  32. ON emp.department_id = dep.department_id; #左外连接 OUTER可省略 #107条记录
  33.  
  34. SELECT * FROM employees AS emp RIGHT OUTER JOIN departments AS dep
  35. ON emp.department_id = dep.department_id; #右外连接 OUTER可省略 #122条记录
  36.  
  37. SELECT * FROM employees AS emp FULL JOIN departments AS dep
  38. ON emp.department_id = dep.department_id; #满外连接 mysql不支持
  39.  
  40. #MySQL支持的满外连接
  41. #UNION合并查询
  42.  -- UNION 去重 UNION ALL 没去重
  43. #自然连接 NATURAL JOIN

-------- 练习多表查询-------

  1. SELECT * FROM employees;
  2. SELECT * FROM departments;
  3. SELECT * FROM locations;
  4.  
  5. # 例：显示所有的员工姓名，部门号，部门名称
  6.  
  7. SELECT emp.last_name,emp.department_id,dep.department_name
  8. FROM employees AS emp LEFT JOIN departments AS dep
  9. ON emp.department_id = dep.department_id;
  10.  
  11. # 例：查询90号部门员工的job_id和90号部门号的location_id,city
  12.  
  13. SELECT emp.job_id,emp.department_id,loc.location_id,loc.city 
  14. FROM employees AS emp,departments AS dep,locations AS loc
  15. WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
  16. AND emp.department_id = 90;
  17. OR
  18. SELECT emp.job_id,emp.department_id,loc.location_id,loc.city 
  19. FROM employees AS emp JOIN departments AS dep
  20. ON emp.department_id = dep.department_id
  21. JOIN locations AS loc
  22. ON dep.location_id = loc.location_id
  23. AND emp.department_id = 90;
  24.  
  25. # 例：查询所有有奖金的员工的last_name,departments_name,lacation_id,city
  26.  
  27. SELECT emp.last_name,dep.department_name,loc.location_id,loc.city
  28. FROM employees AS emp,departments AS dep,locations AS loc
  29. WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
  30. AND emp.commission_pct IS NOT NULL; # 34条记录 少一条
  31.  
  32. SELECT emp.last_name,dep.department_name,loc.location_id,loc.city
  33. FROM employees AS emp
  34. LEFT JOIN departments AS dep
  35. ON emp.department_id = dep.department_id
  36. LEFT JOIN locations AS loc
  37. ON dep.location_id = loc.location_id
  38. WHERE emp.commission_pct IS NOT NULL;#35条记录 正确
  39.  
  40. # 例：查询在city在Toronto工作的员工的last_name,job_id,departments_id,departments_name
  41.  
  42. SELECT emp.last_name,emp.job_id,dep.department_name,dep.department_name,loc.city
  43. FROM employees AS emp,departments AS dep,locations AS loc
  44. WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
  45. AND loc.city = 'Toronto';

例：查询指定员工姓名，员工号，以及他的管理者姓名和员工号，结果显示如下

employeesEmp#managerMgr#
aaa002aaa001
  1. SELECT staf.last_name AS "employees",staf.employee_id AS "Emp#"
  2. ,mgr.last_name AS "manager",mgr.employee_id AS "Mgr#"
  3. FROM employees AS staf,employees AS mgr
  4. WHERE staf.manager_id = mgr.employee_id; # 106条记录 少老板的记录 内连接
  5.  
  6. SELECT staf.last_name AS "employees",staf.employee_id AS "Emp#"
  7. ,mgr.last_name AS "manager",mgr.employee_id AS "Mgr#"
  8. FROM employees AS staf 
  9. LEFT JOIN employees AS mgr
  10. ON staf.manager_id = mgr.employee_id; # 107条记录 老板没有管理者 外连接
  11.  
  12. 例：查询那些部门没有员工
  13.  
  14. SELECT dep.department_id,dep.department_name 
  15. FROM employees AS emp
  16. RIGHT JOIN departments AS dep
  17. ON dep.department_id = emp.department_id
  18. WHERE emp.department_id IS NULL;
  19.  
  20. 例：查询 部门为'Sales' 或者 'IT'的员工信息
  21.  
  22. SELECT emp.last_name,dep.department_name
  23. FROM employees AS emp
  24. JOIN departments AS dep
  25. ON emp.department_id = dep.department_id
  26. WHERE dep.department_name IN('Sales','IT');

3. 函数

3.1 聚合函数

常见聚合函数AVG, SUM,MAX,MIN,GROUP BY, HAVING 
  1. # 例：SELECT AVG(salary) "平均值",SUM(salary) "总和",MAX(salary) "最大值",MIN(salary) "最小值"
  2. FROM employees;
COUNT 出现的次数 
# 例：SELECT COUNT(employee_id) FROM employees;
GROUP BY 分组查询 
  1. # 例：各个部门的平均工资
  2.  
  3. SELECT AVG(salary),department_id
  4. FROM employees GROUP BY department_id;
  5.  
  6. # 例：查询各个department_id,job_id的平均工资
  7.  
  8. SELECT AVG(salary),department_id,job_id
  9. FROM employees GROUP BY department_id,job_id;
  10.  
  11. # 例：查询各个部门中最高工资比10000高的部门信息
  12.  
  13. SELECT department_id,MAX(salary) AS max_salary
  14. FROM employees
  15. GROUP BY department_id
  16. HAVING MAX(salary) > 10000; #聚合函数不能用WHERE


3.2. 流程控制

IF(expr1,expr2,expr3) 
  1. # 例：
  2. SELECT last_name,salary,IF(salary>6000,'优秀','继续加油') AS detail
  3. FROM employees;
  4.  
  5. # 例：
  6. SELECT last_name,commission_pct,IF(commission_pct IS NULL,0,commission_pct)
  7. FROM employees;
  8.  
  9. # 例：
  10. SELECT last_name,commission_pct,IFNULL(0,commission_pct)
  11. FROM employees;
CASE WHEN ...THEN...WHEN...THEN...ELSE...END 
  1. # 例：
  2. SELECT last_name,salary,CASE 
  3.        WHEN salary>6000 THEN 'C'
  4.        WHEN salary>10000 THEN 'B'
  5.        ELSE 'A'	
  6.        END AS detail
  7.        FROM employees; 
  8.  
  9. # 例：
  10. SELECT last_name,salary,CASE department_id
  11. 	WHEN 10 THEN
  12. 		salary * 1.1
  13. 	WHEN 20 THEN
  14.       salary * 1.2
  15. 	ELSE
  16. 		salary * 1.4
  17. END  AS "涨工资"
  18. FROM employees
  19. WHERE department_id IN(10,20);
总结 SQL语法顺序 
  1. # 总结 SQL执行顺序
  2.  
  3. SELECT...  #6
  4. FROM....  #1
  5. (LEFT/RIGHT)JOIN...ON...  #2  先ON...再 JOIN
  6. WHERE...AND/OR  #3
  7. GROUP BY...  #4
  8. HAVING...  #5
  9. ORDER BY...  #7
  10. LIMIT...  #8

4. 子查询

常规子查询 
  1. # 例：谁的工资比Abel的高？
  2. -- 方式一分两步
  3.  
  4. SELECT salary 
  5. FROM employees
  6. WHERE last_name = 'Abel';
  7.  
  8. SELECT last_name,salary
  9. FROM employees
  10. WHERE salary > 11000;
  11.  
  12. -- 方式二子查询
  13.  
  14. SELECT last_name,salary
  15. FROM employees
  16. WHERE salary > (
  17. 		SELECT salary 
  18. 		FROM employees
  19. 		WHERE last_name = 'Abel'
  20. 		); 
  21. # 例：查询工资大于149号员工工资的员工信息
  22.  
  23. SELECT last_name,salary
  24. FROM employees
  25. WHERE salary >(
  26. 	SELECT salary 
  27. 	FROM employees
  28. 	WHERE employee_id = 149
  29. );
  30.  
  31. # 例：返回job_id与141号相同并且salary比143号员工多的员工姓名,job_id,salary
  32. #讲解步骤
  33. -- 第一步拆分需求job_id与141号,salary比143号
  34.  
  35. SELECT job_id FROM employees WHERE employee_id = 141;#ST_CLERK
  36. SELECT salary FROM employees WHERE employee_id = 143;#2600
  37.  
  38. #第二步翻译题目
  39.  
  40. #翻译题目：查询job_id是ST_CLERK并且salary比2600高的员工...
  41. SELECT last_name,job_id,salary
  42. FROM employees
  43. WHERE salary >() AND job_id = ();
  44.  
  45. #第三步 填充子查询语句
  46.  
  47. SELECT last_name,job_id,salary
  48. FROM employees
  49. WHERE salary > (
  50. 	SELECT salary 
  51. 	FROM employees
  52. 	WHERE employee_id = 143 
  53. 	AND job_id = (
  54. 		SELECT job_id 
  55. 		FROM employees 
  56. 		WHERE employee_id = 141
  57. 	)
  58. );
  59.  
  60. # 例： 返回公司工资最少的员工的last_name,job_id,salary
  61.  
  62. SELECT last_name,job_id,salary
  63. FROM employees
  64. WHERE salary = (
  65. 	SELECT MIN(salary) FROM employees
  66. );
HAVING 中的子查询 
  1. # 例：查询最低工资大于110号部门最低工资的部门id和其最低工资
  2.  
  3. SELECT department_id,MIN(salary)
  4. FROM employees
  5. GROUP BY department_id
  6. HAVING MIN(salary) > (
  7. 	SELECT MIN(salary)
  8. 	FROM employees
  9. 	WHERE department_id = 110
  10. 	);
多行子查询 IN,ANY/SOME, ALL 
  1. # 例：
  2. SELECT last_name,job_id,salary
  3. FROM employees
  4. WHERE salary IN (
  5. 	SELECT MIN(salary) FROM employees GROUP BY department_id
  6. );
  7.  
  8. # 例：查询平均工资最低的部门id
  9.  
  10. SELECT AVG(salary) AS avg_salary,department_id
  11. FROM employees
  12. GROUP BY department_id
  13. ORDER BY avg_salary ASC 
  14. LIMIT 1;
  15.  
  16. SELECT MIN(avg_table.avg_salary)
  17. FROM (SELECT AVG(salary) AS avg_salary,department_id
  18. FROM employees
  19. GROUP BY department_id ) AS avg_table;
  20.  
  21. # 例：查询员工中工资大于公司平均工资的员工的last_name,salary和department_id
  22.  
  23. SELECT last_name,salary,department_id
  24. FROM employees
  25. WHERE salary > (
  26. 	SELECT AVG(salary)
  27. 	FROM employees
  28. 	);	
  29. # 例：查询员工中工资大于本部门平均工资的员工的last_name,salary和department_id
  30.  
  31. SELECT e1.last_name,e1.salary,e1.department_id
  32. FROM employees AS e1
  33. WHERE salary > (
  34. 	SELECT AVG(salary)
  35. 	FROM employees AS e2
  36. 	WHERE e2.department_id = e1.department_id
  37. 	);
  38. 	SELECT t2.last_name,t2.salary,t2.department_id
  39. 	FROM employees AS t2,(
  40. 	SELECT AVG(salary) AS avg_sa,department_id
  41. 	FROM employees
  42. 	GROUP BY department_id) AS t1
  43. 	WHERE  t1.department_id = t2.department_id AND t2.salary > t1.avg_sa;
  44.  
  45. ## 子查询 除了在 GROUP BY ,LIMIT 其他地方都可以声明子查询 ##

5. 增删改操作 INSERT DELETE/DROP UPDATE

  1. # 创建表：
  2. CREATE TABLE IF NOT EXISTS myempl(
  3. 	id INT,
  4. 	emp_name VARCHAR(18),
  5. 	hire_date DATE
  6. 	);
  7. #查看：
  8. SELECT * FROM myempl;

5.1. 添加数据

一次一条添加 
  1. 例：
  2. INSERT INTO myempl 
  3. VALUES(1,'tom','2000-12-21');
  4. OR
  5. INSERT INTO myempl(id,emp_name,hire_date)
  6. VALUES(2,'nico','2000-12-21');
一次多条 
  1. # 例：
  2. INSERT INTO myempl(id,emp_name,hire_date)
  3. VALUES(3,'fuyuhua','2000-12-21'),(4,'qinbo','2000-12-21');
将查询的结果插入到表中 
  1. # 例：
  2. INSERT INTO myempl(id,emp_name,hire_date)
  3. SELECT employee_id,last_name,hire_date
  4. FROM employees
  5. WHERE department_id IN (90,80);

5.2. 更新数据UPDATE ... SET...WHERE...

  1. SELECT * FROM myempl;
  2. # 例子：
  3. UPDATE myempl
  4. SET hire_date = CURDATE(),emp_name = 'BIGLOGO'
  5. WHERE id = 1; # 不加WHERE 会全部删除

5.3. 删除数据 DELETE FROM...WHERE...

  1. SELECT * FROM myempl;
  2. # 例子：
  3. DELETE FROM myempl WHERE id = 1;

6. view 视图就是虚拟表 自身不具备数据

创建视图 
  1. CREATE VIEW employees_view
  2. AS
  3. SELECT last_name,salary
  4. FROM employees;	 
  5.  
  6. SELECT * FROM employees_view; # 查看视图
创建视图（自带字段名） 
  1. CREATE VIEW employees_view(`name`,salary)
  2. AS
  3. SELECT last_name,salary
  4. FROM employees;
查看视图 
  1. CREATE VIEW empl_depart_view
  2. AS
  3. SELECT emp.last_name,emp.salary FROM
  4. employees AS emp,departments AS dep
  5. WHERE emp.department_id = dep.department_id;# 创建视图	
  6.  
  7. SELECT * FROM empl_depart_view;  # 查看视图

7. 存储过程与函数

语法 
  1. CREATE PROCEDURE [name] (IN | OUT | INOUT)
  2. BEGIN
  3. 方法体
  4. END;
  5.  
  6. DELIMITER $ 设置分割符号
创建存储过程 
  1. # 例：
  2. CREATE PROCEDURE selset_all_data()
  3. BEGIN
  4. 	SELECT * FROM
  5. 	employees;
  6. END;
  7. # 存储过程的调用
  8. CALL selset_all_data();
带返回值的 
  1. CREATE PROCEDURE select_out_data(OUT min_salary DOUBLE)
  2. BEGIN
  3. 	SELECT MIN(salary) INTO min_salary
  4. 	FROM employees;
  5. END;
  6.  
  7. SELECT @min_salary;	
  8. CALL select_out_data(@min_salary); # 调用
带参数的 
  1. CREATE PROCEDURE select_in_data(IN l_name VARCHAR(20))
  2. BEGIN
  3. 	SELECT salary ,last_name
  4. 	FROM employees
  5. 	WHERE last_name = l_name;
  6. END;	
  7.  
  8. CALL select_in_data('Abel');  # 调用
  • 带参数和带返回值的
  1. CREATE PROCEDURE select_in_out_data(IN dep_id INT,OUT sal DECIMAL(10,2))
  2. BEGIN
  3. 	SELECT salary INTO sal
  4. 	FROM employees
  5. 	WHERE department_id = dep_id
  6. 	LIMIT 1;
  7. END;
  8.  
  9. SET @dep_id = 90;	
  10. CALL select_in_out_data(@dep_id,@sal);
  11. SELECT @sal;
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/397690
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号