库和表的使用操作小记

记录的是基础用法,有很多细节省略了,详细请看课件或者完整笔记

MySQL的SQL语句分为以下三类

  1. DDL 【数据定义语句】

    用来维护存储数据的整体结构,比如库和表的创建、修改、删除、备份与还原、查看

    代表命令create,alter,drop,source,show

  2. DML【数据操纵语句】

    用来操作表中的具体数据,比如插入数据、查询数据、删除数据

    代表命令insert,select,delete,update

  3. DLC【数据控制语句】

    负责权限管理和事务

    代表命令grant,revoke,commit

登录mysql

有两种方式:

指明端口

image-20230415225508111

不指明端口,使用默认的

image-20230415225657207

-p的作用是不显式地输入密码,我并没有设置root账户的密码,直接按回车即可

-u和root之间可以有空格可以没有

DDL【数据定义语句】

查看mysql所有库

image-20230415225821594

注意mysql语句要用分号结尾

创建库

image-20230415230014631

或者“如果这个库不存在的话,就创建”(创建表的时候也一样):

注意,关于库、表的名称,最好使用反引号`` `括起来,这个符号是电脑esc键下面的键,半角状态打出

image-20230415230515065

也可以不括起来,但是如果名称和mysql内部一些名称冲突的话,会报错

查看创建库的记录(查看库结构)

截屏2024-07-14 09.11.54
  1. 只能查看已创建的库
  2. 本质上是查看创建库的所有语句,这些语句保存在了一个文件中,这个文件可以用于库的备份(见另一篇文章)

删除库

image-20230415230810279

进入/使用库

image-20230415231043861

查看当前所在库

image-20230415231150871

退出库

严格来说也不是退出,而是返回到最初的库

截屏2024-07-14 15.19.42

当然,是没有必要退出的,因为在其他数据库的时候,还是可以使用show databases;命令查看所有数据库,并使用use 数据库名;直接进入其他数据库

截屏2024-07-14 15.20.56

创建表

1
2
3
4
5
create table 表名 (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

最下面一行后面的内容可以省略

image-20230415232123861
  1. 可以换行可以不换

    换行更美观,但是换行之后之前输入的内容无法修改,所以写代码要准确

  2. Sno、Sname、Sage之类的就是列属性,就是列名

  3. Sno它们后面跟的是这一列数据的数据类型

  4. comment是给这个列加备注,可以加可以不加

  5. if not exists 可加可不加,意思是如果Student这个表之前不存在的话,就创建,存在的话就不创建,在前面的库的创建和删除中,以及在后面的表的删除中,也是可加可不加

查看表的属性

image-20230415232523386

查看库中的所有表

image-20230415232555462

查看创建表的记录(查看表结构)

image-20230416084512253
  1. 只能查看已存在的表
  2. 本质上是打印出来创建表的时候执行的所有语句

省略长横线:

image-20230416085136345

新增列(新增字段)

在SC表中的Cno列后面新增一列Grade

image-20230416091642802
  1. 如果不加after,默认加在最后一列
  2. 新增一列之后,此列的数据默认为NULL

查看一下表结构,发现列信息被加入进去了:

image-20230416092018226

新增列(字段)时设置默认数据填充

由于一般新增列的时候,默认该列的数据都为空NULL,但是有些场景需要指定默认数据

创建新列时设置默认填充数据:

截屏2024-07-14 15.51.15 截屏2024-07-14 15.51.23

设置主键

主键用于标定一张表中一条数据的唯一性的的列。主键不为空、数据值不能重复,因此可以标定一条数据的唯一性。

一张表中只能有一个主键,但是主键可以是由一列构成,也可以由多列复合而成(复合主键),只要复合主键中并不是每一列的数据都是相同的,那么这个数据就是唯一的。

一般选择int类型字段作为主键(比如ID之类)

新建表时创建主键

方法一:在设置字段的时候设置上主键

截屏2024-07-14 16.05.39

方法二:在最后设置主键

截屏2024-07-14 16.07.42
  1. 两种方式都会用到
  2. 主键本身就不能为空,因此可以省略not null

取消主键

截屏2024-07-14 16.15.01

设置现有列(字段)为主键

截屏2024-07-14 16.15.20

在添加新字段时设置为主键

前提是表中没有主键

截屏2024-07-14 16.18.24

设置复合主键

创建新表时设置

截屏2024-07-14 16.20.57

不能用第一种方式创建

截屏2024-07-14 16.22.59

会报错“定义了重复主键”

设置现有字段为复合主键

此前表中没有主键

截屏2024-07-14 16.24.41

更多

关于主键、默认值(default)、唯一键、外键等SQL字段约束相关知识,在慕雪的寒舍-SQL字段约束

修改列的数据类型

将SC表中的Sno的数据类型由int(11)改为int(20)

image-20230416092343137

查看一下表结构:

image-20230416092422560

说明修改表的某一列是用新的列覆盖掉旧的列

  1. 将同类型缩小,要确保原数据不会溢出

  2. 不同类型之间转换的时候,要确保这两种数据之间是能双向转化的

修改列名

将表SC的Grade列改名为NianJi

image-20230416093816455

注意,在改列名的时候,必须还要重新指定该列的数据类型,可以保留原来的,也可以修改,但必须都要写出。

这里我顺便修改成新的数据类型,还可以加注释。

所以说修改列名是包含了修改修改数据类型的

修改列名使用change而不是rename

  1. rename留给了修改表名使用
  2. 修改列名的时候不只是修改了列名,还有加上该列的数据类型(无论是否更改),因此用change更合理

查看一下表结构:

image-20230416094024996

删除列

image-20230416093110524

凡是涉及到删除的操作,都要慎重

如果表中只剩下一列,不能删除列了,只能删除整张表

删除表

截屏2024-07-14 15.09.43

修改表名

image-20230416094152571

数据库备份

https://blog.musnow.top/posts/2109090510/

备份命令在shell命令行进行

1
$ mysqldump [-P端口号] -u用户名 -p密码 [-B] 数据库名 > 备份后的名称.bak.sql

[ ]内的是可以省略的

-u和用户名、-p和密码之间可以加空格,可以不加

-B和数据库名之间必须有空格(如果-B没有省略的话)

端口号可能是3306,取决于数据库有没有指定端口

备份后的文件要具体到路径,否则认为当前工作路径(同其他shell命令一样)

eg1:mysqldump -uroot -p0295 test2 > ../test2.bak.sql备份到了父目录

eg2:mysqldump -P3306 -uroot -p0295 test2 > test2.bak.sql备份到了当前目录

可以多个数据库同时备份

1
$ mysqldump [-P端口号] -u用户名 -p密码 [-B] 数据库1 数据库2 > 备份后的名称.bak.sql

eg:mysqldump -uroot -p0295 -B test2 test_mysql > test.back.sql两个数据库备份到了一个备份文件上

也可以只进行数据库中表的备份

1
$ mysqldump [-P端口号] -u用户名 -p密码 数据库名 表1 表2 > 备份后的名称.bak.sql

备份表的时候不能加-B,因为-B是专用于数据库的,否则会把表名当成数据库名

eg:mysqldump -uroot -p0295 test2 test2_table1 > test_table1.back.sql

其他知识点详见置顶链接

还原命令在mysql中执行

1
mysql> source 备份文件路径

eg:source /yj/test2.bak.sql

数据库中的内容和原来数据库中的内容是相同的

mysql备份的时候,会将里面的语句智能化简

MySQL通过system执行shell命令

eg:mysql> system clear执行清理屏幕功能

DDL命令操作表时要加table固定写法

比如alter, drop等等,后面都需要加table, 然后才能跟表名

而下面的DML在指定表名的时候不需要写table, 直接用表名就可以

这是因为DDL是操作表的

而DML是操作数据的

DML【数据修改语句】

向表中插入数据(insert)

单行插入

image-20230416085917365

Sno、Sname等字段可以不写,如果不写的话就默认为数据为空(前提是该字段允许为空,或已设置defalut值)

image-20230416090914765

如果字段名全部省略,就默认为所有的数据都要填上,所有数据都要填充,且要按照表中字段顺序填入

image-20230416090326238

多行指定列插入

截屏2024-07-16 15.06.41

同单行插入一样,字段名可以省略(但是数据插入要按字段顺序);

部分字段名可以不写,默认为空(前提是该字段允许为空,或者有default值)

插入否则更新(on duplicate update)

如果要插入的数据,其主键或唯一键,与表中现存数据重合,则插入数据失败。此时我们可以选择更新现有数据。

截屏2024-07-16 15.02.38

在test表中插入数据Sno、Sage(Sno必填,因为是主键),如果填入的内容与主键发生重复冲突,则更新update语句后面指定字段中的内容

示例中是,如果发生主键/唯一键冲突,则将该主键/唯一键对应的数据中Sno字段数据改为21

插入否则替换(replace)

与更新update不同,更新是只更新数据中原有的部分字段,替换replace则是先删除原数据(所有字段),然后根据要填入的value再插入。

要插入的数据的主键/唯一键与现有数据冲突时,replace将现有数据所有字段删除,将要插入的数据重新插入。即replace在遇到冲突时,是先删除,后插入。

截屏2024-07-16 15.42.16
  1. replace在无冲突时,相当于普通的insert
  2. replace在冲突时,将原有数据删除后,原有数据不保留,只会插入replace语句中表明要插入的values
  3. 与insert一样,允许省略全部字段名,或者省略部分允许为空或已设定好default值的字段名
截屏2024-07-16 15.44.09

从这里可以看出,数据是先删除后插入

查看表中的数据

基本写法

1
2
3
4
5
6
7
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

全列查询

*为通配符

查看Student所有数据:

image-20230416090348697

查看Course所有数据:

image-20230416094630308
  1. NULL表示没有数据,和’’不同,’’是有数据,数据是空字符串。
  2. 一般情况下不建议使用全列查询
    1. 查询到的数据越多,数据传输量越大
    2. 可能会影响索引的使用

按列查询

查询指定字段的数据,并汇聚成一张表呈现出来

截屏2024-07-16 16.00.58

表达式查询

所以可以看出来,select到from之间的这部分,实际上就是规定了查询结果的字段。而MySQL支持将查询结果通过表达式进行展示

截屏2024-07-16 16.36.19

为表达式查询结果设置别名

这样可以增强查询结果的可读性

截屏2024-07-16 16.38.00

查询结果去重(select distinct)

有些使用场景下可能需要用到去重,比如上面,只是想知道总体上都是有哪几种总分,因此可以对结果进行去重。

截屏2024-07-16 16.41.27

注意去重只是去掉所有字段都重复的数据,所以结果中,就算有一个字段存在重复的数据,但是其他字段不重复,该数据也不会被去掉

截屏2024-07-16 16.43.05

条件查询(where)【重要】

基本用法就是在表名后加where,后面是查询条件

where语句支持比较运算符和逻辑运算符

比较运算符说明
>, >=, <, <=
=等于,NULL 不安全,NULL=NULL 的结果是 NULL
<=>等于,NULL 安全,NULL<=>NULL 的结果是 TRUE(1)
!=, <>不等于
BETWEEN a AND b范围匹配,闭区间,如果 a <= value <= b 返回 TRUE(1)
IN (option, ..., ...)如果是 option 中的任意一个,则返回 TRUE(1)
IS NULLNULL
IS NOT NULL不是 NULL
LIKE模糊匹配,% 表示任意多个(包括 0 个)字符;_表示任意一个字符;
逻辑运算符说明
AND与,相当于 cpp 中的 &&,全真为 1,有假为 0
OR或,相当于 cpp 中的 `
NOT逻辑取反,条件为 TRUE(1) 的时候结果为 FALSE(0)

使用比较和逻辑运算符:

截屏2024-07-17 09.29.54

使用BETWEEN…AND…进行区间筛选:

截屏2024-07-17 09.34.30

配合逻辑运算符,使用相等筛选:

截屏2024-07-17 09.44.01

使用IN也可以实现相同效果:

截屏2024-07-17 09.49.34

使用LIKE进行字符匹配:

截屏2024-07-17 09.51.53

计算后筛选&设置别名:

需要注意的是,where只是进行条件筛选,起别名的工作并不是通过where语句完成的,而是select本身

并且起别名本质是在表示结果的时候更换一下结果表的字段名,并不影响查询过程,所以where是不认识别名的

截屏2024-07-17 10.12.10

使用IS NULL 或者 IS NOT NULL查询:

截屏2024-07-17 10.25.04

where查询条件不一定必须存在于select要查询的字段中:

使用where时设置条件的字段,可以与select查询要获取的结果集字段无关,而是可以根据原有表中任意字段进行筛选

截屏2024-07-17 10.26.41

结果排序(order by)

按单字段升序【默认】/ 降序【后加DESC】 显示

截屏2024-07-17 10.29.21

降序在字段后加EDSC

截屏2024-07-17 10.30.52

按多字段排序

按照书写先后确定排序优先级

截屏2024-07-17 10.51.57

先按照数学将序排序,相等时,就再参考语文的升序,如果还是相等,参考英语的升序

按求和结果排序

截屏2024-07-17 10.55.36

排序的时候可以使用别名,说明排序只是对于查询后的结果表进行排序,而不同于where(在查询时按条件筛选)

where + order by

截屏2024-07-17 10.58.41

查询语文成绩大于70分的学生的id、姓名、数学成绩

并将查询结果按照语文成绩,降序排列

  1. where查询条件不一定必须存在于select要查询的字段中,而是可以根据原有表中任意字段进行筛选
  2. 排序也不一定按照select查询结果的字段进行排序,而是可以按照原有表中任意字段排序