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
-
错误日志
-
查看错误日志地址
show global variables like ”log_error" -
警告信息开关
show global variables like "log_warnings"; #查看是否开启 开启为1,关闭为0
set global log_warnings=0 #关闭
-
-
一般查询日志
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-
查看错误日志地址
show global variables like ”log_error" 警告信息开关2.2.错误警告开关
show global variables like "log_warnings"; #查看是否开启 开启为1,关闭为0
set global log_warnings=0 #关闭 -
-
慢查询日志
放到配置文件中
查询超时时间: 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;
子查询
把一个查询嵌套在另一个查询,叫内部查询
-
分类
-
标量子查询:返回但一直的标量,最简单的形式
-
列子查询:返回结果是n行一列
-
行子查询:返回结果为一行n列
-
表子查询:返回结果为n行n列
-
-
标量子查询(一个值)
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
-
cuncurrent_insert 为0时:不允许并发插入
-
cuncurrent_insert 为1:如果myisam没有空洞:即使有锁,也会从尾部插入 有空洞:不插入 (空洞:id=1,2,4 少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(读未提交) #脏读
下一篇: IOS马甲包(诚招大量开发)