创建数据库
创建table,create table后可以"库名.表名"或者"库名.用户.表名",也可以用上面工具选择,如果名字中含有特殊关键字,mysql需要交``,sql server需要加[]
创建表
CREATE TABLE Student (
S# CHAR ( 8 ),
Sname CHAR ( 10 ),
Ssex CHAR ( 2 ),
Sage INTEGER,
D# CHAR ( 2 ),
Sclass CHAR ( 6 )
);
CREATE TABLE Dept (
D# CHAR ( 2 ),
Dname CHAR ( 10 ),
Dean CHAR ( 10 )
);
CREATE TABLE Course (
C# CHAR ( 3 ),
Cname CHAR ( 12 ),
Chours INTEGER,
Credit FLOAT ( 1 ),
T# CHAR ( 3 )
);
CREATE TABLE Teacher (
T# CHAR ( 3 ),
Tname CHAR ( 10 ),
D# CHAR ( 2 ),
Salary FLOAT ( 2 )
);
CREATE TABLE SC (
S# CHAR ( 8 ),
C# CHAR ( 3 ),
Score FLOAT ( 1 )
);
向表中追加元组
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98030101', '张三 ', '男', 20, '03', '980301');
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98030102', '张四 ', '女', 20, '03', '980301');
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98030103', '张五 ', '男', 19, '03', '980301');
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98040201', '王三 ', '男', 20, '04', '980402');
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98040202', '王四 ', '男', 21, '04', '980402');
INSERT INTO [Student]([S#], [Sname], [Ssex], [Sage], [D#], [Sclass]) VALUES ('98040203', '王五 ', '女', 19, '04', '980402');
INSERT INTO [Dept]([D#], [Dname], [Dean]) VALUES ('01', '机电 ', '李三 ');
INSERT INTO [Dept]([D#], [Dname], [Dean]) VALUES ('02', '能源 ', '李四 ');
INSERT INTO [Dept]([D#], [Dname], [Dean]) VALUES ('03', '计算机 ', '李五 ');
INSERT INTO [Dept]([D#], [Dname], [Dean]) VALUES ('04', '自动控制 ', '李六 ');
INSERT INTO [Course]([C#], [Cname], [Chours], [Credit], [T#]) VALUES ('001', '数据库 ', 40, 6, '001');
INSERT INTO [Course]([C#], [Cname], [Chours], [Credit], [T#]) VALUES ('003', '数据结构 ', 40, 6, '003');
INSERT INTO [Course]([C#], [Cname], [Chours], [Credit], [T#]) VALUES ('004', '编译原理 ', 40, 6, '001');
INSERT INTO [Course]([C#], [Cname], [Chours], [Credit], [T#]) VALUES ('005', 'C语言 ', 30, 4.5, '003');
INSERT INTO [Course]([C#], [Cname], [Chours], [Credit], [T#]) VALUES ('002', '高等数学 ', 80, 12, '004');
INSERT INTO [Teacher]([T#], [Tname], [D#], [Salary]) VALUES ('001', '赵三 ', '01', 1200);
INSERT INTO [Teacher]([T#], [Tname], [D#], [Salary]) VALUES ('002', '赵四 ', '03', 1400);
INSERT INTO [Teacher]([T#], [Tname], [D#], [Salary]) VALUES ('003', '赵五 ', '03', 1000);
INSERT INTO [Teacher]([T#], [Tname], [D#], [Salary]) VALUES ('004', '赵六 ', '04', 1100);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030101', '001', 92);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030101', '002', 85);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030101', '003', 88);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98040202', '002', 90);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98040202', '003', 80);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98040202', '001', 55);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98040203', '003', 56);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030102', '001', 54);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030102', '002', 85);
INSERT INTO [SC]([S#], [C#], [Score]) VALUES ('98030102', '003', 48);
查询
简单查询
检索学生表中所有年龄小于等于19岁的学生的年龄及姓名
SELECT Sname,Sage
FROM Student
WHERE Sage<=19;
检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名?
错误
SELECT Tname
FROM Teacher
WHERE Salary < 1500 OR Salary > 2000 AND D# = '03';
--相当于Salary < 1500 OR (Salary > 2000 AND D# = '03')
正确
SELECT Tname
FROM Teacher
WHERE (Salary < 1500 OR Salary > 2000) AND D# = '03';
在选课表中,检索成绩大于80分的所有学号
SELECT DISTINCT S#
FROM SC
WHERE Score>80;
检索结果中要求无重复元组, 是通过DISTINCT保留字的使用来实现的。
检索002号课大于80分的所有同学学号并按成绩由高到低顺序显示
SELECT S#
FROM SC
WHERE C#='002' AND Score>80
ORDER BY Score DESC;
结果排序问题
DBMS可以对检索结果进行排序,可以升序排列,也可以降序排列。
- Select语句中结果排序是通过增加order by子句实现的
order by 列名 [asc | desc]
- 意义为检索结果按指定列名进行排序,若后跟asc或省略,则为升序;若后跟desc, 则为降序。
模糊查询问题
比如检索姓张的学生,检索张某某;这类查询问题,Select语句是通过在检索条件中引入运算符like来表示的
- 含有like运算符的表达式
列名 [not] like“字符串”
- 找出匹配给定字符串的字符串。其中给定字符串中可以出现%, _等匹配符.
- 匹配规则:
- “%” 匹配零个或多个字符
- “_” 匹配任意单个字符 两个下划线表示一个汉字
- “ \ ” 转义字符,用于去掉一些特殊字符的特定含义,使其被作为普通字符看待, 如用 “%”去匹配字符%,用_ 去匹配字符_
检索所有姓张的学生学号及姓名
SELECT S#,Sname
FROM Student
WHERE Sname LIKE '张%';
多表联合查询
按“001”号课成绩由高到低顺序显示所有学生的姓名(二表连接)
SELECT
Sname
FROM
Student,
SC
WHERE
Student.S#= SC.S#
AND SC.C#= '001'
ORDER BY
Score DESC;
求有薪水差额的任意两位教师
SELECT
T1.Tname AS Teacher1,
T2.Tname AS Teacher2
FROM
Teacher T1,
Teacher T2
WHERE
T1.Salary> T2.Salary;
AS可以省略,为了方便区分第一行在映射结果时也重命名,Teacher1,Teacher2
求“001”号课成绩比“002”号课成绩高的所有学生的学号
SELECT S1.S#
FROM
SC S1,
SC S2
WHERE
S1.S# = S2.S#
AND S1.C#= '001'
AND S2.C#= '002'
AND S1.Score > S2.Score;
增删改
单一元组新增
INSERT INTO Teacher ( T#, Tname, D#, Salary )
VALUES
( '005', '阮小七', '03', '1250' );
INSERT INTO Teacher
VALUES
( '005', '阮小七', '03', '1250' );
批元组新增
讲选择的内容加入表格
新建立Table: St(S#, Sname), 将检索到的满足条件的同学新增到该表中
CREATE TABLE St ( S# CHAR ( 8 ), Sname CHAR ( 10 ) );
INSERT INTO St ( S#, Sname )
SELECT
S#,
Sname
FROM
Student
WHERE
Sname LIKE '%三';
删除SC表中所有元组
DELETE FROM SC;
删除98030101号同学所选的所有课程
DELETE
FROM
SC
WHERE
S# = '98030101';
将所有计算机系的教师工资上调10%
UPDATE Teacher
SET Salary = Salary * 1.1
WHERE
D# IN ( SELECT D# FROM Dept WHERE Dname = '计算机' );
当某同学001号课的成绩低于该课程平均成绩时,将该同学该门课成绩提高5%
UPDATE SC
SET Score = Score * 1.05
WHERE
C# = '001'
AND Score < SOME ( SELECT AVG ( Score ) FROM SC WHERE C# = '001' );
修正与撤销
在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID
ALTER TABLE Student ADD Saddr CHAR [40],PID CHAR [18];
将上例表中Sname列的数据类型增加两个字符
ALTER TABLE Student ALTER COLUMN Sname CHAR ( 10 );
SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Oracle:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Oracle 10G 之后版本:
ALTER TABLE table_name
MODIFY column_name datatype;
删除学生姓名必须取唯一值的约束
ALTER TABLE Student DROP CONSTRAINT Sname;
撤消学生表Student
DROP TABLE Student;
SQL-delete语句只是删除表中的元组,而撤消基本表droptable的操作是撤消包含表格式、表中所有元组、由该表导出的视图等相关的所有内容,所以使用要特别注意。
撤消SCT数据库
DROP DATABASE SCT;