MySQL笔记
3月7日 SQL语句
show databases;
create database book;
use kaka;
show tables;
设置主键 外键时 sid int(10) primary key; foreign key(sid) references student(sid);
drop database kaka;
show create table book \g;
show character set;
show collation like %gbk%; 查看校验规则 _ci比较时大小写不敏感
status;显示状态
更改乱码问题 my.ini [client]下打default-character-set=gbk [mysqld]下也加 重启服务 就能显示中文了
set names gbk；
desc user;显示表结构
分页显示 select *from emp limit 0,5;从第一条开始共显示5条；
select concat(id," ",name) as 'info' from users; 合并列显示
select distinct deptno from users;去除重复显示
select ename,(sal+ifnull(comm,0))*12 from emp; ifnull(a,b)如果不为空，显示a,为空显示b
order by
select * from emp where ename like '____'; #模糊查询
select * from emp where job = "CLERK" and deptno = 30; #多条件
select * from emp where not job = "CLERK" #反
select * from emp limit 0,5; 从第一条开始 取5条
select round(rand()*100);0-100随机数
select lpad(str,n,'');填充
ltrim();rtrim();trim();
substring(str,x,n);从x(从1开始)后取n个字符
mod(x,y);取模
ceil(x);进小数
floor(y);舍小数
max();min();avg();count();
curdate();curtime();now();
select date_format(now(),'%Y年%m月%d日 %T');
select * from emp order by rand() limit 1;
md5('jerry');加密
select sum(sal) from emp group by job;
多表连接 等值连接 不等值连接
自连接 自己表复制一张起别名当新表用
交叉连接
自然连接
select e.ename,d.dname
from emp e
join dept d
using(deptno); on e.depno=d.depno;
左连接 右连接 left join right join full join 就是显示左边，右边的内容 包括空
嵌套查询(子查询) from后面 where后面都可以用一个select语句代替
在子查询中 要是用到嵌套 比如sid=(select blablabla) 当blablabla有多个结果时，=号就不能用了，
采用IN 或者 NOT IN用法
在嵌套查询中 若要在where语句中用到count(blabla)=blabla 是不能直接应用的 要用到having count(blablabla)
向表中添加主键时 alter table student add primary key(sid);
alert table student add constraint fk_index_cid foreign key(cid) references sclass(id) on delete restrict on update restrict;
ALERT table student ADD CONSTRAINT fk_index_cid FOREIGN KEY(cid) REFERENCES sclass(id) ON DELETE RESTRICT on UPDATE RESTRICT;
并且having 语句要放在group by语句后面
insert into student values(null,'nnn',16,'beijing','nnn@tom.com',7,'2000-10-10',now());
date_format(now(),'%m')来操作日期比较简单
3月8日
简单的存储过程
delimiter $$ 声明定界符 用$$表示结束
create procedure Hello()
BEGIN
SELECT 'hello';
END$$ 遇到了$$表示结束
delimiter ; 把定界符还原为;
若要是有参数的过程
create procedure add1(in i int,in j int)
参数 默认为IN 输入
delimiter $$
create procedure add1(i int,j int)
begin
select i+j;
end$$
delimiter ;
delimiter $$
create procedure add2(IN i int,IN j int,OUT z int)
begin
set z=i+j; set 来赋值
end$$
delimiter ;
set @abc=0; 声明一个变量abc 在前面加一个@
call add2(5,7,@abc);
select @abc;
show create porcedure hello;
show procedure status;
drop procedure cutcut;
3月9日
IF IF IF IF IF
if(a>b,a,b)
case when a>b then set z=a when a<b then set z=b;
if(blablabla) then set bla1=blablabla;
elseif(blablabla) then set
else set
endif;
delimiter $$
create procedure game(in a int,out s varchar(40))
begin
set @randomnum=floor(rand()*100);
if(a=@randomnum)
then set s=concat('you win!!The right num is',@randomnum);
else set s=concat('you lose!!The right num is',@randomnum);
end if;
end$$
CASE CASE CASE CASE
case rs
when 1 then select 'one'
when 2 then select 'two'
else select 'four'
end case;
FOR FOR FOR FOR
s1s2s3:LOOP 前面开始声明一个循环名，后LOOP声明开始
set rs = rs+1;
IF rs =3 声明循环结束条件
THEN LEAVE s1s2s3;
END IF;
END LOOP s1s2s3; 循环结束
WHILE DO WHILE循环 END WHILE;
REPEAT UNTIL REPEAT循环 END REPEAT;
从0开始加到i
delimiter $$
create procedure sum_num(in i int,out r int)
begin
set @j=0;
set r=0;
s1:LOOP
set r=r+@j; 每次赋值前要加set
set @j=@j+1;
IF @j>i THEN IF语句后加操作要有THEN
LEAVE s1;
END IF;
END LOOP s1;
end$$
delimiter $$
create procedure exam_repeat()
begin
declare i int default 0;
declare re int default 0;
repeat
set i= i+1;
set re = re+i;
until i>=10
END REPEAT;
select re;
END$$
drop procedure test_while;
delimiter $$
create procedure test_while()
begin
declare i int default 0;
declare re int default 0;
while
i<10
DO
set i= i+1;
set re = re+i;
END WHILE;
select re;
end$$
用存储过程实现增删改查,比较实用
drop procedure insert_user;
delimiter $$
CREATE PROCEDURE INSERT_USER(IN uname varchar(20),IN pwd varchar(20),IN age int(3))
BEGIN
IF age between 1 and 99
THEN INSERT INTO user values(null,uname,md5(pwd),age);
END IF;
END$$
drop procedure delete_user;
delimiter $$
create procedure delete_user(in a int)
begin
delete from user where id=a;
insert into delete_log values(null,now(),user());
end$$
delimiter $$
create procedure test_cur()
begin
declare done int default 0;
declare a int;
declare b varchar(20);
declare cur1 cursor for select id,name from user; 定义游标
declare exit handler for not found set no_more=1;
open cur1; 打开游标
repeat
fetch cur1 into a,b;
if(NOT done) THEN
select a,b;
END IF;
until done END REPEAT;
close cur1;
end$$
函数
delimiter $$
create function randInt1(start INT(4),end INT(4))
returns int(4)
begin
return start+(rand()*(end-start));
end$$
delimiter $$
create function random_char()
returns varchar(20)
begin
declare a varchar(1);
declare b varchar(1);
declare c varchar(1);
declare d varchar(1);
set a=char(48+rand()*(122-48+1));
set b=char(48+rand()*(122-48+1));
set c=char(48+rand()*(122-48+1));
set d=char(48+rand()*(122-48+1));
return concat(a,b,c,d);
end$$
触发器
delimiter $$
create trigger t_afterdelete_on_tab1
after delete on user
for each row
begin
insert into delete_log values(null,now(),user());
end$$
delimiter $$
create trigger t_afterdelete_on_user
after delete on user
for each row
begin
insert into tempuser values(old.id,old.name,old.pwd,old.age); NEW，OLD是系统规定
end$$
游标
delimiter $$
create procedure test_cur1(out counts int(4))
begin
declare names varchar(200);
declare no_more int default 0;
declare usercur cursor for select name from user;
declare exit handler for NOT FOUND set no_more=1;
set counts=0;
OPEN usercur;
repeat
fetch usercur into names;
select names;
set counts=counts+1;
until no_more
END repeat;
close usercur;
end$$
利用游标来智能更新员工薪水
drop procedure update_sal();
delimiter $$
create procedure update_sal()
begin
declare counts int default 0;
declare no_more int default 0;
declare sal_judge varchar(200);
declare sal_no varchar(20);
declare sal_count cursor for select sal,empno from emp;
declare exit handler for not found set no_more=1;
open sal_count;
repeat
fetch sal_count into sal_judge,sal_no;
if sal_judge between 1000 and 2000
then update emp set sal=sal*1.2 where empno=sal_no;
END IF;
IF sal_judge between 2001 and 3000
then update emp set sal=sal*1.3 where empno=sal_no;
END IF;
IF sal_judge >3001
then delete from emp where empno=sal_no;
END IF;
set counts=counts+1;
until no_more
END repeat;
CLOSE sal_count;
end$$
3月14日 mysql的管理
UNION 可以去掉重复项 UNION ALL就没了
select id from t1
union
select i from t2
union
select i from t3;
用户权限管理
grant all privileges on *.* to tom@localhost identified by '123' with grant option
grant all privileges on testemp.* to 'common'@'%' identified by '123';
一个%号代表任何IP都能登录 别人登录时候用mysql -u common -h 192.168.2.135(目标IP) -p
show grants for z5@'%';
set password =password('123');
set password for 'common'@'%'=password();
flush priviledges;
drop user z1@localhost;
存储引擎 show variables like 'table_type'
show engines \g
myisam不支持事务,但以select insert为主速度快
check table repair table myisamchk -r -o
optimize
外键
RESTRICT 是限制 如果有子存在 不允许改变
CASCADE 级联操作 如果父改变,子表也相应改变
SET NULL
NO ACTION
create table student1(
id int auto_increment,
cid int not null,
name varchar(20) not null,
primary key(id),
KEY idex_fk_cid(cid),
FOREIGN KEY(cid) REFERENCES s_class(id)
on delete restrict on update cascade); //RESTRICT CASCADE
memory存储
create table emp_memory engine=memory
select * from emp;
3月15日
事务,保证数据正确性 以防止大量并发情况下出错
start transaction ||begin
commit提交事务(关闭事务)
rollback
savepoint
开启事务以后,相当于到了世外桃源,自己能看到自己的小世界内变化,不受外界影响,也不能影响外界,直到进行commit操作回到现实
start transaction; 开始事务,下面的内容就是还没有提交
select sal from user where name='aaa';
if(sal>=100)
{
update user set sal=sal-100 where name='aaa'; 更新后只能自己看到
update user set sal=sal+100 where name='bbb';
}
rollback;
commit;
set auto commit=0;自动提交取消 自己再一次手动提交能显著提高速度
事务常见错误
脏读 读到了未提交的数据
不可重复读
READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE
set session transaction isolation level serializable;设置事务安全性最高 速度也最慢
索引 提高查询速度
比如:limit 1
默认为全表查询
create index indexname on emp(id);
一般主键跟唯一性约数自动有索引
分析查询语句
explain select * from emp where ename='king' \G;
drop index i_ename;
备份数据库 完全备份 增量备份
c:\>mysqldump -u root -p -l -F tao(数据库名)>d:\backup\20110315\tao.dmp
c:\>mysqldump -u root -p --default-character-set=gbk tao(数据库名)>d:\backup\20110315\tao.dmp
恢复
c:\mysql -u root -p tao<d:\backup\20110315\tao.dmp
从外导入数据 一个\为转义符 \\表示一个\
load data infile 'd:\\data.txt' into table exam_innodb fields terminated by ',' lines terminated by '\n'
show databases;
create database book;
use kaka;
show tables;
设置主键 外键时 sid int(10) primary key; foreign key(sid) references student(sid);
drop database kaka;
show create table book \g;
show character set;
show collation like %gbk%; 查看校验规则 _ci比较时大小写不敏感
status;显示状态
更改乱码问题 my.ini [client]下打default-character-set=gbk [mysqld]下也加 重启服务 就能显示中文了
set names gbk；
desc user;显示表结构
分页显示 select *from emp limit 0,5;从第一条开始共显示5条；
select concat(id," ",name) as 'info' from users; 合并列显示
select distinct deptno from users;去除重复显示
select ename,(sal+ifnull(comm,0))*12 from emp; ifnull(a,b)如果不为空，显示a,为空显示b
order by
select * from emp where ename like '____'; #模糊查询
select * from emp where job = "CLERK" and deptno = 30; #多条件
select * from emp where not job = "CLERK" #反
select * from emp limit 0,5; 从第一条开始 取5条
select round(rand()*100);0-100随机数
select lpad(str,n,'');填充
ltrim();rtrim();trim();
substring(str,x,n);从x(从1开始)后取n个字符
mod(x,y);取模
ceil(x);进小数
floor(y);舍小数
max();min();avg();count();
curdate();curtime();now();
select date_format(now(),'%Y年%m月%d日 %T');
select * from emp order by rand() limit 1;
md5('jerry');加密
select sum(sal) from emp group by job;
多表连接 等值连接 不等值连接
自连接 自己表复制一张起别名当新表用
交叉连接
自然连接
select e.ename,d.dname
from emp e
join dept d
using(deptno); on e.depno=d.depno;
左连接 右连接 left join right join full join 就是显示左边，右边的内容 包括空
嵌套查询(子查询) from后面 where后面都可以用一个select语句代替
在子查询中 要是用到嵌套 比如sid=(select blablabla) 当blablabla有多个结果时，=号就不能用了，
采用IN 或者 NOT IN用法
在嵌套查询中 若要在where语句中用到count(blabla)=blabla 是不能直接应用的 要用到having count(blablabla)
向表中添加主键时 alter table student add primary key(sid);
alert table student add constraint fk_index_cid foreign key(cid) references sclass(id) on delete restrict on update restrict;
ALERT table student ADD CONSTRAINT fk_index_cid FOREIGN KEY(cid) REFERENCES sclass(id) ON DELETE RESTRICT on UPDATE RESTRICT;
并且having 语句要放在group by语句后面
insert into student values(null,'nnn',16,'beijing','nnn@tom.com',7,'2000-10-10',now());
date_format(now(),'%m')来操作日期比较简单
3月8日
简单的存储过程
delimiter $$ 声明定界符 用$$表示结束
create procedure Hello()
BEGIN
SELECT 'hello';
END$$ 遇到了$$表示结束
delimiter ; 把定界符还原为;
若要是有参数的过程
create procedure add1(in i int,in j int)
参数 默认为IN 输入
delimiter $$
create procedure add1(i int,j int)
begin
select i+j;
end$$
delimiter ;
delimiter $$
create procedure add2(IN i int,IN j int,OUT z int)
begin
set z=i+j; set 来赋值
end$$
delimiter ;
set @abc=0; 声明一个变量abc 在前面加一个@
call add2(5,7,@abc);
select @abc;
show create porcedure hello;
show procedure status;
drop procedure cutcut;
3月9日
IF IF IF IF IF
if(a>b,a,b)
case when a>b then set z=a when a<b then set z=b;
if(blablabla) then set bla1=blablabla;
elseif(blablabla) then set
else set
endif;
delimiter $$
create procedure game(in a int,out s varchar(40))
begin
set @randomnum=floor(rand()*100);
if(a=@randomnum)
then set s=concat('you win!!The right num is',@randomnum);
else set s=concat('you lose!!The right num is',@randomnum);
end if;
end$$
CASE CASE CASE CASE
case rs
when 1 then select 'one'
when 2 then select 'two'
else select 'four'
end case;
FOR FOR FOR FOR
s1s2s3:LOOP 前面开始声明一个循环名，后LOOP声明开始
set rs = rs+1;
IF rs =3 声明循环结束条件
THEN LEAVE s1s2s3;
END IF;
END LOOP s1s2s3; 循环结束
WHILE DO WHILE循环 END WHILE;
REPEAT UNTIL REPEAT循环 END REPEAT;
从0开始加到i
delimiter $$
create procedure sum_num(in i int,out r int)
begin
set @j=0;
set r=0;
s1:LOOP
set r=r+@j; 每次赋值前要加set
set @j=@j+1;
IF @j>i THEN IF语句后加操作要有THEN
LEAVE s1;
END IF;
END LOOP s1;
end$$
delimiter $$
create procedure exam_repeat()
begin
declare i int default 0;
declare re int default 0;
repeat
set i= i+1;
set re = re+i;
until i>=10
END REPEAT;
select re;
END$$
drop procedure test_while;
delimiter $$
create procedure test_while()
begin
declare i int default 0;
declare re int default 0;
while
i<10
DO
set i= i+1;
set re = re+i;
END WHILE;
select re;
end$$
用存储过程实现增删改查,比较实用
drop procedure insert_user;
delimiter $$
CREATE PROCEDURE INSERT_USER(IN uname varchar(20),IN pwd varchar(20),IN age int(3))
BEGIN
IF age between 1 and 99
THEN INSERT INTO user values(null,uname,md5(pwd),age);
END IF;
END$$
drop procedure delete_user;
delimiter $$
create procedure delete_user(in a int)
begin
delete from user where id=a;
insert into delete_log values(null,now(),user());
end$$
delimiter $$
create procedure test_cur()
begin
declare done int default 0;
declare a int;
declare b varchar(20);
declare cur1 cursor for select id,name from user; 定义游标
declare exit handler for not found set no_more=1;
open cur1; 打开游标
repeat
fetch cur1 into a,b;
if(NOT done) THEN
select a,b;
END IF;
until done END REPEAT;
close cur1;
end$$
函数
delimiter $$
create function randInt1(start INT(4),end INT(4))
returns int(4)
begin
return start+(rand()*(end-start));
end$$
delimiter $$
create function random_char()
returns varchar(20)
begin
declare a varchar(1);
declare b varchar(1);
declare c varchar(1);
declare d varchar(1);
set a=char(48+rand()*(122-48+1));
set b=char(48+rand()*(122-48+1));
set c=char(48+rand()*(122-48+1));
set d=char(48+rand()*(122-48+1));
return concat(a,b,c,d);
end$$
触发器
delimiter $$
create trigger t_afterdelete_on_tab1
after delete on user
for each row
begin
insert into delete_log values(null,now(),user());
end$$
delimiter $$
create trigger t_afterdelete_on_user
after delete on user
for each row
begin
insert into tempuser values(old.id,old.name,old.pwd,old.age); NEW，OLD是系统规定
end$$
游标
delimiter $$
create procedure test_cur1(out counts int(4))
begin
declare names varchar(200);
declare no_more int default 0;
declare usercur cursor for select name from user;
declare exit handler for NOT FOUND set no_more=1;
set counts=0;
OPEN usercur;
repeat
fetch usercur into names;
select names;
set counts=counts+1;
until no_more
END repeat;
close usercur;
end$$
利用游标来智能更新员工薪水
drop procedure update_sal();
delimiter $$
create procedure update_sal()
begin
declare counts int default 0;
declare no_more int default 0;
declare sal_judge varchar(200);
declare sal_no varchar(20);
declare sal_count cursor for select sal,empno from emp;
declare exit handler for not found set no_more=1;
open sal_count;
repeat
fetch sal_count into sal_judge,sal_no;
if sal_judge between 1000 and 2000
then update emp set sal=sal*1.2 where empno=sal_no;
END IF;
IF sal_judge between 2001 and 3000
then update emp set sal=sal*1.3 where empno=sal_no;
END IF;
IF sal_judge >3001
then delete from emp where empno=sal_no;
END IF;
set counts=counts+1;
until no_more
END repeat;
CLOSE sal_count;
end$$
3月14日 mysql的管理
UNION 可以去掉重复项 UNION ALL就没了
select id from t1
union
select i from t2
union
select i from t3;
用户权限管理
grant all privileges on *.* to tom@localhost identified by '123' with grant option
grant all privileges on testemp.* to 'common'@'%' identified by '123';
一个%号代表任何IP都能登录 别人登录时候用mysql -u common -h 192.168.2.135(目标IP) -p
show grants for z5@'%';
set password =password('123');
set password for 'common'@'%'=password();
flush priviledges;
drop user z1@localhost;
存储引擎 show variables like 'table_type'
show engines \g
myisam不支持事务,但以select insert为主速度快
check table repair table myisamchk -r -o
optimize
外键
RESTRICT 是限制 如果有子存在 不允许改变
CASCADE 级联操作 如果父改变,子表也相应改变
SET NULL
NO ACTION
create table student1(
id int auto_increment,
cid int not null,
name varchar(20) not null,
primary key(id),
KEY idex_fk_cid(cid),
FOREIGN KEY(cid) REFERENCES s_class(id)
on delete restrict on update cascade); //RESTRICT CASCADE
memory存储
create table emp_memory engine=memory
select * from emp;
3月15日
事务,保证数据正确性 以防止大量并发情况下出错
start transaction ||begin
commit提交事务(关闭事务)
rollback
savepoint
开启事务以后,相当于到了世外桃源,自己能看到自己的小世界内变化,不受外界影响,也不能影响外界,直到进行commit操作回到现实
start transaction; 开始事务,下面的内容就是还没有提交
select sal from user where name='aaa';
if(sal>=100)
{
update user set sal=sal-100 where name='aaa'; 更新后只能自己看到
update user set sal=sal+100 where name='bbb';
}
rollback;
commit;
set auto commit=0;自动提交取消 自己再一次手动提交能显著提高速度
事务常见错误
脏读 读到了未提交的数据
不可重复读
READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE
set session transaction isolation level serializable;设置事务安全性最高 速度也最慢
索引 提高查询速度
比如:limit 1
默认为全表查询
create index indexname on emp(id);
一般主键跟唯一性约数自动有索引
分析查询语句
explain select * from emp where ename='king' \G;
drop index i_ename;
备份数据库 完全备份 增量备份
c:\>mysqldump -u root -p -l -F tao(数据库名)>d:\backup\20110315\tao.dmp
c:\>mysqldump -u root -p --default-character-set=gbk tao(数据库名)>d:\backup\20110315\tao.dmp
恢复
c:\mysql -u root -p tao<d:\backup\20110315\tao.dmp
从外导入数据 一个\为转义符 \\表示一个\
load data infile 'd:\\data.txt' into table exam_innodb fields terminated by ',' lines terminated by '\n'
