select 列明1，列明2 from 表名
select * from 表名
select distinct 列名 FROM 表名
select * from 表名 where 列名 操作符 值
select * from 表名 where 列名 值1 between 值2
select * from 表名 where 列名 like 值 通配符|占位符
select * from 表名 where 列名 in (值1,值2，值3，..)
select * from 表名 where 列名1 操作符 值1 and 列名2 操作符 值2
select * from 表名 where 列名1 操作符 值1 or 列名1 操作符 值2
select * from 表名 where 列名1 操作符 值1 and (列名1 操作符 值1 or 列名1 操作符 值2)
select * from 表名 order by 列名1，列名2 desc
insert into 表名（列名1，列名2，..）values(值1，值2，..)
update 表名 set 列名1='值1',列名2=‘值2’ where 列名=‘值’
delete from 表名 where 列名1=‘值1’ and 列名2=‘值2’
CREATE DATABASE DB_Student
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,--主码
Sname CHAR(20) UNIQUE,--唯一值
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname char(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),--外码
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
alter table 表名 add 列名 列数据类型
alter table Student add S_entrance date--增加列
alter table student alter column Sage int--修改字段类型
alter table course add unique (Cname)--增加唯一性约束
drop table Student --删除基本表
drop table student cascade--删除基本表及相关依赖对象
select sno,sname from student
select sname,sno,sdept from student
select sname,2004-sage from student
select sname,'Year of Birth:',2004-sage, lower(sdept) from student--查询结果第二列是一个算数表达式
select sname name,'Year of Birth:' BIRTH,2004-sage birthday,LOWER(sdept) department from student--LOWER()小写字母
select sno from sc
select distinct sno from sc--消除重复行
select sno from sc
select all sno from sc
select sname from student where sqept='CS'
--=、>、<、>=、<=、!=、<>、!>、!< 比较的运算符
select sname,sage from student where sage<20
select distinct sno from sc where sage<20
select sname,sdept,sage from student where sage between 20 and 23
select sname,sdept,sage from student where sage not between 20 and 23
select sname,ssex from student where sdept in ('CS','MA','IS')
select sname,sage from student where sdept not in('CS','MA','IS')
select * from student where sno like '200215121'
select * from student where sno='200215121'
--% 任意长度字符串，_ 任意单个字符，ESCAPE 转义字符
select sname,sno,ssex from student where sname like '刘%'
select sname from student where sname like '欧阳__'
select sname,sno from student where sname like '__阳%'
select sname,sno,ssex from student where sname not like '刘%'
select cno,ccredit from course where cname like 'DB\_design' escape '\'
select * from course where cname like 'DB\_%i__' escape '\'
select sno,cno from sc where grade is null --null 空值
select sno,cno from sc where grade is not null
select sname from student where sdept='CS' and sage<20
select sname,sage from studnet where sdept='CS' or sdept='MA' or sdept='IS'
select sno,grade from sc where cno='3' order by grade desc -- order by 排序
select * from student order by sdept,sage desc --空值最大
SELECT TOP 100 * FROM Customers;
SELECT TOP 50 PERCENT * FROM Customers;
SELECT * FROM Customers WHERE City LIKE '[!bsp]%';
SELECT * FROM Customers WHERE City IN ('Paris','London');
SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SELECT *
INTO newtable
FROM table1
WHERE 1=0;
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
identity自增
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？
请使用 TRUNCATE TABLE 语句：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
