MySQL数据库常用操作（DDL、DML、DQL、DCL）-CSDN博客

-- 创建数据库

create database 数据库名；

create database if not exists 数据库名；

create database 数据库名 character set 字符集；

create database if not exists 数据库名 character set 字符集；

# 2.R(Retrieve) 查询

show databases; 查询所有数据库的名称

show create database mysql; 查看数据库对应的字符集；

#3.U(update) 修改

alter database 数据库名称 character set 字符集名称；修改数据库的字符集；

#4.D(delete) 删除

drop database 数据库名称；删除数据库

drop database if exists 数据库名称；判断数据库是否存在，存在删除；

#5.使用数据库

select database(); 查询当前正在使用的数据库名称；

use 数据库名称；使用数据库；

------------------------------------------------------

## crud操作表

# 1.C(create) 创建

create table 表名(

列名1 数据类型1,

列名2 数据类型2,

列名3 数据类型3

);

数据库类型：

1.int 整数

2.double 小数

3.date 日期，只包含年月日 yyyyy-MM-dd

4.datetime 日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss

5.timestamp 时间戳类型，包含年月日时分秒 yyyy-MM-dd HH:mm:ss

6.varchar 字符串

eg：create table student(

id int,

name varchar(32),

age int,

score double(4.1),

birthday date,

insert_time timestamp

);

复制表；

create table 表名 like 被复制的表名；

# 2.R(Retrieve) 查询,

show tables; 查询数据库中的所有表；

desc 表名; 查询表结构

#3.U(update) 修改

-- 1.修改表名

alter table 表名 rename to 新的表名；

-- 2.修改表的字符集

alter table 表名 character set utf8;

-- 3.添加一列

alter table 表名 add 列名数据类型；

-- 4.修改列名称类型

alter table 表名 change 列名新列名新数据类型；改名字和类型；

eg:alter table stu change gender sex varchar(20); 改名字和类型；

alter table 表名 modify 列名新数据类型; 只改类型；

eg:alter table stu modify sex varchar(10); 只改类型；

-- 5.删除列

alter table 表名 drop 列名；

eg: alter table stu drop sex;

#4.D(delete) 删除

drop table 表名；

drop table if exists 表名；

part2:DML(data Manipulation Language)数据库操作语言

## crud

# 1.C(create) 创建-添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

eg：insert into stu (id,name,age) values (1,"李华",18); 单双引号都行

如果表名后，不定义列名，则默认给所有列添加值；

除了数字类型，其他类型需要使用引号（单双都可以）引起来；

insert into stu values (2,"小明",19,99.9,null,null); 默认给空值null

# 2.R(Retrieve) 查询-查询表中的记录

select * from 表名；

# 3.U(update) 修改

update 表名 set 列名1=值1,列名2=值2,...[where 条件];

eg:update stu set age =80,score=99 where id =1;

update 表名 set age =20; 后不加where，改所有数据age

# 4. D(delete) 删除

delete from 表名 [where 条件]

eg:delete from stu where id=1; 删除一行数据；

delete from stu;删除所有数据，不推荐使用。有多少条记录就会执行多少次删除操作；

truncate table 表名；推荐使用，效率更高，先删除表，然后再创建一张一样的表；

[trʌŋˈkeɪt] vt.删除；

part3:DQL(data Query Language)数据库查询语言

## 基础查询

# 1.查询

select * from 表名;

select 字段列表

from 表名

where 条件列表

group by 分组字段

having 分组之后的条件

order by 排序

limit 分页限定

# 2.去重查询

select distinct address from student;

# 3.计算和

select name,math,english,math + ifnull(english,0) from student;

# 4.起别名

select name,math,english,math + ifnull(english,0) as 总分 from student;

select name,math,english,math + ifnull(english,0) 总分 from student;

## 条件查询

# 1.where子句后跟条件，><<=>==<>

between and

in

like

is null

and

or

not

eg:查询年龄不等于20岁

select * from student where age !=20;

select * from student where age <> 20;

select * from student where age between 20 and 30; 包括[20,30]

查询年龄22岁，18岁，25岁的信息

select * from student where age =22 or age =18 or age =25;

select * from student where age in (22,18,25); in 与or结果一样；

is null 使用

select * from student where english is null; -- null不能用=

select * from student where english is not null; -- null不能用=

## 模糊查询

select * from student where name like '马%';

select * from student where name like '_天%';查询第二个字是天

select * from student where name like '___';查询三个字的人名单双引号都行；

## 排序查询

order by 排序字段1 排序方式1,排序字段2 排序方式2

select * from student order by math asc; -- 升序

select * from student order by math desc; -- 降序

-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名；

select * from student order by math asc,english desc; -- 只有第一条件数学成绩一样时，才会按照第二条件英语成绩排序；

## 聚合函数

将一列数据作为一个整体，进行纵向的计算；

count

max

min

sum

avg

注意：聚合函数的计算，是排除null值的；

*的含义是：只要一行数据中，有一列不为空，就把一行，算一个数；

*号，不推荐，一般选择不为空的一列（一般是主键id）；

select * from project p ; -- 205行数据

select count(pjt_name) from project p ; -- 单个值 127

select count(ifnull(pjt_name,0)) from project p ; -- 单个值 205

select count(*) from project p ; -- 单个值205

select count(1) from project p; -- 单个值205

区别：

1.COUNT(字段) 只计算该字段中非 NULL 的值的数量。如果某行的指定字段值为 NULL，则这一行不会被计入统计。

2.COUNT(*) 计算表中所有行的数量，无论这些行中的任何列是否包含 NULL 值。

3.COUNT(1) 或使用任何常量作为参数，其行为类似于 COUNT(*)，即计算所有行的数量，但与 COUNT(*) 不同的是，它不需要访问表的所有列，因此在某些情况下可能会更快。

select max(math) from student;

select min(math) from student;

select sum(math) from student;

select avg(math) from student;

## 分组函数

1.分组之后查询的字段：分组字段、聚合函数

2. where 和 having 的区别？

(1).where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来

(2).where 后不可以跟聚合函数，having可以进行聚合函数的判断。

因为group by 是把看做一个整体来看，所以前面，写某一字段或*，就没有任何意义；

-- 按照性别分组，分别查询男、女同学的平均分；

select sex,avg(math) from student group by sex;

select name, sex,avg(math) from student group by sex; -- 这个name加的就没意义；

-- 按照性别分组，分别查询男、女同学平均分，人数。要求:分数低于70分的人，不参与分组。

select sex,avg(math),count(id) from student where math>70 group by sex; -- 分组之前，对分组条件进行限定；

-- 按照性别分组，分别查询男、女同学平均分，人数。要求:分数低于70分的人，不参与分组。分组之后，人数要大于2个人

select sex,avg(math),count(id) from student where math>70 group by sex having count(id)>2; -- 人数大于2，前面count(id)就是人数，所以having中用count(id)

select sex,avg(math),count(id) 人数 from student where math >70 group by sex having 人数>2; -- 起别名，having用别名判断；

## 分页查询

从0开始，查3条；

-- 每页显示3条记录

select * from student limit 0,3; -- 第1页 123

select * from student limit 3,3; -- 第2页 456

select * from student limit 6,3; -- 第3页 789 101112 131415

-- 公式：

开始索引 =（当前的页码-1）*每页显示的条数

结束索引 = 开始索引+每页显示条数-1

## 其他1-约束

# 约束：对表中的数数据进行限定，保证数据的正确性、有效性和完整性；

主键约束 primary key

非空约束 not null

唯一约束 unique

外键约束 foreign key

# 非空约束not null 【某一列的值不能是null】

1.创建表同时添加非空约束

create table stu(id int,name varchar(20) not null ); -- name 非空约束

2.创建完后，添加非空约束；

alter table stu modify name varchar(20) not null;

3.删除name的非空约束

alter table stu modify name varchar(20); -- 删除name的非空约束；

# 唯一约束 unique 【某一列的值不能重复】【唯一可以添加空的，但是不能重复添加null】

1.创建表时添加唯一约束

create table stu(id int,phone_number varchar(20) unique ); -- 手机号唯一

2.删除唯一约束

alter table stu modify phone_number varchar(20); -- 这个删不掉唯一约束；modify只改类型，change改名字和类型；

alter table stu drop index phone_number; -- 这个可以删掉唯一约束；

3.创建完表后，添加唯一约束，如果数据有重复，无法添加唯一约束；

alter table stu modify phone_number varchar(20) unique;

# 主键约束 primary key

1.创建表时，添加主键约束

create table stu (id int primary key ,name varchar(20)); -- 给id添加主键约束；

2.删除主键

alter table stu drop primary key; -- 正确的删除方式

3.创建完后，添加主键

alter table stu modify id int primary key ;

# 自动增长

1.概念：如果某一列是数值类型的，使用auto_increment可以来完成值得自动增长

2.在创建表时，添加主键约束，并且完成主键自增长

create table stu (id int primary key auto_increment,name varchar(20));

删除自动增长

alter table stu modify id int;

添加自动增长

alter table stu modify id int auto_increment;

# 外键约束 foreign key 在多的一方建立外键，指向一的一方主键；

1.外键的作用：

数据完整性：

确保 employee 表中的 dep_id 值必须存在于 department 表的 id 列中。

如果尝试插入或更新 employee 表中的 dep_id 为一个不存在于 department 表中的值，数据库会拒绝操作。

2.级联操作（可选）：

可以定义外键的级联行为，例如：

ON DELETE CASCADE：如果 department 表中的某条记录被删除，employee 表中引用该记录的所有行也会被自动删除。

ON UPDATE CASCADE：如果 department 表中的某条记录的 id 被更新，employee 表中引用该记录的所有行的 dep_id 也会被自动更新。

1.创建部门表：主表，一方；

CREATE TABLE department(

id INT PRIMARY KEY AUTO_INCREMENT,

dep_name VARCHAR(20),

dep_location VARCHAR(20)

);

插入部门数据：

INSERT INTO department VALUES(NULL, '研发部', '广州'), (NULL, '销售部', '深圳');

2.创建员工表：从表，多方；

CREATE TABLE employee(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20),

age INT,

dep_id INT -- 外键对应主表的主键

);

插入员工数据：

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);

外键写到从表中；

-- 创建员工表（从表），并直接添加外键约束

CREATE TABLE employee(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20),

age INT,

dep_id INT,

CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)

create table 表名(

外键列

constraint 外键名 foreign key （从表-外键列名称） references 主表名称（主表列名称）

);

删除外键

alter table employee drop foreign key emp_dept_fk;

添加外键

alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id); -- 外键名随便，dep_id从表中列，department(id)主表中的列

update employee set dep_id=null where dep_id=1; 外键可以为null

# 外键约束-级联操作:删除一个，其他级联的表也会删除，效率会低。所以实际开发用的时候，很谨慎

ALTER TABLE 表名

ADD CONSTRAINT 外键名称

FOREIGN KEY (外键字段名称)

REFERENCES 主表名称 (主表列名称)

ON UPDATE CASCADE

ON DELETE CASCADE;

参数说明：

表名：需要添加外键的从表名称。

外键名称：外键约束的名称，可以自定义。

外键字段名称：从表中用于引用主表的字段。

主表名称：被引用的主表名称。

主表列名称：主表中被引用的列（通常是主键）。

ON UPDATE CASCADE：当主表的主键值更新时，自动更新从表中的外键值。

ON DELETE CASCADE：当主表的记录被删除时，自动删除从表中引用该记录的行。

eg：

ALTER TABLE employee

ADD CONSTRAINT emp_dept_fk

FOREIGN KEY (dep_id)

REFERENCES department(id)

ON UPDATE CASCADE

ON DELETE CASCADE;

==================================================

## 其他2-数据库的设计

1.多表之间的关系

一对一：人身份证；

一对多：部门，员工；多的一方建立外键，指向一的一方主键；

多对多：学生和课程；需要借助第三张表；

2.数据库设计的范式：

第一范式(1NF):每一列都是不可分割的原子数据项

第二范式(2NF):在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式(3NF):在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

=======================

## 其他3-数据库的备份

1.备份：

mysqldump -u用户名 -p密码数据库名称 >保存的路径

mysqldump -uroot -proot db1 > d://a.sql 备份数据库db1

2.还原

drop database db1; 删除

create databse db1：创建

use db1; 使用数据库

source d://a.sql 还原数据库；

================================

## 多表查询

#### 多表查询的分类

select * from dept;

select * from emp;

select * from dept,emp;

# 1.交叉连接，结果是笛卡尔积AxB

注意：交叉连接不考虑where后的限定条件；

select * from emp,dept; -- 传统的隐式连接语法

select * from emp cross join dept; -- 显示的cross join 语法（推荐使用）

# 2.内连接查询

（1）隐式内连接-where连接。可读性较差，连接条件和过滤条件混在一起。

注意，在实际执行中，MySQL 的查询优化器会尝试优化查询，并不会真的先生成完整的笛卡尔积再过滤。

性能在大多数情况下，显式内连接和隐式内连接的性能是相同的，因为 MySQL 的优化器会将它们优化为相同的执行计划。

select * from emp,dept where emp.dept_id = dept.id;

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息

SELECT * FROM emp t1, dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';

（2）显示内连接-on连接【推荐使用】。可读性较好，连接条件和过滤条件分离

（2.1）正常写法：inner join on

select * from emp inner join dept on emp.dept_id = dept.id;

（2.2）省略写法：join on

select * from emp join dept on emp.dept_id = dept.id;

# 3.外连接

（1）左外连接（LEFT OUTER JOIN）

左外连接返回左表（LEFT JOIN 左侧的表）的所有行，即使右表中没有匹配的行。如果右表中没有匹配的行，则结果中右表的列会显示为 NULL。

SELECT emp.name AS 员工, dept.name AS 部门

FROM emp

LEFT JOIN dept ON emp.dept_id = dept.id;

（2）右外连接（RIGHT OUTER JOIN）

右外连接返回右表（RIGHT JOIN 右侧的表）的所有行，即使左表中没有匹配的行。如果左表中没有匹配的行，则结果中左表的列会显示为 NULL。

SELECT emp.name AS 员工, dept.name AS 部门

FROM emp

RIGHT JOIN dept ON emp.dept_id = dept.id;

（3）全外连接（FULL OUTER JOIN）

全外连接返回左表和右表的所有行。如果某一行在另一个表中没有匹配的行，则结果中对应的列会显示为 NULL。

SELECT 列名

FROM 左表

FULL JOIN 右表 ON 左表.列 = 右表.列;

注意：MySQL 不支持 FULL OUTER JOIN，但可以通过 UNION 实现类似的效果。

SELECT emp.name AS 员工, dept.name AS 部门

FROM emp

LEFT JOIN dept ON emp.dept_id = dept.id

UNION

SELECT emp.name AS 员工, dept.name AS 部门

FROM emp

RIGHT JOIN dept ON emp.dept_id = dept.id;

# 4. 子查询

# 5. case when

# 1. 简单 CASE 表达式：适用于对单一字段进行多条件匹配：

CASE 字段

WHEN 值1 THEN 结果1

WHEN 值2 THEN 结果2

...

ELSE 默认结果

end

eg: 1.将 gender 字段的男/女转换为 Male/Female：

SELECT

name,

CASE gender

WHEN '男' THEN 'Male'

WHEN '女' THEN 'Female'

ELSE 'Unknown'

END AS gender_en

FROM emp;

----------------------------------------------------------------------

# 2. 搜索 CASE 表达式：适用于复杂的多条件判断（支持多个字段或表达式）：

CASE

WHEN 条件1 THEN 结果1

WHEN 条件2 THEN 结果2

...

ELSE 默认结果

end

场景 1：数据分类（如工资等级）

根据工资划分员工等级：

SELECT

name,

salary,

CASE

WHEN salary >= 7000 THEN '高薪'

WHEN salary BETWEEN 5000 AND 6999 THEN '中薪'

ELSE '低薪'

END AS salary_level

FROM emp;

场景 2：动态字段生成（如性别转换）

将 gender 字段的男/女转换为 Male/Female：

SELECT

name,

CASE gender

WHEN '男' THEN 'Male'

WHEN '女' THEN 'Female'

ELSE 'Unknown'

END AS gender_en

FROM emp;

场景 3：条件聚合统计

统计各部门男女人数：

SELECT

dept_id,

SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) AS female_count

FROM emp

GROUP BY dept_id;

场景 4：处理 NULL 值

将 NULL 转换为默认值：

SELECT

name,

CASE

WHEN salary IS NULL THEN 0

ELSE salary

END AS safe_salary

FROM emp;

场景 5：在 ORDER BY 中排序

优先显示女性员工：

SELECT name, gender

FROM emp

ORDER BY

CASE WHEN gender = '女' THEN 0 ELSE 1 END,

name;

select @@autocommit; -- 查看事务默认提交方式

select @@tx_isolation; -- 查看全局事务隔离级别

part4:DCL(data Control Language)数据库控制语言

## 管理用户

通配符 %表示可以在任意注意使用用户登录数据库；可以远程访问数据库；

# 1.创建用户

create user '用户名'@'主机名' identified by '密码';

create user 'zhangsan'@'localhost' identified by '123';

create user 'lisi'@'%' identified by '123';

# 2.删除用户

drop user '用户名'@'主机名';

drop user 'zhangsan'@'localhost' ;

# 3. 修改用户密码

方式一：

update user set password=password('新密码') where user ='用户名';

update user set password=password('abc') where user ='123';

ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码'; mysql8.0之后的版本；

方式二：