数据库的操作以及MySQL表的增删查改（初阶）

数据库的操作以及MySQL表的增删改查（初阶）

数据库学习目标：数据库，表，数据进行增删查改（CURD）的操作(SQL）

数据库的简单概念

数据库的操作

一： 数据库的操作

1.查询，显示：
show databases;

2.创建：
直接创建（如果已经存在该数据库，报错）
create database 数据库名称 character set utf8mb4; (character set utf8mb4表示字符编码）
如果没有才创建（if 数据库不存在创建）
create database if not exists 数据库名称 character set utf8mb4;
3.删除数据库：
直接删除（如果不存在报错）
drop database 数据库名称;
如果有才删除
drop database if exists 数据库名称;

4.使用（如果出现No database selected 错误，要先进入/使用数据库
use 数据库名称;

二： 常用数据类型

1.数值类型

2.字符串类型

3.日期类型

表的操作

注：进行表的操作时，首先要进入数据库：use 数据库名称;
1.创建表
语法： create table table_name(
field1 datatype,
field2 datatype,
field3 datatype);
注：可以使用comment增加字段说明。
例：
create table stu_test (
id int,
name varchar(20) comment ‘姓名’,
password varchar(50) comment ‘密码’, age int,
sex varchar(1),
birthday timestamp,
amout decimal(13,2),
resume text
);

2.查看表结构：
语法： desc 表名;
例：

3.删除表
语法格式： DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] …
– 删除 stu_test 表
droptable stu_test;
– 如果存在 stu_test 表，则删除 stu_test 表droptable if exists stu_test;

MySQL表的增删改查初级（CURD)

一： 新增(create)

1.语法：
INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)] …
value_list: value, [, value] …
2. 案例：
– 创建一张学生表
DROPTABLEIFEXISTS student;
CREATETABLE student (
id INT,
sn INTcomment’学号’,
name VARCHAR(20) comment’姓名’,
qq_mail VARCHAR(20) comment’QQ邮箱’);

1. 单行数据 + 全列插入

– 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
insert into student values (100, 10000, ‘唐三藏’, NULL);
insert into student values (101, 10001, ‘孙悟空’, ‘11111’);

2. 多行数据 + 指定列插入

– 插入两条记录，value_list 数量必须和指定列数量及顺序一致
insert into student (id, sn, name) values
(102, 20001, ‘曹孟德’),
(103, 20002, ‘孙仲谋’);

二： 查询操作（Retrieve）

1.语法：
SELECT
[DISTINCT] {* | {column [, column] …} [FROMtable_name]
[WHERE …]
[ORDERBYcolumn [ASC | DESC], …]
LIMIT …
2.案例：
– 创建考试成绩表
DROPTABLEIFEXISTS exam_result;CREATETABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
– 插入测试数据
INSERTINTO exam_result (id,name, chinese, math, english) VALUES
(1,‘唐三藏’, 67, 98, 56),
(2,‘孙悟空’, 87.5, 78, 77),
(3,‘猪悟能’, 88, 98.5, 90),
(4,‘曹孟德’, 82, 84, 67),
(5,‘刘玄德’, 55.5, 85, 45),
(6,‘孙权’, 70, 73, 78.5),
(7,‘宋公明’, 75, 65, 30);

1. 全列查询

使用 * 进行全列查询
select * from exam_result;

2. 指定列查询

– 指定列的顺序不需要按定义表的顺序来
select id, name, english from exam_result;

3. 查询字段为表达式

– 表达式不包含字段
SELECT id, name, 10FROM exam_result;
– 表达式包含一个字段
SELECT id, name, english + 10FROM exam_result;
– 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

4. 别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称

例：
注： name 和 别名n之间间隔一个空格

5. 去重 DISTINCT

使用DISTINCT关键字对某列数据进行去重

例如：
select distinct math from exam_result;
将math列中相同的数据去重，只剩一个。

6. 排序：ORDER BY

注：
– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

<1> 直接对表中数据进行排序

<2> 使用表达式进行排序

<3> 使用别名进行排序

<4> 使用多个字段排序

– 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示SELECT name, math, english, chinese from exam_result
order by math DESC, english, chinese;

7. 条件查询 where

<1>.比较运算符

<2>逻辑运算符

注：

1. WHERE条件可以使用表达式，但不能使用别名。
2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

<3> 基本查询

– 查询英语不及格的同学及英语成绩 ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60;
– 查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;
– 查询总分在 200 分以下的同学
SELECT name, chinese + math + english 总分FROM exam_result WHERE chinese + math + english < 200;

<4> AND与OR查询

– 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80and english > 80;
– 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80or english > 80;
– 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80or math>70and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80or math>70) and english > 70;

<5> 范围查询

1.BETWEEN … AND …
– 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN80AND90;-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80AND chinese <= 90;
2. in
– 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
– 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58OR math = 59OR math = 98OR math = 99;

<6> 模糊查询 like

– % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE’孙%’;-- 匹配到孙悟空、孙权-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE’孙_’;-- 匹配到孙权

<7> NULL 的查询：IS [NOT] NULL

– 查询 qq_mail 已知的同学姓名
select name, qq_mail from student where qq_mail is not null;
– 查询 qq_mail 未知的同学姓名
select name, qq_mail from student where qq_mail is null;

8. 分页查询 limit

语法：
– 起始下标为 0
– 从 0 开始，筛选 n 条结果
SELECT … FROMtable_name [WHERE …] [ORDERBY …] LIMIT n;
– 从 s 开始，筛选 n 条结果
SELECT … FROMtable_name [WHERE …] [ORDERBY …] LIMIT s, n;
– 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT … FROMtable_name [WHERE …] [ORDERBY …] LIMIT n OFFSET s;
例如：
– 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3 OFFSET0;
– 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3 OFFSET3;
– 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDERBY id LIMIT3 OFFSET6;

三： 修改 update

语法：UPDATEtable_nameSETcolumn = expr [, column = expr …] [WHERE …] [ORDERBY …] [LIMIT …]

案例：
– 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80WHERE name = ‘孙悟空’;
– 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70WHERE name = ‘曹孟德’;
– 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30ORDERBY chinese + math + english LIMIT 3;
– 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

四： 删除 delete，drop

1.delete(删除表中数据）
语法：DELETEFROM table_name [WHERE …] [ORDERBY …] [LIMIT …]

案例：
– 删除孙悟空同学的考试成绩
DELETEFROM exam_result WHERE name = ‘孙悟空’;
2.drop（删除表）
drop table 表名

五.其他操作

1.增加列：alter table 表名 add 列名 数据类型;
2.删除列: alter table 表名 drop column 列名;
3.修改列的数据类型：alter table 表名 alter column 列名 新的数据类型

数据库约束

一： 约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

二： NULL约束

创建表时，可以指定某列不为空：
例如：
– 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INTNOTNULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

三： UNIQUE：唯一约束

指定sn列为唯一的、不重复的：
– 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INTNOTNULL,
sn INT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

四： DEFAULT：默认值约束

指定插入数据时，name列为空，默认值unkown：
– 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INTNOTNULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT ‘unkown’, qq_mail VARCHAR(20)
);

五： PRIMARY KEY：主键约束

指定id列为主键：
– 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INTNOTNULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT ‘unkown’, qq_mail VARCHAR(20)
);

注意：对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

六： FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键，语法：
foreign key (字段名) references 主表(列)
– 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT ‘unkown’,
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id));

MySQL表的增删查改（进阶）

MySQL表的增删查改（进阶）