MySQL数据库
MySQL的使用
●MySQL服务器的操作
1.进入MySQL数据库:mysql -h 服务器地址 -u 用户名 -p 密码;
mysql -h localhost -u root -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.34-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2.查看MySQL版本信息:select version();
mysql> select version(); +------------+ | version() | +------------+ | 5.7.34-log | +------------+ 1 row in set (0.04 sec)
3.查看MySQL基本配置信息:status;
mysql> status; -------------- mysql Ver 14.14 Distrib 5.7.34, for Win64 (x86_64) Connection id: 11 Current database: Current user: root@localhost SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Using delimiter: ; Server version: 5.7.34-log MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 5 days 10 hours 59 min 59 sec Threads: 1 Questions: 896 Slow queries: 0 Opens: 314 Flush tables: 1 Open tables: 238 Queries per second avg: 0.001 --------------
4.查看MySQL当前连接数show variables like '%connection%';
mysql> show variables like '%connection%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | character_set_connection | gbk | | collation_connection | gbk_chinese_ci | | max_connections | 151 | | max_user_connections | 0 | +--------------------------+----------------+ 4 rows in set, 1 warning (0.00 sec)
5.查看当前时间:select now();
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-17 20:10:00 | +---------------------+ 1 row in set (0.04 sec)
6.查看超时:show variables like '%timeout%';
mysql> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 28800 | +-----------------------------+----------+ 13 rows in set, 1 warning (0.00 sec)
●MySQL数据库
1.查看服务器有多少数据库:show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.创建数据库:create database 名称;
mysql> create database testdev;
Query OK, 1 row affected (0.00 sec)
3.删除数据库：drop database 数据库名称
mysql> drop database testdev; Query OK, 0 rows affected (0.00 sec)
4.进入数据库:use 数据库名称;
mysql> use testdev; Database changed
5.查看数据库所在本地磁盘目录:show variables like '%datadir%';
mysql> show variables like '%datadir%'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | datadir | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ | +---------------+---------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
6.查看当前使用的是哪个数据库:select database();
mysql> select database(); +------------+ | database() | +------------+ | employees | +------------+ 1 row in set (0.00 sec)
●MySQL表
1.表的数据类型
1.1整型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
| TINYINT | 1字节 | (-128,127) | (0,225) | 小整数值 |
| SMALLINT | 2字节 | (-32768,32767) | (0,65535) | 大整数值 |
| MEDIUMINT | 3字节 | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT或INTEGER | 4字节 | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8字节 | (-9233372036854775808，9223372036854775807) | (0，18446744073709551615) | 极大整数值 |
整型（N）形式：
我们会碰到有些定义整型的写法是int(11)，这种写法从我个人开发的角度看我认为是没有多大用，不过作为一个知识点做一下讲解吧。
int(N)我们只需要记住两点：
- 无论N等于多少，int永远占4个字节
- N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效
如：
drop table if exists test_int_width; create table test_int_width ( a int(5), b int(5) unsigned, c int(5) unsigned zerofill, d int(8) unsigned zerofill ) engine=innodb charset=utf8; insert into test_int_width values(1, 1, 1, 1111111111); select * from test_int_width;
得到的结果 ：
+------+------+-------+------------+ | a | b | c | d | +------+------+-------+------------+ | 1 | 1 | 00001 | 1111111111 | +------+------+-------+------------+
1.2浮点类
| 数据类型 | 字节数 | 备注 |
| float | 4 | 单精度浮点型 |
| double | 8 | 双精度浮点型 |
float(M,D)、double(M、D)的用法规则：
- D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24
- M表示浮点型数据总共的位数，D=2则表示总共支持五位，即小数点前只支持三位数，所以我们并没有看到1000.23、10000.233、100000.233这三条数据的插入，因为插入都报错了
当我们不指定M、D的时候，会按照实际的精度来处理。
如：
drop table if exists test_float; create table test_float ( num float(5, 2) ) engine=innodb charset=utf8; insert into test_float values(1.233); insert into test_float values(1.237); insert into test_float values(10.233); insert into test_float values(100.233); insert into test_float values(1000.233); insert into test_float values(10000.233); insert into test_float values(100000.233);
输出的结果为：
mysql> insert into test_float values(1000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into test_float values(10000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into test_float values(100000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from test_float; +--------+ | num | +--------+ | 1.23 | | 1.24 | | 10.23 | | 100.23 | +--------+ 4 rows in set (0.00 sec)
1.3日期和时间类型
| 数据类型 | 字节数 | 格式 | 备注 |
| date | 3 | yyyy-MM-dd | 存储日期值 |
|
time |
3 | HH:mm:ss | 存储时分秒 |
| year | 1 | yyyy | 存储年 |
| datetime | 8 | yyyy-MM-dd HH:mm:ss | 存储日期+时间 |
| timestamp | 4 | yyyy-MM-dd HH:mm:ss | 存储日期+时间，可作时间戳 |
如：
mysql> create table test_time ( -> date_value date, -> time_value time, -> year_value year, -> datetime_value datetime, -> timestamp_value timestamp -> ) engine=innodb charset=utf8; Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_time values(now(), now(), now(), now(), now());
输出：
mysql> select * from test_time; +------------+------------+------------+---------------------+---------------------+ | date_value | time_value | year_value | datetime_value | timestamp_value | +------------+------------+------------+---------------------+---------------------+ | 2021-11-22 | 19:45:52 | 2021 | 2021-11-22 19:45:52 | 2021-11-22 19:45:52 | +------------+------------+------------+---------------------+---------------------+ 1 row in set (0.00 sec)
1.4字符串（字符）类型
| 数据类型 | 大小（范围） | 用途 |
| char(M)[BINARY] |
M个字节，0 <= M <= 255 (L为固定的=255，不够补空格) |
定长字符串；CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。CHAR值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键词 |
| varchar(M) [BINARY] |
L+1个字节，其中L <= M 且0 <= M <= 65535（MySQL5.0之前都是最大255) |
变长字符串；VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同 |
| tinyblob，tinytext |
L+1个字节，其中L < 28-1 (255) |
tinyblob：不超过 255 个字符的二进制字符串；tinytext：短文本字符串 |
| blob,text |
L+2个字节，其中L < 216-1 (65535) |
blob：二进制形式的长文本数据，在分类和比较时BLOB 类型区分大小写;text：长文本数据，在分类和比较时TEXT 不区分大小写 |
| mediumblob,mediumtext |
L+3个字节，其中L < 224-1 |
mediumblob：二进制形式的中等长度文本数据；mediumtext ：中等长度文本数据， |
| longblob,longtext |
L+4个字节，其中L < 232-1 |
longblob：二进制形式的极大文本数据 ；longtext ：极大文本数据 |
| enum('value1','value2',...) |
1或2个字节，取决于枚举值的个数(最多65,535个值) |
|
| set('value1','value2',...) |
1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员) |
2.表结构的操作
2.1表的创建:
2.1.1新建表
CREATE TABLE 表名 ( 属性名 数据类型 [完整约束条件], 属性名 数据类型 [完整约束条件], ... ... 属性名 数据类型 [完整约束条件] );
如要创建一个有属性id跟name的user表：
mysql> create table user( -> id int(11), -> name varchar(20) -> ); Query OK, 0 rows affected (0.06 sec)
2.1.2克隆表
- 首先查到表的信息
mysql> show create table salaries \G; *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec)
- 分支表结果信息
- 更改名称复制代码即可
2.1.3表的约束条件：
|
约束条件 |
说明 |
|
PRIMARY KEY |
标识该属性为该表的主键，可以唯一的标识对应的元组 |
|
FOREIGN KEY |
标识该属性为该表的外键，是与之联系某表的主键 |
| NOT NULL |
标识该属性不能为空 |
|
UNIQUE |
标识该属性的值是唯一的 |
| AUTO_INCREMENT |
标识该属性的值是自动增加，这是MySQL的SQL语句的特色 |
| DEFAULT |
为该属性设置默认值 |
- 设置表的主键
单字段主键格式：属性名 数据类型 PRIMARY KEY 多字段主键格式：PRIMARY KEY(属性名1,属性名2....属性名n)
- 设置表的外键 CONSTRAINT 外键别名 FOREIGN KEY(属性1,属性2,....属性n) REFERENCES 表名(属性1',属性2',...属性n')
-
设置表的非空约束 属性名 数据类型 NOT NULL
- 设置表的唯一性约束 （就是这个属性的值是不能重复的） 格式：属性名 数据类型 UNIQUE
- 设置表的属性值自动增加（AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT和BIGINT），在默认的情况下，该字段的值是从1开始自增） 属性名 数据类型 AUTO_INCREMENT
-
设置表的属性的默认值 属性名 数据类型 DEFAULT 默认值
如：
mysql> CREATE TABLE student3 ( -> id int PRIMARY KEY AUTO_INCREMENT, -> teacher_id int UNIQUE, -> name varchar(20) NOT NULL, -> sex varchar(10) DEFAULT 'male' -> ); Query OK, 0 rows affected (0.06 sec) mysql> desc student3; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | teacher_id | int(11) | YES | UNI | NULL | | | name | varchar(20) | NO | | NULL | | | sex | varchar(10) | YES | | male | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
2.2表的查询
2.2.1查询表信息：show create table 表名称 \G；
mysql> show create table student3 \G; *************************** 1. row *************************** Table: student3 Create Table: CREATE TABLE `student3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teacher_id` int(11) DEFAULT NULL, `name` varchar(20) NOT NULL, `sex` varchar(10) DEFAULT 'male', PRIMARY KEY (`id`), UNIQUE KEY `teacher_id` (`teacher_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
2.2.2查询表数据信息：desc 表名称；
mysql> desc student3; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | teacher_id | int(11) | YES | UNI | NULL | | | name | varchar(20) | NO | | NULL | | | sex | varchar(10) | YES | | male | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
2.3表名称的修改
①alter table 旧表名 rename 新表名
mysql> alter table student3 rename student4; Query OK, 0 rows affected (0.04 sec)
② rename table 旧表名 to 新表名
mysql> rename table user to userinfo; Query OK, 0 rows affected (0.05 sec)
2.4表的删除
drop table 表名
mysql> show tables; #查询数据库现有的表 +-------------------+ | Tables_in_testdev | +-------------------+ | dept_emp | | salaries | | student4 | | test_float | | test_int_width | | test_time | | test_tinyint | | userinfo | +-------------------+ 8 rows in set (0.00 sec) mysql> drop table userinfo; #删除表userinfo Query OK, 0 rows affected (0.04 sec) mysql> show tables; #查询删除之后剩余的表 +-------------------+ | Tables_in_testdev | +-------------------+ | dept_emp | | salaries | | student4 | | test_float | | test_int_width | | test_time | | test_tinyint | +-------------------+ 7 rows in set (0.00 sec)
3.表的管理
3.1修改字段名称
格式：alter table 表名称 change 原字段名称 新字段名称 新字段类型
mysql> desc user; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | city | varchar(20) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> alter table user change city to_date date; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
3.2修改字段数据类型
格式：alter table 表名称 modify 字段名称 新字段类型
mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> alter table user modify id varchar(20); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
3.3添加字段
3.3.1添加字段（默认添加到最后） alter table 表名 add 字段名称 字段数据类型
mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> alter table user add city varchar(10) ; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
3.3.2添加字段在某一字段之后 alter table 表名 add 字段名称 字段数据类型 after 某一字段名称
mysql> desc user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table user add from_date date after name; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | from_date | date | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
3.3.3添加字段在第一个 alter table 表名 add 字段名称 字段数据类型 first
mysql> desc user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | from_date | date | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table user add stu_id varchar(20) first; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | stu_id | varchar(20) | YES | | NULL | | | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | from_date | date | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
3.4.删除字段
alter table 表名 drop 列名
mysql> alter table user add stu_id varchar(20) first; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | stu_id | varchar(20) | YES | | NULL | | | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | from_date | date | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> alter table user drop stu_id; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | from_date | date | YES | | NULL | | | to_date | date | YES | | NULL | | | salary | float | YES | | NULL | | | city | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
4.表数据的管理
4.1插入 insert
4.1.1单条数据的的插入
insert into 表名 values （插入的数据）
mysql> insert into user values(1,"tang","2020-03-13","9999-01-01",8997.9,"西安"); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | +------+------+------------+------------+--------+------+
4.1.2多条数据的插入
insert into 表名 values （插入的数据），（插入的数据），（插入的数据）；
mysql> insert into user values -> (2,"zhangsan","2019-04-12","9999-01-01",8979.7,"北京"), -> (3,"lisi","2021-07-22","9999-01-01",6999.7,"杭州"), -> (4,"wangwu","2018-07-22","9999-01-01",9545.8,"咸阳"); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 6999.7 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 咸阳 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.01 sec)
4.2 修改 update
4.2.1 修改字段某一数据
update 表名 set 字段名 修改之后的数据 where 条件(这里的条件是单一的)
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 6999.7 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 咸阳 | +------+----------+------------+------------+--------+------+
如表user 我们想要修改id为4的字段city数据为“上海”
mysql> update user set city ="上海" where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
修改之后我们再执行select查询语句查看表user，"咸阳"以及更改为"上海"
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 6999.7 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 上海 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
4.2.2修改多个数据
①update 表名 set 字段名 修改之后的数据 where 条件 and 条件 （and表示两个或多个条件都要符合，只要其中一个条件不符合就不会执行）
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 6999.7 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 上海 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
我们想要修改id=4，name=lisi的salary为9999.9
执行：
mysql> update user set salary=9999.9 where name="lisi" and id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
执行之后我们再去查询表user
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 9999.9 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 上海 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
②update 表名 set 字段名 修改之后的数据 where 条件 or 条件 （or表示两个或多个条件，只要其中一个条件符合就可以）
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 9999.9 | 杭州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 上海 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
如我们像修改name为lisi或者id为4的city为“广州“
执行：
mysql> update user set city="广州" where name="lisi" or id=4; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0
我们查询更改之后的表数据：
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 9999.9 | 广州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 广州 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
4.3 删除 delete
4.3.1单条件删除
delete from 表名 where 删除条件
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 3 | lisi | 2021-07-22 | 9999-01-01 | 9999.9 | 广州 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 广州 | +------+----------+------------+------------+--------+------+ 4 rows in set (0.00 sec)
如我们像删除name为lisi的表数据
delete from user where name="lisi"
查询删除之后的表数据：
mysql> select * from user; +------+----------+------------+------------+--------+------+ | id | name | from_date | to_date | salary | city | +------+----------+------------+------------+--------+------+ | 1 | tang | 2020-03-13 | 9999-01-01 | 8997.9 | 西安 | | 2 | zhangsan | 2019-04-12 | 9999-01-01 | 8979.7 | 北京 | | 4 | wangwu | 2018-07-22 | 9999-01-01 | 9545.8 | 广州 | +------+----------+------------+------------+--------+------+ 3 rows in set (0.00 sec)
4.3.2 多条件删除
delete from 表名 where 多个条件（这里的条件可以是and关系，也可以使or关系）
mysql> delete from user where id=4 and city="广州"; Query OK, 1 row affected (0.01 sec) mysql> delete from user where id=1 or name="zhangsan"; Query OK, 2 rows affected (0.01 sec)
浙公网安备 33010602011771号