Mysql 容易忘的 sql 指令总结

目录

一、操作数据库的基本指令

二、查询语句的指令

1、基本查询语句

2、模糊查询

3、分支查询

4、 分组查询

5、分组查询

6、基本查询总结：

7、子查询

8、连接查询

三、MySQL中的常用函数

1、时间函数

2、字符串函数

3、聚合函数

4、运算函数

四、表的操作指令

五、权限管理的指令

六、SQL 优化中使用的指令

（1）索引的使用

1、创建索引

2、查看索引

3、测试索引

4、删除索引

（2）存储引擎的相关指令

1、查看表引擎

2、修改表引擎

一、操作数据库的基本指令

— 连接mysql数据库

— mysql -u用户名 -p密码

mysql -uroot -p123456

— 查看所有数据库

show databases;

— 创建数据库 默认编码为utf8

— create database 数据库名;

create database java11;

— 创建数据库 指定编码格式

— create database 数据库名 character set 编码格式;

create database java111 character set ‘gbk’;

— 创建数据库 判断数据库不存在再创建

create database if not exists java111 character set ‘gbk’;

— 查看数据库的创建信息

— show create database 数据库名;

show create database java11;

— 删除数据库

— drop database 数据库名;

drop database java11;

— 删除数据库 如果存在才删除

— drop database if exists 数据库名;

drop database if exists java11;

— 修改数据库

— alter database 数据库名 character set ‘字符集’

alter database java111 character set ‘utf8’;

— 查看当前使用的数据库

select database();

— 连接数据库

— use 数据库名;

use java111;

二、查询语句的指令

1、基本查询语句

— 查询不是10部门的员工信息

select * from t_employees where DEPARTMENT_ID 10; — 也是 != 的意思

select * from t_employees where DEPARTMENT_ID != 10;

— 查询工资在 10000 到 15000之间的员工信息

— between 起始值 and 终止值 包含起始值和终止值

— 写法1： select * from t_employees where salary > 10000 and salary < 15000;

— 写法2： select * from t_employees where SALARY BETWEEN 10001 and 14999;

2、模糊查询

— 模糊条件(like not like)

— 占位符 % 表示0个或多个任意字符 _表示一个任意字符

— 查询所有员工姓氏中包含’S’的员工信息

select * from t_employees where last_name like ‘s’; — 此时的like相当于=号

select * from t_employees where last_name like ‘%S%’; — 查询所有员工姓氏中’S’开头的员工信息 select * from t_employees where last_name like ‘S%’;

— 查询所有员工姓氏中第二个字母为’S’的员工信息

select * from t_employees where last_name like ‘_S%’;

— 查询所有员工姓氏中’S’开头并且姓氏的长度至少7个字符的员工信息

select * from t_employees where last_name like ‘S%’ and last_name like ‘%___%’; — 7个_

— 查询所有员工姓氏中不包含’S’的员工信息

select * from t_employees where last_name not like ‘%S%’;

3、分支查询

— 分支查询，相当于构建一个字段

/*

case when 条件1 then 结果1

when 条件2 then 结果2

when 条件3 then 结果3

…

else 不满足条件的结果

end 别名

*/

— 查询所有的员工信息，工资按照登记划分(12000以上A类、10000以上B类、8000以上C类、5000以上D类、其他E类)

select last_name,salary, case when salary > 12000 then ‘A类’

when salary > 10000 then ‘B类’

when salary > 8000 then ‘C类’

when salary > 5000 then ‘D类’

else ‘E类’

end salGrade from t_employees;

4、 分组查询

— 语法1： select 列1,列2,.. from 表名 group by 分组字段

— 语法2： select 列1,列2,.. from 表名 group by 分组字段 having 分组条件

— 查询2018年的各渠道的消费数量

select CHANNEL_CODE,count(1) from user_order uo group by channel

having ORDER_DATE =’2018-01-01′;

— 注意：上述语句会报错，因为having 的分组条件是基于查询的列来进行筛选的，select 查询的列中并没有 order_date 这一列，所以就不能在 having 中判断该条件。

— 正确写法

select CHANNEL_CODE,count(1) from user_order uo where ORDER_DATE =’2018-01-01′

group by channel

5、分组查询

— 分页查询

— 语法1： select 列1,列2,.. from 表名 limit m m表示查询的条数(默认从第一条开始查询)

— 语法2： select 列1,列2,.. from 表名 limit n,m n表示起始位置(默认从0开始),m表示查询的条数

— 总结：

当前页currentPage、每一页条数pageSize — 分页公式 (currentPage-1)*pageSize,pageSize

6、基本查询总结：

/*

SQL的 编写顺序 ：select from where group by having order by limit

SQL的 执行顺序 ：①from ②where ③group by ④having ⑤select ⑥order by ⑦limit

*/

7、子查询

— 子查询：在一个查询语句中嵌套另个查询语句，一般都在 from 或者 where 之后使用。

/*

子查询用在where从句后面：

子查询的结果为：

单行单列：可以直接比较

多行单列: in >all大于最大 any 大于最小

子查询用在from从句后面： 多行多列：注意：必须要取别名

*/

8、连接查询

连接查询的分类：

内连接 特点：左表中的数据必须要与右表的中的数据关联，才会被查询出来。

语法1：select 列1,列2,… from 左表,右表 where 左表.关联字段 = 右表.关联字段;

语法2：select 列1,列2,… from 左表 [inner] join 右表 on 左表.关联字段 = 右表.关联字段;

外连接

左外连接

特点：左表中的数据如果没有与右表关联，那么也会被查询出来，右表中的数

据用null表示

语法：select 列1,列2,… from 左表 left join 右表 on 左表.关联字段 = 右表.关联字段; 右外连接

特点：右表中的数据如果没有与左表关联，那么也会被查询出来，左表中的数据用

null表示

语法：select 列1,列2,… from 左表 right join 右表 on 左表.关联字段 = 右表.关联字

段;

全连接

合并查询

UNION：合并两张表的数据，并且会去重

UNION ALL：合并两张表的数据，不会去重

注意：合并两个查询结果，要保证两个查询的列数相同，数据类型要兼容

三、MySQL中的常用函数

1、时间函数

— 获取系统当前的日期时间

select sysdate();

— 获取系统当前的日期时间

select now();

— 获取系统当前的日期 select curdate();

— 获取系统当前的时间 select curtime();

— 获取时间的年份 select year(‘2021-02-22’);

— 计算两个时间的差值 select datediff(sysdate(),’2021-10-20′);

— 在指定的日期上添加指定的天数 select adddate(sysdate(),25);

2、字符串函数

— 拼接字符串

select concat(‘hello’,’world’,’!’);

— 查询所有员工的姓名

select CONCAT(last_name,” “,first_name) name from t_employees;

— 将’hello,java!!’ 改为 ‘hello,mysql!!’

select insert(‘hello,java!!’,7,4,’mysql’);

— 将字符串变成小写形式

select lower(‘HELLO’);

— 将字符串变成大写形式

select upper(‘hello’);

— 截取字符串

select substr(‘hello,java!!!’,7,4);

— 判断字符串中是否有该子串

select instr(‘hello,java!!!’,’java’);

3、聚合函数

— sum函数

— 查询公司一个月要支付的员工费用(工资+绩效)

select sum(salary + salary * ifnull(COMMISSION_PCT,0)) from t_employees;

— avg函数

— 查询公司的员工平均工资 select avg(salary) from t_employees;

— max函数

— 查询公司的最高工资 select max(salary) from t_employees;

— min函数

— 查询公司最低的工资 select min(salary) from t_employees;

— count函数 表示所有列 count函数只会统计指定的列不为null的列

— 查询公司的总人数

select count(1) from t_employees; select count(1) from t_employees;

— 注意：在使用 min() 和 max() 查询的时候，不要再查询其他字段的数据，因为这样查询没有意义，它会返回这一列数据的第一条数据

— select min(salary),last_name from t_employees; — 不正确写法

4、运算函数

— 将运算结果已百分比形式展示，round 保留小数位

select concat(ROUND(IFNULL(a.price,0) / (IFNULL(a.total_price,0))*100,2),’%’) priceRate

from user_order a;

— ifnull() 和 is null 的区别

ifnull() 是一个函数，用来判断空值然后给一个默认值，一般用于 select 查询字段。

is null 是用来判断该值是否为空，一般用于 where 判断条件。

四、表的操作指令

— 查询当前数据所有表

— show tables;

— 查看表结构

— desc 表名

desc emp;

— 创建表

/*

create table 表名( 字段名1 字段类型 [约束], 字段名2 字段类型 [约束], 字段名3 字段类型 [约束], … );

*/

create table student(

ID int primary key auto_increament, — 主键并且自增 => 主键：非空+唯一

NAME varchar(25) default ‘cxk’, — 设置默认值

AGE int not null, — 不能为空

ADDR varchar(25) unique — 唯一 可以为空

)

insert into user (ID,AGE,ADDR) values (1,20,null)

— 删除表

— 语法： drop table 表名;

— 语法： drop table if exists 表名;

drop table if exists student;

— 修改表

— 修改表名

— 语法：alter table 表名 rename 新的表名

alter table student rename user;

— 添加列

— 语法：alter table 表名 add 字段名 数据类型

alter table student add birthday datetime;

— 修改列数据类型

— 语法: alter table 表名 modify 字段名 数据类型

alter table student modify birthday date;

— 删除列

— 语法：alter table 表名 drop 字段名

alter table student drop age;

— 修改列名

— 语法： alter table 表名 change 旧字段名 新字段名 数据类型

alter table student change name sname varchar(50);

五、权限管理的指令

— 创建用户

— 语法：create user 用户名 identified by 用户密码

create user ‘cxk’ identified by ‘123456’;

— 用户授权

— 语法：grant 权限名称(select、update、insert、alter、create..) on 数据库.数据表 to 用户

grant all on . to ‘cxk’; grant select on java11.emp to ‘cxk’;

— 回收权限

— 语法：revoke 权限名称(select、update、insert、alter、create..) on 数据库.数据表 from 用户 revoke select on java11.emp from ‘cxk’;

— 删除用户

— 语法: drop user 用户名

drop user ‘cxk’;

六、SQL 优化中使用的指令

（1）索引的使用

1、创建索引

— 主键索引：设置为主键默认创建

— 普通索引（单字段）

— create index 索引名称 on 表名(字段名)

— 普通索引（多字段）

— create index 索引名称 on 表名(字段1,字段2)

— 唯一索引

— create unique index 索引名称 on 表名(字段名)

— 全文索引

— create fulltext index 索引名称 on 表名(字段名)

— 示例：为 if_member 列创建索引

create index member on user_order(if_member)

2、查看索引

show create table table_name; — 展示表的创建信息

show indexes from table_name;

show keys from table_name;

3、测试索引

explain select * from 表名 where 条件;

4、删除索引

drop index index_name on 表名;

（2）存储引擎的相关指令

1、查看表引擎

— 查看表的创建信息

show create table tablename；

— 查看该数据库下每张表的引擎

SHOW TABLE STATUS FROM db_name;

— 查看该数据库下指定数据表的引擎

SHOW TABLE STATUS FROM db_name WHERE NAME=’table_name’;

2、修改表引擎

alter table table_name engine=innodb;

alter table table_name engine=myisam;