MySQL常用SQL

目录

库操作 

查询数据库

创建数据库

 删除数据库

 选择数据库

表操作

查看表

创建表

 查看表结构

 查看建表sql

删除表 （整表删除）

 CRUD操作

insert增加 

update修改

delete删除

Drop、Delete、Truncate的共同点和区别：

select查询

去重distinct 

空值查询

union合并查询

带in子查询

limit分页查询

排序order by

分组group by

聚合函数

查看SQL语句的执行计划

实战例子：

 连接查询（多表联合查询）

 内连接查询 ：

外连接查询

左连接查询

右连接查询

---

SQL主要可以划分为以下 3 个类别：

         DDL（Data Definition Languages）语句 数据定义语言，这些语句定义了不同的数据库、表、列、索引等数据库对象的定义。常用的语句关 键字主要包括 create、drop、alter等。

        DML（Data Manipulation Language）语句 数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字 主要包括 insert、delete、update 和select 等。

        DCL（Data Control Language）语句 数据控制语句，用于控制不同的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户 的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

        TCL（Transaction Control Language） 语句管理数据库中的事务，

• COMMIT：用于提交事务。
• ROLLBACK：用于回滚事务。
• SAVEPOINT：用于设置保存点。

库操作 

查询数据库

show databases;

创建数据库

create database ChatDB;

 删除数据库

drop database ChatDB;

 选择数据库

use ChatDB;

表操作

查看表

show tables;

创建表

create table user(id int unsigned primary key not null auto_increment,

                                name varchar(50) not null,

                                age tinyint not null,

                                sex enum('M','W') not null
) engine=INNODB default charset=utf8;

文件存放地址： 

 查看表结构

desc user;

 查看建表sql

show create table user\G （\G 结尾是按分行形式查看  ；结尾是按表格显示）

可以把这个表创建的时候的详细的circle语句给我们写出来 ，可以方便的看这张表有没有创建过索引

删除表 （整表删除）

        注意轻易不要执行该命令

 drop table user;

 CRUD操作

insert增加 

insert into user(nickname, name, age, sex) values('fixbug', 'zhang san', 22, 'M');

insert into user(nickname, name, age, sex) values('666', 'li si', 21, 'W'), ('888', 'gao yang', 20, 'M');

insert into user(nickname, name, age, sex) values('fixbug', 'zhang san', 22, 'M')，('fixbug', 'zhang san', 22, 'M')，('fixbug', 'zhang san', 22, 'M')，('fixbug', 'zhang san', 22, 'M')，('fixbug', 'zhang san', 22, 'M');

每行sql发送过去都要经过一下三行：

1. client和server要进行tcp三次握手

2. client发送sql到server上接收并处理，返回处理结果

3. server和client断开连接，tcp四次挥手

多行一起插入，优势在于减少client与server连接的过程，性能提升，省略了TCP网络交互

update修改

update user set age=23 where name='zhang san';
update user set age=age+1 where id=3;

delete删除

delete from user where age=23;
delete from user where age between 20 and 22;
delete from user;

Drop、Delete、Truncate的共同点和区别：

 Drop、Delete、Truncate都表示删除，但是三者有一些差别：

        DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作，DELETE操作不会减少表或索引所占用的空间；

        TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的，表和索引所占用的空间会恢复到初始大小

       TRUNCATE 和 DROP是DDL，操作立即生效

        在不再需要一张表的时候，用Drop；在想删除部分数据行时候，用Delete；在保留表而删除所有数据的时候用Truncate。

select查询

select * from user;
select id,nickname,name,age,sex from user;
select id,name from user;
select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25;
select id,nickname,name,age,sex from user where sex='M' and age between 20 and
25;
select id,nickname,name,age,sex from user where sex='W' or age>=22;

去重distinct 

select distinct name from user;

空值查询

select * from user where name is null;

union合并查询

默认去重，不用修饰distinct

SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY
country;

带in子查询

select * from user where id in(10, 20, 30, 40, 50)
select * from user where id not in(10, 20, 30, 40, 50)
select * from user where id in(select stu_id from grade where average>=60.0)

limit分页查询

加了一些范围 限制  （没有索引的时候且需要的数据行数知道）

select id,nickname,name,age,sex from user limit 10;
select id,nickname,name,age,sex from user limit 2000,10; （偏移，行数）

 limit对于性能优化也有好处，比如说limit 1，当在表中查到我们想要第一个的结果就停止了

实际过程中：

 pageno：页数 ，通过设置偏移量 ，产看当前页有我们的数据 （效率低，偏移消耗效率）

        使用where先把上一页的过滤掉。

         

limit分页优势： 

        1. 减少数据传输量：使用limit分页查询可以控制每次查询返回的数据行数，避免一次性获取全部数据，减少数据传输量。

        2. 提升查询性能，一次性查询全部数据可能会导致查询速度变慢，limit可以将查询结果分批返回，减少对数据库的负载，提高查询性能。

        3. 支持快速定位：可以根据当前页面显示的行数快速定位到需要显示的数据段。 

排序order by

select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25
order by age asc; 升序
select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25
order by age desc; 降序

分组group by

select sex from user group by sex;
select count(id),sex from user group by sex;
select count(id),age from user group by age having age>20; 分组后剔除一些元素
select age,sum(age) sum from user group by age;

将相同类型的分组，再通过Mysql的聚合函数

聚合函数

count、sum、avg、max、min

SQL查询的逻辑执行顺序

from - where - group by - having - select - distinct - order by - limit/offset

查看SQL语句的执行计划

explain + sql语句

实战例子：

 1. 统计

select count（serno）， sum（amount） from bank_bill;

 2.

select brno,date,sum(amount) as money from bank_bill group by brno,date order by brno,money desc;

 连接查询（多表联合查询）

 

 

 定义三张表：

        第一张表 学生信息 ； 第二张表 课程信息 ； 第三张表 考试分数

        第三张表通过 添加关联父表的主键 联系起来

 

 内连接查询 ：

SELECT a.属性名1,a.属性名2,...,b,属性名1,b.属性名2... FROM table_name1 a inner join table_name2 b on a.id = b.id where a.属性名 满足某些条件;

 预置条件：uid：1    cid ：2

select score from exame where uid=1 and cid=2；

（可以直接通过加a.的方式区分）
select a.uid,a.name,a.age,a.sex from student a where a.uid=1；

select c.score from exame c where c.uid=1 and c.cid=2；

// on a.uid=c.uid 区分大表 和 小表，按照数据量来区分，小表永远是整表扫描，然后去大表搜索  索引加在大表上

// 从student小表中取出所有的a.uid，然后拿着这些uid去exame大表中搜索

// 对于inner join内连接，过滤条件写在where的后面和on连接条件里面，效果是一样的

select a.uid,a.name,a.age,a.sex,c.score from student a inner join exame c on a.uid=c.uid where c.uid=1 and c.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.uid=1 and c.cid=2;

select a.uid,a.name,a.age,a.sex,b.cid,b.cname,b.credit,c.score
from exame c
inner join student a on c.uid=a.uid
inner join course b on c.cid=b.cid
where c.cid=2 and c.score>=90.0;


select b.cid,b.cname,b.credit,count(*)
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
having c.cid=2;


select b.cid,b.cname,b.credit,count(*) cnt
from exame c
inner join course b on c.cid=b.cid
where c.score>=90.0
group by c.cid
order by cnt;

外连接查询

外连接的条件过滤应该写在on的后面

左连接查询

SELECT a.属性名列表, b.属性名列表
 FROM table_name1 a LEFT [OUTER] JOIN table_name2 b 
 on a.id = b.id;

// 把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL
select a.* from User a left outer join Orderlist b 
on a.uid=b.uid where a.orderid is null;

右连接查询

SELECT a.属性名列表, b.属性名列表 
FROM table_name1 a RIGHT [OUTER] JOIN table_name2 b 
on a.id = b.id

// 把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示NULL
select a.* from User a right outer join Orderlist b 
on a.uid=b.uid where b.orderid is null;