mysql篇 - mysql数据库/表操作 - 《mysql》

sql语句分类
DDL语句
DML语句
数据库数据操作的步骤
其他操作命令

sql语句分类

sql语句大致可以分为以下几类

数据定义（DDL）：create table、drop table、alter table 等
数据操作（DML）：select、insert、update、delete
数据控制（DCL，Data Control Language）：grant、revoke 等（在数据库权限一节有提到）
数据查询（DQL，Data Query Language）
事务控制：commit、rollback 等

DDL语句

DDL（data definition language）数据定义语言。用来创建数据库中的各种对象— 表、视图、索引。

创建数据库

# 创建数据库test，指定字符集和排序规则，不指定会采用默认的
create database person default character set utf8mb4 default collate utf8mb4_general_ci;

删除数据库

# 删除数据库
drop database test;

其他的操作。

# 选择要操作的数据库，使用该命令后所有 Mysql 命令都只针对该数据库
use person;

# 列出 MySQL 数据库管理系统的数据库列表
show databases;

创建数据库表

sql 类型可以查看这里。

/* create table 表名... */

create table t_user (
    pid tinyint(10),
    pname varchar(10),
  pwd varchar(10),
    age tinyint(3),
  paddress varchar(100),
  birth date,
  score decimal(10, 2)        // 数字位10，保留2位有效数字
);

删除数据库表

drop table t_user;

修改数据库表名

# 修改数据库表名为t_user => user
alter table t_user rename to user;

删除数据库表内容

# 删除user表中的内容
truncate table person.user;

修改列名

# 将user表的paddress字段修改为address，并指定数据类型
alter table user change paddress address varchar(255);

修改列数据类型

# 修改user表pid字段的数据类型为int
alter table user modify pid int;

注意，如果原来该列已经有数据存在，那么修改只能改大，不能改小。例如原来是 varchar(20)，那么你可以改成 varchar(30)，但是不能改成 varchar(10)。

插入数据列

# 插入数据列--性别，插入的列默认放到最后一列
alter table user add gender int;

删除列

# 删除性别列
alter table user drop column gender;

注意，删除列最后至少要留一列，也就是说最后一列是删除不了的。因为表是由列组成的，如果没有列就不成表了。

创建视图

首先需要知道的是，视图并不是真实的表（没有属于自己的表和列），只是通过物理存在的表（person）创建出来的一个结果集。
先往 person 表中插入几条数据。

# 创建视图 t_per 且显示的是 pid, pname, age
create view t_per as select pid, pname, age from user;

删除视图

drop view t_per;

修改视图

# 用 select * 查询到的结果作为视图t_per
alter view t_per as select * from user;

查询视图数据

# 查询视图跟查询表是类似的
select * from t_per;

DML语句

DML(Data Manipulation Language)，数据操纵语言，主要对表记录进行更新(增、删、改)。常见的有 insert，update，delete 等。

插入数据

insert into user values(1, '张三', '123456', 23, '广东省广州市', '2020-01-01', 88);
insert into user values(2, '李四', '123456', 24, '北京市北京市', '2020-05-01', 90);
insert into user values(3, '王五', '123456', 25, '上海市上海市', '2020-01-01', 85);
insert into user values(4, '六六', '123456', 22, '山东省济南市', '2020-01-01', 50);
insert into user values(5, '王八', '123456', 21, '湖北省武汉市', '2020-02-01', 8);

上面的方式是按照列的顺序依次填写数据插入，如果只想插入部分的数据，可以使用如下方式。

insert into user(pid, pname, pwd) values(6, '阿九', '123456');

更新数据

# 更新pid=6的数据的年龄为20
update user set age = 20 where pid = 6;

# 更新多个字段
update user set age = 28, address='广东省深圳市' where pid = 6;

删除数据

# 删除pid=6的数据
delete from user where pid=6;

# 如果这样写会删除所有的数据
delete from user;

那么 delete 和 truncate 的区别是什么？

delete 删除数据可恢复，而 truncate 删除数据不可恢复。后续说备份时再详细说。

数据库数据操作的步骤

进入数据库

-- -u后带的是用户名，-p后带的是密码，-p和密码之间不能有空格
mysql -u root -proot123

显示所有数据库

show databases;

使用哪个数据库

-- xxx 是数据库名，选择要操作的 Mysql 数据库，使用该命令后所有 Mysql 命令都只针对该数据库
use xxx;

显示所有数据表

# 显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库
show tables;

导入sql脚本

source table.sql

其他操作命令

显示数据表的属性，属性类型，是否为 NULL，主键信息，默认值，是否自动递增等信息

-- show columns from 表名;
show columns from user;

-- desc 表名
desc user;

显示数据表的详细索引信息，包括PRIMARY KEY（主键）

-- show index from 表名;
show index from user;

设定条件的查找（where）

select field1,... from 表名 [where condition] [offset M] [limit N]

查询可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件;
SELECT 命令可以读取一条或者多条记录;
使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据;
使用 WHERE 语句来包含任何条件;
通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0;
使用 LIMIT 属性来设定返回的记录数;

首先在数据库中多加几条数据

insert into user(id, name, age) values(1, "张三", 20);
insert into user(id, name, age) values(2, "李四", 21);
insert into user(id, name, age) values(3, "王五", 22);
insert into user(id, name, age) values(4, "刘六", 23);
insert into user(id, name, age) values(5, "张全蛋", 24);
insert into user(id, name, age) values(6, "李狗蛋", 25);
insert into user(id, name, age) values(7, "李大傻", 26);
insert into user(id, name, age) values(8, "何二傻", 27);
insert into user(id, name, age) values(9, "三傻子", 28);
insert into user(id, name, age) values(10, "席巴", 29);

(1) 设定 limit 和 offset

-- 偏移量为2，从第三条数据开始取，返回3条数据
select * from user limit 3 offset 2;

 id | name      | age |
+----+-----------+-----+
|  3 | 王五      |  22 |
|  4 | 刘六      |  23 |
|  5 | 张全蛋    |  24 |


-- 从第4条数据开始读，读取2条数据
select * from user limit 3,2;

+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  4 | 刘六      |  23 |
|  5 | 张全蛋    |  24 |
+----+-----------+-----+

(2) 设定where条件

操作符列表，用于 where 查询。

操作符	描述
=	等号，检测两个值是否相等，如果相等返回true
<>, !=	不等于，检测两个值是否相等，如果不相等返回true
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true

select * from user where age=20;
select * from user where age>28;
select * from user where age<21;
select * from user where age>=28;
select * from user where age<=21;
select * from user where age!=21;
select * from user where age<>21;

(3) 带like搜索条件（模糊查询）

-- 右模糊
select * from user where name like "张%";
-- 两边模糊
select * from student where name like "%小%";
-- 左模糊
select * from student where name like "%明";

(4) （排序查询）

查看表的创建记录

-- show create table 表名;
show create table user;

表的外键关联(表级设置外键约束)

详情查看约束一节。
使用 constraint ... foreign key ... 后，表于表之间会存在约束，这时不能随意删除表中数据，否则会报错。

create table study_record(
    id int(11) not null,
    day int not null,
  status char(32) not null,
    stu_id int(11) not null,
    primary key (id),
    constraint fk_student_key foreign key (stu_id) references student (id)
);

例如：

表student：
+----+-----------+-----+---------------+--------+
| id | name      | age | register_date | gender |
+----+-----------+-----+---------------+--------+
|  1 | 小明      |  23 | 2019-07-12    | 男     |
|  2 | 张三      |  20 | 2019-07-02    | 男     |
|  3 | 李佳佳    |  18 | 2019-07-13    | 女     |
+----+-----------+-----+---------------+--------+

表study_record:
+----+-----+--------+--------+
| id | day | status | stu_id |
+----+-----+--------+--------+
|  1 |   1 | yes    |      1 |
|  2 |   1 | yes    |      3 |
+----+-----+--------+--------+

执行插入语句insert into study_record(day, status, stu_id) values(1, "yes", 5);时会报错，由于student中没有id=5的值
执行删除语句delete from student where id=1;时也会报错，由于study_record中存在stu_id=1的依赖，不能直接删除

`null`值的查询

mysql中对于 null 值，不能使用 = null 或 != null 在列中查找 null值；（null 值与任何其他值比较永远返回 false，null = null 返回 false）

为了处理这种情况，mysql 提供了三大运算符：

IS NULL: 值为null 返回 true
IS NOT NULL: 值不为 null 返回 true
<=>: 比较操作符；比较的两个值为 null 返回 true

联表查询

详细查看连表查询一节。

left join: 左连接；获取左表所有记录，即使右表没有对应的记录；
right join：右连接；获取右表所有记录，即使左表没有对应的记录；
inner join：内连接；获取两个表中字段匹配关系的记录（字段匹配关系相等的记录，不是所有记录）；
full join：并集；获取两个表中所有的记录；（注意：mysql不支持full join）;

例子：

表A:
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

表B:
+---+
| b |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+

-- 内连接：查找两个表中字段匹配的记录（不是所有记录）

select * from A inner join B on A.a = B.b;
select A.*,B.* from A,B where A.a=B.b;

+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+

-- 左连接：左表全数据，查出右表对应的数据，不对应的全部为null;

select * from A left join B on A.a=B.b;

+---+------+
| a | b    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 1 | NULL |
| 2 | NULL |
+---+------+

select * from B left join A on A.a=B.b;

+---+------+
| b | a    |
+---+------+
| 3 |    3 |
| 4 |    4 |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
+---+------+

-- 右连接：右表全数据，查出左表对应的数据，不对应的全部为null;

select * from A right join B on A.a=B.b;

+------+---+
| a    | b |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 5 |
| NULL | 6 |
| NULL | 7 |
+------+---+

select * from B right join A on A.a=B.b;

+------+---+
| b    | a |
+------+---+
|    3 | 3 |
|    4 | 4 |
| NULL | 1 |
| NULL | 2 |
+------+---+

-- `full join`：mysql不支持`full join`

select * from A full join B on A.a=B.b;

-- 但可以换种写法（通过`union`连接左\右连接查询结果）
select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b;

+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
| NULL |    7 |
+------+------+

to be continue…

mysql数据库/表操作