sql知识
1.关联式资料库存储形式
以表格形式存储，然后表格之间会有关联。即表格是数据存储的单元。
表格的描述：行row为一笔资料。列column为一个表格的属性。
每个表格里至少需要一个属性作为主键。
主键：唯一确定一笔资料的标识。我们不想一张表里出现两笔同样的数据。
外键：一张表和另外一张表有关系的属性。A表的数据范畴和B的数据范畴有关联时就需要外键。
这里以公司的
以员工id为主键来唯一确定一条员工的资料。员工表和部门表有所属关系。然后就在员工表后面再加一条部门id属性作为外键。
1.1外键性质
1.注意：一张表的外键必须另一张表的主键。
原因是在这个所属关系里，如果这个外键它不是主键，就意味着这个外键在另外一张表里会有两个该条属性对应的资料。那么这个外键到底对应哪条资料呢。会出现对应歧义。
2.外键对应的主键可以是自己表的主键。只要你心里清楚这个对应主键的关系所代表的含义。这里的一条员工资料外键对应的主键表示上下级关系。（我觉得应该先给资料想关系，然后再想这个外键对应的这个主键是否合法。）
1.2主键性质
如果一笔资料以一个属性作为主键不能唯一确定该条资料，那么就要新增属性为主键，直到可以唯一确定一条资料为止。主键中的值不允许修改或更新。
主键值不能重用(如果某行从表中删除，它的主键不能赋给以后的新行)。
1.3资料库的创建、删除、所有资料库的显示
资料库关键字DATABASE。
创建资料库：CREATE DATABASE `name`; 。//注：一般把自己创建的资料库用反引号引起来，避免和关键字混淆
显示所有资料库：SHOW DATABASES; 。
删除资料库：DROP DATABASE `name`; 。
资料库创建好之后，就要根据你想要存储的资料去设计表格，表格创建出来。然后我们再把资料存入表格。
资料库创建流程：创建资料库；选择使用哪个资料库；设计表格；
许多SQL开发人员喜欢对SQL关键字使用大写，而对列名和表名使用小写，这样代码更易于阅读和调
试。
1.4资料属性的数据类型
INT --整
DECIMAL(m,n) --浮点数类型，m表示该浮点数一共几位。n表示浮点数小数点后有几位
VARCHAR(n) --字符串，n为字符串长度
BLOB --（Binary Large Object）图片，视频，档案型数据
DATE --格式为 'YYYY-MM-DD'的日期。插入date类型的数据时也是以'1997-11-01'字符串行式插入的。
TIMESTAMP --格式为 'YYYY-MM-DD HH:MM:SS' 的日期和时间。常用来记录某笔资料的更改时间
2.表格操纵、表格修改
2.1表格创建
我们想设计一个学生的表长这样。
先使用USE `资料库名` ;。//定位到哪个资料库，在哪个资料库里建表
创建表使用：CREAT TABEL `表名`;。
表的创建如下格式：
CREAT TABLE `表名` (
`属性1名` 属性数据类型,
`属性2名` 属性数据类型,
`属性3名` 属性数据类型,
PRIMARY KEY(`属性名`)
);。
可同时给多个列设置主键，PRIMARY KEY(`属性名1`， `属性名2`)。
例：
creat table `student` ( `student_id` int primary key, `name` varchar(10), `major` varchar(10) );
2.2查看表格属性
DESCRIBE `表名`; 。//只显示表的属性，不是显示整个表的资料。
例：describe `student`;。
2.3删除表格
DROP TABLE `表名`; 。
2.4修改属性数据类型
ALTER TABLE `表` MODIFY `属性x` 数据类型;。
2.5修改属性名
ALTER TABLE `表` CHANGE `属性X` `新属性X` 数据类型;。
2.5表格增添属性和删除属性
增加属性列：ALTER TABLE `表名` ADD `属性名` 属性类型;。增加属性列到表的最前面：ALTER TABLE `表` ADD `属性列` 数据类型 FIRST; 。
再增加属性列时容易不写数据类型报错。
删除属性列：ALTER TABLE `表名` DROP COLUMN `属性名`;。
例如：alter table `student` add `gpa` decimal(3, 2);//(3,2)的意思是小数数据类型，3位小数，小数点后面有2位。
表格增加和删除属性是ALTER TABLE关键词开头。
3.资料操纵
3.1资料存入
用INSERT INTO `表名` VALUES (属性1值, 属性2值, 属性3值); //注意：值类型的插入顺序一定要和建表的属性值的类型对应
往表中插入一笔资料。
例：
对该表格插入一笔资料：
creat table `student` (
`student_id` int primary key,
`name` varchar(10),
`major` varchar(10)
);
INSERT INTO `student1` VALUES(1, '小白'，‘历史’); 。
不按表顺序需要自己手动对应：INSERT INTO '表名' (`属性3`, `属性2`, `属性1`) VALUES (属性3值，属性2值，属性1值); 。
查看：select * frome `student`; 。
可以看到有的属性可以为空值，在有些场景里有的属性不能为空。那么就可以进行约束。
3.2属性的限制和约束
3.2.1非空和独有
常见限制关键词：NOT NULL（非空）、UNIQUE（独有）、DEFAULT XX（默认为某值）、AUTO_INCREMENT（自增）。
在设计表格属性时，限制词写在属性类型声明后面。
例：
插入空值名字时错误。
主修属性出现重复插入。
3.2.2默认和自增
varchar类型的默认值
`属性名` 属性类型 default '字符串';。DEFAULT 关键词后面的字符串就是默认填充的字符串。
例如：
creat table `student` (
`student_id` int primary key,
`name` varchar(20),
`major` varchar(20) default ‘历史’
);。
insert into `student` (`student_id`, `name`) values (1, ‘张三’);。
1.如果表格中有属性被设为默认值，插入资料时，插入语句必须先列出除默认属性以外的属性进行插入。 不能省略，否则无法匹配插入值和属性。
正确插入语句书写
3.2.3 AUTO_INCREMENT
适用于主键自增的限制。在自定义填充属性时，主键加了此限制后，我们可不填充主键。主键值会自动递增填充。`student_id` in primary key auto_increment;。自增关键词依然是在属性类型后面修饰的。
3.3资料修改和删除
为了练习更多sql语句，我们会使用SET SQL_SAFE_UPDATES = 0; 来关闭mysql的安全模式。等于1则是打开。安全模式是指UPDATE语句和DELETE语句未加WHERE条件会造成数据全部修改的情况。如果安全模式打开，资料修改或删除不加where则会报错，提示需要加where。
资料修改：
UPDATE `表名`
SET `属性名` = 目标值
WHERE `属性名` = 查找值;
（WHERE `属性1` = 查找值 OR `属性2` = 查找值）
先确定欲修改的值属于哪个属性。然后用where关键字输查找值就行。
order by总位于where之后。
这里把小白的主修英语改为了生物。
。where查找条件支持多个条件的‘且’、‘或’连接。比如想把学生表里major的所有生物和化学改为生化。
也可以支持对满足查找条件一笔资料进行多处修改。要预先知道修改的东西属于什么属性。比如把学生id为1的他的名字和主修改成小灰和物理。
如果UPDATE语句不写WHERE条件，mysql会把整个set定位的属性下的所有值都改成目标值。
资料删除：
DELETE FROM `表名`
WHERE `属性名` = 属性值;。由于where是条件，这里可以写等号也可以写小于、大于等判断号。sql语句不等于是'<>'。
同样的，如果DELETE语句不加WHERE条件，整个表的所有资料都会被删除。
3.4资料的取得（资料查找）
资料取得(排序)
SELECT 属性名, 属性名
FROM `表名`
ORDER BY `属性名1`, `属性名2` (ASC/DESC LIMIT 数字)
或者
SELECT 属性名, 属性名
FROM `表名`
WHERE `属性名` = 属性值
ORDER BY `属性名1`, `属性名2` (ASC/DESC LIMIT 数字)
order by必须为select语句中的最后一条子句。否则会出错。
在学习where子句时，order by也位于where之后。
子句：一个关键字加上所提供的数据组成。
3.5排序
资料取得语句一般就是上面那样的结构。select * from中的 ‘*’ 就表示查看资料时展示所有属性。如果在学生表里只填name属性。检索的资料只会出现name这一列属性。需要查看多个属性列，添加多个属性即可。
ASC是ascension升序的缩写。DESC是descesion降序的缩写。asc和desc作为后缀放到属性名后面。oder by后面跟多个属性，先按在前的属性排序，比如属性1值相同的情况下，再按属性2排序。默认是升序排序。 DESC关键字只作用于位于其前面的列名。如果需要对多个列进行降序排列，必须对每一列指定DESC关键字。
按多个列排序
比如：
select id, price, name
from product
order by price, name;。
先对含价格的行进行排序。有且只有价格price值相同时才对含name的行进行排序。
上面的例子就是：学生表里我要看学生的所有属性。呈现规则是按分数排序。
按相对列位置排序
select id, price, name
from product
order by 2, 3;。
这条语句等同于先按price再按name排序。懒人写法。主要提醒当语句变动时记得修改order by后面的数字。
4.过滤数据（筛选）
limit X关键词是限制呈现资料的数量，limit X就是呈现X笔资料。 
资料查找中或OR条件的高级写法IN。等价于多个major = 'XX' OR在一起。
检索时无重复值
selec distinct `列A`
from `表A`;。
限制结果
selec `列A`from `表A`
limit x1;。
from `表A`
limit 4 offset 3;。
from `表A`
limit 3,4;。
范围值检查between ... and ...（搜索在某一范围内的值）
where `列A` between X1 and x2;。
在条件语句后面使用between and即可查看列A在x1和x2之间的数据。
空值检查 is null
where `列A` is null; 。查看列A所有无值的行。
5.高级数据过滤
逻辑操作符（操作符）
连结（或改变）where子句中子句的关键字。and、or。
-- 5.1.1 and操作符 select prod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4;
求值顺序
操作符的处理优先级：括号里的 > and > or。
举例：假如需要列出价格为10美元及以上，且由DLL01或BRS01制造的所有产品。
或筛选为一组逻辑，大于10美元为一组逻辑。多使用圆括号。
-- 5.1.3求值顺序 select prod_name, prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10;
关联表的创建
外键书写方法
foreign key(`属性名`) references `表X` (`表X属性`) on delete set null;。
表创建好后，调整表格为表格增添外键
alter table `表名` add foreign key(`属性名`) references `表X` (`表X属性名`) on delete set null;。
多个主键设置
primary key(`属性名1`, `属性名2`),。
cascade：级联；表示在父表更新或删除时，自动更新或删除子表的匹配记录。
AS
用于指定别名。
为列表达式指定别名通常在select之后from之前。
-- 为列指定别名 SELECT name AS 姓名, birth AS 出生日期 FROM student_table; -- 为表达式指定别名 SELECT id, name, YEAR(birth) AS 出生年份 FROM student_table;
对表使用别名，尤其是在多表查询中使用别名：
-- 为表指定别名（简化查询） SELECT s.id, s.name, s.birth FROM student_table AS s WHERE s.sex = '男'; -- 在多表连接中特别有用 SELECT s.name, g.score FROM student_table AS s JOIN grade_table AS g ON s.id = g.student_id;
AS指定别名挺灵活的，了解了之后就能知道在对什么使用别名。
关联表资料的插入
Employee和Branch表互为父子表，双方均有外键指向对方。如果我们直接给Employee表插入资料，branch_id以1插入则不合法。理由是branch_id外键关联的Branch表尚未存在主键为1的这笔资料。
会报错：
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`studentdata1`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`branch_id`) ON DELETE SET NULL)。
同理先插入Branch表资料也不合法，manager_id关联的Employee主键emp_id也尚未存在。正确的做法是先插入Branch表的资料，插入时manager_id这个外键先以null值插入。然后在插入Employee表资料之后。再修改Branch表的资料的manager_id属性。
可以看到是给Branch表的manager_id表加了如果Empoyee表的资料如果删除，manager_id设为null这个约束的，所以我们以插入branch表时，manager_id以null插入。
公司资料表资料取得练习
聚合函数
count 函数专门用来数数。
这里对sup_id进行计数，由于有个数值为null所以返回4。
对于这题思路，先把符合要求的资料呈现出来，最后用count函数把符合要求的资料一套就数出来了。select只是要呈现资料的多少属性。要精细查找还得用where条件。
AVG求平均函数。同理的先查薪水，再用avg函数一套就行。
SUM求和函数。同理的先查薪水，再用sum函数一套就行。
max最大值函数。同理的先查薪水，再用max函数一套就行。
min最小值函数。同理的先查薪水，再用mix函数一套就行。
wildcards万用字符、通配符、模糊查找， %代表多个字符，_代表1个字符
LIKE关键字，模糊查找
这是查找前3位是254的手机号。
date日期有固定格式，月前面是5位。所以用下划线来逐个排除。
联集union
union把两个表里展示的属性合并为一列。使用时所查询的多个表里的属性的数据类型需要一致，每个表所查询的属性的个数也要一样。右图为错误做法。
集联返回的属性名以第一个查询指令中的属性名为准。
改变集联返回的属性名，在已有属性名后使用as关键字和 '自定义属性名'即可。
JOIN链接
INNER JOIN 可以简写为 JOIN（在大多数数据库中两者等价）。此处在部门表中新增仪表资料是为了后面检验join语句是否成功。
ON
on是连接两个表时使用的条件语句。
JOIN可以把两个有关联的表所呈现的属性拼在一起。表A和表B的两个属性下的资料值要相等。
可以看到join之后两张表变成一张表。甚至可以从员工表中找部门名字。
LEFT JOIN
left join的作用是左表无条件呈现，右边符合连接条件的才呈现。不符合条件的返回null。right join则反过来。
如果两个表中的属性名相同，合并后呈现会混淆。则使用C语言结构体的写法，指明每个属性的作用域。
subquery子查询
子查询就是一个查询语句的结果作为另一个查询语句的条件。
当一个查询语句返回多个结果要作为另一个查询语句的子查询语句时，上级查询语句得使用in。
on delete
group by
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
group by需要写在where后面。
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
1.统计 access_log表各个 site_id 的访问量
select `site_id`, sum(access_log.count) as 'nums'
from `access_log` group by `site_id`;
sum和group by缺少其中一个均无法运行。资料需要呈现site_id还要呈现每个id的总访问量。sum是对各个id下的访问量进行求和。而分组则交给group by来完成。
sum中的属性无需单引号括起来，使用带点作用域表示某个属性范围即可。
2.统计各个网站的访问量
逻辑应当是：先把表连接起来，然后再分组。最后再呈现啥属性。
分组排名rank() over(partition by XX order by XX) as XX
create table `student_scr`( `id` int primary key, `name` varchar(20), `class` varchar(20), `score` int ); describe table `student_scr`; insert into `student_scr` values (1, 'Alice', 'A', 85), (2, 'Bob', 'A', 90), (3, 'Charlie', 'B', 95), (4, 'David', 'B', 80), (5, 'Emma', 'A', 75), (6, 'Frank', 'B', 85); -- 现在按班级分组进行降序排名，展示学生的所有信息，并额外展示一个排名信息 select * , rank() over(partition by `class` order by `score` desc) as ranking from `student_scr`;
SELECT id, name, class, score,
RANK() OVER (PARTITION BY class ORDER BY score DESC) AS ranking
FROM student_scores;。
上述代码select后as之前只是要显示的列，最终资料展示的形态则取决于函数处理。函数是对行的一笔笔资料来处理的。
在select 里使用了rank() over关键字；partition后面是按什么分组；order by是按什么排序。
笛卡尔积
定义：在 SQL 的 JOIN 操作中，当连接条件（ON 子句）的 key 在左表或右表中有重复值时，匹配结果会变成这些重复值的所有组合。
简单来说就是，连接左表和右表时，在on的条件上，左表只有1笔资料，右表有2笔资料和左表的1笔资料匹配。连接成新表时，左表的那笔资料会被复制一次，为了和右表的两笔资料匹配。同理地，左表两笔资料和右表两笔资料匹配。最终连接结果右表那笔资料也会被复制。这样就变成了1*2行。通常发生在两本表连接时匹配的条件非主键的情况。
笛卡尔积：
是指两个集合（表）的所有可能组合。
如果表 A 有 m 行，表 B 有 n 行，它们的笛卡尔积就是 m × n 行。
在 JOIN 中，如果 ON 条件的 key 没有唯一约束，就可能产生部分笛卡尔积。
('1009' , '李四', '2000-01-01', '男');
('1010' , '李四', '2001-01-01', '男');
('1006' , '王五', '2000-08-06' , '女');
('1008' , '张三', '2002-12-01', '女');
('1012' , '张三', '2001-12-01', '女');
('1011' , '李四', '2002-08-06' , '女');
执行
from (
select * from student_table where sex = '男' ) t1
join
(select * from student_table where sex = '女')t2
on t1.name = t2.name ; 。
浙公网安备 33010602011771号