数据库操作

  1. # 注意：[] 表示占位符，实际使用时没有
  2. # 创建数据库：数据库在RDBMS中必须是唯一的
  3. create database [database_name];
  5. # 删除数据库：删除时会删除存储在此数据库中的完整信息，包括表、函数、视图
  6. drop database [database_name];
  8. # 重命名数据库
  9. rename database [old_db_name] to [new_db_name];
  11. # 选择数据库
  12. use [database_name];

表操作

1、建表语句：

  • table_name：表名
  • column_name：列名
  • data_type：列的数据类型
  • column_constraint：列约束，如 NOT NULL（非空）、UNIQUE（不重复）等 ```sql

    创建表

    create table [table_name] ( [column_name] [data_type] default value [column_constraint], … )

在创建数据表时判断是否存在，存在不创建，不存在则创建：

create table if not exist [table definition];

在建表时先删除存在的表

drop table if exists [tableName]; create table if not exists [table defintion];

  2. 2、修改表结构：
  3. ```sql
  4. # 向表中添加列：after指定新增列的位置，如果不写则添加为最后列
  5. alter table [table_name]
  6. add [new_column] [data_type] [column_constraint] after [existing_column];
  8. # 修改现有列的某些属性：修改有数据的列时可能存在数据丢失的情况
  9. alter table [table_name]
  10. modify [column_definition]
  12. # 删除已存在的列：只能删除那些没有被任何其他数据库对象，如视图、触发器、存储过程等使用的列
  13. alter table [table_name]
  14. drop column [column_name], drop column [column_name1],...

3、删除表结构：

  1. # 删除表结构：当要删除的表不存在时，会报错
  2. drop table [table_name,...];
  4. # 防止报错：drop table 语句永久删除表的数据和结构
  5. drop table [table_name] if exist;

4、删除表中的全部数据：

  • delete 数据库系统会记录操作， 通过一些努力，可以回滚已删除的数据；使用TRUNCATE TABLE语句时，除非在尚未提交的事务中使用它，否则无法回滚
  • 要从外键约束引用的表中删除数据，不能使用TRUNCATE TABLE语句。 在这种情况下，必须使用DELETE语句
  • 如果表具有与之关联的触发器，则TRUNCATE TABLE语句不会触发delete触发器
  • 执行TRUNCATE TABLE语句后，某些数据库系统会将自动增量列(或标识，序列等)的值重置为其起始值。 DELETE语句不是这种情况
  • 带有WHERE子句的DELETE语句从表中删除部分数据，而TRUNCATE TABLE语句始终从表中删除所有数据
    ```sql

    低效方式：对于几百万行的达标，delete语句很慢

    delete from [table_name];

高效方式：通过取消分配表分配的数据页来删除表中的所有行，不会记录日志，无法回滚

truncate table [table_name,table_name1,…];

  2. 5、表的重命名：
  3. ```sql
  4. # 方式一：rename 关键字
  5. rename table [old_table_name]
  6. to [new_table_name];
  8. # 方式二：alter
  9. alter table [old_table_name]
  10. rename to [new_table_name];

6、表的复制：

  1. # 将一个表的数据复制到另外一个表中：select ... into 与 insert ... into 不同
  2. select * into [new_table_name] from [old_table_name];

数据操作

1、插入数据：insert 语句

  • 插入数据时，列名和值属于一一对应的关系
  • 添加新行之前，数据库检查所有完整性约束，外键、主键等，如果违反了约束，会终止语句
    1. insert into [table_name] (column1,...) 
    2. values 
    3. (column1_value,...),
    4. (column1_value,...),...;

2、从其他表复制数据：通过 select 子选择

  1. insert into [table_name1] (column1,column2,...)
  2. select
  3.     column1,column2,...
  4. from [table_name2]
  5. where [condition]

3、更新数据：update

  • 在 update 子句中指出要更新的表
  • 在 set 子句中指出要修改的列
  • 在 where 子句中指出更新条件 
    1. update [table_name] 
    2. set 
    3.   column1=value1,column2=value2,...
    4. where
    5.   condition;

4、update join：使用一个表和连接条件来更新另外一个表

  1. # 创建两个表
  2. CREATE TABLE table1 (
  3.   column1 INT,
  4.   column2 INT,
  5.   column3 VARCHAR (100)
  6. );
  8. INSERT INTO table1 (column1, column2, column3)
  9. SELECT 1, 11, 'FIRST'  
  10. UNION ALL  
  11. SELECT 11,12, 'SECOND'  
  12. UNION ALL   
  13. SELECT 21, 13, 'THIRD'  
  14. UNION ALL   
  15. SELECT 31, 14, 'FOURTH';
  17. CREATE TABLE table2 (
  18.   column1 INT, 
  19.   column2 INT, 
  20.   column3 VARCHAR (100)
  21. );
  23. INSERT INTO table2 (column1, column2, column3)
  24. SELECT 1, 21, 'TWO-ONE'  
  25. UNION ALL  
  26. SELECT 11, 22, 'TWO-TWO'  
  27. UNION ALL   
  28. SELECT 21, 23, 'TWO-THREE'  
  29. UNION ALL   
  30. SELECT 31, 24, 'TWO-FOUR';
  32. # 查看表中数据
  33. mysql> SELECT * FROM table1;
  34. +---------+---------+---------+
  35. | column1 | column2 | column3 |
  36. +---------+---------+---------+
  37. |       1 |      11 | FIRST   |
  38. |      11 |      12 | SECOND  |
  39. |      21 |      13 | THIRD   |
  40. |      31 |      14 | FOURTH  |
  41. +---------+---------+---------+
  43. mysql> SELECT * FROM table2;
  44. +---------+---------+-----------+
  45. | column1 | column2 | column3   |
  46. +---------+---------+-----------+
  47. |       1 |      21 | TWO-ONE   |
  48. |      11 |      22 | TWO-TWO   |
  49. |      21 |      23 | TWO-THREE |
  50. |      31 |      24 | TWO-FOUR  |
  51. +---------+---------+-----------+
  53. # 假定想要将table2中的column2列和column3列的 (11,21) 行的数据更新到 table1，可以使用 update join
  54. update table1 t1
  55. left join table2 t2
  56. on t1.column1=t2.column2
  57. set t1.column2=t2.column2,t1.column3=t2.column3
  58. where t1.column1 in (21,31);
  60. # 执行结果
  61. mysql> select * from table1;
  62. +---------+---------+-----------+
  63. | column1 | column2 | column3   |
  64. +---------+---------+-----------+
  65. |       1 |      11 | FIRST     |
  66. |      11 |      12 | SECOND    |
  67. |      21 |      23 | TWO-THREE |
  68. |      31 |      24 | TWO-FOUR  |
  69. +---------+---------+-----------+

4、时间日期更新：

  1. update table [table_name]
  2. set create_time='YYYY-MM-DD HH:MM:SS'
  3. where id = value

5、删除表中的数据：从表中删除一行或多行

  • delete 后面没有 where 子句时，会删除表中的所有行记录
  • delete 语句返回的值为已删除的行数
    1. delete from [table_name]
    2. where [condition];

    查询语句 select

select 语句用于查询表中的数据，可以用于选择行、选择列、分组数据、连接表以及执行简单计算等语法。

  1. # 基本用法
  2. select column1,... from [table_name];
  4. # 查询表中的全部数据
  5. select * from [table_name];
  7. # 条件查询，添加 where 子句
  8. select * from [table_name]
  9. where [condition];
  11. # 对查询结果进行排序：order by column {ASC | DESC}
  12. # 排序子句位于where子句之后，ASC 表示升序（默认），DESC 表示降序
  13. # 支持多个排序列，即在第一排序条件列的基础上，对第一排序条件相等的元素按第二排序条件列进行排序
  14. select * from [table_name]
  15. where [condition]
  16. order by column1 ASC, column2 DESC;
  18. # 去除查询结果的重复项：distinct运算符
  19. # distinct后面跟着一列时，则数据库会使用该列来计算重复
  20. # distinct后面跟着多列时，会对这些列进行组合来进行计算重复
  21. # 注意事项：
  22. #   在SQL中，NULL是一个特别的值，用于标记缺少信息或信息不适应，
  23. #   该值无法与任何值进行比较（包括自身），对于NULL，distinct将所有的NULL值是视为相同，
  24. #   因此，distinct只会保留一个NULL值，切会删除其他的NULL值
  25. select distinct column1,... from [table_name]
  26. where [condition];
  28. # limit和offset用于返回行的一部分
  29. # row_count：确定将返回的行数
  30. # off_count：在开始返回之前跳过偏移行，可选，与limit连用时该语句先执行
  31. select * from [table_name]
  32. limit [row_count] offset [off_count];

select 语句的最全格式：

  • all 和 distinct ：去重选项，即是否对查询结果中完全相同的记录（所有字段数据都相同）进行去重
    • all：不去重
    • distinct：去重
  • from ：后接数据来源，支持从多个数据表中查询，结果为这些表的笛卡尔积
  • where 子句：支持基于值、基于值的范围、基于条件复合的子句（where子句中无法使用表别名）
    • 值：比较运算符（如 =，!=等）和相似（like）
    • 值的范围：in、not in、between x and y
    • 条件复合：or、and、not、&&、||、!
  • group by 子句：分组语句，可以将查询结果依据字段来进行分组，支持多个字段，即在一次分组的基础上再次进行分组
  • having 子句：对已经查询出来的结果进行筛选
  • order by 行 {desc | asc}：对结果进行排序
  • limit 与 offset：限制结果数量，支持两种写法
    • limit [row_count] offset [off_count]
    • limit [off_count]，[limit_count]
      1. select {ALL | distinct} * from [table_name]
      2. where [condition]
      3. group by [column]
      4. having [condition]
      5. order by [column]
      6. limit [row_count] offset [off_count]

SQL 中的时间日期格式：

  • yyyy-mm-dd：日期
  • hh:mm:ss：时间

SQL 具有三值逻辑，即 TRUE、FALSE、UNKNOWN（NULL）

  • NULL 表示数据未知的值，用来表示缺少的数据
  • NULL 与 任何值比较都不会导致 true 或者 false，NULL 甚至不等于自身，因此不能使用 xx=NULL 来判断某个值是否为 NULL
  • 要判断表达式或列的值是否为 NULL，应该使用 is null 或者 is not null ```sql mysql> select null=null; +—————-+ | null=null | +—————-+ | NULL | +—————-+ 1 row in set (0.00 sec)

mysql> select 2=null; +————+ | 2=null | +————+ | NULL | +————+ 1 row in set (0.00 sec)

mysql> select false and null; +————————+ | false and null | +————————+ | 0 | +————————+ 1 row in set (0.00 sec)

mysql> select null is null; +———————+ | null is null | +———————+ | 1 | +———————+ 1 row in set (0.00 sec)

  2. SQL 比较运算符：
  4. | 运算符 | 含义 | 运算符 | 含义 |
  5. | --- | --- | --- | --- |
  6. | = | 等于 | <> 或 != | 不等于 |
  7. | < | 小于 | > | 大于 |
  8. | <= | 小于或等于 | >= | 小于或等于 |
  9. | and | 与运算 | or | 或运算 |
  11. - between low and high：值在 low 和 high 之间，即 [low , high]
  12. - not expression：用于反转布尔表达式的值
  13. - in (value1, value2, ...)：值在后面表达式中
  14. - like pattern：模式匹配，支持通配符
  15.    - %：百分号，匹配0个、1个或多个字符
  16.    - _ ：下划线，匹配单个字符
  17.    - 当需要匹配 % 或 _ ，需要对其进行转义，默认转义字符反斜杠 \ ，需要自定义使用 escape 语句，示例：expression like pattern escape escape_character
  19. SQL别名：用于在执行查询期间为表或列分配临时名称。 有两种类型的别名：表别名和列别名。<br />几乎所有关系数据库管理系统都支持列别名和表别名。AS 关键字，用于创建别名，可以省略 。<br />![](https://cdn.nlark.com/yuque/0/2020/png/750131/1604027866510-9926b4ec-4ae2-4888-832a-90fc3c197ddc.png#align=left&display=inline&height=271&margin=%5Bobject%20Object%5D&originHeight=271&originWidth=716&size=0&status=done&style=none&width=716)
  20. <a name="nPbG8"></a>
  21. #### 连接
  23. 连接表的过程称为连接 Join，SQL 支持多种连接，如 内连接、左连接、右连接、全外连接等
  25. ![](https://cdn.nlark.com/yuque/0/2020/jpeg/750131/1604039658909-519b7793-9ee6-4533-802f-7fc2568f2f01.jpeg#align=left&display=inline&height=689&margin=%5Bobject%20Object%5D&originHeight=689&originWidth=991&size=0&status=done&style=none&width=991)
  27. 笛卡尔积：cross join，将 A 表的每一条记录与 B 表的每一条记录强行拼接在一起，就是笛卡尔积。假设 A 表有 m 条记录，B 表有 n 条记录，笛卡尔积就会产生 m*n 条记录
  28. ```sql
  29. select * from [table_name1 as t1] cross join [table_name2 as t2];

内连接：inner join ，从数学的角度为求两个表的交集，从笛卡尔积的角度上看就是从笛卡尔积中跳出 on 子句条件成立的记录，常用的写法有如下几种：

  1. # 写法一：通过on子句进行筛选，inner可以省略不写
  2. select * from [table_name1 as t1] 
  3. inner join [table_name2 as t2]
  4. on t1.id=t2.id;
  6. # 写法二：通过where子句筛选（等值连接）
  7. select * from [table_name1 as t1] 
  8. inner join [table_name2 as t2]
  9. where t1.id=t2.id;
  11. # 写法三：straight_join
  12. select * from [table_name1 as t1] 
  13. straight_join [table_name2 as t2] 
  14. on t1.id=t2.id;

左连接：left join，左连接的含义就是求两个表的交际后再加左表剩下的数据。从迪卡尔积的角度来说，就是先从笛卡尔积中挑出 on 子句条件成立的记录，然后加上左表剩余的记录

  1. select * from [table_name1 as t1] 
  2. left join [table_name2 as t2]
  3. on t1.id = t2.id;

右连接：right join，右连接的含义就是求两个表的交际后再加右表剩下的数据。从迪卡尔积的角度来说，就是先从笛卡尔积中挑出 on 子句条件成立的记录，然后加上右表剩余的记录

  1. select * from [table_name1 as t1] 
  2. right join [table_name2 as t2]
  3. on t1.id = t2.id;

外连接：outer join，外连接就是求两个集合的并集，从迪卡尔积的角度来说，就是先挑出 on 子句成立的记录，然后加上左表中剩余的记录，然后再加上右表中剩余的记录。MYSQL 不支持外连接，可以通过左连接和右连接的结果做 UNION 操作来实现

  1. select * from [table_name1 as t1] left join [table_name2 as t2] on t1.id = t2.id
  2. union
  3. select * from [table_name1 as t1] right join [table_name2 as t2] on t1.id = t2.id

using 子句：MySQL 中连接 SQL 语句时，on 子句的语法为 t1.column1 = t2.column1，当模式设计对链表的列采用了相同的命名时，可以使用 using 来简化 on 语法，格式为 using(column1)

  1. # on 子句写法
  2. select * from t1 join t2 on t1.id=t2.id;
  3. # using 写法
  4. select * from t1 join t2 using(id);