SQL学习之基础篇

create table 表名 [表约束]
(列名1 数据类型 [缺省值1，列约束1],
 列名2 数据类型 [缺省值2，列约束2],
...
);

Alter table 表名 add (新列名 数据类型(长度));
Alter table 表名 modify (新列名 数据类型(长度));

drop table 表名;

create view 视图名 as

select 字段列表 from 表1
inner join 表2 on
表1.字段=表2.字段
where 条件

insert into 表名 [(列名1,...)]

values (值1,值2,...) [子查询]

例：

insert into student values (1003, 'E', 12);

update 表名 set 列名1=表达式1,列名2=表达式2,...where 条件;
例：

update student set age=18 where name='lili';

delete from 表名 where 条件；
例：
delete from student where age=12;

select student.name from student where age=12;
//查询年龄为12的学生姓名
select * from student;
//列出所有的学生
select student.name from student where age between 12 and 16;
//列出年龄在12到16之间的学生姓名
select student.name from student where age not between 12 and 16;
//列出年龄不在12到16之间的学生
select student.name from student where name like 'A%';
//列出以A开头的学生姓名
select avg(age), sum(age), max(age), min(age), max(age)-min(age) from student;
//列出所有学生年龄的和、平均值、最大值、最大值和最小值之间的差。
select * from student order by NO desc;
//按NO降序排列
select * from student order by NO asc;
//按NO升序排列

select 列名 from 表名 as alias_name;

select 列名 as alias_name from 表;

例：
select PO.orderID, P.lastname, P.firstname from person as P, product_orders as PO where P.lastname='Adams' and P.firstname='John';