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: linux10
Create 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=utf8mb4
1 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 Warnings: 0
mysql> SHOW CREATE TABLE linux10\G
*************************** 1. row ***************************
Table: linux10
Create 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_ci
1 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 Warnings: 0
mysql> 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 Warnings: 0
mysql> 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 Warnings: 0
mysql> 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 语句可以顺利执行,但是会发出警告(warning)。
-- 两点注意:
-- • 用户必须拥有执行 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 Warnings: 0
mysql> 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 Warnings: 0
mysql> 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 Warnings: 0
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)
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 Warnings: 0
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)
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: student
Create 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=utf8mb4
1 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 Warnings: 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 Warnings: 0
mysql> INSERT score VALUES(1,80),(2,90),(3,97);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> -- 查询张三的成绩
-- 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 条件 group by 字段
#或者
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 specified
mysql> SELECT id,name,SUM(money) FROM consumption WHERE SUM(money)>2000 GROUP BY id,name,money;
ERROR 1111 (HY000): Invalid use of group function
10.修改表数据
#在数据表中存储的数据时常都会有所更改,例如:是否单身,是今天是否国庆。所以,怎么会随着一些事务的推移从而需要修改表数据,这个时候我们就需要用到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 Warnings: 0
mysql> 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 Warnings: 0
mysql> 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 > delete
2、truncate无法通过binlog回滚。
truncate会清空所有数据且执行速度很快。
truncate不能对有外键约束引用的表使用。
执行truncate需要drop权限,不建议给账号drop权限。
执行truncate前一定要再三检查确认,最好提前备份下表数据。
3、如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;
如果想删除表,当然用drop;
如果想保留表而将所有数据删除,如果和事务无关,用truncate即可;