MySQL基础、主从复制、优化

mysql

apache公司 开源共享 免费

mysql [-hlocalhost] -uroot -p 以超级管理员的身份登录

use demo; 查看

• dcl（数据库控制语言）:

• ddl（数据库定义语言）:

• dml（数据库操纵语言）:

• dql（数据库查询语言）:

• tcl（事务控制语言）：

• 数据库锁：

• 主从配置：

命令行链接方式

mysql -u用户名   -p密码  -h服务器ip地址   -p服务器端口mysql端口号   -d数据库名
mysql -uroot -p9264934.. -hlocalhost -p3306 -dguanli

数据库控制语言命令（dcl）:

创建本地用户

-- 选择mysql数据库
use mysql;
-- 创建本地用户
create user 'superboy'@'localhost' identified by 'iamsuperboy';
-- 刷新mysql的系统权限相关表，使添加用户操作生效，以免会出现拒绝访问
flush privileges;

创建远程用户

-- 从192.168.122.12登陆的用户
create user 'superboy'@'192.168.122.12' identified by 'password';
-- 从任意ip登陆的用户
create user 'superboy'@'%' identified by 'password';
-- 不做指定默认为'%'
create user 'superboy' identified by 'password';

对用户的基本操作

 创建用户
 create user 'wang'@'localhost' identified by 'iamsuperboy';

 修改用户的权限：
 grant all privileges on *.* to 'wang'@'%';

 # all 可以替换为 select,delete,update,create,drop
 -- 赋予部分权限，其中的shopping.*表示对以shopping所有文件操作。
 grant select,delete,update,insert on 数据库.* to 'wang'@'localhost' identified by 
 'superboy';

 -- 赋予所有权限
 grant all privileges on 数据库.* to superboy@localhost identified by 'iamsuperboy';

 'revoke'
 --撤销权限
 revoke privlieges_type on 权限名 from 用户名

 删除用户：
 delete from mysql.user where user='user_name' and host='localhost' ;

 --允许远程链接
 grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;flush privileges;--刷新

修改密码

1、set password for 'wang'@localhost = '123456'
2、update mysql.user set password=password('新密码') where user='root' and                  host='localhost' flush privileges;
3、mysqladmin -u用户名 -p旧密码 password 新密码
​
#忘记root密码
1、停掉mysql服务  serve mysql stop
2、cmd中进入安装目录bin  然后 mysqld --console --skip-grant-tables --shared-memory  #跳过权限检查
3、再开启另一个cmd进入mysql    #直接输入mysql
4、flush privileges
5、退出，并重启系统

 

数据库备份

mysqldump -u [username] -p[password] [database_name] > [文件夹路径]
mysqldump -uroot -p9264934.. -hlocalhost wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-data [database_name] > [文件夹路径] #只要结构，不要数据
mysqldump -uroot -p9264934.. -hlocalhost --no-data wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-create-info [database_name] > [文件夹路径] #只要数据，不要结构
mysqldump -uroot -p9264934.. -hlocalhost --no-create-info wangwei > mysql
​
​
mysqldump -u [username] -p[password] [dbname1,dbname2] > [文件路径]  #备份多个数据库

 

 

数据库查询

• 数据库查看

- 查看所有的表：show tables
​
- 查看表的信息： show  full tables;
​
- 查看相关列：show columns from teach；
            show columns from teach like 'id'；
            show columns from teach like '%e%'； #查看带有 ’e‘的内容

• 查看用户信息

查看当前用户：select user();
            select current_user();

查看当前有多少用户登录
            select user,host,db,command from information_schema.procrsslist:

 

数据库维护

• 分析表

analyze table 表名1 [表名2]

• 优化表

optimize table 表名

• 检查表

check table 表名

• 修复表

repair table 表名

 

 

• 查看表的字段内容

desc t1;         //自增  primary key//主键

• 添加数据

insert into t1 values(1,'tom','man',18);

insert into t1 (id,name,sex,age) values(1,'tom','man',18);

• sql注入

" or 1=1;-- 

 

• 查看数据是否添加成功

select * from t1;

• 删除一个表

drop t1;

• 删除表中的一行数据

delete from stu where id=1

• 替换某字段内容的update语句

update t1 set id=replace(id,1,2);

update t1 set sex='woman' where c_id=1;

 

数据库定义语言（ddl）

新建数据库

create database [if not exists] database_name

删除数据库

drop database [if exists] database_name

创建表

create table ceshi1(
id int(10) not null auto_increment primary key,
name varchar(10),
age varchar(10),
sex enum('男','女') not null,
phone varchar(11) unique,
habbit set("游泳","健身"),
pasword varchar(32))default charset=utf8;    comment='示例';

 

修改表

alter table 表名 add|drop|modify|change|alter|rename

• alter 增加列

alter table user add sex enum("男","女") not null [after name]

• alter 增加在table最后一列 的一个属性

alter table t2 add hobby char(20);

• alter 在table的第一列添加新字段属性

alter table t2 add project int first;

• alter 在sex字段之后添加新字段num属性

alter table t2 add num int after sex;

• alter 删除表中的字段

alter table t2 drop class_id; //删除class_id字段

• alter 添加主键

alert table t2 primary key(id)

• alter (设置默认值)

alter table student alter class_id set default 0;//设置默认值为0
alter table student alter class_id drop default;//删除默认值

*为什么有了modify和change还要来个alter呢？这是因为另外两个在修改的时候会把字段之前旧的属性全部覆盖掉

举个例子：现在需要修改class_id default的值，我们需要这么写

alter table student modify class_id varchar(20) default 10;

而采用alter则不需要则可以只需要设置默认值

 

• modify && change (修改字段)

区别：change必须指定新的字段名而modify则不需要

如果需要修改字段名只能用change，否则用哪个都可以

alter table t1 modify num char(10);
//modify 修改num 的数据类型int(10)=>char(10)

 

alter table t2 change class_id c_id varchar(20);
//将class_id字段名改为c_id并修改数据类型为varchar(20)
alter table t2 change class_id class_id varchar(20);
//修改class_id数据类型为varchar(20)不修改字段名
alter table t2 modify class_id varchar(20);
//将class_id数据类型修改为varchar(20)

 

• rename (重命名数据表）

alter table t2 rename to t2s;

修改存储引擎

• 修改引擎

#先删除再添加
alter table t2s engine=myisam;   //将存储引擎修改为myisam

• 删除引擎

alter table t2 drop index column_name

• 删除引擎

alter table t2 engine=inndb
show engines
show create table 表名

• 修改自增值（开始值）

alter table t2 auto_increment = 1

 

myisam

不支持事务，不支持外键，访问速度特别快（主要的基本应用为insert，select），创建成功后，有以下三个文件，扩展名分别为（二进制）

• .frm(存储表定义，表结构)

• myd（mydata存储数据）

• myi（myindex，存储索引）

innodb

• 健壮的事务型存储引擎

• 更新密集的表

• 自动灾难恢复

• 外键约束

• 需要事务支持

 

索引类型

• 主键索引 primary

  • 一个表中唯一的，在数据的查询，写入，读出能够按照一定的顺序，一定排列进行有序的操作，并且除主键外的其他的字段都会收到其影响

  • 主键的值只能是唯一的，不能重复，auto_increment

• 唯一的键 unique

  • 一个表中能够给多个字段设置唯一的键，他会在查询本字段，形成一定的顺序，分组查询

  • 在本字段中，不能出现相同的内容，除了null外

• 普通索引 index

  • 能给多个字段设置普通索引，会在查询本字段

• 文本索引 fulltext mysql 5.7版本后有效

  • 文本编辑器

  • 帮助我们在大批文本中有序查找内容

 

外键

• 查看外键

show create table student;

 

• 创建外键

alter table student
add foreign key(cid)
references classes(cid) on delete cascade;

• 删除外键

alter table student 
drop key aa;  #删除约束
alter table student 
drop foreign key aa;   #删除键

 

1、create table if not exists classes(     #创建主表 ：班级表
cid int(10) auto_increment primary key,
cnaem varchar(20))default charset=utf8
​
​
2、create table if not exists student(     #创建副表 ：学生表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) #  ******加外键
)default charset=utf8

• 外键链接下，删除主表内容提示

on delete
district（默认）     cascade（同时删除）   no action（什么都不做）   set null(设置为空)
​
on update
district（默认）     cascade（同时删除）   no action（什么都不做）   set null(设置为空)
​
​
create table if not exists student(     #没附表的情况下    创建副表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) on delete cascade
)default charset=utf8
​
#或
​
alter table student     #有副表没外键的情况下
add foreign key(cid)
references classes(cid) on delete cascade;

 

数据库操纵语言（dml）

insert

• 插入的内容与原unique的id等唯一值冲突的时候

insert into student (sid,sname,cid) values (3,ai,3) on duplicate key update sid=sid+1     #student 内有sid=3的数据

• 快速复制一个表的结构

create table aaa like stu

• 快速复制一个表的内容

insert into aaa select * from stu

 

 

• replace

可以置换现有的主键或unique

replace into aaa (sname, cid) value("zhangsan",3)

 

update

update t1 #low_priority 延迟更新，等没人查询在更新 set column_name1 = expr1 column_name2 = expr2

where
    condition
show full tables

• 带有select子句的更新

update stu set sname='111' where id=5;
select tname from teach order by id asc;#随机取  desc倒序
select tname from teach order by rand() limit 1;
#两个表之间的更新
update stu set tname=(select tname from teach order by rand() limit 1) where tname is null;
#关联更新
update table1,table2,...
set table1.attr=val,table2.attr=val,...
where condition
​
update table1 join table2 on...
set table1.attr=val,table2.attr=val,...
where condition

 

update classes，student where classes.cname=student.sname set classes.cname="aa",student.sname=""  

 

delete

• 带有limit的删除语句

delete from student order by id desc limit 1  #删除id从后致前的第一个

 

• 关联删除

delete classes,student from classes,student where classes.cname="allj" 

 

 

清空数据

delete fro student     # 逐条删除，主键自增不会从1开始，而是继续，效率低

truncate [table] student     #自增从1开始  效率高

 

日志管理

记录服务器运行信息，通过日志文件可以监视服务器的运行状态和性能，还能对服务器进行排错与故障处理

• mysql有六种不同类型的日志：

  • 错误日志：记录启动，运行或停止时出现的问题，一般也记录警告信息 一般开启

  • 一般查询日志：记录客户端的链接和执行的语句 一般关闭

  • 慢查询日志：记录所有执行时间超过long_time的所有不适用索引的

  • 二进制日志：数据库信息有任何改变，都会放到二进制日志中 （需要指定）

  • 中继日志：

  • 事务日志：

• 查询变量

  
   show global variables [like '%log%']

• 修改变量

  
  set global variables_name=val  

1. 错误日志

   1. 查看错误日志地址

      
      show global variables like  ”log_error"    

   2. 警告信息开关

      
       show global variables like "log_warnings"; #查看是否开启  开启为1，关闭为0
       set global log_warnings=0   #关闭

2. 一般查询日志

   
   1. 启用开关：general_log=(on|off)     #set global general_log=on   一般时候都关闭
   2. 记录类型：log_output               #show global variables like 
   3. 查看存储位置：general_log_file       #show global variables like 

   1. 查看错误日志地址

      
      show global variables like  ”log_error"    警告信息开关2.

      2.错误警告开关

   
   show global variables like "log_warnings"; #查看是否开启  开启为1，关闭为0
   set global log_warnings=0   #关闭

3. 慢查询日志

   放到配置文件中

   
   查询超时时间： long_query_time=3
   查询慢查询 ： log_slow_querys={yes|no}
   启动慢查日志： log_query_log=1 (on|off)
   日志记录文件： slow_query_log_file[=file_name]

数据库查询语言（dql）

* 通配符 所有

函数	描述
escape ‘$’	把 $ 规定为转义字符
cast(1982-3-1 as data)	把1928-3-1转换成data型
count(cname)	cname的条数
sqlfind_in_set(needle,place)	needle:查询内容 place：所在字段

select

select count(cname) as num from classes

 

select
​
column_1 , column_2,...
​
from a
​
    table_1

• 模糊查询

  内容	描述
  %老师	以老师结尾的数据
  老师%	以老师开头的数据
  _老师	老师前面只有一个可变量
  老师_	老师后面只有一个可变量
  like	精确查询
  =	精确查询

• where

• 语句顺序

where 
​
    conditions
​
group by column_1
​
having group_conditions
​
order by column_
​
limit offset, length

操作符                                    描述
=                    #等于，几乎任何数据类型都可以用
<> !=                #不等于
<
>
<=
>=
​
逻辑运算符
or                   #或者
and                  #并且
not                  #非

操作/逻辑运算符	描述
=	等于
<>!=	不等于
<	小于
>	大于
<=
>=
or	或
not	非
and	和

• between 包含两端

  (cast(1982-3-1 as data) 把1928-3-1转换成data型)

  select * from student where birth between cast(1982-3-1 as data) and cast(1988-6-10 as data) ;

• in

select * from classes where sqlfind_in_set(l,cname)   #一个字段中含多个内容

 

 

• group by

select
    c1,c2,c3,....
from
    table
where
    where_conditions
group by t1,t2,t3...;

 

having

分组后进行筛选 where是分组前筛选

having后可跟条件（函数）

 

函数	描述
avg()	计算一组值
count()
instr()
sum()
min()
max()

1、通过时间分类 看一时间段内进货 额

2、通过类别

3、时间、类

 

• order by

对单列或多列的查询结果 进行升序或降序排序

select column1,column2,...
from t2
order by num desc，price asc  #降序排列   asc升序    以前一列为基础，再排后一列
​
select column1,column2,...
from t2
order by field（name,"商品3"，"商品2","商品1"）desc / asc    #自定义排序

 

• limit

约束查询结果的行数 ，一般跟order by 一起使用

select column1,column2,...
from tablename
limit offset , count     #offset：偏移量     count：条数
                         #只有一个参数n：从头开始取n条
​
select * from goods where cid=1 group by cid desc limit

 

 

关联查询

表与表之间有关系，通过关系去查询

mysql支持一下连接：

• 交叉链接

select cname,gname
from category cross join goods;
​

 

• 内链接

select
name
from
t1
inner join
t2 on t1.id=t2.id

 

• 左连接

select              #以左面为基础
name
from
t1
left join
t2 on t1.id=t2.id

 

• 右连接

select              #以右面为基础
name
from
t1
right join
t2 on t1.id=t2.id

• 联合查询

union    #可以去掉重复项    union all  包括重复项
​
select cname from category union select gname from goods;

 

子查询

把一个查询嵌套在另一个查询，叫内部查询

• 分类

  1. 标量子查询：返回但一直的标量，最简单的形式

  2. 列子查询：返回结果是n行一列

  3. 行子查询：返回结果为一行n列

  4. 表子查询：返回结果为n行n列

 

1. 标量子查询(一个值)

select * from article where uid = (select uid from user where status=1 order by uid desc limit 1)                  in     

• 关键字 any all

  any ：< > = <>条件中的任何一个就可以

  all： < > = <> 条件中的所有条件才可以

  
  select * from goods where cid < any (select id from category where id=3 or id=2) 
                                 #all

2.列子查询

​

 

函数

聚合函数

总值，平均值，最大值，最小值，求和 除count外会忽略null

• count（）

  
  select count(*) from category group by cid;

• avg 平均数

  
  ​

• num

• max(),min()

• group_conatan()

  把分组后的结果连接起来

  
  select group_concat(gname) from goods group by cid;   

• concat() 字符串链接函数

  
  select concat("first","last") from names
  ​
  #concat_wg()   指定连接符号
  select concat_ws("-","first","last") from names

 

• left（） 指定从左至右取的内容长度

  select left ("abcdef",3)
  "abc"

 

• replace（） 替换，更新

  select replace（"this is firts","firts","first"）  #把""中的 firts 换成first

 

• substring() 截取 可以从任意位置取

  select substring("abcdefg"2,2)   #从第二位开始，取两个

 

• trim() 删除不必要的前导或尾随字符

  select trim({both|leading|trailing} from str)
  
  select trim("    abcd    ")  #默认去掉前后的空格
  select trim(leading from    "abcd"   )  #之去掉前面的
  select trim(leading "a" from    "abcd"   )  #去掉前面的  a
  select trim(both "a" from    "abcd"   )   #去掉所有的  a

 

 

• format 保留有效数字位数

  默认en_us

  select format(1001.353535,2)   #保留三位
  select format(1001.353535,2，"de_de")   #默认en_us 改成  de_de

 

时间函数

• curdate() 返回当前的日期

select curdate()

• now()

select now()
select now(),sleep(5),now()

• sysdate()

  返回指定日期函数

select sysdate()
select sysdate(),sleep(5),sysdate()

• day() 获取今天几号

• month()

select month(now());

• year()

• weekday(now()) 获取星期几

• dayname() 获取星期几英文名称

set @@lc_time_names = 'zh_cn';     #改中文
select dayname(time) from goods;

 

时间计算函数

• datediff（）

select datediff("2017-08-03","2018-08-03")   #相差几天

 

• timediff()

select timediff("2017-08-03 12:00:00","2018-08-03 12:00:00")    #相差得时间  

 

• timestampdiff( unit, begin,end)

  unit:microsecond second minute hour day week month quarter year

#unit:microsecond,second,minute,hour,day,week,month,quarter,year
select timestampdiff(day,"2017-08-03 12:00:00","2018-08-03 12:00:00")    #相差得时间用什                                                                           么单位表示  

 

• date_add(start_date,interval expr unit)

  在开始时间上加一个时间后得到的时间

select date_add("2018-12-31 23:59:59",interval "1:1" minute_second)

 

视图

mysql中有一些复杂的语句，对于复杂的查询，每一次查询都是对性能的消耗，而视图就是把第一次所查询出来的内容做成一个表

• 查看所有视图

show full view

• 查看视图创建过程

show create view viewname

 

• 创建视图

create view viewname as select ...

• 修改视图

alter view viewname as select ...

• 创建或替换视图

没有就创建，有就修改

creat or replace view viewname as select ...     #viewname：视图名

• 删除视图

drop view [databases.name].[viewname]

 

临时表

使用频率低，不想每一次都查询，又不想创建视图，临时保存数据，生命周期为数据库使用期间，自动删除

• 创建

create temporary table tempname select ...   #没有as   tempname：临时表表名

 

事务

只有innodb支持事务

数据库处理操作，执行就好像它是一个单一的一组有序的工作单元，在组内每个单独的操作是成功的，那么一个事务才是完整的，如果事务中任何操作失败，整个事务将失败。

事务性质

• 原子性：确保了工作单位中的所有操作都成功完成：否则，事务被终止，在失败时会被回滚到事务操作以前的状态。

• 一致性：可确保数据库在正确的更改状态在一个成功的结果提交事务。

• 隔离：使事务相互独立地操作。

• 持久性：确保了提交事务的结果或系统故障情况下仍然存在作用。

 

事务控制语句

• begin或start transaction;显示地开启一个事务；

• commit;也可以使用commit work,不过二者是等价的。commit会提交事务，并使已对数据库进行的所有修改成为永久性的

• rollback;有可以使用rollback work,不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

• set autocommit=0 禁止自动提交

• set autocommit=1 开启自动提交 variable

 

锁

不同的存储引擎支持不同的锁机制 mysql中锁的大分类分为：1、表级锁，2、行级锁，3、页面锁

• 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

• 行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高。

 

表级锁

共享锁：读锁，所有人（包括自己）能查看，不能修改（修改会提示错误），锁期间不能操作别的表

独占锁：写锁，只有 自己可见可修改，锁期间不能操作别的表

• mysql表级锁存储引擎：

  • myisam 引擎

  • memory 引擎

• 加锁（用到mysql时候，mysql本身已经给加锁了）

演示事务操作的时候 操作多条命令时，不希望被其他用户修改

lock tables t1 [t2] read [local],      lock tables t1 [t2] write [local]

• 释放锁

unlock tables;

• 查看表锁征用情况：

  • table_locks_immediate

  • table_locks_waited

  
  show status like "table%"  
  show status like "%lock%"
  show processlist   #查看那些当前是哪些命令在等待，从而进行优化
  show open tables   #当前倍速欧珠的表以及锁的次数

 

 

• 表锁优化

• optimize table 表名

• set concurrent_insert=2 允许并发插入

• 是否设置写的优先级，（登录）

• 是否设置写内存，解决批量插入数据（新闻系统更新）

• 解决并发问题

  • 并发插入（只能插入，不能修改和删除）

    myisam存储引擎有一个系统变量 concurrent_insert ,专门控制并发插入行为 值可为 0，1，2

    1. cuncurrent_insert 为0时：不允许并发插入

    2. cuncurrent_insert 为1：如果myisam没有空洞：即使有锁，也会从尾部插入 有空洞：不插入 （空洞：id=1,2,4 少3）

    3. cuncurrent_insert 为2：加锁的时候把 [local] 加上 allways 总是可以队尾插入信息（锁的情况下）********很重要，用mysql就改成2 set cuncurrent_insert=2

• 读写锁的优先级

  • 修改写锁的最大次数

  
  set global max_write_lock_count=1      #写一次之后暂停写操作，给读操作机会

  • 降低写锁的优先级

  
  set global low_priority_updates=1

• 设置写内存

  可以根据具体的业务设置读写内存

  
  max_allowed_packet=1m      # 限制接受的数据包大小，大的插入和更新会被简直掉
  net_buffer_length=2k       #insert 语句的缓存值 (),(),()  2k-16m
  bulk_insert_buffer_size=8m   #一次性insert语句插入的大小

间隙锁

数据库里id有 1，3，4，5 锁是id>1 再操作id=2的时候也会被锁。所以确定条件的时候一定要有范围

行级锁

引擎：innodb

如果一个事务请求的锁模式与当前的锁兼容，innodb就将请求的锁授予该事务；反之如果两者不兼容，该事务就要等待锁释放

 

• 请求锁是否兼容当前锁模式

	x（排他锁）	ix（意向排他锁）	s（共享锁）	is（意向共享锁）
x	冲突	冲突	冲突	冲突
ix	冲突	兼容	冲突	兼容
s	冲突	冲突	兼容	兼容
is	冲突	兼容	兼容	兼容

• 特点

  • 想让innodb 上行锁，当前行的操作字段必须要有索引。

  • 如果操作行的操作字段没有索引，行锁会自动升级为表锁。

  • 即使有索引，但用字段的时候修改了类型，索引失效 eg:（规定类型：name varchar(255） 命令输入：name=0;）

  • 意向锁是innodb自己加的，不用干预；对于update,delete和insert，innodb会自动加排他锁（x）；对于select不会自动加共享锁（s）。

  • 当对一行上了排他锁（update，delete，insert），其他用户对这一行数据没有任何权限，但并不影响其他用户修改其他数据。

  • 当对一行上了排他锁（update，delete，insert）后，其他用户可以对当前行进行读操作：修改前的值（innodb默认隔离方式）。

  • 研究行锁时，需要将自动提交关闭，

    
    set autocommit = 0
    #注：如果有多个客户端，每个都要设置

• 加锁

  
  共享锁：select 后需要加  lock in share mode
  排他锁：select 后需要加  for update

• 释放锁

  
  commit;
  rollback;

• 查询行锁征用

show status like 'innodb_row_lock%';

• 隔离

• 查看隔离级别

select @@session.tx_isolation

• 设置隔离级别

set session transaction isolation level read uncommitted(读未提交)  #脏读