了解知识
MySQL
create table test(id int(3) auto_increment primary key,tname char(10));
注意自增的字段必须是主键
Eg:
mysql> create table phoneList
(pid int(8) auto_increment primary key,phoneNo char(11),
isdeleted int(1) default 0,
operateTime timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into phoneList(phoneNo) values('12345678912');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from phoneList;
+-----+-------------+-----------+---------------------+
| pid | phoneNo     | isdeleted | operateTime         |
+-----+-------------+-----------+---------------------+
|   1 | 12345678912 |         0 | 2010-08-21 15:36:09 |
+-----+-------------+-----------+---------------------+
1 row in set (0.00 sec)
Oracle:
前提是: create table test(id char(5),name char(10));
(1)建立sequence
 create sequence test_id increment by 1 start with 1;
(2)建立触发器
A)执行命令ed test打开窗口,填写以下内容
create or replace trigger test_trigger
before insert
on test
for each row
declare
primary_key_value char(5);
begin
select test_id.nextval into primary_key_value from dual;
:new.id:=primary_key_value ;
end;
B)保存后,执行get test
C)然后执行/
如果/执行有错误的话,可以通过show error来显示错误并修改。
 
(3)
SQL> insert into test(name) values('one');
 
已创建 1 行。
 
SQL> select * from test; 
 
ID    NAME
----- ----------
1     one
 
==================Mysql操作列
//主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
//增加一个新列
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';
//删除列
alter table t2 drop column c;
//重命名列 
alter table t1 change a b integer;
//改变列的类型
alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';
//重命名表
alter table t1 rename t2;
加索引
mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
mysql> alter table tablename add index emp_name (name);
加主关键字的索引
mysql> alter table tablename add primary key(id);
加唯一限制条件的索引
mysql> alter table tablename add unique emp_name2(cardnumber);
删除某个索引
mysql>alter table tablename drop index emp_name;
修改表:
增加字段:
mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型:
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段
mysql> ALTER TABLE table_name DROP field_name;
标签: MySQL Oracle
扩展知识