• 数据库——创建和管理表

    目录

    1.1 一条数据存储的过程

     1.2 标识符命名规则

    1.3 MySQL中的数据类型  

    2. 创建和管理数据库  

    2.2 使用数据库

     2.3 修改数据库

    2.4删除数据库 

    3. 创建表  

    3.3 查看数据表结构

    4.2 修改一个列

    4.3 重命名一个列  

    4.4 删除一个列  

    5.-重命名表

    6. 删除表  

    7. 清空表 

    8. 内容拓展 

    数据库学习记录-----代码

    1.1 一条数据存储的过程

    • 存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只
      能是一团乱麻，无从下手。
      那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，
      一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

    我们要先创建一个数据库，而不是直接创建数据表呢？
    因为从系统架构的层次上看， MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数
    据表的 行与列 。

     1.2 标识符命名规则

    •  数据库名、表名不得超过30个字符，变量名限制为29个
    • 数据库名、表名不得超过 30 个字符，变量名限制为 29 个
    • 数据库名、表名、字段名等对象名中间不要包含空格
    • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
    • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在 SQL 语句中使 用` （着重号）引起来
    • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

    1.3 MySQL中的数据类型  

     其中，常用的几类类型介绍如下：

    2. 创建和管理数据库  

    •  方式1：创建数据库

     CREATE DATABASE 数据库名;

    • 方式2：创建数据库并指定字符集  

    CREATE DATABASE 数据库名 CHARACTER SET 字符集;  

    • 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ） 

    CREATE DATABASE IF NOT EXISTS 数据库名; 

    最好也加上字符集

    CREATE DATABASE IF NOT EXISTS 数据库名  CHARACTER SET 字符集;（ 推荐 ）

    字符集：utf8---gbk

    •  如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。
    • 注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删 旧库完成的。

     #1-2管理数据库
    #查看当前连接中的数据库都有哪些:
    SHOW DATABASES;

    2.2 使用数据库

    •  查看当前所有的数据库

    SHOW DATABASES; #有一个S，代表多个数据库 

    • 查看当前正在使用的数据库 

    SELECT DATABASE(); #使用的一个 mysql 中的全局函数  

    • 查看指定库下所有的表 

     SHOW TABLES FROM 数据库名;

    • 查看数据库的创建信息  
    SHOW CREATE DATABASE 数据库名 ;
    或者：
    SHOW CREATE DATABASE 数据库名 \G

    • 使用/切换数据库  

    USE 数据库名;  

    •  注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数 据库名.”。

     2.3 修改数据库

    • 更改数据库字符集  

    ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等  

    1.  
    2. #2.3修改数据库
    3. #更改数据库字符集
    4. #ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
    5. SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    6.  
    7. ALTER DATABASE dbtest CHARACTER SET 'gbk';
    8. SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */


    #2.3修改数据库
    #更改数据库字符集
    #ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
    SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

    ALTER DATABASE dbtest CHARACTER SET 'gbk';
    SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
     

    2.4删除数据库 

    • 方式1：删除指定的数据库  

    DROP DATABASE 数据库名;  

    • 方式2：删除指定的数据库（ 推荐 ）

     DROP DATABASE IF EXISTS 数据库名; 

    1. #1.4删除数据库
    2. #方式一：
    3. DROP DATABASE dbtext01;
    4.  
    5. #方式二：
    6. DROP DATABASE IF EXISTS dbtext01;

    1.  
    2. #DDL数据定义语言：CREATE \ALTER\ DROP \RENAME \TRUNCATE
    3. #1. 创建和管理数据库
    4. #如何创建数据库？
    5. #方式一：
    6. CREATE DATABASE dbtest;   #创建此数据库使用的是默认的字符集utf8mb4
    7.  
    8. SHOW DATABASES;
    9.  
    10. #方式二：CREATE DATABASE 数据库名 CHARACTER SET 字符集;
    11. #        显式了指明了要哦创建的数据库的字符集
    12.  
    13. CREATE DATABASE dbtest CHARACTER SET 'gbk';
    14.  
    15. #方式三：：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
    16. #如果已经存在，则创建不成功，但是不会报错。
    17. #CREATE DATABASE IF NOT EXISTS 数据库名;
    18. CREATE DATABASE IF NOT EXISTS dbtest CHARACTER SET 'utf8';
    19.  
    20. #1-2管理数据库
    21. #查看当前连接中的数据库都有哪些
    22. SHOW DATABASES;
    23.  
    24. #2.2 使用数据库
    25.  
    26. #切换数据库
    27. USE dbtest;
    28.  
    29. #查看当前数据库中都有哪些数据表
    30. SHOW TABLES;
    31.  
    32. #查看当前使用的数据库
    33. SELECT DATABASE()
    34. FROM DUAL;
    35.  
    36. #查看指定数据库下保存的数据表
    37. SHOW TABLES FROM dbtest;
    38.  
    39. #2.3修改数据库
    40. #更改数据库字符集
    41. #ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
    42. SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    43.  
    44. ALTER DATABASE dbtest CHARACTER SET 'gbk';
    45. SHOW CREATE DATABASE dbtest;#CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
    46.  
    47. #1.4删除数据库
    48. #方式一：
    49. DROP DATABASE dbtext01;
    50.  
    51. #方式二：
    52. DROP DATABASE IF EXISTS dbtext01;
    53.

    3. 创建表  

    3.1 创建方式 1（白手起家型）
    •  必须具备: 
    • ---CREATE TABLE权限
      ---存储空间
    •  语法格式：
    CREATE TABLE [ IF NOT EXISTS ] 表名 (
    字段 1, 数据类型 [ 约束条件 ] [ 默认值 ],
    字段 2, 数据类型 [ 约束条件 ] [ 默认值 ],
    字段 3, 数据类型 [ 约束条件 ] [ 默认值 ],
    ……
    [ 表约束条件 ]
    );

    加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表； 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

    • 必须指定：  
    •                  表名
                       列名(或字段名) ，数据类型， 长度
       
    • 可选指定： 
      • 约束条件
        默认值
    • 创建表举例1： 
    创建表
    CREATE TABLE emp (
    -- int 类型
    emp_id INT ,
    -- 最多保存 20 个中英文字符
    emp_name VARCHAR ( 20 ),
    -- 总位数不超过 15 位
    salary DOUBLE ,
    -- 日期类型
    birthday DATE
    );

     DESC emp;

     

    MySQL 在执行建表语句时，将 id 字段的类型设置为 int(11) ，这里的 11 实际上是 int 类型指定的显示宽度，默 认的显示宽度为11 。也可以在创建数据表的时候指定数据的显示宽度。

    •  创建表举例2：
    CREATE TABLE dept(
    -- int 类型，自增
    deptno INT ( 2 ) AUTO_INCREMENT ,
    dname VARCHAR ( 14 ),
    loc VARCHAR ( 13 ),
    -- 主键
    PRIMARY KEY (deptno)
    );

    DESCRIBE dept;

    在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。  

    3.2 创建方式2（基于现有的表创建,同时导入数据）

    • 使用 AS subquery 选项， 将创建表和插入数据结合起来

    • 指定的列和子查询中的列要一一对应
    • 通过列名和默认值定义列 

    1. CREATE TABLE emp1 AS SELECT * FROM employees;
    2. CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
    3. CREATE TABLE dept80
    4. AS
    5. SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
    6. FROM employees
    7. WHERE department_id = 80;
    8.  
    9.  
    10. DESCRIBE dept80;

     

    3.3 查看数据表结构

    • 在 MySQL 中创建好数据表之后，可以查看数据表的结构。 MySQL 支持使用 DESCRIBE/DESC 语句查看数据 表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

    语法格式如下：

                            SHOW CREATE TABLE 表名\G  

    >>使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。 
    1.  
    2. #3. 创建表
    3. USE dbtest;
    4.  
    5. SHOW CREATE DATABASE dbtest;
    6. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
    7. ALTER DATABASE dbtest CHARACTER SET 'utf8';
    8.  
    9. SHOW TABLES;
    10.  
    11. #方式一：
    12. CREATE TABLE IF NOT EXISTS myempl(
    13. id INT,
    14. emp_name VARCHAR(15),
    15. hire_date DATE
    16. );
    17. #查看表结构
    18.  
    19. DESC myempl;
    20. #或者
    21. #查看创建表的语句结构
    22. SHOW CREATE TABLE myempl;
    23.  
    24.  
    25. SELECT * FROM myempl;
    26.  
    27. #方式二：基于现有的表创建,同时导入数据
    28. CREATE TABLE myemp2
    29. AS
    30. SELECT *
    31. FROM employees;
    32.  
    33. DESC myemp2;
    34.  
    35. SELECT *
    36. FROM myemp2;

    小练习： 

    1.  
    2. #练习一：创建一表 employees_copy,实现对 employees表的复制，包括表的数据
    3. CREATE TABLE employees_copy
    4. AS
    5. SELECT *
    6. FROM employees;
    7.  
    8. SELECT *
    9. FROM employees_copy;
    10.  
    11. #练习一：创建一表 employees_blank,实现对 employees表的复制，不包括表的数据
    12.  
    13. CREATE TABLE employees_blank
    14. AS
    15. SELECT*
    16. FROM employees
    17. WHERE 1=2;
    18.  
    19. SELECT*
    20. FROM employees_blank;

    employees表：

      

    练习一：

    练习二：

    4. 修改表
    • 修改表指的是修改数据库中已经存在的数据表的结构。  

    使用 ALTER TABLE 语句可以实现：

    >>>向已有的表中添加列
    >>>>修改现有表中的列
    >>>>>删除现有表中的列
    >>>>>>>重命名现有表中的列

    4.1 追加一个列  

    语法格式如下：

            ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;  

    •  举例：
    ALTER TABLE dept80
    ADD job_id varchar ( 15 );

    练习：

    1. #3-1添加一个字段
    2. #ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
    3. //把age放在name后面,如果放第一个用 FIRST
    4.  
    5. ALTER TABLE myemp2
    6. ADD age INT AFTER name;

     

     #查看myemp2表结构：

    DESC myemp2;

    4.2 修改一个列

    • 可以修改列的数据类型，长度、默认值和位置
    • 修改字段数据类型、长度、默认值、位置的语法格式如下： 
    ALTER TABLE 表名 MODIFY 【 COLUMN 】 字段名 1 字段类型 【 DEFAULT 默认值】【 FIRST| AFTER 字段名 2】 ;

    •  举例：
    ALTER TABLE dept80
    MODIFY last_name VARCHAR ( 30 );

    ALTER TABLE dept80
    MODIFY salary double ( 9 , 2 ) default 1000 ;
    •  对默认值的修改只影响今后对表的修改
    • 此外，还可以通过此种方式修改列的约束。

    小练习：

    1. #3-2修改一个字段：数据类型-长度-默认值（略）
    2. #ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
    3. ALTER TABLE myemp2
    4. MODIFY name VARCHAR(25);
    5.  
    6. ALTER TABLE myemp2
    7. MODIFY name VARCHAR(25) DEFAULT 'ddd';
    8.  
    9. DESC myemp2;

     

    4.3 重命名一个列  

    •  使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

    ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;  

    • 举例：  
    ALTER TABLE dept80
    CHANGE department_name dept_name varchar ( 15 );

    小练习：

    1.  
    2. #3-3重命名一个字段
    3. #ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
    4. ALTER TABLE myemp2
    5. CHANGE name emp_name VARCHAR(20);
    6.  
    7. DESC myemp2;

     

    4.4 删除一个列  

    • 删除表中某个字段的语法格式如下：

    ALTER TABLE 表名 DROP 【COLUMN】字段名  

    • 举例： 
    ALTER TABLE dept80
    DROP COLUMN job_id; 
    1. #3-4删除一个字段
    2. #ALTER TABLE 表名 DROP 【COLUMN】字段名
    3. ALTER TABLE myemp2
    4. DROP COLUMN age;
    5.  
    6. DESC myemp2;

    5.-重命名表

    • 方式一：使用RENAME  
    RENAME TABLE emp
    TO myemp;
    • 方式二： 
    ALTER table dept
    RENAME [ TO ] detail_dept; -- [TO] 可以省略
    •  必须是对象的拥有者 
    1. #4-重命名表
    2. #方式一：使用RENAME
    3. 	#RENAME TABLE emp
    4. 	#TO myemp;
    5.  
    6. RENAME TABLE myempl
    7. To myemp11;
    8.  
    9. DESC myemp11;
    10.

    6. 删除表  

    •  在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
    • 数据和结构都被删除
    • 所有正在运行的相关事务被提交
    • 所有相关索引被删除
    • 语法格式：

    DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];  

    IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存 在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。
    •  举例：

    DROP TABLE dept80;  

    • DROP TABLE 语句不能回滚  
    1. #5-删除表---DROP TABLE 语句不能回滚(不能撤销，删除了就没了)
    2. #DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
    3. DROP TABLE IF EXISTS myemp11;
    4.  
    5. DESC myemp11;

     

    7. 清空表 

    •  TRUNCATE TABLE语句：
      • 删除表中所有的数据
      • 释放表的存储空间
    •  举例：

    TRUNCATE TABLE detail_dept;  

    TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚  

     对比：

    DELETE FROM emp2;
    #TRUNCATE TABLE emp2;
    SELECT * FROM emp2;
    ROLLBACK ;
    SELECT * FROM emp2;

    阿里开发规范：
    【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无
    事务且不触发 TRIGGER ，有可能造成事故，故不建议在开发代码中使用此语句。
    说明： TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。 

    1. SELECT *
    2. FROM myemp2;

     原来表：

    执行清空操作：

    TRUNCATE TABLE myemp2;

    清空后：

    1. SELECT *
    2. FROM myemp2;

     

    表结构还在：

     

    8. 内容拓展 

    • 拓展 1 ：阿里巴巴《 Java 开发手册》之 MySQL 字段命名

    表删除 操作将把表的定义和表中的数据一起删除，并且 MySQL 在执行删除操作时，不会有任何的确认信 息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行 备份 ，这样当操作失误时可 以对数据进行恢复，以免造成无法挽回的后果。
    同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进 行完整的 备份 ，因为数据库的改变是 无法撤销 的，如果添加了一个不需要的字段，可以将其删除；相 同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

    拓展3：MySQL8新特性—DDL的原子化 

    • 在 MySQL 8.0 版本中， InnoDB 表的 DDL 支持事务完整性，即 DDL 操作要么成功要么回滚 。 DDL 操作回滚日志 写入到data dictionary 数据字典表 mysql.innodb_ddl_log （该表是隐藏的表，通过 show tables 无法看到） 中，用于回滚操作。通过设置参数，可将DDL 操作日志打印输出到 MySQL 错误日志中。

    分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

    CREATE DATABASE mytest;
    USE mytest;
    CREATE TABLE book1(
    book_id INT ,
    book_name VARCHAR ( 255 )
    );
    SHOW TABLES ;

    （1）在 MySQL 5.7 版本中，测试步骤如下： 删除数据表 book1 和数据表 book2 ，结果如下：

    mysql> DROP TABLE book1,book2;
    ERROR 1051 ( 42 S02): Unknown table 'mytest.book2'

    再次查询数据库中的数据表名称，结果如下：

    mysql> SHOW TABLES ;
    Empty set ( 0.00 sec)

     从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。

    （2）在 MySQL 8.0 版本中，测试步骤如下： 删除数据表 book1 和数据表 book2 ，结果如下：
    mysql> DROP TABLE book1,book2;
    ERROR 1051 ( 42 S02): Unknown table 'mytest.book2'

    再次查询数据库中的数据表名称，结果如下：

    数据库学习记录-----代码

    1. #字符串函数
    2. SELECT
    3. 	ASCII('abcdf'),
    4. 	CHAR_LENGTH('hello'),
    5. 	CHAR_LENGTH('诗栋'),
    6. 	LENGTH('helllo'),
    7. 	LENGTH('我嗯')
    8. FROM
    9. 	DUAL;
    10.  
    11. SELECT
    12. 	CONCAT(NAME, '-work for-', id) "details"
    13. FROM
    14. 	students;
    15.  
    16. #日期和时间函数
    17. #获取日期，时间
    18. SELECT
    19. 	CURDATE(),
    20. 	CURRENT_DATE (),
    21. 	CURTIME(),
    22. 	NOW(),
    23. 	SYSDATE(),
    24. 	#2022-09-24 -2022-09-24- 09:42:57 -2022-09-24 09:42:57- 2022-09-24 09:42:57 
    25. 	UTC_DATE(),
    26. 	UTC_TIME() #-2022-09-24-01:42:57
    27. FROM
    28. 	DUAL;
    29.  
    30. #日期与时间的转换
    31. SELECT
    32. 	UNIX_TIMESTAMP(),
    33. 	FROM_UNIXTIME(1663984252),
    34. 	UNIX_TIMESTAMP('2022-09-24 09:50:52'),
    35. 	FROM_UNIXTIME(1663984252)
    36. FROM
    37. 	DUAL;
    38.  
    39. SELECT
    40. 	YEAR (CURDATE()),
    41. 	MONTH (CURDATE()),
    42. 	DAY (CURDATE()),
    43. 	WEEKDAY('2022-09-25'),
    44. 	HOUR (CURTIME()),
    45. 	MINUTE (NOW()),
    46. 	SECOND (SYSDATE())
    47. FROM
    48. 	DUAL;
    49.  
    50. #日期的操作函数----》》extract(type FROM data)type指定返回的值
    51. SELECT
    52. 	EXTRACT(YEAR FROM NOW()),
    53. 	EXTRACT(MONTH FROM NOW()),
    54. 	EXTRACT(DAY FROM NOW()),
    55. 	EXTRACT(HOUR FROM NOW()),
    56. 	EXTRACT(MINUTE FROM NOW()),
    57. 	EXTRACT(SECOND FROM NOW()),
    58. 	EXTRACT(QUARTER FROM NOW())
    59. FROM
    60. 	DUAL;
    61.  
    62. #时间和秒钟的转换函数 TIME_TO_SEC(time)将time转化为秒返回结果值；
    63. #SEC_TO_TIME(seconds)TIME(second) 将second描述转化为包含小时、分钟、秒的时间
    64. SELECT
    65. 	TIME_TO_SEC(CURTIME()),
    66. 	SEC_TO_TIME(9000)
    67. FROM
    68. 	DUAL;
    69.  
    70. #计算日期和时间的函数 
    71. # DATE_ADD(date,INTERVAL expr unit)或ADDDATE(date,INTERVAL expr unit)返回与给定时间相差INTERVAL时间段的日期时间
    72. #DATE_SUB(date,INTERVAL expr unit)返回与data相差INTERVAL时间间隔的日期
    73. SELECT
    74. 	NOW(),
    75. 	DATE_ADD(NOW(), INTERVAL 1 YEAR),
    76. 	#2022-09-26 00:48:01;2023-09-26 00:48:01
    77. 	DATE_SUB(NOW(), INTERVAL 1 YEAR),
    78. 	#2021-09-26 00:53:59
    79. 	DATE_ADD(
    80. 		NOW(),
    81. 		INTERVAL '1_1' YEAR_MONTH
    82. 	) #2023-10-26 00:58:14;加1年1个月
    83. FROM
    84. 	DUAL;
    85.  
    86. #日期的格式化与解析,格式化：日期---》字符串，解析：字符串————》日期
    87. #DATE_FORMAT(date,format)按照字符串format格式转化日期date值
    88. SELECT
    89. 	DATE_FORMAT(CURDATE(), '%Y-%M-%d'),
    90. 	#2022-September-26
    91. 	DATE_FORMAT(NOW(), '%Y-%M-%d'),
    92. 	#2022-September-26
    93. 	TIME_FORMAT(CURDATE(), '%H:%i:%s'),
    94. 	#00:00:00
    95. 	DATE_FORMAT(
    96. 		CURDATE(),
    97. 		'%Y-%M-%d %h:%i:%s %W %T %r'
    98. 	) #2022-September-26 12:00:00 Monday 00:00:00 12:00:00 AM
    99. FROM
    100. 	DUAL;
    101.  
    102. #解析：格式化的逆过程·
    103. SELECT
    104. 	STR_TO_DATE(
    105. 		'2022-October-26 12:30:20 Monday 1',
    106. 		'%Y-%M-%d %h:%i:%s %W'
    107. 	) #2022-10-26 00:30:20
    108. FROM
    109. 	DUAL;
    110.  
    111. SELECT
    112. 	GET_FORMAT(DATE, 'USA') #%m.%d.%Y
    113. 	SELECT
    114. 		GET_FORMAT(
    115. 			CURDATE(),
    116. 			GET_FORMAT(DATE, 'USA')
    117. 		)
    118. 	FROM
    119. 		DUAL;
    120.  
    121. #流程控制函数
    122. #IF(VALUE,VALUE1,VALUE2)
    123. SELECT
    124. 	NAME,
    125. 	salary,
    126.  
    127. IF (
    128. 	salary >= 6000,
    129. 	'高工资',
    130. 	'低工资'
    131. )
    132. FROM
    133. 	employees;
    134.  
    135. #IFNULL(expr1,expr2)如果expr1是null则输出expr2否则输出本身
    136. #CASE
    137. #相当于java的if....else
    138. SELECT
    139. 	last_name,
    140. 	CASE
    141. WHEN salary >= 2000 THEN
    142. 	'孙悟空'
    143. WHEN salary >= 5000 THEN
    144. 	'孙悟净'
    145. WHEN salary >= 9000 THEN
    146. 	'孙悟能' ELT '曹耿'
    147. END "details"
    148. FROM
    149. 	employees;
    150.  
    151. #相当于java的switch
    152. #CASE....WHEN.......THEN........WHEN....THEN...ELSE....end
    153. SELECT
    154. 	employee_id,
    155. 	last_name,
    156. 	department_id,
    157. 	salary,
    158. 	CASE department - id
    159. WHEN 10 THEN
    160. 	salary * 1.1
    161. WHEN 20 THEN
    162. 	salary * 1.2
    163. WHEN 20 THEN
    164. 	salary * 1.3
    165. ELSE
    166. 	salary * 1.4
    167. END "details"
    168. FROM
    169. 	employees;
    170.  
    171. #相当于java的switch
    172. #CASE....WHEN.......THEN........WHEN....THEN...ELSE....end
    173. SELECT
    174. 	employee_id,
    175. 	last_name,
    176. 	department_id,
    177. 	salary,
    178. 	CASE department - id
    179. WHEN 10 THEN
    180. 	salary * 1.1
    181. WHEN 20 THEN
    182. 	salary * 1.2
    183. WHEN 20 THEN
    184. 	salary * 1.3
    185. END "details"
    186. FROM
    187. 	employees;
    188.  
    189.  
    190. WHERE
    191. 	department_id IN (10, 20, 30);
    192.  
    193. #5-加密与解密函数
    194. #PASSWORD()在mysql8.0被弃用
    195. #加密
    196. SELECT
    197. 	MD5('mysql'),
    198. 	SHA('mysql') #81c3b080dad537de7e10e0987a4bf52e
    199. FROM
    200. 	DUAL;
    201.  
    202. #f460c882a18c1304d88854e902e11b85d71e7e1b
    203. #加密：
    204. #ENCODE(str,,)/DECODE(crypt_str,pass_str):返回pass_str作为加密密码加密value在mysql8.0被弃用
    205. #解密：
    206. #DECODE(crypt_str,pass_str):返回pass_str作为解密密码解密value    在mysql8.0被弃用
    207. SELECT
    208. 	ENCODE('shenlidong', 'mysql'),
    209. 	ENCODE(
    210. 		'shenlidong',
    211. 		'mysql',
    212. 		'mysql'
    213. 	)
    214. FROM
    215. 	DUAL;
    216.  
    217. #在mysql8.0被弃用,5.7可以
    218. #[SQL]SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')
    219. #FROM DUAL;
    220. #[Err] 1305 - FUNCTION dbtest.ENCODE does not exist
    221. #mysql信息函数
    222. SELECT
    223. 	VERSION(),
    224. 	CONNECTION_ID(),
    225. 	DATABASE (),
    226. 	SCHEMA (),
    227. 	USER (),
    228. 	CURRENT_USER (),
    229. 	CHARSET('shenlidong'),
    230. 	COLLATION ('shenlidong')
    231. FROM
    232. 	DUAL;
    233.  
    234. #其他函数
    235. SELECT
    236. 	FORMAT(123.123, 2),
    237. 	FORMAT(123.125, 0),
    238. 	FORMAT(123.125, 2)
    239. FROM
    240. 	DUAL;
    241.  
    242. SELECT
    243. 	CONV(16, 10, 2),
    244. 	CONV(8888, 10, 16),
    245. 	CONV(NULL, 10, 2)
    246. FROM
    247. 	DUAL;
    248.  
    249. #BENCHMARK(count,expr):用于测试表达式的执行效率（时间）
    250. SELECT
    251. 	INET_ATON('192.168.10.1')
    252. FROM
    253. 	DUAL;
    254.  
    255. SELECT
    256. 	INET_ATON('192.168.10.1'),
    257. 	CHARSET(
    258. 		CONVERT ('shenlidong' USING 'utf8') }
    259. 		FROM
    260. 			DUAL;
    261.  
    262. #3232238081
    263. /*
    264. SELECT employees
    265. INSERT INTO employees VALUES(1002,'wangwu',1500);
    266. INSERT INTO employees VALUES(1003,'likui',2300);
    267. INSERT INTO employees VALUES(1004,'shimin',3000);
    268. INSERT INTO employees VALUES(1005,'liyuan',3500);
    269. FROM employees;*/
    270. #8-1聚合函数
    271. #8-1-1AVG() SUM() 
    272. SELECT
    273. 	id,
    274. 	NAME,
    275. 	salary,
    276. 	AVG(salary),
    277. 	SUM(salary),
    278. 	SUM(id)
    279. FROM
    280. 	employees;
    281.  
    282. #8-1-2 MAx()  MIN()  
    283. SELECT
    284. 	MAX(salary),
    285. 	MIN(salary)
    286. FROM
    287. 	employees;
    288.  
    289. SELECT
    290. 	MAX(NAME),
    291. 	MIN(NAME) #wangwu  lisi
    292. FROM
    293. 	employees;
    294.  
    295. #8-1-3 COUNT()
    296. #1.计算指定字段在查询结构中出现的个数（不包含有Null值的）
    297. SELECT
    298. 	COUNT(id),
    299. 	COUNT(salary),
    300. 	COUNT(salary * 3),
    301. 	COUNT(1),
    302. 	COUNT(*) #4 4 4 4 4
    303. FROM
    304. 	employees;
    305.  
    306. #如果计算表中有多少条记录，如何实现？
    307. #方式一：COUNT(*)
    308. #方式二：COUNT(1)‘
    309. #方式三：COUNT(具体字段)：不一定对！
    310. #2-注意：计算指定字段出现的个数时，是不计算有null值的。
    311. #3-
    312. SELECT
    313. 	AVG(salary),
    314. 	SUM(salary) / COUNT(salary) aver #2550.0000  2550.0000
    315. FROM
    316. 	employees;
    317.  
    318. #。。。。。。。。。。。。。。。。。
    319. SELECT
    320. 	AVG(IFNULL(salary, 1)) aver1,
    321. 	SUM(salary) / COUNT(IFNULL(salary, 1)) aver2 #2550.0000  2550.0000
    322. FROM
    323. 	employees;
    324.  
    325. #
    326. SELECT
    327. 	*
    328. FROM
    329. 	employees;
    330.  
    331. #方差  标准差  中位数等
    332. #8-2  GROUP BY 的使用
    333. #需求：查询各个部门的平均工资、最高工资
    334. SELECT
    335. 	id,
    336. 	AVG(salary),
    337. 	SUM(salary)
    338. FROM
    339. 	employees
    340. GROUP BY
    341. 	id #结论1：select中出现的非组函数的字段必须声明在group BY中
    342. 	#反之，group BY中声明的字段可以不出现在select中。
    343. 	#结论2：GROUP BY 声明在from后面、where后面，Order BY前面、limit前面
    344. 	#结论3：MySql中GROUP BY使用with ROLLUP
    345. 	SELECT
    346. 		id,
    347. 		AVG(salary),
    348. 		SUM(salary)
    349. 	FROM
    350. 		employees
    351. 	GROUP BY
    352. 		id WITH ROLLUP #计算整体的平均 : AVG(salary): 2550.0000 SUM(salary):10200
    353. 		#需求：查询各个部门的平均工资,按照低到高排列
    354. 		SELECT
    355. 			id,
    356. 			AVG(salary) aver_sal
    357. 		FROM
    358. 			employees
    359. 		GROUP BY
    360. 			id
    361. 		ORDER BY
    362. 			aver_sal ASC;
    363.  
    364. SELECT
    365. 	id,
    366. 	AVG(salary) aver_sal
    367. FROM
    368. 	employees
    369. GROUP BY
    370. 	id WITH ROLLUP
    371. ORDER BY
    372. 	aver_sal;
    373.  
    374. #8-3 HAVING  的使用（作用：用来过滤数据的）
    375. SELECT
    376. 	id,
    377. 	MAX(salary)
    378. FROM
    379. 	employees #WHERE MAX(salary)>1000   #错误方式过滤
    380. GROUP BY
    381. 	id;
    382.  
    383. #要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换where。否则，报错
    384. #要求2:HAVING必须声明在GROUP BY 后面
    385. SELECT
    386. 	id,
    387. 	MAX(salary)
    388. FROM
    389. 	employees
    390. GROUP BY
    391. 	id
    392. HAVING
    393. 	MAX(salary) > 2000;
    394.  
    395. #开发中，我们使用HAVING的前提是SQL中使用了GROUP BY
    396. #需求：查employees中id为1001，1002,1004中的比2000的最高工资
    397. #方式一：(推荐使用，执行效率高于方式二)
    398. SELECT
    399. 	id,
    400. 	MAX(salary)
    401. FROM
    402. 	employees
    403. WHERE
    404. 	id IN (1001, 1002, 1004)
    405. GROUP BY
    406. 	id
    407. HAVING
    408. 	MAX(salary) > 2000;
    409.  
    410. #方式二：
    411. SELECT
    412. 	id,
    413. 	MAX(salary)
    414. FROM
    415. 	employees
    416. WHERE
    417. 	id IN (1001, 1002, 1004)
    418. GROUP BY
    419. 	id
    420. HAVING
    421. 	MAX(salary) > 2000
    422. AND id IN (1001, 1002, 1004);
    423.  
    424. #结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING
    425. #   	 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE或HAVING中都可以。但是，建议声明在WHERE中
    426. /*
    427. 			WHERE和HAVING的对比：
    428. 1-从适用范围上：HAVING适用范围更广
    429. 						HAVING 可以完成 WHERE 不能完成的任务。这是因为，
    430. 在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。
    431. HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，
    432. 对分组的结果集进行筛选，这个功能是 WHERE 无法完成
    433. 的。另外，WHERE排除的记录不再包括在分组中。
    434. 2-如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING
    435. 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接
    436. 后筛选。
    437. */
    438. #4. SQL底层执行原理
    439. #SELECT语句的完整结构
    440. /*sql92语法：
    441. SELECT ...,...,...(存在聚合函数)
    442. FROM ...,...,...,...
    443. WHERE 多表连接条件 AND 不包含组（聚合函数）函数的过滤条件
    444. GROUP BY  包含组函数的过滤条件
    445. ORDER BY ... (ASC/DESC)
    446. LIMIT  ...,...(分页操作)
    449. #sql99语法：
    450. SELECT ...,...,...(存在聚合函数)
    451. FROM ...(LEFT/RIGHT)JOIN...ON 多表连接条件
    452. ...(LEFT/RIGHT)JOIN...ON
    453. WHERE 不包含组（聚合函数）函数的过滤条件
    454. GROUP BY  包含组函数的过滤条件
    455. ORDER BY ... (ASC/DESC)
    456. LIMIT  ...,...(分页操作)
    458. */
    459. #4.SQL语句的执行过程：
    460. #FROM...,...-->ON-->(LEFT/RIGHT JOIN)-->WHERE-->GROUP BY-->HAVING-->SELECT-->DISTINCT(去重)-->ORDER BY-->LIMIT
    461. #第09章_子查询
    462. #子查询指一个查询语句嵌套在另一个查询语句内部的查询。
    463. #需求：谁的工资比lidong高
    464. SELECT
    465. 	id,
    466. 	salary
    467. FROM
    468. 	employees
    469. WHERE
    470. 	salary > (
    471. 		SELECT
    472. 			salary
    473. 		FROM
    474. 			employees
    475. 		WHERE
    476. 			id = 1002
    477. 	);
    478.  
    479. #称谓的规范：外查询（或主查询）、内查询（子查询）
    480. /*
    482. 子查询（内查询）在主查询之前一次执行完成。
    483. 子查询的结果被主查询（外查询）使用 。
    484. 注意事项
    485. 子查询要包含在括号内
    486. 将子查询放在比较条件的右侧
    487. 单行操作符对应单行子查询，多行操作符对应多行子查询
    489. */
    490. /*
    492. 3 子查询的分类:
    493. 角度一：
    494. 按内查询的结果返回一条还是多条记录,将子查询分为 单行子查询 -多行子查询 。
    496. 角度二：
    497. 我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 
    498. 不相关(或非关联)子查询 （如上例）。
    499. 相关(或关联)子查询：比如：查询工资大于本部门平均工资的员工信息
    501. */
    502. #4. 单行子查询
    503. #4.1 单行比较操作符
    504. #题目：查询工资大于1002号员工工资的员工的信息
    505. #子查询的编写技巧（或步骤）：1-从里往外写；2-从外往里写
    506. SELECT
    507. 	salary
    508. FROM
    509. 	employees
    510. WHERE
    511. 	id = 1002;
    512.  
    513. #工资:2500
    514. SELECT
    515. 	id,
    516. 	employees.`name`,
    517. 	salary
    518. FROM
    519. 	employees
    520. WHERE
    521. 	salary > 2500;
    522.  
    523. # id :1003 name: shangsan salary:2700
    524. #子查询方式：
    525. SELECT
    526. 	id,
    527. 	employees.`name`,
    528. 	salary
    529. FROM
    530. 	employees
    531. WHERE
    532. 	salary > (
    533. 		SELECT
    534. 			salary
    535. 		FROM
    536. 			employees
    537. 		WHERE
    538. 			id = 1002
    539. 	);
    540.  
    541. # id :1003 name: shangsan salary:2700
    542. #题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
    543. SELECT
    544. 	last_name,
    545. 	job_id,
    546. 	salary
    547. FROM
    548. 	employees
    549. WHERE
    550. 	job_id = (
    551. 		SELECT
    552. 			job_id
    553. 		FROM
    554. 			employees
    555. 		WHERE
    556. 			employee_id = 141
    557. 	)
    558. AND salary > (
    559. 	SELECT
    560. 		salary
    561. 	FROM
    562. 		employees
    563. 	WHERE
    564. 		employee_id = 143
    565. );
    566.  
    567. #题目：返回公司工资最少的员工的last_name,job_id和salary
    568. SELECT
    569. 	last_name,
    570. 	job_id,
    571. 	salary
    572. FROM
    573. 	employees
    574. WHERE
    575. 	salary = (
    576. 		SELECT
    577. 			MIN(salary)
    578. 		FROM
    579. 			employees
    580. 	);
    581.  
    582. #题目：查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
    583. #方式一：
    584. SELECT
    585. 	employee_id，manager_id，department_id
    586. FROM
    587. 	employees
    588. WHERE
    589. 	manager_id = (
    590. 		SELECT
    591. 			manager_id
    592. 		FROM
    593. 			employees
    594. 		WHERE
    595. 			employee_id = 141
    596. 	)
    597. AND department_id = (
    598. 	SELECT
    599. 		department_id
    600. 	FROM
    601. 		employees
    602. 	WHERE
    603. 		employee_id = 141
    604. )
    605. AND employee_id <> 141;
    606.  
    607. #方式二（了解）
    608. SELECT
    609. 	employee_id,
    610. 	manager_id,
    611. 	department_id
    612. FROM
    613. 	employees
    614. WHERE
    615. 	(manager_id, department_id) = (
    616. 		SELECT
    617. 			manager_id,
    618. 			department_id
    619. 		FROM
    620. 			employees
    621. 		WHERE
    622. 			employee_id = 141
    623. 	)
    624. AND employee_id <> 141;
    625.  
    626. #题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
    627. SELECT
    628. 	department_id ,, MIN(salary)
    629. FROM
    630. 	employees
    631. WHERE
    632. 	department_id IS NOT NULL
    633. GROUP BY
    634. 	department_id
    635. HAVING
    636. 	MIN(salary) > (
    637. 		SELECT
    638. 			MIN(salary)
    639. 		FROM
    640. 			employees
    641. 		WHERE
    642. 			department_id = 50
    643. 	);
    644.  
    645. #题目：显式员工的employee_id,last_name,location。
    646. #      其中，若员工department_id与location_id为1800
    647. #      的department_id相同，则location为’Canada’，其余则为’USA’。
    648. SELECT
    649. 	employee_id,
    650. 	last_name,
    651. 	CASE department_id
    652. WHEN (
    653. 	SELECT
    654. 		department_id
    655. 	FROM
    656. 		department
    657. 	WHERE
    658. 		location_id = 1800
    659. ) THEN
    660. 	’Canada’
    661. ELSE
    662. 	’USA’
    663. END "location"
    664. FROM
    665. 	employees #2.5 子查询中的空值问题
    666. 	SELECT
    667. 		last_name,
    668. 		job_id
    669. 	FROM
    670. 		employees
    671. 	WHERE
    672. 		job_id = (
    673. 			SELECT
    674. 				job_id
    675. 			FROM
    676. 				employees
    677. 			WHERE
    678. 				last_name = 'Haas'
    679. 		);
    680.  
    681. #非法使用子查询
    682. #Subquery returns more than 1 row
    683. SELECT
    684. 	employee_id,
    685. 	last_name
    686. FROM
    687. 	employees
    688. WHERE
    689. 	salary = (
    690. 		SELECT
    691. 			MIN(salary)
    692. 		FROM
    693. 			employees
    694. 		GROUP BY
    695. 			department_id
    696. 	);
    697.  
    698. #3. 多行子查询
    699. #IN
    700. SELECT
    701. 	employee_id,
    702. 	last_name
    703. FROM
    704. 	employees
    705. WHERE
    706. 	salary IN (
    707. 		SELECT
    708. 			MIN(salary)
    709. 		FROM
    710. 			employees
    711. 		GROUP BY
    712. 			department_id
    713. 	);
    714.  
    715. #ANY
    716. #题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、
    717. #姓名、job_id 以及salary
    718. SELECT
    719. 	employee_id,
    720. 	job_id,
    721. 	salary
    722. FROM
    723. 	employees
    724. WHERE
    725. 	job_id <> ‘IT_PROG’
    726. AND salary < ANY (
    727. 	SELECT
    728. 		salary
    729. 	FROM
    730. 		employees
    731. 	WHERE
    732. 		job_id = ‘IT_PROG’
    733. );
    734.  
    735. #ALL
    736. #题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、
    737. #姓名、job_id 以及salary
    738. SELECT
    739. 	employee_id,
    740. 	job_id,
    741. 	salary
    742. FROM
    743. 	employees
    744. WHERE
    745. 	job_id <> ‘IT_PROG’
    746. AND salary < ALL (
    747. 	SELECT
    748. 		salary
    749. 	FROM
    750. 		employees
    751. 	WHERE
    752. 		job_id = ‘IT_PROG’
    753. );
    754.  
    755. #题目：查询平均工资最低的部门id
    756. #方式一：
    757. SELECT
    758. 	department_id
    759. FROM
    760. 	employees
    761. GROUP BY
    762. 	department_id
    763. HAVING
    764. 	AVG(salary) = (
    765. 		SELECT
    766. 			MIN(avg_sal)
    767. 		FROM
    768. 			(
    769. 				SELECT
    770. 					AVG(salary) avg_sal
    771. 				FROM
    772. 					employees
    773. 				GROUP BY
    774. 					department_id
    775. 			) dept_avg_sal
    776. 	) #方式二：
    777. 	SELECT
    778. 		department_id
    779. 	FROM
    780. 		employees
    781. 	GROUP BY
    782. 		department_id
    783. 	HAVING
    784. 		AVG(salary) <= ALL (
    785. 			SELECT
    786. 				AVG(salary) avg_sal
    787. 			FROM
    788. 				employees
    789. 			GROUP BY
    790. 				department_id
    791. 		) #3.3 空值问题 解决
    792. 		SELECT
    793. 			last_name
    794. 		FROM
    795. 			employees
    796. 		WHERE
    797. 			employee_id NOT IN (
    798. 				SELECT
    799. 					manager_id
    800. 				FROM
    801. 					employees
    802. 				WHERE
    803. 					manager_id IS NOT NULL
    804. 			);
    805.  
    806. #4. 相关子查询
    807. /*
    808. 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件
    809. 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
    810. */
    811. #题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
    812. #方式一：
    813. SELECT
    814. 	last_name,
    815. 	salary,
    816. 	department_id
    817. FROM
    818. 	employees e1
    819. WHERE
    820. 	salary > (
    821. 		SELECT
    822. 			AVG(salary)
    823. 		FROM
    824. 			employees e2
    825. 		WHERE
    826. 			department_id = e1.`department_id`
    827. 	);
    828.  
    829. #方式二：（在from中声明子查询）
    830. SELECT
    831. 	e1.last_name,
    832. 	e1.salary,
    833. 	e1.department_id
    834. FROM
    835. 	employees e1,
    836. 	(
    837. 		SELECT
    838. 			department_id,
    839. 			AVG(salary) dept_avg_sal
    840. 		FROM
    841. 			employees
    842. 		GROUP BY
    843. 			department_id
    844. 	) e2
    845. WHERE
    846. 	e1.`department_id` = e2.department_id
    847. AND e1.`salary` > e2.dept_avg_sal;
    848.  
    849. #在ORDER BY 中使用子查询：
    850. #题目：查询员工的id,salary,按照department_name 排序
    851. SELECT
    852. 	employee_id,
    853. 	salary
    854. FROM
    855. 	employees e
    856. ORDER BY
    857. 	(
    858. 		SELECT
    859. 			department_name
    860. 		FROM
    861. 			departments d
    862. 		WHERE
    863. 			e.`department_id` = d.`department_id`
    864. 	);
    865.  
    866. #结论：在 SELECT中，出除了 GROUP BY和 LIMIT之外，其他位置都可以声明子查询
    867. #题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
    868. #id的员工的employee_id,last_name和其job_id
    869. SELECT
    870. 	e.employee_id,
    871. 	last_name,
    872. 	e.job_id
    873. FROM
    874. 	employees e
    875. WHERE
    876. 	2 <= (
    877. 		SELECT
    878. 			COUNT(*)
    879. 		FROM
    880. 			job_history
    881. 		WHERE
    882. 			employee_id = e.employee_id
    883. 	);
    884.  
    885. #4.3 EXISTS 与 NOT EXISTS关键字
    886. #题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
    887. #方式一：
    888. SELECT
    889. 	employee_id,
    890. 	last_name,
    891. 	job_id,
    892. 	department_id
    893. FROM
    894. 	employees e1
    895. WHERE
    896. 	EXISTS (
    897. 		SELECT
    898. 			*
    899. 		FROM
    900. 			employees e2
    901. 		WHERE
    902. 			e2.manager_id = e1.employee_id
    903. 	);
    904.  
    905. #方式二：（自连接）
    906. SELECT DISTINCT
    907. 	e1.employee_id,
    908. 	e1.last_name,
    909. 	e1.job_id,
    910. 	e1.department_id
    911. FROM
    912. 	employees e1
    913. JOIN employees e2
    914. WHERE
    915. 	e1.employee_id = e2.manager_id;
    916.  
    917. #方式三：(子查询）
    918. SELECT
    919. 	employee_id,
    920. 	last_name,
    921. 	job_id,
    922. 	department_id
    923. FROM
    924. 	employees
    925. WHERE
    926. 	employee_id IN (
    927. 		SELECT DISTINCT
    928. 			manager_id
    929. 		FROM
    930. 			employees
    931. 	);
    932.  
    933. #题目：查询departments表中，不存在于employees表中的部门的department_id和department_name
    934. #方式一：
    935. SELECT
    936. 	d.department_id,
    937. 	d.department_name
    938. FROM
    939. 	employees e
    940. RIGHT JOIN departments d ON e.`department_id` = d.`department_id`
    941. WHERE
    942. 	e.`department_id` IS NULL;
    943.  
    944. #方式二：
    945. SELECT
    946. 	department_id,
    947. 	department_name
    948. FROM
    949. 	departments d
    950. WHERE
    951. 	NOT EXISTS (
    952. 		SELECT
    953. 			'X'
    954. 		FROM
    955. 			employees e
    956. 		WHERE
    957. 			d.department_id = e.department_id
    958. 	);
    959.  
    960. #4.4 相关更新
    961. UPDATE table1 alias1
    962. SET COLUMN = (
    963. 	SELECT
    964. 		expression
    965. 	FROM
    966. 		table2 alias2
    967. 	WHERE
    968. 		alias1. COLUMN = alias2. COLUMN
    969. );
    970.  
    971. #题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
    972. # 1）
    973. ALTER TABLE employees ADD (
    974. 	department_name VARCHAR2 (14)
    975. );
    976.  
    977. # 2）
    978. UPDATE employees e
    979. SET department_name = (
    980. 	SELECT
    981. 		department_name
    982. 	FROM
    983. 		departments d
    984. 	WHERE
    985. 		e.department_id = d.department_id
    986. );
    987.  
    988. #4.4 相关删除
    989. DELETE
    990. FROM
    991. 	table1 alias1
    992. WHERE
    993. 	COLUMN operator (
    994. 		SELECT
    995. 			expression
    996. 		FROM
    997. 			table2 alias2
    998. 		WHERE
    999. 			alias1. COLUMN = alias2. COLUMN
    1000. 	);
    1001.  
    1002. #题目：删除表employees中，其与emp_history表皆有的数据
    1003. DELETE
    1004. FROM
    1005. 	employees e
    1006. WHERE
    1007. 	employee_id IN (
    1008. 		SELECT
    1009. 			employee_id
    1010. 		FROM
    1011. 			emp_history
    1012. 		WHERE
    1013. 			employee_id = e.employee_id
    1014. 	);
    1015.  
    1016. #问题：谁的工资比Abel的高？
    1017. #方式1：自连接
    1018. SELECT
    1019. 	e2.last_name,
    1020. 	e2.salary
    1021. FROM
    1022. 	employees e1,
    1023. 	employees e2
    1024. WHERE
    1025. 	e1.last_name = 'Abel'
    1026. AND e1.`salary` < e2.`salary` #方式2：子查询
    1027. SELECT
    1028. 	last_name,
    1029. 	salary
    1030. FROM
    1031. 	employees
    1032. WHERE
    1033. 	salary > (
    1034. 		SELECT
    1035. 			salary
    1036. 		FROM
    1037. 			employees
    1038. 		WHERE
    1039. 			last_name = 'Abel'
    1040. 	);
    1041.  
    1042. #DDL数据定义语言：CREATE \ALTER\ DROP \RENAME \TRUNCATE
    1043. #1. 创建和管理数据库
    1044. #如何创建数据库？
    1045. #方式一：
    1046. CREATE DATABASE dbtest;
    1047.  
    1048. #创建此数据库使用的是默认的字符集utf8mb4
    1049. SHOW DATABASES;
    1050.  
    1051. #方式二：CREATE DATABASE 数据库名 CHARACTER SET 字符集;
    1052. #        显式了指明了要哦创建的数据库的字符集
    1053. CREATE DATABASE dbtest CHARACTER
    1054. SET 'gbk';
    1055.  
    1056. #方式三：：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
    1057. #如果已经存在，则创建不成功，但是不会报错。
    1058. #CREATE DATABASE IF NOT EXISTS 数据库名;
    1059. CREATE DATABASE
    1060. IF NOT EXISTS dbtest CHARACTER
    1061. SET 'utf8';
    1062.  
    1063. #1-2管理数据库
    1064. #查看当前连接中的数据库都有哪些
    1065. SHOW DATABASES;
    1066.  
    1067. #2.2 使用数据库
    1068. #切换数据库
    1069. USE dbtest;
    1070.  
    1071. #查看当前数据库中都有哪些数据表
    1072. SHOW TABLES;
    1073.  
    1074. #查看当前使用的数据库
    1075. SELECT
    1076. 	DATABASE ()
    1077. FROM
    1078. 	DUAL;
    1079.  
    1080. #查看指定数据库下保存的数据表
    1081. SHOW TABLES
    1082. FROM
    1083. 	dbtest;
    1084.  
    1085. #2.3修改数据库
    1086. #更改数据库字符集
    1087. #ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
    1088. SHOW CREATE DATABASE dbtest;
    1089.  
    1090. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    1091. ALTER DATABASE dbtest CHARACTER
    1092. SET 'gbk';
    1093.  
    1094. SHOW CREATE DATABASE dbtest;
    1095.  
    1096. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
    1097. #1.4删除数据库
    1098. #方式一：
    1099. DROP DATABASE dbtext01;
    1100.  
    1101. #方式二：
    1102. DROP DATABASE
    1103. IF EXISTS dbtext01;
    1104.  
    1105. #3. 创建表
    1106. USE dbtest;
    1107.  
    1108. SHOW CREATE DATABASE dbtest;
    1109.  
    1110. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
    1111. ALTER DATABASE dbtest CHARACTER
    1112. SET 'utf8';
    1113.  
    1114. SHOW TABLES;
    1115.  
    1116. #方式一：
    1117. CREATE TABLE
    1118. IF NOT EXISTS myempl (
    1119. 	id INT,
    1120. 	emp_name VARCHAR (15),
    1121. 	hire_date DATE
    1122. );
    1123.  
    1124. #查看表结构
    1125. DESC myempl;
    1126.  
    1127. #或者
    1128. #查看创建表的语句结构
    1129. SHOW CREATE TABLE myempl;
    1130.  
    1131. SELECT
    1132. 	*
    1133. FROM
    1134. 	myempl;
    1135.  
    1136. #方式二：基于现有的表创建,同时导入数据
    1137. CREATE TABLE myemp2 AS SELECT
    1138. 	*
    1139. FROM
    1140. 	employees;
    1141.  
    1142. DESC myemp2;
    1143.  
    1144. SELECT
    1145. 	*
    1146. FROM
    1147. 	myemp2;
    1148.  
    1149. #练习一：创建一表 employees_copy,实现对 employees表的复制，包括表的数据
    1150. CREATE TABLE employees_copy AS SELECT
    1151. 	*
    1152. FROM
    1153. 	employees;
    1154.  
    1155. SELECT
    1156. 	*
    1157. FROM
    1158. 	employees_copy;
    1159.  
    1160. #练习一：创建一表 employees_blank,实现对 employees表的复制，不包括表的数据
    1161. CREATE TABLE employees_blank AS SELECT
    1162. 	*
    1163. FROM
    1164. 	employees
    1165. WHERE
    1166. 	1 = 2;
    1167.  
    1168. SELECT
    1169. 	*
    1170. FROM
    1171. 	employees_blank;
    1172.  
    1173. #3-修改表-----ALTER TABLE
    1174. DESC myemp2;
    1175.  
    1176. #3-1添加一个字段
    1177. #ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
    1178. // 把age放在name后面,
    1179.  如果放第一个用 FIRST ALTER TABLE myemp2 ADD age INT AFTER NAME;
    1180.  
    1181. DESC myemp2;
    1182.  
    1183. #3-2修改一个字段：数据类型-长度-默认值（略）
    1184. #ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
    1185. ALTER TABLE myemp2 MODIFY NAME VARCHAR (25);
    1186.  
    1187. ALTER TABLE myemp2 MODIFY NAME VARCHAR (25) DEFAULT 'ddd';
    1188.  
    1189. DESC myemp2;
    1190.  
    1191. #3-3重命名一个字段
    1192. #ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
    1193. ALTER TABLE myemp2 CHANGE NAME emp_name VARCHAR (20);
    1194.  
    1195. DESC myemp2;
    1196.  
    1197. #3-4删除一个字段
    1198. #ALTER TABLE 表名 DROP 【COLUMN】字段名
    1199. ALTER TABLE myemp2 DROP COLUMN age;
    1200.  
    1201. DESC myemp2;
    1202.  
    1203. #4-重命名表
    1204. #方式一：使用RENAME
    1205. #RENAME TABLE emp
    1206. #TO myemp;
    1207. RENAME TABLE myempl TO myemp11;
    1208.  
    1209. DESC myemp11;
    1210.  
    1211. #5-删除表---DROP TABLE 语句不能回滚
    1212. #DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
    1213. DROP TABLE
    1214. IF EXISTS myemp11;
    1215.  
    1216. DESC myemp11;
    1217.  
    1218. #6-清空表--表结构还在，数据没了
    1219. #TRUNCATE TABLE detail_dept;
    1220. SELECT
    1221. 	*
    1222. FROM
    1223. 	myemp2;
    1224.  
    1225. TRUNCATE TABLE myemp2;
    1226.  
    1227. SELECT
    1228. 	*
    1229. FROM
    1230. 	myemp2;
    1231.  
    1232. DESC myemp2;
    1233.

  • 相关阅读:
    Bootstrap5 网格系统
    01-为什么 switch case 语句需要加入 break
    软件测试——自动化测试框架有哪些？
    【Java面试小短文】HashMap是如何解决Hash冲突的？
    java多线程面试题目
    语义召回进阶之路：从传统到深度学习的搜索革新
    Java配置47-Spring Eureka 未授权访问漏洞修复
    LabVIEW项目中实时目标出现黄色感叹号
    黑马JVM总结（六）
    Android Studio(列表视图ListView)
  • 原文地址：https://blog.csdn.net/m0_63064861/article/details/127458581