MySQ基本操作详解

今天药忘吃喽~ 2024-03-16 13:48 48阅读 0赞

## MySQL的基本操作 ##

首先sql操作中的关键字的是大小写不敏感的，create 和CREATE是一样的。

### 1.库操作 ###

#### 1. 1查看数据库 ####

show databases;

![在这里插入图片描述][943d984743684e4fb90a2bd5ab1f8e5c.png]

1.  show 和databases 之间有一个或者多个空格
2.  注意是databases而不是database
3.  结尾分号是英文形式，分号在SQL中是表示一行执行+
4.  代码的，如果语句后面么有分号，那么默认是要一句代码分多行来写（如下图）

![在这里插入图片描述][afb8b82a1c4840f7b3a0697e7497b410.png]

1.  下图中是每次执行完一个sql语句之后，会得到的一个反馈，反馈会告诉我们，当前结果有多少行记录，以及消耗了多少时间。在下图中set表示集合，所以意思就是在当前集合里有四行，执行共花费0.01秒（sec = second 秒）。有时会显示0.00 sec.,这表示小于10毫秒，所以不显示。

![在这里插入图片描述][02fc31313f5640708d480b3d46c07b8e.png]

#### 1. 2创建数据库 ####

CREATE DATABASE [IF NOT EXISTS] 数据库名称 [create_specification [, 
    create_specification] ...]
    create_specification:
     [DEFAULT] CHARACTER SET charset_name
     [DEFAULT] COLLATE collation_name

大写的表示关键字  
\[\] 是可选项  
CHARACTER SET: 指定数据库采用的字符集  
COLLATE: 指定数据库字符集的校验规则

##### 创建的语法规则 #####

1.  数据库名字可由数字，字母，下划线组成，数字不能开头（和java变量名一样），名字也不能是sql关键字（例如 show 、database）

![在这里插入图片描述][3077ce07ab5f40b5b2921d0e54cccc1c.png]

1.  如果就是想拿关键词作为数据库名，可以使用反引号\`把数据库名引起来。  
    ![在这里插入图片描述][852a8846238c440484ad44ca1224fef5.png]这里面的错误是  
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘databese’ at line 1

syntax是句法的意思，就理解成语法就行。manual——手册  
对我们比较重要是最后 `near 'databese' at line 1`这一句，表面错误在第一行的database附近

1.  创建数据库的时候可以指定字符集和校验规则  
    当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8\_ general\_ ci  
    **注意：MySQL的utf8编码不是真正的utf8，没有包含某些复杂的中文字符。MySQL真正的utf8是使用utf8mb4，建议大家都使用utf8mb4**

#### 示例 ####

1.创建名为db\_test1的数据库

CREATE DATABASE db_test1;

2.如果系统没有db\_test2的数据库，则创建一个名叫db\_test2的数据库，如果有则不创建

CREATE DATABASE IF NOT EXISTS db_test2;

3.如果系统没有db\_test的数据库，则创建一个使用utf8mb4字符集的db\_test数据库，如果有则不创建

CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;

### 1.3使用数据库 ###

use 数据库名;

#### 示例 ####

![在这里插入图片描述][98766ab84f514310976bf870b41d67c6.png]  
使用该数据库后会有相应的提示，表明数据库已经切换了。

#### 1.4删除数据库 ####

DROP DATABASE [IF EXISTS] db_name;

删除操作是非常危险的！一旦删除，数据就没了，难以恢复。  
数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除

#### 示例 ####

drop database if exists db_test1;
    drop database if exists db_test2;

如果database 里有db\_test1表就删除database.

### 2.表操作 ###

需要操作数据库中的表时，需要先使用该数据库

#### 2.1 创建表 ####

CREATETABLE table_name (
        field1 datatype,
        field2 datatype,
        field3 datatype
    );

示例1：

create table stu_test ( 
       id int,
       name varchar(20) comment '姓名',
       password varchar(50) comment '密码', 
       age int,
       sex varchar(1),
       birthday timestamp, 
       amout decimal(13,2), 
       resume text
    );

![在这里插入图片描述][2e7cbdc21718481fb83d228baa3bd4b5.png]

1.  comment 相当于注释，这个不太好用，只能在创建表的时候使用，所以一般来说我们更推荐使用\#或者 – 来表示注释

![在这里插入图片描述][f027c48018374117829d0bf96b11556f.png]

#### 2.2查看数据库中的表结构 ####

use 数据库名;
    desc 表名;

示例：

desc stu_test;

![在这里插入图片描述][b6318aa0cd9c43bc8a2f2a03cdf60d8a.png]

1.  desc是describe的缩写。
2.  查看结果经常会有int(11),这表示这一列哭护短查询显示的时候最多显示11个字符。这只是显示宽度，这与实际存储是没有关系的。
3.  NULL这一列表示的是否可以为空，如果是YES表示可以。
4.  Default 表示默认值。

#### 2.3删除数据库中的表结构 ####

use database_name;
    drop table 表名;

#### 2.4查询某个数据库内的所有表名 ####

use database_name;
    show tables;

## 3.SQL中的数据类型 ##

### 3.1数值类型 ###

![在这里插入图片描述][c25929b56f5846969c23981baebddcce.png]

1.  数值类型可以指定为无符号（unsigned），表示不取负数。
2.  对于整型类型的范围：有符号范围：-2（类型字节数\*8-1）到2（类型字节数\*8-1）-1，如int是4字节，就是-231到231-1；无符号范围：0到2（类型字节数\*8）-1，如int就是232-1。
3.  在设计的时候尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。
4.  BIT常用来表示串二进制数字。
5.  DOUBLE(3,1)表示三位有效数字，小数位数位为1位，所以10.2合法，10.20就不合法了。

#### DECIMA的进一步说明 ####

FLOAT和DOUBLE有一个很严重的问题，表示有些数据的时候，不能精确表示（存在误差），我们都知道FLOAT,DOUBLE在底层是通过多少位的底数多少位指数这种形式去表示数据的，这样带来的好处是计算速度快，存储空间小，但是会有误差。所以此时SQL提供DECIMAL(英文原意是十进制)来保存精确小数，而其底层的保存方式类型与字符串。

### 3.2字符串类型 ###

![在这里插入图片描述][de8db11c250046e98567b5eafad840da.png]

1.  VACHAR是一个可变字符串，SIZE可以指定最大长度，单位是“字符”，所以VACHAR(10)如果表示名字的话，一个名字最多有可以存十个字，而不是五个字。同时VACHAR(10)也并不是在一开始就占10个字符的存储空间，这是动态变化的。
2.  BLOB 存储的是二进制串，注意与BIT区别，BIT最多存64个二进制数，BLOB更长。比如我要存一个小一些的图片或者音频文件，就可以使用BLOB（大概64Kb）。

### 3.3日期类型 ###

![在这里插入图片描述][54bbe081dad548ffbb04c8f9c8559d34.png]

1.  TIMESTAMP是时间戳

## 4.MySQL的增删改查 ##

CRUD 增删改查（Create（增） Restrieve（查） Updata（改） Delete（删除））；

### 4.1新增插入数据 ###

先创建一个student表

#### 4.1.1基础的插入 ####

insert [into] 表名 values [列名] (值1，值2，值3.....);

实例

mysql> insert into student values(123,4456,'张三','123456@qq.com');
    Query OK, 1 row affected (0.00 sec)

mysql>  insert student (id,sn,name,qq_mail) values (123,4456,'张三','123456@qq.com');
    Query OK, 1 row affected (0.00 sec)

注意

1.  SQL没有字符类型，所以‘’ ""都是表示字符串类型。
2.  假如遇到这种错误`Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1`显示的是不正确是字符值，往往可能是表格字符集的问题，需要将整个数据库都删除，去重新建立数据库和表格，并且一定要在创建数据库时指定字符集为utf8mb4(utf8mb4比utf8更加完整，多了对emoji表情的编码)。
3.  insert into里面into可以省略

#### 4.1.2指定列的的插入 ####

insert [into] 表名 (列名1,列名2,列名3......) values(值1,值2,值3,......)

示例：

mysql> insert student(id,qq_mail) values (213,'34567@qq.com');
    Query OK, 1 row affected (0.00 sec)

注意：

1.  在前面的表结构定义里面定义了`id INT NOT NULL,`所以id不能为空，所以id是不能系统默认创建的

结果

mysql> select * from student;
    +-----+------+--------+---------------+
    | id  | sn   | name   | qq_mail       |
    +-----+------+--------+---------------+
    | 123 | 4456 | 张三   | 123456@qq.com |
    | 123 | 4456 | 张三   | 123456@qq.com |
    | 213 | NULL | NULL   | 34567@qq.com  |
    +-----+------+--------+---------------+
    3 rows in set (0.00 sec)

#### 4.1.3多次数据的的插入 ####

示例：

mysql> insert into student values(1,1,"李四","1@qq.com"),(2,2,"李四","2@qq.com");

结果

mysql> select * from student;
    +-----+------+--------+---------------+
    | id  | sn   | name   | qq_mail       |
    +-----+------+--------+---------------+
    | 123 | 4456 | 张三   | 123456@qq.com |
    | 123 | 4456 | 张三   | 123456@qq.com |
    | 213 | NULL | NULL   | 34567@qq.com  |
    |   1 |    1 | 李四   | 1@qq.com      |
    |   2 |    2 | 李四   | 2@qq.com      |
    +-----+------+--------+---------------+
    5 rows in set (0.00 sec)

1.  一次插入N个记录是比一次插入一个记录，分N次插入效率要高，因为MySQL是c/s模式，每次请求都是需要客户端和服务器交互一次的。前者交互了一次，后者交互了多次。

#### 4.1.4时间日期类型的插入 ####

插入时间是通过特定的时间日期来表示时间日期的.  
形如  
‘2023-02-17 21:25:20’

示例

mysql> create table homework(id int,createTime datetime);
    Query OK, 0 rows affected (0.03 sec)
    mysql> insert into homework values(1,'2023-12-25 18:32:16');
    Query OK, 1 row affected (0.00 sec)
    mysql> select * from homework;
    +------+---------------------+
    | id   | createTime          |
    +------+---------------------+
    |    1 | 2023-12-25 18:32:16 |
    +------+---------------------+
    1 row in set (0.00 sec)

同时SQL还提供now()函数来返回当前时间。  
示例：

mysql> insert into homework values(2,now());
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from homework;
    +------+---------------------+
    | id   | createTime          |
    +------+---------------------+
    |    1 | 2023-12-25 18:32:16 |
    |    2 | 2023-06-02 12:04:26 |
    +------+---------------------+
    2 rows in set (0.00 sec)

### 4.2查询数据SELECT ###

#### 4.2.1全列查找 ####

select * from 表名

在实际操作中执行select \* 是非常危险的，因为实际项目中的数据量是非常大的，如果数据全部从服务器读取到客户端，数据带宽会拥堵。

#### 4.2.2指定列查找 ####

select 列名，列名 from 表名;

示例：

mysql> select id,name from student;
    +-----+--------+
    | id  | name   |
    +-----+--------+
    | 123 | 张三   |
    | 123 | 张三   |
    | 213 | NULL   |
    |   1 | 李四   |
    |   2 | 李四   |
    +-----+--------+
    5 rows in set (0.00 sec)

#### 4.2.3 查询可以是表达式 ####

示例：

先来创建一个考试成绩表

mysql> DROP TABLE IF EXISTS exam_result;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> CREATE TABLE exam_result (
        ->     id INT,
        ->     name VARCHAR(20),
        ->     chinese DECIMAL(3,1),
        ->     math DECIMAL(3,1),
        ->     english DECIMAL(3,1)
        -> );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> -- 插入测试数据
    mysql> INSERT INTO exam_result (id,name, chinese, math, english) VALUES
        ->     (1,'唐三藏', 67, 98, 56),
        ->     (2,'孙悟空', 87.5, 78, 77),
        ->     (3,'猪悟能', 88, 98.5, 90),
        ->     (4,'曹孟德', 82, 84, 67),
        ->     (5,'刘玄德', 55.5, 85, 45),
        ->     (6,'孙权', 70, 73, 78.5),
        ->     (7,'宋公明', 75, 65, 30);
    Query OK, 7 rows affected (0.01 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    
    mysql> select * from exam_result;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
    +------+-----------+---------+------+---------+
    7 rows in set (0.00 sec)

我们可以查询所有人数学成绩+10后的结果

mysql> -- 查询所有人的数学成绩+10分的结果
    mysql> select math+10 from exam_result;
    +---------+
    | math+10 |
    +---------+
    |   108.0 |
    |    88.0 |
    |   108.5 |
    |    94.0 |
    |    95.0 |
    |    83.0 |
    |    75.0 |
    +---------+
    7 rows in set (0.00 sec)

但是注意：经过上述这样的查询，数据库服务器硬盘里面的数据是没有变化的。因为mysql是C/S模式，用户在客户端输入的sql，通过请求发送给服务器，服务器在解析并执行sql把查询结果从硬盘里读取出来，通过网络响应还给客户端，客户端把这些数据以***临时表***的形式显示出来。  
其实细心的同学可以发现实际在定义`exam_result`是`math DECIMAL(3,1),`也就是说math这一列的数据应该是三个有效数字，且小数点后一位，但是math+10不难发现，这里第一行就是108.0这是因为这里的表是***临时表***。

还可以将多个列放在一起计算  
比如查询每个同学的平均成绩

mysql> select name,(math+chinese+english)/3 from exam_result;
    +-----------+--------------------------+
    | name      | (math+chinese+english)/3 |
    +-----------+--------------------------+
    | 唐三藏    |                 73.66667 |
    | 孙悟空    |                 80.83333 |
    | 猪悟能    |                 92.16667 |
    | 曹孟德    |                 77.66667 |
    | 刘玄德    |                 61.83333 |
    | 孙权      |                 73.83333 |
    | 宋公明    |                 56.66667 |
    +-----------+--------------------------+
    7 rows in set (0.00 sec)

#### 4.2.4指定别名 ####

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称

SELECT 表达式 [AS] 别名 [...] FROM table_name;

示例：

mysql> SELECT id, name, (chinese + math + english)/3  as 平均分 FROM exam_result;
    +------+-----------+-----------+
    | id   | name      | 平均分    |
    +------+-----------+-----------+
    |    1 | 唐三藏    |  73.66667 |
    |    2 | 孙悟空    |  80.83333 |
    |    3 | 猪悟能    |  92.16667 |
    |    4 | 曹孟德    |  77.66667 |
    |    5 | 刘玄德    |  61.83333 |
    |    6 | 孙权      |  73.83333 |
    |    7 | 宋公明    |  56.66667 |
    +------+-----------+-----------+
    7 rows in set (0.00 sec)

### 4.3去重DISTINCT ###

mysql> select  distinct math from exam_result;
    +------+
    | math |
    +------+
    | 98.0 |
    | 78.0 |
    | 98.5 |
    | 84.0 |
    | 85.0 |
    | 73.0 |
    | 65.0 |
    +------+
    7 rows in set (0.00 sec)

distinct 也可以实现多列的去重，但是只有每列的元素值都是相同的，才会去掉，有一列不同是sql认为是不可以去重的。  
此外，下面这种写法也不对

mysql> select name,(distinct mat)h from exam_result; -- 错误
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct mat)h from exam_result' at line 1
    mysql> select name, distinct math from exam_result; -- 错误
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct math from exam_result' at line 1

### 4.4 查询结果排序ORDER BY ###

#### 4.4.1order by 子句 ####

语法：

-- ASC 为升序（从小到大）
    -- DESC 为降序（从大到小）
    -- 默认为 ASC
    SELECT ... FROM table_name [WHERE ...] 
     ORDER BY column [ASC|DESC], [...];

示例：

mysql> select * from exam_result order by math asc;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    +------+-----------+---------+------+---------+
    7 rows in set (0.00 sec)

注意：

1.  对于mySql而言，当我们没有指定order by （查询顺序）的人时候，此时显示的查询数据是顺序是不可预期的，代码的逻辑是不能依赖于此的。
2.  NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。

#### 4.4.2使用表达式及别名排序 ####

mysql> select *,(math+chinese+english) as 总分 from exam_result order by math desc;
    +------+-----------+---------+------+---------+--------+
    | id   | name      | chinese | math | english | 总分   |
    +------+-----------+---------+------+---------+--------+
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |  276.5 |
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |  221.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |  185.5 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |  233.0 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |  242.5 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |  221.5 |
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |  170.0 |
    +------+-----------+---------+------+---------+--------+
    7 rows in set (0.00 sec)
    
    mysql> select *,(math+chinese+english) as 总分 from exam_result order by 总分 desc;
    +------+-----------+---------+------+---------+--------+
    | id   | name      | chinese | math | english | 总分   |
    +------+-----------+---------+------+---------+--------+
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |  276.5 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |  242.5 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |  233.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |  221.5 |
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |  221.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |  185.5 |
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |  170.0 |
    +------+-----------+---------+------+---------+--------+
    7 rows in set (0.00 sec)

#### 4.4.3可以对多个字段进行排序，排序优先级随书写顺序 ####

示例：

mysql> select * from exam_result order by math asc,chinese desc,english asc;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    +------+-----------+---------+------+---------+
    7 rows in set (0.00 sec)

### 4.5条件查询WHERE ###

比较运算符  
![在这里插入图片描述][33ab7ec5c3294a2f96b27cd8fc62b17e.png]  
逻辑运算符  
![在这里插入图片描述][84f72a04cd974a849bf228e9187d9d4e.png]

注意：

1.  WHERE条件可以使用表达式，但不能使用别名。
2.  AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

#### 4.5.1比较运算 ####

示例：

1.  查询英语成绩小于60的同学信息

mysql> select * from exam_result where english < 60;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
    |    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
    |    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
    +------+-----------+---------+------+---------+
    3 rows in set (0.00 sec)

注：这行代码的底层是这样的，针对数据库的表，进行遍历，取出每一行的数据，把数据带入到条件中，看是否满足条件，如果为真就保留，如果为假，就不保留。

1.  查询语文成绩好于英语成绩的同学

mysql> select name,english,chinese from exam_result where english < chinese;
    +-----------+---------+---------+
    | name      | english | chinese |
    +-----------+---------+---------+
    | 唐三藏    |    56.0 |    67.0 |
    | 孙悟空    |    77.0 |    87.5 |
    | 曹孟德    |    67.0 |    82.0 |
    | 刘玄德    |    45.0 |    55.5 |
    | 宋公明    |    30.0 |    75.0 |
    +-----------+---------+---------+
    5 rows in set (0.00 sec)

1.  查询总分在 200 分以下的同学

mysql> select * from exam_result where english+chinese+math > 200;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    +------+-----------+---------+------+---------+
    5 rows in set (0.00 sec)

**注意**  
看下面的代码

mysql> select name,chinese+math+english as 总分 from exam_result where 总分 > 200;
    ERROR 1054 (42S22): Unknown column '总分' in 'where clause'

别名是不可以作为where条件的，这和sql的执行顺序有关，本身也是sql语法规定。上述代码的执行过程是：  
1.遍历每一行；  
2.把这一行带入到where的条件里去；  
3.符合条件的结果，在根据select这里指定的列进行查询、计算。

但是order by 这个关键字是可以的

mysql> select name,chinese+math+english as 总分 from exam_result order by 总分 ;
    +-----------+--------+
    | name      | 总分   |
    +-----------+--------+
    | 宋公明    |  170.0 |
    | 刘玄德    |  185.5 |
    | 唐三藏    |  221.0 |
    | 孙权      |  221.5 |
    | 曹孟德    |  233.0 |
    | 孙悟空    |  242.5 |
    | 猪悟能    |  276.5 |
    +-----------+--------+
    7 rows in set (0.00 sec)

#### 4.5.2逻辑运算 ####

示例：

mysql> -- 查询语文成绩大于80分，且英语成绩大于80分的同学
    mysql> SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    +------+-----------+---------+------+---------+
    1 row in set (0.00 sec)
    
    mysql> -- 查询语文成绩大于80分，或英语成绩大于80分的同学
    mysql> SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    +------+-----------+---------+------+---------+
    3 rows in set (0.00 sec)

mysql> -- 观察AND 和 OR 的优先级：
    mysql> SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    +------+-----------+---------+------+---------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    +------+-----------+---------+------+---------+
    3 rows in set (0.00 sec)

AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分.

#### 4.5.3between and ####

示例：

mysql> -- 查询语文成绩在 [80, 90] 分的同学及语文成绩
    mysql> SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
    +-----------+---------+
    | name      | chinese |
    +-----------+---------+
    | 孙悟空    |    87.5 |
    | 猪悟能    |    88.0 |
    | 曹孟德    |    82.0 |
    +-----------+---------+
    3 rows in set (0.00 sec)
    
    mysql> -- 使用 AND 也可以实现
    mysql> SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese
        -> <= 90;
    +-----------+---------+
    | name      | chinese |
    +-----------+---------+
    | 孙悟空    |    87.5 |
    | 猪悟能    |    88.0 |
    | 曹孟德    |    82.0 |
    +-----------+---------+
    3 rows in set (0.00 sec)

#### 4.5.4 in ####

示例：

mysql> -- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
    mysql> SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
    +-----------+------+
    | name      | math |
    +-----------+------+
    | 唐三藏    | 98.0 |
    +-----------+------+
    1 row in set (0.00 sec)
    
    mysql> -- 使用 OR 也可以实现
    mysql> SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math
        -> = 98 OR math = 99;
    +-----------+------+
    | name      | math |
    +-----------+------+
    | 唐三藏    | 98.0 |
    +-----------+------+
    1 row in set (0.00 sec)

#### 4.5.5模糊查询：LIKE ####

like相对于正则表达式只支持两种用法

1.  使用%代表任意N个字符（包括0个字符）；
2.  使用\_代表任意一个字符。

示例：

mysql> select * from exam_result where name like '孙%';
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
    |    6 | 孙权      |    70.0 | 73.0 |    78.5 |
    +------+-----------+---------+------+---------+
    2 rows in set (0.00 sec)

mysql> select * from exam_result where name like '孙_';
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    6 | 孙权   |    70.0 | 73.0 |    78.5 |
    +------+--------+---------+------+---------+
    1 row in set (0.00 sec)

#### 4.5.6NULL 的查询：IS \[NOT\] NULL ####

**在使用null作为查询条件即删选出某列为空的数据的时候可以使用 is null 和< = > null，这样的语句，但是此时要注意 = null、与< = >的区别**

示例：

mysql> select * from exam_result;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    1 | 唐三藏 |    67.0 | 98.0 |    56.0 |
    |    2 | 孙悟空 |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能 |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
    |    6 | 孙权   |    70.0 | 73.0 |    78.5 |
    |    7 | 宋公明 |    75.0 | 65.0 |    30.0 |
    |    1 | 贾宝玉 |    NULL | NULL |    NULL |
    +------+--------+---------+------+---------+
    8 rows in set (0.00 sec)
    
    mysql> select * from exam_result where chinese = null;
    Empty set (0.00 sec)
    mysql> select * from exam_result where chinese <=> null;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    1 | 贾宝玉 |    NULL | NULL |    NULL |
    +------+--------+---------+------+---------+
    1 row in set (0.00 sec)
    
    mysql> select * from exam_result where chinese is not null;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    1 | 唐三藏 |    67.0 | 98.0 |    56.0 |
    |    2 | 孙悟空 |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能 |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
    |    6 | 孙权   |    70.0 | 73.0 |    78.5 |
    |    7 | 宋公明 |    75.0 | 65.0 |    30.0 |
    +------+--------+---------+------+---------+
    7 rows in set (0.00 sec)

注意  
`select * from exam_result where Chinese = null`执行后并不会返回Chinese列等于null的行，这是因为chinese =null 执行成功后返回的就是null也就是false。也就是默认查询条件不成立，所以根本就不会筛选。

### 4.5分页查询LIMIT ###

语法：

-- 起始下标为 0
    -- 从 0 开始，筛选 n 条结果
    SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
    -- 从 s 开始，筛选 n 条结果
    SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
    -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
    SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

示例：

mysql> select * from exam_result limit 3;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    1 | 唐三藏 |    67.0 | 98.0 |    56.0 |
    |    2 | 孙悟空 |    87.5 | 78.0 |    77.0 |
    |    3 | 猪悟能 |    88.0 | 98.5 |    90.0 |
    +------+--------+---------+------+---------+
    3 rows in set (0.00 sec)
    
    mysql> select * from exam_result limit 3 offset 2;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    3 | 猪悟能 |    88.0 | 98.5 |    90.0 |
    |    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
    +------+--------+---------+------+---------+
    3 rows in set (0.00 sec)
    
    mysql> select * from exam_result limit 3,2;
    +------+--------+---------+------+---------+
    | id   | name   | chinese | math | english |
    +------+--------+---------+------+---------+
    |    4 | 曹孟德 |    82.0 | 84.0 |    67.0 |
    |    5 | 刘玄德 |    55.5 | 85.0 |    45.0 |
    +------+--------+---------+------+---------+
    2 rows in set (0.00 sec)

注意：`limit s,n` 与`limit n offset s` 这里面的s n 的顺序是相反的。s表示从哪开始显示，n表示显示多少行。

limit语句常常和其他查询条件语句一起用达到组合效果

示例  
寻找总分前三名的学生

mysql> select id,name,chinese+math+english as 总分 from exam_result order by 总分 desc limit 3;
    +------+--------+-------+
    | id   | name   | 总分  |
    +------+--------+-------+
    |    3 | 猪悟能 | 276.5 |
    |    2 | 孙悟空 | 242.5 |
    |    4 | 曹孟德 | 233.0 |
    +------+--------+-------+
    3 rows in set (0.00 sec)

### 4.6 修改语句UPDATE ###

语法：

UPDATE table_name SET 列名1 = 数值1 [, 列名2 = 数值2 ...]
     [WHERE ...] [ORDER BY ...] [LIMIT ...]

示例：

mysql> -- 将孙悟空的数学成绩变更为80分
    mysql> update exam_result set math = 10 where name = "孙悟空";
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> -- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
    mysql> update exam_result set math = 60,chinese = 70 where name = "曹孟德";
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> -- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
    mysql> update exam_result set math = math+30 order by math+chinese+english limit 3;
    Query OK, 2 rows affected (0.01 sec)
    Rows matched: 3  Changed: 2  Warnings: 0

这里有几个点需要注意一下，

1.  null 与数值运算的时候返回值依旧为null ，比如在这里面，将总成绩倒数前三的 3 位同学的数学成绩加上 30 分，这里面总成绩倒数的第一名是贾宝玉 math Chinese English 全部是null ,在执行完这一句后并不会全部变成30,而是依旧是null，这是因为 null +10 = null。
2.  update 更改数据如果超出既有类型的范围，那么直接会报错并且并不会更改
3.  math = math +10;在SQL中并不支持简写为mate+= 10;

### 4.7删除DELETE ###

语法

DELETEFROM  table_name [WHERE ...] [ORDERBY ...] [LIMIT ...]

示例

mysql> -- 删除孙悟空同学的考试成绩
    mysql> delete from exam_result where name = "孙悟空";
    Query OK, 1 row affected (0.01 sec)
    
    mysql> -- 删除姓孙的同学的考试成绩
    mysql> delete from exam_result where name like "孙%";
    Query OK, 1 row affected (0.01 sec)
    
    mysql> -- 删除数学第一名的同学的考试成绩
    mysql>- delete from exam_result order by math desc limit 1;
    
    mysql> -- 删除整张exam_result表
    mysql> delete from exam_result ;

注意  
`delete from 表名` 与 `drop 表名`前者是表里面的内容删除，但是表还在，而后者是表整个都直接删除了。

## 5.数据库约束 ##

### 5.1约束类型 ###

1.  **NOT NULL** \- 指示某列不能存储 NULL 值。
2.  **UNIQUE** \- 保证某列的每行必须有唯一的值。
3.  **DEFAULT** \- 规定没有给列赋值时的默认值。
4.  **PRIMARY KEY - NOT NULL 和 UNIQUE 的结合**。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。
5.  **FOREIGN KEY** \- 保证一个表中的数据匹配另一个表中的值的参照完整性。
6.  **CHECK** \- 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

### 5.2 NULL 约束和 NOT NULL约束 ###

创建表时，可以指定某列不为空：  
示例

在设置了该列的约束NOT NULL后，此行一旦插入id = null 就会报错。

### 5.3UNIQUE：唯一约束 ###

插入数据或者修改数据的时候，就会先查询，先看看数据是否已经存在，如果不存在，就能够插入、修改成功，如果存在就插入或者修改失败。

示例：

mysql> create table student (id int unique,name varchar(10) not null);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc student;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  | UNI | NULL    |       |
    | name  | varchar(10) | NO   |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    mysql> insert into student values(1,"zhangsan");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | zhangsan |
    +------+----------+
    1 row in set (0.00 sec)
    
    mysql> insert into student values(1,"lisi");
    ERROR 1062 (23000): Duplicate entry '1' for key 'id'

duplicate 重复的，entry 条目，入口

### 5.4 DEFAULT：默认值约束 ###

1.  默认值是insert指定列插入的时候其他未被指定到的列就是按照默认值来填充。
2.  我们在建表的时候，如果不指定默认值，那么SQL也会自动设置默认值为null。

示例：  
指定插入数据时，name列为空，默认值“无名氏”

mysql> drop table if exists student;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> create table student(id int unique,name varchar(20) default "无名氏");
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into student(id) values(1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | 无名氏 |
    +------+--------+
    1 row in set (0.00 sec)

### 5.5 PRIMARY KEY：主键约束 ###

1.  主键是一条记录在表中的身份标识，唯一标记每一条数据
2.  mySQL要求主键所标识的列（属性）是唯一的（unique）、且不能为空（not null）
3.  一个表里只能有一个主键
4.  创建主键的时候，可以使用一个列作为主键，但是也可以用两个或者更多的列作为主键（复合主键）。但是一般项目中，一个表里就以一个列作为主键。

1.  对于整数类型的主键，常配搭自增长auto\_increment来使用。插入数据对应字段不给值时，使用最大值+1。也就是说mySQl自己会维护一个类似全局变量的自增主键，在我们设置该属性为整数类型的主键后，该属性插入是可以不赋值（即可以为null）此时，MySQL会根据当前主键的最大值加1来赋值为当前这一行记录的主键值。
2.  自增主键一般只适用于数据是单机部署的，此时自增主键一般是够用的，但是如果自增主键是分布式部署，一般是不适用自增主键的。

示例

mysql> drop table if exists student;
    Query OK, 0 rows affected (0.01 sec)
    ysql> create table student (id int primary key auto_increment,name varchar(20));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc student;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(20) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)
    
    mysql> insert into student(name) values("张三");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | 张三 |
    +----+------+
    1 row in set (0.00 sec)
    mysql> insert into student(id,name) values(null,"李四");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | 张三 |
    |  2 | 李四 |
    +----+------+
    2 rows in set (0.00 sec)
    
    mysql> insert into student values(100,"王五");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from student;
    +-----+------+
    | id  | name |
    +-----+------+
    |   1 | 张三 |
    |   2 | 李四 |
    | 100 | 王五 |
    +-----+------+
    3 rows in set (0.00 sec)
    
    mysql> select * from student;
    +-----+------+
    | id  | name |
    +-----+------+
    |   1 | 张三 |
    |   2 | 李四 |
    | 100 | 王五 |
    | 101 | 六六 |
    +-----+------+
    4 rows in set (0.00 sec)

### 5.6 FOREIGN KEY：外键约束 ###

语法：

foreign key (字段名) references 主表(列)

示例

1.  此时class表是空的，如果我在student表里面新增一个记录（这个记录中classId为1），那么这就会报错  
    示例：

mysql> insert into student values(123,"zhangsan",1);
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`base1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

其中`Cannot add or update a child row: a foreign key constraint fails (`base1`.`student`, CONSTRAINT`student\_ibfk\_1`FOREIGN KEY (`classId`) REFERENCES`class`(`classId`))`  
student中的classId受到class表里面的classId约束，在MySQL中我们将student表称之为子表，class表称之为父表。这句话翻译过来“不能增加或者修改一个子行：一个外键约束（constraint）失败”

mysql> insert into class values(null,"一班"),(null,"二班");
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from class;
    +---------+-----------+
    | classId | className |
    +---------+-----------+
    |       1 | 一班      |
    |       2 | 二班      |
    +---------+-----------+
    2 rows in set (0.00 sec)
    mysql> insert into student values(100,"张三",1),(101,"李四",2);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from student;
    +-----------+-------------+---------+
    | studentId | studentName | classId |
    +-----------+-------------+---------+
    |       100 | 张三        |       1 |
    |       101 | 李四        |       2 |
    +-----------+-------------+---------+
    2 rows in set (0.00 sec)

不但插入会影响，修改也是会影响，如果修改的classId的值并不在class表中，那么就会报错。

mysql> select * from student;
    +-----------+-------------+---------+
    | studentId | studentName | classId |
    +-----------+-------------+---------+
    |       100 | 张三        |       1 |
    |       101 | 李四        |       2 |
    +-----------+-------------+-
    
    mysql> update student set classId = 2 where studentId = 100;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from student;
    +-----------+-------------+---------+
    | studentId | studentName | classId |
    +-----------+-------------+---------+
    |       100 | 张三        |       2 |
    |       101 | 李四        |       2 |
    +-----------+-------------+---------+
    2 rows in set (0.00 sec)

1.  同样如果我去删除class表中的数据依旧会报错，因为这里面的数据在student表中关联着，不能轻易删除。

mysql> select * from student;
    +-----------+-------------+---------+
    | studentId | studentName | classId |
    +-----------+-------------+---------+
    |         1 | zhangsan    |       1 |
    |         2 | lisi        |       2 |
    +-----------+-------------+---------+
    2 rows in set (0.00 sec)
    
    mysql> delete from  class where classId = 1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`base1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

那么此时如果我此时确实是有删除这个班级记录的需求的呢？  
比如说现在班级改制，将二班这个记录删除，但是这student表不变，作为存根。那么这样我们就在class表里面在增加一个列，标记为删除，实际上是逻辑删除了。

[943d984743684e4fb90a2bd5ab1f8e5c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/0638232baad64e139c6ee4603ca9ae8d.png
[afb8b82a1c4840f7b3a0697e7497b410.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/7bbdb4b05d9a46c8968768f4e31de8f5.png
[02fc31313f5640708d480b3d46c07b8e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/93ce0f6688dc427893f2fb33977aa4f6.png
[3077ce07ab5f40b5b2921d0e54cccc1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/cec7f9685ee947bd865f2fb58f44ab5d.png
[852a8846238c440484ad44ca1224fef5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a081c8b99fe3456bbff4c3512e93ee8a.png
[98766ab84f514310976bf870b41d67c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/90282e6dc9ff4e53abec81dbed838008.png
[2e7cbdc21718481fb83d228baa3bd4b5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/fc0ba9382832405ba7a3897e73bd8051.png
[f027c48018374117829d0bf96b11556f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/636d76e509884139bf9710ba829e10eb.png
[b6318aa0cd9c43bc8a2f2a03cdf60d8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/3eb05f2d9c034efb826e1483a389e0d2.png
[c25929b56f5846969c23981baebddcce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/99fbcb909b4049dd89daa171c414ba7b.png
[de8db11c250046e98567b5eafad840da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/8bc8362e50a84995b861f234a4b96cc4.png
[54bbe081dad548ffbb04c8f9c8559d34.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/9605e1ca54604d6090ec4f131481d978.png
[33ab7ec5c3294a2f96b27cd8fc62b17e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/34ddf584a2934aa7934636e8df72aa93.png
[84f72a04cd974a849bf228e9187d9d4e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/99f1778cd266493884a66c44d5461762.png