mysql 知识点

---相关网站
 1 https://www.cnblogs.com/linhaifeng/articles/7495918.html
 2 https://www.luffycity.com/home
 3 https://book.apeland.cn/details/12/


关系型数据库
    把复杂的数据逻辑归纳为二元结构（二维表格数据），表格与表格之间可以相互关联
  便于查询

1 常见的关系型数据库
 1 mysql ,小巧，免费，功能强大，目前市场上大多互联网公司都在使用
 2 Microsoft SQL Server 微软数据库，只能在windows平台使用
 3 oracle,oracle公司出品 速度快 稳定 贵 维护成本高
 4 DB2.IBM提供的数据库 运行在UNIX上，生活中少见

01 数据库相关概念
    1、数据库服务器：运行数据库管理软件的计算机

    2、数据库管理软件：mysql，oracle，db2，slqserver

    3、库：文件夹

    4、表:文件

    5、记录：事物一系列典型的特征：egon,male,18,oldgirl

    6、数据：描述事物特征的符号

02 初识SQL语句
前期准备：
mysql服务端的启动与终止 net start（stop） mysql ；
切换用户： mysql -uroot -p
修改密码： mysqladmin -uroot -p password ‘123‘
查看当前使用用户 ： select user（）；
查看当前所在文件夹：select database（）；

SQL语句：

操作文件夹（库）
   增
      create database db1 charset utf8;
   查
      show create database db1;（显示编码格式）

      show databases;
   改
      alter database db1 charset gbk;（修改编码格式）
   删
      drop database db1;

操作文件（表）
   切换文件夹：use db1;
   查看当前所在文件夹：select database();

   增
      create table t1(id int,name char);
   查
      show create table t1;
      show tables;
      desc t1; （显示表结构）
   改
      alter table t1 modify name char(6);
      alter table t1 change name NAME char(7);
   删
      drop table t1;

操作文件内容（记录）
   增
      insert t1(id,name) values(1,‘egon1‘),(2,‘egon2‘),(3,‘egon3‘);
   查
      select id,name from db1.t1;
      select * from db1.t1;
   改
      update db1.t1 set name=‘SB‘;
      update db1.t1 set name=‘ALEX‘ where id=2;
   删
      delete from t1;
      delete from t1 where id=2;

03 存储引擎介绍
1、什么是存储引擎？
   存储引擎就是表的类型

2、查看MySQL支持的存储引擎
   show engines;

3、指定表类型/存储引擎
   create table t1(id int)engine=innodb;
   create table t2(id int)engine=memory;
   create table t3(id int)engine=blackhole;
   create table t4(id int)engine=myisam;

   insert into t1 values(1);
   insert into t2 values(1);
   insert into t3 values(1);
   insert into t4 values(1);

04 日期类型
create table student(
   id int,
   name char(6),
   born_year year,
   birth_date date,
   class_time time,
   reg_time datetime
);

insert into student values
(1,‘egon‘,now(),now(),now(),now());

insert into student values
(2,‘alex‘,"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");

05 字符类型
char:定长
varchar：变长

#宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));

insert into t13 values(‘李杰 ‘); #‘李杰   ‘
insert into t14 values(‘李杰 ‘); #‘李杰 ‘


select char_length(name) from t13; #5
select char_length(name) from t14; #3


select name from t13 where name=‘李杰‘;
select name from t13 where name like ‘李杰‘;




name char(5)
egon |alex |wxx  |


name varchar(5)
1bytes+egon|1bytes+alex|1bytes+wxx|
4+egon|4+alex|3+wxx|

06 枚举类型和集合类型
create table consumer(
   id int,
   name char(16),
   sex enum(‘male‘,‘female‘,‘other‘),
   level enum(‘vip1‘,‘vip2‘,‘vip3‘), # 一个
   hobbies set(‘play‘,‘music‘,‘read‘,‘run‘) # 多个
);


insert into consumer values
(1,‘egon‘,‘male‘,‘vip2‘,‘music,read‘);

insert into consumer values
(1,‘egon‘,‘xxxxx‘,‘vip2‘,‘music,read‘); # 没有为空

07约束条件 not null 与 default
create table t15(
   id int(11) unsigned zerofill
);

create table t16(
   id int,
   name char(6),
   sex enum(‘male‘,‘female‘) not null default ‘male‘
);

insert into t16(id,name) values(1,‘egon‘);

08 约束条件 unique key
unique key

单列唯一
   #方式一
   create table department(
      id int unique,
      name char(10) unique
   );
   #方式二：
   create table department(
      id int,
      name char(10)，
      unique(id),
      unique(name)
   );

   insert into department values
   (1,‘IT‘),
   (2,‘Sale‘);

联合唯一
create table services(
   id int,
   ip char(15),
   port int,
   unique(id),
   unique(ip,port)
);

insert into services values
(1,‘192.168.11.10‘,80),
(2,‘192.168.11.10‘,81),
(3,‘192.168.11.13‘,80);

insert into services values
(4,‘192.168.11.10‘,80);

09 primary key
primary key
约束：not null unique
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键

# 单列主键
create table t17(
   id int primary key,
   name char(16)
);
insert into t17 values
(1,‘egon‘),
(2,‘alex‘);
insert into t17 values
(2,‘wxx‘);
insert into t17(name) values
(‘wxx‘);

create table t18(
   id int not null unique,
   name char(16)
);

# 复合主键
create table t19(
   ip char(15),
   port int,
   primary key(ip,port)
);

insert into t19 values
(‘1.1.1.1‘,80),
(‘1.1.1.1‘,81);

10 约束条件之auto_increment
auto_increment

create table t20(
   id int primary key auto_increment,
   name char(16)
);

insert into t20(name) values
(‘egon‘),
(‘alex‘),
(‘wxx‘);

insert into t20(id,name) values
(7,‘yuanhao‘);

insert into t20(name) values
(‘egon1‘),
(‘egon2‘),
(‘egon3‘);
#了解
   show variables like ‘auto_inc%‘;

   #步长：
   auto_increment_increment默认为1
   #起始偏移量
   auto_increment_offset默认1

   #设置步长
   set session auto_increment_increment=5;
   set global auto_increment_increment=5;

   #设置起始偏移量
   set global auto_increment_offset=3;
   强调：起始偏移量<=步长

   create table t21(
      id int primary key auto_increment,
      name char(16)
   );

   insert into t21(name) values
   (‘egon‘),
   (‘alex‘),
   (‘wxx‘),
   (‘yxx‘);
清空表：
   delete from t20;
   delete from t20 where id = 3;
   insert into t20(name) values
   (‘xxx‘);

   truncate t20; #应该用它来清空表
11 约束条件之foreign key
foreign key:建立表之间的关系

#1、建立表关系：
   #先建被关联的表,并且保证被关联的字段唯一
   create table dep(
      id int primary key,
      name char(16),
      comment char(50)
   );


   #再建立关联的表
   create table emp(
      id int primary key,
      name char(10),
      sex enum(‘male‘,‘female‘),
      dep_id int,
      foreign key(dep_id) references dep(id)
      on delete cascade  #同步删除
      on update cascade  #同步更新
   );

#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

#再往关联表插入记录
insert into emp values
(1,‘egon‘,‘male‘,1);

insert into emp values
(2,‘alex‘,‘male‘,1),
(3,‘wupeiqi‘,‘female‘,2),
(4,‘yuanhao‘,‘male‘,3),
(5,‘jinximn‘,‘male‘,2);

delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;

12 表之间的关系
foreign key:建立表之间的关系

#1、建立表关系：
   #先建被关联的表,并且保证被关联的字段唯一
   create table dep(
      id int primary key,
      name char(16),
      comment char(50)
   );


   #再建立关联的表
   create table emp(
      id int primary key,
      name char(10),
      sex enum(‘male‘,‘female‘),
      dep_id int,
      foreign key(dep_id) references dep(id)
      on delete cascade  #同步删除
      on update cascade  #同步更新
   );

#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

#再往关联表插入记录
insert into emp values
(1,‘egon‘,‘male‘,1);

insert into emp values
(2,‘alex‘,‘male‘,1),
(3,‘wupeiqi‘,‘female‘,2),
(4,‘yuanhao‘,‘male‘,3),
(5,‘jinximn‘,‘male‘,2);

delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;

12 表之间的关系
两张表之间的关系：
   1、多对一
      出版社    书（foreign key(press_id) references press(id)）
   2、多对多
      作者       书
      egon:
         九阳神功
         九阴真经
      alex:
         九阳神功
         葵花宝典
      yuanhao:
         独孤九剑
         降龙十巴掌
         葵花宝典
      wpq:
         九阳神功

      insert into author2book(author_id,book_id) values
      (1,1),
      (1,2),
      (2,1),
      (2,6);

   3、一对一
      customer表       student表

13 单表查询

三 简单查询
复制代码
company.employee
    员工id      id                  int
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum(‘male‘,‘female‘) not null default ‘male‘, #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2), #  15为数字个数，2为小数个数
office int, #一个部门一个屋子
depart_id int
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum(‘male‘,‘female‘) | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部
(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘成龙‘,‘male‘,48,‘20101111‘,‘teacher‘,10000,401,1),

(‘歪歪‘,‘female‘,48,‘20150311‘,‘sale‘,3000.13,402,2),#以下是销售部门
(‘丫丫‘,‘female‘,38,‘20101101‘,‘sale‘,2000.35,402,2),
(‘丁丁‘,‘female‘,18,‘20110312‘,‘sale‘,1000.37,402,2),
(‘星星‘,‘female‘,18,‘20160513‘,‘sale‘,3000.29,402,2),
(‘格格‘,‘female‘,28,‘20170127‘,‘sale‘,4000.33,402,2),

(‘张野‘,‘male‘,28,‘20160311‘,‘operation‘,10000.13,403,3), #以下是运营部门
(‘程咬金‘,‘male‘,18,‘19970312‘,‘operation‘,20000,403,3),
(‘程咬银‘,‘female‘,18,‘20130311‘,‘operation‘,19000,403,3),
(‘程咬铜‘,‘male‘,18,‘20150411‘,‘operation‘,18000,403,3),
(‘程咬铁‘,‘female‘,18,‘20140512‘,‘operation‘,17000,403,3)
;
#三个部门：教学，销售，运营


#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
复制代码
复制代码
#简单查询
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee; （distinct 不同种类的）

#通过四则运算查询
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(‘姓名: ‘,name,‘  年薪: ‘, salary*12)  AS Annual_salary
   FROM employee;

   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(‘:‘,name,salary*12)  AS Annual_salary
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN NAME = ‘egon‘ THEN
               NAME
           WHEN NAME = ‘alex‘ THEN
               CONCAT(name,‘_BIGSB‘)
           ELSE
               concat(NAME, ‘SB‘)
           END
       ) as new_name
   FROM
       emp;
复制代码
小练习：

1 查出所有员工的名字，薪资,格式为
    <名字:egon>    <薪资:3000>
2 查出所有的岗位（去掉重复）
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

单表查询

select distinct 字段1,字段2,字段3 from 库.表
   where 条件
   group by 分组条件
   having 过滤
   order by 排序字段（优先级在 distinct 之后）
   limit n;


#where
select id,name,age from employee where id > 7;

# and
select name,post,salary from employee where post=‘teacher‘ and salary > 8000;

# between.. and..
select name,salary from employee where salary >= 20000 and salary <= 30000;
select name,salary from employee where salary between 20000 and 30000;
# not between and
select name,salary from employee where salary < 20000 or salary > 30000;
select name,salary from employee where salary not between 20000 and 30000;

# or 或者 in
select * from employee where age = 73 or age = 81 or age = 28;
select * from employee where age in (73,81,28);

# is (is not)
select * from employee where post_comment is Null;
select * from employee where post_comment is not Null;

# like 模糊匹配
select * from employee where name like "jin%";（% 任意字符）
select * from employee where name like "jin___";（___3个任意字符）


#group by
mysql> set global sql_mode="ONLY_FULL_GROUP_BY";
分组之后，只能取分组的字段，以及每个组聚合结果
select post from employee group by post;

#聚合函数
max
min
avg
sum
count

#每个职位有多少个员工
select post,count(id) as emp_count from employee group by post;
select post,max(salary) as emp_count from employee group by post;
select post,min(salary) as emp_count from employee group by post;
select post,avg(salary) as emp_count from employee group by post;
select post,sum(age) as emp_count from employee group by post;

#没有group by则默认整体算作一组
select max(salary) from employee;

#group_concat
select post,group_concat(name) from employee group by post;

#having （过滤，分组之后）
select post,group_concat(name),count(id) from employee group by post;

select post,group_concat(name),count(id) from employee group by post having count(id) < 2;

select post,avg(salary) from employee group by post having avg(salary) > 10000;

#order by 排序
select * from employee order by age asc; #升序
select * from employee order by age desc; #降序

select * from employee order by age asc,id desc; #先按照age升序排，如果age相同则按照id降序排


select distinct post,count(id) as emp_count from employee
   where salary > 1000
   group by post
   having count(id) > 1
   order by emp_count desc
   ;

#limit （限制条数）
select * from employee limit 3;
select * from employee order by salary desc limit 1;


select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;

#总结：
一 SELECT语句关键字的定义顺序
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
      （select distinct 字段1,字段2,字段3 from 库.表
          from
         where 条件
         group by 分组条件
         having 过滤
         order by 排序字段
         limit n;显示条数）
   执行顺序：

二 关键字的执行优先级(重点)

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

详细见：http://www.cnblogs.com/linhaifeng/articles/7372774.html

#正则表达式
select * from employee where name like ‘jin%‘;
select * from employee where name regexp ‘^jin‘; （^以jin开头）
select * from employee where name regexp ‘^jin.*(g|n)$‘;（^以jin开头，$以（g或n结尾））


14 连表操作
# 交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;

内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;

左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;

右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;

全外连接：在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id ;
（不支持full_join）

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id ;

-练习题
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;
13、查询全部学生都选修了的课程号和课程名
17、查询平均成绩大于85的学生姓名和平均成绩
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

 --- 子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

1 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in
        (select id from department where name=‘技术‘);

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

2 带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

3 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

17 权限管理
权限管理
select * from mysql.user\G  # 查询相关用户 授权信息

   1、创建账号
      # 本地账号
      create user ‘egon1‘@‘localhost‘ identified by ‘123‘; # mysql -uegon1 -p123
      # 远程帐号
      create user ‘egon2‘@‘192.168.31.10‘ identified by ‘123‘; # mysql -uegon2 -p123 -h 服务端ip
      create user ‘egon3‘@‘192.168.31.%‘ identified by ‘123‘; # mysql -uegon3 -p123 -h 服务端ip
      create user ‘egon3‘@‘%‘ identified by ‘123‘; # mysql -uegon3 -p123 -h 服务端ip

   2、授权
      user:*.*
      db:db1.*
      tables_priv:db1.t1
      columns_priv:id,name

      grant all on *.* to ‘egon1‘@‘localhost‘; # 授权
      grant select on *.* to ‘egon1‘@‘localhost‘;
      revoke select on *.* from ‘egon1‘@‘localhost‘; 取消，废除

      grant select on db1.* to ‘egon1‘@‘localhost‘;
      revoke select on db1.* from ‘egon1‘@‘localhost‘;
    3 平常使用(创建+授权)
        grant all on *.* to ‘root‘@‘%‘ identified by ‘123‘
        flush privileges; (刷新)

      grant select on db1.t2 to ‘egon1‘@‘localhost‘;
      revoke select on db1.t2 from ‘egon1‘@‘localhost‘;

      grant select(id,name),update(age) on db1.t2 to ‘egon1‘@‘localhost‘;

18 Navicat工具的使用
    用于自己开发和调试
    一 IDE工具介绍
生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具

下载链接：https://pan.baidu.com/s/1bpo5mqj

掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表
#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键

19 pymtsql模块
# 安装
pip install pymysql

# --链接，执行sql，关闭（游标）
user=input(‘用户名: ‘).strip()
pwd=input(‘密码: ‘).strip()

#链接
conn=pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘123‘,database=‘egon‘,charset=‘utf8‘)
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)


#执行sql语句
sql=‘select * from userinfo where name="%s" and password="%s"‘ %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)

cursor.close()
conn.close()

if res:
    print(‘登录成功‘)
else:
    print(‘登录失败‘)

18 视图
一 视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，
用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
一 创建视图

#语法：CREATE VIEW 视图名称 AS  SQL语句
create view teacher_view as select tid from teacher where tname=‘李平老师‘;

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view)

二 触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

19 存储过程

一 介绍

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

使用存储过程的优点：

#1. 用于替代程序写的SQL语句，实现程序与sql解耦

#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
使用存储过程的缺点：

#1. 程序员扩展功能不方便

#1、无参存储过程
   delimiter //
   create procedure p1()
   BEGIN
      select * from db7.teacher;
   END //
   delimiter ;


   # MySQL中调用
   call p1();


   # Python中调用
   cursor.callproc(‘p1‘)


#2、有参存储过程
   delimiter //
   create procedure p2(in n1 int,in n2 int,out res int)
   BEGIN
      select * from db7.teacher where tid > n1 and tid < n2;
      set res = 1;
   END //
   delimiter ;


   # MySQL中调用
   set @x=0
   call p2(2,4,@x);
   select @x;

   # Python中调用
   cursor.callproc(‘p2‘,(2,4,0))# @_p2_0=2,@_p2_1=4,@_p2_2=0
   cursor.execute(‘select @_p3_2‘)
   cursor.fetchone()

应用程序与数据库结合使用
方式一：
   Python:调用存储过程
   MySQL:编写存储过程


方式二：
   Python：编写纯生SQL
   MySQL：

方式三：
   Python：ORM->纯生SQL
   MySQL：


# 存储过程的执行

#1、增删改
import pymysql

# 建立链接
conn=pymysql.connect(
    host=‘192.168.10.15‘,
    port=3306,
    user=‘root‘,
    password=‘123‘,
    db=‘db7‘,
    charset=‘utf8‘
)

# 拿游标
cursor=conn.cursor()

# 执行sql
# cursor.callproc(‘p1‘)
# print(cursor.fetchall())

cursor.callproc(‘p2‘,(2,4,0))
# print(cursor.fetchall())

cursor.execute(‘select @_p2_2‘)
print(cursor.fetchone())

# 关闭
cursor.close()
conn.close()

mysql 知识点

原文：https://www.cnblogs.com/huahuawang/p/12662776.html