# ======================第一章=======================
# 1.MySQL简单介绍
# 1.数据库服务器:运行数据库管理软件的计算机
# 2.数据库管理软件：mysql，db2, slqserver,
# 3.库：文件夹
# 4.表：文件
# 5.记录：事物一系列典型的特征
# 6.数据：描述事物特征的符号
# 2.登陆与破解：
# 查询当前登陆的账号：select user()；
# 退出登陆：exit
# 管理员账号：-uroot -p (账号root密码为空)
# 初始化账号密码：mysqladmin -uroot -p password"新密码" (cmd)
# 修改密码：mysqladmin -uroot -p"原密码" password"新密码" (cmd)
# update mysql.user set password=password("abc123") where user="root" and host="localhost"；
# 关闭服务：net stop MySQL (cmd)
# 跳过授权表登陆：mysqld --skip-grant-tables
# 登陆时候指定端口：mysql -uroot -pliuwei -h127.0.0.1 -P3306 (默认端口3306)
# 3.统一字符编码
# 查看数据库基础属性信息： \s
# 新建配置文件my.ini (复制以下内容)
# [mysqld]
# character-set-server=utf8
# collation-server=utf8_general_ci
# [client]
# default-character-set=utf8
# [mysql]
# default-character-set=utf8
# 4.初识SQL语句
# 操作文件夹：操作库
# 增：create database db1 charset utf8; # 创建一个新库db1，编码设置为utf8
# 删：drop database db1; # 删除库
# 改：alter database db1 charset gbk # 修改库的编码为GBK
# 查：show create database db1; # 查看刚创建的库db1
# show databases; # 查看所有的库
# select database(); # 查看当前在哪个库
# 操作文件：
# .frm 文件用于存放表结构 .ibd 文件用于存放表数据
# 切换文件夹：use db1; # 把工作路径切换到db1库
# 增：create table t1(id int,name char); # 创建一个表，表里面有id号（整数型）名字（字符型）
# 删：drop table t1 # 删除表t1
# truncate 表名; # 完全清空表（包括自增设置）
# 改：alter table t1 modify name char(6); # 修改表t1的name字段，修改char的宽度为6（默认为1）
# 设置的宽度就代表字节，写1就代表1个字节长度
# alter table t1 change name NAME char(7); # 修改字段名name变成NAME,设置char宽度为7。
# 查：show create table t1 # 查看刚才创建的表t1
# show tables； # 查看当前库下面所有的表
# desc t1; # 查看表结构信息
# 操作文件内容：记录
# 增：insert t1(id,name) values(1, 'alex'),(2, 'jack') # 往t1表中插入记录（多条用逗号分隔）
# insert t1 values(1, 'alex'),(2, 'jack') # 不写字段就按照字段的默认顺序来添加
# 删：delete from t1; # 删除t1表中的所有数据（delete多用于跟where一起用）
# delete from t1 where id=2; # 删除表中id为2的那条信息
# 改：update db1.t1 set name='SB' # 把name字段下面的内容都修改为SB
# update db1.t1 set name='SB' where id=2; # 修改ID为2的信息中name字段为SB
# 查：select id,name from t1 # 查看t1表中id,name两个字段对应的值(如果库没在db1下面，就写db1.t1)
# select name from t1 # 也可以单独查看某个单独字段
# select * from t1 # 查看表1下所有字段的信息
# 如果信息太长无法格式化就在语句末尾加上 \G
# 库操作语句
# SQL语句分为三大来：
# DDL语句：例如create drop alter 数据库定义语言
# DML语句：例如delete update select 数据库操纵语言
# DCL语句：例如grant(授权) revoke(移除权限) 数据库控制语言
# 库命名规则：
# 数字，字母，下划线，@, #, $
# 区分大小写
# 唯一性
# 不能使用关键字
# 不能单独为数字
# 最长128位
# 存储引擎
# 定义：存储引擎就是表的类型
# 查看mysql存储引擎：show engines;
# 指定表类型：create table t1(id int)engine=innodb; 设置储存引擎为innodb(默认其实就是这个)
# 指定表类型：create table t2(id int)engine=memory; 这个引擎是把数据存入内存里面，不往硬盘放
# 指定表类型：create table t3(id int)engine=blackhole; 数据存入就消失
# 指定表类型：create table t4(id int)engine=myisam;
# 表增删改查操作细讲：
# 修改表名：alter table 原表名 rename 新表名;
# 增加字段：alter table 表名 add 字段名 数据类型 [约束条件];
# alter table 表名 add 字段名 数据类型 [约束条件] first; 把新加的字段放到第一个位置
# alter table 表名 add 字段名 数据类型 [约束条件] after 字段名; 把新加的字段放到哪一个字段之后
# 删除字段：alter table 表名 drop 表名;
# 修改字段：alter table 表名 modify 字段名 数据类型 [约束条件]; # 修改字段数据类型
# alter table 表名 change 旧字段名 新字段名 旧数据类型 [约束条件]; # 修改字段名
# alter table 表名 change 旧字段名 新字段名 新数据类型 [约束条件]; # 修改字段名和数据类型
# 复制表：create table t1 select * from mysql.user; # 复制mysql库下面的user表内容到新建的表t1里面去（表结构+记录）
# 设定一个不存在的限定条件，然后再复制表，就可以只复制表结构而不复制表记录
# create table t2 select * from mysql.user where 1>5; （表结构）
# 只拷贝表结构使用like就可以了
# create table t3 like mysql.user;
# 整数类型：
# unsigned 限定条件：插入的记录限定为无符号（符号，小数点之类的）
# zerofill 限定条件：如果记录的显示宽度少于设定的宽度，不足的位置用0填充
# 整型（所有int类型）记录都不用设置宽度，这个宽度并不是存储宽度，而是指的它的显示宽度
# 浮点数类型：
# 语法 float(m,d)
# 定义：单精度浮点数，m是数字总个数，d是小数点后个数，m最大为255，d最大为30
# 浮点数跟整数一样也分有符号和无符号
# 最好使用decimal 精度最高，float精度最低（大多数情况也就够了）
# 日期类型：
# year 年份格式
# date 日期格式
# time 时间类型（秒分时）
# datetime （日期+时间）
# now() 调用mysql内置函数（截取当前时间：年 月 日 时 分 秒）
# 举例：建立一个学生信息表
# create table student(
# id int, # ID号 整数类型
# name char(6), # 名字 字符串类型
# born_year year, # 出生年份 年份类型
# birth_date date, # 生日 日期类型
# class_time time, # 上课时间 时间类型
# reg_time datetime # 注册时间 时间+日期类型
# )
# insert into student values(
# 1,'alex',now()#获取当前时间的年份数据,now()#获取当前时间的日期数据,now()#获取当前时间的时间数据,now()#获取当前时间的综合数据
# )
# select * from student
# 1 alex 2018 2018-09-25 14:47:05 2018-09-25 14:47:05
# 字符类型：
# 字符类型分为 char 和 valchar
# char类型指的是定长的字符（固定长度，长度不够就自动补全）
# valchar类型指的是变长的类型（不固定长度）
# select char_length(字段名) from 表名; 可以通过内置函数char_length( )来查询字段记录的长度
# 枚举类型：
# enum 单选 只能在给定范围中选择，如单选框，多选框（从多个当中选择一个）
# set 多选 （多个当中选择多个）
# 举例：建一个客户信息表
# create table consumer(
# id int,
# name char(16),
# sex enum('男','女'), # 性别（2选一）
# hobbies set('喝酒','吃饭','嫖娼','打牌','打球') 爱好（多选）
# );
# insert into consumer values(1,'alex','男','喝酒,嫖娼')
# 传值如果不存在（非法）,则该字段记录为空。
# 约束条件
# unsigned: 限制无符号
# zerofill: 用0填充
# not null default 默认值 限制记录不可为空 例:sex enum('男','女') not null default '男'
# 性别有二选一（男女），不可以为空，默认为男性
# unique 限制字段的记录不可重复 例：name char(10) unique
# 限制姓名不可以重复
# 语法有两种：一种是直接在需要限制不重复的字段创建或修改时加上unique
# 二是在创建语句的末尾加上unique(写需要限制的字段名)
# 举例：
# create table 表名(
# id int unique,
# name char(10) unique
# );
# create table 表名(
# id int,
# name char(10),
# unique(id),
# unique(name)
# );
# 以上限制叫单列唯一：单独一个字段内保持唯一性
# ==========================================================
# 以下限制叫联合唯一（多列唯一）：多个字段内保持唯一性
# 举例：ip和端口联合起来应该是唯一的。
# create table services(
# id int unique, # 这里就是单列唯一
# ip char(15),
# port int,
# unique(ip,port) # 这里就是限制ip+port的组合是必须唯一的
# );
# primary key 限制条件：not null unique 不为空且唯一
# 对于存储引擎innodb来说，一张表内必须有一个主键（就是primary key）
# 创建一个主键(通常都是ID字段设置成主键)
# 单列主键：
# create table 表名(id int primary key,name char(16));
# 复合主键:
# create table 表名(ip char(15),port int,primary key(ip,port));
# auto_increment 限制条件：自增长
# 举例：create table 表名(id int primary key auto_increment,name char(16));
# 把ID字段设置成主键并且自增长。
# 此时仍然可以给ID字段传值，但是后面如果继续传值不带上ID字段的话，ID的自增就从上一次手动传值的位置继续。
# 默认自增的起点为1，步长为1.
# show variables like 'auto_inc%' # 模糊查询auto_inc开头的变量的值（查看自增这个变量的值）
# set session auto_increment_increment=5; # 设置自增步长为5（会话级别：只在本次连接有效）
# set global auto_increment_increment=5; # （全局级别）
# set global auto_increment_offset=3 #设置起点（起点一定要设置的小于步长！！！）
# 注意：delete from 表名 进行清空表操作的时候，不会把自增设置还原，还保留着上次增到的位置。
# foreign key 限制条件用来建立表之间的关系
# 应用场景举例：建立一个员工信息表，员工的部门很多都是一样的，都记录在同一个表中就造成了数据重复，为了提高效率，
# 可以使用foreign key，在员工信息表中设置一个字段叫部门，然后里面填上各个部门的ID号就可以，然后
# 建立一个部门表，把每个部门录入进去，然后设置员工表中的部门字段通过foreign key关联到部门表的ID号
# 这样就可以使两张表关联起来并且避免数据重复。
# 语法示范：
# 1.建表
# 1.1先创建被关联表，并且保证被关联的字段唯一（dep的ID唯一）
# create table dep(
# id int primary key,
# dep_name char(20)
# );
# 1.2后创建关联表
# create table emp(
# id int primary key,
# name char(16),
# sex enum('男','女'),
# dep_id int,
# foreign key(dep_id) references dep(id)
# on delete cascade on update cascade
# );
# 2.插入数据
# 2.1先插入被关联的表数据
# insert dep(id,dep_name) values
# (1,'财务部'),
# (2,'技术部'),
# (3,'销售部');
# 2.2再插入关联的表数据
# insert emp values
# (1,'alex','男',1);
# 3.操作数据
# 因为存在关联关系，所以如果修改、删除数据时，有两种办法
# 1：先修改关联表的数据，再修改被关联表的数据
# 2：在建立关联表时，加上同步指令：on delete cascade on update cascade
# 表示同步删除、同步修改（和foreign key在同一行）
# 此时只要操作被关联表就可以影响到关联表。
# 表关系
# 多对一表关系：
# 两份表放在一起，寻找是否存在一张表的多个记录对应另一张表的一个字段，如果存在，那就是多对一
# 也就是所foreign key存在的表，就是多对一关系
# 例：出版社表---书表 多本书对应一个出版社
# 多对多表关系：
# 例：作者----书 多个作者可以一起写一本书，多本书可以是同一个作者写的（双向多对一）
# 示例：
# create table book(
# id int primary key auto_increment,
# name char(16)
# );
# create table author(
# id int primary key auto_increment,
# name char(16)
# );
# create table author2book(
# id int not null unique auto_increment,
# book_id int not null,
# author_id int not null,
# foreign key(author_id) references author(id)
# on delete cascade
# on update cascade,
# foreign key(book_id) references book(id)
# on delete cascade
# on update cascade,
# primary key(book_id,author_id)
# );
# 一对一表关系：
# 例：客户----学员 客户在没报名学习班之前一直是客户，报名后就变成了学习班的学员
# 原理：foreign key + unique 就可以保证关联性和唯一性，即一对一
# 代码示范:
# create table customer(
# id int primary key auto_increment,
# name char(16) not null,
# sex enum('男','女'),
# QQ bigint not null,
# telphone bigint not null
# );
# create table student(
# id primary key auto_increment,
# class_name varchar(20) not null,
# customer_id int unique,
# foreign key(customer_id) references customer(id)
# on delete cascade
# on update cascade
# );
# 记录增删改查
# 插入完整数据（顺序插入）
# 方法一：insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
# 方法二：insert into 表名 values(值1,值2,值3);
# 多条同时插入：insert into 表名 values
# (值1,值2,值3),
# (值1,值2,值3),
# (值1,值2,值3);
# 指定字段插入数据
# insert into 表名(字段1,字段2) values(值1,值2);
# 插入查询结果
# insert into 表1(字段1,字段2,字段3)
# select (字段1,字段2,字段3) from 表2
# where.......;
# 修改记录
# update 表 set 字段1=值1,字段2=值2 where 条件;
# 删除记录
# delete from 表 where 条件
# 查询记录
# 单表查询：
# select distinct 字段1,字段2,字段3 from 库.表 where 条件 # distinct 去重
# group by 分组条件 # 分组
# having 过滤 # 过滤
# order by 排序字段 # 排序
# limit n; # 限制显示的条数
# 代码示范：
# create table emp(
# id int not null unique auto_increment,
# name char(20) not null,
# sex enum('男','女') not null default '男',
# age int(3) unsigned not null,
# hire_date date not null,
# post char(50),
# post_comment varchar(100),
# salary double(15,2),
# office int,
# dep_id int
# );
# +----+------------+-----+------------+-----------------------------------------+--------------+------------+--------+--------+
# | id | name | age | hire_date | post | post_comment | salary | office | dep_id |
# +----+------------+-----+------------+-----------------------------------------+--------------+------------+--------+--------+
# | 1 | egon | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
# | 2 | alex | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
# | 3 | wupeiqi | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
# | 4 | yuanhao | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
# | 5 | liwenzhou | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
# | 6 | jingliyang | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
# | 7 | jinxin | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
# | 8 | 成龙 | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
# | 9 | 歪歪 | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
# | 10 | 丫丫 | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
# | 11 | 丁丁 | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
# | 12 | 星星 | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
# | 13 | 格格 | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
# | 14 | 张野 | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
# | 15 | 程咬金 | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
# | 16 | 程咬银 | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
# | 17 | 程咬铜 | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
# | 18 | 程咬铁 | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
# +----+------------+-----+------------+-----------------------------------------+--------------+------------+--------+--------+
# 简单查询：
# 1.查询部门（去重）
# select distinct post from emp;
# 2.查每个人的名字和年薪
# select name,salary*12 as year_salary from emp; # as 别名 给查出来的这个数据起个小名
# 3.自定义显示格式：concat
# select concat('姓名：',name,'月薪：',salary) as month_salary,concat('年龄：',age) as age_info from emp;
# (表名：month_salary 姓名:alex 月薪：3500| 年龄：25 )
# select concat(name,':',age) as info from emp;
# (alex:25) # 使用冒号作为分隔符
# select concat_ws(':',name,age,salary) as info from emp;
# (alex:25:8500) # 复用冒号
# where约束条件：(where不可以使用聚合函数)
# 1.可以用比较运算符：< > >= <= <> !=
# select * from emp where id>5; 查询ID号大于5的记录
# 2.between...and... 在什么和什么之间
# select * from emp where id between 3 and 6; 查询ID号在3到6之间的
# 3.in(xx,xx,xx) 在这个范围之内
# select * from emp where id in(3,5,7); 查询ID为3,5,7的
# 4.like 'china%' 模糊匹配（带上 % 表示前面以china开头后面还有内容）（还可以用 _ 表示后面还有1个字符）
# select * from emp where name like 'al%'; 查询名字开头为al的信息
# 5.逻辑运算符：and or not
# select * from emp where post='teacher' and salary>8000; 查询老师职位工资大于8千的
# select * from emp where salary not between 10000 and 20000; 取工资小于1万和大于2万的
# 6.is null 为空
# select * from emp where post_conment is null;
# group by分组（优先级低于where，写在where之后）
# 先进行分组设置：set global sql_mode='ONLY_FULL_GROUP_BY'; 只能取分组的字段（就不可用 * ）
# 此时统计结果就需要聚合函数：(下例都是按每个部门来分组)
# 1.max 最大值
# select post,max(salary) as count from emp group by post; 每个部门最大工资
# 2.min 最小值
# select post,min(salary) as count from emp group by post; 每个部门最小工资
# 3.avg 平均值
# select post,avg(salary) as count from emp group by post; 每个部门平均工资
# 4.sum 求和
# select post,sum(age) as count from emp group by post; 每个部门员工年龄总和
# 5.count 统计
# select post,count(id) as count from emp group by post; 每个部门有多少员工
# 6.group_concat
# select post,group_concat(name) from emp group by post; 获取每个组中成员的名字
# having 过滤（优先级低于group by:也就是这种过滤在分组之后进行）
# where不能用聚合函数，而having则可以使用
# having 后面同样可以使用各种逻辑关系（比较、逻辑...）
# 1.select post,count(id) as emp_count,group_concat(name) as emp_name from emp group by post having count(id) > 2;
# 查看各岗位内包含的员工个数大于2的岗位名、岗位内包含的员工名字，个数
# order by 排序（优先级低于having）（order by运行在distinct之后，也就是说可以使用前面语句的别名）
# mysql默认升序排列，筛选结果默认降序排列
# asc 升序
# desc 降序
# select * from emp order by age asc; 把整个表的年龄按照升序排列
# select * from emp order by age desc; 把整个表的年龄按照降序排列
# select * from emp order by age asc,id desc 先按照年龄升序排列，如果年龄相同就按照ID降序排列
# limit 限制显示个数
# select * from emp limit 2; 限制显示个数为2个
# 单表总结：
# 1.语法顺序
# select distinct 字段1,字段2,字段3 from 库.表 where 条件
# group by 分组条件 # 分组
# having 过滤 # 过滤
# order by 排序字段 # 排序
# limit n; # 限制显示的条数
# 2.执行顺序
# from--->where--->gourp by--->having--->distinct--->order by---->limit
# 正则查询
# select * from emp where name like 'al%';
# select * from emp where name regexp '^al'; 搜al开头的
# select * from emp where name regexp '^al.*(g|n)$'; 搜al开头，中间不限制，以g或者n结尾的
# 连表查询（多表）
# 需求：员工表 + 部门表 进行查询操作
# 举例：查员工对应的部门
# select * from dep2,emp2 where emp2.dep_id=dep2.id;
# （不要用这种语句）如果员工表中的部门ID和部门表中的ID相同,就是想要的数据
# 方式1
# 内连接：只取两个表的共同部分(语句后面还可以跟where这些筛选)
# select * from emp2 inner join dep2 on emp2.dep_id=dep2.id;
# 方式2
# 左连接：在内连接的基础上保留左表的记录(左右对应不上时候仍然保留左边的)
# select * from emp2 left join dep2 on emp2.dep_id=dep2.id;
# 方式3
# 右连接：在内连接的基础上保留右表的记录(左右对应不上时候仍然保留右边的)
# select * from emp2 right join dep2 on emp2.dep_id=dep2.id;
# 方式4
# 全外连接：在内连接的基础上保留左右都没有对应关系的记录
# select * from emp2 left join dep2 on emp2.dep_id=dep2.id
# union
# select * from emp2 right join dep2 on emp2.dep_id=dep2.id;
# 练习题.查询平均年龄大于30岁的部门名
# 1.select dep2.name,avg(age) from emp2 inner join dep2 on emp2.dep_id=dep.id group by post having avg(age)>30;
# 2.select name from dep2 where id in
# (select dep_id from emp2
# group by dep_id)
# having avg(age)>30);
# select关键词执行优先级
# 语法：select distinct (查询的字段) from 左表 <join type> join 右表
# on (连表条件)
# where
# group by
# having
# order by
# limit
# 优先级：from--->on--->join type--->where--->group by--->having--->distinct--->order by--->limit
# 子查询：
# 定义：就是把一条sql语句放到一个括号里面，当做另外一条查询语句的查询条件
# 可以把select查询的临时表内容用括号括起来，起一个别名，临时表就不会在打印后消失，仍然可以通过别名使用
# 例：(select name,age,sex from emp2) as t1;
# 可以包含：in---not in---any---all---exists---not exists 等关键字
# = != > < >= <=
# 查询平均年龄大于25岁的部门名
# select name from dep2 where id in
# (select dep_id from emp2
# group by dep_id
# having avg(age)>25
# );
# 查出技术部门员工的姓名
# select name from emp2 where dep_id=
# (select id from dep2 where name='技术');
# 查出人数不足1人的部门名
# select distinct name from dep2
# where id not in
# (select dep_id from emp2);
# 查询大于平均年龄的员工信息
# select * from emp2 where age >
# (select avg(age) from emp2);
# exists的使用：（判断是否有结果，返回布尔值）
# 查询是否有技术部门，有的话就打印员工表
# 还可以使用not exists
# select * from emp2
# where EXISTS
# (select id from dep2 where name='技术');
# 通过as 别名 来保存临时表，可以多个临时表之间互相连接，可以临时表与主表互相连接，随意玩
# 需求：获取每个部门最新入职的员工
# select * from emp as t1
# inner join
# (select post,max(hire_date) as max_hire_date from emp group by post) as t2
# on t1.post=t2.post
# where t1.hire_date=t2.max_hire_date;
# 小练习（跨4表）
# 查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
# select
# *
# from
# student
# where
# sid = (
# select
# student_id
# from
# score
# where
# corse_id in (
# select
# cid
# from
# course
# where
# teach_id=(
# select
# tid
# from
# teacher
# where
# tname='李平老师')
# )
# group by
# student_id
# order by
# avg(number) desc
# limit
# 1
# )
# 权限管理
# 作用：控制用户对库、表、字段的权限
# 操作：1.创建账号 （本地账号）
# create user'用户名'@'localhost' identified by '密码';
# mysql -u用户名 -p密码
# 操作：1.创建账号 （远程账号）
# create user'用户名'@'客户端IP地址' identified by '密码';
# 举例：（单机登陆）create user'用户名'@'192.168.0.101' identified by '密码';
# 举例：（多机登陆）create user'用户名'@'192.168.0.%' identified by '密码';
# 举例：（全网登陆）create user'用户名'@'%' identified by '密码';
# mysql -u用户名 -p密码 -h服务端IP
# 2.授权
# 创建账号加授权：
# 1.grant all on *.* to '账号'@'%' identified by '密码';
# 2.flush privileges; # 刷新权限表
# 授权操作只能以root用户登录来进行
# 权限表有4个：user db tables_priv columns_priv (控制力度依次降低)
# user: *.* 对所有库都开放权限（user不放权不代表就没有用了，还可以降低力度限制下面三个）
# db: 库名.* 对具体库开放权限
# tables_priv：库名.表名
# columns_priv：库名.表名.字段1，字段2
# 开放权限：
# grant all on *.* to '账号'@'loaclhost'; # 对账号开通所有库的所有权限
# grant select on *.* to '账号'@'loaclhost'; # 对账号开通所有库的select权限
# grant select on db1.* to '账号'@'loaclhost'; # 对账号开通db1库下面所有表的select权限
# 回收权限：
# revoke all on *.* from '账号'@'loaclhost'; # 回收账号select 所有库的权限
# revoke select on *.* from '账号'@'loaclhost'; # 回收账号select 所有库的权限
# revoke select(id,name),update(id) on db1.test from '账号'@'loaclhost';
# 回收账号查询db1库test表的查询id字段name字段 修改id字段的权限
# Navicat工具
# 这个工具以图形界面的方式来操作mysql
# ctrl加问号可以注释mysql执行语句 ctrl加shift解除注释
# 备份操作：右键库或者表，选择转储，选择保存路径
# 运行备份：选中库，右键选择运行sql文件，然后选中备份就OK
# python中使用mysql
# 模块：pymysql
# '''
# 模拟远程登录数据库读取用户信息进行登录验证
# 账号：testZH
# 密码：123
# '''
# import pymysql
# def check_userinfo(username,password):
# # 1.建立连接
# conn = pymysql.connect(
# host='192.168.1.105',
# port=3306,
# user='testZH',
# password='123',
# db='db1',
# charset='utf8'
# )
# # 2.拿到游标 cmd里面的'mysql >' 光标
# cursor = conn.cursor()
# # 3.执行ssql语句
# sql = 'select * from userinfo where username=%s and password=%s'
# rows = cursor.execute(sql, (username,password))
# 这里不是拿到查询的结果，而是拿到受影响的记录个数(一定用这种方式传值)
# cursor.close() # 关闭游标
# conn.close() # 关闭连接
# # 4.判断结果
# return rows
# def InPut():
# while True:
# username = input('---> ').strip()
# password = input('---> ').strip()
# if not username or not password: continue
# return check_userinfo(username,password)
# if __name__ == '__main__':
# res = InPut()
# print('登陆成功！') if res else print('登陆失败！')
#
# pymysql模块之sql注入
# 上例中存在漏洞，如果用户输入：username = 随便'--随便，然后不输入密码也可以登陆
# select * from userinfo where username="alex" -- sadasdasd" and password=""
# 登陆成功！
# 这是因为在sql语句中 空格--空格 表示注释，后面写什么都行，那么只要username存在，它就返回信息了。
# 还可以写成 xxxx" or 1=1 -- xxxxxxxxxxx
# 这样连用户名都不要就可以登陆！
# 所以想要拼接sql语句，一定要按照下面这样的方式来对语句进行传值
# sql = 'select * from userinfo where username=%s and password=%s'
# 正常进行拼接，但是不要把占位符%s用引号引起来
# rows = cursor.execute(sql, (username, password))
# 通过内置的游标执行函数来传值进行拼接，让pymysql模块来完成这个任务（去掉这种特殊字符）
# pymysql模块之记录增删改查
# '''
# 通过python对mysql进行增删改查
# 账号：testZH
# 密码：123
# '''
# import pymysql
# # 1.建立连接
# conn = pymysql.connect(
# host='192.168.1.105',
# port=3306,
# user='testZH',
# password='123',
# db='db1',
# charset='utf8'
# )
# # 2.拿到游标 cmd里面的'mysql >' 光标
# # cursor = conn.cursor() # 这个游标默认记录为元祖格式
# cursor = conn.cursor(pymysql.cursors.DictCursor) # 此时用游标获取的记录就为字典格式了
# # 3.执行ssql语句
# # 3.1 增、删、改
# # sql = 'insert into userinfo(username,password) values(%s,%s)'
# # rows = cursor.execute(sql, ('rain', 'abc123')) # 插入单条记录的操作
# # rows = cursor.executemany(sql, [
# # ('王大锤', 'abc123'),
# # ('张铁蛋', 'abc123'),
# # ('李钢炮', 'abc123')]) # 插入批量记录的操作
# # 在增记录的操作中，一直没有管ID字段，每次新增人员后都不知道ID现在到几了
# # 可以通过cursor.lastrowid 来查询： print(cursor.lastrowid) 这个打印是显示插入操作之前ID跑到几了
# # 3.2 查
# # 游标只能返回受影响的记录个数，并不能返回信息
# rows = cursor.execute('select * from userinfo;')
# # 想要获得查询的结果，需要用到游标下得fetch函数,结果返回为元祖格式: (1, 'alex', 'abc123')
# # 如果想获取成字典格式，让数据前面带上字段名便于查看，需要在获取游标时变动
# # 字典格式：{'id': 1, 'username': 'alex', 'password': 'abc123'}
# # 1.fetchone # 一次取一行，超出了就返回None
# # print(cursor.fetchone())
# # 2.fetchmany # 传值需要的个数，按照个数显示
# # print(cursor.fetchmany(2))
# # 3.fetchall # 取出所有
# print(cursor.fetchall())
# # 在第一次执行fetchall操作后，如果再执行一次就返回了一个空列表，因为游标移到表末尾了
# # 如果需要移动光标，需要用到scroll函数：sursor.scroll(步长,mode='模式')
# # 1.绝对位置移动(从头开始数3条记录，移动到那里)
# cursor.scroll(3, mode='absolute')
# # 2.相对位置移动（从当前位置往后数3条记录，移动到哪里）
# cursor.scroll(3, mode='relative')
# # 4.提交操作(增删改操作一定要有这一步，不然不会生效)
# conn.commit()
# # 5.关闭游标
# cursor.close()
# # 6.关闭连接
# conn.close()
# mysql内置功能介绍
# 1.视图（虚拟表）
# 需求：保存一张已经查询好的临时表，方便以后使用时不用重复的敲这个查询语句
# select * from course
# inner join teacher
# on course.teach_id=teacher.tid;
# 这条语句查询出了课程表对应老师表，但是想要把它保存下来
# create view course2teacher as # 创造一个虚拟表课程to老师，按下面查询结果这样的
# select * from course
# inner join teacher
# on course.teach_id=teacher.tid;
# 此时就造出了这个虚拟表，不会存入硬盘，只存在于内存，退出就消失，但是show tables可以看到
# 可以直接把他当做正常的表来操作 select * from course2teacher;
# 如果视图涉及单张表，则可以修改记录，如果涉及多表，就不可以修改了，只能查询
# 修改视图：alter view 视图名 as 新的sql查询语句
# 删除视图：drop view 视图名
# 触发器
# mysql中主要是为增删改定制触发器（一碰就动）
# 以增操作为例展示语法：
# before insert ...... begin 在之前插入
# create trigger 触发器别名 before insert on 表名 for each row begin 触发机制
# after insert ...... begin 在之后插入
# create table cmd(
# id int not null unique auto_increment,
# user char(32),
# priv char(10),
# cmd char(64),
# sub_time datetime, #命令错误时间
# success enum('yes','no') #判断是否成功
# );
# create table errlog(
# id int not null unique auto_increment,
# err_cmd char(64),
# err_time datetime
# );
# 逻辑：插入cmd，如果执行状态为不成功（NO）
# 则需要把这条命令插入到errlog表中
# 动作：针对cmd表的插入动作，在插入后判断如果符合条件就执行触发器
# 创建触发器
# delimiter //
# create trigger tri_after_insert_cmd after insert on cmd for each row
# begin
# if new.success='no' then
# insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
# end if;
# end //
# delimiter ;
# 触发器提供了两个对象，一个是new一个是old，new表示新增进来的记录，old代表之前的旧记录
# 修改的情况下才用old
# delimiter表示定义结束符号，因为语句中的逻辑关系问题需要用到分号，所以不能用分号来结束
# 语句，重新定义一下结束符号为任何自定义的符号，语句结束后一定要想着把结束符号还原
# 删除触发器：drop trigger tri_after_insert_cmd;
# 查看触发器：show create trigger tri_after_insert_cmd;
# 存储过程
# 创建一个存储过程：操作哪一个数据库，就应该把存储过程建到哪一个数据库下面
# delimiter // # 1.自定义结束符号 //
# create procedure p1(参数) # 2.创建一个存储过程，起名叫p1()
# begin # 3.开始
# select * from db1.userinfo; # 4.操作：打印表记录
# end // # 5.结束
# delimiter ; # 6.切换结束符
# 创建完了存储过程，然后就可以调用了
# 1.在mysql中调用
# call p1(); 此时调用这条指令，就会直接执行封装的查询userinfo表语句，打印表记录
# 2.在python中调用
# cursor.callproc('p1') 跟上面一样
# import pymysql
# conn = pymysql.connect(
# host='192.168.1.105',
# port=3306,
# user='testZH',
# password='123',
# db='db1',
# charset='utf8'
# )
# cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor.callproc('p1')
# print(cursor.fetchall())
# conn.commit()
# cursor.close()
# conn.close()
# ===========================================================
# 上例中并没有对存储过程传参数，下面实现一个带参数的
# 有两个注意点：1.必须明确参数的类型 2.必须明确参数是用来接收值的还是用来返回值的
# delimiter //
# create procedure p2(in n1 int,in n2 int,out res int)
# #in表示n1、n2这个参数是需要传入的，类型为int
# #out表示res这个参数是需要传出的
# #还有第三种情况，inout 表示这个参数可进可出
# begin
# select
# *
# from
# db1.userinfo
# where
# id>n1 and id<n2;
# set res=1; #设置返回参数res=1
# end //
# delimiter ;
# 在mysql中调用带参数的存储过程
# set @x=0;
# call p3(2,4,@x);
# 在python中使用：cursor.callproc('p3', (2, 4, 0))
# pymysql模块自动转化sql语句，这里面的三个参数的形式分别为：
# @_p3_0=2 @_p3_1=4 @_p3_2=0
# 此时如果想查询返回值：
# cursor.execute('select @_p3_2')
# print(cursor.fetchone())
# 应用程序与数据库结合使用的三种方式
# 1.
# python：调用存储过程
# mysql：编写存储过程
# 2.
# python：编写纯生sql语句
# mysql：不干活
# 3.
# python：基础ORM框架来编写类，实例化对象，由ORM转换成纯生SQL语句
# mysql：
# 事务
# 定义：就是把一堆sql语句放在一起，要么同时执行成功，要么同时失败
# 场景：转账操作，买方---中介---卖方
# 需求：买方支出100，中介收入10，卖方收入90
# start transaction; 1.开启事务
# 修改买方的账户余额，支出100元 2.执行事务
# 修改中介的账户余额，收入10元
# 修改卖方的账户余额，收入90元
# rollback; 3.回滚（出现异常可以回滚）
# commit; 4.提交事务
# 完整示例:
# delimiter //
# create procedure p4(out p_return_code int)
# begin
# declare exit handler for sqlexception #定义一个异常处理检测
# begin
# -- error #注释：针对错误的异常
# set p_return_code = 1; #设定返回值为1
# rollback; #回滚
# end;
# declare exit handler for sqlwarning #定义一个异常针对警告信息
# begin
# -- warning
# set p_return_code = 2; #设定返回值为2
# rollback;
# end;
# start transaction; #开启事务
# delete from t1 #删除t1表
# insert into blog(name,sub_time) values('xxx',now()) #对blog表插入记录
# commit;
# -- success #注释：操作成功
# set p_return_code = 0; #设置返回值为0
# end //
# delimiter ;
#
# #在mysql中调用储存过程
# set @res=123; #设置参数为123
# call p4(@res); #调用存储过程，并传入参数
# select @res; #查看返回结果
# 函数与流程控制
# 函数、流程控制、视图、触发器这些都是可以封装到存储过程里面。
# 重点介绍函数：
# 场景：写的博客都有博客名和提交时间 （'第一篇博客','2018-08-13 11:31:05'）
# 需求：格式化展示博客名和提交时间
# select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m')
# 从blog表按照date_format函数（提交时间，获取格式为年-月）分组，展示年-月格式并统计个数
# 打印效果就像： 2018-03 5
# 2018-04 3
# 2018-04 4
# 索引原理
# 创建索引：create index 索引名 on 表名(字段名);
# 现在查询就快多了
浙公网安备 33010602011771号