MySQL笔记

一、存储过程

创建过程如下 先改变结束符号

delimiter && //表示以&&为结束符号

create procedure test(a int)

begin

select * from student where sno=a;

end &&

使用的时候用 call test(x);  x为int类型 表示带参存储过程

begin

declare a int;  //当需要定义参数的时候在begin里面定义 并且所有语句必须有分号;结尾 否则报错

set a=5;

select * from student where sno=a;

二、自定义函数

创建自定义函数过程如下 规则和存储过程差不多

delimiter &&

create function test5(i int,o int,p int)

returns int

begin  // declare d int; 也可以在begin之后声明一个变量

set p=i*o; //同理用set创建函数体

return p;

end &&

这个是带参并且是begin里面有函数体的函数

使用的时候用  select test5(i,o,p) 赋值3个int即可 执行结果只与函数体有关

三、触发器

创建触发器 首先也是delimiter一下 然后

delimiter &

create trigger test after insert on student for each row  //这里可以有after 和 before两种触发器

update student_h set row=row+1;  //当有函数体的时候用begin包裹起来

end &

四、数据库所有账户

三种类型：create login login_name   //登录账户 指登录sql server2008的账户 可以拥有多个user 如下

create user user_name           // 数据库用户 隶属于账户里面的，可以简称为映射 grant on 表名 to <用户名,账号名> with grant option表示该主体可以向其他主体授予所指定的权限

  deny  execute on test1 to studnet cascade 同理grant 属于权限的一种  cascade=with grant option

  revoke 同理deny 表示收回权限

各类操作汇总：

数据库操作：

新增：create database test; 

删除：drop database if exists test1; 

修改：alter database test1 default character set gb2312;

表操作：

一、和表结构之类有关：

查看：

desc table_name;  Or  show columns from table_name;

新增：

create table test(

a int not null default 0 primary key auto_increment CHECK (a>0),

b char(10) not null,

c datetime，

CONSTRAINT chk_Person CHECK (b什么什么的 AND c 什么什么的)//这种是为了命名约束

//  CHECK (a>0)可以写在这里

);  //当约束作用于两列以上时候要写在最后面

更改(列)：

alter table test    //新增行

add column d char(1) not null default 'a' after b; 

//after可以换成first表示设为第一行(只有这两种)

alter table test   

alter column c char(10); //修改行

[alter column b set default 'hello';]  // 也可以修改默认值

alter table test1

    drop column b;  //删除行

alter table test 

    change column b e varchar(2) null default '啊';  //同时改列名和类型(将列名b改为e)

alter table test

modify column b char(5) first;  //只修改数据类型 可以使用first或者after修改它的顺序

alter table test

rename to test_backup //修改table名字

重命名表：

rename table test_backup to test;

复制表：

create table test_copy like test; //结构完全复制

create table test_copy select * from test;  //将test作为结果集复制过去 不会复制约束和索引等

create table test_copy as select * from test; //同上 可以用括号将as后面的东西包裹起来

删除表:

drop table [if exists] test;  //表里的外键可以用cascade策略 例如： on delete cascade 表示

在父表上update/delete记录时，同步update/delete掉子表的匹配记录

二、和数据有关：

插入：

insert into test values(1,'潘冠宇','2017-03-18 16:52:00');

insert into test(a,b) values(1,'潘冠宇');  //如果数据只插入某些列 那么要在表名后用圆括号括起来

insert into test set 列名=值,列名=值; //只对想输入的列输入且主键非空 not null自动赋值但会有warning

replace into test values(1,'张馨允','2017-03-18 16:52:00'); 

//当要替换信息的时候可以用replace更改除主键以外的信息

删除：

delete from test where 列名=值;  //

delete [*] from test 或者 turncate test //均表示删除所有行  还有一种从多个表删除看书

修改：

update test set j=1.22,g='女' where f=1;  //修改多列的时候用逗号

用了ignore关键词在更新中有错误也不会中断

查询：

select * from test;

select a,b from test;

{--简单Case函数

case:

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

--Case搜索函数

CASE WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END}

select a,case b

when ...  //可以使用上述的case函数 此为高级查询

select a,b from test where a like '我_%';  //%表示匹配任意字符0到任意次数 下划线_表示匹配一个任意字符 当列值有_的时候 需要转义字符#去除下划线_的特殊作用

regexp:

select * from test where a regexp '我';  //等同于where a like '%我%' 表示含有我字的值

select distinct * from test where g regexp '东莞|北京'; 表查询带有东莞和北京的记录

group by a  //表示通过a中的每个值来分组

having 字句会对分组的过滤 比如count(*)<=3 那么通过group by分组之后的就只取小于等于3的

select a,b from test order by c limit 4,3(limit 3 offset 4); //limit表示限制从第5行开始的3行数据

在MySQL中 没有top n的用法 需要用limit对数据进行选取 比如从0开始  limit 0,10

视图操作：

创建：

create view view_name as select column_name(s) from table_name where condition [with check option] // with check option 用于指定在可更新视图上的修改都需要满足select中所指定的限制条件。

修改：  alter view...  //同创建一样

        // 视图的操作大多数和表一样 在插入数据的时候 当有check option的选项时 那么在插入的时候必须满足condition的条件比如：

插入： create view v as select * from test where age > 20 with check option;

mysql> insert into v1(ssex) values(11); //如果在创建的时候有 with check option

ERROR 1369 (HY000): CHECK OPTION failed 'test1.v1' //那么在插入数据不满足创建的condition的时候就会报错  并且主表也不会创建

// 如果没有加option  那么数据会插入到主表上 但是视图不会挑选这一条行记录 所有数据随时更新

删除：

drop view v1;

更新：

update v1 set a='你好';

索引操作：

创建：

1、 create index index_name on test.test(a,b) [using btree];  //在test数据库的test表对a和b列创建索引

2、 create table test(

a int not null primary key, //primary key也是一个索引 是对本列声明非空的索引

b char(2),

name char(50),

index index_name(b),  //在创建表的时候可以同时创建索引 可以创建多个索引

constraint foreign key(name) references customers(name));

//外键也是一种索引 key通常是index的同义词

3、 alter table test add [constraint fk_name] foreign key(sno) references student(sno);

//上面这一条是增加外键约束 被参照的列必须是主键或者unique索引

alter table test add index index_name(a,b); //

查看：

show index from 表名;

删除：

alter table test drop index index_name,drop paimary key;

//无法直接用  drop index 索引名称; 那是标准SQL MYSQL不支持

存储过程操作：

一、存储过程：

创建：

delimiter &&

create procedure sp_name(in/out/inout cid int,in csex char(1))

//  in/out/inout 表示三种参数，in表示输入参数，out表示输出参数，inout表示输入输出参数。

begin

update customers set cust_sex=csex where cust_id=cid;

end &&

delimiter ;

事件操作：

一、事件调度：

查看:

show variables like 'event_scheduler';

select @@event_scheduler;  //两种方式都可以查看  结果为1或者on表示开启

开启：

set global event_scheduler=1;

set global event_scheduler=ture;  //经测试 这一条value不可以为true;

二、事件操作：

创建：

1、 delimiter &&

create event if not exists et

on schedule every 1 month //注意这里是 schedule 没有 【r】！！

// year quarter month day hour minute week second 等等

starts curdate() +interval 1 month

ends '2018-03-30'

do

begin

if year(curdate())<2018 then

insert into t1()

values(1,'你好'); //这个事件的功能是 每个月向表t1插入一条数据

  end if; 该事件于下个月开始 并且结束于2018年3月30号。

end && 

2、 delimiter &&

create event t3

on schedule every 10 second

starts curdate() +interval 1 minute

ends '2017-03-21 21:50:00'  //可以精确到秒数

do

begin

insert into test(number) values(number+1);

end

&&

修改：

alter event et....  //打法和创建一样

alter event et disable; // 事件关闭

alter event et enable; //事件再次开启

alter event et rename to et1；  //事件改名

删除事件:

drop event if exists et;

触发器操作：

一、创建触发器(标准):

create triggerigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt

//trigger_time 有两个选项分别是 before 和 after 表示触发器被触发的时刻

// trigger_event指定激活触发器的语句的种类：insert delete update

// trigger_body 可使用begin..end 复合语句结构

create trigger t after insert on t1 for each row set @str='one customer added!';

删除：

drop trigger [if exists] trugger_name;

二、insert触发器(详解)：

//也可以被load data触发 均表示新增数据

create trigger insert_trigger after insert on customers for each row set @str=new.id;

//往t2插入数据的时候往对应的t1表中增加人数

delimiter &&

create trigger tr1 after insert

on t2 for each row

begin

declare a int;

set a=(select count from t1 where sno=new.sno);

update t1 set count=a+1 where sno=new.sno;

end && 

三、delete和update

//update和delete触发器写法于insert大同小异 在trigger_time和trigger_event上有所不同

并且在运用old和new临时表的不同

//old上保存已删除的数据记录  new保存即将更新的数据，包括insert和update

当涉及自身表的更新的时候 只能使用before update触发器 而 after触发器不被允许