文章目录
一、环境说明
操作系统:win10专业版
MySQL版本:8.0.19
二、操作数据库
2.1、查看所有数据库
在命令行终端或是客户端,执行下面的脚本:
# MySQL中的所有数据库
SHOW DATABASES;
打印所有的数据库如下:
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| nacos |
| performance_schema |
| seata |
| sys |
+-----------------------+
这里许多人会产生误区:
认为这条命令会返回MySQL中所有的数据库名。
其实这里需要考虑当前登录账号的权限因素。
因为如果账号授权时
只允许访问一个数据库
,那么该语句执行后,也只会显示一个数据库,虽然可能MySQL中实际还有其他数据库。
2.2、创建数据库
使用下面的脚本,可以创建自己的数据库:
-- 数据库的名称为:my_database
CREATE DATABASE my_database;
能创建数据库的前提,是当前账号被授予CREATE
权限。
查看数据库的定义:
-- 在创建数据库前增加了SHOW
SHOW CREATE DATABASE my_database;
结果如下:
+-------------+---------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-------------+---------------------------------------------------------------------------------------------------------+
| my_database | CREATE DATABASE `my_database` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
数据库的字符集和排序规则如下:
2.3、使用数据库
切换到新建的数据库:my_database:
-- 切换到新建的数据库
USE my_database;
2.3.1、查看当前库的所有表
可以通过命令查看当前库中的所有表:
-- 查看当前库中的所有表
SHOW TABLES;
-- 没有表
Empty set
我们可以看出这是一个全新的数据库,尚未创建任何数据表。那么,我们是不是,可以尝试创建一张表格。
2.3.1、创建表
1.数据表的创建
可以使用如下语句创建数据表:
CREATE TABLE `t_user` (
`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户id',
`name` varchar(50) COMMENT '姓名',
`phone` varchar(20) COMMENT '电话',
`status` int DEFAULT '10' COMMENT '状态 10启用/20禁用',
`valid` int DEFAULT '1' COMMENT '是否有效'
);
重点是字段id,它满足如下条件:
- 主键:字段定义时使用
PRIMARY KEY
- 自增列:字段定义时使用
AUTO_INCREMENT
- 非空列:字段定义时使用
NOT NULL
如果一个列有默认值,需要在字段类型后增加DEFAULT ${默认值}
。
2.查看数据表的定义
如果你想知道一张表的创建脚本,可以这样获得:
-- 需要切换到my_database数据库
SHOW CREATE TABLE t_user;
结果中的Create Table
字段就是我们需要的值:
复制出来是这样的:
CREATE TABLE `t_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户id',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`phone` varchar(20) DEFAULT NULL COMMENT '电话',
`status` int DEFAULT '10' COMMENT '状态 10启用/20禁用',
`valid` int DEFAULT '1' COMMENT '是否有效',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
和我们当时执行的脚本还是有一点区别的。
2.3.1、查看表结构
所谓表结构,就是指表有哪些字段,类型是什么,是否可空,有无默认值,是否主键…
可通过如下脚本获得:
DESCRIBE my_database.t_user;
-- or
DESC my_database.t_user;
结果如下:
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| status | int | YES | | 10 | |
| valid | int | YES | | 1 | |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)
如果希望查看表结构的同时,能够查看字段的注释,使用下面的命令:
-- 查看表结构和注释
SHOW FULL FIELDS FROM t_user;
-- or
SHOW FULL COLUMNS FROM t_user;
结果如下:
+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
| id | bigint | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 用户id |
| name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 姓名 |
| phone | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 电话 |
| status | int | NULL | YES | | 10 | | select,insert,update,references | 状态 10启用/20禁用 |
| valid | int | NULL | YES | | 1 | | select,insert,update,references | 是否有效 |
+--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+--------------------+
5 rows in set (0.04 sec)
2.3.1、修改表结构
1).修改表名
将表名由t_user改为t_account
-- 表名:t_user改为t_account
ALTER TABLE t_user rename t_account;
-- 查看表结构
DESC t_account;
-- 名称再改回来
ALTER TABLE t_account rename t_user;
2).修改表的注释
ALTER TABLE t_user comment '用户表';
3).增加字段
ALTER TABLE t_user ADD added_by int(11) COMMENT '新增人';
如果需要将字段增加到指定列的后面,比如:现在需要给表增加一个字段remark,希望将其放在status的前面,则可以这样编写脚本:
ALTER TABLE my_database.t_user ADD remark VARCHAR(500) COMMENT '备注' AFTER phone;
-- or
ALTER TABLE my_database.t_user ADD COLUMN remark VARCHAR(500) COMMENT '备注' AFTER phone;
after 列名
需要放在末尾,下面的写法是错误的:
ALTER TABLE t_user ADD remark VARCHAR(500) AFTER PHONE COMMENT '备注';
4).增加自增列
适用于建表时没有自增列,后期需要增加的场景:
-- 增加自增列,并指定其为第一列
ALTER TABLE my_database.t_user ADD COLUMN pid int(14) PRIMARY KEY AUTO_INCREMENT AFTER FIRST;
说明:
- 该方法新增的自增列,会追加为表的最后一列。
- 自增列的字段,需要同时设置为主键
如果表中已经有自增列,再次执行上面的语句,会报如下异常:
>1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
意思是:
不正确的表定义;表中只能有一个自增列,并且自增列必须定义为主键。
更通常的情况,是需要将 已经存在的列 设置为自增列:
表t_user的表结构为:t_user(id, name,phone,status,valid)
如果在创建表的时候,id没有设置为主键,也不是自增列
此时可这样处理:
-- 1.将已存在的列id设为主键
ALTER TABLE my_database.t_user ADD PRIMARY KEY(id);
-- 2.设置主键为增长
ALTER TABLE my_database.t_user MODIFY id bigint AUTO_INCREMENT;
5).修改字段的名称和类型
字段名added_by重命名为created_by,数据类型:int -> bigint。
ALTER TABLE t_user CHANGE added_by created_by
bigint(0) COMMENT '新增人';
6).修改字段的注释和类型
ALTER TABLE t_user MODIFY created_by bigint(0) COMMENT '创建人';
7).删除列
-- 删除created_by列
ALTER TABLE t_user DROP COLUMN created_by;
2.3.1、删除表结构
当某张表不再需要时,可以使用drop命令进行删除:
DROP TABLE t_user;
2.4、删除数据库
当某个库不再需要时,也可以使用drop命令进行删除:
DROP DATABASE my_database;
三、DML操作
3.1、新增数据
新增一行数据:
INSERT INTO t_user(`name`,phone)
values('lucy','15210023001');
新增多行数据:
INSERT INTO t_user(`name`,phone) values('lily','15210023002'),
('zhang_san','15210023003'),('li_si','15210023004');
3.2、查询数据
select * from my_database.t_user;
结果如下:
+----+-----------+-------------+--------+-------+
| id | name | phone | status | valid |
+----+-----------+-------------+--------+-------+
| 1 | lucy | 15210023011 | 10 | 1 |
| 2 | lily | 15210023002 | 10 | 1 |
| 3 | zhang_san | 15210023003 | 10 | 1 |
| 4 | li_si | 15210023004 | 10 | 1 |
+----+-----------+-------------+--------+-------+
4 rows in set (0.03 sec)
where语句
where语句后面的称之为条件判断,而其中连接字段和值的称之为操作符
;多个条件之间用and或是or进行连接。
例子:
select * from my_database.t_user
where valid=1 and `status` = 10;
常见操作符如下:
>、<、>=、<=、=、!=、
between...and...
、like、not null
、is null
条件逻辑操作符如下:
and、 or
排序
-- 按id升序排列
select * from my_database.t_user order by id;
-- 按id降序排列
select * from my_database.t_user order by id desc;
取指定条数的记录
其中使用到LIMIT关键字,可用于实现真分页:
-- 按id升序排列,取前10条记录
select * from my_database.t_user order by id limt 10;
-- 跳过前10条记录,取第二个10条数据
select * from my_database.t_user order by id limit 10,10;
真分页的原理:
-- 跳过前10条记录,取第二个10条数据
select * from my_database.t_user
order by id limit $page_size*($current_page-1),$page_size;
3.3、修改数据
将lucy的电话号码改为:15210023011
-- 为了防止误删,都是使用主键作为where条件
update t_user set phone = '15210023011'
where id=1 and name='lucy';
因为where语句的原因,update语句可以一次更新多条记录。
3.4、删除数据
删除表中数据有两种方法:
- delete 语句
- truncate 语句
1.删除单行数据
删除用户表中 li_si
的数据:
-- 为了防止误删,都是使用主键作为where条件
delete from t_user where id=4 and name='li_si';
2.使用delete删除表
删除整表的数据:
delete from t_user;
3.使用truncate 删除表
truncate会直接删除全表数据,且不可恢复:
truncate t_user;
两种方式的区别:
- delete仅删除内容,并且会在事务日志中增加一条记录,所以可以通过事务日志恢复数据;但是此方法非常占用空间。
- 删除数据后不可恢复(通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放)。执行速度比delete方式快,占用空间少。
四、问题
4.1、如何查看MySQL的版本?
答:可以使用如下命令:
select version();
4.2、ALTER TABLE CHANGE 和 ALTER TABLE MODIFY有什么不同?
答:前一个功能可用于重命名列名;后一个只改列的属性,不改列名;
如果是修改列的注释,建议用第二个。
参考资料:
https://dev.mysql.com/doc/refman/8.0/en/alter-table-examples.html
数据库的字符集可以修改
创建数据表时,可以指定其字符集,可以与数据库默认的字符集不同。
五、总结
本文讲解了关于数据库的常见操作:库的创建,库中表的查看,表的创建,表结构的修改与查看,表的删除和库的删除。这些都属于DDL的范畴。
然后又讲了针对数据表的常用操作(DML):数据的新增、查询、修改和删除。
参考资料