Datawhale MySQL 训练营 Task2 查询语句

MySQL 管理

MySQL版本 8.0.15

MySQL 用户管理 参考

# root 用户登录
mysql -u root -p;

# 添加用户
use mysql;
create user test@localhost identified by 'test123';

# 授予用户全部权限
 GRANT ALL on *.* to 'test'@'localhost' WITH GRANT OPTION;

数据库管理

  • 创建数据库
CREATE DATABASE db_example;
  • 切换数据库
use db_example;
  • 删除数据库
drop database <数据库名>;

SQ查询语句

注:SQL 不区分大小写,建议关键词大写增加可读性;SQL 是 0-base 的语句

1. 导入示例数据库,教程 MySQL导入示例数据库

参考 https://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html

2. 查询语句 SELECT

  • 从表 tablex 中查询某一列或者某几列:
SELECT
    column0,column1,...clomunn
FROM
    table_x;

注: SELECT * FROM tabel 可以查询所有列, * 为通配符

  • 去重语句 DISTINCT
SELECT DISTINCT vend_id
FROM Products;

DISTINCT 列名 只返回 vend_id 这一列不同的值

  • 前N个语句, LIMIT
    在 MySQL 中返回前 N 行
SELECT columns
FROM table
LIMIT N;
  • 从第M行开始的N行数据, LIMIT N offset M

    MySQL 和 MariaDB 支持简化版本的 LIMIT M,N 语句,逗号前面对应 OFFSET, 逗号后面对应 LIMIT

SELECT columns
FROM table
LIMIT N OFFSET M;
  • CASE...END判断语句

3. 筛选语句 WHERE ,过滤

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
  • 语句解释: 筛选出 prod_price = 3.49 的prod_name
  • 运算符
    Datawhale MySQL 训练营 Task2 查询语句
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

BETWEEN 需要AND 连接上下限;

空值检查: IS NULL 语句

  • 操作符
    逻辑操作符: AND 连接两个条件 / OR连接两个条件; AND OR 一起用的时候 AND优先级更高,应该用()对操作符分组,()优先级更高

IN 操作符: 指定条件范围,() 内的被筛选出来,与 OR 有同样的作用

NOT 操作符: 否定后面的条件

  • 通配符 用来匹配值的一部分的特殊字符; 可以和字面值组合成搜索模式

LIKE 谓词

% 通配符:表示任何字符出现任何次数
_ 通配符: 匹配单个字符
[] 通配符: 指定字符集匹配一个字符

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )

4. 分组语句 GROUP BY

  • GROUP BY 语句根据一个或多个列对结果集进行分组
  • 聚集函数:汇总表中的数据的函数:平均、最大、最小、计数、求和

Datawhale MySQL 训练营 Task2 查询语句

示例: 统计 prod_price 的平均值保存为 avg_price

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
  • HAVING子句,类似 WHERE,但是WHERE过滤行但是 HAVING 过滤分组,HAVING 支持所有的WHERE的操作符,还支持聚集操作

5. 排序语句 ORDER BY

  • 语句解释: 根据 BY 对取出的列进行排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

ORDER BY 后面可以是非检索的列,可以是其他列; ORDER BY 应该保证是 SELECT 语句中的最后一条子句(也必须放在 ); 使用选择的列(这里的 prod_id, prod_price, prod_name)排序可以用它们的顺序进行排列如 ORDER BY 2,3

  • 正序、逆序
    默认正序,可以使用ASC关键字,一般用不上
    逆序使用 DESC 关键字;直接位于 DESC 前面的那一列将会按照逆序列排列
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

6. 函数

时间函数: 跟具体的DBMS 有关
SQL 数值函数
![image](http://media.alearner.cn/PictureRepo/Note/blog20190227SQLFuncNum.PNG)
SQL字符串函数
![image](http://media.alearner.cn/PictureRepo/Note/blog20190227SQLFuncString.PNG)
另外 MySQL 还提供了丰富的函数,[查表](http://www.runoob.com/mysql/mysql-functions.html)

作业

  1. 编写一个 SQL 查询,查找 email 表中所有重复的电子邮箱。
    根据以上输入,你的查询应返回以下结果:
-- 创建表
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
)

-- 插入数据
INSERT INTO email VALUES('1','a@b.com');
INSERT INTO email VALUES('2','c@d.com');
INSERT INTO email VALUES('3','a@b.com');

-- 查询
SELECT
    Email
FROM
  email
GROUP BY
  Email
HAVING COUNT(*) > 1

结果:
Datawhale MySQL 训练营 Task2 查询语句

  1. 项目二:查找大国(难度:简单)
    创建如下 World 表
+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过300万平方公里,或者(人口超过2500万并且gdp超过2000万),那么这个国家就是大国家。
编写一个SQL查询,输出表中所有大国家的名称、人口和面积。
例如,根据上表,我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+
CREATE TABLE World (
name VARCHAR(50) NOT NULL,
continent VARCHAR(50) NOT NULL,
area INT NOT NULL,
population INT NOT NULL,
gdp INT NOT NULL
);

-- 插入数据
INSERT INTO World VALUES( 'Afghanistan', 'Asia',652230,25500100,20343000);
INSERT INTO World VALUES( 'Albania', 'Europe' ,28748,2831741,12960000);
INSERT INTO World VALUES( 'Algeria', 'Africa' ,2381741,37100000,188681000);
INSERT INTO World VALUES( 'Andorra' , 'Europe' ,468,78115,3712000);
INSERT INTO World VALUES( 'Angola' , 'Africa' ,1246700,20609294,100990000);

-- 查询

SELECT
    name,population,area
FROM
    world
WHERE area>3000000 OR (population > 2500000 AND gdp > 20000000)

结果:

Datawhale MySQL 训练营 Task2 查询语句

总结

今天好好学习了一下 SQL 的查询、筛选、分组、排序和函数, 总的来说比较简单。
文中主要图表和代码参考 《SQL 必知必会》,人民邮电出版社出版,[美]Ben Forta 著,钟鸣,刘晓霞译。

上一篇:[转]自定义注释@interface的用法


下一篇:SQL注入之MySQL常用的查询语句