MySQL-2-基本操作

tobing

理论学习

发布于：Nov 15, 2019

次浏览

MySQL复习第二弹。

MySQL-2-基本操作

1. DDL：操作数据库、表

操作数据库：CRUD

C（Create）：创建

-- 创建数据库
create database [数据库名称];			
create database if not exists [数据库名称];				-- 创建并判断是否存在
create database [数据库名称] character set [字符集名称]; 	 -- 创建并指定字符集

1 2	-- 创建db4数据库，判断是否存在，并指定字符集为gbk create database if not exists db4 character set gbk;

R（Retrieve）：查询

-- 查询所有数据库名称
show databases;
-- 查询某一个数据库的字符集：查询某一个数据库的创建语句
show create database [数据库名称]

U（Update）：修改

1 2	-- 修改数据库字符集 alter database [数据库名称] character set [字符集名称]

D（Delete）：删除

-- 删除数据库
drop database [数据库名称];
-- 判断存在，存在再删除
drop database if exists [数据库名称];

使用数据库

-- 查询当前使用的数据库名称
select database();
-- 使用数据库
use [数据库名称];

操作表

C（Create）：创建
1
2
3
4
5
6
7
-- 创建表
create table [表名] (
列名数据类型,
列名数据类型,
...
-- 最后一个没有逗号
);
- int：整数
- double：浮点
- date：日期（年月日），yyyy-MM-dd
- datetime：（年月日时分秒） yyyy-MM-dd HH:mm:ss
- timestamp：时间戳类型包含年月日时分秒 yyyy-MM-dd（默认为当前系统时间）
- varchar：字符串
1
2
-- 复制表
create table [表名] like [被复制的表]

R（Retrieve）：查询

-- 查询当前数据库下所有的表
show tables;
-- 查指定的表结构
desc [表名]

U（Update）：修改

-- 修改表名
alter table [表名] rename to [新的表名]
-- 修改表的字符集
alter table [表名] character set [字符集名称]
-- 添加一列
alter table [表名] add [列名] [数据类型]
-- 修改列名称 类型
alter table [表名] change [列名] [新列名] [新数据类型]
-- 删除列
alter table [表名] drop [列名]

D（Delete）：删除

-- 删除表
drop table [表名];
drop table if exists [表名];

2. DML：修改表中的数据

添加数据：insert
1
2
3
4
5
-- 插入
insert into 表名(列名1，列名2，列名3....) values(值1，值2，值3...);

insert into 表名 values(值1，值2，....);
1. 列名要和值一一对应。
2. 如果表名后，不定义列名，则给所有的列添加值。
3. 除了数学类型，其他类型需要使用引号（单双都可以）引起来。

删除数据：delete

delete from [表名] [where 条件]

-- 删除所有记录
delete from [表名]; 	-- 不推荐使用。有多少天记录就输出第三条记录（按记录删除）
truncate table [表名]	-- 推荐使用，效率更高，先删除表，如果在创建一张的表（按表删除）

如果不加条件，则删除表中所有记录

修改数据：update
1
2
update [表名] set [列1] = [列1], [列2] = [值2] .... [where 条件]
注意：如果不加任何条件，表中使用记录都会被更改。

3. DQL：查询表中记录

格式

select
    [字段列表]
from
    [表名列表]
where
    [条件列表]
group by
    [分组字段]
having
    [分组之后的条件]
order by
    [排序]
limit
    [分页限定]

普通字段查询

-- 多字段查询
select 字段1，字段2... from [表名];
-- 查询所有字段
select * from [表名];

-- 去除重复distinct
select distinct [列名] from [表名];

-- 查询结果运行
select *,(列1+列2) form [表名]	-- 显示该表所有数据，并将列1和列2的也相加显示（不限于相加）
select *,(列1+列2) as [新列名] from [表名] -- 将新加的类列名设置为【新列名】

as : 起别名

条件查询
1
2
where [条件]
- 运算符
<、>

between…and

in(集合)

like:模糊查询

_：单个任意字符

%：多个任意字符

is null

and 或 &&

or 或 ||

not 或！

排序查询

1
2
3

order by [子句]
order by 排序字段1，排序方式1，排序字段2，排序方式2....

asc：默认，升序排序

desc：降序排序

聚合函数：将一列数据作为一个整体，降序纵向计算。（例如：求和）
1
2
3
select count(列名) from [表名] -- 给某一列求个数
select count(ifnull([列名],0)) from [表名]
1. count：计算个数
  - 一般选择非空的列，主键
  - count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值
注意：使用时排查null值，一般选择非空列
分组查询 group by

常用于对某个具有相同特征的整体进行操作
注意：分组之后，查询的字段只能写聚合函数和分组字段。否则没有意义

having和where的区别
- where在分组之前进行限定，如果不满足条件，不参与分组。where之后不可以跟聚合函数。
- having在分组后进行限定，如果不满足，则不会被查询出来。having可以进行聚合函数的判断。

分页查询 limit

-- 模拟每页显示3条内容
-- 显示从0开始的3条记录
select * from [表名] limit 0,3;  -- 第一页
-- 显示从3开始的3条记录
select * from [表名] limit 3,3;  -- 第二页

注意：limit是Mysql的方言

4. limit案例

创建学生表

CREATE TABLE student3(  -- 创建学生表
	id INT,		-- id
	NAME VARCHAR(20), -- 姓名
	age INT,	  -- 年龄
	sex VARCHAR(10),  -- 性别
	address VARCHAR(30), -- 地址
	math DOUBLE,         -- 数学成绩
	english DOUBLE       -- 英语成绩
);

插入数据

INSERT INTO student3 VALUES
	(1,"张三丰",20,'男','长安',77,99),
	(2,"张无忌",25,'男','西安',99,45),
	(3,"张翠山",28,'男','武汉',99,12),
	(4,"孙悟空",33,'男','花果山',44,99),
	(5,"猪八戒",50,'男','高老庄',99,24),
	(6,"白骨精",22,'女','蚕丝洞',99,88),
	(7,"蜘蛛精",23,'女','蜘蛛洞',98,99);

使用limit分页显示查询

-- 每页显示5条记录
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
-- 当查询第一页时，可以省略开始的页码
SELECT * FROM student3 LIMIT 5;

5. 约束

约束是对表中的数据进行限定，保证数据的正确性，有效性和完整性。
分类
1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

非空约束：not null,值不能为null

-- 创建表时，添加约束
create table student(
	id int;
    name varchar(20) not null  -- name属性不能为空
);
-- 创建表之后，添加约束
alter table student modify name varchar(20) not null;
-- 删除name的非空约束
alter table student modify name varchar(20);

唯一约束：unique，值不能重复

-- 创建表时，添加唯一约束
create table student(
	id int;
	id_key varchar(20) unique; -- id_key值唯一
);

在Mysql中，唯一约束限定的列可以有多个null。

-- 删除唯一约束
alter table student drop index id_key; -- 删除id_key列的唯一约束
-- 创建表后，添加唯一约束
alter table student modify id_key varchar(20) nuique;

主键约束：primary key

主键非空且唯一。

一张表只能有一个字段为主键。

主键就是表中记录的唯一标识。

-- 创建表时，添加主键
create table user(
	id int primary key; -- id为主键
    username varchar(20);
);
-- 删除主键
alter table user drop primary key;

注意：删除主键并不是：alter table user modify primary key;

-- 自增长（常常与主键一起使用）
-- 创建表时，指定自增长
crete table user(
	id int primary key auto_increament,	-- 主键，自增长
    name varchar(20)
);
-- 删除自增长
alter table user modify id int;
-- 添加自增长
alter table user modify id int auto_increament;

外键约束：foreign key，使得表与表之间产生关系。（保证了数据的完整性）

-- 创建表时添加
create table [表名](
	...
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主列表名称)
);
-- 删除外键
alter table [表名] drop foreign key [外键名称]; -- 注意此处使用drop而非modify
-- 创建表后，添加外键
alter table [表名] add constraint [外键名称] foreien key (外键字段名称) references [主表名称](主表列名称);

6. 外键约束案例

引入

INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES
	('张三',20,'研发部','广州'),
	('李四',21,'研发部','广州'),
	('赵六',24,'销售部','深圳'),
	('王五',15,'销售部','深圳');

+----+--------+------+-----------+--------------+
| id | name   | age  | dep_name  | dep_location |
+----+--------+------+-----------+--------------+
|  1 | 张三   |   20 | 研发部   | 广州         |
|  2 | 李四   |   21 | 研发部   | 广州         |
|  3 | 赵六   |   24 | 销售部    | 深圳         |
|  4 | 王五   |   15 | 销售部    | 深圳         |
+----+--------+------+-----------+--------------+

观察以上的数据可以看出，在部门和工作地址存在很多冗余的数据。

改进：引用外表

-- 创建部门表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自增长
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);
-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT -- 对应department表的主键
);
-- 添加数据
INSERT INTO department VALUES
	(NULL,'销售部','深圳'),
	(NULL,'研发部','广州');

INSERT INTO employee VALUES
	(NULL,'张三丰',20,1),
	(NULL,'张无忌',21,1),
	(NULL,'猪八戒',33,2),
	(NULL,'孙悟空',33,2);

把部门和地址拆分出来之后，数据的冗余量下降了，但是会存在新的问题

什么问题？

如果明天研发部取消了，直接从部门表中删除了研发部这条记录，但是删除的时候并不知道还有其他的员工记录关联了该记录。这就会导致数据不完整。

如何解决？

外键约束

使用外键约束保证数据完整性

-- 格式
constraint 外键名称 foreign key (外键列名称) references 主键名称(主列表名称)
-- 创建employee表给其添加外键约束
-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT -- 对应department表的主键
    constraint emp_dep_fk foreign key (dep_id) references department(id)  -- 给dep_int 添加到department表的外键约束
);

-- 试图删除部门表中的id为1的部门
delete from department where id = 1
-- 执行时报以下错误
Cannot delete or update a parent row: a foreign key constraint fails (`db8100`.`employee`, CONSTRAINT `emp_dep_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))

此时，部门表已经和员工表建立了关系

7. 外键约束-级联

引入

当我们在使用外键连接了不同的表的时候，当我们要改动被关联表中的被关联的列的时候（如上图中，要改动department中的id的记录，如果直接修改department的记录，就会报错，因为id列的记录还被employee表的记录关联着，直接修改会导致出现数据丢失。）
解决办法
1. 传统办法，条件更改，把所有关联要被修改的（外键记录）的都改为null，更改外键记录之后，在通过条件把以上修改的设置为新的记录。
2. 级联
  
  修改的时候把所有关联着该外键记录的值一同更新。
  
  语法：
  1
  2
  3
  Alter table [表名] add constraint [外键名称]
  foreign key (外键字段名称)references [主表名称](主表列名称) on update cascade on delete cascdae;
  - 级联更新：on update cascade
  - 级联删除：on delete cascade （谨慎使用，如果不注意容易导致删除很多数据）

更新于：Jan 15, 2021

MySQL

MySQL-3-数据库的设计

MySQL学习第三弹。 MySQL-3-数据库的设计1. 表之间的关系1. 分类一对一人和身份证一个人只有一个身份证，一个身份证对应一个人一对多部门和员工一个部门有多个员工...

MySQL-1-概述

本文是对MySQL的复习笔记。 MySQL-1-概述1. 数据库引入常见不同存放数据介质的比较存储位置优点缺点内存速度快断电丢失硬盘永久保存操作数据不方便...