1.数据库基础知识
(1)什么是数据库?数据库使用来干什么的?
例如:学校的高校图书管理:图书馆有包含图书信息的数据库,该数据库拥有借阅者信息,图书信息,借书信息等,通过该数据库图书管理员可以查找借阅者的信息,借阅者可以查询借阅图书的信息等,还有公司员工数据库等。数据库(DBMS)是为了方便数据的存储和管理,它将数据按照特定的 规则存储在磁盘上,就是一个存储数据的仓库。简单来说数据库就是用来存储信息的仓库,但是存储的信息不是乱存,而是通过建立关系表,通过各关系表来存储信息,删除信息,查找信息等。
(2)关系.属性.元组.主码
关系:关系简单来说就是一个表,例如:高校图书管理中的读者表,
属性:属性就是关系表中每一列,如:高校图书管理中读者表中的读者卡号,姓名,性别,单位,办卡日期等。
元组:元组就是关系表中的每一行数据,如:高校图书管理中读者表中的一组数据,(2100001,李丽,女,数计学院,2021-03-06)
主码:主码就是在关系表中属性或属性组可以唯一表示一个元组,而其子集不能,则称该属性或者属性组为一个主码,如高校图书管理中读者表中的读者卡号。
(3)常用的数据库软件
常见的数据库产品:
国外
MySQL 快捷、可靠 开源、免费
Oracle:功能强大,收费.
SQL Server(微软): 只能安装在Windows操作系统
DB2 (IBM):适合处理海量数据,收费.
(4)sql
2.使用sql语句创建数据库,创建表,删除表及相关约束条件(DDL)
create DATABASE schooldb
(2)使用create语句创建表(注意要选中存入的数据库建表)
表中数据类型:
字符类型:char() 固定长度,varchar() 可变长度,定义最大长度大小 。
日期类型:date 年月日, datetime 年月日,时分秒。
整形:TINYINT 范围为:(-128,127),一个字节
SMALLINT 范围为:(-32768 ,32767),两个字节
MEDIUMINT范围为: (-8388608 ,8388607),三个字节
-- 创建学生表 学号,姓名,性别,生日,电话,地址,身高,注册时间
CREATE TABLE student(
num INT,
NAME VARCHAR(10),
gender CHAR(1),
birthday DATE,
phone CHAR(11),
address CHAR(30),
height DECIMAL(3,2),
reg_time DATETIME
)
(3)使用相关约束条件创建学生表
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号,主键',
NAME VARCHAR(10) NOT NULL COMMENT '姓名',
gender CHAR(1) NOT NULL COMMENT '性别',
birthday DATE ,
phone CHAR(11) NOT NULL UNIQUE,
address CHAR(30),
height DECIMAL(3,2) CHECK(height<2.7),
reg_time DATETIME COMMENT '注册时间'
)
primary key 设置主键,
AUTO_INCREMENT主键自增,只能修饰主键
如图所示每给建好的表插入数据就会自动增值。
NOT NULL 设置属性值不为空
COMMENT 给属性列添加注释
unique 唯一约束,属性的数据不能重复,如给电话号添加unique约束电话号就不能重复
check 约束 给属性添加相关限制
(4)删除数据库和表
删除数据库:
DROP DATABASE schooldb;
删除表:
DROP TABLE student;
3.修改表名及给表插入数据(注意,数据库名不能被修改)
-- 修改表名
RENAME TABLE student TO stu
-- 对数据进行操作
(1)-- 插入 insert
/*
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)
*/
INSERT INTO student(NAME,gender,birthday,phone)VALUES('张三','男','2003-3-5','15233336666')
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
VALUES('张三','男','2003-3-5','15233336688','汉中',1.75,NOW())
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
VALUES('张三丰','男','2003-3-5','15233336677','汉中',1.75,NOW()),
('李四','女','2003-3-3','15233336699','汉中',1.85,NOW()),
('王五','男','2003-4-3','15233336609','汉中',1.95,NOW())
INSERT INTO student SET NAME = '赵六',gender='女',phone='15777778888'
INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time)
SELECT NAME,gender,birthday,phone,address,height,reg_time FROM student
(2)修改表数据 UPDATE
UPDATE student SET address='陕西',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1
(3)删除表数据
DELETE FROM stu -- 没有条件 删除所有的数据
DELETE FROM student WHERE num = 13
上述代码删除了学号为13的学生
4.查询语句(DQL)
查询结果处理:
![](https://img-blog.csdnimg.cn/direct/c7816af309bf45228fcc1bc2ca7b1fb2.png)
(1)分组查询 group by ,count() having,order by
-- 分组查询
-- 将某类数据分到一个组中进行处理 例如按性别查询
-- 查询男生 女生各有多少人(统计数量) 例如按性别分组 每月销量
-- GROUP BY 分组条件(列名)
SELECT COUNT(*),gender FROM student GROUP BY gender
SELECT SUM(height),gender FROM student GROUP BY gender
SELECT AVG(height),gender FROM student GROUP BY gender
SELECT MAX(height),gender FROM student GROUP BY gender
(2)条件查询
(1)比较大小
例:查询计算机科学系全体学生的名单
select Sname
from Student
where Sdept = 'CS';
(2)确定范围
例:查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
select Sname,Sdept,Sage
from Student
where Sage between 20 and 23;
(3)确定集合
例:查询计算机科学系(CS)、数学系(MA)、信息系(IS)学生的姓名和性别。
select Sname,Ssex
from Student
where Sdept in ('CS','MA','IS');
(4)字符匹配
在字符匹配中,%代表任意长度(可以为0)的字符串,下划线代表任意单个字符。如果要查询的字符串中含有%。当匹配串中不含通配符时,可以用等于运算符代替like。
例:查询以"DB_"开头,且倒数第三个字符为i的课程的详细情况。
select *
from Course
where Cname like 'DB\_%i__' escape '\';
(5)涉及空值的查询
空值查询中的is和is not不能用等于或者不等于代替,等于或不等于的前提是有值,空值没有值。
例:查询所有有成绩的学生学号和课程号。
select Sno,Cno
from Sc
where Grade is not null
(6)多重条件查询
逻辑运算符and和or可以用来连接多个查询条件。and的优先级高于or,但用户也可以用括号改变优先级。
例:查询计算机科学系年龄在20岁以下的学生姓名。
select Sname
from Student
where Sdept = 'CS' and Sage > 20;
(3)连接查询
通常情况下,只查询一个表并不能满足我们的需求,这时就引入了连接查询。如果一个查询同时涉及两个以上的表,则称之为连接查询。
等值与非等值连接查询
连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词,其一般格式为
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
[<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>
当比较运算符为“=”时,称为等值连接,其余则为非等值连接。连接条件中的列名称为连接字段,各连接字段的名称不必相同,但必须是可比的。在等值连接中,去掉目标列中的重复列称为自然连接
例:查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
select Student.Sno,Sname
from Student,Sc
where Student.Sno = Sc.Sno and Sc.Cno = '2' and Sc.Grade > 90;
自身连接一个表自己与自己连接叫做自身连接。
例:查询每一门课的间接先修课(即先修课的先修课)。
select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno = second.Cno;
外连接
普通连接操作只输出满足连接条件的元组,外连接操作以指定表为主体,将主体表中不满足连接条件的元组一并输出。外连接分为左外连接和右外连接,如果列出左表中的所有元组,则称为左外连接;列出右表的所有元组,则成为右外连接。
例:查询每个学生及其选修课程的情况,未选课的学生的选课情况以空值输出。
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join Sc on (Student.Sno = Sc.Sno);
或
select Student.*,Sc.*
from Student left outer join Sc using(Sno);
多表连接
两个表以上的连接称为多表连接
例:查询每个学生的学号、姓名、选修的课程名及成绩。
select Student.Sno,Sname,Cname,Grade
from Student,Sc,Course
where Student.Sno = Sc.Sno and Sc.Cno = Course.Cno;
(4)嵌套查询
在SQL语言中,一个select-from-where语句称为一个查询块。将一个查询块嵌套在另外一个查询块中的where子句或having短语的条件中的查询称为嵌套查询。被嵌套的查询块称为内层查询或子查询,另一个则为外层查询或父查询。需要注意的是,group by子句只能对最终的查询结果进行排序,也就是只能用在最外层的查询中,不能用在任何子查询中。
如果子查询的条件不依赖于父查询,则称为不相关子查询。如果子查询的条件依赖于父查询,则称为相关子查询。
带有in谓词的子查询
例:查询选修了课程名为“信息系统”的学生的学号和姓名。
select Sno,Sname
from Student
where Sno in
(select Sno
from Sc
where Cno in
(select Cno
from Course
where Cname = '信息系统'
)
);
带有比较运算符的子查询
父查询与子查询之间用比较运算符进行连接,这时内层查询返回的是单个值。
例:找出每个学生超过他自己选修课程平均成绩的课程号。
select Sno,Cno
from Sc x
where Grade >= (
select avg(Grade)
from Sc y
where x.Sno = y.Sno);