文中加入了自己的一些理解，如有不对请评论指教O(∩_∩)O。
感谢老师的详细讲解和指导。
SQL与MySQL介绍
SQL: 结构化查询语言(Structured Query Language)简称SQL，用于存取数据以及查询、更新和管理关系数据库系统。
MySQL: MySQL是最流行的关系型数据库管理系统。
两者关系：一个是语言一个是系统，可以再MySQL软件下些SQL语句。
数据库基础
-
数据库（database）保存有组织的数据的容器，数据库与数据库软件是两回事，MySQL是数据库软件，可以在其中创建数据库。
database
如上图，data、exercise等是数据库（database），schema就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。两者关系暂时不展开，以后单独介绍。
-
表（table）某种特定类型数据的结构化清单
table
如上图，world库下有city、country、countrylanguage三个表。
-
列（column）表中的一个字段。所有表都是由一列或者多列组成的。
column
如上图，city表下有ID、Name等5个字段。
- 行（row）表中的一个记录。
city表中的每一行都是一条记录 - 主键（primary key）一列（或一组列），其值嫩够唯一区分表中的每一行。表中的每一行都应该有一个唯一标识自己的一列，表中的任何列都可以作为主键，但是必须满足以下条件：
- 任意两行不具有相同主键
-
每一行都必须有一个主键
pramary key
如上图，city表中的字段ID作为该表的主键。
SQL介绍
- SQL是结构化查询语言（Structured Query Language）的缩写，专门用来与数据库进行通信的语言
- SQL不是某个特定的数据库专用语言，几乎所有的DBMS都支持SQL
- SQL简单易学，需要学习的关键词很少（会再总结一篇SQL关键词文章）
- SQL可以进行非常复杂和高级的数据库操作
检索数据
- 使用select选取列数据，返回的结果是无序的
- SQL不区分大小写
- 结束SQL语句需要使用分号（;）
- SQL语句会忽略空格
- 使用limit 限制返回结果行数
使用某个库，用use database（库名）
use world;
查询语句，如已制定库，则可以select 字段名 from 表名
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from city;
未指定库，则可以select 字段名 from 库名.表名
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from world.city;
切换库，直接用use database
use data;
limit的使用，只显示10行，limt 10
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from city limit 10;
查看数据库的列表
show databases;
查看表的列表
show tables;
查看表字段信息
show columns from city;
如上图，int(11)、char(35)等，里面的数字表示最大显示宽度。int最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。
- 使用order by 排序数据
- desc 降序
- asc 升序
- order by 位于 from子句之后
use world;
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from city order by CountryCode desc,Population asc LIMIT 10;
默认为升序排列(asc)，多字段排序有先后，如在CountryCode 排序组内对Population 进行排序。
过滤数据
- 在select语句中，数据根据where子句指定的搜索条件进行过滤
- 多个过滤子句使用 and/or 连接
- where子句中的圆括号决定了计算次序
- in/not
and/or过滤时要注意先后顺序，必要时加括号
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from world.city where population >= 100000 and population <= 500000 and (countrycode = "NLD" or countrycode = "AFG");
或者用in/not，not in表示不在
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from world.city where population >= 100000 and population <= 500000 and countrycode in ("NLD", "AFG");
通配符
- 通配符（wildcard） 用来匹配值的一部分的特殊字符，通配符可以替代一个或多个字符。在搜索子句中使用通配符必须使用like操作符
- 百分号（%）通配符
- “abc%” 以abc开头，任意字符结尾的数据
- ‘%abc’ 以abc结尾，任意字符开头的数据
- ‘%abc%’ 任意字符开头和结尾，中间包含abc
- 下划线（_）通配符
- 只匹配单个或多个字符
‘_abc’ 以abc结尾，开头只有一个长度的任意字符 - 注意尾部空格
- 注意null
使用通配符进行匹配数据时要注意索引失效的问题，"%ab"、"%ab%"会导致索引失效的问题，因为select会全值匹配，使用左索引来避免（方法一，后续再展开）
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from city where reverse(NAME) like reverse("%ab");
数据处理函数
- SQL支持函数，但是函数却不像SQL具有很强的可移植性，因为不同厂商的DBMS会指定不同的特殊函数
- 大部分SQL包含的函数
- 用于处理文本
- 数值计算
- 处理时间
length()函数
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION, length(NAME) as len_name from city; #length()在select内
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION from city where length(NAME)=6 #length()在where内
substring()、left()、upper()、lower()函数
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION,substring(NAME,1,3) as sub_name,left(NAME,3) as left_name,upper(NAME) as upper_name,lower(NAME) as lower_name FROM city;
replace()函数
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION,replace(NAME, "Al", "lA") from city where length(NAME)=6;
日期和时间处理函数
date()函数
select date(last_update) from sakila.film where date(last_update)="2006-02-15";
时间差datediff()，比较的是天的差值，parameter1-parameter2
select datediff(now(), last_update) from sakila.film;
汇总数据
- 聚集函数（aggregate function）运行在行组上，计算和返回单个值的函数
- 常用的avg/count/max/min/sum
- distinct
- 注意，它们会忽略掉null值
select max(population), min(population), avg(population), sum(population) from city;
distinct()需在select后，distinct前面不可以有其他字段，后面可跟多个字段，查找所有字段都不相同的row
select distinct COUNTRYCODE, District from city;
在select语句中可以使用group by子句将行划分成较小的组，然后，使用聚合函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集，因为where关键字无法与聚合函数一起使用。
select COUNTRYCODE,District,avg(population) as avg_popu from city group by CountryCode, District having avg(Population)>=1000000;
子查询
- 使用 group by 进行数据分组，对每个组应用函数
- 使用having对组进行过滤
select * from (select COUNTRYCODE,District,avg(population) as avg_popu from city group by CountryCode, District) a where avg_popu>=1000000;
联结表
对多张表进行联结，使用select选取数据
JOIN的方式如下：
select a.* from
(select * from world.city) a
left join
(select * from country limit 100) b
on a.countrycode = b.code
left join
(select * from countrylanguage) c
on c.countrycode = a.countrycode;
组合查询
- 将多个查询结果通过 union 进行组合
- union 规则：
- 必须由两条或者两条以上的select语句组成，语句之间用union分隔
- union中的每个查询必须包含相同的列，表达式或聚集函数
- 列数据类型必须兼容
- union会自动对数据进行去重，如果不需要请使用 union all
- order by 只能用于最后一条select语句
使用union时，返回的字段须一致（数据类型一致，但是容易混乱）。
默认地，UNION 操作符选取不同的值（去重）。如果允许重复的值，请使用 UNION ALL。
select id,name from city where population >= 1000000
union all
select id,name from city where population >= 100000
order by id desc;
创建和操作表
- 利用 CREATE TABLE 创建表，必须给出下列信息：
- 新表的名字，在关键字CREATE TABLE 之后给出
- 表列的名字和定义，用逗号隔开
- 使用NULL值
在创建表的是否可以定义列允许或者不允许NULL值 - 主键
- 表中的一列或者多列可以成为主键，这些值或者组合必须唯一
- PRIMARY KEY（col），主键不允许有NULL值
- 指定默认值
关键字DEFAULT可以对列进行默认值设置
create table if not exists xiaoming_test2
(
id int not null,
name char(40) not null,
countrycode char(5),
population int,
gnp float,
language char(30)
);
更新/删除表
- ALTER TABLE 用来更新表结构，但是强烈建议在创建表的时候就仔细考虑表结构，不要轻易变动
- add column char(20)
- drop column
- DROP TABLE 删除表
插入数据
- insert 是用来插入行到数据库中，插入可以使用几种方式：
- 插入完成的行（不推荐）
- 插入行的一部分
- 插入多行
- 插入某些查询结果
- update用来更新表中的数据，一定要仔细，不要省略where字句
- delete用于从表中删除特定的行或者从表中删除所有行，一定要仔细，不要省略where字句
- 指导原则：
- 绝对不要轻易忽略where字句，否则会更新或删除全部的行
- 在使用where条件前先使用select进行测试，保证正确无误，因为Mysql是没有撤销功能的
