数据库概论_3标准化的SQL语言_ 数据定义
Student:
Course:
SC:
1.数据库的定义、删除
1.1数据库的定义(创建)
1 CREATE DATABASE student;
1.2数据库的删除
1 DROP DATABASE student;
2.表的定义、修改与删除
2.1表的定义
建表语句:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE TABLE student( 2 Sno VARCHAR(255) PRIMARY KEY, 3 Sname VARCHAR(255), 4 Ssex CHAR(4), 5 Sage SMALLINT, 6 Sdept CHAR(4) 7 );
添加数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 INSERT INTO student(Sno,Sname,Ssex,Sage,Sdept) VALUES ("20170707001","张一","男",20,"CS"); 2 INSERT INTO student VALUES ("20170707002","张二","男",21,"CS"); 3 INSERT INTO student(Sno,Sname,Ssex,Sage,Sdept) VALUES ("20170707003","张三","女",22,"MA"); 4 INSERT INTO student(Sno,Sname,Ssex,Sage,Sdept) VALUES ("20170707004","张四","女",18,"IS");
2.2表的修改
2.2.1向student表中添加“入学时间”列,数据类型为日期型
SQL语句:
1 ALTER TABLE student ADD s_entrance DATE;
结果:
2.2.2将年龄的数据类型由字符型改为整型
mysql>>
ALTER TABLE student MODIFY COLUMN Sage INT;
2.2.3修改表名
ALTER TABLE studentt RENAME TO course;
2.3表的删除
DROP TABLE course;
3.数据查询
3.1单表查询
3.1.1查所有
1 -- 方式一 2 SELECT * from student; 3 -- 方式二 4 SELECT Sno,Sname,Ssex,Sage,Sdept,S_entrance from student;
3.1.2选择查询表中某些列
SELECT Sno,Sname,Sdept FROM student;
3.1.3查询经过计算的值并起个别名birthday
SELECT Sname,2014-Sage as birthday FROM student;
3.1.4 查询所在系(用小写字母表示系名)
1 SELECT Sname,LOWER(Sdept) FROM student;
3.1.5去除结果中重复的行
SELECT DISTINCT s_entrance FROM student;
3.1.6查询满足条件的元组
1)年龄小于25岁的
SELECT * FROM student WHERE Sage<25;
2)Sno等于20170707001的
-- Sno值可以加"",也可以不加"" SELECT * FROM student WHERE Sno="20170707001";
3)年龄在20到30之间的
SELECT * FROM student WHERE Sage BETWEEN 20 AND 30; -- 等价于 SELECT * FROM student WHERE Sage >= 20 AND Sage <=30;
4)名字中含有“一”的
SELECT * FROM student WHERE Sname LIKE "_一";
5)名字中不含“一”的
SELECT * FROM student WHERE Sname NOT LIKE "_一";
6)按学号降序排列
-- 默认升序(ASC) SELECT * FROM student ORDER BY Sno DESC;
7)聚集函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 -- 统计元组个数 2 SELECT COUNT(*) FROM student; 3 -- 统计一列中的个数(空值不算) 4 SELECT COUNT(DISTINCT s_entrance) FROM student; 5 -- 计算一列值的总合(必须为int) 6 SELECT SUM(Sage) FROM student; 7 -- 计算一列值的平均值 8 SELECT AVG(DISTINCT Sage) FROM student; 9 -- 找一个值的最大值 10 SELECT MAX(Sage) FROM student; 11 -- 找一列值的最小值 12 SELECT MIN(Sage) FROM student;
8)GROUP BY:分组语句
SELECT Sname,SUM(Sage) FROM student GROUP BY Sname; -- GROUP BY 与WHERE语句不能共用 SELECT Sname,SUM(Sage) FROM student GROUP BY Sname HAVING SUM(Sage)>=50;
3.2连接查询
3.2.1等值与非等值连接查询
等值连接与非等值连接:当连接运算符为“=”时,称为等值连接。使用其它运算符称为非等值连接。
例:查询每个学生及其选修课程的情况
SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;
把目标列中重复的属性列去掉则为自然连接。
3.2.2自身连接
例:查询每一门课的间接先修课
SELECT f.Cno,s.Cpno FROM Course f,Course s WHERE f.Cpno=s.Cno;
3.2.3外连接
分为:左外连接,右外连接,内连接
left join,right join,inner join
如:
SELECT * FROM Course f LEFT JOIN Course s ON f.Cpno=s.Cno; SELECT * FROM Course f RIGHT JOIN Course s ON f.Cpno=s.Cno; SELECT * FROM Course f INNER JOIN Course s ON f.Cpno=s.Cno;
3.2.4多表连接
两个表以上的操作称为多表连接
例:查询每个学生的学号,姓名,选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
3.3嵌套查询
-- 子查询:嵌套查询 -- '='子查询结果只有一个 SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE classname='Java1812'); -- 子查询结果不只一个 SELECT * FROM student WHERE banji_id IN (SELECT id FROM banji WHERE classname IN 'Java1812','Java1807'));