分类 笔记 下的文章

mysql触发器

创建触发器

create trigger <触发器名称>
{before|after}
{insert|update|delete}
on <表名>
for each row
<触发器sql语句>

如下

创建两张表
create table `user`
(
`id` int unsigned primary key auto_increment,
`name` char(10),
`money` int unsigned
)charset=utf8;
create table `users`
(
`users` int unsigned primary key,
`money` int unsigned
)charset=utf8;
insert into `users` value (0,0);# 初始化数据
创建插入触发器
create trigger `t1`
after insert on `user` 
for each row
update `users` set `users`=`users`+1,`money`=`money`+new.`money`;
创建删除触发器
create trigger `t2`
after delete on `user` 
for each row
update `users` set `users`=`users`-1,`money`=`money`-old.`money`;
创建更新触发器
create trigger `t3`
after update on `user`
for each row 
update `users` set `money`=`money`+new.`money`-old.`money`;

多行语句的触发器

建表

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  `is_vip` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=utf8;
CREATE TABLE `users` (
  `users` int(10) unsigned DEFAULT NULL,
  `vip` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM CHARSET=utf8;
insert into `users` value (0,0);# 初始化数据

插入时的触发器(注意if语句的写法)

delimiter //
create trigger `t1`
after insert on `user`
for each row
if new.is_vip = 0 then
    update `users` set `users`=`users`+1;
else
    update `users` set `vip`=`vip`+1;
end if//
delimiter ;

删除时的触发器

delimiter //
create trigger `t2`
after delete on `user`
for each row
if old.is_vip=0 then
    update `users` set `users`=`users`-1;
else
    update `users` set `vip`=`vip`-1;
end if//
delimiter ;

更新时的触发器

delimiter //
create trigger `t3`
after update on `user`
for each row
if old.is_vip!=new.is_vip then
    if new.is_vip=0 then
        update `users` set `users`=`users`+1,`vip`=`vip`-1;
    else
        update `users` set `users`=`users`-1,`vip`=`vip`+1;
    end if;
end if //
delimiter ;

循环(loop)的使用

变量赋值

delimiter //
create trigger `t4`
after insert on `t4`
for each row
begin# 代码开始标记
    declare i int default 0;# 定义变量，默认值为1
    declare j int;
    set j=new.num;# 赋值
    t4_loop:loop# 循环开始
        insert into `t5` value (i);
        if j < i then
            leave t4_loop;# 结束循环
                        #iterate t4_loop#跳出循环
        end if;
        set i=i+1;
    end loop t4_loop;
end//# 代码结束标记
delimiter ;

查询结果赋值给变量

drop trigger `t4`;
delimiter //
create trigger `t4`
after insert on `t4`
for each row
begin
    declare i int default 0;
    declare j int;
    select count(*) into j from `t4`;# 查询结果赋值给变量
    `t4_loop`:loop
        insert into `t5` value (i);
        if i>j then
            leave `t4_loop`;
        end if;
        set i=i+1;
    end loop `t4_loop`;
end//
delimiter ;

使用repeat达到上面的效果

drop trigger `t4`;
delimiter //
create trigger `t4`
after insert on `t4`
for each row
begin
        declare i int default 0;
        declare j int;
        select count(*) into j from `t4`;
        repeat
            insert into `t5` value (i);
            set i=i+1;
        until i>j end repeat;
end//
delimiter ;

使用while达到上面的效果

drop trigger `t4`;
delimiter //
create trigger `t4`
after insert on `t4`
for each row
begin
    declare i int default 0;
    declare j int;
    select count(*) into j from t4;
    while i<j do
        insert into `t5` value (i);
        set i=i+1;
    end while;
end//
delimiter ;

查看触发器

show create trigger `t4`;
show triggers;

msyql 索引

索引设计原则

  • 索引并非越多越好
  • 数据量不多不需要建索引
  • 列中的值变化不多不需要建索引
  • 经常排序和分组的列需要建立索引
  • 唯一性约束对应使用唯一性索引

索引创建

普通索引

create table if not exists `user`
(
`id` int,
`name` char(10),
index `index_id`(`id`)
)engine=myisam charset=utf8;

唯一索引

create table if not exists `user`
(
`id` int,
`name` char(10),
unique index `unique_index_id`(`id`)
)engine=myisam charset=utf8;

单列索引

create table if not exists `user`
(
`id` int,
`name` char(10),
index `index_name`(`name`(5))#这里数字是索引的长度的意思
)engine=myisam charset=utf8;

索引长度

对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

此处展示的语句用于创建一个索引，索引使用列名称的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));

因为多数名称的前10个字符通常不同，所以此索引不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

组合索引

create table if not exists `user`
(
`id` int,
`name` char(10),
index `multiple_index`(`id`,`name`)
)engine=myisam charset=utf8;

组合索引注意点

经常使用MySQL组合索引，也经常发现，其实匹配程度并不高，尤其是条件组合非常多的时候。
其实使用组合索引有这些注意点的：

比如这个索引 key(last_name, first_name, dob)
如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
(1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。
即所有条件都是等于，并且全部匹配

(2)匹配最左前缀(Match a leftmost prefix)：仅仅使用索引中的第1列。
即索引中的最左边的用等于条件。

(3)匹配列前缀(Match a column prefix)：这仅仅使用索引中的第1列。
即’X%’

(4)匹配值的范围查询(Match a range of values)：仅仅使用索引中第1列。
即第一列 可以用大于 小于 X>0 and X<1

(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。
即，第一列 精确匹配，后面一列 范围匹配

(6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。

当然，使用B-tree索引有以下一些限制：
(1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。
(3) 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23′，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

总结出来就是，使用了组合索引以后，你必须要从左到右依次精确匹配索引，能匹配多少匹配多少，直到最后一个可以匹配范围索引，只要用了某列范围索引，后面的列的索引就无效了。。所以组合索引虽好，但必须要用巧。条件并不能随便给的。

全文索引

create table if not exists `user`
(
`id` int,
`name` char(10),
fulltext index `full_index`(`name`)
)engine=myisam charset=utf8;

使用 alter 命令添加,和上面的在建表语句里面添加索引的格式是一致的

alter table `user` add index `index_id`(`id`);#添加索引

使用 create 命令添加

create index `index_name` on `user`(`name`);

索引删除

alter table `user` drop index `index_id`;#删除索引
drop index `index_name` on `user`;

for ((i=1;i<=1000000;i++));do `mysql -uroot -p123456 test -e "insert into test values ($i,floor($i + rand()*$i),md5($i))"`;done

msyql修改表

创建表

create table if not exists `category`
(
`category_id` int unsigned primary key not null,
`category_name` char(10) unique
)engine=innodb charset=utf8;

修改表可以用如下语句

alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
alter table <表名> modify <字段名> <新数据类型>;

mysql> desc `category`;
+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| category_id   | int(10) unsigned | NO   | PRI | NULL    |       |
| category_name | char(20)         | YES  | UNI | NULL    |       |
+---------------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table `category` change `category_name` `category_name1` char(30);
Query OK, 1 row affected (1.34 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id    | int(10) unsigned | NO   | PRI | NULL    |       |
| category_name1 | char(30)         | YES  | UNI | NULL    |       |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table `category` modify `category_name1` char(10);
Query OK, 1 row affected (1.21 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id    | int(10) unsigned | NO   | PRI | NULL    |       |
| category_name1 | char(10)         | YES  | UNI | NULL    |       |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加字段可以用如下语句

alter table <表名> add <新字段名> <新数据类型> [约束条件] [first|after已存在的字段名];

如下

mysql> desc `category`;
+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| category_id    | int(10) unsigned | NO   | PRI | NULL    |       |
| category_name1 | char(10)         | YES  | UNI | NULL    |       |
+----------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table category add `category_description` char(20) after `category_id`;
Query OK, 0 rows affected (0.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id          | int(10) unsigned | NO   | PRI | NULL    |       |
| category_description | char(20)         | YES  |     | NULL    |       |
| category_name1       | char(10)         | YES  | UNI | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除字段可以用如下语句

alter table <表名> drop <字段名>;

如下

mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id          | int(10) unsigned | NO   | PRI | NULL    |       |
| category_description | char(20)         | YES  |     | NULL    |       |
| category_name1       | char(10)         | YES  | UNI | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table `category` drop `category_name1`;
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id          | int(10) unsigned | NO   | PRI | NULL    |       |
| category_description | char(20)         | YES  |     | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改字段排列位置可以用如下语句

alter table <表名> modify <字段1> <字段类型> first|after <字段2>;

如下

mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_id          | int(10) unsigned | NO   | PRI | NULL    |       |
| category_description | char(20)         | YES  |     | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table `category` modify `category_description` char(20) first;
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc `category`;
+----------------------+------------------+------+-----+---------+-------+
| Field                | Type             | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+-------+
| category_description | char(20)         | YES  |     | NULL    |       |
| category_id          | int(10) unsigned | NO   | PRI | NULL    |       |
+----------------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表的存储引擎

alter table <表名> engine=<新的存储引擎>;

如下

mysql> show create table `category`;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                            |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
  `category_description` char(20) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table `category` engine=myisam;
Query OK, 1 row affected (0.49 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table `category`;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                            |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
  `category_description` char(20) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除表的外键

alter table <表名> drop foreign key <外键名>;

如下

mysql> show create table `book`\G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `book_id` int(10) unsigned NOT NULL,
  `book_name` char(10) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `fk_1` (`category_id`),
  CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table `book` drop foreign key `fk_1`;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `book`\G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `book_id` int(10) unsigned NOT NULL,
  `book_name` char(10) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `fk_1` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

msyql唯一性约束

创建表的时候指定唯一性约束

create table if not exists `category`
(
`category_id` int unsigned primary key not null,
`category_name` char(10) unique
)engine=innodb charset=utf8;

如果插入进 category 表的数据的 category_name 在 category 表的中 category_name 字段中已经存在，就会报错

mysql> insert into `category` value (1,'c1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into `category` value (2,'c1');
ERROR 1062 (23000): Duplicate entry 'c1' for key 'category_name'

默认约束就不记录了

mysql外键

指定外键,注意，表的存储引擎药选择支持外键的存储引擎，不然无效

create table `category`
(
`category_id` int unsigned primary key not null,
`category_name` char(10)
)engine=innodb charset=utf8;
create table `book`
(
`book_id` int unsigned primary key,
`book_name` char(10),
`category_id` int unsigned not null,
constraint fk_1 foreign key(`category_id`) references `category`(`category_id`)#指定 `category` 表的 `category_id` 字段为 当前表的 `category_id` 字段的外键
) engine=innodb charset=utf8;

如果插入进 book 表的数据的 category_id 不在 category 表的中 category_id 字段中，就会报如下的错误

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`book`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`))

如果直接删除外键表的话会报错

mysql> drop table if exists `category`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

需要先删除创建了外键约束的表，或者删除外键约束

drop table if exists `book`;
drop table if exists `category`;
  1. 1
  2. ...
  3. 5
  4. 6
  5. 7
  6. 8
  7. 9
  8. 10
  9. 11
  10. ...
  11. 31