1.建表: mysql: create table student(id int(2),name varchar(20)) oracle: create table student(id number(2),name varchar(20)) ( 注:int 和number可不标明长度,没有注明适用于两种数据库) 2.删除表: drop table student 3.删除表数据不破坏表结构: 两种方式:truncate table student delete from student (注:适用于mysql和oracle) 4. 插入: insert into student(id,name)values(15,,'jack') 5.删除: delete from student where id=15; 6.修改: update student set name='rose',age=18 where id=15(注:多个值用逗号隔开) 一些基础查询如下 1.去重复: select distinct name from student 2:排序: select id,name from student order by id,name desc(注:先按id正序排列,再按name倒叙排列) 3.规定要返回的记录的数目 mysql:select * from student limit 5(查出五条数据) oracle:select * from student where rownum <=5 4.模糊查询 select * from student where name like %媛%(匹配名字里带有媛字的,%代表匹配一个或多个字符,_下划线代表一个字符) select * from student where name not like %媛%(匹配名字里带有媛字的) 5.union 和union all union 操作符用于合并两个或多个 select 语句的结果集。注意,union 内部的 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 select 语句中的列的顺序必须相同 select id,name from person union all select id_o,orderon from order_o 6.not null 不允许空值 create table student(id int not null,name varchar(20) not null) 7.unique 值唯一 mysql:create table student(int id,name varchar(20) not null,unique(name)) oracle:create table student(int id,name varchar(20) not null unique) 当表已被创建时,如需在某一列创建 UNIQUE 约束,请使用下列 SQL: alter table student add unique(name) 8.PRIMARY KEY 约束唯一标识数据库表中的每条记录。 mysql:create table student(id int not null ,name varchar(20) not null,primary key(id)) oracle: create table student(id int not null primary key,name varchar(20) not null) 为已存在的表添加主键 alter table student add primary key(id) 9.foreign 外键 create table order(o_id int,orderno varchar(20),id int,foreign key(id) references student(id)) 10.default 默认值 create table student( id int,name varchar(20) default 'jack') 11.create index 创建索引 创建简单索引:create index index_name on student(name) 创建唯一索引: create unique index index_name on student(name) 删除索引 mysql:alter table student drop index name oracle:drop index name 12添加列 alter table student add name varchar(20) 13.删除列 alter table student drop column name 14.auto_increment mysql : create table(id int not null auto_increment primary key,name varchar(20)) 自增长初始值为1,想自己设置初始值如下: alter table student auto_increment =100 oracle: create sequence seq minvalue 1 start with 1 increment by 1 cache 10 insert into student(id,name)value(seq.nextval,'jack') 15view 视图 创建视图: create view view_st as select id from student where conditon 删除视图: drop view view_st 16. null 和 not null select * from student where name= null 或者不等于空值,注意 空值跟 0是两回事 |
|
来自: 昵称19003105 > 《SQL》