2022/04/29 Fri 08:05    2022/06/12 Sun 21:48

数据库的知识就暂时整理到这里，后面有能力和需求再继续补充

76条经典的数据库语言
自带的字符客户端MySQL

-- 删除已经创建的表
drop table dept;
drop table employees;
drop table managers;
-- 创建部门表
create table dept(
deptno int primary key,
dname varchar(9),
loc varchar(10));

-- 创建员工表
create table employees(
empno int primary key ,
name char(10) not null,
deptno int,
manager int,
hiredate date,
salary numeric(7, 2)) ;


-- 创建经理表
create table managers(
empno int primary key,
title varchar(16));

-- insert 语句向员工表中插入记录，包括所有字段的值
insert into employees values(1,'张三',1,2,'2011-03-03',4400.00);
insert into employees values(2,'王五',1,2,'2013-01-03',4100.00);
insert into employees values(3,'李四',2,4,'2021-02-10',3900.00);
insert into employees values(5,'李明',3,5,'2004-09-11',5200.00);
insert into employees values(6,'孙军',3,5,'2016-05-22',4700.00);
insert into employees values(7,'钱杰',3,5,'2013-07-22',4900.00);
insert into employees values(8,'李爱国',3,5,'2013-07-22',4900.00);
-- 向员工表中插入记录，只包括部分字段的值
insert into employees(empno,name,salary) values(4,'赵六',5000.00);
insert into employees(empno,name,hiredate,salary) values(9,'刘国强','2010-04-12',5000.00);
-- 向部门表中插入记录
insert into dept values(1,'技术部','一楼a 区');
insert into dept values(2,'销售部','二楼a 区');
insert into dept values(3,'行政部','一楼b 区');

-- 向经理表中插入记录
insert into managers values(2,'技术部经理');
insert into managers values(4,'销售部经理');
insert into managers values(5,'行政部经理');
insert into managers values(99,'总裁');

-- 主键重复的记录无法插入
insert into employees values(1,'郭君声',1,2,'2010-04-11',6100.00);
insert into dept values(3,'市场部','一楼a 区');
-- 不能将现有的记录修改成违反主键约束的记录
update managers set empno=2 where empno=4;

-- select 语句查询员工表的所有记录的所有字段
select * from employees;
-- select 语句查询员工表的所有记录的姓名和工资字段
select name,salary from employees;

-- distinct 去重复值查询员工表中的所有经理号
select manager from employees;
-- 查询员工表中的不重复的经理号
select distinct manager from employees;


-- where 条件过滤查询在3 部门的员工的员工的姓名和工资：
select name,salary from employees where deptno=3;
-- 查询2010 年前入职的员工的姓名和工资：
select name,salary from employees where hiredate<'2010-01-01';

-- and 和or 运算符查询出第3 部门里工资大于等于5 千的员工
select * from employees where deptno=3 and salary>=5000;

-- 查询出第3 部门或者工资大于等于5 千的员工：
select * from employees where deptno=3 or salary>=5000;
-- 查询出第3 部门或者第1 部门中工资大于等于5 千的员工：
select * from employees where (deptno=3 or deptno=1) and salary<5000;

-- like 搜索匹配的字符串找出在一楼办公的部门：
select * from dept where loc like '一楼%';
-- 找出姓李的员工
select * from employees where name like '李%';

-- in 匹配多个值列出员工号为3 和5 的员工：
select name from employees where empno in (3,5);
-- 前面的SQL 语句相当于
select name from employees where empno=3 or empno=5;
-- 找出不在部门3 和部门5 的员工的姓名
select name from employees where empno not in (3,5);
-- 找出在一楼办公的员工的姓名：
select name from employees where deptno in (select deptno from dept where loc like '一楼%');

-- between 查询出在2013 年之间入职的员工的姓名：
select name from employees where hiredate between '2013-01-01' and '2013-12-31';

-- 查询出不在2013 年入职的员工：
select name from employees where hiredate not between '2013-01-01' and '2013-12-31';

-- 按工资从低到高列出员工姓名和工资：
select name,salary from employees order by salary;
-- 如果要按从高到低输出，加上desc 的关键字：
select name,salary from employees order by salary desc;

-- 下面的命令按照员工的入职顺序列出员工：
select name,hiredate from employees order by hiredate;
-- update 修改数据先查询一下员工号为4 的员工信息：
select * from employees where empno=4;
-- 下面的sql 语句将这个员工所属的部门改成2，经理改成4:
update employees set deptno=2, manager=4 where empno=4;
-- 修改完成后再次查询这个员工的记录如下：
select * from employees where empno=4;

-- delete 删除记录查询9 号员工的记录：
select * from employees where empno=9;
-- 删除9 号员工的记录：
delete from employees where empno=9;
-- 再次查询9 号员工的记录：
select * from employees where empno=9;
-- create index 创建索引在表employees 的name 字段上创建索引
create index in_name on employees(name);

-- create view 创建视图创建一个视图名为employees_2015 的视图，包括2015 年之前入
职的员工的姓名和工资。
create view employees_2015 as select name,salary from employees where hiredate <'2015-01-01';
-- 查询这个视图
select * from employees_2015;
-- 更新这个视图，把张三的工资增加400 元：
update employees_2015 set salary=salary+400 where name='张三';
-- 再次查询这个视图，发现已经更新成功：
select * from employees_2015;

-- 检查底层表，发现也已经更新成功：
select name,salary from employees where name='张三';

-- 删除视图
drop view employees_2015;
-- null 值查询不属于任何部门的员工的信息
select * from employees where deptno is null;

-- 查询有经理的员工的信息：
select * from employees where manager is not null;
-- 字段或表的别名把字段名换成中文，表意更明确；把表名换成简短的字符串，写起
来更方便

select name 姓名, salary 工资from employees e;
-- 把count(*)的字段用汉字“总人数”代替，表意更清楚
select count(*) 总人数from employees;

-- join 连接表inner join 内连接，内连接查询出员工表和经理表中都存在的员工的姓名
和头衔
select name,title from employees inner join managers on employees.empno=managers.empno;

-- left join 左连接查询员工表中的所有记录，并列出他们在经理表中的头衔，如果在经
理表中没有记录头衔用null 代替
select name,title from employees left join managers on employees.empno=managers.empno;

-- right join 右连接查询经理表中的所有记录，并列出他们在员工表中的姓名，如果在员
工表中没有记录姓名用null 代替
select name,title from employees right join managers on
employees.empno=managers.empno;

-- 子查询查询在二楼办公的员工的姓名
select name from employees where deptno in (select deptno from dept where loc like '二楼%');

-- 查询不在员工表中的经理
select * from managers where empno not in (select empno from employees);

--常用的函数
-- count 函数count(*)统计统计员工表中的所有员工
select count(*) from employees ;

-- 统计员工表中所有部门号不为null 的员工
select count(deptno) from employees ;

-- 查询员工表中部门号为null 的员工
select * from employees where deptno is null;

-- max 函数查询员工表中工资最高的员工
select max(salary) from employees;

-- min 函数查询员工表中最早入职的员工
select min(hiredate) from employees;

-- avg 函数查询员工表中员工的平均工资
select avg(salary) from employees;

-- sum 函数查询员工表中员工的工资合计
select sum(salary) from employees;

-- group by 分组统计按部门统计员工的平均工资
select deptno,avg(salary) from employees group by deptno;

-- having 过滤分组统计查询部门员工平均工资小于4500 的部门
select deptno,avg(salary) from employees group by deptno having avg(salary)<4500;

数据存放在数据库库中,SQL是用于访问和处理数据库的标准计算机语言，是指结构化查询语言，机会所有的关系数据库都可以用，不需要区分大小写。

访问数据库

如何定义数据的存储格式

为了便于程序保存和读取数据，而且，能直接通过条件快速查询到指定的数据，就出现了数据库（Database）这种专门用于集中存储和查询的软件。
数据库软件诞生的历史非常久远，早在1950年数据库就诞生了。经历了网状数据库，层次数据库，关系数据库，我们现在广泛使用的关系数据库是20世纪70年代基于关系模型的基础上诞生的。

数据库的类型

* MySQL，大家都在用，一般错不了；
* PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL高；
* sqlite，嵌入式数据库，适合桌面和移动应用。

作为Python开发工程师，选择哪个免费数据库呢？当然是MySQL。因为MySQL普及率最高，出了错，可以很容易找到解决方法。而且，围绕MySQL有一大堆监控和运维的工具，安装和使用很方便。

关于python3中默认的数据库：SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。

Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。

  1. 表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，比如学生的表，班级的表，学校的表，等等。表和表之间通过外键关联。
  2. 要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；
  3. 连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。
  4. Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。
  5. 由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。
# 导入SQLite驱动:
>>> import sqlite3
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
>>> conn = sqlite3.connect('test.db')
# 创建一个Cursor:
>>> cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
<sqlite3.Cursor object at 0x10f8aa260>
# 继续执行一条SQL语句，插入一条记录:
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x10f8aa260>
# 通过rowcount获得插入的行数:
>>> cursor.rowcount
1
# 提交事务:
>>> conn.commit()
# 关闭Cursor:
>>> cursor.close()
# 关闭Connection:
>>> conn.close()

查询记录

>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
# 执行查询语句:
>>> cursor.execute('select * from user where id=?', ('1',))
<sqlite3.Cursor object at 0x10f8aa340>
# 获得查询结果集:
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
>>> cursor.close()
>>> conn.close()

使用注意事项

  1. 使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后一定记得关闭（否则会出现资源的泄露），就可以放心地使用。
  2. 使用Cursor对象执行insert，update，delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果。
  3. 使用Cursor对象执行select语句时，通过fetchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录。
  4. 如果SQL语句带有参数，那么需要把参数按照位置传递给execute()方法，有几个?占位符就必须对应几个参数

在python中连接MySQL:

使用MySQL的原因：MySQL是Web世界中使用最广泛的数据库服务器。SQLite的特点是轻量级、可嵌入，但不能承受高并发访问，适合桌面和移动应用。而MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远远大于SQLite。
为什么要连接？（这一步可以跳过，自己已经连接好了）
由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以，需要支持Python的MySQL驱动来连接到MySQL服务器。MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数–allow-external：

# 自己已经试验成功的，没有啥问题
# 以下为如何连接到MySQL服务器的test数据库：
# 由于Pythonde DB-API定义都是以通用的，操作MySQL的数据库代码和SQLite类似。
>>> import mysql.connector
>>> conn = mysql.connector.connect(user='root', password='xr1806144220092',database='test')
>>> cursor = conn.cursor()
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1
>>> conn.commit()
>>> cursor.close()
True
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
>>> cursor.close()
True
>>> conn.close()
>>>

使用SQLAlchemy

可以用Python的数据结构表示出来，用list表示多行，list的每一个元素是tuple,但是用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示，就可以更容易地看出表的结构来：
更多关于ORM框架的知识可以后面去学习

# 也即ORM技术：把关系数据库的表映射到对象上
# 用ORM框架来做转换（SQLAlchemy）
# ORM就是把数据库表的行与列相应的对象建立关联，互相转换
# 由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能
class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]

数据模型：

按照数据结构来组织、存储和管理数据，一共有三种模型：

  1. 层次模型：上下级的层次关系来组织数据的方式，像一颗树
  2. 网状模型：把每个数据节点和其他的很多节点连接起来，像很多城市的路网
  3. 关系模型：二维表格，像一个excel表格



    主流的关系数据库：
  4. 商用数据库：Oracle，SQL Server，DB2
  5. 开源数据库：MySQL，PostgreSQL等
  6. 桌面数据库：以微软Access为代表，适合桌面应用程序使用
  7. 嵌入式数据库：以Sqlite为代表，适合手机应用和桌面程序

SQL语言定义了几种操作数据库的能力：

  1. DDL(Data Definition Language):允许用户定义数据，创建表、删除表、修改表结构等

  2. DML(Data Manipulation Language):为用户提供添加、删除、更新数据的能力（应用程序对数据库的日常操作）

  3. DQL(Data Query Language):允许用户查询数据，这也是通常最频繁的数据库日常操作

  4. 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

  5. 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

  6. 字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’’。

  7. 关系数据库的表和表之间需要建立“一对多”，“多对一”以及“一对一”的关系（按照应用程序的逻辑来组织和存储数据）

  8. 在关系数据库中，关系通过主键和外键来维护

主键

  1. 主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
  2. 可以使用多个列作为联合主键（允许通过多个字段唯一标识记录，用两个或更多的字段都设置为主键），但联合主键并不常用。

外键

  1. 外键就可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证
  2. 多对多：通过一个表的外键关联到另一个表，我们可以定义出一对多的关系，同时也有多对多的关系(多对多的关系是通过两个一对多关系实现的，通过一个中间表，关联两个一对多关系，就形成了多对多关系)



    通过中间表teacher_class知道teachers和class的关系
  3. id=1的张老师对应id=1,2的一班和二班
  4. id=2的王老师对应id=1,2的一班和二班
  5. id=3的李老师对应id=1的一班
  6. id=4的赵老师对应id=2的二班
    也可以知道classes到teachers的关系
  7. id=1的一班对应id=1,2,3的张老师、王老师和李老师
  8. id=2的二班对应id=1,2,4的张老师、王老师和赵老师
    相关的一些代码：
    外键不是通过列名实现的，而是通过定义外键约束来实现的
    ALTER TABLE students
    ADD CONSTRAINT fk_class_id
    FOREIGN KEY (class_id)
    REFERENCES classes (id);
    要删除一个外键约束，通过ALTER TABLE实现的：
    ALTER TABLE students
    DROP FOREIGN KEY fk_class_id;

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

  1. 通过堆数据库表创建索引，可以提高查询速度
  2. 通过创建唯一索引，可以保证，某一列的值具有唯一性
  3. 数据库索引对于用户和应用程序都是透明的

查询数据

练习：自动创建test数据库，并在test数据库下创建students表和classes表，以及必要的初始化数据步骤如下：

mysql -u root -p
输入密码：xr1806144220092

把sql的文件上传到服务器上面去：source E:\init-test-data.sql (文件放在了E盘下面)
回车之后就会发现已经成功了，之后就是

show databases;（实现数据库）
show tables from test;

基本查询

SELECT * FROM <表名> （可以查询一个表的所有行和所有列的数据）

其中SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。该SQL将查询出students表的所有数据，查询的结果是一个二维表，它包含列名和每一行的数据。
SELECT * FROM classes; （查询所有表的数据）

SELECT 1;（来测试数据库连接）

use test;（切换到数据库）
还有一个问题亟待解决：文字乱码的问题（后面解决）

  1. 内容替换：把文件里的charset=utf-8字样改为charset=gb2312
  2. 编码转换：把记事本中：格式-字体 保存成gb2312，文件保存时设置成ansi的编码方式。

条件查询

使用背景：很多时候，我们并不希望获得所有记录，而是根据条件选择性地获取指定条件的记录，例如，查询分数在80分以上的学生记录。在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。
语句：SELECT * FROM students WHERE score >= 80;
公式化：SELECT * FROM <表名> WHERE <条件表达式>

如果是存在两个条件的，需要把两个条件都写出来
条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

  1. 条件1：根据score列的数据判断：score >= 80；
  2. 条件2：根据gender列的数据判断：gender = ‘M’，注意gender列存储的是字符串，需要用单引号括起来。
  3. WHILE条件为：score >= 80 AND gender = ‘M’
    按照AND条件查询students:
    SELECT * FROM students WHILE score >=80 ANDgender = ‘M’;

    第二种条件：<条件1>OR<条件2>，表示满足条件1或条件2，以下的结果是查询“分数在80分或以上或男生”，满足任意之一的条件就可以选出该记录。OR比AN要宽松很多
    SELECT * FROM students WHERE score >= 80 OR gender = ‘M’;

    第三种条件：NOT<条件>，表示不符合该条件的记录
    SELECT * FROM students WHERE NOT class_id = 2;（不是二班的学生）

    要组合三个或更多的条件，需要用（）表示如何进行条件运算
    SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = ‘M’;

    常用的一些表达式

投影查询

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。
如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM …，让结果集仅包含指定列。这种操作称为投影查询。
例子一：SELECT id, score, name FROM students;（从学生中查询序号、分数、姓名 ）

SELECT 列1, 列2, 列3 FROM …，可以给每一个列取一个别名，这样结果集的列名就可以与原表的列名不同，语法是：SELECT 列1 别名1，列2 别名2，列3 别名3 FROM…
例子二：SELECT id, score points, name FROM students;(用SELECT语句将列名score重命名为point,而且id和name列名保持不变)

可以接WHILE条件，实现复杂的查询
例子三：SELECT id,score points,name FROM students WHERE gender = ‘M’;

小结如下：

  1. 使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。
  2. SELECT语句可以对结果集的列进行重命名。

排序

对于使用SELECT查询的时候，查询的结果通常是按照id排序的（根据主键排序），这是大部分数据库的做法，如果想要根据其他的条件排序的话，可以用ORDER BY子句
例子一：SELECT id,name,gender,score FROM students order by score;(按照score从低到高)

如果score列有相同的数据，要进一步排序，可以继续添加列名，使用ORDER BY score DESC,gender表示先按score列排序，如果有相同的分数，再按照gender列排序
例子二：SELECT id,name,gender, score FROM students ORDER BY score DESC,gender;(按照score,gender排序)

默认的排序规则是ASC:升序，即从小到大，ASC可以省略，ORDER BY score ASC 和ORDER BY score效果一样，如果有WHERE子句，那么ORDER BY 子句要放到WHERE子句后面
例子三：带WHERE条件的ORDER BY:

# 查询一班的学生成绩，并按照倒序排序
# 结果集合仅包含符合WHERE条件的记录，并按照ORDER BY的设定排序
SELECT id ,name,gender,score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

小结如下：

  1. 使用ORDER BY可以对结果集进行排序
  2. 可以对多列升序、倒序排序

分页查询

分页

使用SELECT查询时，数据量很大的时候，放在一个页面显示数据量太大，可以分页显示每次显示100条。
如何实现分页功能：

  1. 从结果集中显示第1100条记录作为第一页，显示101200条记录作为第二页
  2. 归纳如下：从结果集中’截取’出第M~N条记录，查询可以通过LIMIT OFFSET 子句实现
    以下为一些实现的过程：
    第一步：SELECT id ,name,gender, score FROM students ORDER BY score DESC;

    第二步：把结果集分页，每页3条记录，要获取第一页的记录，可以使用LIMIT 3 OFFSET 0:
# 查询第一页
SELECT id, name,gender,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;   # 对结果集从0号记录开始，最多取3条，注意SQL记录集的索引从0开始


# 查询第2页，将OFFSET  3(跳过前3条)
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;


# 查询第3页，将OFFSET  6(跳过前6条)
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;


# 查询第4页，将OFFSET  9(跳过前9条)
# 最后一页只有一条记录
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;

总结如下：

分页的关键在于首先要确定每页需要显示的结果数量pageSize(3),然后根据当前页的索引pageindex（从1开始），确定LIMIT和OFFSET应该设定的值，然后就能正确的查询出第N页的记录集

  1. LIMIT总是设定为pageSize
  2. OFFSET计算公式为pageSize *(pageIndex -1)
  3. 如果OFFSET超过了查询的最大的数量并不会报错，会得到一个空的结果集（例如最多有10条的，但是我编写LIMIT 3 OFFSET 20, 只能得到一个空的结果集）
  4. OFFSE是可选的，如果只写LIMIT 15，相当于LIMIT 15 OFFSET 0
  5. LIMIT 15 OFFSET 30 可以简写为LIMIT 30,15（OFFSET就是截取的意思，截取（30，30+15]之间的）
  6. 使用LIMIT OFFSET ,N越大，查询的效率也会越来的越低

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：
例子一：SELECT COUNT(*) FROM students; (使用聚合查询学生总数)

COUNT():
表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但**查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT()*。
通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：
SELECT COUNT() num FROM students;
COUNT()和COUNT(id)实际上是一样的效果，聚合查询同样可以使用WHERE条件，我们可以方便的统计出有多少男生、多少女生、多少80分以上的学生等
例子：SELECT COUNT() boys FROM students WHERE gender = ‘M’;

除了COUNT()函数外，SQL还提供了如下的聚合函数：
SUM: 计算某一列的合计值，该列必须为数值类型
AVG: 计算某一列的平均值 ，且必须为数值类型
MAX: …(不限数值类型，会返回排序最后的字符)
MIN: ….(不限数值类型，会返回排序最前的字符)
例子：SELECT AVG(score) average FROM students WHERE gender = ‘M’;(使用聚合查询计算男生平均的成绩)

注意点：如果聚合查询WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM() 、AVG() MAX() MIN()会返回NULL
question:每页三条记录，如何通过聚合查询获得总页数：SELECT CEILING(COUNT(*)/3) FROM students;

分组

SELECT COUNT() num FROM students WHERE class_id = 1;可以用来查询1班的学生数量，
如果像继续统计二班、三班的学生数量的话，SQL提供了一个分组聚合的功能
按照class_id进行分组：SELECT COUNT() num FROM students GROUP BY class_id;

GROUP BY 子句指定了按class_id分组，因此，在执行SELECT语句的时，会把class_id相同的列先分组，再分别计算，得到3行结果，但是有一个缺陷，无法看出分别时哪三个班级，我们可以把class_id列的结果放入结果集合中：
SELECT class_id, COUNT() num FROM students GROUP BY class_id;(可以十分清晰的看出各个班级的学生人数)

也可以使用多个列进行分组，比如想统计各班的男生和女生人数：
SELECT class_id , gender ,COUNT() num FROM students GROUP BY class_id, gender;(按照class_id和gender进行分组，以下的6条记录分别对应3个班的男生和女生的人数)

questions:使用一条select查询出每个班级的平均分：SELECT class_id,AVG(score) average FROM students GROUP BY class_id;
questions:使用一条select查询出每个班级男生和女生的平均分：SELECT class_id,gender, AVG(score) average FROM students GROUP BY class_id, gender;

多表查询

SELECT查询不仅可以从一张表查询数据，还可以从多张表中同时查询数据，语法如下：SELECT *FROM <表1> <表2>
SELECT * FROM students,classes; (同时从students和classes表的乘积，即查询数据)
一次查询两个表的数据，查询的结果也是一个二维表，是students表和classes表的乘积，即students表的每一行与classes表的每一行都两两拼在一起返回，结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积，其中有两列id（分别是students表和class表的id）和两列name(分别是students表和class表的name),但是在结果集中却无法区分
解决的方法：利用投影查询的’设置列的别名’来给两个表各自的id和name起别名

SELECT
        students.id sid,
        students.name,
        students.gender,
        students.score,
        classes.id cid,
        classes.name cname
FROM students, classes;

注意点：多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复的问题，但是当每个表的列数很多的时候就会很麻烦，
解决办法：SQL允许给表设置一个别名，让在投影查询中引用起来稍微简洁一点:用别名s和c分别表示students表和classes表。

SELECT 
        s.id sid,
        s.name,
        s.gender,
        c.score,
        c.id cid,
        c.name cname
FROM students s, classes c;

多表查询也可以添加WHERE条件：

SELECT 
        s.id sid,
        s.name,
        s.gender,
        s.score,
        c.id cid,
        c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

这个表的特点：结果集每行记录都满足条件s.gender = ‘M’和c.id = 1,添加WHERE条件后结果集的数量大大减少。

连接查询

连接查询是另一种类型的多表查询，连接查询对多个表进行JOIN运算：先确定一个主表作为结果集，然后把其他表的行有选择性的“连接”在主表结果集上。
举例说明：
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
运行的结果如下：

增加需求：我们希望结果集合中包含所在班级的名称，上面的结果集只有class_id列，缺少name列
解决办法：根据students表的class_id，找到classes表对应的行，再取出name列就可以获得班级名称，使用最常用的内部连接——INNER JOIN来实现

SELECT s.id, s.name, s.class_id,c.name class_name, s.gender,s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;


INNER JOIN查询的写法的注意事项：

  1. 先确定主表：FROM<表1>
  2. 再确定需要连接的表：INNER JOIN <表2>
  3. 然后确定连接的条件：使用ON<条件…>,这里的条件是：s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接。
  4. 可选：加上WHERE子句、ORDER BY等子句
    如果是使用OUTER JOIN
SELECT s.id, s.name, s.class_id,c.name class_name, s.gender,s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;


比较INNER JOIN和OUTER JOIN的结果

  1. 多出了一行是’四班’，与学生相关的列name gender score 都为NULL,students表中并不存在class_id=4的行
  2. 有RIGHT OUTER JOIN 就有LEFT OUTER JOIN以及FULL OUTER JOIN
    1. INNER JOIN只返回同时存在于两张表的行数据
    2. RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
    3. EFT OUTER JOIN则返回左表都存在的行。

使用图来表示结果集，相应的结果就是一目了然

修改数据

增删改查 ：Create Retrieve Update Delete
对应的语法：

  1. INSERT:插入新的记录
  2. UPDATE:更新已有的记录
  3. DELETE:删除已有的记录

INSERT

基本语法：INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
举例说明：向students表中插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

INSERT INTO students (class_id, name,gender,score) VALUES (2,'大牛' ,'M' ,80);
SELECT *FROM students;


还可以一次性添加多条记录，只需要在VALUE子句中指定多个记录，每个记录是由（…）包含的一组值

INSERT INTO students (class_id,name,gender,score) VALUES
    (1,'大宝','M',87),
    (1,'二宝','M',81)
SELECT * FROM students;

dudata

基本语句：UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
需求：想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name = ‘大牛’，scores=66,然后在WHERE子句中写出需要更新的行的筛选条件：id=1

--更新id=1的记录
UPDATE students SET name = '大牛'，score=66 WHERE id=1;
--查询并观察结果
SELECT * FROM students WHERE id=1;

在此基础上，一次更新多条记录：

UPDATE students SET name = '大牛'，score=77 WHERE id>=5 AND id<=7;
SELECT *FROM sudents;

MYSQL

在MYSQL clinet中输入的SQL语句通过TCPO连接发送到MySQL Sever,默认的端口是3306，即如果发送到本机MYSQL Sever，地址就是127.0.0.1：3306
也可以只只安装MySQL Lient,然后连接到远程MySQL Sever
命令运行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是myqld,在后台运行。
要管理MYSQL，可以使用可视化图形界面My Workbench，是一个图形的客户端，它对MYSQL的操作是发送SQL语句并执行.MySQL Workbench 和MySQL交互，唯一的接口就是SQL
在一个运行MySQL的服务器上，实际上可以创建多个数据库（Datebase),要列出所有的数据库有如下的：
mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| shici |
| sys |
| test |
| school |
+——————–+
informaiton_schema mysql performace_scheam和sys是系统库，不需要去改动，其他的是用户创建的数据库

ToTOP
2018 bear