mysql 复习

最新推荐文章于 2022-06-29 19:05:42 发布

dingsai88

最新推荐文章于 2022-06-29 19:05:42 发布

阅读量837

点赞数

分类专栏：数据库 mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/dingsai88/article/details/42120895

版权

数据库同时被 2 个专栏收录

45 篇文章 0 订阅

订阅专栏

mysql

11 篇文章 0 订阅

订阅专栏

第四天

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

1.
create database study_mysql ;

2.

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

CREATE TABLE employees_list(
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL

)
PARTITION BY LIST (store_id)(
PARTITION pNorth VALUES IN (2,4,6,8,10),
PARTITION pEast VALUES IN (1,3,5,7,9),
PARTITION pWest VALUES IN (11,13,15,17,19),
PARTITION pControl VALUES IN (12,14,16,18)
);

CREATE TABLE t2 (
id INT NOT NULL

)
PARTITION BY HASH(id)
PARTITIONS 4；

create index in_id on t2(id);

insert into t2(id) values(1);

insert into t2 values(2) ,(3),(4);

--创建新表
create table t3(id int);
--存储过程

create procedure p3()
begin
set @i=1;
while @i<10000 do
insert into t3 values(@i);
set @i=@i+1;
end while;
end

--测试分区

CREATE TABLE part_tab(
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL)engine=myisam

PARTITION BY RANGE(year(c3))(
PARTITION p0 VALUES LESS THAN(1995),
PARTITION P1 VALUES LESS THAN(1996),
PARTITION P2 VALUES LESS THAN(1997),
PARTITION P3 VALUES LESS THAN(1998),
PARTITION P4 VALUES LESS THAN(1999),
PARTITION P5 VALUES LESS THAN(2000),
PARTITION P6 VALUES LESS THAN(2001),
PARTITION P7 VALUES LESS THAN(2002),
PARTITION P8 VALUES LESS THAN(2003),
PARTITION P9 VALUES LESS THAN(2004),
PARTITION P10 VALUES LESS THAN(2010),
PARTITION p11 VALUES LESS THAN MAXVALUE);

CREATE TABLE no_part_tab(
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL)engine=myisam;

--测试分区测试数据
delimiter //

CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v<8000000
do
insert into part_tab
values(v,'testing partitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
set v=v+1;
end while;
end

delimiter;

insert into no_part_tab select * from part_tab

--分区表
select count(*) from part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'

--未分区

select count(*) from no_part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'

--解析分区表
desc select count(*) from part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'
mysql> desc select count(*) from part_tab where c3>date'1995-01-01' and c3<date'
1996-12-31' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

--解析未分区表
desc select count(*) from no_part_tab where c3>date'1995-01-01' and c3<date'1996-12-31'
mysql> desc select count(*) from no_part_tab where c3>date'1995-01-01' and c3<da
te'1996-12-31' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)

给未分区表创建索引
create INDEX idx_of_c3 on no_part_tab(c3)

mysql> create INDEX idx_of_c3 on no_part_tab(c3);
Query OK, 8000000 rows affected (56.75 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

给分区表创建索引
create INDEX idx_of_c3 on part_tab(c3)

mysql> create INDEX idx_of_c3 on part_tab(c3);
Query OK, 8000000 rows affected (57.39 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

索引分区总结:

分区表和未分区表建立索引以后速度差不多，但是未分区表更占用CPU等资源

加入未索引的列 \G 格式化显示 desc结果
--分区表
desc select count(*) from part_tab where c3>date'1995-01-01' and c3<date'1996-12-31' and c2='hello'
--未分区
desc select count(*) from no_part_tab where c3>date'1995-01-01' and c3<date'1996-12-31' and c2='hello'

总结:加入未索引的列以后，分区表优势明显

MySql 存储引擎

InnoDB: .fim

MyISAM:

InnoDB

my.ini 加入
--代表数据库存放的目录
innodb_data_home_dir="C:\Program Files\MySQL\MySQL Server 5.6\data\InnoDB"
--日志存放目录
innodb_log_group_home_dir="C:\Program Files\MySQL\MySQL Server 5.6\data\InnoDB"

innodb_data_file_path=ibdata1:10M:autoextend
--是否使用共享以及独立表空间
innodb_file_per_table=1

重启mysql

myisam 有三个文件 .frm 表结构 .MYD存表数据 .MYI 存索引
innodb有两个文件

create table t3(in int)ENGINE=InnoDB

create table t4(in int)ENGINE=InnoDB

create table t5(in int)ENGINE=InnoDB

create table t6(in int)ENGINE=InnoDB

create table t7(id int)engine=innodb
partition by hash(id)
partitions 5;

show create table t7

第五天Mysql优化

1.复制表结构

create table t3 like t1;

create table t1(id int);

create table t1(id int unsigned not null auto_increment primary key,
name varchar(30))

insert into t1(name) values("user1")
insert into t1(name) values("user2")

create table t2 like t1

insert into t2 select * from t1

2.索引

--创建索引
create index in_name on t1(name)

--查看索引
show index from t1;

--删除索引
drop index in_name on t1

--唯一索引
create unique index un_name on t1(name)

alter table t1 add index in_name(name)

自增长必须是主键

alter table t1 modify id int unsigned not null
alter table t1 drop primary key

desc t1;

alter table t1 drop index in_name

alter table t1 add unique un_name(name)

--主键
alter table t1 add primary key (id)
--调整自增
alter table t1 modify id int unsigned not null auto_increment

3.视图

insert into t1(name) values("user1"),("user2"),("user3"),("user4"),("user5"),
("user6"),("user7"),("user8"),("user9"),("user10")

create view v_t1 as select * from t1 where id>4 and id<8

drop view v_t1

4.内置函数

--连接字符串
select concat("hello" ,"world") as myname
--变小写
select lcase("ABC") as myname;
--变大写
select ucase("abc") as myname;
--长度
select length("linux") as myname;
--去掉左边空格
select ltrim(" test ") as myname;
--去掉右边空格
select rtrim(" test ") as myname;

select replace("123456","2","3") as myname;

--重复出现三次
select repeat("linux",3) ;
//从第1开始取得4个
select substring("123456789",1,4);

select concat(space(10),"linux");

数学函数
--十进制转2进制
select bin(10)

--ceiling 向上取整
select CEILING (10.10); 11

--floor向下取整
select FLOOR(10.10); 10

MAX(col)

MIN(col)
--平方根
select sqrt(4)
--返回0-1随机数
RAND()
select rand(3);

select * from t1 order by rand()

日期函数
curdate()
curtime()
now()

5.预处理PREPARE

prepare prepare1 from 'select * from t1 where id>?';

set @i=1;
execute prepare1 using @i;

drop prepare prepare1

6.事务处理

MySIAM不支持事务

关闭自动提交功能
set autocommit=0;

delete from t1 where id>5;
--回滚
rollback;
--提交
commit;

alter table t1 engine=Innodb;

inset into t1(name) values("user")
--还原点
savepoint savepoint1;

rollback to savepoint1;

7.mysql 存储过程 procedure

修改语句定界符为//
\d //

create procedure p1()
begin
set @i=14;
while @i<=100 do
insert into t1(name)values(concat("user",@i));
set @i=@i+1;
end while;
end//
--修改语句定界符为;
\d ;
--查看过程
show procedure status \G
call p1;

8.触发器 trigger

修改delimiter为// 语句定界符
\d //
delimiter //

创建叫trigger1的触发器,当向t1表插入数据时,也插入到t2表
--插入触发
create trigger trigger1 before insert on t1 for each row
begin
insert into t2(name)values(new.name);
end//

delete 是一行一行删除
truncate 删除表重新创建auto_increment 从头开始

insert into t1(name) values("user1")

--删除触发器
delimiter //
create trigger trigger2 before delete on t1 for each row
begin
delete from t2 where id=old.id;
end //

--更新触发器
delimiter //
create trigger trigger_update before update on t1 for each row
begin update t2 set name=new.name where name=old.name;
end //

9.重拍auto_increment 值
--auto_increment变成1
truncate table table_name

--清空表再设置1,否则自增
deleter from t1;
alter table table_name auto_increment=1;

二.1正则表达式regexp

select name,email from t1 where email regexp"@163[.,]com$"

2.rand()随机数

select * from stu order by rand();
--随机拿出钱三条
select * from stu order by rand() limit 3;

3.group by 的with rollup

create table table_group (cname varchar(30),pname varchar(30))

insert into table_group (cname,pname)values ("bj","hd"), ("bj","hd"), ("bj","xc"), ("bj","xc"), ("bj","hd"), ("sh","dh"), ("sh","dh"), ("sh","rg"), ("sh","dh")

select cname,pname,count(pname)from table_group group by cname,pname with rollup

4.bit group functions

bit_and ,bit_or

create table demo2(
id int
);
insert into demo2 values(10);
insert into demo2 values(20);

select * from demo2 group by id;

select bit_or(id) from demo2 group by id;
alter table demo2 add name varchar(30)

create table table_bit (name varchar(30),
score int
)

insert into table_bit(name,score)values("user1",10),("user1",20),("user1",30),("user2",1),("user2",2),("user2",3)

select * from table_bit group by name

select bit_and(score)from table_bit group by name

select bit_or(score)from table_bit group by name

5.外键 myisam不支持外键 innodb支持外键

create table temp(id int ,name char(20),foreign key(id) references outTable(id) on delete cascade on update cascade)

6.help

--查询记不太清楚的命令
? pro% ;
--查看存储过程procedure用法
? procedure;
--查看所有命令
? contents;
--行列互换
\G

三、SQL语句优化

show [session|global] status;
session(default) 表示当前连接
global 表示自数据库启动至今

这些针对所有表引擎
show session status like"com_insert%"
show global status like"com_insert%"

show session status like "com_delete%"
show global status like "com_delete%"

show session status like"com_select%"
show global status like"com_select%"

只针对innodb引擎
--select
Innodb_rows_read
--
innodb_rows_updated
--插入
innodb_rows_inserted
--删除
innodb_rows_deleted

--连接数mysql数量
connections
--服务器工作的秒数
uptime
--慢查询的次数
slow_queries;
--查看慢查询是否开启
show variables like "slow_query_log";
--慢查询相关
show variables like"%slow%";
--慢查询时间设定
show variables like '%long%';
show variables like'long_query_time';

--查看表结构和引擎类型
show create table table_name

--解析查询语句1
desc select * from demo2
--解析查询语句2
explain select * from demo2

mysql> explain select * from demo2 \G
*************************** 1. row ***************
id: 1
select_type: SIMPLE --简单查询(不使用表连接或子查询) primary（住查询，外层查询）、union（union第二个或者后面的查询）、subquery(子查询第一个select等)
table: demo2 --表名数据集
type: ALL --all全表扫描;性能好到差system(表就一行)const(只一行匹配)eq_ref(每一行使用主键和唯一)ref(同eq_ref无主键和唯一) ref_or_null(同前面对null查询)、index_merge(索引合并优化)、 unique_subquery(主键子查询)、index_subquery（非主键子查询）、range(表单中的范围查询)、index(都通过查询索引来得到数据)、all(全表扫描)
possible_keys: NULL --可能用到的索引名
key: NULL --实际用到的索引
key_len: NULL --索引字段长度
ref: NULL
rows: 17 --影响行数
Extra: NULL --where index 执行情况的说明和描述

explain select * from demo2 where name='test2' \G
mysql> explain select * from demo2 where name='test2' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 18
Extra: Using where
1 row in set (0.00 sec)

alter table demo2 add index in_name(name)

mysql> explain select * from demo2 where name='test2' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo2
type: ref
possible_keys: in_name
key: in_name
key_len: 93
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)

步骤，1查看慢查询日志

2.desc 、explain 查看语句
3.增加索引

数据库优化:group by 加入order by null 不排序,提高速度

mysql> desc select * from t2 group by name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> desc select * from t2 group by name order by null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14
Extra: Using temporary
1 row in set (0.00 sec)

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
alter table t1 add index index_name (name);

CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
alter table t2 add index index_class on class;
create index index_uid on t2(uid);

insert into t1(name)values("user1"),("user2"),("user3"),("user4"),("user6
"),("user5"),("user7"),("user8"),("user9");

insert into t2(uid,class)values(1,111),(2,222),(3,333),(4,111),(5,111);

嵌套查询--外边的表没使用索引

mysql> explain select * from t1 where id in(select uid from t2) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index
possible_keys: index_uid
key: index_uid
key_len: 5
ref: NULL
rows: 5
Extra: Using where; Using index; LooseScan
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: study_mysql.t2.uid
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

--多表查询
mysql> explain select t1.* from t1,t2 where t1.id=t2.uid \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index
possible_keys: index_uid
key: index_uid
key_len: 5
ref: NULL
rows: 5
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: study_mysql.t2.uid
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

--左连接
mysql> explain select t1.* from t1 left join t2 on t1.id=t2.uid where t2.uid is
not null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index
possible_keys: index_uid
key: index_uid
key_len: 5
ref: NULL
rows: 5
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: study_mysql.t2.uid
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

四、数据库优化

4.1优化表的类型

--删除自增
alter table t1 modify id int ;
--删除主键
alter table t1 drop primary key ;

mysql> explain select * from t1 where id <4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
Extra: Using where
1 row in set (0.00 sec)

优化
create table t1_temp like t1;
删除数据 truncate table t1_temp;

insert into t1_temp select * from t1 where id<4;

视图
create view v_t1 as select * from t1 where id<4;

4.2通过拆分表提高表的访问效率

主从数据库

4.3使用中间表提高统计查询查询速度

五、Mysql 锁

备份数据库用
1.myisam 读锁定所有人能读，但是不能增删改
2.myisam 写锁定不能读不能写

--读锁大家能读不能增删改
lock table t1 read;
--解锁
unlock tables;

--写锁 write 别人不能增删改查--自己可以
lock table t1 write;

六、服务器优化

6.1字符集:

\s 查看字符集 status
Server characterset
Db characterset
Client characterset
Conn characterset
修改my.ini文件

character-set-server=utf8
--校验字符集,order by 排序a -z这种排序
collation-server=utf8_general_ci

查看字符集的校验字符集
show character set

6.2打开bin log 日志 binary 二进制

6.3慢查询日志时间

6.4 socket 插座

socket被删不能登录通过tcpip协议和端口连接

--通过tcp ip登录
mysql -uUser -pPass --protocol tcp -hlocalhost

重启一次服务会自动创建mysqlsocket

6.5 root密码丢失

重启mysql服务越过授权表
1.关闭服务 linux(pkill mysqld) windows(net stop mysql56)

2.启动服务 linux(mysqld_safe --skip-grant-table --user=mysql &)

3.登录root mysql -uroot 不需要密码

4.select user,password from mysql.user;

5.update msql.user set password=password("123") where user="root" and host="localhost";

9.数据库备份恢复

本地:

1.进入MySQL目录下的bin文件夹：e：回车；

e:\>cd mysql\bin 回车

2.导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

范例：
mysqldump -uroot -p abc > abc.sql
（导出数据库abc到abc.sql文件）
提示输入密码时，输入该数据库用户名的密码（如果导出单张表的话在数据库名后面输入表名即可）

如果提示locktable错误：则在mysqldump -uroot -p abc后面空一格加上--skip-lock-tables

3、会看到文件news.sql自动生成到bin文件下?

MySQL命令行导入数据库：

1，将要导入的.sql文件移至bin文件下，这样的路径比较方便
2，同上面导出的第1步
3，进入MySQL：mysql -u 用户名 -p
如我输入的命令行:mysql -u root -p?? (输入同样后会让你输入MySQL的密码)
4，在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库
5，输入：mysql>use 目标数据库名
如我输入的命令行:mysql>use news;
6，导入文件：mysql>source 导入的文件名;
如我输入的命令行：mysql>source news.sql;

MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。
1.Win32下MySQL的备份与还原
1.1 备份
开始菜单 | 运行 | cmd |利用“cd \Program Files\MySQL\MySQL Server 5.0\bin”命令进入bin文件夹 | 利用“mysqldump? -u 用户名 -p databasename >exportfilename”导出数据库到文件，如mysqldump -u root -p voice>voice.sql，然后输入密码即可开始导出。

1.2 还原
进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；建立你要还原的数据库，输入"create database voice；"，回车；切换到刚建立的数据库，输入"use voice；"，回车；导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。

2.Linux下MySQL的备份与还原
2.1 备份
[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，输入密码即可。
2.2 还原
法一：
[root@localhost ~]# mysql -u root -p 回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。
法二：
[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
[root@localhost mysql]# mysql -u root -p voice<voice.sql，输入密码即可。

dingsai88

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql 复习

第四天GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;1.create database study_mysql ;2. CREATE TABLE employees (
复制链接

扫一扫