数据库——创建和管理表

古城微笑少年丶 2024-04-01 09:28 201阅读 0赞

目录

1.1 一条数据存储的过程

1.2 标识符命名规则

1.3 MySQL中的数据类型

  1. 创建和管理数据库

2.2 使用数据库

2.3 修改数据库

2.4删除数据库

  1. 创建表

3.3 查看数据表结构

4.2 修改一个列

4.3 重命名一个列

4.4 删除一个列

5.-重命名表

  1. 删除表

  2. 清空表

  3. 内容拓展

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

1.1 一条数据存储的过程

  • 存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只

    能是一团乱麻，无从下手。

    那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，

    一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

76b2c4eb45e847ceb8c5b3708b7395d2.png

我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看， MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数

据表的 行与列 。

1.2 标识符命名规则

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

1.3 MySQL**中的数据类型**

f55fcf72fd7e4d0f841dde4358ac0341.png

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

f9959d59f75c412bb11fd815ea16bcf1.png

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

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;

65612c4e16644c0a936cebb9c6f43071.png

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;

7a3932ffdc8745fea168630df86bede3.png

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

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

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

e1eb95285f48490c9b15e9dd837406af.png

  • 指定的列和子查询中的列要一一对应

  • 通过列名和默认值定义列

    CREATE TABLE emp1 AS SELECT FROM employees;
    CREATE TABLE emp2 AS SELECT     FROM employees WHERE 1=2; — 创建的emp2是空表
    CREATE TABLE dept80
    AS
    SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
    FROM employees
    WHERE department_id = 80;

  1. DESCRIBE dept80;

87b899fe701146bd807a22addf9afab3.png

3.3 查看数据表结构

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

语法格式如下：

SHOW CREATE TABLE 表名\G

使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

  1. #3. 创建表
  2. USE dbtest;
  4. SHOW CREATE DATABASE dbtest;
  5. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
  6. ALTER DATABASE dbtest CHARACTER SET 'utf8';
  8. SHOW TABLES;
  10. #方式一：
  11. CREATE TABLE IF NOT EXISTS myempl(
  12. id INT,
  13. emp_name VARCHAR(15),
  14. hire_date DATE
  15. );
  16. #查看表结构
  18. DESC myempl;
  19. #或者
  20. #查看创建表的语句结构
  21. SHOW CREATE TABLE myempl;
  24. SELECT * FROM myempl;
  26. #方式二：基于现有的表创建,同时导入数据
  27. CREATE TABLE myemp2
  28. AS
  29. SELECT *
  30. FROM employees;
  32. DESC myemp2;
  34. SELECT *
  35. FROM myemp2;

小练习：

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

employees表：

423f0cda8ae743d490408e24216e4def.png

练习一：

9b7ae3d944cd44f4938a7ae15d5fdea6.png

练习二：

75c1ec091e634bfcb43f347ab9060008.png

4. 修改表

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

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列

修改现有表中的列

删除现有表中的列

重命名现有表中的列

4.1 追加一个列

语法格式如下：

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

  • 举例：

ALTER TABLE dept80

ADD job_id varchar ( 15 );

2a2e32deaa7e4dc9a6488d84170bfb26.png

练习：

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

4837f9a051a14e8ba886f74c528c37ad.png

#查看myemp2表结构：

DESC myemp2;

f4015e6aae1f4de6a03517f3517de5b8.png

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);
  6. ALTER TABLE myemp2
  7. MODIFY name VARCHAR(25) DEFAULT 'ddd';
  9. DESC myemp2;

5f2ab07b35874153b35c0d97da3fe68e.png

4.3 重命名一个列

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

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

  • 举例：

ALTER TABLE dept80

CHANGE department_name dept_name varchar ( 15 );

小练习：

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

981698afd8d94cb1b459a9ccbcafd72f.png

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;
  6. DESC myemp2;

fe5e13bca75a4dd2ab3d72f1a310047e.png

5.-重命名表

  • 方式一：使用RENAME

RENAME TABLE emp

TO myemp;

  • 方式二：

ALTER table dept

RENAME [ TO ] detail_dept; -- [TO] 可以省略

  • 必须是对象的拥有者

    4-重命名表

    方式一：使用RENAME

    1. #RENAME TABLE emp
    2. #TO myemp;

    RENAME TABLE myempl
    To myemp11;

    DESC myemp11;

cb3d8a0cde6541c79295d531c8625257.png

6. 删除表

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

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

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

  • 举例：

DROP TABLE dept80;

  • DROP TABLE 语句不能回滚

    5-删除表—-DROP TABLE 语句不能回滚(不能撤销，删除了就没了)

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

    DROP TABLE IF EXISTS myemp11;

    DESC myemp11;

97aff0ecc3ed44deb974c0cac690248d.png

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;

原来表：

a6ca9634fb994746bfa296570930c8d9.png

执行清空操作：

  1. TRUNCATE TABLE myemp2;

清空后：

  1. SELECT *
  2. FROM myemp2;

b0966410420342158164ef1ddaecc11a.png

表结构还在：

46304813e9a24ae98aa8dc341e5e476e.png

8. 内容拓展

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

3ed10e33fe1d41449e8bbc5af54e6ae2.png

表删除 操作将把表的定义和表中的数据一起删除，并且 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’

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

1215b46b4252451dabbb70c99ebe3b81.png

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

  1. #字符串函数
  2. SELECT
  3.     ASCII('abcdf'),
  4.     CHAR_LENGTH('hello'),
  5.     CHAR_LENGTH('诗栋'),
  6.     LENGTH('helllo'),
  7.     LENGTH('我嗯')
  8. FROM
  9.     DUAL;
  11. SELECT
  12.     CONCAT(NAME, '-work for-', id) "details"
  13. FROM
  14.     students;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  121. #流程控制函数
  122. #IF(VALUE,VALUE1,VALUE2)
  123. SELECT
  124.     NAME,
  125.     salary,
  127. IF (
  128.     salary >= 6000,
  129.     '高工资',
  130.     '低工资'
  131. )
  132. FROM
  133.     employees;
  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;
  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;
  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;
  190. WHERE
  191.     department_id IN (10, 20, 30);
  193. #5-加密与解密函数
  194. #PASSWORD()在mysql8.0被弃用
  195. #加密
  196. SELECT
  197.     MD5('mysql'),
  198.     SHA('mysql') #81c3b080dad537de7e10e0987a4bf52e
  199. FROM
  200.     DUAL;
  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;
  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;
  234. #其他函数
  235. SELECT
  236.     FORMAT(123.123, 2),
  237.     FORMAT(123.125, 0),
  238.     FORMAT(123.125, 2)
  239. FROM
  240.     DUAL;
  242. SELECT
  243.     CONV(16, 10, 2),
  244.     CONV(8888, 10, 16),
  245.     CONV(NULL, 10, 2)
  246. FROM
  247.     DUAL;
  249. #BENCHMARK(count,expr):用于测试表达式的执行效率（时间）
  250. SELECT
  251.     INET_ATON('192.168.10.1')
  252. FROM
  253.     DUAL;
  255. SELECT
  256.     INET_ATON('192.168.10.1'),
  257.     CHARSET(
  258.         CONVERT ('shenlidong' USING 'utf8') }
  259.         FROM
  260.             DUAL;
  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;
  282. #8-1-2 MAx()  MIN()  
  283. SELECT
  284.     MAX(salary),
  285.     MIN(salary)
  286. FROM
  287.     employees;
  289. SELECT
  290.     MAX(NAME),
  291.     MIN(NAME) #wangwu  lisi
  292. FROM
  293.     employees;
  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;
  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;
  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;
  325. #
  326. SELECT
  327.     *
  328. FROM
  329.     employees;
  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;
  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;
  374. #8-3 HAVING  的使用（作用：用来过滤数据的）
  375. SELECT
  376.     id,
  377.     MAX(salary)
  378. FROM
  379.     employees #WHERE MAX(salary)>1000   #错误方式过滤
  380. GROUP BY
  381.     id;
  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;
  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;
  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);
  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.     );
  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;
  513. #工资:2500
  514. SELECT
  515.     id,
  516.     employees.`name`,
  517.     salary
  518. FROM
  519.     employees
  520. WHERE
  521.     salary > 2500;
  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.     );
  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. );
  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.     );
  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;
  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;
  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.     );
  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.         );
  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.     );
  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.     );
  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. );
  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. );
  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.             );
  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.     );
  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;
  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.     );
  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.     );
  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.     );
  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;
  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.     );
  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;
  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.     );
  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. );
  971. #题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
  972. # 1）
  973. ALTER TABLE employees ADD (
  974.     department_name VARCHAR2 (14)
  975. );
  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. );
  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.     );
  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.     );
  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.     );
  1042. #DDL数据定义语言：CREATE \ALTER\ DROP \RENAME \TRUNCATE
  1043. #1. 创建和管理数据库
  1044. #如何创建数据库？
  1045. #方式一：
  1046. CREATE DATABASE dbtest;
  1048. #创建此数据库使用的是默认的字符集utf8mb4
  1049. SHOW DATABASES;
  1051. #方式二：CREATE DATABASE 数据库名 CHARACTER SET 字符集;
  1052. #        显式了指明了要哦创建的数据库的字符集
  1053. CREATE DATABASE dbtest CHARACTER
  1054. SET 'gbk';
  1056. #方式三：：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
  1057. #如果已经存在，则创建不成功，但是不会报错。
  1058. #CREATE DATABASE IF NOT EXISTS 数据库名;
  1059. CREATE DATABASE
  1060. IF NOT EXISTS dbtest CHARACTER
  1061. SET 'utf8';
  1063. #1-2管理数据库
  1064. #查看当前连接中的数据库都有哪些
  1065. SHOW DATABASES;
  1067. #2.2 使用数据库
  1068. #切换数据库
  1069. USE dbtest;
  1071. #查看当前数据库中都有哪些数据表
  1072. SHOW TABLES;
  1074. #查看当前使用的数据库
  1075. SELECT
  1076.     DATABASE ()
  1077. FROM
  1078.     DUAL;
  1080. #查看指定数据库下保存的数据表
  1081. SHOW TABLES
  1082. FROM
  1083.     dbtest;
  1085. #2.3修改数据库
  1086. #更改数据库字符集
  1087. #ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
  1088. SHOW CREATE DATABASE dbtest;
  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';
  1094. SHOW CREATE DATABASE dbtest;
  1096. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
  1097. #1.4删除数据库
  1098. #方式一：
  1099. DROP DATABASE dbtext01;
  1101. #方式二：
  1102. DROP DATABASE
  1103. IF EXISTS dbtext01;
  1105. #3. 创建表
  1106. USE dbtest;
  1108. SHOW CREATE DATABASE dbtest;
  1110. #CREATE DATABASE `dbtest` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */
  1111. ALTER DATABASE dbtest CHARACTER
  1112. SET 'utf8';
  1114. SHOW TABLES;
  1116. #方式一：
  1117. CREATE TABLE
  1118. IF NOT EXISTS myempl (
  1119.     id INT,
  1120.     emp_name VARCHAR (15),
  1121.     hire_date DATE
  1122. );
  1124. #查看表结构
  1125. DESC myempl;
  1127. #或者
  1128. #查看创建表的语句结构
  1129. SHOW CREATE TABLE myempl;
  1131. SELECT
  1132.     *
  1133. FROM
  1134.     myempl;
  1136. #方式二：基于现有的表创建,同时导入数据
  1137. CREATE TABLE myemp2 AS SELECT
  1138.     *
  1139. FROM
  1140.     employees;
  1142. DESC myemp2;
  1144. SELECT
  1145.     *
  1146. FROM
  1147.     myemp2;
  1149. #练习一：创建一表 employees_copy,实现对 employees表的复制，包括表的数据
  1150. CREATE TABLE employees_copy AS SELECT
  1151.     *
  1152. FROM
  1153.     employees;
  1155. SELECT
  1156.     *
  1157. FROM
  1158.     employees_copy;
  1160. #练习一：创建一表 employees_blank,实现对 employees表的复制，不包括表的数据
  1161. CREATE TABLE employees_blank AS SELECT
  1162.     *
  1163. FROM
  1164.     employees
  1165. WHERE
  1166.     1 = 2;
  1168. SELECT
  1169.     *
  1170. FROM
  1171.     employees_blank;
  1173. #3-修改表-----ALTER TABLE
  1174. DESC myemp2;
  1176. #3-1添加一个字段
  1177. #ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
  1178. // 把age放在name后面,
  1179.  如果放第一个用 FIRST ALTER TABLE myemp2 ADD age INT AFTER NAME;
  1181. DESC myemp2;
  1183. #3-2修改一个字段：数据类型-长度-默认值（略）
  1184. #ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
  1185. ALTER TABLE myemp2 MODIFY NAME VARCHAR (25);
  1187. ALTER TABLE myemp2 MODIFY NAME VARCHAR (25) DEFAULT 'ddd';
  1189. DESC myemp2;
  1191. #3-3重命名一个字段
  1192. #ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
  1193. ALTER TABLE myemp2 CHANGE NAME emp_name VARCHAR (20);
  1195. DESC myemp2;
  1197. #3-4删除一个字段
  1198. #ALTER TABLE 表名 DROP 【COLUMN】字段名
  1199. ALTER TABLE myemp2 DROP COLUMN age;
  1201. DESC myemp2;
  1203. #4-重命名表
  1204. #方式一：使用RENAME
  1205. #RENAME TABLE emp
  1206. #TO myemp;
  1207. RENAME TABLE myempl TO myemp11;
  1209. DESC myemp11;
  1211. #5-删除表---DROP TABLE 语句不能回滚
  1212. #DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
  1213. DROP TABLE
  1214. IF EXISTS myemp11;
  1216. DESC myemp11;
  1218. #6-清空表--表结构还在，数据没了
  1219. #TRUNCATE TABLE detail_dept;
  1220. SELECT
  1221.     *
  1222. FROM
  1223.     myemp2;
  1225. TRUNCATE TABLE myemp2;
  1227. SELECT
  1228.     *
  1229. FROM
  1230.     myemp2;
  1232. DESC myemp2;

发表评论

表情：
评论列表 （有 0 条评论，201人围观）

还没有评论，来说两句吧...

相关阅读