欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL基础、主从复制、优化

程序员文章站 2022-04-23 09:12:15
MySQL apache公司 开源共享 免费 mysql [-hlocalhost] -uroot -p 以超级管理员的身份登录 use demo; 查看 DCL(数据库控制语言): DDL(数据库定义语言): DML(数据库操纵语言): DQL(数据库查询语言): TCL(事务控制语言): 数据库 ......

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(读未提交)  #脏读