数据库:
免费的,开源的(MySQL)
关系型
安装MySQL服务 1通过命令行操作 2通过软件操作 3程序存入数据
操作MySQL:
- mysql -hlocalhost -u root -p -P(端口,数据库默认端口3306) 登入数据库
常用命令:
-
show databases; 查看库
-
create database + 名字 if not exists ; 创建库
-
use + 名字; 使用库
-
show tables; 查看表
-
create table + 名字(
id int(10) auto_increment primary key,
name varchar(255),
age varchar(10),
sex varchar(10))default charset=utf-8; 创建表
-
desc + 名字; 查看表结构
-
insert into + 名字 (name,age,sex) values(‘zhangsan’,‘10’,‘nan’); 往表里插入数据
-
update + 名字 set sex=‘nv’ where id=1; 更新数据
-
select * from + 名字; 查看某个表
-
delete from + 名字 where id=2; 删除表中的某条数据
-
drop table + 名字; 删除表
-
drop database + 名字; 删除库
-
source + sql文件 导入数据库文件
-
关联查询:
-
子查询:
- select * from logs where id in(枚举) (1,2,3)
-
格式化
- time_format(事件对象,"%Y/%m/%d") //初始化事件对象,格式转化为 年/月/日
-
limit 0,1 //第0条开始查询一条
主要内容
- DCL(数据库控制语言):是用来设置或者更改数据库胡勇或者角色权限的语句,关键字:grant、revoke(8.0版本及以上)
- DDL(数据库定义语言):创建表、创建库
- 创建库
create database if not exists databasename;
- 创建表
create table if not exists tablename;
- 删除库
drop database if exists databasename;
- 创建库
- DML(数据库操作语言):更新,删除、插入
- DQL(数据库查询语言):查询
- TCL(事务控制语言):事物(一个完整的事件)
- 数据库锁
- 主从配置
访问控制权限:
安全性
命令行连接方式:
-u 用户 -p 密码 -h 服务器IP地址 -D 数据库名 -P服务器端MySQL的端口号
默认表
- 默认mysql库里的user表 储存用户的信息 HOST字段储存可以访问数据库的地址 % 代表所有地址都能访问
- db表 某一个用户对数据库级别的权限
- table_priv表和column_priv表 包含表级别和列级别的权限
- procs_priv 存储过程的权限
创建用户:
create user 'usrname'@'localhost'(要访问的地址/ % 代表所有地址都能登录) identified by 'password'
查看用户的权限:
show grants for 用户名@localhost
@'%.alibaba.com' //代表在alibaba.com这个域名里的所有子域名才能访问
删除用户:
drop user 用户名,用户名..... 删除多个用户
设置权限:
grant privilege(权限名),[privilege(权限名)]......on privilege_level(给哪个表/层级) to user(用户名) [with [grant_option | resource_option]](创建之后允许修改权限)
#设置所有权限
grant all privileges on *.* to username@localhost with grant option; *******
#设置指定的权限
grant user rfc identified by 'mypassword';
grant select,update,insert......
允许远程连接:
grant all privileges on *.*(所有库里的所有表) to 'root'@'%' identified by 'mysql'(密码) with grant option(允许修改) flush privileges(刷新、修改完立马生效);
REVOKE:(撤销权限)
REVOKE [(权限名)] ON (库名).(表名) FROM user(用户名)
修改密码:
#登录MySQL 命令设置
set password for 用户名@localhost = password('新密码'); #在最新8.0.11版本的mysql中删除了password这个方法
#更新user表里的信息修改密码
use mysql
use user
update......
#使用mysqladmin 修改root密码
mysqladmin -u用户名 -p旧密码 password 新密码;
忘记root或者初始密码:
- 关闭MySQL服务
- 打开dos窗口,cd到MySQL的安装目录
- 输入
mysqld --skip-grant-tables
(跳过权限检查) 不能关闭窗口!!! - 再开一个dos窗口 登录
- 切换到mysql库
use mysql
- 改密码
update user set password=password('123') where user='root';
(password(‘123’) 内置函数 将密码加密处理) - 刷新权限
flush privileges
(必须步骤)
数据库备份:
mysqldump命令 用于数据库备份
#所有表结构和数据都备份导出
mysqldump -u [username] -p[password] [database_name](数据库名) > [dump_file.sql](导出的文件名)
#只备份导出表结构,不备份数据
mysqldump -u [username] -p[password] --no-data [database_name](数据库名) > [dump_file.sql](导出的文件名)
#只要数据,不要表结构
mysqldump -u [username] -p[password] --no-create-info [database_name](数据库名) > [dump_file.sql](导出的文件名)
#导出所有的数据库
mysqldump -u [username] -p[password] --all-databases > [dump_file.sql](导出的文件名)
#指定导出数据库
mysqldump -u [username] -p[password] [database1,database1,](数据库名) > [dump_file.sql](导出的文件名)
source命令 用于导入数据库文件
source d:/ddd/aaa.sql #source之后路径和文件名
查看表结构:
#查看所有的列
desc table_name;
show columns from table_name
#筛选列
show columns from table_name like '%e%'; //所有列中带e字母的
当前用户:select user();
当前在线的所有用户:select user,host,db,command from information_schma ...
数据库维护:
MySQL提供了几个有用的语句操作数据库进行优化
- 分析表语句:分析表里的索引是否正确
analyze table tablename;
- 优化表语句:在操作数据库时会有碎片
optimiza table tablename;
- 检查表语句:检查数据库表是否正常
check table tablename;
- 修复表语句:修复检查出来的数据库表错误
repair table tablename;
修改表:
ALTER TABLE tablname CHANGE [COLUMN] oldname(字段名) newname(新名字) varchar(100) not null;
追加字段:
ALTER TABLE tablename ADD sex enum('1','0') not null [FIRST](放到最前面) [AFTER phone](放到哪个字段前面);
删除字段:
ALTER TABLE tablename DROP columnname(要删除的字段名);
添加主键:
ALTER TABLE tablename ADD PRIMARY KEY colunmname(字段名);
删除主键:
ALTER TABLE tablname DROP PRIMARY KEY columnname(字段名);
添加索引:
ALTER TABLE tablename ADD UNIQUE xxx(索引名)(columnname)(字段名); //添加唯一索引
ALTER TABLE tablename ADD FULLTXT xxx(索引名)(columnname)(字段名); // 添加全文索引
ALTER TABLE tablename ADD INDEX xxx(索引名)(columnname)(字段名); // 添加普通索引
删除索引:
ALTER TABLE tablename DROP INDEX(索引类型) (字段名);
修改引擎:
ALTER TABLE tablename ENGINE = InnoDB;
show engines; //查看所有支持的引擎
修改自增值:
ALTER TABLE tablename auto_increment = 1;
数据类型:
数值类型:
- zerofill 数值前面的位数是否用零填充
- int(n) n为要显示的宽度 并不代表只能存储这么大的数字
- unsigned 代表无符号 有这个属性即代表存储数据中没有负数
类型对应表:
类型 | 大小 | 范围(有符号) | 范围(无符号) |
---|---|---|---|
tinyint | 1字节 | (-128,127) | (0,255) |
smallint | 2字节 | (-32768,32767) | |
mediumint | 3字节 | ||
int | 4字节 | ||
bigint | 8字节 | ||
float | 4字节 | ||
double | 8字节 | ||
decimal |
字符串类型:(只有char类型是定长的,无论存储多少都占用255字节)
类型 | 大小 | 用途 |
---|---|---|
char | 0–255字节 | 定长字符串 |
varchar | 0–65535 | 变长字符串 |
tinyblob | 0–255 | 不超过255字节的二进制 |
tinytext | 0–255 | 短文本 |
enum | ||
set |
日期类型:
引擎类型:
MyISAM:不支持事物,不支持外键,不支持行级锁,存储速度快,效率高
- .frm(存储表的信息)
- MYD(MYData,存储数据)
- MYI(MYIndex,存储索引)
InnoDB:健壮的事物存储引擎,功能强大,适合处理多并发,可以自动从灾难中恢复,支持外键
索引类型:
-
!!复合索引
多个字段同时使用一个索引
alter table tablename add index abc(字段1,字段2,字段3);
-
主键索引:primary key索引
- 主键必须是唯一的,不能重复
- 主键在一个表中只能有一个字段设置
-
唯一键:unique
- 可以给多个字段设置unique
- 多个字段的unique值不能重复
-
普通索引:index
- 一个表中可以给多个字段设置index
-
文本索引:fulltext(MySQL5.7版本以后有效)
- 文本编辑器
- 能够帮助我们快速在大批量的文本当中,有序查找内容
-
外键:将数据库中的两个表进行关联,主表中的主键和副表中的非主键字段进行关联
-
作用:对数据操作进行约束
-
设置外键:
#创建表时添加设置外键 [CONSTRAINT constrint_name(给约束一个名字)] FOREIGN KEY [forign_key_name(给外键一个名字)] (column)(给谁去设置外键) REFERENCES parent_table_name(主表名) (column)(主表中的哪个字段);
-
删除外键:
ALTER TABLE tablename DROP FOREIGN KEY aaa(外键的名字); ALTER TABLE tablename DROP KEY aaa(键的名字);
-
添加外键:
ALTER TABLE tablename ADD [CONSTRAINT constrint_name(给约束一个名字)] FOREIGN KEY [forign_key_name(给外键一个名字)] (column)(给谁去设置外键) REFERENCES parent_table_name(主表名) (column)(主表中的哪个字段) ON DELETE CASCADE(在删除的时候进行关联);
-
district(默认,严格模式)
-
cascade(关联)
-
set null(将关联的值变成null)
-
no action(什么也不做)
-
-
DML数据库操纵语言:order by desc(反序)
排序
-
insert
- ON DLPLICATE KEY:
ON DUPLICATE KEY UPDATE sid=cid+1
(id 冲突时执行这个事件) insert into aaa select * from stu;
(拷贝stu表并插入到aaa)
- ON DLPLICATE KEY:
-
replace:如果要插入的信息已经指定主键或者unique,在使用replace插入时就是在更新数据,否则就是在插入数据(和insert一样)效率比较低
-
update:更新语句,注意在更新时必须加入条件,没有条件将全部更新
-
update [LOW_PRIORITY] [ignore] tablename .....
前一个参数表示调低优先级 后一个出错忽略 -
带有select 的更新语句
select tname from teacher order by rand() limit 1; #查询tname并进行随机排序并拿一条数据 update stu set tname=(select tname from teacher order by rand() limit 1) where tname is no null; #带有select和条件的更新语句
-
关联更新
-
第一种写法
update table1,table2 set table1.attr="xxx",table2.attr="xxx" where table1.xx=table2.xx and ....
-
第二种写法
#副表只需要更改一条数据 update table1 [inner] join table2 on table1.xx=table2.xx set table1.attr="xxx",table2.attr="xxx" where table1.xx=xx #副表需要更改多条数据 update table1 [inner] join table2 set table1.attr="xxx",table2.attr="xxx" where table1.xx=xx and table2.xx=xx
-
-
-
delete:删除语句
-
delete from tablename order by desc limit 1;
先排序 然后删除第一条 -
关联删除
-
第一种写法
delete table1,table2 from table1,table2 where....
-
-
-
truncade:清空语句
truncade [table] tablename #清空这个表中的数据 并且自增数据重置
日志管理:
设置日志开启或关闭
set global variable like "xxxxxx=on/off"
日志的种类:
-
错误日志(一般开启):
-
查看错误日志
show global variables like "log_error";
-
查看错误日志是否开启
show global variables like "log_warnings"; 0--关;1--开
-
-
一般查询日志(一般不开启):
- 启动开关:
general_log=(on|off)
- 日志文件变量:
general_log_file
- 启动开关:
-
慢查询日志(一般开启):
- 查询超时事件:
long_query_time
- 启动慢查询日志:
log_slow_queries=(yes|no)
- 启动慢查日志:
slow_query_log=(on|off)
- 日志记录文件:
show_query_log_file[=file_name]
- 查询超时事件:
-
二进制日志:
记录了数据库发生改变的数据
- 二进制日志的开启
- 通过系统变量
log_bin
查看二进制日志是否开启 - 开启二进制日志,必须在my.cnf中[mysqld]下面添加log-bin[=DIR[filename]]
- 系统变量
log_bin
是静态参数,不能动态修改 - 修改my.cnf
- datadir %log_bin%
- 通过系统变量
- 查看二进制文件
show binary logs
#查看当前所使用的日志show master logs
#查看所有使用的日志show master status
#查看当前二进制日志文件状态
- 删除二进制文件
- 删除某个文件之前的二进制文件
purge binary logs to xxx
- 清除所有二进制
reset master
- 设置自动清理
show variables like 'expire_logs_days'
set expire_logs_days=7(设置为0表示不自动删除)
- 删除某个文件之前的二进制文件
- 二进制日志的开启
-
中继日志:
-
事务日志:与具体事务配合
onnerdb_flush_log_at_trx_commit; a:每秒同步,并执行磁盘flush操作 1:每事务同步,并执行磁盘flush操作 2:每事务同步,但不执行磁盘flush操作
mysql全局变量的查询和修改:
查询变量:
show global variables [like '%log%']
修改变量:
set global variables_name=val
DQL数据库查询语言
-
查询语法
#查询关键字同时出现的时候先后顺序如所示 select column1,column2... from tablename [inner|left|right] join table2 on conditions where conditions group by column1 having group_conditions order by xxx limit offset,length;
-
别名:
slect xxxx as xxxx from...
给这个字段一个别名 -
where之后能加的字句
-
比较运算符
操作符 描述 = 等于,几乎任何数据类型都能使用 <>或!= 不等于 < 小于,判断数字或者日期 > 大于,判断数字或者日期 <= 小于等于 >= 大于等于 -
逻辑运算符
操作符 描述 or 或 and 且 not 非 -
between关键字
-
between运算符允许指定要查询的范围
expr [not] between begin and end; #如果expr的值大于或等于begin的值且小于或等于end的值
-
-
like关键字(效率低下)
select * from tablename where xxx like "%xx%"; #%代表能有任意多个字符 select * from tablename where xxx like "_xx_"; #_代表只能有任意一个字符 like "%xx\%xx%" #中间的%加上\转义 ESCAPE "$" #在句尾加上表示将$变成转义字符
-
in关键字:是否包含(在 in 里面放的都是确定的东西)
select * from tablename where xxx in ("xxx","xxx");
-
find_in_set("xxx",column)
函数,从column中查找包含xxx的数据- 第一个参数是要查找的字符串
- 第二个参数是要查找的字段
-
group by
:分组,通过where筛选出来的数据中进行分组select ..... from ... where ... group by c1,c2,c3...
group by
中的having
子句:将分完组之后的数据进行过滤筛选avg()
:计算一组值或者表达式的平均值count()
:计算表中的行数instr()
:返回字符串在字符串中第一次出现的位置sum()
:计算一组或者表达式的和min()
:最小值max()
:最大值
-
order by
:排序,可对单列或者多列进行排序select * from tablename order by xxx [ASC|DESC] #默认是升序排序
-
对多列进行排序:在前一列排好序的基础上再进行排序
-
自定义排序
select * from tablename order by field (columnname,xxx,xxx,xxx,xxx) [ASC|DESC];
-
-
limit
之后有一个或两个参数 (经常与order by
结合使用,放在order by
之后)select * from tablename limit n,m; #n为偏移量 m为从偏移的这个位置取多少条数据
-
-
关联查询
将两个有联系的表数据查询出来,带条件的关联查询在最后加上where条件
-
交叉连接
selct xxx,xxx from table1 cross join table2;
-
内连接(得到两个表中的交集)
select xxx,xxx from table1 inner join table2 on xxx=xxx;(交集)
-
左连接
select xxx,xxx from table1 left join table2 on xxx=xxx;(以左边为基准)
-
右连接
select xxx,xxx from table1 right join table2 on xxx=xxx;(以右边为基准)
-
-
联合查询
将多个
select
语句查询的结果合并 union-
union
:查询出来的数据表头是第一个表中的字段,内容为两个表的数据,它会自动去除重复项select xx from table1 union [all] select xx from table2; #加上all之后就不会执行去重操作
-
-
子查询
将一个查询嵌套在另一个查询语句之中,查询之中嵌套查询、子查询
-
标量子查询:返回单一的标量,最简单的形式
select xxx from xxx where uid=(select uid from xxx where xxx);
-
列子查询:返回的结果集是N行一列
-
in
:包含在后面条件的任意一个就执行#关键字:in select xxx from xxx where cid in (select cid from xxx where xxx);
-
any
:可以与=、>、>=、<、<=、<>配合使用,只满足后面条件里的任意一个就执行#关键字:any select xxx from xxx where cid any < (select cid from xxx where xxx || xxx);
-
all
:可以与=、>、>=、<、<=、<>配合使用,要满足后面所有条件才执行关键字:all select xxx from xxx where cid all < (select cid from xxx where xxx || xxx);
-
-
行子查询:返回的结果集是一行N列
select xxx from xxx where (cname,cid) = (select cname,cid from xxx where xxx);
-
表子查询:返回的结果集是N行N列
select xxx from xxx where (uid,uname) in (select uid,uname from xxx); select xxx from xxx where city='hangzhou' and exisit (select * from xxx where cid=1(查询xxx表中是否有hangzhou的id)) #把子查询写到exiit中,防止表xxx中没有对应数据报错
-
常用的函数:
聚合函数:
除count函数之外,其他的聚合函数
count()
:统计表中的行数avg()
:计算一组值或表达式的平均值sum()
:求和max()
:最大值min()
:最小值group_count()
:将分组后的数据聚合起来
字符串函数:
-
concat()和concat_ws()
:将查询结果连接起来select concat(xxx,xxx) from xxx; select concat_ws("-",xxx,xxx) from xxx; #将查询出来的数据用-连接
-
left()
:select left(con,3) from xxx; #查询出来的内容从左边开始截取3个
-
replace()
:select replace(str,old,new); #字符串的替换,从str中选出old用new替换
-
substring()
:select substring(con,start,num); #截取字符串con,从start开始,截取长度为num
-
trim()
:trim([both|leading|trailing][remove_str] from str) #both 两边都去 #leading 去左边 #trailing 去右边 #remove_str 去掉什么字符
-
format()
:用来格式化数字format(N,D,locle) #N是要格式化的数字 #D是要舍入的小数位数 #locale是表示的方式
日期和时间函数:
-
返回当前日期的函数
curdate()
:返回当前日期(只有日期)now()
:返回当前的时间(程序开始执行的时间)sysdate()
:整个程序执行完之后的时间
-
返回指定日期的函数
day()
:返回当前日期的日month()
:返回当前日期的月year()
:返回当前日期的年week()
:返回当前日期是本年的第几周weekday()
:返回当前日期是周几 0—6 周一到周日dayname()
:返回当前日期是周几,英文表示方式- 查看中国人习惯的表示方式:
set @@lc_time_names='zh_CN';
- 调用
dayname()
-
日期计算函数
-
datediff(xxx,xxx)
:计算两个任何有效日期的时间差 -
timediff(xxx,xxx)
:计算两个任何有效时间的时间差 -
timestampdiff(unit,begin,end)
:unit表示呈现出来的单位,有毫秒、秒、分、时、天、week、月、季度、年,start表示开始的时间,end表示结束的时间,计算时间差microsecond
毫秒second
秒minute
分hour
时day
天week
周mounth
月quarter
季度year
年
-
date_add()
:将时间间隔添加到date或者datetimedate_add(start,interval expr type) #start表示date或者datetime的起始值 #interval expr unit 表示是要添加到起始值的时间间隔值 #type 是时间类型 #expr 是值 #常用的type值: microsecond/second/minute/hour/day/week/month/quarter/year
-
视图:
1.创建视图:
create view viewname as select xxxxxx;
2.修改视图:
alter view viewname as select xxxxxx;
create or replace view viewname as select xxxxxx; #没有就创建,有就替换
3.删除视图:
drop view [is exists] viewname;
4.查看视图:
show full tables;
show create view viewname; #查看语句
临时表:
适用于多表查询的临时数据存放,生命周期为发起请求到数据库关闭
1.创建临时表
create temporary table xxx select xxxxxx;
事务:
事务的数据库处理操作,如果事务中的任何操作失败,整个事务都失败
事务性质:(记住)
- 原子性:确保了工作单位中的所有操作都成功完成,否则事务被终止,在失败时会回滚到事务操作前的状态
- 一致性:可确保数据库在正确的更改状态在一个成功提交事务(查询和更改同时发生,数据得一致)
- 持久性:确保了提交事务的结果或系统故障的情况下仍然存在作用
- 隔离性:使事物互相独立的操作(两个数据操作冲突)
数据库的隔离级别:
- Serializable (串行化):可避免脏读、不可重复读、幻读的发生
- Repeatable read (可重复读):可避免脏读、不可重复读的发生
- Read committed (读已提交):可避免脏读的发生
- Read uncommitted (读未提交):最低级别,任何情况都无法保证
以上四种隔离级别最高的是Serializable级别,最低的是Read uncommittd级别,当然级别越高,执行的效率就越低。像Serializable这样的级别,就是以锁表的方式使得其他的线程只能在锁外等待,所以平时选用何种隔离应该根据实际情况。在MySQL中默认的隔离级别为Repeatable read(可重复读)
在Oracle中,只支持Serializable(串行化)级别和Read committed(读已提交)这两种级别,其中默认的为Read committed级别
数据库底层机制:MVCC机制(多版本并发控制,提高读操作的性能)
就是在每一行记录的后面增加两个隐藏列,记录创建版本号和删除版本号,而每一个事务在启动的时候,都有一个唯一的递增的版本号。
查看隔离级别:
select @@session.tx_isolation;
设置隔离级别:
set @@session transaction isolation level read uncommitted(级别名);
!!!注意:多个客户端都要设置此命令
事务并发的问题:
- 脏读:事务A读取了事务B的更新的数据,然后B数据回滚,事务A拿到的数据就是脏数据
- 不可重复读:事务A多次读取同一条数据,事务B在事务A多次读取同一条数据是进行了更新,结果不一致
- 幻读(虚读):幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读
幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)
事务控制语句:(begin
、start transaction
、set autocommit=0
三个命令开启事务)
BEGIN
或START TRANSACTION
:显示地开启一个事务COMMIT
:也可以使用commit work
结束事务的执行,提交事务执行之后的数据到数据库ROLLBACK
:回滚到开启事务之前的状态SET AUTOCOMMIT=0
:禁止自动提交SET AUTOCOMMIT=1
:开启自动提交
数据库锁:
什么是锁?
悲观锁(访问的人都是破坏者)、乐观锁(访问的人都不是破坏者)
MySQL锁概述:
MySQL锁的机制,最显著的特点是不同的存储引擎支持不同的锁机制,innoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下使用行级锁
-
表级锁:开销小,加锁快;不会出现锁死;锁定粒度大,发生锁冲突的概率最高,并发度最低
两种锁模式:共享锁(读锁)、独占锁(写锁)
存储引擎:MyISAM MEMERY
特点:
- 作用范围在表的级别
- 如果加了读锁,对MyISAM表的读操作,不会阻塞其他用户对同一个表的读请求,但是会阻塞对同一个表的写请求
- 如果加了读锁,可以查询这个表的记录,但更新或访问其他表都会提示错误
- 如果加了写锁,对MyISAM表的写操作,则会阻塞其他用户对于同一个表的读和写的操作
- 如果加了写锁,可以读写表中的记录,但更新或访问其他表时会提示错误
如何加表锁:
#单个表加锁 lock table tablename read [local]; #多个表加锁 lock tables tablename1 read [local],tablename2 write [local];
释放锁:
unlock tables;
查询表级锁争用情况:
show status like 'table%' #MyISAM引擎的情况 或者 show status like '%lock%' #所有引擎的情况 或者 show processlist #能精确看到是哪些命令正在等待 或者 show open tables #当前被锁住的表以及被锁的次数
并发插入:(设置锁的时候加上local即可模拟并发插入)
MyISAM存储引擎有一个系统变量
concurrent_insert
,专门用来控制器并发插入的行为,其值可以为0、1、2concurrent_insert=0
never 不允许并发插入concurrent_insert=1
auto 如果MyISAM表中没有空洞(即表中没有被删除的行)MyISAM允许在一个远程读表的同时,在表的最后插入数据concurrent_insert=2
always 无论在表中有没有空洞,都可以在表尾插入数据
读写锁优先级:
默认情况下,写操作的优先级要高于读操作的优先级
-
设置写锁的最多次数(次数按需修改)
max_write_lock_count=1; eq:set global max_write_lock_count=1;
-
降低写的优先级
low_priority_updates=1; sql_low_priority_updates=1; eq:set global low_priority_updates=1; set global sql_low_priority_updates=1; #将这两个变量打开 insert low_priority into xxxx #将insert/update/delete的操作优先级降低
-
视情况而定,如果读的场景比较重要或者是场景较多,可以如此设置
设置写内存:
可以根据具体的业务设置读写锁的内存
max_allowed_packet=1M; #限制接收的数据包大小,大的插入或更新将被限制,导致失败 net_buffer_length=2K; #insert语句的缓存值 2K--16M bulk_insert_buffer_size=8M; #一次性insert语句插入的大小
优化:
- 可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一个表查询和插入的锁争用,例如,将
concurrent_insert
的值改为2 - 同时,通过定期在系统空闲时间段执行
optimize table
语句来整理空间碎片,收回因删除记录而产生的中间空洞 - 是否设置写的优先级,视场景而定,解决查询相对重要的应用(如用户登录)中
- 是否设置写内存,视场景而定,解决批量插入数据的场景中(如新闻系统更新)
-
行级锁:开销大,加锁慢;会出现锁死;锁定粒度小,发生锁冲突的概率最低,并发度也最高
行级锁的存储引擎:innoDB
MySQL的行级锁的两种模式
共享锁(S):允许一个事务读一行,组织其他事务获得相同数据
排它锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排它锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
请求锁和当前锁是否冲突 X IX S IS X 冲突 冲突 冲突 冲突 IX 冲突 兼容 冲突 兼容 S 冲突 冲突 兼容 兼容 IS 冲突 兼容 兼容 兼容 如果一个事务请求的锁模式与当前的锁兼容,innoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放
行级锁(事务锁)的特点:
- innoDB行级锁是通过给索引加锁来实现的,只有通过索引条件检索数据,innoDB才能使用行级锁,否则,innoDB自动升级为表锁
- 意向锁是innoDB自动加的,不需要用户干预,对于update、insert和delete语句,innoDB会自动给涉及数据集加上排它锁(X);对于普通的select语句,innoDB不会加任何锁
- 在研究行级锁的时候,需要将自动提交关闭,默认为开启,
set autocommit=0
注意:多个客户端都要设置
set autocommit=0
加行级锁:
select xxxx from xxx where xxx lock in share mode #共享锁 select xxxx from xxx where xxx for update #排它锁
释放锁:
commit 或者 rollback
共享锁主要用在需要数据依存关系时来确认某行数据是否存在,并确保没有人对这个记录进行update或delete操作
!!!注意:
-
当你给一条数据上了排它锁的时候,其他人是操作不了这条数据的,而第二个锁要是长时间未释放,系统会自动释放这个锁!
-
当我们给一条数据加上排它锁的时候,其他人对这条数据没有任何的操作权限,但并不影响其他人访问和操作其他的数据
-
innoDB默认的隔离方式:当一条数据加上了排它锁的时候,其他人是可以读取这条数据的,但读取的时候拿到的是以前的数据
-
当我们给一条数据加上排它锁并这条数据的字段并没有索引的时候,行级锁将会自动升级为表级锁,其他人对这张表的其他内容访问和操作都将会被阻塞
-
即使字段加上了索引,但是在使用这个索引的时候,把数据类型转换了,这个索引就失效,行级锁自动升级为表级锁
-
间隙锁:当你操作批量数据(where id>1)的时候,中间有间隙的数据(比如已经被删除的数据,指定的范围中间有间隙)其他人是不能进行操作的,所以在确定范围的时候需要注意
查看行级锁的争用情况:
show status like 'innodb_row_lock%';
如何优化行级锁:
- 尽量使用较低的隔离级别,精心设置索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会
- 选择合理的事务大小,小失误发生锁冲突的几率也会更小
- 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排它锁,而不是先申请共享锁,再修改数据,这样就容易出现死锁
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响
- 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能性(比如添加购物车)
-
页面锁:开销的加锁时间介于表、行锁之间;会出现锁死
主从复制:
在实际的生产环境中,由单台MySQL数据库时完全不能满足实际需求的,无论安全性、高可用性、高并发性
主从复制原理:
主从复制配置过程:
-
两台服务器的MySQL版本得一致,如果不一致,主机版本要低于从机版本
-
两台服务器的防火墙需关闭
-
双方数据库的用户得拥有远程访问的权限,允许远程连接
-
主服务器配置
1.修改服务器的MySQL配置文件 --my.ini:
放到【mysqld】下 #mysql唯一id server-id=1 #二进制日志文件,此项为必填项,否则不能同步数据 log-bin='mysql-bin' #指定二进制错误文件 log-error='mysql-error' #需要同步的数据库,如果需要同步多个数据库: binlog-do-db=uek_demo #binlog-do-db=slaveDB1 #binlog-do-db=slaveDB2 #不需要同步的数据库 binlog-ignore-db=mysql
2.授权给从数据库服务器
grant replication slave on *.* to 'root'@'localhost' identified by '123456' flusk privileges
!!!mysql8.0版本的授权方式:
mysql> CREATE USER 'repl'@'192.168.199.198' IDENTIFIED WITH mysql_native_password BY 'Ron_master_1'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.199.198';
3.重启服务器
#Linux:登录进mysql,运行 sudo /etc/init.d/mysql restart
4.查看主服务器的二进制信息
show master status
-
从服务器配置
1.修改从服务器的MySQL配置信息
server.id=2 log-bin='mysql-bin' replicate-do-db=uek_demo #需要同步的数据库 replicate-ignore-db=mysql #不需要同步的数据库 read_only #只读
2.重启服务器(重启MySQL服务)
3.执行同步sql语句
change master to master_host='xxx.xxx.xx.xx' #设置要连接的主服务器IP master_user='root' #设置要连接的主服务器用户名 master_password='123456' #设置要连接的主服务器密码 master_log_file='mysql-bin.000002' #设置要连接的主服务器bin日志的日志名称 master_log_pos=1041 #设置要连接的主服务器bin日志的记录位置
4.启动slave同步进程:
start slave
#查看状态 show slave status\G #其中Slave_IO_Running Slave_SQL_Running值都是yes,表示状态正常 #如果之前从服务器启动过需要先停止运行 stop slave
MySQL的优化:
1.sql及索引 2.数据库结构 3.系统配置 4.硬件
MySQL逻辑架构
1.客户端、连接层:处理连接
2.连接线程处理:分析sql语句
3.查询缓存:分析器,之前有过查询,放到缓存里
4.优化器
MySQL索引底层实现原理
B树:
经常用在文件目录结构和数据库索引
MyISAM索引实现:通过链表原理,存储索引和数据所在的地址,占用空间小,查询慢
innoDB索引实现:存储索引和数据,占用空间大,查询快
explain参数详解:
详细分析我们所写的sql语句的优劣性,最主要分析查询语句,从各种方面显示sql语句的执行效率等
sql语句的执行顺序:from/where/group by/having/select/distincy(去重)/order by/limit
使用方式:
explain + sql语句
参数:
- id:sql执行的顺序标识
- 如果是子查询,id会递增,id越大,执行优先级越高
- 如果id一样,执行的顺序为由上到下(数据少的先执行,数据多的后执行,为了节省内存空间)
- select_type: 查询的类型
- simple 简单查询
- primary 主键查询
- union 联合查询
- dependent union
- union result
- subquery 子查询
- dependent
- derived 派生表查询:
select aa.cname from(select * from course) as aa;
- table 在查询的表
- type:表示MySQL在表中找到他所需内容的一种方式 ALL<index<range<ref<eq_ref<const<system<NULL
- ALL:FULL TABLESCAN MySQL将遍历全表以找到匹配的数据
- index:FULL index Scan index和ALL的区别wiindex类型只遍历索引树
- range:只检索给定范围的内容,查找的内容不带索引,可以用between、<、>,但不要用in,用in时索引失效
- ref:表示上述的连接匹配条件,即哪些列或者常量被用于查找索引列上的值
- eq_ref:与ref类似,但是只有在关联语句中出现唯一值的时候出现
- const、system:用主键去做相等条件
- system:通过派生表和主键查询时所用
- prossible_keys:预想要使用哪些索引
- key:实际使用哪些索引
- key_len:索引的长度(所占用的内存空间) 在utf8编码中 英文占3个字节 中文占了4个字节 所以在我们加索引的时候,尽量在单字节的字段上加索引,并且索引名不宜太长
- ref:给定的条件中的数据类型
- rows:查询出来的数据的行数
- Extra:其他的一些信息:为空表示效率低下
- using index:使用了索引查询(效率高)
- using where:回表查询,通过索引到表中中查找数据(效率不高)
- using temporary:查询出来的信息不直接给你显示,建立了一个临时表来存储数据集,常见于排序和分组查询(效率很低)
eq:distincy(将数据结果进行去重)、在sql语句中加了distincy就回出现temporary
- using filesort:MySQL进行了多次排序,没有利用索引进行排序,效率太低
- using join buffer(缓存):在关联多个表查询的时候(效率低)
- impossible:语句没有报错,但是出现这个表示写了无意义的sql语句(查询了无意义的数据)
最佳左前缀:在你加上复合索引,并要使用索引的时候,索引使用的顺序和你加复合索引时的顺序最好一致,否则将会降低执行效率
eq: abc('one','two','three') #复合索引的顺序
select three from xxx where one=xxx and two=xxx;
MySQL优化的方法
优化工具:
- 通过使用explain命令分析sql语句的运行效率
- 通过开启慢查询来查看运行效率慢的sql语句
索引优化:
索引是我们提升sql查询效率的重要手段,同时索引的使用不当也会带来性能的问题,这时候应该注意以下的问题
-
不能将索引用作表达式的一部分,也不能用作函数的参数,否则索引失效
select * from xxx where id+=1; select * from xxx where max(id)
-
索引不能进行类型转换,否则索引失效
-
复合索引应遵循左前缀策略
-
复合索引不要使用
or
,否则索引失效 -
复合索引不要使用
!=
,否则索引失效 -
索引不要和
in
一起使用,导致索引失效 -
及时删除冗余索引
-
like
查询时尽量不要出现左边的%
,否则索引失效
单、多表sql优化手段
- 加索引(加在频繁使用的字段上)
- 调整索引顺序
- 删除多余干扰索引
- 调整查询条件,对索引有干扰的语句放在条件的最后
- 多索引添加原则:小表驱动大表(小表在前 where 小表.xx=大表.xx)
- 使用
left join
的时候给左边的表加索引,同理使用right join
的时候给右边的表加索引
表级别的优化(锁的应用)
系统级别的优化(配置项的设置)
其他方面的优化:
- 可以把为
null
的列改为not null
- 对于数据类型,一定要根据业务需求选择尽可能小的存储数据类型
UNSIGNED
表示不允许负值,可以提高上限一倍- 尽量不要使用
decimal
- 尽量使用
timestamp
使用4个字节存储空间,datatime
占用8个字节 - 大多数情况下没必要使用枚举
- 一个表的列不要太多,尽量控制在10个字段以下(多使用副表)
课程表:id name teacherID python Java nodejs 112
教师表:id tname jianjieid
简介表:id info
select * from jianjie where id=(select jianjieid from teacher where id=(s
elect teacherid from course where name='python'));
select info from teacher join jianjie on (teacher.jianjieid=jianjie.id) where teach
er.id=(select teacherid from course where name='python');
select * from jianjie,teacher,course where jianjie.id=teacher.jianjieid and teacher.id=course.teacherid and course.id=1;
explain 查看效率 type ---- 运行效率
查看 带python的老师的信息 3种方式 如何建表
列表 库存、单价、入库时间
查询在一段时间之内入库的商品总价
select sum(price*gnum) from goods where time between cast('2015-10-01' as date) and cast('2017-10-01' as date);
查询所有时间段男、女、童商品总价
select lid,sum(price*gnum) as totle from goods group by lid;
查询在指定的时间内类型的入库总价
select lid,sum(price*gnum) as totle,time from goods where time between cast('2012-10-01' as date) and cast('2016-10-01' as date) group by lid;
flask模块
app = Flask(__name__)
服务器app.run()
服务器启动@app.route('/',methods=["GET","POST"])
访问根目录时要执行的东西 后面定义执行函数return render_template("index.html",results=results)
执行函数返回index页面 保存results在页面中使用id = request.args.get("id")
获取get方法请求回来的idid = request.form
非关系型
数据仓
B/S和C/S:
-
B/S
- 可以随时访问最新的内容 免去用户下载
- 延迟 不能够流畅的操作 影响用户体验
-
C/S
- 延迟是可控的
- 必须有客户端 更新不及时
-
AJAX的出现
async javascript and xml (ajax)
xml 定义了一种语言的规范 标签文档 树文档(HTML是XML的扩展)
html:模板 xml(数据):模型
-
定义ajax对象
window.onload = function(){ } var ajax = new XMLHttpRequest() //定义对象 ajax.onload=function(ev){ console.log(ajax.) ajax.response //判断这个值是否和后台返回值一致 } ajax.onreadystatechange=function(ev){ if(ajax.) } ajax.open("get","2.html") //去哪执行,用什么方式执行 ajax.send() !!!!ajax.reponse就是数据 post方式和get方式传递数据 获取方式不一样 ajax.open("post","/",true) //第三个参数 默认为true 异步的 false为同步的 ajax.setRequestHeader("content-type","application/x-www-form-urlencoded") ajax.send("name=zhangsan") //里面填入要传递给服务器的参数
-
ajax要解决的问题
- 页面无刷新操作数据
- 按需获取数据
- 让b/s架构的软件能够像c/s架构的软件一样操作流畅
-
async javascript and xml
-
new XMLHttpRequest()
-
open() send() onload
-
可以处理多种类型的返回的数据 (text、json、document、blod、arraybuffer)
-
传递数据 get post
-
ajax函数“
function ajax(parents){ if (typeof parents!="object"){ return console.error("参数类型错误") } if (!parents.url){ return console.error("请输入url") }else{ var url=parents.url } //参数初始化 var type=parents.type || "get" var dataType=parents.dataType || "text" //处理数据 var data=parents.data || "" if (typeof data=="object"){ var str="" for(var i in data){ str+=i+"="+data[i]+"&" } data=str.strip(["&"]) } var obj=new XMLHttpRequest() obj.responseType=type obj.onload=function(ev){ parents.success(obj.response) } if(type=="get"){ obj.open("get",url+"?"+data) obj.send() }else if(type=="post"){ obj.open("post",url) obj.setRequestHeader("content-type","application/x-www-form-urlencoded") obj.send(data) } } ajax({ url:"/", type:"get", dataType:"text", data:"{name:name,age:age}", success:function(data){ console.log(data) } })
-