第六讲 SQL语言概述
2018-08-11 本文已影响0人
天际神游
SQL语言概述
- 1974年首次由Boyce和Chamber提出
- 1975-1979年, 由IBM的San Jose研究室在System R上首次实现
- 1986年ANSI/ISO推出SQL标准: SQL-86
- 1989年ANSI/ISO推出SQL标准: SQL-89
- 1992年进一步推出了SQL标准: SQL-92, 也称为SQL2
- 1999年进一步推出了SQL标准: SQL-99. 也称为SQL3
- SQL 2003; SQL 2006; SQL 2008
利用SQL建立数据库
可定义的数据类型
--- 创建一个数据库
create database Learn_sql_SCT;
-- 删除一个数据库
drop database Learn_sql_SCT;
-- 切换一个数据库
use learn_sql_SCT;
-- 定义一个学生表Student
-- 反引号它是为了区分mysql的保留字与普通字符而引入的符号. 我在这里使用mysql进行练习, 为了避免冲突, 把表名和列名括起来
create table `Student`(`S#` char(8) not null, `Sname` char(10), `Ssex` char(2), `Sage` integer, `D#` char(2), `Sclass` char(6));
-- 定义课程表Course
create table `Course`(`C#` char(3), `Cname` char(12), `Chours` integer, `Cred` float(1), `T#` char(3));
-- 都不给图数据库和表(使用DDL), 向表中追加元组(使用DML)
-- 向表中追加新的元组: Insert
-- 追加学生表中的元组
-- 方式一, 不给列名, 则值需要和定义时的列名顺序一致
insert into `Student` values ('98030101', 'SanZhang', 'male', 20, '03', '980301');
-- 方式二, 给出列名
insert into `Student`(`S#`, `Sname`, `Ssex`, `Sage`, `D#`, `Sclass`) values('98030102', 'SiZhang', 'female', 20, '03', '980301');
-- 追加课程表中的原组
insert into `Course` values('001', 'database', 40, 6, '001');
insert into `Course`(`Cname`, `C#`, `Cred`, `Chours`, `T#`) values('data structure', '003', 6, 40, '003');
-- 检索语句select
-- 检索学生表中的所有信息
select * from Student;
-- 检索学生表中所有学生的姓名和年龄
select Sname, Sage from Student;
-- 检索学生表中所有小于等于19岁的学生的年龄及姓名
select Sage, Sname from Student where Sage <= 19;
-- 可以这样写, 分号表示结束, 所以这样写的好处是语句清晰
select Sage, Sname
from Student
where Sage <= 19;
-- 检索条件的书写
-- 逻辑运算的优先次序要注意, 可以使用括号规定优先级别避免歧义, 优先级排序: not --> and --> or
-- 检索教师表中所有工资少于1500元或者大于2000元 并且是03系的教师的姓名(错误示例)
select `Tname` from `Teacher`
where `Salary`<1500 or `Salary` > 2000 and `D#`='03';
-- 正确示例
select `Tname` from `Teacher`
where (`Salary`<1500 or `Salary` > 2000) and `D#`='03';
-- 求学过001号课程, 或者学过002号课程的学生的学号
select `S#`
from SC
where `C#`='001' or `C#`='002';
-- 求既学过001号课程, 又学过002号课程的学生的学号(错误示例)
-- 对于`C#`而言, 其不可能是'001', 同时又是'002'
select `S#` from SC
where `C#`='001' and `C#`='002';
-- 正确示例
-- distinct 表示选择出来的结果(做投影)没有重复
-- 在选课表中, 检索成绩大于80分的所有学号(有重复示例)
select `S#` from `SC` where Score>80;
-- 在选课表中, 检索成绩大于80分的所有学号(无重复示例)
select distinct `S#` from `SC` where Score>80;
-- 根据选择出来的结果进行排序
-- 按学号由小到大的顺序显示出所有学生的学号及姓名
-- 默认是升序asc, 可以不写
select `S#`, Sname
from Student
order by `S#` asc;
-- 检索002号课大于80分的所有同学学号并按成绩由高到低顺序显示
-- 降序排列
select `S#` from `SC`
where `C#`='002' and Score > 80
order by Score desc;
-- 模糊查询问题
-- 含有"like"或者"not like"来表示 "字符串", 匹配规则:
-- "%"匹配零个或多个字符, "_" 匹配任意单个字符, "\" 转义字符
-- 检索所有姓张的学生的学号和姓名
select `S#`, Sname from Student where Sname like "%Zhang";
-- 检索名字为张某某的所有同学姓名
-- 这个...
-- 检索名字不姓张的所有同学姓名
select Sname from Student where Sname not like "%Zhang";
-- 多表联合查询
-- 按“001”号课成绩由高到低顺序显示所有学生的姓名(二表连接)
-- 表名.属性名的方式限定该属性属于哪个表, 如果属性只出现一次, 也可以不加
select Sname from Student, SC
where Student.`S#`=SC.`S#` and SC.`C#` = '001'
order by Score desc;
-- 按 数据库 课的成绩由高到低顺序显式所有同学姓名(三表连接)
select Sname from Student, SC, Course
where Student.`S#`=SC.`S#` and SC.`C#`=Course.`C#` and Course.`Cname`='database'
order by Score desc;
-- 连接运算涉及重名的问题时, 使用别名加以区分(as)
-- 西塔连接之不等值连接
-- 求有薪水差额的任意两位教师
select T1.Tname as Teacher1, T2.Tname as Teacher2
from Teacher T1, Teacher T2
where T1.Salary > T2.Salary;
-- 求年龄有差异的任意两位同学的姓名
select S1.Sname as Student1, S2.Sname as Student2 from Student S1, Student S2 where S1.Sage > S2.Sage;
-- 求既学过'001'号课又学过'002'号可的所有同学的学号
-- 先选一个学过'001'的, 再选一个学过'002'的, 最后连接起来即可
select SC1.`S#` from SC SC1, SC SC2 where SC1.`C#`='001' and SC2.`C#`='002' and SC1.`S#`=SC2.`S#`;
-- 求'001'号课成绩比'002'号课成绩高的所有的学生的学号
select SC1.`S#` from SC SC1, SC SC2
where SC1.`C#`='001' and SC2.`C#`='002' and SC1.Score > SC2.Score and SC1.`S#`=SC2.`S#`;
-- 列出没学过李明老师讲授课程的所有同学的姓名
-- 留待以后
-- 新增Insert, 更新update, 删除delete
-- 单一元组新增
insert into Teacher(`T#`, Tname, `D#`, Salary) values ('005', 'XiaoqiRuan', '03', '1250');
insert into Teacher values ('006', 'XiaohuLi', '03', '950');
-- 批元组新增
-- 将检索满足条件的同学新增到表中
insert into St (S#, Sname)
select S#, Sname from Student
where Sname like '%伟';
insert into St (S#, Sname)
select S#, Sname from Student order by Sname;
-- 新建Table: SCt(S#, C#, Score), 将检索到的成绩及格同学的记录新增到该表中
Insert Into SCt (S#, C#, Score)
Select S#, C#, Score From SC
Where Score>=60 ;
-- 追加成绩优秀同学的记录
Insert Into SCt (S#, C#, Score)
Select S#, C#, Score From SC
Where Score>=90 ;
--新建Table: St(S#, Sname, avgScore), 将检索到的同学的平均成绩新增到该表中
-- 此SELECT语句的书写语法后面再解释
Insert Into St (S#, Sname, avgScore)
Select S#, Sname, Avg(Score) From Student, SC
Where Student.S# = SC.S#
Group by Student.S#;
-- 元组删除Delete命令: 删除满足指定条件的元组
Delete From 表名 [ Where 条件表达式];
-- Where条件省略，则删除所有的元组(!!!)
-- 删除98030101号同学所选的所有课程
Delete From SC Where S# = '98030101';
-- 删除自动控制系的所有同学(简单的嵌套子查询)
Delete From Student Where D# in
(Select D# From Dept Where Dname = '自动控制');
-- 删除有四门不及格课程的所有同学(后面会讲)
Delete From Student Where S# in
( Select S# From SC Where Score < 60
Group by S# Having Count(*)>= 4 );
-- 元组更新Update命令: 用指定要求的值更新指定表中满足指定条件的元组的指定列的值
Update 表名
Set 列名 = 表达式 | (子查询)
[ [ , 列名 = 表达式 | (子查询) ] … ]
[ Where 条件表达式] ;
-- 将所有教师的工资上调%5
update Teacher
set Salary = Salary*1.05;
-- 将所有计算机系的教师工资上调10%
update Teacher
set Salary = Salary*1.1
where `D#` in
(select `D#` from Dept where Dname='computer');
-- 当某同学001号课的成绩低于该课程平均成绩时，将该同学该门课成绩提高5%
Update SC
Set Score = Score * 1.05
Where C# = ‘001’ and Score < some
( Select AVG(Score) From SC
Where C# = '001' ) ;
-- 将张三同学001号课的成绩置为其班级该门课的平均成绩
Update SC
Set Score = ( Select AVG(SC2.Score)
From SC SC1, Student S1, SC SC2, Student S2
Where S1.Sclass = S2.Sclass and SC1.S# = S1.S#
and SC2.S# = S2.S# and S1.Sname='张三'
and SC1.C# = '001' and SC1.C# = SC2.C# )
Where C# = '001' and S# in ( Select S# From Student
Where Sname = '张三' ) ;
-- 修正数据库: 修正数据库的定义, 主要是修正表的定义
-- 修正基本表的定义
alter table tablename
[add {colname datatype, …}] 增加新列
[drop {完整性约束名}] 删除完整性约束
[modify {colname datatype, …}] 修改列定义
-- 在学生表中增加两列Saddr, PID
Alter Table Student Add Saddr char[40], PID char[18] ;
-- 在mysql中
alter table Student add `Saddr` char(40), add `PID` char(18);
-- 将上列表中的Sname列的数据类型增加两个字符
alter table Student modify Sname char(10);
-- 删除学生姓名必须取唯一值的约束
Alter Table Student Drop Unique( Sname );
-- 在mysql中
alter table tablename drop index columnname;
-- 撤销表
drop table 表名;
-- 与delete的区别是, drop整个表包括表格式, 表中所有元组等都会被删除, delete仍然会保留表的基本结构.
-- 撤销数据库
drop database 数据库名;
-- 指定当前数据库
use 数据库名;
-- 关闭当前数据库
close 数据库名;
