目录
1 导入示例数据库(两种方式)
- 使用命令行导入: 教程 MySQL导入示例数据库 - MySQL教程™
- 使用Navicat for MySQL可视化操作导入:
- 在数据库连接的基础上新建数据库,自定义数据库名,字符集和排序规则也自选,最好是和需要导入的数据库格式一致;
- 新建完成数据库后右键点击选择运行SQL文件,出现如下图所示窗口,填写你需要导入的数据库文件的地址;
- 填写无误后点击开始即开始导入,导入完成后显示如下,点击关闭即可。
- 测试导入结果,查询实例示例如下,结果正确显示,说明导入成功了。
2 SQL是什么,MySQL是什么?
SQL: SQL(发音为字母 S-Q-L或 sequel)是 Structured Query Language(结构化查询语言)的缩写。SQL是一种专门用来与数据库沟通的语言。
- SQL有如下的优点:
- SQL不是某个特定数据库供应商专有的语言。几乎所有重要的 DBMS 都支持 SQL,所以学习此语言使你几乎能与所有数据库打交道。
- SQL简单易学。它的语句全都是由有很强描述性的英语单词组成,而且这些单词的数目不多。
- SQL虽然看上去很简单,但实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。
MySQL: MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。
本质区别:SQL是一种语言,而MySQL是一种数据库
3 查询语句(select from)
语句解释:从一个或多个表中检索信息。
去重语句:使用 DISTINCT关键字,顾名思义,它指示数据库只返回不同的值。
注意:不能部分使用 DISTINCT,DISTINCT关键字作用于所有的列,不仅仅是跟在其后的那一列。例如,你指定SELECT DISTINCT vend_id, prod_price,除非指定的两列完全相同,否则所有的行都会被检索出来。
前N个语句:使用LIMIT关键字来限制最多返回多少行,eg:SELECT prod_name
FROM Products LIMIT 0 OFFSET N;指示 MySQL等 DBMS返回从第 1行起的 N行数据。第一个数字是指从哪儿开始,第二个数字是检索的行数。 注意:第 0 行:第一个被检索的行是第 0行,而不是第 1行。因此,LIMIT 0 OFFSET 1会检索第 2行,而不是第 1行。
CASE…END判断语句
4 筛选语句(where)
- 语句解释:数据库表一般包含大量的数据,很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filter condition)。在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤。
- 操作符:SQL支持表 4-1列出的所有条件操作符。
- 通配符:
- 1.百分号(%):在搜索串中,%表示任何字符出现任意次数。特别注意,除了能匹配一个或多个字符外,%还能匹配 0个字符。%代表搜索模式中给定位置的 0个、1个或多个字符。但是WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行
- 2.下划线(_):下划线的用途与%一样,但它只匹配单个字符,而不是多个字符。
- 方括号([ ]):用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符。
使用通配符技巧:
不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
5 分组语句(group by)
- 聚集函数:
- 语句解释:使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算。
- 使用GROUP BY子句的一些重要规定:
GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
如果在GROUP BY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUPBY子句中指定相同的表达式。不能使用别名。
大多数 SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。
除聚集计算语句外,SELECT语句中的每一列都必须在GROUPBY子句中给出。
如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
- HAVING子句:HAVING非常类似于WHERE。唯一的差别是,WHERE过滤行,而HAVING过滤分组。另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别,WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING子句中基于这些值过滤掉的分组。
6 排序语句(order by)
- 语句解释:ORDER BY子句取一个或多个列的名字,据此对输出进行排序。
- 正序、逆序:默认的排序顺序是升序,若想使用ORDER BY子句进行降序排序,必须使用关键字DESC。注:DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序,必须对每一列指定DESC关键字。
7 函数
与几乎所有 DBMS都等同地支持 SQL语句(如 SELECT)不同,每一个DBMS都有特定的函数。
- 时间函数:取当前时间:Access使用 NOW(); DB2和 PostgreSQL使用 CURRENT_DATE;MariaDB和MySQL使用CURDATE();Oracle使用SYSDATE;SQLServer使用GETDATE();SQLite使用DATE()
- 数值函数:数据类型转换:Access和Oracle使用多个函数,每种类型的转换有一个函数;DB2和PostgreSQL使用CAST();MariaDB、MySQL和SQL Server使用CONVERT()
- 字符串函数:提取字符串的组成部分:Access使用 MID(); DB2、 Oracle、 PostgreSQL和 SQLite使用SUBSTR();MySQL和SQL Server使用SUBSTRING()
8 SQL注释
注释的作用:
- 随着 SQL语句变长,复杂性增加,添加一些描述性的注释,便于今后参考,或者供项目后续参与人员参考。
- 适用于 SQL文件开始处的内容,它可能包含程序员的联系方式、程序描述以及一些说明。
- 暂时停止要执行的 SQL代码。如果你碰到一个长 SQL语句,而只想测试它的一部分,那么应该注释掉一些代码,以便 DBMS将其视为注释而加以忽略。
注释的方法:
- 行内注释:
SELECT prod_name - - 这是一条注释
FROM Products;
- -之后的文本就是注释
# 这是一条注释
SELECT prod_name
FROM Products;
在一行的开始处使用#,这一整行都将作为注释。
多行注释:
/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name
FROM Products;
注释从/开始,到/结束,/和/之间的任何内容都是注释。这种方式常用于给代码加注释,就如这个例子演示的,这里定义了两个 SELECT语句,但是第一个不会执行,因为它已经被注释掉了。
9 SQL代码规范
命名建议:
- 使用统一的、描述性强的字段命名规则
- 保证字段名是独一无二且不是保留字的,不要使用连续的下划线,不用下划线结尾
- 最好以字母开头
格式建议:
- 最好使用标准SQL函数而不是特定供应商Oracle、Mysql等的函数以提高可移植性。
- 大小写的运用:系统关键字大写,字段表名小写
- 灵活使用空格和缩进来增强可读性——两大法宝空白隔道与垂直间距
select name,id,sex
from (select *
from school_score
where class_cd=110)
where sex = ‘man’
and exam_dt = ‘2016-06-01’;
- -空白隔道+垂直间距+大小写+缩进(感受下是不是下面的看得更清晰有序呢)
SELECT name, id, sex
FROM (SELECT *
FROM school_score
WHERE class_cd = 110)
WHERE sex = ‘man’
AND exam_dt = ‘2016-06-01’;
语法建议:
- 尽量使用BETWEEN而不是多个AND
- 同样,使用 IN 而不是多个OR
- 利用CASE语句嵌套处理更复杂的逻辑结构
- 避免UNION语句与临时表
Tools:
- SQLinForm(SQL Formatter for Professionals - Also Free Online SQL Beautifiers & Plugins available)插件
- 对于Sublime重度爱好者可以安装zsong/SqlBeautifier插件,CTRL+K 加 CTRT+F 实现格式转换,相比SQLinForm,自定义功能略显单薄,但也足够可用。
- 喜欢离线操作的老铁,可以使用Notepad++的用户可以安装插件或者下载官方编程程序,勾选LIVE FORMATTING,实现实时自动整理格式的功能。
10 实践
项目一:查找重复的电子邮箱(难度:简单)
创建 email表,并插入如下三行数据
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
编写一个 SQL 查询,查找 email 表中所有重复的电子邮箱。
根据以上输入,你的查询应返回以下结果:
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明:所有电子邮箱都是小写字母。
建立表格:
-- 创建表
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');
查询表格:
项目二:查找大国(难度:简单)
创建如下 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 ,gdp
FROM World
WHERE area > 3000000 OR (population > 25000000 AND gdp > 20000000);