mysql常用语句2

explain 执行分析

explain select city ,count(*) as num from staff group by city;

• Using temporary表示在执行分组的时候使用了临时表
• Using filesort表示使用了排序

·查 

 1.GROUP BY 单级分组 逐级分组

group by 就是分组统计的意思，一般情况都是配合聚合函数 如（count(),sum(),avg(),max(),min())一起使用。

• count() 数量
• sum() 总和
• avg() 平均
• max() 最大值
• min() 最小值

如果没有配合聚合函数使用，返回的是分组的第一行数据。

2.having

where & having 区别

• 如果条件是从数据库获取数据时的筛选条件，应该用where语句；如果是分组聚合之后的条件，应该用having语句。
• having一般都是配合group by 和聚合函数一起出现如(count(),sum(),avg(),max(),min())，where条件子句中不能使用聚集函数，而having子句就可以。
• having只能用在group by之后，where执行在group by之前

select s_id,avg(score) from sc group by s_id having avg(score)>=60;

3.LIMIT  限制SELECT语句返回的行数

SELECT 
    column_list
FROM
    table1
ORDER BY column_list
LIMIT row_count OFFSET offset;

• row_count确定将返回的行数。
• OFFSET子句在开始返回行之前跳过偏移行。 OFFSET子句是可选的。 如果同时使用LIMIT和OFFSET子句，OFFSET会在LIMIT约束行数之前先跳过偏移行。

eg:

SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 5 OFFSET 3;
// 要跳过3行并获取接下来的5行

//也可以写成
SELECT 
    employee_id, first_name, last_name
FROM
    employees
ORDER BY first_name
LIMIT 3 , 5;

4.子查询

• 单列/单行子查询-返回单列/单行数据

主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较

// 例如查出结果后想要将课程编号换成课程名
select a.StudentNo,a.SubjectNo,a.StudentResult,
(select b.SubjectName from `subject` b 
where b.SubjectNo=a.SubjectNo) as 课程名称
from result a;

• 多行子查询-返回列表

主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较

IN ：匹配子查询结果中的任一个值即可（"IN"操作符，能够测试某个值是否在一个列表中）

ALL ： 必须要符合子查询的所有值才可

ANY：要符合子查询结果的任何一个值即可

ALL和ANY操作符不能单独使用，而只能与单行比较符（=、>、< 、>= 、<= 、<>）结合使用

#查询课程为《高等数学-2 高等数学-1》且分数不小于80分的学生的学号和姓名
select * from result a
 where a.StudentResult>80
and a.SubjectNo in (
select SubjectNo 
from `subject` b 
where b.SubjectName = "高等数学-2" or b.SubjectName = "高等数学-1"
);

• 表

1.查看当前数据库中的表

SHOW TABLES;

2.创建新表

CREATE TABLE <表名> (
    <列名1> <列类型1>,
    <列名2> <列类型2>,
    <列名3> <列类型3>,
    PRIMARY KEY (<列名1>),
    FOREIGN KEY (<列名2>) REFERENCES <表名2>(<列名2>)
);

3.概述表中的列

查看表中的列的基本信息：

DESCRIBE <表名>;

4.插入

查看表中的列的基本信息：

INSERT INTO <表名> (<列名1>, <列名2>, <列名3>, …)
    VALUES (<值1>, <值2>, <值3>, …);

5. 在表中更新记录

UPDATE <表名>
    SET <列名1> = <值1>, <列名2> = <值2>, ...
    WHERE <条件>;

6. 清空表

DELETE FROM <表名>;

7. 删除表

DROP TABLE <表名>;

•  数据库

1. 查看现有数据库

SHOW DATABASES;

2. 新建数据库

CREATE DATABASE <数据库名>;

3. 选择数据库

USE <数据库名>;

4. 从.sql文件引入SQL语句

SOURCE <.sql文件路径>;

5. 删除数据库

DROP DATABASE <数据库名>;