添加数据库
数据库名称为db_name
CREATE DATABASE db_name;
创建一张叫做Student的表
表中有四个属性，ID，姓名，年龄和班级，各个属性都有各自的类型。在这张表中主键是ID。
CREATE TABLE Student(
ID varchar(20) not null,
name varchar(10) not null,
age int,
class varchar(50),
primary key (ID));
向Student表中添加一条数据
INSERT INTO Student(ID,name,age,class) VALUES(10152510302,"张三",20,"一班");
INSERT INTO Student(ID,name,class) VALUES(10152510303,"李四","一班");
INSERT INTO Student(ID,name,age,class) VALUES(10152510304,"王五",21,"一班");
INSERT INTO Student(ID,name,age,class) VALUES(10152510305,"赵五",19,"一班");
INSERT INTO Student(ID,name,age,class) VALUES(10152510306,"赵五六",22,"一班");
INSERT INTO Student(ID,name,age,class) VALUES(10152510307,"钱二",22,"一班");
修改ID=10152510303的数据，将"一班"修改为"二班"
UPDATE Student SET class="二班" WHERE ID=10152510303;
查询Student中ID=10152510303的所有记录
SELECT * FROM Student WHERE ID=10152510303;
删除Student中ID=10152510303的所有记录
DELETE FROM Student WHERE ID=10152510303;
删除数据表格Student
DROP TABLE Student;
删除数据库db_name
DROP DATABASE db_name;
模糊查询
select * from Student where name like ‘赵%‘
select * from Student where name like ‘%五%‘
select * from Student where name like ‘%五‘
排序
select * from Student order by age asc ,name desc
总数
select count(*) as totalcount from Student
求和
select sum(age) as sumvalue from Student
平均
select avg(age) as avgvalue from Student
最大
select max(age) as maxage from Student
最小
select min(age) as minvalue from Student
UNION 运算符
运算符通过组合其他两个结果表 并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION
一起使用时（即 UNION ALL），不消除重复行。
select min(age) as value from Student
UNION
select max(age) as value from Student
select min(age) as value from Student
UNION ALL
select max(age) as value from Student
左外连接
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c
右外连接
select a.a, a.b, a.c, b.c, b.d, b.f from a RIGHT OUT JOIN b ON a.a = b.c
全外连接
full/cross （outer） join
分组
select sum(age) as sumvalue, class from Student group by class
having
select sum(age) as sumvalue, class from Student group by class having sum(age) > 20
select a.name,b.job from A a inner join B b on a.id=b.A_id
select a.name,b.job from A a left join B b on a.id=b.A_id
select a.name,b.job from A a right join B b on a.id=b.A_id
select a.name,b.job from A a full join B b on a.id=b.A_id
select A.id,A.name from A where exists (select B.* from B where A.id=B.A_id)
select A.id,A.name from A where not exists (select B.* from B where A.id=B.A_id)
向表中添加性别属性，并且将表中以存放的信息的sex值设为null。所有通过这种方式添加的属性都不能被设置为not null类型。
alter table…add
alter table Student add sex varchar(5);
要改变表中列的数据类型，请使用下面的语法：
alter table table modify column ..
alter table student modify column sex varchar(4);
从表中将class这一列属性删除。
alter table…drop
alter table Student drop sex；
DISTINCT 仅仅列出不同(distinct)的值
select * from Student
SELECT DISTINCT age from Student
取前几条数据
SQL Server 的语法：
SELECT TOP number|percent column_name(s) FROM table_name
例：
SQL Server 的语法：
SELECT TOP 5 age FROM Student
MySQL 语法
SELECT column_name(s) FROM table_name LIMIT number
例：SELECT * FROM Student LIMIT 5
Oracle 语法
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
例：SELECT * FROM Persons WHERE ROWNUM <= 5
IN 语法
IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT * FROM Student WHERE age IN(19,20);
NOT IN 语法
SELECT * FROM Student WHERE age NOT IN(19,20);
BETWEEN 语法
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT * FROM Student WHERE age BETWEEN 20 AND 22;
NOT BETWEEN 语法
SELECT * FROM Student WHERE age NOT BETWEEN 20 AND 22;
判断第一个为NULL，返回第二个值
SQL Server 的语法：
SELECT ISNULL(UnitsOnOrder,0) FROM Products
Oracle 语法
SELECT NVL(UnitsOnOrder,0) FROM Products
SELECT NVL2(UnitsOnOrder,1,0) FROM Products
MySQL 语法
SELECT IFNULL(UnitsOnOrder,0) FROM Products