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 列名 列數(shù)據(jù)類型
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--查詢結果第二列是一個算數(shù)表達式
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
如果我們僅僅需要刪除表內的數(shù)據(jù)，但并不刪除表本身证鸥，那么我們該如何做呢僚楞？
請使用 TRUNCATE TABLE 語句：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
