mysql
mysql
一. 基本操作
1.显示
show databases;
show tables;
use 数据库名称;
desc 表名;
select * from 表名;
show create table 表名;
show create table 表名 \G;
msyql备份：
mysqldump -u root db1 > db1.sql -p
mysqldump -u root -d db1 > db1.sql -p
执行文件:
create database db5;
mysql -u root -d db5 < db1.sql -p
2. 自增步长
基于会话级别:
show session variables like 'auto_inc%'; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
set session auto_increment_offset=10 设置从哪里开始
基于全局级别：
show global variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10 设置从哪里开始
alter table 表名 AUTO_INCREMENt=20
基础表级别:
create table t1(
pid,int(11) not null auto_increment,primary key
)engine=innobd auto_increment=4 default charset=utf8
3.增删改查
1.创建用户:
create user 'alex'@'192.168.1.1' identified by '123';
create user 'alex'@'%' identified by '123';
2.授权
grant select,insert,update on db1.t1 to 'alex'@'%';
grant all privileges on *.* to 'alex'@'%';
revoke all privileges on db1.t1 from 'alex'@'%';
3.操作文件
建表：
create table t1(
id int not null auto_increment primary key,
name char(10),
)engine=innodb default charset=utf8
清空表：
清空数据，但保留表定义结构
delete from t1;
truncate from t1; 清除自增的
删除表：
drop table t1;
插入数据:
insert into t1(id,name) values(1,'alex'),(2,'susy');
insert into t1(age,name) select age,name from t2;
删除:
delete from t1 where id<6;
delete from t1 where id !=2;
delete from t1 where id >=2;
delete from t1 where id <=2 or name='alex';
修改:
update t1 set age=18 where age=17;
查看:
select * from t1;
select id,name from t2 where id > 10 or name='xx';
select * from t2 where id !=1;
select * from t2 where id in (1,4,12);
select * from t2 where id not in (1,5,12);
select * from t2 where id in (select id from t3);
select * from t2 where id between 5 and 12;
通配符:
select * from t2 where name like "ale%"; ale开头的所有(多个字符串)
select * from t2 where name like "ale_"; ale开头的所有（1个字符串）
限制:
select * from t1 limit 5; 前5行
select * from t1 limit 4,5; 从第5行开始往后5行
select * from t1 limit 5 offset 4 从第5行开始读5行
排序:
select * from t1 order by age asc; 从小到大
select * from t1 order by age desc; 从大到小
select * from t1 order by age desc,sid asc age大到小，sid小到大
分组:
select num from t1 group by num;
select num,nid from t1 group by num,nid;
select num,nid from t1 where nid > 10 group by num,nid order nid desc;
select num,nid,count(*)
排序:
order by desc/asc
条件:
select * from 表 where id > 1 and name != 'alex' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表)连表:
left join
right join
inner join
临时表:
select * from (select * from tb where id<10 ) as B;
通配符:
select * from 表 where name like 'ale%'; #ale开头的所有（多个字符）
select * from 表 where name like 'ale_'; #ale开头的所有（一个字符）
分页: limit
组合: union() ,union all()
select id,name from t1 union select num,name from t2 #自动去重
select sid,sname from student union all select sid,sname from student #不去重
4.视图(虚拟)
1.创建
create view 视图名称 as SQL
2.修改
alter view 视图名称 as SQL
3.删除
drop view 视图名称
5.触发器
当对某张表做： 增删改查，可以使用触发器自定义关联行为
insert into tb (
delimiter //
create trigger t1 BEFORE insert on student for each row
BEGIN
INSERT into teacher(tname) values(NEW.sname);
INSERT into teacher(tname) values(NEW.sname);
INSERT into teacher(tname) values(NEW.sname);
END //
delimiter ;
)
6.函数
内置函数:
执行函数 select CURDATE();
自定义函数（有返回值）
delimiter \\
create function f1(
i1 int,
i2 int)
return int
BEGIN
declare num int default 0;
set num = i1 +i2
return(num);
END \\
delimiter ;
select f1(1,100)
7.存储过程
保存在MySQL上的一个别名
1.创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END //
delimiter ;
call p1() #执行存储过程
或者 cursor.callproc('p1')
2.接收的参数:
1.in 仅用于传入参数用
2.out 仅用于返回值用
3.inout 既可以传入又可以当做返回值
3.事务
delimiter \\
create procedure p4(
out p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
delete from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END \\
4. 游标
delimiter //
create procedure p6()
begin
declare row_id int; ---- 自定义变量1
declare row_num int; ----自定义变量2
declare done INT default FALSE;
declare temp int;
declare my_cursor CURSOR for select id,num from A;
declare continue handler for not found set done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimiter;
动态执行SQL（防止SQL注入）
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL=格式化 tpl + arg
3. 执行SQL语句
set @xo = arg;
PREPARE xxx FROM 'select from students where sid > ?'
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimiter ;
call p7("select * from tb where id > ?",9)
8.索引
1.作用
-- 约束
-- 加速查找
2.分类
-- 主键索引：加速查找 + 不能为空 + 不能重复
-- 普通索引：加速查找
-- 唯一索引： 加速查找 + 不能重复 + 可以为空
-- 联合索引（多列）：
-- 联合主键索引
-- 联合唯一索引
-- 联合普通索引
普通索引：
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_name (name)
)
或者 create index index_name on userinfo(name)
create index ix_extra on userinfo(extra(32)); ##对于创建索引时如果是BLOB和TEXT类型，必须制定length
drop index_name on userinfo
show index from userinfo;
唯一索引
create table n1(
nid int not null auto_incrment primary key,
name varchar(32) not null,
email varchar(32) not null,
extra text,
unique ix_name (name)
)
或者 create unique index index_name on userinfo(name)
drop unique index index_name on userinfo
主键索引
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_name (name)
)
alter table userinfo add primary key(name);
alter table userinfo drop primay key;
alter table userinfo modify age int,drop primary key;
组合索引（最左前缀匹配）：
create unique index 索引名称 on 表名（列名，列名）
drop unique index 索引名称 on 表名
create index ix_name_email on userinfo(name,email,)
最左前缀匹配
select * from userinfo where name='alex' #最左前缀
select * from userinfo where name='alex' and email='alex@qq.com'
select * from userinfo where email='alex@qq.com' # 这个查不到！！！
组合索引效率 > 索引合并
组合索引
-（name,email,）
select * from userinfo where name='alex' and email='alex@qq.com'
select * from userinfo where name='alex'
索引合并
-name
select * from userinfo where name='alex' and email='alex@qq.com'
select * from userinfo where name='alex'
selcct * from userinfo where email='alex@qq.com'
名词：
覆盖索引：
-- 在索引文件中直接获取数据
索引合并：
-- 把多个单列索引合并使用
3.分类（某种格式存储）
hash索引：（规定的数字索引）
单值快
范围
btree索引：btree索引
二叉树
建立索引：
-a. 额外的文件保存特殊的数据结构
-b. 查询快，插入更新删除慢
-c. 命中索引
select * from userinfo where email='abc' ！！
select * from userinfo where email like 'abc' 慢
4.时间
执行计划： 让mysql预估执行操作
all < index < range < index_merge < ref < eq_ref < system/const
慢：
explain select * from userinfo where name='alex'
type: ALL(全表扫描)
快：
type:const(走索引)
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
5.慢日志查询
a.配置mysql自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
查看当前配置信息： show variables like '%query%'
修改当前配置： set global 变量名 = 值
6.分页
a.select * from userinfo limit 200,10'
b.select * from userinfo limit where id in (select id from userinfo limit 2000,10)
c.select * from userinfo where id<max_id order by id desc limit 10;
d.select * from userinfo where id in (
select id from (select id from userinfo where id > max_id limit 30) as N order by N.id desc limit 10
)
