SQL学习

1.连接查询

1.1交叉连接，产生笛卡尔积，没有实际作用。
语法：

select * from 表1 cross join 表2；

1.2内连接，显示共同的那部分。
语法：

select * from 表1 inner join 表2 on 匹配条件；

(匹配条件一般是 表一的班级id=表二的id)
1.3外连接，分为左连接和右连接
左连接，以左边的表为主表，匹配另外的表。
语法：

select * from 表1 left join 表2 on 匹配条件；

右连接，以右边的表为主表，匹配另外的表。
语法：

select * from 表1 right join 表2 on 匹配条件；

1.4 拓展 using关键字，两个表需要有个字段相同，一般不用。
语法：select * from 表1 left join 表2 using（相同的字段）；

2.mysql查询的五种子句

where子句（条件查询）：按照“条件表达式”指定的条件进行查询。
group by子句（分组）：按照“属性名”指定的字段进行分组。group by子句通常和count()、sum()等聚合函数一起使用。
having子句（筛选）：有group by才能having子句，只有满足“条件表达式”中指定的条件的才能够输出。
order by子句（排序）：按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出，默认是按照“asc”来排序，即升序。
limit（限制结果集）
2.1 group by
语句：
SELECT coalesce(name, ‘总数’), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
±-------------------------±-------------+
| coalesce(name, ‘总数’) | singin_count |
±-------------------------±-------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
±-------------------------±-------------+

3.子查询

3.1标量子查询：结果为一个数据（一行一列）
基本语法：
select 列名 from 表1 where （条件，select 列名 from 表 where 条件）；
例子：
select name from my_class where id = (select class_id from my_teacher where name = ‘tom’);
3.2列子查询：结果为一列多行
基本语法：
主查询 where 列名 in 子查询；
例子：
select name from my_class where id in (select class_id from my_teacher);
3.3行子查询：结果为一行多列
行元素：
行元素对应多个字段
基本语法：
主查询 where 条件[（构造行元素）]=（行子查询）
例子：
select * from my_teacher where (age,height)=(select max(age),max(height) from my_teacher);
3.4表子查询：结果是一个表（多行多列）
基本语法：
select * from （表子查询）as 别名 [where…]；
例子：
select * from (select * from my_teacher order by height desc)as temp group by class_id;
3.5Exists子查询：结果只有0和1，0代表不成立，1代表成立。
基本语法：
where exists（查询语句）；
例子：
select * from my_class as c where exists (select class_id from my_teacher as t where t.class_id = c.id);
3.6子查询关键字：in，any，some，all（了解）

4.整库数据备份与还原

5.用户权限

5.1创建用户
语法：create user 用户名 identified by ‘明文密码’;
5.2删除用户
5.3修改密码

• 专门语句
  语法：
  set password for 用户 = password(‘新密码’);

• 更新user表
  语法：
  update mysql.user set password = password(‘新密码’) where user=’’ and host=’’;

5.4权限管理

• 授予权限：grant
  语法：grant 权限列表/all privileges on 表 to 用户；
  例子：grant select on mydatabase2.my_teacher to ‘user1’@’%’;
• 取消权限：revoke
  语法：revoke 权限列表/all privileges on 表 from 用户;
  例子：revoke all privileges on mydatabase2.my_teacher from ‘user1’@’%’；
• 刷新权限：flush
  语法：flush privileges;

5.5root密码找回

• 停止服务
• 重启服务
• 重连并跳过grant
• 更改root密码
• 停止和重启新服务

6.外键 :foreign key

6.1概念
一张表（A）有一个字段，保存的值指向另一张表（B）的主键
B:主表 A:从表

6.2增加外键
1.创建表时增加外键
语法：
例子：create table my_foreign(
id int primary key auto_increment,
name varchar(10) not null,
class_id int,
foreign key(class_id) references my_class(id) #创建外键指定其他表的字段
)charset utf8;
2.创建完了再增加外键
语法：
例子：alter table my_teacher foreign key(class_id) references my_class(id);
6.3删除和修改外键
1.修改：外键不允许修改，只能先删除再增加。
2.删除
语法：
例子：alter table my_teacher drop foreign key my_teacher_ibfk_1;
6.4设置约束等级
1.严格
2.从随主便
3.置空
6.5作用
1.保证数据一致性，约束主表。

7.视图:虚拟表

7.1创建视图
例子：create view teacher_class_v as select * from my_teacher;
7.2使用视图
例子：跟使用表类似
7.3.更改视图
例子：alter view teacher_class_v as select * from my_class;
7.4删除视图
例子：drop view teacher_class_v;

8.事务：innodb

8.1事务原理（了解）
8.2自动事务
查看指令：

show variables like ‘autocommit’;

关闭自动指令：

set autocommit = ‘off’;

手动提交和回滚：

commit； 或者 rollback；

8.3手动事务
开启事务：

start transaction;

执行事务：需要执行的sql语句
提交事务：

commit或者rollback;

设置回滚点：

savepoint 回滚点名字;

回到回滚点：

rollback to 回滚点名字;

8.4事务特性

• 原子性
• 一致性
• 隔离性
• 持久性

9.变量

9.1系统变量
指令：select @@autocommit;
9.2修改变量

• 局部修改：
• set autocommit = 值;
• 全局修改：
• set global autocommit=值; 或者 set @@global.autocommit=值;
  全局修改后，不改当前值，后面连接的会话才会改变。

9.3会话变量（用户变量）

• 普通赋值指令：

• set @name := ‘hello world’;（专有赋值符号 := ）
• set @name = ‘hello world’;

• 通过查询数据赋值：

• select @name := name,@age:=age from my_teacher limit 1;
• select name,age from my_teacher order by height limit 1 into @name,@age;
• select @name,@age;

9.4局部变量

10.流程结构（代码执行顺序）

10.1 if分支

• 基本条件判断语法：

select *,if (age>20,‘符合’,‘不符合’) as judge from my_teacher;

• 复杂语句块中使用（函数、存储过程、触发器）：

if  条件表达式  then
  		满足条件执行;
end if;

• 复合语句

if  条件表达式  then  	
	满足条件执行;  	
else  		
	不满足条件执行;  		
	(如果有分支，继续if判断)  	
end if;

10.2 while循环

• 基本语法：

while 条件 do
	要循环的代码;
end while;

• 结构标识符：为循环命名，为了循环体中使用循环控制。

• iteratie：（continue）跳过此次循环

• leave：跳出循环

标识名字:while 条件 do
	if 条件判断 then
		循环控制;
		 iteratie/leave 标识名字;
	end if;
	循环
end while

11.函数

11.1内置函数

---

浅谈数据认识及数据库

• 认识数据：
  • 数据产生
  • 数据保存
  • 数据备份
  • 数据查找
• 数据库：是一种特殊的文件，其中保存着我们需要的数据
  • 优点：持久化存储、读写速度极高、保证数据的有效性、对程序支持性好，容易扩展。
  • 分类：关系型数据库、非关系型数据库
    mysql就属于关系型数据库(RDBMS.Relational Database management System)
• SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持SQL语言。
  • SQL语句主要分为：
    • DQL：数据查询语言，用于对数据进行查询，如select
    • DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
    • TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
    • DCL：数据控制语言，进行授权与权限回收，如grant、revoke
    • DDL：数据定义语言，进行数据库、表的管理等，如create、drop
    • CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
  • 对于web开发，爬虫开发程序员而言，主要掌握DML、DQL(增删改查)，DDL数据库、数据表的管理。

Mysql数据库

• 安装：sudo apt-get install mysql-server

• 启动：sudo service mysql start

  • 脚本启动：/etc/init.d/mysql start
  • 使用safe_mysql启动：safe_mysql &

• 停止：sudo service mysql stop

  • 脚本启动的关闭：/etc/init.d/mysql stop
  • mysqladmin shut down

• 命令行客户端安装：sudo apt-get install mysql-client

• 连接命令：mysql -uroot -pmysql

• quit 或者 exit 退出

数据库操作

• 查看所有数据库
  show databases;
• 使用数据库
  use 数据库名;
• 查看当前使用的数据库
  select database();
• 创建数据库
  create database 数据库名 charset=utf8;
  例：
  create database python charset=utf8;
• 删除数据库
  drop database 数据库名;
  例：
  drop database python;

数据表操作

• 查看当前数据库中所有表
  show tables;
• 查看表结构
  desc 表名;
• 创建表
• auto_increment表示自动增长
  • CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    …
    columnN datatype,
    PRIMARY KEY(one or more columns)
    );
  • 例：创建班级表
    create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
    );
  • 例：创建学生表
    create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default ‘’,
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum(‘男’,‘女’,‘人妖’,‘保密’),
    cls_id int unsigned default 0
    )
• 修改表-添加字段
  alter table 表名 add 列名 类型;
  例：
  alter table students add birthday datetime;
• 修改表-修改字段：重命名版
  alter table 表名 change 原名 新名 类型及约束;
  例：
  alter table students change birthday birth datetime not null;
• 修改表-修改字段：不重命名版
  alter table 表名 modify 列名 类型及约束;
  例：
  alter table students modify birth date not null;
• 修改表-删除字段
  alter table 表名 drop 列名;
  例：
  alter table students drop birthday;
• 删除表
  drop table 表名;
  例：
  drop table students;
• 查看表的创建语句
  show create table 表名;
  例：
  show create table classes;

数据的增删改

• 增加

  • insert into 表名 values(…)
    说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准
    全列插入：值的顺序与表中字段的顺序对应

  • insert into 表名(列1,…) values(值1,…)
    部分列插入：值的顺序与给出的列顺序对应

  • 全列多行插入：值的顺序与给出的列顺序对应

    • insert into 表名 values(…),(…)…;
    • insert into 表名(列1,…) values(值1,…),(值1,…)…;

• 修改

  • update 表名 set 列1=值1,列2=值2… where 条件

• 删除

  • delete from 表名 where 条件
  • 逻辑删除，本质是修改操作
    update students set isdelete=1 where id=1;
    ###数据库的备份与恢复

• 备份
  运行mysqldump命令
  mysqldump –uroot –p 数据库名 > python.sql;

• 恢复
  连接mysql，创建新的数据库
  退出连接，执行如下命令
  mysql -uroot –p 新数据库名 < python.sql
  ###数据库的查询操作

• 普通查询

  • 查询所有字段
    select * from 表名;
  • 查询指定字段
    select 列1,列2,… from 表名;
  • 使用as 给字段起别名
    select id as 序号, name as 名字, gender as 性别 from students;
  • 可以通过 as 给表起别名(多表查询，列必须指定所属的表明，此时给表起别名便于书写sql语句)
    select s.name,s.age,s.gender,c.name from students as s,classes as c;
  • 在select后面列前使用distinct可以消除重复的行
    select distinct 列1,… from 表名;

• 条件查询：

  • 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
    select * from 表名 where 条件;
  • where后面支持多种运算符，进行条件的处理
    • 比较运算符
      等于: =
      大于: >
      大于等于: >=
      小于: <
      小于等于: <=
      不等于: != 或 <>
    • 逻辑运算符
      and
      or
      not
    • 模糊查询
      like
      %表示任意多个任意字符
      _ 表示一个任意字符
    • 范围查询
      • in表示在一个非连续的范围内
      • between … and …表示在一个连续的范围内
    • 空判断
      注意：null与’'是不同的,null不占内存空间
      • 判空is null
      • 判非空 is not null
        ####优先级
        优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
        and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序

• 语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]
• 说明：将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
  默认按照列值从小到大排列（asc）
  asc从小到大排列，即升序
  desc从大到小排序，即降序
• 示例：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序
  select * from students order by age desc,height desc;

聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

• 总数：count( * )表示计算总行数，括号中写星与列名，结果是相同的
  例：查询学生总数
  select count( * ) from students;
• 最大值：max(列)表示求此列的最大值
  例：查询女生的编号最大值
  select max(id) from students where gender=2;
• 最小值：min(列)表示求此列的最小值
  例：查询未删除的学生最小编号
  select min(id) from students where is_delete=0;
• 求和：sum(列)表示求此列的和
  例：查询男生的总年龄
  select sum(age) from students where gender=1;
• 平均值：avg(列)表示求此列的平均值
  例：查询未删除女生的编号平均值
  select avg(id) from students where is_delete=0 and gender=2;

分组

• group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
  group by可用于单个字段分组，也可用于多个字段分组
• group by + group_concat()
  • 例：select gender,group_concat(name) from students group by gender;
• group by + 集合函数
  通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
  示例：
  • select gender,group_concat(age) from students group by gender;
  • 分别统计性别为男/女的人年龄平均值
    select gender,avg(age) from students group by gender;
  • 分别统计性别为男/女的人的个数
    select gender,count( * ) from students group by gender;
  • group by + having
    having 条件表达式：用来分组查询后指定一些条件来输出查询结果
    having作用和where一样，但having只能用于group by
    例：
    select gender,count( * ) from students group by gender having count( * )>2;
  • group by + with rollup
    with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
    例：
    select gender,count( * ) from students group by gender with rollup;

分页

• 语法：select * from 表名 limit start,count
• 示例：分页
  已知：每页显示m条数据，当前显示第n页
  求总页数：此段逻辑后面会在python中实现
  查询总条数p1
  使用p1除以m得到p2
  如果整除则p2为总数页
  如果不整除则p2+1为总页数
  求第n页的数据
  select * from students where is_delete=0 limit (n-1)* m,m

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
mysql支持三种类型的连接查询，分别为：

• 内连接查询
• 右连接查询
• 左连接查询
• 语法：select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
  select * from A inner join B A.name=B.writer

自关联查询

示例：针对省市县的区域存储，存到一张表，进行自关联查询。语法参照连接查询
表结构，id,atitle(区域名称)，pid(该区域上级区域的id)

• 例1：查询省的名称为“山西省”的所有城市
  select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle=‘山西省’;
• 例2：查询市的名称为“广州市”的所有区县
  select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle=‘广州市’;

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

• 主查询和子查询的关系：
  子查询是嵌入到主查询中
  子查询是辅助主查询的,要么充当条件,要么充当数据源
  子查询是可以独立存在的语句,是一条完整的 select 语句

• 子查询分类
  标量子查询: 子查询返回的结果是一个数据(一行一列)
  列子查询: 返回的结果是一列(一列多行)
  行子查询: 返回的结果是一行(一行多列)

• 标量子查询
  查询班级学生平均年龄
  查询大于平均年龄的学生
  查询班级学生的平均身高
  select * from students where age > (select avg(age) from students);

• 列级子查询
  查询还有学生在班的所有班级名字
  找出学生表中所有的班级 id
  找出班级表中对应的名字
  select name from classes where id in (select cls_id from students);

• 行级子查询
  需求: 查找班级年龄最大,身高最高的学生
  行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
  select * from students where (height,age) = (select max(height),max(age) from students);

• 子查询中特定关键字使用
  in 范围
  格式: 主查询 where 条件 in (列子查询)

select 总结

• 完整的select语句
  select distinct *
  from 表名
  where …
  group by … having …
  order by …
  limit start,count

• 执行顺序为：
  from 表名
  where …
  group by …
  select distinct *
  having …
  order by …
  limit start,count

外键相关

如何防止无效信息的插入,就是可以在插入前判断类型或者品牌名称是否存在呢? 可以使用之前讲过的外键来解决 外键约束:对数据的有效性进行验证 关键字: foreign key,只有 innodb数据库引擎 支持外键约束

• 添加外键语法：alter table 主表名 add foreign key(添加外键的字段) references 副表名(副表对应外键字段);

• 创建表时外键字段：foreign key(添加外键字段) references 副表名(副表对应外键字段)

• 取消外键约束
  show create table 表名; – 查看表的创建语句获取外键名称
  alter table goods drop foreign key 外键名称

• 说明：创建表的顺序是有要求的,即如果goods表中的外键约束用的是goods_cates或者是goods_brands,那么就应该先创建这2个表,否则创建goods会失败
  创建外键时,一定要注意类型要相同,否则失败

###视图

• 定义：通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
  视图是对若干张基本表的引用，一张虚表，查询语句执行的结果
• 创建视图：create view 视图名称 as select语句;
• 查看视图：show tables;
• 使用视图：select * from v_stu_score;
• 删除视图：drop view 视图名称;
• 视图作用：
  1.提高了重用性，就像一个函数
  2.对数据库重构，却不影响程序的运行
  3.提高了安全性能，可以对不同的用户
  4.让数据更加清晰

###事务
认识：所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

• 事务四大特性(简称ACID)：
  1.原子性(Atomicity)
  • 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
    2.一致性(Consistency)
  • 数据库总是从一个一致性的状态转换到另一个一致性的状态
    3.隔离性(Isolation)
  • 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的.
    4.持久性(Durability)
  • 一旦事务提交，则其所做的修改会永久保存到数据库。
• 事务的命令：
  • 开启事务，命令如下：
    开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
    begin;
    或者
    start transaction;
  • 提交事务，命令如下
    将缓存中的数据变更维护到物理表中
    commit;
  • 回滚事务，命令如下：
    放弃缓存中变更的数据
    rollback;
  • 注意
    1.修改数据的命令会自动的触发事务，包括insert、update、delete
    2.而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

认识：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

• 查看索引：show index from 表名;
  如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致

• 创建索引
  如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致字段类型如果不是字符串，可以不填写长度部分
  create index 索引名称 on 表名(字段名称(长度))
  create index putong on person(name varchar(10))
  字段类型如果不是字符串，可以不填写长度部分

• 删除索引：drop index 索引名称 on 表名;

• 可以插入100000条数据，比较有无索引查找数据的时间来判断查找效率。

  • 开启运行时间监测：set profiling=1;
  • 查看执行的时间：show profiles;

• 注意：
  要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
  建立索引会占用磁盘空间