SQL关系数据库示例

司马万

已于 2023-08-01 14:03:24 修改

阅读量2k

点赞数 7

文章标签：数据库

于 2023-02-22 16:29:25 首次发布

本文链接：https://blog.csdn.net/weixin_47219891/article/details/129161827

版权

以学生选课数据库（SCT）为例：

表1：学生：学号、姓名、性别、年龄、所属系别、班级

Student( S# char(8), Sname char(10),Ssex char(2),Sage integer,D# char(2),Sclass char(6))

表2：院系：系别、系名、系主任

Dept( D# char(2),Dname char(10),Dean char(10))

表3：课程：课号、课名、学时、学分、教师编号

Course(C# char(3),Cname char(2),Chours integer,Credit float(1),T# char(3))

表4：教师：教师编号、教师姓名、所属院系、工资

Teacher(T# char(3),Tname char(10),D# char(2),Salary float(2))

表5：选课：学号、课号、成绩

SC（ S# char(8),C# char(3),Score float(1)）

建立数据库--定义数据库和数据库里面的表

1.1创建DATABASE

Create database 数据库名；


create database SCT;#创建数据库SCT

1.2创建表

Create table 表名（列名数据类型 [Primary key|Unique][Not null],......）;

注意：[]——表示其扩起的内容可以省略。

|——表示其隔开两项可以二选其一。

Primary key:主键约束。每个表只能创建一个主键约束。

Unique：候选键，唯一性约束。可以有多个候选键。

Not null:非空约束。


Create Table Student(S# char(8) not null,
                     Sname char(10),
                     Ssex char(2),
                     Sage integer,
                     D# char(2),
                     Sclass char(6)
                      );
#定义学生表


 create teble Course(C# char(3),
                     Cname char(2),
                     Chours integer,
                     Credit float(1),
                     T# char(3));
#定义课程表

1.3插入数据

Insert into 表名[(列名[,列名]...)]

values (值[,值]，...);

例:


insert into Student
            values('78930102','张三','男',20,'03','789301');


insert into Student(S#,Sname,Ssex,Sage,D#,Sclass)
            values('78930102','张三','男',20,'03','789301');

1.3.1插入数据的优化-批量插入（一次性插入多条数据,建议一千条以内）


insert into Student(S#,Sname,Ssex,Sage,D#,Sclass)
            values('78930102','张三','男',20,'03','789301'),
                  ('78930103','李四','女',19,'04','789301');

1.3.2插入数据的优化-手动事务提交


start transaction;
insert into Student(S#,Sname) values ('78930102','张三'),('78930101','王奇');
insert into Student(S#,Sname) values ('78930103','李四'),('78930104','Joy');
insert into Student(S#,Sname) values ('78930105','Polle'),('78930106','Lay');
commit;

1.3.3插入数据的优化-主键顺序插入（比乱序插入效率高）

另：SQL日期数据类型

date: 年+月+日 yyyy-MM-dd

time:时+分+秒 hh:mm:ss

datetime 年+月+日+时+分+秒 yyyy-MM-dd hh:mm:ss

timetamp:实践戳，自带默认值，默认当前时间为默认值

1.3.4大批量插入数据-利用Load指令

基本Select语句-简单查询

2.1查询学生表中的所有信息


Select * From Student;

2.2查询学生表中所有学生的姓名及年龄


Select Sname,Sage
       From Student;

2.3查询学生表中所有年龄小于等于19岁的学生的年龄和姓名


Select Sname,sage
From Student
where Sage<=19;

2.4求或者学过001号课程或者学过002号课程的学生的学号


Select S#
From SC
Where  C#=‘001’OR C#='002';

2.5求既学过001号课程，又学过002号课程的所有学生的学号


select S1.S# From SC S1,SC S2
where S1.S#=S2.S# and S1.C#='001' and S2.C#='002';

2.6去除重复的记录-在选课表中，查询成绩大于80分的所有学号


Select S#
from SC
Where Score>80;
//有重复记录，例如一个同学有两门以上课程大于80


Select DISTINCT S#
from SC
Where Score>80;
//DISTINCT:过滤重复的记录。

2.7结果排序问题-按学号由大到小的顺序显示出所有学生的学号及姓名

order by 列名 [asc | desc ]，列名 [asc | desc ]......

注意：写在前面的列名优先级较高。

例：

order by age desc,classId desc，studentNo asc。

优先年龄降序，如年龄相等，按照班级降序排，如班级一样，再按照学号升序。

升序（默认）降序


select S#,Sname
from Student
Order by S# ASC;

注意：不对数据库的数据表进行排序，则默认按照主键升序排序。

2.8查询002号课程大于80分的所有学生学号并按成绩由高到低进行显示


select S#
from SC
where C#='002'and Score>80
Order By Score DESC;

2.9模糊查询--查询所有姓张的学生学号及姓名

%	匹配0个或多个字符
_	匹配任意单个字符
\	转义字符

列名 [not] like “字符串”


Select S#,Sname
From Student
where Sname Like '张%';

2.10查询名字为张某某的所有同学姓名


select Sname from Student
where Sname Like '张_ _ _ _';//两个下划线表示一个汉字

2.11查询名字不姓张的所有学生姓名


Select Sname
From Student
where Sname Not Like '张%';

2.12CASE-WHEN语句分类

以下表为例：

执行：


select  
CASE
 WHEN Ssex='1' then '男'
 ELSE '女'
end as Sex,Sname
from student;

结果：

2.13.Mysql limit语句（ Mysql独有）

limit n；排除结果集中的0条数据，直接取出前n条数据展示。

limit m,n;排除结果集前m条数据，直接取出接下来n条数据展示。

例：

limit (page-1)size,size

2.14.Mysql 系统函数

SQL函数分为单行【函数操针对一行中的数据，不跨行】函数和分组（聚合）【例：avg函数】函数。数据库中的得所有函数都必须有返回值。

1.数学函数

abs()：计算一个数绝对值。

ceil():浮点数取整，向下取整，注：ceil(40.000000)=40 ceil(-40.0001)= - 40 即数值变大

floor(）:向下取整。即数值变小

round():四舍五入取整

2.字符串函数

1.length()与char_length().

2.转换字符串中英文字母的大小写：upper()和lower()

3.去除字符串空格：ltrim()--->left和rtrim()--->right与trim()--->两边

4.instr('kjdahskfkfdjsaldsf','z')=0 (这是找不到的情况),instr('kjdahskfkfdjsaldsf','k')=1,instr('kjdahskfkfdjsaldsf','j')=2

5.substring(字符串，起点下标，截取字符数量)--->substr()，同上，下标从1开始。

6.left(字符串,数量)从左向右取

7.right(字符串，数量)从右向左操作

8.字符串拼接函数concat(null,'abc')=null,concat('abc','efg','llo')='abcefgllo'

3.日期函数

1.获取当前时间：localtime() 格式：yyyy-MM-dd hh:mm:ss 取得服务器当前时间

2.获取当前时间：now() 格式：yyyy-MM-dd hh:mm:ss取得本地当前时间

3.获取日期中的部分数据：

year() quarter():季度 month() day() weekofyear():这一年的第几周

hour() dayofyear():这一年的第几天 minute() second()

dayname()：当前日期是星期几，返回英文单词 dayofweek():当前日期是这一周的第几天，星期日返回1，以此类推

4.日期运算函数

计算两个日期相差的天数：datediff(被减数，减数)

在一个时间基础上进行加法运算：dateadd() 例：date_add(now(),interval 100 week)：现在时间加一百周；date_add(now(),interval 100 day)：现在时间加一百天；date_add(now(),interval -100 week)：现在时间减一百天。

5.多行（聚合）函数

1.max()

2.min()

3.sum()-->总

4.count()-->计数，注：对特定列计数时，null不计数，所以建议使用主键计数。另外不建议使用count(*)计数，因为在表连接时，尤其是外连接，会产生无效数据行，计数有误。count(整数)：例如count(1)，count(5)结果为整张表的行数。

5.avg()-->平均数

2.15 分组group by子句与having子句

having:对聚合列进行条件筛选，放在group by之后，不能单独存在。

2.16between ....(小).....and .......(大）.....

该函数可处理日期和数值类型数据,日期数据需打单引号。

联表查询

3.1按‘001’号课成绩由高到低顺序显示所有学生姓名


Select Sname FROM Student,SC
Where Student.S#=SC.S# and SC.C#='001'
Order By Score Desc;

3.2按‘操作系统’课成绩由高到低顺序显示所有学生姓名


Select Sname From Student,SC,Course
Where Student.S#=SC.S# and SC.C#=Course.C# and Cname='操作系统'
Order By Score DESC;

3.3求有薪水差额的任意两位老师(同表连接)


Select T1.Tname as Teacher1 ,T2.Tname as Teacher2
From Teacher T1,Teacher T2
Where T1.Salary>T2.Salary;

3.4表连接;

默认的表连接为交叉连接 select * from A,B where A.aid=B.bid；（注：具有主外关系的表才有表连接的意义），产生的结果集以子表为参照表。

内连接（inner join）：参照表（其数据行在连接结果集中至少要出现一次）默认为子表，一对多关系，多为子表。效果与交叉连接相同，正常情况下优先选择内连接。

左外连接（left join）：左表做参照表（join前）

右外连接（right join）:右表做参照表（join后）

3.5子查询

子查询可以全面取代表连接，表连接不能全面取代表连接。

出现位置：

1.where/having子句中。

2.表名，子查询的结果集必须取别名

3.列筛选位置

分类：相关子查询子查询语句不能独立运行

非相关子查询子查询语句能独立运行

3.6分页查询
4.PostgreSQL与MySQL比较

文档一：文档一

文档二：文档二

文档三：文档三

5.ACID保证

ACID保证是原子性（Atomicity）、一致性(Consisttency)、隔离性(Isolation)和耐用性(Durability)的缩写,是对数据库事务方面的一组要求。一般来说，系统对ACID保证越严格，则在性能上做出的让步就会越大。开发人员用ACID分类来交流不同方案所做的妥协，比如在聊NoSQL系统时。

5.1原子性：无论成败，事务必须整体执行。

原子性事务不能被部分执行：或者整个操作都执行了，或者数据库保持原样。比如说要删除某个用户的所使用记录，如果作为一个事务的话，或者全删掉，或者一条都不删。最终不能是有些删掉了，有些没删掉，还保持原来的状态。甚至在系统出错或断电后，任然要保持原子性。原子性在这里的意思是不可再分。

5.2一致性：始终确保约束条件。

成功完成的事务必须符合系统中定义的所有数据的完整性约束。例如：主键必须唯一、数据必须要符合某种特定的模式，或外键必须要指向所存在的实体。产生不一致状态的事务一般也会失败，然而小问题是可以自动解决的，比如：将数据转换为正确的形态。不要吧一致性的C与CAP定理中的C搞混了，那个C是指在读取分布式存储的数据时，确保呈现的是一个视图。

5.3隔离性：并发事务不会相互干扰。

不管是并发还是线性执行，隔离性事务的执行结果应该都是一样的。系统的隔离水平会直接影响它执行并发操作的能力。全局锁是一种比较低幼的隔离方式，由于在事务期间会把整个数据库锁住，所以只能串行处理事务。这是很强的隔离性保证，但效率也极低：那些跟事务完全无关联的数据集根本不应该被锁住（比如说：一个用户添加评论时不应该导致另一个用户无法更新自己的个人资料）。在现实情况中，数据库系统会提供更加精细的和有选择性的锁方式（比如：锁表、锁记录或锁数据域），以实现各种程度的隔离水平。更复杂的系统甚至可能会采用隔离水平最低的锁方式，乐观地并行执行所有事务，直到检测到冲突时才会逐步细化锁模式。