MySQL数据库

MySQL_Lesson_1

1.数据库

数据库是用来存储数据的,数据是不是直接存储在数据库中?

不是的,数据库中还有一个结构,叫做表,表中中存储的才是数据

我们要学习的:数据库的连接,数据库的创建,表的创建,数据的管理,优化,便捷操作,关系的理解

2.数据的发展历史

2.1萌芽阶段

所有的的数据都是存储在文件中的,安全性低,操作性繁琐.

2.2层次模型

1.优点:查询分类的效率比较高

2.缺点:1.没有导航结构,导致分类困难

    2.数据不完整

注意:数据的不完整,如果不能准确的分辨两条数据有什么不同,称之为失去了’数据的完整性’

2.3网状模型

网状模型:没有解决导航问题,解决了数据的不完整性.

2.4关系模型

现在的主流数据库都是关系模型的.

特点:1.每张表都是独立的,没有导航结构

 2.表于表之间会建立公共字段,也就将两张表之间建立了关系

注意:公共的字段名可以不一样,但是数据类型必须相同(数据类型相同的不一定是公共字段),两个字段的含义必须也要一致.

关系型数据库,解决了数据的完整性,也解决导航问题,但是带来的是低效率.

NOSQL(非关系型数据库):MongoDB,Redis

3.列,行(字段,记录)(表属性,数据),字段的属性

1.一行就是一条记录也是一条数据(在数据科学中,被称之为一个样本)

2.一列就是一个字段,也是表的一个属性

3.字段的属性:是用来描述这个列的功能

4.数据的冗余:相同的数据不要多次存储

1.冗余只能减少,不能杜绝.

2.冗余减少了,表的体积就小了,更新的速度提高了,保证了数据的完整性.

3.减少了冗余,表的数量一定会增加,多表查询的效率就下降了(在项目中,我们一般牺牲效率,保证完整性;但是在机器性能严重不足的时候,一定要保证效率)

4.数据的完整性

正确性 + 准确性 = 数据的完整性

分析:1.学生的年龄(age),数据类型代表的是正确性, 范围代表的就是准确性

2.手机号:字符串类型,长度(11个)

正确性:数据类型是否使用得当

准确性:描述的范围是否得当

5.Linux数据库的开启和连接

1.Ubuntu : service mysqld start|stop|restart|reload|status
2.CentOS7 : systemctl stop|start mysqld
3.CentOS6 : service mysqld start|stop|restart|reload|status
4.Deepin : systemctl stop|start mysqld

#连接数据库都一样

语法:mysql -hloaclhost  -uroot -p123456 -P3306
1. -h : host(ip地址)   localhost = 127.0.0.1
2. -u : username(用户账户)
3. -p : password(密码)
4. -P : port(端口,默认端口3306)

#退出
1.exit
2.quit
3.\q

密码忘记怎么办?

#需要修改配置
vim  /etc/my.cnf

#找到[mysqld]
skip-grant-tables

#修改完重新启动

SQL语言

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

关系型数据库

数据库 SQL类型 公司
access SQL 微软
SQL-server T-SQL 微软
Oracle PL/SQL 甲骨文
MySQL My/SQL 甲骨文
SQL-Lite 内嵌型小型数据库 移动前端用的比较多

思考题:

#已知SQL是标准的SQL,Oracle编写的PL/SQL能不能爱Mysql上运行?
答:可以

数据库的操作

• 创建数据库

  create database [if not exists] 数据库名 charset=字符编码(utf8mb4);

  1.如果多次创建会报错
  2.字符编码不指定默认utf8mb4
  3.给数据库命名一定要习惯性加上反引号,防止和关键字冲突

• 查看数据库

  show databases;

• 选择数据库

  use 数据库的名字

• 显示建库语句

  show create database 数据库名;

• 修改数据库

  #只能修改字符集
  alter database 数据库名 charset=字符集;

• 删除数据库

  drop database [if exists] 数据库的名字;

表的操作

表示建立在数据库中的

• 表的创建

  create table [if not exists] 表的名字(
  id int not null auto_increment primary key comment ‘主键’,
  account char(255) comment ‘用户名’ default ‘admin’,
  pwd varchar(65535) comment ‘密码’ not null
  )engine=myisam charset=utf8mb4;

  #字符集如果不指定,默认继承库的字符集.
  #engine 默认innodb

• 查看所有的表

  #必须先选择数据库
  show tables;

• 表的引擎

  #innodb和myisam
  #CURD操作:增删改查
  #C create insert 插入
  #U update 修改
  #R read select 查询
  #D delete 删除
  #less /etc/my.cnf
  #默认的存储路径
  #datadir = /data/mysql

  innodb 在 写的操作上非常的有优势(事物) CUD全是写的操作 5%
  myisam 在 读的操作上非常的有优势(健全的索引) R操作 95%

  #引擎的存储方式
  myisam将一张表存储为三个文件
  demo.frm -> 表的结构
  demo.MYD -> 存储的是数据
  demo.MYI -> 存储的是表的索引
  #myisam的文件可以任意的移动

  innodb将一张表存储为两个文件
  demo.frm -> 表的结构+表的索引
  demo.ibd -> 存储的是数据
  ibd存储是有限的,存储不足自动创建ibd1,ibd2
  #innodb的文件创建在哪个数据库中,不认任意的移动

• 删除表

  #删除表必须在数据库中进行删除
  drop table [if exists] 表名

• 显示建表结构

  show create table 表的名字\G

  #另外一种方式,显示的是表的属性
  desc 表名;
  describe 表名;

• 修改表

  #1.修改表的名称
  alter table old_name rename new_name;

  #2.增加一个新的字段
  alter table table_name add field_name 数据类型 属性;
  #将某个字段添加在第一个位置
  alter table table_name add field_name 数据类型 属性 first;
  #添加在某一个字段之后
  alter table table_name add field_name 数据类型 属性 after 指定字段;

  #修改字段的属性
  alter table table_name modify 字段名 数据类型 属性;

  #修改字段的名称
  alter table 表名 change 原字段名 新的字段名 数据类型;

  #修改字段的位置
  alter table 表名 change 原字段名 新的字段名 数据类型 after 指定字段;

  #修改表的引擎
  alter table 表名 engine = innodb|myisam;

  #移动表 到指定的数据库
  alter table 表名 rename to 数据库名.表名;

复制表

create table abc(
    id int primary key auto_increment comment '主键',
    username char(32) not null comment '账户',
    password char(32) not null comment '密码'
)engine=myisam;

insert into abc values(null,'admin',md5(123456)),(null,'admin1',md5(123456));

1.create table `表名` select * from `要被复制的表名`;
特点:把数据给复制过来了,但是没有复制主键

2.create table `表名` like `要被复制的表名`;
特点:复制所有表结构,但是不复制数据
#数据可以单独复制
insert into `表名` select * from `要被复制的表名`;

sql语句的基本使用

insert(插入)

#主键字段不用我们考虑
#not null 的字段,说明一定要输入数据

#一次插入一行
insert into `表名` set `字段`=值,`字段`=值;


#一次插入多行
insert into `表名`(字段1,字段2....)  values(值1,值2...),values(值1,值2...);

insert into `表名` values(null,值1,值2....),values(null,值1,值2....);

select(查询)

#*的位置一个结果集
#* 代表所有的字段名
select * from `表名`;
select 字段1,字段2 from `表名`;

update(更新)

update `表名` set `字段名`=值,`字段`=值;

#在更新的时候一定要加上where条件
#where相当于if条件,只执行返回结果为True的语句
update `表名` set `字段名`=值,`字段`=值 where `字段`=值;

update `表名` set `字段名`=值,`字段`=值 where `字段`=值 and `字段`=值;

delete(删除)

#删除表中的所有数据
delete from `表名`;

#在删除的时候一定要加上where条件
#where相当于if条件,只执行返回结果为True的语句

delete from `表名` where `字段` = 值;

delete from `表名` where  `字段` in (1,2,3,4);


#一旦数据被删除,再次插入数据,自增长的列的记录值,从从之前表自增长列最大值的下一次开始.
#在开发中,真实数据是无价的,数据是不会被删除
#数据保留位置,是为了数据恢复准备的.


#删除表在重建=>清空表(在开发的时候会经常使用)
truncate `表名`

MySQL_Lesson_2

1.字符集

字符集在什么时候可以发挥作用?

1.保存数据的时候需要使用字符集

2.数据传输的时候也需要使用字符集

• 在存续的时候使用字符集

  1. 在MySQL的服务器上,在数据库中,在表的使用上,在字段的设置上.
  2. 在服务器安装的时候,可以指定默认的字符集

  #mysql中创建一个远程连接的用户并且授权
  #root不可以执行远程连接
  grant all privileges on . to ‘admin’@’%'identified by ‘123456’ with grant option;

• gbk,utf8,utf8mb4的一个区别

  gb2312:简体中文字符,一个汉字最多占用2个字节
  gbk:只是所有的中文字符,一个汉字最多占用2个字节
  utf8:国际通用编码,一个汉字最多占用3个字节
  utf8mb4:国际通用编码,在utf8的基础上加强了对新文字识别,一个汉字最多占用4个字节

  #65535/2 -1
  create table test(
  text varchar(32766)
  )charset=gbk;

  #65535/3 -1
  create table test1(
  text varchar(21844)
  )charset=utf8;

  #65535/4 -1
  create table test2(
  text varchar(16382)
  )charset=utf8mb4;

• 在传输的时候使用字符集

  #查看当前mysql系统支持的字符集
  show variables like ‘character_%’;

  ±-------------------------±---------------------------------+
  | Variable_name | Value |
  ±-------------------------±---------------------------------+
  | character_set_client | gbk #服务器接收终端传输的编码格式 |
  | character_set_connection | gbk |
  | character_set_database | utf8mb4 #数据库支持的编码 |
  | character_set_filesystem | binary |
  | character_set_results | gbk #服务器返回的字符编码 |
  | character_set_server | utf8mb4 #mysql服务器支持的编码 |
  | character_set_system | utf8 |
  | character_sets_dir | /usr/local/mysql/share/charsets/ |
  ±-------------------------±---------------------------------+

  #修改当前的mysql系统的字符集编码的
  #它是一次性命令,mysql链接断开以后,再次链接就不好使了
  set names gbk;

  #修改系统的字符编码
  set character_set_client = gbk;
  set character_set_results = gbk;

2.校对集

在某一种字符集下,让字符和字符形成一种关系的集合称之为校对集,

比如说ASCLL中的a和B,如果区分大小写a>B,如果不区分a<B;

#数据库默认的排序方式,是升序
create table t1(
	str char(1)
)charset=utf8mb4 collate=utf8mb4_general_ci;
#在排序规则上看到带有_general_ci 尾缀的都是不区分大小写的

create table t2(
	str char(1)
)charset=utf8mb4 collate=utf8mb4_bin;
#看到尾缀边是_bin的都是区分大小的

#Linux中Mysql是区分大小的
#需要自己去配置
#vim /etc/my.cnf
#找到[mysqld]
#1是不区分大小写,0是区分大小写
lower_case_table_names=1

#查看字符集+校对集
show character set;

#显示所有的校对集
show collation;

3.MySQL的数据类型–值的类型

(1)整型

类别 占用字节 范围
tinyint 1 -2^7-27-1 [-128~127]
smallint 2 -2^15-215-1 [-32768-32767]
mediumint 3 -2^23-223-1
int 4 -2^31-231-1 [长度为10]
bigint 8 -2^63-263-1

(2)unsigned(无符号)

一个数是无符号的数,那么这个数一定是非负数

#0-255  0-2^8-1
tinyint unsigned 

create table t3(
	age tinyint unsigned
);

(3)显示宽度(zerofill)

整型支持显示宽度

create table t4(
	id int(10) zerofill primary key auto_increment,
	name char(32)
);

(4)浮点型

类型 占用字节 范围
float 4 -3.4E+38 ~3.4E+38
double 8 -1.8E+308 ~1.8E+308

(5)定点数

#定点数的位数更加长
#M是支持多少个长度,D小数点后面的位数
float(M,D)
double(M,D)
decimal(M,D)

create table t5(
	a float(10,2),
	b double(10,2),
	c decimal(10,2)
);
#五舍六如

4.字符串类型

类型 描述
char 定长(255)
varchar 可变长度(65535)
tinytext 定长(255)
text 定长(65535)
mediumtext 2^24-1
longtext 2^32-1

5.枚举(enum)

多选一的时候使用的一种数据类型

在前端使用单选框的时候,枚举类型可以发挥作用

create table t6(
	name varchar(32),
	sex enum('男','女','保密')
);
#枚举类型的下标默认从1开始
insert into t6 set name='王宝强',sex=1;

#枚举类型的优点:
1.限制值
2.节省空间
3.运行效率高

6.集合(set)

在前端是复选框的时候,也不用

有多少可以选多少的一种数据类型

create table t7(
	name varchar(32),
	hobby set('吃','睡','玩','喝','抽')
);

insert into t7 values('孙婷挣','睡,抽,玩,吃,喝');
insert into t7 values('孙挺正','睡,抽');

#索引计算方式
第一个字:2^0
第二个数:2^1
第三个数:2^2

#为什么不是用set类型?
应为在现代网站开发中,多选框的值有上千个,值存储的空没有索引用的多
#那复选框的问题怎么解决?
将复选框的值单独设计成一张表

7.时间类型

类型 描述
date 日期
year 年份
time 时间
datetime 日期和时间
timestamp 时间戳

1.datetime

create table `datetime`(
	create_at datetime
);

insert into `datetime` values('2019-4-2 16:54:00');
insert into `datetime` values('2019/4/2 16:54:00');
insert into `datetime` values(now());
#年份最大支持4个长度
insert into `datetime` values('10000/4/2 16:54:00');  #错误
insert into `datetime` values('9999/4/2 16:54:00');

2.time

create table `time`(
	create_at time
);

insert into `time` values('12:12:12');
insert into `time` values('100:12:12');
insert into `time` values('-100:12:12');
insert into `time` values('10 10:12:12');
#-838:59:59 - 838:59:59
insert into `time` values('839:12:12');#错误的

3.timestamp

#时间戳类型,时间戳类型在显示方面和datetime是一样的,在存储上是不一样

create table `timestamp`(
	create_at timestamp
);

#1970-1-1 0:0:0的格林尼治  - 2038-1-19 11:14:07
insert into `timestamp` values(now());
insert into `timestamp` values('2038-1-19 11:14:07');
insert into `timestamp` values('2038-1-19 11:14:08');#错误

4.year

create table `year`(
	create_at year
);

#从1900年开始 - 1900+255

insert into `year` values(now());
insert into `year` values('2155');
insert into `year` values('2156'); #错误

8.布尔型

mysql中的bool类型也是1和0

create table `bool`(
	cond boolean 
);

insert into `bool` set cond=True;
insert into `bool` set cond=False;
insert into `bool` set cond=1;
insert into `bool` set cond=10;
insert into `bool` set cond=-1;
insert into `bool` set cond=0;
insert into `bool` set cond=0.1;
insert into `bool` set cond='True'; #错误
#2分类

9.列的属性

• not null | null

  #插入的值是否可以为空
  null:是可以为空,默认不写
  not null:不可以为空,如果插入的时候,摸个字段的值为空,则报错

  create table null(
  id int primary key auto_increment,
  username varchar(32) not null,
  pwd varchar(16) null
  );

  insert into null values(null,null,null);

• default

  #默认值一般是和null做搭配的
  create table default(
  id int primary key auto_increment,
  username varchar(32) default ‘admin’ not null,
  pwd varchar(16) default 123456
  );

  insert into default(username) values(‘admin’);

• auto_increment

  #自动增长的列
  默认从1开始

  配合主键使用的

  create table auto(
  id int primary key,
  username int auto_increment
  );

• primary key

  #主键一般是唯一的标识
  #特性:不能为空,也不能重复,一张表当中只可以拥有一个主键

  create table primary(
  id int,
  sid int,
  primary key(id,sid)
  );
  #这里只有一个主键,这种主键叫做联合主键,在项目中不用

• unique

  #唯一键,保证列当中的每一个数据都不重复
  #邮箱不可以重复,手机号不可以重复
  create table unique(
  #uid是个什么…
  uid int auto_increment primary key,
  mobile char(11) unique
  );

  insert into unique set mobile=13999999999;

• comment

  #注释:给程序员看的,解释说明的作用
  #源码解释

  #状态
  status tinyint commnt ‘0代表普通用户,1代表普通会员,2高级会员’

10.SQL注释

#python
单行:#
多行:'''

'''

#mysql
单行注释:#
单行注释:--
多行:/*文字*/





create table userinfo(
	uid int primary key auto_increment,
	uname char(32),
	pwd char(16)
);

insert into userinfo(uname,pwd) values('admin',123),('admin1',1234),('admin2',12345),('admin3',123456);


##sql注入
select * from userinfo where uname='admin' and pwd ='123';

select * from userinfo where uname='' or 1=1 --'' and pwd ='123';
select * from userinfo where uname='' or 1=1 #'' and pwd ='123';
select * from userinfo where True;

#数据库穿透
#前端正则,后端正则
select * from userinfo;

MySQL_Lesson_3

1.数据的完整性

1.实体的完整性,一条记录,就是一个实体,如果记录无法区分,则失去了实体的完整性
2.域完整性:如果有两个字段无法区分,则失去了域完整性
3.引用的完整性:两个表的对应记录不完整,则失去了引用完整性
4.自定义完整性:自己定义的一套规则

(1).保证实体的完整

1.主键的约束(primary key)
2.自动增长的列(auto_increment)
3.唯一键(unique)

(2).保证域的完整性

1.数据类型的约束
2.默认值(default)
3.非空约束(not null)

(3).引用的完整性

应用外键(foreign key)

(4).自定义完整性

1.存储过程(相当于python中的自定义函数)
2.触发器

2.外键

外键:从表的公共字段

外键的约束主要是用来保证引用的完整性的,主外键的名字可以不一样,但是数据类型可以一样.

#特点
1.主表中不存在的记录,从表中不能插入
2.从表已存在的记录,主表中不能删除
3.先删除从表,再删除主表

#学生表
create table stuinfo(
	id int primary key auto_increment,
	name char(32) not null
);

insert into stuinfo set name='孙挺正';
insert into stuinfo(name)  values('周健鹏'),('李野');
insert into stuinfo set name='李嘉俊';

#成绩表
	#foreign key(本表的外键) references 主表(关联字段)
	#foreign key(stuno) references stuinfo(id)
	#cascade 联动操作
create table score(
	nid int primary key auto_increment,
	stuno int ,
	ch float,
	math float
	#foreign key(stuno) references stuinfo(id) on delete cascade on update cascade
);

insert into score values(null,1,100,100),(null,2,100,100),(null,3,100,100),(null,4,100,100);


#两种串联的操作:
1.set null: 让一个字段设置为NUll
2.cascade : 跟着主表的变化而变化


#添加外键
alter table score add foreign key(stuno) references stuinfo(id) on delete cascade on update cascade;
#添加外键,并指定外键的名称
alter table score add CONSTRAINT `stuno` FOREIGN KEY (`stuno`) REFERENCES `stuinfo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

#删除外键
#score_ibfk_1 外键的名字,外键可以有多个
alter table score drop foreign key score_ibfk_1;

#外键只能在innodb的引擎上使用

3.实体之间的关系

实体的关系:

1.一对一
2.一对多
3.多对一
4.多对多

(1).一对一:主键关系

stuinfo

stuno(学号) name(姓名)
1 王健林
2 许家印

stuno(期末考试) score
1 100
2 120

(2)一对多|多对一

user

uid account pwd email
1 admin 123456 123@qq.com
2 root 123456 126@126.com

order(订单)

oid(订单的编号) uid gid create_at
201904037000001 1 7000 2019/04/03
201904047000001 1 7000 2019/04/04

(3)多对多

user

uid account pwd email
1 admin 123456 123@qq.com
2 root 123456 126@126.com

address

address uid mobile name
上海徐汇区1208弄A小区3栋1608 1 13555555555 马云
上海徐汇区1208弄A小区3栋1608 1 13666666666 雷军
上海徐汇区1208弄B小区3栋1608 1 13777777777 吴军
上海徐汇区1208弄B小区3栋1608 1 13888888888 李斌
上海徐汇区1208弄A小区3栋1608 2 13555555555 马云
上海徐汇区1208弄A小区3栋1608 2 13666666666 雷军

4.数据库的设计

公司要做一个项目,首先项目管理获得需求,知道项目是什么类型的,然后产品经理负责产品的规划,设计原型

UI将需求的草图给UI,UI可以绘制E-R图,或者是DB自己构建E-R图

DB自己根据E-R图设计数据库,建立表,设定关联度.

码农看到E-R图可以干嘛,我们根据E-R图上的需求写代码

• E-R图

  E-R图是描述实体和实体之间的关系的

  语法:
  1.矩形代表实体
  2.椭圆形代表实体拥有的属性
  3.菱形代表实体之间的关系

博客的E-R图:

#用户和板块之间的关系
1.某个用户是版主,版主管理板块
2.普通用户和版块之间没有直接的关系,用户发帖或者用户评论间接的和版块之间形成关系

#用户和帖子之间的关系
1.用户发表了帖子
2.用户评论了某个帖子

#用户和评论之间的关系
1.用户发表了评论
2.用户发表了帖子,被其他人评论了
3.如果有二级评论,你的评论被人喷了

#帖子和版块之间的关系
帖子属于版块

4.数据的规范

(1)第一范式

第一范式:确保每一列原子化(不可分割)

(2)第二范式

第二范式:,基于第一范式,一张表只能描述一件事情,非主键字段必须依赖主键字段(不论在什么情况下主键都是唯一的)

(3)第三范式

第三范式:基于第二范式,消除传递依赖(一个主键字段可以确定其它的信息)

5.规范化和性能

高考成绩查询系统:高并发

不符合三范式

stuno(考号) 姓名 语文 数学 总分
1 小明 130 120 250

select * from gaokao where stuno=1;

规范化:

stuno(考号) 姓名
1 小明
2 小强

stuno(考号) 语文 数学
1 130 120

select *,ch+math as score from A left join B on A.stuno = B.stuno where A.stuno = 1;

总结:性能的完备良好的时候,选择规范化;性能不足,优先考虑性能

7.查询语句

所有的查询都依赖统计分析

select 字段(结果集) from 表名(数据源) [where 条件] [group by 分组][having 条件][order by 排序 asc|desc][limit 限制 m,n]

create table stuinfo(
	sid int primary key auto_increment comment'学号(主键)',
    sname varchar(32) not null comment'姓名',
    sex enum('男','女','不详') default '不详',
    age tinyint unsigned not null comment'年龄',
    city varchar(64) comment'地级市'
);

create table score(
	stuno int not null comment'学号',
    python float,
    java float
);

insert into stuinfo values(null,'挺正',1,18,'重庆'),(null,'李野',1,60,'北京'),(null,'劲宇',1,81,'深圳'),(null,'杨幂',1,18,'重庆'),(null,'赵薇',1,20,'北京'),(null,'迪丽热巴',1,18,'深圳');

insert into score values(1,88,99),(2,78,100),(3,30,60),(4,100,99),(5,70,69),(6,100,0);

(1)字段表达式

select 既可以做查询,也可以做输出

select now();  #显示当前时间
select rand();   #随机数
select unix_timestamp(); # 显示Unix时间戳

(2)from子句

from 后面是数据源
数据源可以写多个,返回的是一个笛卡尔积
select * from A,B,C;

(3)dual表

dual是一个语法,是一个关键字

dual表示为了保证select完整性的

select now() from dual;

(4)where子句

where是做条件查询,只返回结果为True的数据

select * from stuinfo where age <50;

• is null | is not null

  where条件使用的比较运算符

  select * from score where java is null;
  select * from score where java is not null; #返回不为空的所有结果

• between | not between

  #between是where一个查询方式
  #查询某一个范围
  select * from stuinfo where age between 18 and 20;

  select * from stuinfo where age>=18 and age<=20;

(5)运算符

• 算术运算符

  • • • / % ++ –

• 比较运算符

  = > < >= <= != <>

• 逻辑运算符

  and 与
  or 或
  not 非

(6)聚合函数

max() #最大值
min() #最小值
sum() #求和
avg() #平均值
count() #计数

#聚合函数使用在结果集上

(7)通配符

_ #一次只匹配一个字符
% #一次匹配任意数量的字符

#在模糊查询的时候使用

(8)模糊查询

关键字:like
#like写在where后面

select * from stuinfo where sname like '_丽__';

select * from stuinfo where sname like '%丽%';

8.分组查询

将查询的结果分类显示,为了方便统计

group by,如果有where要放在where的后面

select * from stuinfo group by sex;


#mysql57默认不支持group by
#修改配置文件
#vim /etc/my.cnf

#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

select count(sid) from stuinfo group by sex;

#在group将需要的结果拼接
select group_concat(sid) from stuinfo group by sex;

#添加where语句
select group_concat(sid) from stuinfo where age>20 group by sex ;

9.回溯统计

在统计的基础上,在做一次统计
with rollup
添加在group by之后

select count(sid) from stuinfo group by sex with rollup;

10.having(条件)

select * from stuinfo where age>=20;
select * from stuinfo having age>=20;
select sid from stuinfo where age>=20;
select sid from stuinfo having age>=20;   #错误
select * from stuinfo where age>=20 having city='北京';
select * from stuinfo where age>=20 and city='北京';
select * from stuinfo having sum(age>=20);
select * from stuinfo where max(age);  #错误
select * from stuinfo group by sex where age >=20;  #错误的
select * from stuinfo group by age having age >=20; 


where:条件的查询,where后面不能加上聚合函数,只能写在.数据源的后面
having:条件查询,having条件字段必须要在结果集中,having可以写在group by的后面

11.order by

order by 写在 groupby后面 ,如果有having也要写在having的后面
#主要作用是排序
#拍讯分为升序asc 降序desc,默认asc(可以不写)

select * from stuinfo order by age;
select * from stuinfo order by age desc;

12.limit

#主要作用,限制数据的显示数量,limit位置放在最后

select * from stuinfo limit 3;   #显示前三行

#从索引为0开始,向后取3行
select * from stuinfo limit 0,3;
#从索引为3的开始,向后取3条
select * from stuinfo limit 3,3;

select * from stuinfo where city='北京' order by age desc limit 3; 

#distinct去除相同的字段值
#我们需要查询数据表中一共有哪些那些地方的人注册了
select distinct city from stuinfo;
select city from stuinfo group by city; 

13.插入语句的其它用法

#拷贝数据
insert....select.....
#复制表
create table stuinfo1 like stuinfo;

insert into stuinfo1 select * from stuinfo;

#插入重复值
on duplicate key update....

#如果主键已经存在,则不能覆盖
insert into stuinfo values(1,'tom',1,18,'大阪');

#如果不存在直接插入,如果存在则更新
insert into stuinfo values(7,'tom',1,18,'大阪') on duplicate key update sname='tom',city='大阪';

MySQL_Lesson_4

1.联合查询

关键字:union

将多个select语句的结果纵向拼接在一块

select * from stuinfo where sex=1 union select * from stuinfo where sex=2;

select * from stuinfo union select * from stuinfo1;

#union语句的使用范围?
数据库优化,分表,假设每一张最大存储限制是10w,需要复制一张表,复制的表的id从100001开始


#不同表的连接
select sname,sex,age from stuinfo union select * from score;

select * from score union select sname,sex,age from stuinfo;

union要求:
1.两边select语句的字段数必须一样
2.两边可以具有不同数据类型的字段
3.字段名默认按照左边的表来设置

#查找深圳的男生和北京的女生
select * from stuinfo where sex=1 and city='深圳' union select * from stuinfo where sex=2 and city='北京';
#不使用union
select * from stuinfo where (sex=1 and city='深圳') or (sex=2 and city='北京');



#男生的年龄降序排列 女生的年龄升序排列
#limit
(select * from stuinfo where sex=1 order by age desc limit 999999999) union (select * from stuinfo where sex=2 order by age asc limit 999999999);

2.多表查询

• 多表查询的分类

  1.内连接查询
  2.外连接查询
  3.交叉连接查询
  3.自然连接查询

• 内连接(inner join)

  #查询所有学生的信息加上成绩
  #innerjoin 连接以后,需要指定公共字段
  select * from stuinfo inner join score on stuinfo.sid = score.stuno;

  #as 是起别名,原先的名字不可以使用了
  select a.,b.python,b.java from stuinfo as a inner join score as b on a.sid = b.stuno;
  #as 可以省略不写,as也可以给结果集起别名
  select a.,b.python,b.java class from stuinfo a inner join score b on a.sid = b.stuno;
  #inner可以不写 ,直接写join默认就是内连接
  select a.*,b.python,b.java class from stuinfo a join score b on a.sid = b.stuno;

  内连接特性:
  关联表的数据不完整,默认不返回

• 外连接(outer join)

  #左外连接 left outer join
  #以左边表中的数据为主,如果右表中的数据不对应,则用Null补齐
  select * from stuinfo a left join score b on a.sid=b.stuno;

  #右外连接 right outer join
  #以右边表中的数据为主,如果左表中的数据不对应,则用Null补齐
  select * from stuinfo a right join score b on a.sid=b.stuno;

  #思考:
  select * from A inner join B on A.id = B.id;
  select * from B inner join A on B.id = A.id;
  #一样嘛?查询的结果是一样

  select * from A left outer join B on A.id = B.id;
  select * from B right join A on B.id = A.id;
  select * from A right outer join B on A.id = B.id;

  #left join可以拼接多少层?
  select A.,B.,C.* from A left join B on A.id =B.id left join C on B.id = C.id ;

• 交叉连接(cross join)

  #返回一个笛卡尔积
  select * from stuinfo cross join score;
  #但是,在mysql中被破坏了,cross join被添加上了一个on的功能
  #被破坏的cross join 和inner join的作用一样
  select * from stuinfo a cross join score b on a.sid=b.stuno;

• 自然连接(natural join)

  1.natural join 自然内连接
  2.natural left join 自然左外连接
  3.natural right join 自然右外连接
  #特点:
  1.可以自动判断连接的条件,依据的是同名字段
  2.如果没有同名字段,返回的是笛卡尔积
  3.自动返回整理好的结果
  a.连接的字段只返回一个
  b.连接的字段放在最前面

  select * from stuinfo a natural join score b;
  select * from stuinfo a natural left join score b;

• using函数

  select * from stuinfo a left join score b on a.sid=b.sid;

  #主要作用,是自动查找关联字段,依据的是同名字段,但是同名字段是自己制定

  select * from stuinfo a join score b using(sid);

• 练习

  #显示地区 和 每个地区参加java考试的人数,并且人数按照降序排列
  select a.city,count(b.java) count from stuinfo a join score b using(sid) group by a.city order by count desc;

  #显示男生和女生人数
  select sex,count(sex) from stuinfo group by sex;

  (select sex,count(sex) from stuinfo where sex=1) union (select sex,count(sex) from stuinfo where sex=2);

  select sum(sex=1) 男 ,sum(sex=2) 女 from stuinfo;

3.子查询

什么是子查询?

查询的语句中还有一个查询

外面的查询叫做父查询,里面的查询叫做子查询

子查询作为父查询的条件

#查询Python成绩大于等于80的学生的信息,不要成绩
select a.* from stuinfo a left join score b using(sid) where python>=80;

select * from stuinfo where sid in (select sid from score where python>=80);

#查找python最高分的学生,不要成绩
select * from stuinfo where sid in (select sid from score where python in (select max(python) from score));

• in | not in

  在什么时候使用in,在任何情况下都可以使用in

  #查询python成绩不及格的学生
  select * from stuinfo where sid in (select sid from score where python<60);

  #查询没有参加java考试的学生
  select * from stuinfo where sid in (select sid from score where java is null);

• some | any | all

  #in =
  #some | any | all 用在=后面
  some 和 any 是一样的 和 in比较相似
  all 表示全部的 ,和=号一样
  select * from stuinfo where sid =some (select sid from score where python<60);
  select * from stuinfo where sid =any (select sid from score where python<60);
  select * from stuinfo where sid =all (select sid from score where python<60);

  #!=some !=any 和 not in 不一样
  #返回及格
  select * from stuinfo where sid not in (select sid from score where python<60);
  select * from stuinfo where sid !=some (select sid from score where python<60);
  select * from stuinfo where sid !=any (select sid from score where python<60);
  #!=all 和 not in一致
  select * from stuinfo where sid !=all (select sid from score where python<60);

• exists | not exists

  #如果有学生的python成绩达到100分,则显示所有人的信息
  select * from stuinfo where exists(select * from score where python=100);

  select * from stuinfo where not exists(select * from score where python!=100);

• 子查询分组

  #找出java成绩最高的男生和女生

  select * from stuinfo where sid in (select sid from score where java in (select max(java) from score join stuinfo using(sid) group by sex));

  #没有问题的
  (select * from stuinfo where sid in (select sid from score where java in (select max(java) from score join stuinfo using(sid) where sex=1)) and sex=1) union (select * from stuinfo where sid in (select sid from score where java in (select max(java) from score join stuinfo using(sid) where sex=2)) and sex=2);

  #用最普通查询

4.视图

1.视图是一张虚拟的表,视图当中包含了多个表的数据
2.视图中实际上没有数据,只有表的结构,数据从基础表中去获取
3.一张表可以创建多个视图,一个视图可以引用多张表

• 创建视图

  create view view1
  as
  select * from stuinfo left join score using(sid);

  create view view2
  as
  select * from stuinfo;
  #视图是一张表
  select * from view1;

  #视图主要就是方便查询

• 查询

select * from view1;

• 修改视图

  alter view 视图名
  as
  select a.sid,a.sname,b.python from stuinfo a left join score b using(sid);

• 查看创建视图的语句

  show create view 视图名;

• 查看视图的结构

  desc 视图名;

• 查看所有的视图

  show tables;

• 删除视图

  drop view [if exists] 视图名字;

• 视图的算法论

  1.merge :合并算法(默认算法)
  2.temptable:零时表算法
  3.undefined:未定义的算法,自定义算法

  create or replace algorithm=merge view 视图名
  as
  select a.sid,a.sname,b.python from stuinfo a left join score b using(sid);

5.事务

什么是mysql中的事务?
1.事务是一个不可拆分的工作单元
2.事务作为一个整体向系统提交,要么一起执行成功,要么一起失败
3.事务不支持嵌套

• 事务的特性

  1.原子性:不可被拆分
  2.一致性:要么一起执行成功,要么一起失败
  3.隔离性:事务彼此之间没有关系
  4.永久性:一旦执行成功,不可被修改

• 开启事务

  #事务指针对写的动作 insert update delete
  start transaction;
  insert into stuinfo values(null,‘name’,1,18,‘city’); #会产生一个新的id
  insert into score values(9,100,100);

  #回滚
  rollback;

  #执行成功
  commit;

• 自动提交事务

  #自动提交事务是一个机制
  show variables like ‘autocommit’;
  #修改自动提交的状态
  set autocommit= 0|1

  #事务只有在开启的状态下才能使用
  #事务只能在innodb的引擎下才能使用,myisam中没有这个机制

6.索引

#key
优点:加速了查找的速度
缺点:
1.额外的使用了一些存储的空间
2.索引会让写的操作变慢
#mysql中的索引算法叫做 B+tree(二叉树)

• 索引的创建原则

  适用于myisam的表引擎
  #适合
  1.用于频繁查找的列
  2.经常用于条件判断的列
  3.经常由于排序的列
  #不适合
  1.列中数据并不多
  2.不经常查询的列
  3.表中数据量过小

• 索引的类型

  1.主键索引(primary key)
  2.外键索引(foreign key)
  3.唯一键(unique)
  4.全文索引(fulltext key) #在模糊查询的使用
  5.普通索引(index)

  #联合索引
  index key(‘sid’,'sname)
  #只要同时查询两个字段,才会触发
  where sid=1 and sname=‘tom’;

• mysql优化

  1.表类型的不同
  2.myisam中建立索引
  2.水平分表,垂直分表
  3.插入优化
  insert为什么需要优化

  #一次插入一条
  insert into 表名 set 字段=值;
  #爬虫,现将数据给爬取下来,在数据插入到数据库
  #数据库 连接数据库服务器 选择数据库 语法分析 检查 插入 关闭链接

  #对于python来说sql语句就是一个字符串,每次插入1w条
  insert into 表名 values(值),(值);
  #数据库 100次 连接数据库服务器 选择数据库 语法分析 检查 插入100w次 关闭链接

  4.select语句优化
  结果集尽量不要写*,要使用那些字段,就填写那些字段
  #使用选择排序,还要加上limit
  select sid,sname from stuinfo where order by xxx limit 20;

  5.分库#讲一个项目拆解为多个项目,多个项目就是多个库
  6.分机器:主从复制(一主多从,多主多从)
  #主服务器中的表示innodb的,多台从服务器myisam的表
  7.硬盘的选择,王者,把mysql的服务器换成固态硬盘

MySQL_Lesson_5

建立表

CREATE TABLE `stuinfo` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `seat` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `score` (
  `sid` int(11) NOT NULL COMMENT '学号(主键)',
  `ch` tinyint(4) DEFAULT NULL COMMENT '语文成绩',
  `math` tinyint(4) DEFAULT NULL COMMENT '数学成绩',
  `seat` tinyint(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.存储过程(procedure)

语法:
create procedure 存储过程名(参数,…)
begin
	//代码
end//

注意：存储过程中有很多的SQL语句，SQL语句的后面为了保证语法结构必须要有分号（;），但是默认情况下分号表示客户端代码发送到服务器执行。必须更改结束符

通过delimiter指令来跟结束符

delimiter // #将结束字符定义为//(原来是;)

(1)创建存储过程

#简单的
create procedure pro_1()
select * from stuinfo;
//

#如果存储过程中就一条SQL语句，begin…end两个关键字可以省略。

#调用存储过程
call pro_1()//

#包涵多条sql语句的
#in代表输入参数,可以省略
#return
#procedure方便大型语句的查询;在创建成功以后,直接进行了语法的检查;
create procedure pro_2(in param int)
begin
	select * from stuinfo where sid=param;
	select * from score where sid=param;
end//

#调用
call pro_2(5)// 

(2)参数的类别

在存储过程中，没有return，如果需要返回值，通过输出参数来实现
在MySQL中，参数分为3类，输入参数（in），输出参数（out）,输入输出参数（inout），默认情况下是是输入参数（in）

(3)删除存储过程

语法:drop procedure [if exists] 存储过程名

drop procedure if exists pro_1//

(4)查看存储过程的信息

show create procedure pro_2\G


#查看存储过程
show procedure status where db ='python'\G

(5)局部变量

#1.py
a = 1;
def b():
    d = 2;
    def c():
        global a;
        #局部的变量申明
        nonlocal d;
        pass
    return c



语法：declare 变量名 数据类型 default [初始值]
通过:select ...into…或set命令给变量赋值

例题通过sid查询姓名和年龄
create procedure pro_3(id int)
begin
	 declare name varchar(10);
     declare sexx char(10);
     select sname,sex into name,sexx from stuinfo where sid=id;
     select name,sexx from dual;
end //
#调用pro_3
call pro_3(3)//

#注意:声明的变量名不能和列名(字段名)同名

create table_name like table;

insert into stuinfo1 select * from stuinfo//

例题:查找同桌
create procedure pro_4(name varchar(32))
begin
     declare stuseat tinyint;
     select seat into stuseat from stuinfo where sname=name;
     select * from stuinfo where  seat=stuseat+1 or seat=stuseat-1;
end //

#调用

call pro_4('小芳')//

#通过set给变量赋值

create procedure pro_5(in num1 year,in num2 year,in name varchar(32))
begin
	declare num int default 0;
	set num=num2-num1; #得到年龄
	update stuinfo set age=num where sname=name;
	select * from stuinfo where sname = name;
end//

call pro_5(1996,2018,'小芳')//

(6)全局变量(用户变量)

全局变量前面必须有一个@，全局变量的数据类型取决于变量的值。如果一个全局变量没有赋值，他的数据类型为null。

set @name='小芳'//
select * from stuinfo where sname=@name//

(7)系统变量

通过两个@开头的都是系统变量

 select @@version from dual//

系统命令 作用
@@version 版本号
current_date 当前日期
current_time 当前时间
current_timestamp 当前日期和时间

(8)带有输出参数的存储过程

#带有out关键字的参数,在存储过程运行结束以后,默认返回
create procedure pro_6(in num int,out result int)
begin
	set result=num*num;
end//

#调用
#@result 接受返回值
call pro_6(6,@result)//
select @result from dual//

(9)带有输入输出参数的存储过程

create procedure pro_7(inout num int)
begin
     set num=num*num;
end //

#调用

set @num=10//
call pro_7(@num)//
select @num from dual//

2.SQL编程(了解)

(1) if-elseif-else语句

#语法:
if 条件 then
	//代码1
elseif 条件 then
	//代码2
else
	//代码3
end if;

create procedure pro_8(in grade int)
begin
     if grade=1 then
        select '金牌会员' as '等级';
     elseif grade=2 then
        select '普通会员' as '等级';
     else
         select '游客' as '等级';
     end if;
end //
#调用
call pro_8(3)//

(2) case-when语句

create procedure pro_9(in num int)
begin
	#需要做判断的变量
     case num
          when 1 then select '杀马特' as '气质';
          when 2 then select '屌丝' as '气质';
          when 3 then select '正常人' as '气质';
          when 4 then select '贵族' as '气质';
          else select '输入不正确' as '气质';
     end case;
end //

call pro_9(0)//

#显示学员的学号、姓名、性别、语文成绩、等级

select sid,sname,case
	   when age<18 then '未成年'
       when age>=18 and age<60 then '成年'
       else '人精'
end as 'sign' from stuinfo//

(3)loop循环

#loop遇到leave退出
create procedure proc(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     sign:loop
         set total=total+i;
         set i=i+1;
         if i>=num then
            leave sign;# leave=break
         end if;
     end loop;
     select total from dual;
end //

call proc(100)//
#如果没有设置标签名,leave loop
#sign是循环名,用于结束循环,可以自己随意取名字

(4)while循环

#语法:
while 条件 do
	//代码
end while

create procedure pro_11(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     while num>=i do
           set total=total+i;
           set i=i+1;
     end while;
     select total from dual;
end //

call pro_11(100)//

(5)repeat循环

#语法
repeat
	代码
	until 条件    -- 直重复到条件为true才结束
end repeat

create procedure pro_12(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     repeat
           set total=total+i;
           set i=i+1;
           until i>num
     end repeat;
     select total from dual;
end //

call pro_12(100)//

(6)leave和iterate

leave类似于break，iterate类似于continue

create procedure pro_13()
begin
     declare i int default 0;
     sign:while i<5 do
           set i=i+1;
           if(i=3) then
                   #leave sign;   -- 类似于break
                   iterate sign;    -- 类似于continue
           end if;
           select i from dual;
     end while;
end //

call pro_13()//

3.MySql函数

内置函数

(1).数字类

语句 含义
select rand() from dual; 随机数
select * from stuinfo order by rand(); 随机排序
select round(5.6); 四舍五入
select ceil(5.3); 向上取整
select floor(5.6); 向下取整

(2).大小写转换

语句 含义
select ucase(‘i am lyb’); 大写
select lcase(‘I AM LYB’); 小写

(3).截取字符串

语句 含义
select left(‘abcdefg’,3); 截取左边的3位
select right(‘abcdefg’,3); 截取右边3位
select substring(‘abcdefg’,2,3); 从第2位开始取3个字符，起始位置从1开始

(4).字符串拼接

select concat(sid,sname,age,sex,city) from stuinfo;

mysql> select concat(sid,sname,age,sex,city) from stuinfo;
+--------------------------------+
| concat(sid,sname,age,sex,city) |
+--------------------------------+
| 7小明18male上海                      |
| 8小刚20male北京                       |
| 9小强22male重庆                      |
| 10小力23male天津                      |
| 11小丽21female北京                    |
| 12小月20female天津                    |
| 13小yb18male重庆                    |
| 17百强18male黑龙江                      |
| 18百强118male黑龙江                     |
| 19百强218male黑龙江                     |
+--------------------------------+

(5).coalesce(str1,str2):如果str1不为null则显示str1，否则显示str2

#指定的条件为Null,我们会自动补齐
select sid,sname,coalesce(python,'缺考'),coalesce(java,'缺考') from stuinfo left join score using(sid);

(6).length(字节长度)、char_length(字符长度)、trim(去两边空格)、replace(替换)

select length('千锋');

select char_length('千锋');

select length(trim(' 千锋 '));

select replace('pgone','one','two');

(7).时间戳

select unix_timestamp();

(8).将时间戳转成当前时间

select from_unixtime(unix_timestamp());

(9).获取当前时间

select now(),year(now()),month(now()),day(now()),hour(now()), minute(now()),second(now())\G

#现在时间,年,月,日,时,分,秒

(10).dayname(),monthname(),dayofyear()

select dayname(now()) as `星期`,monthname(now()) as `月份`,dayofyear(now()) as `本年第几天`;

(11).datediff(结束日期，开始日期)

例题计算自己活了多少天
select datediff(now(),'1998-01-01');

(12).md5():md5加密

select md5('@123456.');

3.自定义函数

#语法:
Create function 函数名(形参) returns 返回的数据类型
begin
	//函数体
end

#第一步
delimiter //

#不带参数的函数
create function myfun() returns varchar(32)
begin
     return 123;
end//

#调用函数
select myfun()//

#Linux中的mysql不支持函数
#先查看是否支持
show variables like 'log_bin_trust_function_creators';
#进入/etc/my.cnf
#放在[mysqld]
log_bin_trust_function_creators=1
#写好以后重启mysql服务器
service mysqld restart

#带参数
create function myfun_1(num1 int,num2 int) returns int
begin
     declare num int default 0;
     set num=num1+num2;
     return num;
end //

select myfun_1(100,200)//


#删除函数
drop function myfun_1//

4.触发器

1、触发器是一个特殊的存储过程
2、不需要直接调用，在MySQL自动调用的
3、是一个事务，可以回滚

(1)触发器的类型(触发事件)

1、insert触发器
2、update触发器
3、delete触发器

(2)创建触发器

#语法:
Create trigger 触发器名 触发时间[before|after] 触发事件 on 表名 for each row
Begin
	//代码
end//

(3)new表和old表

1、这两个表是个临时表
2、当触发器触发的时候在内存中自己创建，触发器执行完毕后自动销毁
3、他们的表结构和触发器触发的表的结构一样
4、只读，不能修改

stuinfo curd

打开文件,内存中需要加载,会随即分配一个空间用来保存文件的所有数据,->old  6

在新的一轮操作后,内存会生成新的空间,这个空间里面保存了新的数据变化->new 7

(5)insert触发器

#在stuinfo中插入一个值,就会自动在stumarks中插入一条数据
#after insert 表示的是在insert动作执行完毕以后触发
#on stuinfo for each row  针对的stuinfo表,并且可以读取到每一行的变化
#触发器中定义的局部变量不能与表中的字段名一致,否则会发生字段识别问题(识别不出到底是字段,还是变量)
create trigger trig1
after insert on stuinfo 
for each row
begin
     declare sidno int default 0;
	 declare npy int default 0;
     declare nja int default 0;
	 declare nseat int default 0;
     set sidno=new.sid;
	 set nseat=new.seat;
     insert into score set sid=sidno,python=npy,java=nja,seat=nseat;
end //

insert into stuinfo values(null,'鸡哥',1,23,'安庆',16)//

(6)update触发器

create trigger trig2
after update on stuinfo for each row
begin
	declare sidno int default 0;
    declare seatno int default 0;
	set seatno=new.seat;
	set sidno =new.sid;
	update score set seat=seatno where sid = sidno;
end //

select ((select max(seat) from stuinfo)+1)//
update stuinfo set seat=17 where sid=8//

(7)delete触发器

create trigger trig3
after delete on stuinfo for each row
begin
     declare sidno int default 0;
	 set sidno =old.sid; #删除了新表里面就没有了,只能从老表里面拿
	 delete from score where sid=sidno;
end //

delete from stuinfo where sid =8//

#触发器能做钩子函数

(8)查看 和 删除 触发器

show triggers\G

drop trigger if exists trig1//

##5.用户管理

[mysqld] --skip--grant--tables
#(5.5最好用)
#--skip--grant--tables 跳过登陆验证(MYSQL服务器开起中)

(1)创建用户

语法：create user ‘用户名’@’允许登录的主机地址’  identified by 密码

#%代表数据库的库名
create user 'ruidong'@'%' identified by '123456';

(2)删除用户

语法：drop user 用户

drop user ruidong;

(3)增加用户权限

#将python的所有表的select权限付给ruidong用户
#grant select on 运行使用的数据库.允许使用的表 to 'ruidong'@'%'; 
grant select on python.* to 'ruidong'@'%';  

#将所有数据库中所有表的所有权限付给ruidong用户
grant all privileges on *.* to 'ruidong'@'%';

#创建用户并授权
grant all privileges on *.* to 'mufeng'@'%' identified by '123456' with grant option;

#创建好用户以后,刷新mysql用户权限表
flush privileges ;(linux ,mac)

revoke select on python.* from 'ruidong'@'%';   #删除select权限
revoke all privileges on *.* from 'ruidong'@'%'; #删除所有权限

(4)mysql57忘记密码

1、首先停止mysql服务进程：
service mysqld stop
2.#然后编辑mysql的配置文件my.cnf(如果是windows的话找到my.ini)
vim /etc/my.cnf
3.#找到 [mysqld]这个模块：
#在最后面添加一段代码
skip-grant-tables   ##忽略mysql权限问题，直接登录
#然后保存 :wq!退出
#启动mysql服务：
service mysqld start

#直接进入mysql数据库：
mysql
#选择mysql数据库
use mysql;
#对user表的root用户进行密码修改
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

#特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了
#而是将加密后的用户密码存储于authentication_string字段


#执行刷新
 flush privileges;
#exit退出mysql
exit;
#启动服务
service mysqld start

Mysql主从实验

主从的概念

操作步骤

1. 关闭selinuxs
   grant all privileges on . to ‘hal’@’%’ identified by ‘123456’ with grant option;
   #128从 171主
   #以下针对两台服务器同时操作
   chkconfig selinux off
   #关闭开机启动
   setenforce 0
   #关闭selinuxvim /etc/selinux/config
   #编辑配置文件
   SELINUX=disabled 修改这一行chkconfig iptables off
   #关闭防火墙的开机启动
   service iptables stop #关闭防火墙
   getenforce #检测是否关闭

   systemctl stop firewalld  #关闭防火墙
   

2. 修改主服务器的配置
   vim /etc/my.cnf
   修改如下行
   [mysqld]#添加在mysqld模块下
   log-bin=mysql-bin #要开启
   server-id=5 #建议改成服务器ip地址的后一位
   #master端：
   binlog-do-db= py #二进制需要同步的数据库名
   binlog-ignore-db=mysql #避免同步 mysql 用户配置，以免不必要的麻烦
   #slave端:
   replicate-do-db= python       #(do这个就是直接指定的意思)
   replicate-ignore-db=mysql
   #重启服务器
   service mysqld restart

   systemctl restart mariadb
   

   2.查看主数据库信息

   #进入mysql
   mysql -uroot -p #连接主服务器
   #查看主服务器状态
   show master status\G
   *************************** 1. row ***************************             File: mysql-bin.000012  #master_log_file=mysql-bin.000012      
   Position: 554           #pos master_log_pos= 554    
   Binlog_Do_DB: python
   Binlog_Ignore_DB: mysql
   Executed_Gtid_Set:

3.配置从服务器

mysql -uroot -p #连接从服务器
#查看监听语句
? change master;
? change
#stop slave 必须是从服务器关闭的状态下:

change master to
master_host='10.11.58.88',
master_user='admin',
master_password='123456',
master_log_file='mysql-bin.000017',  
master_log_pos=317;

#需要做交换的文件名
start slave;    #开启从服务器
show slave status \G;  
#查看状态#==================#当看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#===================

#proxy_sql 代理sql软件

python使用pymysql

一、安装

pip3 install pymysql

二、使用操作

import pymysql
  
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='dbname', charset='utf8')

# 创建游标
#相当于迭代器
cursor = conn.cursor()

'''
cursor中还没有数据，只有等到fetchone()或fetchall()的时候才返回一个元组tuple，才支持len()和index()操作，这也是它是迭代器的原因。但同时为什么说它是生成器呢？因为cursor只能用一次，即每用完一次之后记录其位置，等到下次再取的时候是从游标处再取而不是从头再来，而且fetch完所有的数据之后，这个cursor将不再有使用价值了，即不再能fetch到数据了。
'''

mysql的游标详细看:https://blog.youkuaiyun.com/xushouwei/article/details/52201360


# 执行SQL，并返回受影响行数
effect_row = cursor.execute("select * from stuinfo")

# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update tb7 set pass = '123' where nid = %s", (11,))

# 执行SQL，并返回受影响行数,执行多次
#effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u1","u1pass","11111"),("u2","u2pass","22222")])

# 提交，不然无法保存新建或者修改的数据
#conn.commit()

# 关闭游标
cursor.close()


# 关闭连接
conn.close()

获取查询数据

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='dbname')
cursor = conn.cursor()
cursor.execute("select * from tb7")

#将游标的数据返回类型设置为字典
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 
# 获取结果的第一行数据
row_1 = cursor.fetchone()
print(row_1)
# 获取剩余结果前n行数据
# row_2 = cursor.fetchmany(3)
 
# 获取剩余结果所有数据
# row_3 = cursor.fetchall()
 
conn.commit()
cursor.close()
conn.close()

获取新创建数据自增ID

可以获取到最新自增的ID，也就是最后插入的一条数据ID

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
effect_row = cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)", [("u3","u3pass","11113"),("u4","u4pass","22224")])
conn.commit()
cursor.close()
conn.close()
#获取自增id
new_id = cursor.lastrowid      
print new_id

4、移动游标

操作都是靠游标，那对游标的控制也是必须的

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
 
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

5、fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
#游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("select * from tb7")
 
row_1 = cursor.fetchone()
print row_1　　#{u'licnese': 213, u'user': '123', u'nid': 10, u'pass': '213'}
 
conn.commit()
cursor.close()
conn.close()

6、调用存储过程

a、调用无参存储过程

 import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
#游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#无参数存储过程
#p2是存储过程的名字,*args参数以元祖的形式传递
cursor.callproc('proname',*args)  #等价于cursor.execute("call p2()")
 
row_1 = cursor.fetchone()
print row_1
 
 
conn.commit()
cursor.close()
conn.close()

b、调用有参存储过程

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 
cursor.callproc('p1', args=(1, 22, 3, 4))
#获取执行完存储的参数,参数@开头
cursor.execute("select @p1,@_p1_1,@_p1_2,@_p1_3")  #{u'@_p1_1': 22, u'@p1': None, u'@_p1_2': 103, u'@_p1_3': 24}
row_1 = cursor.fetchone()
print row_1
 
 
conn.commit()
cursor.close()
conn.close()

三、关于pymysql防注入

2、字符串拼接查询，造成注入

import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
 
user="abc' or '1'-- "
passwd="u1pass"
sql="select user,pass from tb7 where user='%s' and pass='%s'" % (user,passwd)
 
#拼接语句被构造成下面这样，永真条件，此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。
#select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'
 
row_count=cursor.execute(sql)
row_1 = cursor.fetchone()
print row_count,row_1
 
 
conn.commit()
cursor.close()
conn.close()

2、避免注入，使用pymysql提供的参数化语句

#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql
 
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1')
cursor = conn.cursor()
 
user="u1' or '1'-- "
passwd="u1pass"
#执行参数化查询
row_count=cursor.execute("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
#内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。
# sql=cursor.mogrify("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
# print sql
#select user,pass from tb7 where user='u1\' or \'1\'-- ' and pass='u1pass'被转义的语句。
 
row_1 = cursor.fetchone()
print row_count,row_1
 
conn.commit()
cursor.close()
conn.close()