mysql 复习





第四天








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 二进制



--查看bin log日志
 
show variables like "%bin%";


| log_bin                                 | OFF
| log_bin_basename                        |
| log_bin_index                           |
| log_bin_trust_function_creators         | OFF


在my.ini里修改
log-bin=mysql-bin


重启mysql 
exit;
net stop mysql56   --没有;
net start mysql56
| log_bin                                 | ON
| log_bin_basename                        | C:\mysql-bin       
| log_bin_index                           | C:\mysql-bin.index 
| log_bin_trust_function_creators         | OFF
| log_bin_use_v1_row_events               | OFF






6.3慢查询日志  时间

--慢查询日志是否开启
mysql> show variables like '%slow%';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| log_slow_admin_statements | OFF                      |
| log_slow_slave_statements | OFF                      |
| slow_launch_time          | 2                        |
| slow_query_log            | ON                       |
| slow_query_log_file       | DELL-670024CA3F-slow.log |
+---------------------------+--------------------------+


--慢查询时间是否开启
mysql> show variables like '%long%';
+--------------------------------------------------------+-----------+
| Variable_name                                          | Value     |
+--------------------------------------------------------+-----------+
| long_query_time                                        | 10.000000 |
| performance_schema_events_stages_history_long_size     | 10000     |
| performance_schema_events_statements_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size      | 10000     |
+--------------------------------------------------------+-----------+


修改my.ini
slow-query-log=1
slow_query_log_file="DELL-670024CA3F-slow.log"
long_query_time=10




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,输入密码即可。








评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

dingsai88

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值