数据库ajax

数据库：

免费的，开源的（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)

• 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或者datetime

    date_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、2

  • concurrent_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方法请求回来的id
• id = 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)
        }
    })