数据库总结

1.数据库基础知识

(1)什么是数据库？数据库使用来干什么的？

例如：学校的高校图书管理：图书馆有包含图书信息的数据库，该数据库拥有借阅者信息，图书信息，借书信息等，通过该数据库图书管理员可以查找借阅者的信息，借阅者可以查询借阅图书的信息等，还有公司员工数据库等。数据库(DBMS)是为了方便数据的存储和管理，它将数据按照特定的 规则存储在磁盘上,就是一个存储数据的仓库。简单来说数据库就是用来存储信息的仓库，但是存储的信息不是乱存，而是通过建立关系表，通过各关系表来存储信息，删除信息，查找信息等。

(2)关系.属性.元组.主码

关系：关系简单来说就是一个表，例如：高校图书管理中的读者表，

属性：属性就是关系表中每一列，如：高校图书管理中读者表中的读者卡号，姓名，性别，单位，办卡日期等。

元组：元组就是关系表中的每一行数据，如：高校图书管理中读者表中的一组数据，（2100001，李丽，女，数计学院，2021-03-06）

主码：主码就是在关系表中属性或属性组可以唯一表示一个元组，而其子集不能，则称该属性或者属性组为一个主码，如高校图书管理中读者表中的读者卡号。

(3)常用的数据库软件

常见的数据库产品：

国外

MySQL 快捷、可靠 开源、免费

Oracle:功能强大,收费.

SQL Server(微软): 只能安装在Windows操作系统

DB2 (IBM):适合处理海量数据,收费.

国内

南大通用GBASE: 天津南大通用数据技术股份有限公司

达梦: 武汉达梦数据库股份有限公司

人大金仓 : 北京人大金仓信息技术股份有限公司

神通 : 神舟通用公司

(4)sql

结构化查询语言 (Structured Query Language) 简称 SQL ，是一种特殊

目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以

及查询、更新和管理关系数据库系统 .

SQL 优点：

不是某个特定数据库供应商专有的语是言，几乎所有 DBMS 都支持 SQL

简单易学，灵活使用可以进行非常复杂和高级的数据库操作

2.使用sql语句创建数据库，创建表,删除表及相关约束条件(DDL)

(1)使用create语句创建数据库

create DATABASE schooldb

(2)使用create语句创建表(注意要选中存入的数据库建表)

  表中数据类型:
字符类型：char() 固定长度，varchar() 可变长度，定义最大长度大小 。
 日期类型：date 年月日， datetime 年月日，时分秒。

整形:TINYINT  范围为：(-128,127),一个字节

SMALLINT 范围为：(-32768 ,32767),两个字节

MEDIUMINT范围为： (-8388608 ,8388607)，三个字节

INT 范围为：(-2147483648, 2147483647)，四个字节

BIGINT范围为： (-9223372036854775808 9223372036854775807)，八个字节

​
-- 创建学生表  学号，姓名，性别，生日，电话，地址，身高，注册时间
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 表名 SET 列名 = ‘新值’ WHERE 条件

 UPDATE student SET address='陕西',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1

(3)删除表数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名 ; 清空整张表

 DELETE FROM stu -- 没有条件 删除所有的数据
 DELETE FROM student WHERE num  = 13 
    

上述代码删除了学号为13的学生

4.查询语句(DQL)

数据查询语言查询是使用频率最高的一个操作

可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

语法：

select 查询列表 from 表名;

 特点：

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

查询结果处理：

特定列查询:select column1,column2 from table

全部列查询: select * from table

算数运算符:+ - * /

排除重复行: select distinct column1,column2 from table

查询函数：select 函数; / 例如version()

例如：

(1)分组查询 group by  ,count()  having,order by

[where 筛选条件 ]

group by 分组的列表

[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);