MySQL

创建表：

Create tabletable_name(

A1 D1,

A2 D2,

A3,D3

...

完整性约束1,

完整性约束2,

...

)

其中A1为属性名称，D1为属性的约束

如：

create tableStudent(

id int not null auto_increment,

name varchar(20)not null,

course_id int notnull,

primary key(id),

foreignkey(course_id) references Course(列名)

)
完整性约束主要有以下几种：

1、  not null

2、  unique：控制字段内容不能重复。

3、  primary key

4、  foreign key

5、  check：主要用于控制字段的范围

6、  default：用于设置新纪录的默认值

一般创建表这样创建：

create tablemy_table(

id int not nullauto,

age varchar(20)not null,

sex varchar(10)default “man”,

primary key(id),

foreign key(id),

check(age>18and age<150)

)

alter修改表：

增加列：alter tabletable_name add 列名约束。如：
alter table Student add age int not null ;

修改列：alter tabletable_name alter 列名约束。如：

alter tableStudent alter age  varchar(10)

ALTER TABLEtablename ALTER COLUMN column1 int not null

删除列：alter tabletable_name drop 列名。如：

alter  table Student drop age;

增加表的约束：alter tabletable_name add constraint 。如：

alter tableStudent add constaint foreign key(id) references Course(id)

聚集函数：

avg、min、max、sum、count

分组聚集：

group by：值得说明的是，任何没有出现在group by子句中的属性如果出现在select中的话，那么它只能出现在聚集函数内部，否则这样的查询就是错误的。

如：

select department count(id) age from my_table group bydepartment//错误，因为可能会有很多的age。

进行分组后的条件选择时，可以使用having，如：

select department count(id) from my_table group bydepartment having count(id)>10

或者这样写：

select department count(id) from my_table group bydepartment where count(id)>10

和select一样，select中只能出现在group by子句中出现过的属性。

表之间的连接：

在数据库中，表之间的连接可以分为三种：

1、 内连接（默认的连接）

2、 外连接（又可以分为左外连接、右外连接、全外连接）

3、 交叉连接（又称为笛卡尔乘积）

MySQL语句：

循环语句：

(a)while 布尔表达式 do

...

end while;

 

(b)repeat

...

until 布尔表达式

end repeat;

 

mysql不支持for循环

 

条件控制语句：

If 布尔表达式 then

...

elseif then

...

else

...

end if;

创建视图:

视图的概念实际上分为两部分：第一就是视图是一张表；第二就是视图是查询语句，两者结合起来便是视图就是通过查询语句动态查询出来的表，我们可以像普通表一样操作视图。

create view view_name as 查询语句;

使用视图：select * from view_name;

MySQL创建函数：

Delimiter //

Create functionfucntion_name(参数1 参数1类型,参数2 参数2类型,....)returns 返回值类型

begin

declare resultint;

set result=a+b;参数的赋值

return result;

end //

①  需要注意的是在MySQL中是无法返回table类型的。但是可以创建临时表，临时表在连接断开的时候会被自动删除：

createtemporary table  if not existstable_name(一些参数);

droptable_name;//清除掉里面的数据

②  关于table的赋值，一般常见的方法有：

(1)

Create table table_name(id int,namevarchar(20));

Insert into table_name select id,name fromuser;//这种方法需要首先创建表。

(2)

Select id,name into table_name from user;//这种方法不要创建表table_name，这里会自动创建。（MySQL不支持selectinto，但是可以像③那样写）

(3)

在创建的时候直接赋值：

createtemporary table if not not exists table_name (参数)select * from table;

MySQL创建函数：

Delimiter//

createfunction my_function(my_name varchar(20)) returns int

begin

declareresult int;

setresult=0;

selectcount(*) into result from Student where name=my_name;

returnresult;

end//

注意：在MySQL中变量分为3种，一是系统变量，以@@...形式表示，这种变量一直都存在；第二是用户自定义变量，以@...形式表示，这种变量从声明后，到客户端退出后消失；第三种可以说是临时变量，以declare 形式声明，这种变量只存在于局部范围。另外，在mysql中，function和触发器是不能返回一个数据集(表)。但是存储过程是可以选择一个数据集的，具体而言，就是不在function和trigger中有这样的操作：select id,name from Student或者是这样：select “删除成功！”.在存储过程中是可以这样写的，在调用存储过程的时候会将结果输出出来。

MySQL存储过程：

delimiter//

createprocedure my_proc(name varchar(20),out my_count int)

Begin

Selectcount into my_count from Student ;

end//

调用：

callmy_proc(“xiaoming”,@count);

select@count;

MySQL创建触发器：

deleimiter//

createtrigger my_trigger after insert on Student

foreach row

begin

insertinto table_tmp values (new.id,new.name);

end//

 

注意：deleter update insert/before after只有表才支持触发器，视图和临时表都不支持。每张表最多支持6个触发器。Old是只读的，不允许修改，但是before的new是可以修改的，这样我们可以很容易地在进行操作前检查数据的类型如将小写转为大写等。在before的new中，自增的字段为0，但是after则不是。

MySQL游标的创建（游标其实就是一个数据集，只是这个数据集游标可以对结果取出一行一行地来进行处理）：

Delimiter//

createprocedure my_cursor_proc()

decalremy_name varchar(20);

declaremy_id int;

declaredone boolean default false;

declaremy_cursor cursor for select id,name from Student;//创建游标

declarecontinue handler for not found set done=true;//声明当数据库发生产生not found的时候设置done=true

openmy_cursor;//打开游标

whilenot done do

fetchmy_cursor into my_id,my_name;//游标浮动

selectmy_id,my_name;

endwhile;

closemy_cursor;//关闭游标

end//

上面需要注意的是变量的声明顺序，应当先声明一般的变量，然后是cursor，最后是continue handler

MySQL事务

特性（ACID）：原子性，一致性，隔离性，持久性。

原子性（Atomicity）：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性（Isolation）：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性（Durability）：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

在MySQL中，有两种方法可以创建事务（推荐第一种方法）：

1、Start transaction/begin  commit/rollback

Delimiter //

Create procedure my_proc()

Begin

Decalre message int default 0;

Declare continue handler for sqlexception set message=1;   //发生sql错误则回滚

Start transaction;

Delete from Student where name=”Liming”;

Delete from message_table where name=”Liming”;

if message then

rollback;

else

commit;

end //

 

2、直接设置set autocommit=0;设置之后，之后所有的sql语句都不会自动执行，必须用commit执行或者是rollback才可以执行结束事务。

 

注意事项：（具体参考http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html）

(1)    不能回滚的操作

事务处理用来管理Insert 、update和delete语句。不能回滚select语句，这样做是没有意义的，也不能回滚create和drop操作，事务处理块中可以使用这两条语句，但是如果执行回退，他们不会被撤销。

(2)

(3)MySQL中事务能不能自动回滚？我测试了下，开始事务后，直接commit，如果发生错误，好像是不能自动回滚的，必须手动地调用rollback或者先判断sqlexception异常再回滚，不知道有没有人知道这方面的知识。