MySQL核心SQL

_小菜鸟
148 阅读4分钟

一、结构化查询语句SQL

SQL是结构化查询语言（Structure Query Language），是关系型数据库的通用语言。 SQL主要分以下三个类别：

  • DDL（Data Definition Language）语句 数据定义语言，这些语句定义了不同的数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等
  • DML（Data Manipulation Language）语句 数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字包括insert、delete、update和select等
  • DCL（Data Control Language）语句 数据控制语句，用于控制不同的许可和访问级别的语句，这些语句定义了数据库、表、字段用户的访问权限和安全级别，主要的语句关键字包括grant、revoke等

二、库操作

查询数据库

show databases;

创建数据库

create database chat;

删除数据库

drop database chat;

选择数据库

use chat;

三、表操作

因为业务层操作内存，MySQL操作磁盘，数据库永远是最先达到性能瓶颈，我们不能把过多的逻辑操作放到数据库上，逻辑操作应该在业务层做。

MySQL只做最核心的CRUD，触发器、存储函数、存储过程等都不会在MySQL上设置，统一迁移到业务层中的服务层做。

1. 创建表

create table user(
    id int unsigned primary key not null auto_increment,
    name varchar(50) unique not null,
    age tinyint not null,
    sex enum('M','W')not null
);

2. 查看表结构

desc user;

image.png

3. 删除表

drop table user;

4. 打印表创建的SQL

show create table user\G

image.png

四、CRUD操作

1. insert增加

insert into user(name,age,sex) values('zhangsan',20,'M');
insert into user(name,age,sex) values('wanger',10,'W');

insert into user(name,age,sex) values('zhangsan',10,'M'),('wanger',20,'W');

2. delete删除

delete from user;

3. update更新

update user set age=age+1 where name='zhangsan';

面试提问：上述两种insert的区别：

批量导入数据的时候，常用的是一条SQL语句插入多条数据，而不是一条SQL语句插入一条数据。因为每条SQL语句都需要C/S之间建立连接，最好是一条SQL插入更多的数据

image.png

4. select查询

 select * from user;
 select name,age,sex from user where age>=21 and sex='M';
 select name,age,sex from user where age between 20 and 22;(包括20和22)
 select name,age,sex from user where age>20 and age<22;
 select name,age,sex from user where name like "zhang%";
 select name,age,sex from user where name like "zhang_";
 select name,age,sex from user where name is null;
 select name,age,sex from user where name is not null;
 select name,age,sex from user where age in (20,21);

查询截图 image.png

image.png distinct去重

select distinct age from user;

image.png union合并查询

and用到索引，or被MySQL优化为union也用到了索引

select name,age,sex from user where age>=21 union [all|distinct] select name,age,sex from user where sex='M';

//union默认去重，不用distinct修饰，all表示显示所有重复值

image.png

5. 分页查询

在没有索引的时候添加limit可以提高搜索的效率

select * from user limit N;
select * from user limit M,N;
select * from user limit N offset M;

image.png

image.png

image.png 实际出现的效率问题

image.png 我们若使用如下SQL查询，就会有前几页查询快，后几页查询慢的问题
效率主要低在(page_num-1)*n偏移量，偏移需要花费时间

select * from t_user limit (page_num-1)*n, n;

我们可以使用id索引直接偏移

select * from t_user where id>(page_num-1)*n limit n;

image.png explain查看SQL语句的执行计划。

image.png

image.png

6.带in的子查询

select * from user where age in (11, 22, 33);
select * from user where age not in (11, 22, 33);
select * from user where age between 11 and 22; -- [11, 22]
select name from stu where id in (select id from stu where age > 10);

四、创建存储过程procedure

快速向表里添加数据

create table t_user(
	id bigint primary key not null auto_increment,
	domain char(20) not null,
	passwd char(20) not null
);

delimiter $ -- 修改MySQL的分隔符，避免和创建语句的分隔符冲突

create procedure add_t_user(in n int)
begin
declare i int;
set i = 0;

while i < n do
insert into t_user(domain, passwd) values(concat(i+1,"@BugMaker.com"), i+1);
set i = i + 1;
end while;
end$

delimiter ;

call add_t_user(100000);

五、MySQL常用函数

image.png

六、order by排序

order by使用

select* from user order by name asc;//按升序排序
select* from user order by name desc;//按降序排序
select* from user order by name;// 默认升序排序
select* from user order by name,age;//按name进行排序，name相同再按age进行排序

image.png

image.png

select* from user where sex='M' order by name desc;

image.png

image.png 因为order by涉及磁盘IO效率低，所以一定要优化 order by的性能不仅仅和order by的待排序字段有没有建索引有关，而且还和select选择的列有关，这就和后边要学习到的聚集索引和非聚集索引搜索过程有关。

image.png

七、group by分组

一般和聚合函数一起使用

select age,count(age) from user group by age;
select age,count(age) as sum from user group by age;
select age,count(age) sum from user group by age;
select age,count(age) sum from user group by age having age>20;//条件查询
select age,count(age) sum from user where age>20 group by age;
select age, sex, count(*) from user group by age, sex order by age;//统计age和sex都相同的有几个人

image.png

image.png

image.png order by和group by在考虑性能上都是和索引紧紧相关的

image.png

八、笔试问题实践

image.png

  1. 统计表中缴费的总笔数和总金额
select count(serno), sum(amount) from bank_bill;
  1. 给出一个sql,按网点和日期统计每个网点每天的营业额，并按照营业额进行倒序排序
select brno, date, sum(amount) as money from bank_bill group by brno, date order by brno, money desc;
avatar
文章
阅读
粉丝