MySQL数据库（二）- SQL

目录

​编辑

一 DDL

(一 数据库操作

1 查询-数据库（所有/当前）

2 创建-数据库

3 删除-数据库

4 使用-数据库

(二 表操作

1 创建-表结构

2 查询-所有表结构名称

3 查询-表结构内容

4 查询-建表语句

5 添加-字段名+数据类型

6 修改-字段数据类型

7 修改-字段+字段数据类型

8 删除-字段

9 修改-表名

10 删除-表

11 删除-表中数据

(三 Datagrip图形化界面激活码使用

二 DML-表-增删改数据

(一 添加数据

1 给指定字段添加数据

2 给全部字段添加数据(逐个)

3 批量添加数据

(二 修改数据

1 修改表中的数据

2 删除表中的数据

三 DQL-表-查数据

 查询数据

(一 基础查询

(二 条件查询 - where

(三 聚合查询 - count/max/min/sum/avg

(四 分组查询 - group by ... having

(五 排序查询 - order by

(六 分页查询 - limit

(七 补充

四 DCL-权限控制

(一 查询用户

(二 创建用户

(三 修改用户密码

(四 删除用户

(五 权限控制

---

一 DDL

(一 数据库操作

1 查询-数据库（所有/当前）

1 所有数据库：

show databases;

2 查询当前数据库：

select database();

2 创建-数据库

可以定义数据库的编码方式

create database if not exists ax1;

create database ax2;

3 删除-数据库

drop database ax1;

4 使用-数据库

use ax1;

select database();

切换当前数据库位置并验证是否为当前数据库：

(二 表操作

1 创建-表结构

2 查询-所有表结构名称

show tables;

图形化展示

3 查询-表结构内容

desc myfirst_tb;

图形化展示：

4 查询-建表语句

show create table first_tb;

图形化展示：

5 添加-字段名+数据类型

alter table 表名 add 字段名 类型 注释;

alter table emp add nickname varchar(10) comment '昵称';

图形化展示：

6 修改-字段数据类型

alter table 表名 modify 字段名 新数据类型;

alter table emp modify IDcard char(19);

图形化展示

7 修改-字段+字段数据类型

alter table 表名 change 旧字段名 新字段名 类型 comment 注释;

alter table emp change IDcard idcard char(18) comment '身份证';

图形化展示：

8 删除-字段

alter table 表名 drop 字段名;

alter table emp drop ax2;

图形化展示：

9 修改-表名

alter table 表名 rename to 新表名;

alter table emp rename to newemp;

图形化展示：

10 删除-表

drop table if exists 表名;

drop table if exists 表名;

图形化展示：

11 删除-表中数据

表结构保留：TRUNCATE TABLE 只会删除表中的所有数据，但不会删除表本身或其结构定义。因此，使用 DESC 或 SHOW COLUMNS FROM myfirst_tbl1; 时，您仍然可以看到表的字段定义。

truncate table 表名;

truncate table myfirst_tb1;

图形化展示：

(三 Datagrip图形化界面激活码使用

网址：

2025 最新Jetbrains激活服务器 全部产品均适用 | ahhhhfs - A姐分享

二 DML-表-增删改数据

1 概念：DML全称Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改。

• 添加数据：（INSERT）
• 修改数据：（UPDATE）
• 删除数据：（DELETE）

(一 添加数据

1 给指定字段添加数据

insert into 表名 （字段名1，字段名2...）values （值1，值2...）;

2 给全部字段添加数据(逐个)

insert into 表名 values (值1，值2);

3 批量添加数据

insert into 表名（字段名1，字段名2...）values (值1，值2...),(值1，值2)...;

insert into 表名  values（值1，值2...）;

# 逐个添加
insert into first_tb (id, name, age, gender) values (2,'超哥',100,'男');insert into first_tb values (3,'小明',100,'女');# 批量添加
insert into first_tb (id, name, age, gender) values (4,'张三',100,'男'),(5,'李四',100,'男'),(6,'刘六',21,'男');insert into first_tb values (7,'王五',32,'男'),(8,'he',12,'男'),(9,'ok',22,'女');# 查看table 数据
select * from first_tb;

注意事项：

• 插入数据时，指定的字段顺序需要与值的顺序一一对应。
• 字符串和日期类型应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

(二 修改数据

1 修改表中的数据

updata 表名 set 字段名1 = 值1 ，字段名2 = 值2 ，.......where 条件；

注意事项：

• 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

# 将first_tb表当中的id为1的数据项的name修改为it
update first_tb set name = 'it' where id = 1;# 将first_tb表当中的name为it的数据项的id修改为0,name修改为it_er
update first_tb set id = 0,name='it_er' where name = 'it';

2 删除表中的数据

delete from 表名 where 条件;

注意事项：

• 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
• 可以使用and or 进行范围判定。

# 删除id为0的数据项
delete from first_tb where id = 0;

# 删除id为3并且姓名为小明的数据项
delete from first_tb where id = 3 and name='小明';# 删除id为3或姓名为小明的数据项
delete from first_tb where id = 3 or name='小明';

三 DQL-表-查数据

1 概念：DQL全称Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

• 查询数据关键字：（SELECT） 

 查询数据

(一 基础查询

1 查询多个字段

select 字段1，字段2...from 表名;

# 返回first_tb表中字段name和id字段列表
select name,id from first_tb;

2 查询所有字段

select  * from 表名;

# 返回first_tb表中的所有字段列表
select * from first_tb;

3 设置别名并查询

select 字段1 as 别名1 ，字段2 as 别名2 ...from 表名;

# 设置别名(as可省略)作用是将表中的字段显示给我们时更加容易区分
select id as ID , name as NAME from first_tb;

4 去除重复项

select distinct 字段列表 from 表名;

# 删除id重复项
select distinct id from first_tb;

5 代码实现

insert into user_pro (id, worker, gender, age, idcard, workaddress, entrydate, name)
values (1, '1000000101', '男', 18, '123456789123456781', '安徽合肥', '2000-01-01', '超哥'),(2, '1000000102', '男', 28, '123456789123456782', '安徽阜阳', '2000-11-01', '张三'),(3, '1000000103', '男', 38, '123456789123456783', '安徽六安', '2000-02-01', '李四'),(4, '1000000104', '女', 48, '123456789123456784', '安徽淮北', '2000-03-01', '王五'),(5, '1000000105', '女', 58, '123456789123456785', '安徽亳州', '2000-04-01', '刘六'),(6, '1000000106', '女', 68, '123456789123456786', '安徽马鞍山', '2000-05-01', '周七'),(7, '1000000107', '女', 78, '123456789123456787', '安徽黄山', '2000-01-11', '安八'),(8, '1000000108', '女', 88, '123456789123456788', '安徽滁州', '2000-01-31', '老八'),(9, '1000000109', '男', 98, '123456789123456789', '安徽安庆', '2000-01-21', '李白'),(10, '1000000110', '男', 18, '12345678912345678X', '安徽芜湖', '2000-01-21', '杜甫'),(11, '1000000111', '男', 28, '123456789123456711', '安徽宿州', '2000-01-11', '骆宾王'),(12, '1000000112', '女', 38, '123456789123456722', '安徽蚌埠', '2000-03-01', '齐白石'),(13, '1000000113', '女', 48, '123456789123456733', '安徽宣称', '2000-06-01', '薛之谦'),(14, '1000000114', '女', 58, '123456789123456744', '安徽池州', '2000-08-01', '小白'),(15, '1000000115', '男', 68, '123456789123456755', '安徽铜陵', '2000-05-01', '小黑'),(16, '1000000116', '男', 78, '123456789123456766', '安徽合肥', '2000-03-01', '小蓝'),(17, '1000000117', '男', 88, '123456789123456777', '安徽合肥', '2000-07-01', '小一'),(18, '1000000118', '男', 98, '123456789123456788', '安徽合肥', '2000-08-01', '小儿'),(19, '1000000119', '男', 18, '123456789123456799', '安徽合肥', '2000-09-01', '小三'),(20, '1000000120', '男', 28, '12345678912345679X', '安徽合肥', '2000-10-01', '小四'),(21, '1000000121', '男', 38, '12345678912345677X', '安徽合肥', '2000-01-01', '小五');
update user_pro set workaddress='安徽淮南' where id=16;# 1 查询name age worker字段列表
select name,age,worker from user_pro;
# 2 查询所有字段
select name,id,worker,workaddress,gender,idcard,entrydate,age from user_pro;
select * from user_pro;
# 3 查询所有员工的工作地址（将列表名显示为工作地址 as 可省略）
select workaddress as '工作地址' from user_pro;
# 4 查询员工的工作地址（去重）
select distinct workaddress from user_pro;

(二 条件查询 - where

1 语法

select 字段列表 from 表名 where 条件列表；

2 条件符号

3 代码实现：

# 1 查询年龄等于88的员工信息
select * from users where age = 88;# 2 查询年龄小于30的员工信息
select * from users where age <= 30;# 3 查询没有身份证的员工信息
select * from users where idcard is null ;# 4 查询有身份证的员工信息
select * from users where idcard is not null;# 5 查询年龄不等于88和78的员工信息
select * from users where age != 88 and age != 78;
select * from users where age <> 88 and age <> 78;# 6 查询在28至58之间的员工信息(左右都包括)
select * from users where age between 28 and 58;
select * from users where age >= 28 && age <= 58;# 7 查询性别为女且年龄小于58的员工信息
select * from users where gender = '女' and age < 58;# 8 查询姓名为三个字的员工信息 <--> _ 单个字符  % 任意数个
select * from users where name like '___';# 9 查询年龄为18 28 38 的员工信息
select * from users where age = 18 or age = 28 or age = 38;
select * from users where age in (18,28,38);# 10查询身份证号最后一位是X的员工信息
select * from users where idcard like '%X';
select * from users where idcard like '_________________X'

(三 聚合查询 - count/max/min/sum/avg

1  语法

select 聚合函数 （字段列表）from 表名;

2 聚合函数：将一列数据作为一个整体，进行纵向计算。

• count:  统计数量
• max  :最大值
• min : 最小值
• avg : 平均值
• sum : 求和

3 注意事项

• null不参与计算

4 代码实现：

# 求员工最大年龄
select max(age) from users;# 统计员工数量
select count(name) from users;# 求平均年龄
select sum(age)/count(name) from users;
select avg(age) from users;# 求最小年龄
select min(age) from users;# 统计安徽合肥地区员工的年龄之和
select * from users where workaddress = '安徽合肥';
select sum(age) from users where workaddress = '安徽合肥';

(四 分组查询 - group by ... having

1 语法

select  字段列表 from 表名 where 条件 group by 字段分组名 having 分组后过滤条件 ;

2 where 与 having 的区别

1. 执行时机不同：where 是分组之前进行过滤，不满足where ,不参与分组；二having 是分组之后对结果进行过滤。
2. 判断条件不同：where 不能对聚合函数进行判断，而having 可以。

3 注意事项：

1. where先判断，在进行聚合函数的处理，再进行having 的判断处理。
2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。

4 代码实现：

# 1 根据性别分组，统计男性员工 和 女性员工的数量
select gender,count(*) from users group by gender;# 2 根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from users group by gender;# 3 查询年龄小于等于58的员工，根据共组地址分组获取员工数量大于等于2的地址
select workaddress,count(*) from users where age <= 58 group by workaddress having count(*)>=2;

(五 排序查询 - order by

1 语法

select 字段列表 from 表名 order by 字段1  排序方式1 ，字段2  排序方式2；

2 排序方式

• ASC：升序（默认值）
• DESC：降序

3 注意事项：

• 如果是多段字段排序，当地一个字段值相同时，会按第二个字段排序规则。

4 代码实现：

# 1 根据年龄对公司的员工进行升序排序
select * from users order by age ASC ;# 2 根据入职时间对公司员工进行降序排序
select * from users order by entrydate DESC ;# 3 根据年龄对公司员工进行升序排序，年龄相同在按照入职时间进行降序排序
select * from users order by age ASC , entrydate DESC;

(六 分页查询 - limit

1 语法

select 字段列表 from 表名 limit 起始索引，查询次数；

2 注意事项：

1. 起始索引从0开始，其实索引 = （查询页码 - 1）* 每页显示记录数。
2. 分页查询时数据库的方言，不同数据库有不同的实现，MySQL是limit。
3. 如果查询的是第一页的数据，其实索引可以省略，之间简写为limit 10；

3 代码实现：

# 1 从第1个开始展示，展示10个
select * from users limit 0,10;# 2 从第5个开始展示，展示5个
select * from users limit 4,5;

(七 补充

练习：

# 1 查询年龄为28，18，38的员工信息
select * from users where age in (28,18,38);# 2 查询姓名两个字性别为男并且年龄为38至68之间的员工信息
select * from users where age between 38 and 68 && name like '__';# 3 统计员工表中奈年龄小于等于68，男性和女性分别的人数(age统计的是非空，如果想包含null则最好使用*)
select gender,count(age) from users where age <= 68 group by gender;# 4 查询所有年龄小于68岁的员工的姓名和年龄，并对查询结果按照年龄升序排序，如果年龄相同按入职时间降序排序
select name,age from users where age <= 68 order by age ASC ,entrydate DESC ;# 5 查询性别为男，并且年龄在28-68之间的前五个员工信息，对查询结果按年龄升序排序，年龄相同的按入职时间升序排序
select * from users where age between 28 and 68 and gender='男' order by age ASC , entrydate DESC limit 0,5;

编写顺序与执行顺序

四 DCL-权限控制

1 概念：全称Data Control Language(数据控制语言)，用来管理数据库用户，控制数据库的访问权限。

注意事项：

• 主机名可以使用%通配（任意）
• 这类SQL开发成员操作的较少，主要是DBA(Data Administrator 数据库管理员) 使用。

2 图形化展示

(一 查询用户

1 语法 

• use mysql;
• select * from user;

2 代码实现

# 查询数据库
use mysql;
select * from user;

(二 创建用户

1 语法

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

2 代码实现

# 创建一个用户anxian1 只能在当前主机localhost访问，密码123456
create user 'anxian1'@'localhost' identified by '123456';# 创建用户anxian2 可以在任意主机访问数据库，密码654321
create user 'anxian2'@'%' identified by '654321';

(三 修改用户密码

1 语法

alter user '用户名'@’主机名‘ identified with mysql_native_password by '新密码'；

2 代码实现

# 修改用户anxian2 访问密码改为123456
alter user 'anxian2'@'%' identified with mysql_native_password by '123456';

(四 删除用户

1 语法

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

2 代码实现

# 删除anxian1@localhost用户
drop user 'anxian1'@'localhost' ;

(五 权限控制

MySQL中定义了很多权限，常见的就以下几种

1 查询权限

show grants for  '用户名'@‘主机名’；

# 查询权限
show grants for 'anxian2'@'%';

2 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@‘主机名’；

# 授予权限
grant all on itheima.* to 'anxian2'@'%';

3 撤销权限

revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；

# 撤销权限
revoke all on itheima.* from 'anxian2'@'%';