MySQL数据表操作
数据表是数据库的重要组成部分，每一个数据库都是由若干个数据表组成的。换句话说，没有数据表就无法在数据库中存放数据。
创建数据表
在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。
创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程
-- 在 MySQL 中，可以使用 CREATE TABLE 语句创建表。-- 其语法格式为： CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];-- 其中，[表定义选项]的格式为： <列名1> <类型1> [,…] <列名n> <类型n>-- CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。-- 这里首先描述一个简单的新建表的例子，然后重点介绍 CREATE TABLE 命令中的一些主要的语法知识点。-- CREATE TABLE 语句的主要语法及使用说明如下： CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。 <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。 <表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。-- 数据表属于数据库，在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。mysql> CREATE TABLE test01(-> id int(11),-> name VARCHAR(25)-> );Query OK, 0 rows affected (0.02 sec)-- 语句执行后，便创建了一个名称为 test01 的数据表，使用 SHOW TABLES；语句查看数据表是否创建成功，如下所示。mysql> SHOW TABLES;+---------------+| Tables_in_db1 |+---------------+| test01|+---------------+2 rows in set (0.00 sec)#数据类型not null： 非空primary key： 主键（唯一且非空的）auto_increment： 自增（此列必须是：primary key或者unique key）unique key： 唯一键（单独的唯一的）default： 默认值unsigned： 非负数comment： 注释#案例：#建表mysql> CREATE TABLE student(-> id int unsigned primary key auto_increment comment '学生id',-> name varchar(10) not null comment '学会姓名',-> sex enum('男','女') default '男' comment '性别',-> age tinyint unsigned comment '年龄',-> cometime datetime default now() comment '入学时间',-> class varchar(12) not null comment '班级',-> status enum('0','1') default 1 comment '状态');Query OK, 0 rows affected (0.39 sec)#查看建表语句mysql> show create table student; | student | CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', `name` varchar(10) NOT NULL COMMENT '学会姓名', `sex` enum('男','女') DEFAULT '男' COMMENT '性别', `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄', `cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', `class` varchar(12) NOT NULL COMMENT '班级', `status` enum('0','1') DEFAULT '0' COMMENT '状态', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |1 row in set (0.00 sec)#插入数据mysql> INSERT INTO student(name,class) values('张三','高二3班');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO student(name,class) values('李四','高二3班');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO student(name,class) values('林五','高二3班');Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO student(name,sex,age,class) values('杨雪','女','16','高二3班');Query OK, 1 row affected (0.01 sec)#查看数据mysql> select * from student;+----+--------+------+------+---------------------+------------+--------+| id | name| sex | age | cometime| class | status |+----+--------+------+------+---------------------+------------+--------+| 1 | 张三| 男| NULL | 2021-09-28 11:26:42 | 高二3班| 0 || 2 | 李四| 男| NULL | 2021-09-28 11:27:33 | 高二3班| 0 || 3 | 林五| 男| NULL | 2021-09-28 11:28:11 | 高二3班| 0 || 4 | 杨雪| 女|16 | 2021-09-28 11:32:25 | 高二3班| 0 |+----+--------+------+------+---------------------+------------+--------+4 rows in set (0.00 sec)修改数据表
修改数据表的前提是数据库中已经存在该表。修改表指的是修改数据库中已经存在的数据表的结构。修改数据表的操作也是数据库管理中必不可少的，就像画素描一样，画多了可以用橡皮擦掉，画少了可以用笔加上。
1.修改表名
-- MySQL 通过 ALTER TABLE 语句来实现表名的修改。-- 语法格式： ALTER TABLE <旧表名> RENAME [TO] <新表名>;#案例:mysql> ALTER TABLE linux13 RENAME TO linux10;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+------------------+| Tables_in_test01 |+------------------+| linux10 |+------------------+1 row in set (0.00 sec)2.修改表字符集
-- MySQL 通过 ALTER TABLE 语句来实现表字符集的修改。其中，DEFAULT 为可选参数，使用与否均不影响结果。-- 语法格式： ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;#案例:mysql> SHOW CREATE TABLE linux10\G*************************** 1. row ***************************Table: linux10Create Table: CREATE TABLE `linux10` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `sex` enum('man','girl') DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)mysql> ALTER TABLE linux10 CHARACTER SET utf8 COLLATE utf8_unicode_ci;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Waings: 0mysql> SHOW CREATE TABLE linux10\G*************************** 1. row ***************************Table: linux10Create Table: CREATE TABLE `linux10` ( `id` int(11) DEFAULT NULL, `name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `sex` enum('man','girl') CHARACTER SET utf8mb4 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci1 row in set (0.00 sec)3.修改表字段
-- 在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。-- 其语法格式如下：ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；#案例:mysql> DESC linux10;+-------+--------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+--------------------+------+-----+---------+-------+| id| int(11)| YES | | NULL||| name | varchar(50)| YES | | NULL||| age| tinyint(2) | YES | | NULL||| sex| enum('man','girl') | YES | | NULL||+-------+--------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> ALTER TABLE linux10 CHANGE name name CHAR(11);Query OK, 3 rows affected (0.31 sec)Records: 3 Duplicates: 0 Waings: 0mysql> DESC linux10;+-------+--------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+--------------------+------+-----+---------+-------+| id| int(11)| YES | | NULL||| name | char(11)| YES | | NULL||| age| tinyint(2) | YES | | NULL||| sex| enum('man','girl') | YES | | NULL||+-------+--------------------+------+-----+---------+-------+4 rows in set (0.00 sec)4.修改字段数据类型
-- 修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。-- 语法格式： ALTER TABLE <表名> MODIFY <字段名> <数据类型>#其中： 表名：指要修改数据类型的字段所在表的名称； 字段名：指需要修改的字段； 数据类型：指修改后字段的新数据类型。#案例:mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| city | varchar(20) | YES | | NULL||| age| tinyint(2) | YES | | NULL||| addr | varchar(20) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> ALTER TABLE linux13 MODIFY name char(10);Query OK, 5 rows affected (0.12 sec)Records: 5 Duplicates: 0 Waings: 0mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | char(10)| YES | | NULL||| city | varchar(20) | YES | | NULL||| age| tinyint(2) | YES | | NULL||| addr | varchar(20) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+5 rows in set (0.00 sec)5.为已存在的表添加主键
#数据类型not null： 非空primary key： 主键（唯一且非空的）auto_increment： 自增（此列必须是：primary key或者unique key）unique key： 唯一键（单独的唯一的）default： 默认值unsigned： 非负数comment： 注释-- 语法格式： ALTER TABLE <数据表> ADD 数据类型(字段名称);#案例:mysql> alter table linux13 add primary key auto_increment(id);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc linux13;+-------+-------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id| int(11) | NO| PRI | NULL||| name | varchar(10) | YES | | NULL||| age| tinyint(2) | YES | | NULL||+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)删除数据表
在 MySQL 数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。
在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。
1.删除数据表
-- 使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下： DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]-- 对语法格式的说明如下：-- 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。-- IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（waing）。-- 两点注意：-- • 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。-- • 表被删除时，用户在该表上的权限不会自动删除。-- 案例：mysql> drop table linux13;Query OK, 0 rows affected (0.00 sec)2.删除字段
-- 删除字段是将数据表中的某个字段从表中移除。-- 格式： ALTER TABLE <表名> DROP <字段名>;#案例:mysql> DESC linux10;+-------+--------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+--------------------+------+-----+---------+-------+| id| int(11)| YES | | NULL||| name | varchar(10)| YES | | NULL||| age| tinyint(2) | YES | | NULL||| sex| enum('man','girl') | YES | | NULL||+-------+--------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> ALTER TABLE linux10 DROP name;Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Waings: 0mysql> DESC linux10;+-------+--------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+--------------------+------+-----+---------+-------+| id| int(11)| YES | | NULL||| age| tinyint(2) | YES | | NULL||| sex| enum('man','girl') | YES | | NULL||+-------+--------------------+------+-----+---------+-------+3 rows in set (0.00 sec)增加字段
MySQL 数据表是由行和列构成的，通常把表的“列”称为字段（Field），把表的“行”称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。
1.在开头位置添加字段
-- MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字。-- 格式: ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST; -- 案例:mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| age| tinyint(2) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> alter table linux13 add address varchar(20) first;Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc linux13;+---------+-------------+------+-----+---------+----------------+| Field| Type| Null | Key | Default | Extra |+---------+-------------+------+-----+---------+----------------+| address | varchar(20) | YES | | NULL||| id | int(11) | NO| PRI | NULL| auto_increment || name| varchar(10) | YES | | NULL||| age | tinyint(2) | YES | | NULL||+---------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)2.在中间位置添加字段
-- MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字。-- 格式: ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;-- 案例:mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| age| tinyint(2) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)mysql> -- AFTER 的作用是将新字段添加到某个已有字段后面。mysql> alter table linux13 add city varchar(20) after name;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| city | varchar(20) | YES | | NULL||| age| tinyint(2) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)3.在末尾位置添加字段
-- 一个完整的字段包括字段名、数据类型和约束条件。-- 格式: ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];-- 对语法格式的说明如下： <表名> 为数据表的名字 <新字段名> 为所要添加的字段的名字 <数据类型> 为所要添加的字段能存储数据的数据类型 [约束条件] 是可选的，用来对添加的字段进行约束-- 案例:mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| city | varchar(20) | YES | | NULL||| age| tinyint(2) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)mysql> alter table linux13 add addr varchar(20);Query OK, 0 rows affected (0.23 sec)Records: 0 Duplicates: 0 Waings: 0mysql> desc linux13;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO| PRI | NULL| auto_increment || name | varchar(10) | YES | | NULL||| city | varchar(20) | YES | | NULL||| age| tinyint(2) | YES | | NULL||| addr | varchar(20) | YES | | NULL||+-------+-------------+------+-----+---------+----------------+5 rows in set (0.00 sec)4.是否允许为空
-- NOT NULL 设置是否允许字段为空-- 示例：mysql> create table pm1(-> id int primary key auto_increment,-> name varchar(20) not null-> );Query OK, 0 rows affected (0.10 sec)#报错,提示name字段必须要有值mysql> insert into pm1(id) values (1);ERROR 1364 (HY000): Field 'name' doesn't have a default value'mysql> insert into pm1 values (1,'张三');Query OK, 1 row affected (0.00 sec)5.默认值
-- default 给字段设置一个默认值，当字段没有添加任何值的时候，使用默认值进行填充。-- 示例：mysql> create table pm2(-> id int primary key auto_increment,-> name varchar(20) not null,-> sex enum('男','女') default '男' -> );Query OK, 0 rows affected (0.01 sec)mysql> insert into pm2(id,name) values (1,'张三');Query OK, 1 row affected (0.00 sec)mysql> select * from pm2;+----+--------+------+| id | name| sex |+----+--------+------+| 1 | 张三| 男|+----+--------+------+1 row in set (0.00 sec)6.字段注释
-- comment 就是给字段一个注释，有利于后期维护的时候快速理解字段含义。-- 示例：mysql> create table student( -> id int primary key auto_increment comment '学生id',-> name varchar(20) not null comment '学生姓名',-> sex enum('男','女') default '男' comment '学生性别'-> );Query OK, 0 rows affected (0.00 sec)mysql> show create table student\G*************************** 1. row ***************************Table: studentCreate Table: CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id', `name` varchar(20) NOT NULL COMMENT '学生姓名', `sex` enum('男','女') DEFAULT '男' COMMENT '学生性别', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)表数据的增删改查
表相当于文件，表中保存的其实是真正的数据。
1.增加表数据
-- 增加数据其实就是向表中插入数据，或者是向表中添加数据。-- 格式： INSERT INTO <表名> (字段1,字段2,...字段n) VALUES (数据1,数据2...数据n);#案例:#创建班级表mysql> CREATE TABLE class(-> id int,-> name varchar(10),-> age tinyint(2),-> sex enum('男','女')-> );Query OK, 0 rows affected (0.39 sec)#插入数据mysql> INSERT INTO class (id,name,age,sex) VALUES (1,'张三',18,'男');Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO class (id,name,age,sex) VALUES (2,'李四',17,'男'),(3,'小米',18,'女');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Waings: 0#当所有的字段都涉及到了,那么 < 表 >的括号中的指端可以省略,插入的字段没有全部涉及到,则必须指定字段mysql> INSERT INTO class VALUES (4,'王五',19,'男');Query OK, 1 row affected (0.00 sec)#查看数据mysql> SELECT * FROM class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||3 | 小米|18 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)2.查询数据
#表中保存了很多数据，其目的就是为了使用的时候可以立即查询出来，所以数据库的查询语句的使用率是其他语句的数倍。下面我们介绍查询语法：#格式：SELECT [查询字段] FROM [表名] [条件语句] [显示规则] [规则条件]#案例1:#-- * 默认代表所有的字段mysql> SELECT * FROM class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||3 | 小米|18 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)#-- 查询指定字段mysql> SELECT name FROM class;+--------+| name|+--------+| 张三|| 李四|| 小米|| 王五|+--------+4 rows in set (0.00 sec)#或者使用where条件mysql> SELECT * FROM class where id >=1;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||3 | 小米|18 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)#案例2: 多个表之间如何查询,多表联查mysql> -- 建立学生表mysql> CREATE TABLE student(id int,name varchar(20));Query OK, 0 rows affected (0.39 sec)mysql> -- 建立成绩表mysql> CREATE TABLE score(id int,mark int);Query OK, 0 rows affected (0.01 sec)mysql> -- 插入数据mysql> INSERT student VALUES(1,'张三'),(2,'李四'),(3,'小米');Query OK, 3 rows affected (0.31 sec)Records: 3 Duplicates: 0 Waings: 0 mysql> INSERT score VALUES(1,80),(2,90),(3,97);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Waings: 0mysql> -- 查询张三的成绩-- 1.方法一:mysql> SELECT student.name,score.mark FROM student,score WHERE student.id=1 and score.id=1;+--------+------+| name| mark |+--------+------+| 张三|80 |+--------+------+1 row in set (0.00 sec)-- 2.方法二:mysql> SELECT student.name,score.mark FROM student,score WHERE student.id=score.id and name='张三'; +--------+------+| name| mark |+--------+------+| 张三|80 |+--------+------+1 row in set (0.00 sec)3.条件语句 where
#条件语句是用来筛选数据的，主要用于查询某些数据# 判断条件> ：大于< : 小于= ：等于!= 和 <> : 不等于>=: 大于等于<=: 小于等于like : 模糊查询and: 并且or：或者#案例1: 根据姓名查询张三和小米的信息mysql> SELECT * FROM class where name='张三' or name='小米';+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||3 | 小米|18 | 女|+------+--------+------+------+2 rows in set (0.00 sec)#案例2: 假如李四id=2,查询李四的信息mysql> SELECT * FROM class where id=2;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|2 | 李四|17 | 男|+------+--------+------+------+1 row in set (0.00 sec)#案例3: 查询性别为女性的信息mysql> SELECT * FROM class where sex='女';+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|3 | 小米|18 | 女|+------+--------+------+------+1 row in set (0.00 sec)#案例4: -- 模糊查询中的%类似于正则表达式中的*，代表匹配所有的内容。-- 前置% 代表的是以什么结尾 和 后置% 代表以什么开头，如果两者皆有，则表示包含。mysql> SELECT * FROM class where name like '%小%';+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|3 | 小米|18 | 女|+------+--------+------+------+1 row in set (0.00 sec)#案例5: 查询id大于2,且小于5的信息mysql> SELECT * FROM class where id>2 and id<5;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|3 | 小米|18 | 女||4 | 王五|19 | 男|+------+--------+------+------+2 rows in set (0.00 sec)#案例6: 查询id小于等于2,且大于等于4的信息mysql> SELECT * FROM class where id<=2 or id>=4;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||4 | 王五|19 | 男|+------+--------+------+------+3 rows in set (0.00 sec)4.排序 order by
#排序，顾名思义就是按照某种规则查询出数据，默认情况下是按照从前到后查询数据，但是也可以通过排序语法查询出相关的数据。#格式：SELECT [查询字段] FROM [表名] [显示规则]-- 排序的规则ASC：默认，正向排序DESC ：反向排序#案例1: 按照年龄的从大到小的顺序查询mysql> SELECT * FROM class order by age DESC;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|4 | 王五|19 | 男||1 | 张三|18 | 男||3 | 小米|18 | 女||2 | 李四|17 | 男|+------+--------+------+------+4 rows in set (0.00 sec)#案例2: id从大到小查询mysql> SELECT * FROM class order by id DESC;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|4 | 王五|19 | 男||3 | 小米|18 | 女||2 | 李四|17 | 男||1 | 张三|18 | 男|+------+--------+------+------+4 rows in set (0.00 sec)5.去重 DISTINCT
-- 去重，顾名思义就是在查询的数据中过滤掉重复数据，默认会显示所有的数据，可以使用出重语法实现去掉重复数据。-- 格式: SELECT DISTINCT [字段] FROM [表名]#案例:mysql> SELECT DISTINCT sex FROM class;+------+| sex |+------+| 男|| 女|+------+2 rows in set (0.00 sec)6.别名
# 别名，顾名思义就是将字段设置一个新的名字。#案例：要求计算出当前表中所有的行数-- 计算行数的函数是count() --mysql> SELECT count(id) FROM class;+-----------+| count(id) |+-----------+| 4 |+-----------+1 row in set (0.00 sec)#设置别名mysql> SELECT count(id) '行数' FROM class;+--------+| 行数|+--------+| 4 |+--------+1 row in set (0.00 sec)7.常用的函数
# 函数，就是具备某种功能的工具。那么在数据库中使用函数就是通过函数实现某种具体的功能。#案例1：计算出当前数据有多少行-- 计算行数的函数是count() --mysql> SELECT count(id) FROM class;+-----------+| count(id) |+-----------+| 4 |+-----------+1 row in set (0.00 sec)#案例2: 计算表中所有年龄的总和-- 计算和的函数是sum() --mysql> SELECT sum(age) FROM class;+----------+| sum(age) |+----------+|72 |+----------+1 row in set (0.00 sec)#案例3: 计算表中所有年龄的平均值-- 计算平均值的函数是avg() --mysql> SELECT AVG(age) FROM class;+----------+| AVG(age) |+----------+| 18.0000 |+----------+1 row in set (0.00 sec)8.having语句
# having也是一个条件判断语句，类似于前面所讲的where语句是用于做条件判断的，但是有所不同的是where的条件作用于查询之前的字段，having是作用于查询之后的语句。#案例1: 所有男性,大于17岁的有哪些mysql> SELECT * FROM class where sex='男' HAVING age >17;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||4 | 王五|19 | 男|+------+--------+------+------+2 rows in set (0.00 sec)#案例2: 查询出一个月消费在2000元以上的同学mysql> -- 创建消费表mysql> CREATE TABLE consumption(-> id int,-> name varchar(15),-> money DECIMAL (8,2)-> );Query OK, 0 rows affected (0.39 sec)#插入数据mysql> INSERT INTO consumption VALUES (1,'小米',500.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (1,'小米',500.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (1,'小米',500.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (1,'小米',500.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (1,'小米',500.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (2,'张三',600.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (2,'张三',600.01);Query OK, 1 row affected (0.10 sec)mysql> INSERT INTO consumption VALUES (2,'张三',600.01);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO consumption VALUES (2,'张三',600.01);Query OK, 1 row affected (0.00 sec)mysql> -- 查询出每个人的消费超过2000元的#小米的总消费额mysql> SELECT name,SUM(money) mon FROM consumption WHERE name='小米' HAVING mon >2000;+--------+---------+| name| mon |+--------+---------+| 小米| 2500.05 |+--------+---------+1 row in set (0.01 sec)#张三的总消费额mysql> SELECT name,SUM(money) mon FROM consumption WHERE name='张三' HAVING mon >2000;+--------+---------+| name| mon |+--------+---------+| 张三| 2400.04 |+--------+---------+1 row in set (0.00 sec)#这里是使用的 where 和 having 查询,还有下面使用 group by 的方法9.分组 group by
# group_by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组#语法格式:select 字段 from 表名 where 条件groupby字段#或者select 字段 from 表名 group by 字段 having 过滤条件#注意：对于过滤条件，可以先用where，再用group by或者是先用group by，再用having#案例1: 查询消费总额大于2500的人的信息mysql> -- 使用group by 和 having 查询mysql> SELECT id,name,SUM(money) mon FROM consumption GROUP BY id,name,money HAVING mon >2500;+------+--------+---------+| id| name| mon |+------+--------+---------+|1 | 小米| 2500.05 |+------+--------+---------+1 row in set (0.00 sec)#案例2: mysql> -- 使用group by 和 where 查询mysql> SELECT id,name,SUM(money) FROM consumption WHERE id>=1 GROUP BY id,name,money;+------+--------+------------+| id| name| SUM(money) |+------+--------+------------+|1 | 小米|2500.05 ||2 | 张三|2400.04 |+------+--------+------------+2 rows in set (0.00 sec)mysql> SELECT id,name,SUM(money) FROM consumption WHERE name='张三' GROUP BY id,name,money;+------+--------+------------+| id| name| SUM(money) |+------+--------+------------+|2 | 张三|2400.04 |+------+--------+------------+1 row in set (0.00 sec)#注意:where判断无法识别别名,且无法识别组函数mysql> SELECT id,name,SUM(money) mon FROM consumption WHERE mon >2000 GROUP BY id,name,money;;ERROR 1054 (42S22): Unknown column 'mon' in 'where clause'ERROR: No query specifiedmysql> SELECT id,name,SUM(money) FROM consumption WHERE SUM(money)>2000 GROUP BY id,name,money;ERROR 1111 (HY000): Invalid use of group function10.修改表数据
#在数据表中存储的数据时常都会有所更改，例如：是否单身，是今天是否国庆。所以，怎么会随着一些事务的推移从而需要修改表数据，这个时候我们就需要用到MySQL UPDATE语句。#语法格式: UPDATE <表名> SET [修改的内容] [条件]; #案例: 修改单条数据(将数据表中的小米年龄改为16) mysql> SELECT * from class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||3 | 小米|18 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)mysql> UPDATE class SET age=16 WHERE name='小米';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Waings: 0mysql> SELECT * from class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|17 | 男||3 | 小米|16 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)#案例2: 修改多条数据(李四年龄修改19,性别女)mysql> UPDATE class SET age=19,sex='女' WHERE name='李四';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Waings: 0mysql> SELECT * from class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||2 | 李四|19 | 女||3 | 小米|16 | 女||4 | 王五|19 | 男|+------+--------+------+------+4 rows in set (0.00 sec)11.删除表数据
#删除表数据，就是当数据表中有错误或者没有任何价值的数据时，通过SQL语句去将这部分数据删除。#语法格式:DELETE FROM <表名> [条件];#案例1: 删除linux10表所有数据(谨慎使用)mysql> DELETE FROM linux10;Query OK, 3 rows affected (0.01 sec)mysql> SELECT * FROM linux10;Empty set (0.00 sec)#案例2: 删除一部分数据mysql> DELETE FROM class WHERE id=2;Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM class;+------+--------+------+------+| id| name| age | sex |+------+--------+------+------+|1 | 张三|18 | 男||3 | 小米|16 | 女||4 | 王五|19 | 男|+------+--------+------+------+3 rows in set (0.00 sec)#案例3: 清空数据表mysql> DELETE FROM linux10;Query OK, 3 rows affected (0.01 sec)mysql> TRUNCATE TABLE linux10;Query OK, 0 rows affected (0.01 sec)-- DELETE 和 TRUNCATE 之间的区别？ DELETE删除的是数据，不删除索引，TRUNCATE不仅删除数据而且删除索引。 #总结：1、在速度上，一般来说，drop> truncate > delete2、truncate无法通过binlog回滚。truncate会清空所有数据且执行速度很快。truncate不能对有外键约束引用的表使用。执行truncate需要drop权限，不建议给账号drop权限。执行truncate前一定要再三检查确认，最好提前备份下表数据。3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；作者：泽野
来源链接：https://www.cnblogs.com/backz/p/15351346.html
版权声明：
1、JavaClub（https://www.javaclub.cn）以学习交流为目的，由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容，包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网，本站不享有版权，版权归原作者所有。
2、本站提供的内容仅用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律的规定，不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容，请及时通知本站（javaclubcn@163.com），我们将第一时间核实后及时予以删除。
