MySQL课程 各种语法与好处

1. 为什么要使用Mysql

因为开源，免费，功能也强大

 

1. Mysql的安装

 

 

1. 服务器引擎

1. 常用引擎：Myisam   ,InnoDB   ,BDB，MERGE（默认：Myisam）
2. Myisam与InnoDB的区别

（1）事务处理：

MyISAM是非事务安全型的，而InnoDB是事务安全型的（支持事务处理等高级处理）；

（2）锁机制不同：

MyISAM是表级锁，而InnoDB是行级锁；

（3）select ,update ,insert ,delete 操作：

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

（4）查询表的行数不同：

MyISAM：select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含   where条件时，两种表的操作是一样的

InnoDB ： InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行

（5）外键支持：

mysiam表不支持外键，而InnoDB支持

 

Myisam主要有三个文件，.frm表结构存储文件,.MYD 数据存储文件，.MYI 索引存储文件

 

 

4.基本语法

1.数据库的创建与删除

Create database 数据库名称

 

Drop database 数据库名称

 

2.创建表，定义字段，设置默认值

-- 创建学生表

create table student

(

id int auto_increment primary key,

studentname varchar(50),

accountname varchar(50),

pwd varchar(50),

nickname varchar(20),

createtime datetime default now()

);

 

-- 创建层级关系表

create table dept

(

id int auto_increment PRIMARY key,

deptname varchar(50),

parentId int

);

 

-- 删除表

-- drop table dept;

 

-- 添加字段

alter table dept

add icon VARCHAR(200);

 

-- 删除字段

alter table dept

drop column icon;

 

-- 修改字段类型

alter table dept

modify column deptname varchar(150);

 

 

1. 会话变量，局部变量，全局变量

会话变量：在当前连接有效，直到会话结束，会话变量还可以跨库查询。

set @myname='任我行';-- 定义会话变量

select @myname; -- 查询会话变量

set @myname='111'; -- 修改会话变量的值

select @myname;

局部变量：

-- declare 必须在begin 与 end 的里面

-- 而begin 与end 只在函数/存储过程/触发器中才能有

-- 也就是说局部变量只能在 函数/存储过程/触发器 中定义

全局变量：

Set @@global.变量名称=变量值 -- 定义全局变量（一般不建议使用）

作用域：整个Mysql

 

1. if ，while等结构化语句的综合使用

注意：只能在 函数/存储过程/触发器 中使用，if   then,最后要加 end if;

if 条件表达式  THEN

 

elseif 条件表达式

...

else

   表达式

end if;

 

1. 函数的定义，删除，调用

-- 修改语句结束符

delimiter $$

 

-- 定义函数

drop function if EXISTS test_sum;

create function test_sum(j int) returns INT

BEGIN

declare i int default 0;

declare sumcount int default 0;

 sum_while: while i<=5 do  -- 定义循环体，给循环体取名为sum_while

if i%5=0 THEN

set i=i+1;

ITERATE sum_while;-- 迭代

-- ITERATE:结束当前循环，进行下一次循环(continue)

-- LEAVE :结束循环（break）

end if;

set sumcount=i+sumcount;

set i=i+1;

END WHILE sum_while;

return sumcount;

END;

$$

delimiter ;-- 把结束符修改回;号

 

-- 函数调用

select test_sum(1);

 

 

 

1. 存储过程的定义与调用

select * from student where 1=1 and studentname like '%李%' and accountname like '%1%' limit 0,8;

select count(id) from student where 1=1 and studentname like '%李%' and accountname like '%1%' into @totalcount;

select @totalcount;

 

drop PROCEDURE if EXISTS SutdnetPagedList;

Create PROCEDURE SutdnetPagedList(

in pageindex int,

in pagesize int,

in accountname varchar(50),

in studentname varchar(50),

out totalcount int

)

BEGIN

declare _sql varchar(2000) default 'select * from student ';

declare _where varchar(500) default ' where 1=1 ';

declare _begin int default (pageindex-1)*pagesize;-- 不加1

declare _countsql varchar(500) default 'select count(id) from student ';

 

-- 拼接查询账号为查询条件

IF (accountname IS not NULL or accountname<>'') THEN

set _where = CONCAT(_where,' and accountname like ''%',accountname,'%''');

  end if;

-- 拼接查询学生姓名为查询条件

IF (studentname IS not NULL or studentname<>'') THEN

set _where = CONCAT(_where,' and studentname like ''%',studentname,'%''');

end if;

-- 得到一个完整的分页查询语句

set _sql=CONCAT(_sql,_where,' limit ',_begin,',',pagesize);

-- 得到一条完整的获取总记录数的查询语句

set _countsql=CONCAT(_countsql,_where,' into @totalcount');

 

set @_sql = _sql;-- 将局部变量赋值给会话变量

 

PREPARE pre_sql from @_sql;-- 预编译Sql语句 类似于SqlServer里面的 EXEC sp_executesql

EXECUTE pre_sql; -- 执行拼接好的sql

DEALLOCATE PREPARE pre_sql;-- 把预编译的Sql释放掉

 

set @_countsql = _countsql;-- 将局部变量赋值给会话变量

PREPARE pre_countsql from @_countsql;-- 预编译Sql语句 类似于SqlServer里面的 EXEC sp_executesql

EXECUTE pre_countsql; -- 执行拼接好的sql

set totalcount=@totalcount;-- 获取总记录数

 

DEALLOCATE PREPARE pre_countsql;-- 把预编译的Sql释放掉

 

 

END;

 

set @count=0;

call SutdnetPagedList(2,3,'','',@count);

select @count;

 

1. 事务的使用

drop PROCEDURE if EXISTS p_multydelete;

-- 创建批量删除的存储过程

create PROCEDURE p_multydelete

(

in ids varchar(150),

in tablename varchar(20)

)

BEGIN

 

DECLARE _sql varchar(1000) default '';

declare _err int default 0;-- 定义删除过程中错误的数量

-- 捕捉异常，如果发生了错误，将错误数量设置为1

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _err=1;

-- 开启事务

START TRANSACTION;

-- 创建批量删除的Sql语句，定义为用户变量

set _sql = CONCAT('delete from ',tablename,' where id in(',ids,')');

set @_sql = _sql;

PREPARE pre_sql from @_sql;

EXECUTE pre_sql;

DEALLOCATE PREPARE pre_sql;

if _err>0 then

ROLLBACK;

  ELSE

COMMIT;

  end if;

END;

8.触发器的使用

/*

创建一个触发器，

for each ROW:每一行发生变化的时候都触发此触发器

特性：具有事务性，如果在触发器中发生了错误，则回滚数据，包括事件触发前的数据

new:代表操作前的表(inserted)

old:代表操作后的表（deleted,updated）

*/

create TRIGGER insert_student_after after INSERT on student for each ROW

BEGIN

-- 将语文和数学设置为必选课

insert into stu_course values(null,0,new.id)

END