2018.10.15：周一
-- 返回前5个数据
SELECT TOP 5 *
FROM Student;
-- 返回前50%的数据
SELECT TOP 50 PERCENT *
FROM Student;
-- 以"C"或"P"开头
SELECT *
FROM Student
WHERE Subject LIKE '[CP]%';
-- 不以"C"或"P"开头
SELECT *
FROM Student
-- WHERE Subject LIKE '[^CP]%';
-- WHERE Subject LIKE '[!CP]%'; //不行
WHERE Subject NOT LIKE '[CP]%';
-- 以"A-D"开头
SELECT *
FROM Student
WHERE Subject LIKE '[A-D]%';
--选择科目是java和c#的所有人
SELECT *
FROM Student
WHERE Subject IN ('Java','C#');
-- WHERE Subject NOT IN ('Java','C#');
--选择范围之间
SELECT *
FROM Student
WHERE StuAge BETWEEN 22 AND 24;
-- WHERE StuAge NOT BETWEEN 22 AND 24;
SELECT *
FROM Student
WHERE (StuAge BETWEEN 22 AND 24) AND Subject NOT IN('Java');
SELECT *
FROM Student
WHERE StuName BETWEEN '曹操' AND '关羽'
ORDER BY StuName;
--若有日期的话
--SELECT * FROM Student WHERE Date BETWEEN #07/04/1996# AND #07/09/1996#;
-- 连接
-- 内
SELECT *
FROM Student INNER JOIN UserLogin
ON Student.StuName=UserLogin.UserName;
-- 左外
SELECT *
FROM Student LEFT JOIN UserLogin
ON Student.StuName=UserLogin.UserName;
-- 右外
SELECT *
FROM Student RIGHT JOIN UserLogin
ON Student.StuName=UserLogin.UserName;
-- 自然
SELECT *
FROM Student FULL JOIN UserLogin
ON Student.StuName=UserLogin.UserName;
-- 自连接
SELECT S.StuName AS NAME1,U.UserName AS NAME2
FROM Student S,UserLogin U
WHERE S.ID =U.ID;
--组合并去重复
SELECT StuName AS NAME
FROM Student
UNION
SELECT UserName
FROM UserLogin
ORDER BY StuName;
--组合但未去重复
SELECT StuName AS NAME
FROM Student
UNION ALL
SELECT UserName
FROM UserLogin
ORDER BY StuName;
--复制表中的数据到另一个新建表
SELECT *
INTO Student1015
FROM Student;
-- 复制到另一个数据库//貌似不可行
SELECT *
INTO Student181015 IN 'Nwind.mdb'
FROM Student;
--只复制一些列到新表
SELECT StuName,StuAge
INTO Student1015_1
FROM Student;
--复制多个表中信息插入新表
SELECT UserLogin.ID,Student.StuName,Student.StuAge
INTO Student1015_2
FROM Student INNER JOIN UserLogin
ON Student.StuName=UserLogin.UserName
ORDER BY UserLogin.ID;
--建一个空表，查询返回时无返回值
SELECT *
INTO newtable
FROM Student
WHERE 1=0;
--把指定列插入另一个表的指定列
INSERT INTO Student1015_2(ID,StuName,StuAge)
SELECT TOP 3 Student.ID,Student.Subject,Student.StuAge
FROM Student
WHERE Student.Subject='c#';
--删除表
DROP TABLE Student1015_2;
--只删除表中数据
TRUNCATE TABLE Student1015_2;
--添加列
ALTER TABLE Student1015_1
ADD STU15 DATE;
--删除列
ALTER TABLE Student1015_1
DROP COLUMN STU15;
--更改列
ALTER TABLE Student1015_1
ALTER COLUMN STU15 DATETIME;
--创建视图
GO
CREATE VIEW [MYSTUDENT] AS
SELECT ID,StuName
FROM Student1015
WHERE StuAge=24;
GO
GO
CREATE VIEW [DYMYSTUDENT] AS
SELECT ID,StuName
FROM Student1015
WHERE StuAge>(SELECT AVG(StuAge) FROM Student1015);
GO
--查询视图
GO
SELECT *
FROM [MYSTUDENT]
GO
--更新视图//貌似不行
GO
CREATE OR REPLACE VIEW [MYSTUDENT] AS
SELECT ID,StuName
FROM Student1015
WHERE StuAge=23;
GO
--删除视图
DROP VIEW [MYSTUDENT];
--查询时间
SELECT *
FROM Student1015_1
WHERE STU15='2018-10-15';
