谁能给个mysql创建序列sequence 和触发器的例子啊
以前用oracle
刚用mysql
不怎么会用
谢谢了
以前用oracle
刚用mysql
不怎么会用
谢谢了
解决方案 »
- mysql日期格式转化。
- 一个时间转换的小问题
- 请高手指点!!!!!!!!!!!mysql if not exists 不让用,急急急、!!!!!
- mysql中如何获取年、月、日及本周是一年中的第几周??
- Mysql客户端中文显示问题
- 请问使用mysql 时的一些问题
- 《PHP_&_MySQL无需编辑轻松创建数据库网站》
- 初次使用MySQL,c api简单问题,立刻给分
- 用C++开发PostgreSQL应用,为什么我Redhat Linux9上没有libpq++.h? 只有libpq-e.h
- mysql多库共用数据的同步如何设计
- 关于分表和分区
- 怎样从Windows命令行启动MySQL????
r(32) not null);
Query OK, 0 rows affected (0.08 sec)mysql> insert into animals(name) values ('dog'), ('cat');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from animals;
+----+------+
| id | name |
+----+------+
| 1 | dog |
| 2 | cat |
+----+------+
2 rows in set (0.00 sec)mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+mysql> DROP TRIGGER account.ins_sum;
DELIMITER $$DROP TRIGGER /*!50032 IF EXISTS */ `zz`.`gg`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `zz`.`gg` AFTER INSERT ON `zz`.`bbb`
FOR EACH ROW BEGIN
if (select count(*) from bbb)>9 then
delete from bbb where cid=new.cid;
end if;
END;
$$DELIMITER ;
1、用自增字段;
2、用查询生成,前提是表中有唯一标识的字段。
1.序列。mysql> create table seq(id int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)mysql> insert into seq select NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into seq select NULL from seq;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from seq;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)2、触发器。
mysql> create table tr(id int not null);
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ||
mysql> create trigger tr_test after insert on seq for each row
-> begin
-> insert into tr values(new.id);
-> end||
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> insert into seq values (10);
Query OK, 1 row affected (0.00 sec)mysql> select * from seq;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 10 |
+----+
9 rows in set (0.00 sec)mysql> select * from tr;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
insert into seq select NULL;