mysql 总结

来源：互联网发布：数据库视频下载编辑：程序博客网时间：2024/06/16 08:50

数据库总结：

http://blog.csdn.net/dcj0913/article/details/39252153

Mysql基本语法：

create table Emp( create table Empess(

id int auto_increment PRIMARY key not null, Eid int not null,

Ename VARCHAR(20), Ename varchar(50);

Esex VARCHAR(2), Esex varchar(4)

Eage INT(2), );

Etel INT(11),

Edesc VARCHAR(50)

);

-- 给创建好的表添加字段

alter table Emp add Eaddr VARCHAR(50)

-- 修改列名

alter table Emp change Edesc Ehobby VARCHAR(50)

-- 删除表已有字段

alter table Emp drop Ehobby

-- 修改已有字段类型

alter table Emp modify Etel VARCHAR(50)

-- 添加数据到表中

insert into Emp(Ename,Esex,Eage,Etel,Eaddr) values('张一','男',12,'13510441111','湖南')

insert into Emp(Ename,Esex,Eage,Etel,Eaddr) values('张二','男',13,'13510441110','东莞')

insert into Emp(Ename,Esex,Eage,Etel,Eaddr) values('张三','男',13,'13510441110','东莞')

-- 查询表中的所有信息

select * from Emp

-- 删除信息

delete from Emp where id=1

-- 将Eid作为表的主键

alter table Empess add PRIMARY key(Eid)

-- 添加唯一约束

alter table Empess add constraint pn_uk unique key(Eid)

-- 删除唯一约束

alter table Empess drop index pn_uk

alter table Empess add Eaddr VARCHAR(50)

-- 主键的值自行增长

alter table Empess change Eid Eid int auto_increment

insert into Empess(Eid,Ename,Esex) values (1,'李四','女')

insert into Empess(Eid,Ename,Esex) values (2,'李三','男')

insert into Empess(Ename,Esex) values ('李三','男')

-- 添加外键约束

alter table Emp add constraint c_fk foreign key (Eid)REFERENCES Empess(Eid)

-- 删除外键约束

alter table Emp drop foreign key c_fk

select * from Empess

复制表结构和数据

创建一张表名为account_es，并将table表的数据和结构复制给account表;

格式： create table account_es as select * from account;

只复制表结构： create table account_es like account;

create table as select * from account where 1=2;

alter

alter table redis add age int default 0

alter table bol drop n_chinese

alter table bol change n_chinese n_English int; -- 改变字段

alter table bol modify n_English int; -- 修改字段的类型

exists , in 区别

-- 如果主表数据大,子表数据小,用IN来查询,效率高

-- 如果主表数据小,子表数据大,用EXISTS来查询,效果高

select * from person where id in(select id from person_1 where id > 3)

select * from person p where exists (select * from person_1 p1 where p.id = p1.id and p1.id > 3);

sql之left join、right join、inner join的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

连接查询

条件 :where

group by : HAVING

select count(*),`name` from operation GROUP BY `name` having count(*)>1

join : on

内连接查询

select *from number p inner join number_1 p1 on n.id = n1.id

外连接查询

左外连接不管是否符合条件,左边表记录都会显示.右边表要符合条件才会显示

left join

select * from number n left join number_1 n1 on n.id = n1.id

右外连接不管是否符合条件,左边表记录都会显示.右边表要符合条件才会显示

right join

select * from number n right join number_1 n1 on n.id = n1.id

全外连接 inner join 只返回两个表中联结字段相等的行

truncate和 delete不同点:

1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

3.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值

Truncate 可以将数据全部清空并且将ID也从头开始

Delete将表中数据清空但ID不会从头开始

4.想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.

5..速度,一般来说: drop>; truncate >; delete

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

运算符

包括（算术运算符，比较运算符，逻辑运算符，位运算符等）

http://blog.csdn.net/wxq544483342/article/details/51476348

习题：

CREATE TABLE student (

id VARCHAR(8),

Tname VARCHAr(20),

class VARCHAR(20),

chinese int(5),

math int(5)

);

drop table student;

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090001', '张三', '五年级A班', 80 ,90);

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090002', '李四', '五年级A班', 60 ,75);

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090003', '王五', '五年级A班', 90 ,95);

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090004', '赵红', '五年级B班', 70 ,90);

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090004', '李白', '五年级B班', 85 ,80);

INSERT INTO STUDENT (ID, Tname, CLASS, CHINESE, MATH) VALUES ('20090005', '王蓝', '五年级B班', null ,70);

select * from student

-- A班哪些学生的数学成绩高于B班数学成绩的最小值

select * FROM student where class='五年级A班' and math >(select min(math) from student where class='五年级B班');

-- 除此之外，我们还可以使用SOME或ANY 注意：ANY和 SOME 的作用和使用方式完全相同

select * from student where class='五年级A班' and math > any (select math from student where class='五年级B班');

-- 除此之外，我们还可以使用ALL

select * from student where class='五年级A班' and math > all(select math from student where class='五年级B班');

-- 忽略了null值

select * from student where class='五年级A班' and chinese <(select min(chinese) from student where class='五年级B班');

-- 没有忽略null值

select * from student where class='五年级A班' and chinese < all(select chinese from student where class='五年级B班');

视图:

首先是一缍虚拟表,可以来源与表与视图.

优点:

1.简化操作

2.定制数据字段

3.数据安全(限定权限) (oracle)

4.数据分割

缺点:

1.性能不高

2.数据修改

创建视图：（create）

格式：CREATE VIEW 视图名称 AS SQL语句

CREATE VIEW view_student AS SELET nid, name FROM A;

删除视图：(drop)

格式：DROP VIEW 视图名称

DROP VIEW view_student;

修改视图:(alter)

格式：ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW view_student AS SELET A.nid from A

视图的使用和普通表一样，由于视图是虚拟表，所以无法对其真实表进行创建、更新和删除操作，仅做查询用。

select * from view_student;

union 并集，去掉重复的数据

union all 不会去掉重复的数据

mysql循环：

while 循环、loop循环和repeat循环

1. loop循环

drop procedure if exists lopp;

create procedure lopp()

begin

declare i int ;

set i = 1;

lp1 : LOOP// lp1 为循环体名称 LOOP 为关键字insert into user_profile (uid) values (i);

set i = i+1;

if i > 30 then

leave lp1;// 离开循环体

end if;

end LOOP;　// 结束循环

end

2.while 循环

drop procedure if exists wk; // 删除已有的存储过程

create procedure wk()　　　　　　//　创建新的存储过程

begin

declare i int;　　　　　　　　　　// 变量声明

set i = 1;　　　　　

while i < 11 do // 循环体

insert into user_profile (uid) values (i);

set i = i +1;

end while;

end 　　　　　　　　　　　　　　// 结束定义语句

触发器

http://www.ttlsa.com/mysql/application-of-mysql-triggers/

http://www.bcty365.com/content-35-1270-1.html

对某个表进行【增/删/改】操作的前后触发一些操作即为触发器，如果希望触发增删改的行为之前或之后做操作时，可以使用触发器，触发器用于自定义用户对表的行进行【增/删/改】前后的行为。

old,new都是代表当前操作的记录行，你把它当成表名

# 插入前CREATE TRIGGER 名字 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

# 插入后CREATE TRIGGER 名字 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

...

END

# 删除前TRIGGER 名字 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

...

END

# 删除后CREATE TRIGGER 名字 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

...

END

# 更新前CREATE TRIGGER 名字 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

# 更新后CREATE TRIGGER 名字 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

删除触发器：drop trigger 名字;

例子：

drop trigger tri_test;

create trigger tri_test

after

insert on tri_emp

for each row

BEGIN

if new.sex = '男'or'女' then

update tri_emp1 set count = count + 1 where sex = new.sex;

else delete from tri_emp where id = new.id;

end if ;

END

select * from tri_emp;

INSERT into tri_emp VALUES(1,'一号','男');

INSERT into tri_emp VALUES(2,'一号','女');

INSERT into tri_emp VALUES(3,'一号','未知');

INSERT into tri_emp VALUES(4,'一号','男');

INSERT into tri_emp VALUES(9,'三号','男');

.注意事项

(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

1.show procedure status 显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name 显示某一个mysql存储过程的详细信息

3.参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名数据类形...])

in 在存储过程中改变值，外面调用参数变量不会改变

out 在存储过程中，不能传入值，默认都为NULL,在过程内部改变，外面调用参数变量会一起改变

inout 既可输入值，又可输出输值

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

declare代表创建一个局部变量

存储过程

1.无参数的存储过程

-- 创建存储过程

create procedure p1()

begin

select * from tl;

end

-- 执行存储过程

call p1();

in 仅用于传入参数用

out 仅用于返回值用

inout 既可以传入又可以当作返回值

2.有参数存储过程

例题一：

create procedure procedure_1(in a int,inout b int )

BEGIN

arithmetic:LOOP

while a<100 DO

set a=a+1;

set b=b+a;

if a>=100 THEN

leave arithmetic;

end if;

end while;

end loop;

END;

set @b=0;

call procedure_1(0,@b);

select @b;

例题二：

drop procedure procedure_2;

create procedure procedure_2(in a VARCHAR(10),inout b VARCHAR(50))

BEGIN

if a='happy' then set b=CONCAT(a,'every day');

elseif a='bad' then set b=CONCAT(a,'no happy');

elseif a='good' then set b=CONCAT(a,'study well');

else set b='no exists';

end if;

END;

set @b='noon';

call procedure_2('good',@b);

select @b;

游标

drop procedure if exists StatisticStore;

CREATE PROCEDURE StatisticStore()

BEGIN

declare c int; -- 创建接收游标数据的变量

declare n varchar(20);

declare total int default 0; -- 创建总数变量

declare done int default false; -- 创建结束标志变量

declare cur cursor for select name,count from store where name = 'iphone'; -- 创建游标

declare continue HANDLER for not found set done = true; -- 指定游标循环结束时的返回值

set total = 0; -- 设置初始值

open cur; -- 打开游标

read_loop:loop -- 开始循环游标里的数据

fetch cur into n,c; -- 根据游标当前指向的一条数据

if done then -- 判断游标的循环是否结束

leave read_loop; -- 跳出游标循环

end if;

set total = total + c; -- 获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，

end loop; -- 结束游标循环

close cur; -- 关闭游标

select n,total; -- 输出结果

END;

call StatisticStore();

题目：

1.查询班级ID,放入游标

2.循环游标（每一个游标插入10个学生）

3.10个学生是通过while循环插入

BEGIN

DECLARE mark int DEFAULT 0;

DECLARE a int DEFAULT 1; #变量a,用于添加学生数量

DECLARE b varchar(55) DEFAULT '';#变量b,用于存班级ID

DECLARE d int DEFAULT 1;

DECLARE c cursor for select id from clazz;#游标，获取班级ID

DECLARE continue HANDLER for not found set mark = 1; #游标查询不到值时执行

drop table if exists student; #删除学生表

create table student( #创建学生

id varchar(55),

name varchar(55),

d_time TIMESTAMP,

clazz_id varchar(55),

` desc` varchar(55)

);

OPEN c; #打开游标

aa: LOOP

if mark = 1 then

LEAVE aa;

end if;

FETCH c into b; #把游标c第一个下标值，赋给变量b

while d<=10 do #concat 把字符串相加

insert into student values(concat('学生id',a),concat('学生名字',a),now(),b,'aaaa');

set d = d +1;

set a = a +1;

end while;

set d = 1;

end loop;

CLOSE c;

END

第一个例题：

DECLARE no_more_record INT DEFAULT 0;

declare aid int DEFAULT 1;

declare aname varchar(50) default '';

DECLARE aaaaa CURSOR FOR select id,name from emp;

首先这里对游标进行定义声明定义

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;

这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1 OPEN aaaaa;

接着使用OPEN打开游标

FETCH aaaaa INTO aid,aname;

把第一行数据写入变量中,游标也随之指向了记录的第一行 WHILE no_more_record != 1 DO

select aid,aname;

FETCH aaaaa INTO aid,aname;

第二个例题：

drop procedure if exists procedure_3;

create procedure procedure_3()

begin

declare no_found int default 1;

declare Cmath int default 0;

declare Cname VARCHAR(20) default '';

declare swim cursor for select Tname,math from student;

declare CONTINUE HANDLER for not found SET no_found=0;

OPEN swim;

FETCH swim into Cname,Cmath;

while no_found!=0 do

select Cname,Cmath;

FETCH swim into Cname,Cmath;

end while;

CLOSE swim;

end;

call procedure_3();

第三个例题：

drop procedure procedure_4;

create procedure procedure_4()

BEGIN

declare no_found int default 1;

declare Cname VARCHAR(10) default '';

declare swim cursor for select Tname from student;

declare CONTINUE HANDLER for not found set no_found=0;

open swim;

a:LOOP

fetch swim into Cname;

if no_found=0 then

leave a;

end if;

select Cname;

end loop;

close swim;

END;

call procedure_4();

第四个例题：

drop procedure if exists procedure_5;

create procedure procedure_5()

BEGIN

declare no_found int default 0;

declare E_money int ;

declare money cursor for select e_money from salary;

declare continue HANDLER for not found set no_found=1;

open money;

fetch money into E_money;

while no_found!=1 DO

select E_money;

fetch money into E_money ;

end while;

close money;

END

call procedure_5();

函数

例题一：

create function my_function(a double,b double ,c int)

returns double

BEGIN

declare num double;

case when c=1 then set num=a+b;

when c=2 then set num=a-b;

when c=3 then set num=a*b;

else set num=4;

end case;

return num;

END

-- 运行函数

select my_function(3,2,2);

drop function my_function;

例题二：

create function my_function5(a int,b int)

returns INT

BEGIN

declare num int;

arithmetic:LOOP

set a=a+1;

set num=a*b;

if a>100 then

leave arithmetic;

end if;

end loop;

return num;

END

drop function my_function5;

select my_function5(5,3);

例题三：

create function my_function_4(a int,b int)

returns INT

BEGIN

declare sum int;

while a<1000000 do

set a=a+1;

insert into aaaa values(a,if(a=1,0,2),4);

set sum=a+2;

end while;

return sum;

END

select my_function_4(1,1);

drop FUNCTION my_function_4

索引

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL索引的类型

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似

索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

最后总结一下，MySQL只对操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)

索引:

1.索引为了加快数据检索

2.惟一性

3.加快连接(inner join ,left join ,right join ,group by)

索引类型:

聚簇主键属于聚簇类型与数据绑定在一起,

一个表,只能一个聚簇索引. 自动会按索引把数据排序好

1,插入速度慢

2,存储空间变大

3,修改速度变慢

4,删除速度变慢

一个列可以创建多个索引

create index index_name444 on dept(id ,desc);

drop index index_name on dept;

drop index index_name1 on dept;

drop index tableName.index_name sqlServer

drop index index_name on tableName mysql

非聚簇不是与数据绑定在一起一个表,有多个非聚簇索引

CREATE TABLE aaaa (

id int(11) NOT NULL ,

e_name varchar(10) not null,

e_desc varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

parentId int(11) NULL DEFAULT NULL ,

PRIMARY KEY (id,e_name)

)

create index index_name on person(id ,asc);

describe person;

create index index_name1 on person(sex ,desc);

drop index index_name1 on person

drop index index_name on person

Explain(索引)

explain tbl_name或：explain SELECT * from select_options

前者可以得出一个表的字段结构等等，后者主要是给出相关的一些索引信息

事务：

start transaction; 开启事务

commit; 提交

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

http://www.jellythink.com/archives/952

http://www.jb51.net/article/44913.htm (事务的详细用法)

事务用于将某些操作的多个SQL语句作为原子性操作，只有出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关

1.MyISAM：不支持事务，用于只读程序提高性能

2.InnoDB：支持ACID事务、行级锁、并发（支持事务）

3.Berkeley DB：支持事务

mysql事物处理实例

mysql的事务处理主要有两种方法
1.用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit 事务确认
2.直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！

可以通过
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
来实现事务的处理。

rollback commit 都可以结束一个事务,(标志一个事务完成)

CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误时马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作；

一个事务是一个连续的一组数据库操作，就好像它是一个单一的工作单元进行。换言之，永远不会是完整的事务，

除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败，则整个事务将失败。

实际上，会俱乐部许多SQL查询到一个组中，将执行所有的人都一起作为事务的一部分。

事务的特性：

事务有以下四个标准属性的缩写ACID，通常被称为：

原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。

一致性: 确保数据库正确地改变状态后，成功提交的事务。

隔离性: 使事务操作彼此独立的和透明的。

持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。

/*autocommit 0禁止自动提交,1自动提交,默认为1*/

/*rollback 回滚事务*/

/*commit 提交事务*/

/*savepoint 设置事务保存点(名字) */

/*rollback to 事务保存点名字回滚事务*/

/*start TRANSACTION 开始新事务,自动提交上一个事务*/

事务隔离级别：

可以使用如下语句设置MySQL的session隔离级别：

select @@tx_isolation;

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

set session transaction isolation level repeatable read;

默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。

如果使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。

你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。

任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

set session transaction isolation level read UNCOMMITTED;

select @@tx_isolation;

事务

http://www.cnblogs.com/ymy124/p/3718439.html

概念：

事务是一系列的sql组成的逻辑单元(而函数,存储过程类似于一个事务,要么全部执行成功,要么

全部执行失败)

属性：

autocommit 事务提交属性 1表示自动提交 0表示不自动提交

设置事务属性：set autocommit = 0/1

查看事务提交属性：select @@autocommit

ACID：

Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、原子性

一组事务，要么成功；要么撤回。

2、稳定性

有非法数据（外键约束之类），事务撤回。

3、隔离性

事务独立运行。(可串行）一个事务处理后的结果，影响了其他事务，

那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性

软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。

可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定什么时候

吧事务保存到日志里。

引擎：

对于引擎一般使用 MyISAM和 InnoDB两种,而myslq 中默认使用的MyISAM

(MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持)

MyISAM和InnoDB存储引擎性能区别并非非常大，针对InnoDB来说，影响性能

的主要是 innodb_flush_log_at_trx_commit 这个选项，假设设置为1的话，那么每次

插入数据的时候都会自己主动提交，导致性能急剧下降，应该是跟刷新日志有关系，

设置为0效率可以看到明显提升

事务级别：

事务级别的分类

(针对多个事务而言)

1 Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

2. Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

3. Repeatable Read（可重复读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4. Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。只要操作产生了锁，就不允许其他事务读取和修改！(可以看看加锁处理分析http://hedengcheng.com/?p=771或者百度网盘http://pan.baidu.com/s/1mgN00Og)

事务级别优劣分析

-- 事务级别从上往下越来越高(越来越安全，效率越来越低)

　事务级别　　　　　　　脏读　　　　　不可重复读　　　幻读

1.读取未提交数据　可　　　　　　可　　　　　　　　可

2.读取提交数据　　　　　不　　　　　　可　　　　　　　　可　　　　　

3.可重复读　　　　　　　不　　　　　　不　　　　　　　　可

4.可串行　　　　　　　　不　　　　　　不　　　　　　　　不

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

可串行化：一个事务一个事务按顺序执行

隔离级别的设置

查看当前回话级别：select @@tx_isolation

查看系统当前隔离时间：select @@global.tx_isolation

设置当前回话的隔离级别:set session transaction isolatin level repeatable read

设置系统当前隔离级别：set global transaction isolation level repeatable read

命令行开启事务的时候 set autocommit = off 或 start transaction

开启事务：

开启事务的方式：begin commit start transaction

提交事务：

(每开启一个新事务就会提交上一个事务)

即提交方式：begin commit start transaction

回滚：

1. 定义保存点---SAVEPOINT 保存点名;

2. 回滚到指定保存点---ROLLBACK TO SAVEPOINT 保存点名:

3. 如果不指定回滚到那个保存点,那么将会回滚整个事务

案例：

set autocommit =0;

begin;

insert into role values(21,"1"); --1

SAVEPOINT point;

insert into role values(22,"2"); --2

insert into role values(23,"3"); --3

insert into role values(24,"4"); --4

ROLLBACK to SAVEPOINT point;

insert into role values(25,"5"); --5

COMMIT;

set autocommit = 1;

---- 如此则会插入 1,5

set autocommit =0;

begin;

insert into role values(21,"1"); --1

SAVEPOINT point;

insert into role values(22,"2"); --2

insert into role values(23,"3",34) --3

insert into role values(24,"4"); --4

insert into role values(25,"5"); --5

ROLLBACK to SAVEPOINT point;

insert into role values(26,"6"); --6

COMMIT;

set autocommit = 1;

-- 在一个事务中如果存在语法错误，那么该语段下面的所有语句都不会执行

所以该例的效果是：存有了(1,2)两条待定的数据，要等待提交才能插入

面试题

http://wenku.baidu.com/view/ebcf5145a8956bec0975e33b.html?re=view

http://www.cnblogs.com/mr-guan/p/5402726.html

http://blog.itpub.net/23890223/viewspace-1223177/（纵转横，横转纵）

http://www.cnblogs.com/jinshui/p/5545695.html 45道练习题

分页

2.实现分页效果 a.每页多少条记录

首页 b.上页下页（1,上一页 2,下一页 3,首页 4,尾页）

上页 c 当前第几页

下页

尾页

drop PROCEDURE if EXISTS mysql_emp1;

-- a 每页记录数

-- b 0首页 1尾页 2上页 3下页

-- c 当前页数

create procedure mysql_emp1(in a int,in b int,inout c int)

BEGIN

declare sum int default 0; -- 总页数

declare num int default 0; -- LIMIT的下标数

select ceil(count(*)/a) into sum from mysql_emp; -- 总页数的值

select c; -- 打印当前第几页

if b=0 then set num=0; -- 为0的话就为首页

elseif b=1 then set num=(sum-1)*a; -- 为1的话就为尾页

elseif b=2 and c<=1 then set num=0; -- 当页数为第一页的上一页时设置为第一页的数据

elseif b=2 and c>0 and c<sum then set num=(c-2)*a; -- 为2的话就为上一页

elseif b=3 then set num=(c+1)*a; -- 为3的话就为下一页

end if;

select * from mysql_emp LIMIT num,a;

END;

set @c=7;

call mysql_emp1(7,2,@c);

死板方法：

if c>0 and c<=sum then set num=(c-1)*a; -- 如果存在这个页数就打印相应页数的数据

elseif c>sum then set num=(sum-1)*a; -- 超过这个页数就打印最后一页数据

elseif c<0 then set num=0; -- 小于最小的页数就打印第一页数据

end if;

Mysql定时器

http://blog.csdn.net/cug_jiang126com/article/details/50441880

查看是否开启定时器

show variables like '%event_scheduler%';

开启定时器 0关闭，1开启

set global event_scheduler =1;

create event aaa(定时器名字) on schedule 配置时间

do sql语句

drop event aaa(定时器名字)

开启定时任务 enable

alter event 定时器名字 on

completion preserve enable;

关闭定时任务定时器名字

alter event EVENT_lqy_test_insert_23 on

completion preserve disable;

删除定时器

drop event myeveny;

create event myeveny

on schedule every 5 SECOND

alter event myeveny on

completion preserve enable;

alter event myeveny on

completion preserve DISABLE;

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'myeveny';

MYSQL导出数据库

进入数据安装目录/bin

1.导出结构不导出数据

复制代码代码如下:

mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql　　

2.导出数据不导出结构复制代码代码如下:

mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　

3.导出数据和表结构

复制代码代码如下:mysqldump　数据库名　-uroot　-p　>　xxx.sql　

4.导出特定表的结构复制代码代码如下:

mysqldump　-uroot　-p　数据库名　　表名　>　xxx.sql　　

5.导入数据：

由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了：

复制代码代码如下:

#mysql　数据库名　<　文件名

#source　/tmp/xxx.sql　　

二、引擎：

http://c.biancheng.net/cpp/html/1465.html

下面是常用存储引擎的适用环境：

MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一

InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

三、账号权限

1、GRANT命令使用说明：

先来看一个例子，创建一个只允许从本地登录的超级用户jack，并允许将权限赋予别的用户，密码为：jack.

mysql> grant all privileges on *.* to jack@'localhost' identified by "jack" with grant option;

GRANT命令说明：

ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。

ON 用来指定权限针对哪些库和表。

*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。

TO 表示将权限赋予某个用户。

jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。

IDENTIFIED BY 指定用户的登录密码。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

2、刷新权限

使用这个命令使权限生效，尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，只要对权限做了更改就使用FLUSH PRIVILEGES命令来刷新权限。

mysql> flush privileges;

3、查看权限

查看当前用户的权限： show grants;

查看某个用户的权限： show grants for 'test1'@'localhost';

4、回收权限： revoke all on *.* from 'test1'@'localhost';

5、删除用户：drop user 'jack'@'localhost';

select host,user,password from user;

注：删除用户如果不清除权限，下次再创建同样用户，所有权限还存在。

6、对账户重命名

rename user 'jack'@'%' to 'jim'@'%';

7、修改密码

1、用set password命令

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

2、用mysqladmin

mysqladmin -uroot -p123456 password 1234abcd

备注：

格式：mysqladmin -u用户名 -p旧密码 password 新密码

3、用update直接编辑user表

use mysql

update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

flush privileges;

4、在丢失root密码的时候：

[root@rhel5 ~]# mysqld_safe --skip-grant-tables &

[root@rhel5 ~]# mysql -u root

mysql> use mysql

mysql> update user set password = PASSWORD('123456') where user = 'root';

mysql> flush privileges;

四、字符

my.cnf

character-set-server=utf8

show variables like 'character_set_database'; -- 查看当前数据库编码

show variables like 'character_set_server'

概念

1．触发器的作用？

答：触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

2。什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

3。索引的作用？

答：索引是一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

6。什么叫视图？游标是什么？

答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

阅读全文

0 0