02【数据库的基本操作】

---

上一篇：01【数据库的介绍】

下一篇：03【MySQL字符集】

目录：【MySQL零基础系列教程】

---

---

二、数据库的基本操作

2.1 DDL操作数据库

2.1.1 创建数据库相关操作

• 1）创建数据库

CREATE DATABASE 库名;

create database db01;
1
2
3

• 2）判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 库名

create database if not exists db01;
1
2
3

• 3）创建数据库并指定字符集

CREATE DATABASE 库名 CHARACTER SET 字符集

create database db01 character set utf8;
1
2
3

Tips：我们在安装MySQL的时候，就已经指定了MySQL的字符集（为MySQL服务器字符集），我们创建的数据库如果没有特别指定默认为MySQL服务器的字符集；

2.1.2 查看数据库相关操作

-- 查看所有的数据库
show databases;

-- 查看某个数据库的DDL语句
show create database db01;
1
2
3
4
5

2.1.3 修改数据库编码

• 修改数据库默认的字符集

ALTER DATABASE 数据库 CHARACTER SET 字符集

alter database db01 character set utf8;
1
2
3

2.1.4 删除数据库

DROP DATABASE 数据库名;

drop database db01;
1
2
3

2.1.5 使用数据库

• 1）查看正在使用的数据库

select DATABASE();
1

• 2）使用/切换数据库

USE 数据库名;

use db01;
1
2
3

2.2 DDL操作表结构

操作表之前，先使用某个数据库。

2.2.1 MySQL数据类型

• 常使用的数据类型如下：

2.2.2 创建表

• 创建表的格式

create table 表名(
	字段名 字段类型,
	字段名 字段类型,
    字段名 字段类型
);

create table 表名(字段名 字段类型,字段名 字段类型);
1
2
3
4
5
6
7

Tips：字段名就是表中的列名，字段类型指数据类型

• 具体操作:

-- 创建student表包含id整数,name变长字符串长20,sex性别定长型1,birthday字段日期类型

use db01;

create table emp (
    id int,
    `name` varchar(20), -- 重音符号：在Esc下面，~ 在同一个键
    sex char(1),
	address varchar(20)
);
1
2
3
4
5
6
7
8
9
10

2.2.3 查看表

• 查看某个数据库中的所有表

show tables;
1

• 查看表结构

desc 表名;

desc emp;
1
2
3

• 查看创建表的SQL语句

show create table 表名;

show create table emp;
1
2
3

2.3.4 复制表结构

CREATE TABLE 新表 LIKE 旧表;

-- 创建e1表，e1表结构和emp表结构相同
create table e1 like emp;

-- 查看e1表的表结构
desc e1;
1
2
3
4
5
6
7

2.3.5 删除表

• 直接删除表

DROP TABLE 表名; 

drop table e1;
1
2
3

• 判断表是否存在，如果存在则删除表

DROP TABLE IF EXIST 表名;

drop table if exists e1;
1
2
3

2.3.6 修改表结构

• 添加表列ADD

ALTER TABLE 表名 ADD 列名 类型;
1

-- 为学生表添加一个新的字段age,类型为int
alter table emp add age int;
1
2

• 修改列类型MODIFY（只修改数据类型）

ALTER TABLE 表名 MODIFY 列名 新的类型;

alter table emp modify address varchar(100);
1
2
3

• 修改列名 CHANGE（既修改字段名又修改类型）

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

alter table emp change address addr varchar(80);
1
2
3

• 删除列 DROP

ALTER TABLE 表名 DROP 列名;

alter table emp drop age;
1
2
3

• 修改表名

RENAME TABLE 表名 TO 新表名;

-- 将emp表改为employee
rename table emp to employee;

-- 将employee表改为emp
rename table employee to emp;
1
2
3
4
5
6
7

Tips：MySQL中没有直接修改库名的语句

2.3.7 修改表字符集

ALTER TABLE 表名 character set 字符集;

-- 查看emp表的建表语句
show create table emp;

-- 修改emp表的字符集为gbk
alter table emp character set gbk;

-- 修改emp表的字符集为utf8
alter table emp character set utf8;
1
2
3
4
5
6
7
8
9
10

2.3 DML操作表中的数据

2.3.1 插入记录

• 1）插入全部字段

INSERT INTO 表名 VALUES (字段值1，字段值2...);

insert into emp values(1,'张三','男','河南汤阴',39);
1
2
3

• 2）插入部分数据

INSERT INTO 表名 (字段名1,字段名2) VALUES (值1，值2);

-- 插入指定的字段
insert into emp(id,name,sex) values(2,'李四','男');

-- 也可以不按照列的顺序插入
insert into emp(id,name,addr) values(3,'王五','湖南邵阳');
1
2
3
4
5
6
7

Tips：没有添加数据的字段会使用NULL

• 3）插入多条记录

INSERT INTO 表名 values (值1,值2),(值1,值2),(值1,值2);

INSERT INTO emp
VALUES
	( 4, '小红', '女', '四川南充', 25 ),
	( 5, '小明', '女', '河南平顶山', 24 ),
	( 6, '小李', '男', '陕西榆林', 20 );
1
2
3
4
5
6
7

插入记录规范：

• 1）数据的大小应在列的规定范围内，例如：不能将一个长度为30的字符串加入到长度为20的列中。

• 2）在values中列出的数据位置必须与被加入的列的排列位置相对应。

• 3）字符和日期型数据应包含在单引号中。MySQL中也可以使用双引号做为分隔符。

• 4）不指定列或使用null，表示插入空值。

2.3.2 更新记录

• 1）不带条件修改数据

语法：

UPDATE 表名 SET 字段名=值,字段名=值;

update emp set sex='女',addr='广西来宾';
1
2
3

Tips：不带条件表示更新所有的记录

• 2）带条件修改数据

语法：

UPDATE 表名 SET 字段名=值 WHERE 条件表达式;

-- 一次修改一列
update emp set addr='甘肃白银' where id=2;

-- 一次修改多列
update emp set sex='男',addr='陕西忻州' where id=5;
1
2
3
4
5
6
7

2.3.3 删除表记录

• 1)不带条件删除数据

DELETE FROM 表名;

delete from emp;
1
2
3

Tips：不带条件的删除会删除表中的所有记录，慎用！

准备数据：

INSERT INTO emp
VALUES
	( 1, '小明', '男', '山西铜川', 19 ),
	( 2, '小兰', '女', '江西萍乡', 20 ),
	( 3, '小龙', '男', '云南临沧', 23 ),
	( 4, '小红', '女', '贵州毕节', 18 ),
	( 5, '小龙', '男', '吉林白城', 22 );
1
2
3
4
5
6
7

• 2）带条件删除

语法：

DELETE FROM 表名 WHERE 条件表达式;

delete from emp where id=5;
1
2
3

2.3.4 清空表

• 语法：

truncate 表名;

truncate emp;
1
2
3

Tips：先删除表结构,再创建一个相同的表结构 相当于drop table emp，再create table emp

2.3.5 增删改方法小结

2.4 数据库的查询

2.4.1 查询全部数据

建立测试数据：

INSERT INTO emp
VALUES
	( 1, '小明', '男', '辽宁盘锦', 26 ),
	( 2, '小兰', '女', '安徽亳州', 21 ),
	( 3, '小龙', '男', '湖北随州', 24 ),
	( 4, '小红', '女', '西藏林芝', 25 ),
	( 5, '小龙', '男', '福建宁德', 22 );
1
2
3
4
5
6
7

• 语法

SELECT * FROM 表名;

select * from emp;
1
2
3

2.4.2 查询指定列

查询指定列的数据,多个列之间以逗号分隔

• 语法：

SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;

select id,name,addr from emp;
1
2
3

2.4.3 指定列的别名进行查询

使用关键字AS：

• 使用别名的好处：可以让查询结果，列名显示为汉字，可读性好一些。后期也可以给表起别名，主要用于多表查询。

对列指定别名：

• 语法：

SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;

select id as 编号,name as 姓名,addr 地址 from emp;
1
2
3

Tips：as可以省略

2.4.4 清除重复值

添加数据：

INSERT INTO emp
VALUES
	( 1, '小陈', '男', '青海西宁', 28 ),
	( 7, '小辉', '男', '浙江衢州', 20 );
1
2
3
4

查询表数据：

• 清除重复数据语法：

SELECT DISTINCT 字段名 FROM 表名;
1

去重id相同的行：

-- 只去重id相同的行
select distinct id from emp;
1
2

去重sex相同的行：

select distinct sex from emp;
1

2.4.5 查询结果参与运算

• 1）某列数据和固定值运算

SELECT 列名1 + 固定值 FROM 表名; 

select id,name,addr,age+1 from emp;
1
2
3

• 2）某列数据和其他列数据参与运算

SELECT 列名1 + 列名2 FROM 表名; 
 
select id,name,addr,id+age from emp;
1
2
3

Tips：参与运算的必须是数值类型

2.5 DQL条件查询

2.5.1 准备数据

创建一个学生表，包含如下列：

CREATE TABLE student (  
    id int, -- 编号  
    `name` varchar(20), -- 姓名  
    age int, -- 年龄  
    sex char(1), -- 性别  
    address varchar(100), -- 地址  
    math int, -- 数学  
    english int -- 英语  
);  

-- 插入数据
insert into student values(1,'小明',20,'男','河北衡水',90,100);
insert into student values(2,'小红',26,'女','湖南衡阳',88,69);
insert into student values(3,'小龙',18,'男','内蒙包头',78,80);
insert into student values(4,'小丽',24,'女','陕西宝鸡',95,80);
insert into student values(5,'张三',22,'男','江西上饶',80,90);
insert into student values(6,'李四',19,'男','广西北海',100,95);
insert into student values(7,'王五',24,'男','黑龙江大庆',90,95);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.5.2 比较运算符

• 练习：

-- 查询年龄大于25岁的学生

-- 查询数学成绩大于等于80分的学生

-- 查询英语成绩低于60的学生

-- 查询数学成绩大于英语成绩的学生

-- 查询性别不为男的学生

-- 查询性别不为男的学生(第二种写法)

-- 查询年龄大于25岁的学生
select * from student where age>25;

-- 查询数学成绩大于等于80分的学生
select * from student where math>=80;

-- 查询英语成绩小于等于90的学生
select * from student where english<=90;

-- 查询数学成绩大于英语成绩的学生
select * from student where math>english;

-- 查询性别不为男的学生
select * from student where sex!='男';

-- 查询性别不为男的学生(第二种写法)
select * from student where sex<>'男';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

2.5.3 逻辑运算符

• 练习：

-- 查询年龄大于22岁并且性别为男的学生
select * from student where age>22 and sex='男';

-- 查询数学成绩大于80并且性别为女的学生
select * from student where english>80 and sex='女'

-- 查询数学成绩大于90或者英语成绩大于90的学生
select * from student where math>60 or english>60;
1
2
3
4
5
6
7
8

2.5.4 in关键字

• 语法：

SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
1

in里面的每个数据都会作为一次条件，只要满足条件的就会显示

• 练习：

-- 查询id是1或2或5的学生
select * from student where id in (1,2,5);

-- 查询id不是1或2或5的学生
select * from student where id not in (1,2,5);
1
2
3
4
5

2.5.5 范围查询

• 语法：

BETWEEN 值1 AND 值2
1

表示从值1到值2范围，包头又包尾，值1要小于值2，类似于>=值1 and <=值2

-- 查询english成绩大于等于60，且小于等于90的学生
select * from student where english between 60 and 90;

select * from student where english >=60 and english <=90;
1
2
3
4

2.5.6 like 关键字

LIKE表示模糊查询

• 语法：

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
1

• MySQL通配符

-- 查询地址为'广'开头城市的学生
select * from student where address like '广%';

-- 查询地址中包含有'西'城市的学生
select * from student where address like '%西%';

-- 查询五个字地址城市的学生
select * from student where address like '_____';		-- 五个_
1
2
3
4
5
6
7
8

2.5.7 查询为空的列IS NULL

-- 查询性别成绩为NULL的学生
select * from student where sex is null;

-- 错误的
select * from student where sex = null;

-- 查询英语成绩不为NULL的学生
select * from student where sex is not null;
1
2
3
4
5
6
7
8

2.5.8 条件查询小结