Mysql——SQL语句

SQL语句在功能上主要分为四类：

• DDL（Data Definition Languages，数据定义语言）主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。
• DML（Data Manipulation Language，数据操纵语言）主要是对数据进行增加、删除、修改操作。
• DQL（Data Query Language，数据查询语言）主要是对数据进行查询操作。
• DCL（Data Control Language，数据控制语言）主要是用来设置/更改数据库用户权限。

一、DDL

1.1数据库基本操作

查询所有数据库

show databases;

查询当前正在操作的数据库

select database();

创建数据库(方括号内为可选参数)

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

删除

drop database [if exists] 数据库名;

使用

use 数据库名;

 注：

• [ ]中的内容都是可选的
• [if not exists ]：如果库不存在，则进行创建，否则，不进行操作

1.2实例

登录并查询所有数据库

创建并查询数据库

删除数据库

使用数据库

1.3表查询操作 

表创建

create table 表名(
    字段1 字段1类型 [comment 字段1注释],
    字段2 字段2类型 [comment 字段2注释],
    字段3 字段3类型 [comment 字段3注释],
    ...
    字段n 字段n类型 [comment 字段n注释]
)[comment 表注释];

查询当前所有数据库所有表

show tables;

查询表结构

desc 表名

查询指定表的建表语句

show create table 表名;

 1.4实例

1.5表修改操作 

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释];

 修改字段

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释];

 修改表名

alter table 表名 rename to 新表名;

删除字段

alter table 表名 drop 字段名;

 删除表

drop table [if exists] 表名;

删除指定表并重新创建该表

truncate table 表名;

1.6数值类型

数值类型

字符串类型

日期事件类型

二、DML

2.1添加数据

给指定字段添加数据

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

给全部字段添加数据

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

批量添加数据

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

注：

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

2.2修改数据

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

注： 

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

2.3删除数据

delete from 表名 [where 条件];

注：

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE语句不能删除某一个字段的值（可以使用UPDATE）

三、DQL

3.1基本查询

查询多个字段

select 字段1,字段2,字段3... from 表名;
select * from 表名;

设置别名

select 字段1[as 别名1],字段2[as 别名2],字段3[as 别名3]... from 表名;

去除重复记录

select distinct 字段列表 from 表名;

 3.2条件查询

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

3.3聚合函数

        聚合函数将一列数据作为整体进行计算。

select 聚合函数(字段) from 表名;

注：

• null值不参与任何聚合函数运算。 

3.4分组查询 

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

注：

• where在分组之前进行过滤，不满足条件不参与分组，having是对分组后的结果进行过滤。
• where不能对聚合函数进行过滤，having可以。

3.5排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

排序关键字：ASC（升序），DESC（降序），默认为升序。

注：

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。 

3.6分页查询 

select 字段列表 from 表名 limit 起始索引,查询记录数;

注：

• 起始索引从0开始，起始索引=（查询列表-1）*每页显示记录数。
• 如果查询的是第一页数据，起始索引可以省略。 

3.7执行顺序

四、DCL

4.1用户管理

查询用户：

use mysql;
selecr * from user;

创建用户

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

修改用户密码

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

删除用户：

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

注：

• 在Mysql中，用户所具有的信息以及用户所具有的权限信息都是存放在mysql中的user表中，同时知道主机地址和用户名才能完整的定位一个用户。 

4.2权限控制

常见权限：

查询权限

show grants for '用户名'@'主机名';

授予权限

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

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注：

• 多个权限之间，使用逗号分隔。
• 授权时，数据库名和表名可以使用*进行通配，代表所有。 

五、函数 

5.1字符串函数

 常用的字符串函数：

 -- concat
 select concat('hello','mysql');  #hellomysql
 
 -- lower
 select  lower('Hello');    #hello
 
 -- upper
 select  upper('hello');    #HELLO
 
 -- lpad
 select lpad('01',5 ,'-');   #---01
 
 -- rpad
 select rpad('01',5 ,'-');   #01---
 
 -- trim
 select trim(' hello mysql ');  #hello mysql
 
 -- substring
 select substring('hello world',1,5);  # hello (从 1 开始数的)

5.2数值函数

 -- 数值函数
 -- ceil 向上取整
select ceil(1.2);              # 2
 
 -- floor 向下取整
select ceil(1.9);              # 1
 
 -- mod  （x / y）的模
select mod(6, 4);              # 2
 
 -- rand
select rand();
 
 -- round
 select round(2.345, 2);       # 2.35

5.3日期函数

 -- ------------------------------------------------------ 日期函数
 -- curdate()
select curdate();  # 2025-2-5
 
 -- curtime()
select curtime();  # 20:50:12
 
-- now()
 select now();  # 2025-2-5 20:50:12
 
 -- YEAR, MONTH, DAY
select YEAR(now());
select MONTH(now());
select DAY(now());
 
 -- DATA_ADD(data, INTERVAL expr type)
select date_add(now(), INTERVAL 70 DAY); # 2024-01-03 13:27:39
 
 -- DATEDIFF(date1, date2)
select datediff('2024.1.13', '2023.10.25');  # 80 
select datediff('2001.7.1', '2023.10.25');  # 8151
 

 5.4流程函数

 -- -------------------------------------流程函数
 -- IF(value,t,f)
select if(true, 'ok', 'error');  #ok
 
-- IFNULL(value1,value2)
select ifnull('ok', 'default');  #ok
select ifnull('', 'default');  #''
select ifnull(null, 'default');  #default
 
-- CASE WHEN [val1] THEN [res1]....ELSE[default] END
 
-- CASE [expr] WHEN [val1] THEN [res1]....ELSE[default] END
 
-- 需求：查询emp表员工姓名和城市（北京，上海---->一线城市，其他----->二线城市）
select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else'二线城市' end) as '工作地址'
from emp;