数据库系统原理之SQL语言

最新推荐文章于 2022-06-12 18:21:06 发布

C++小白、

最新推荐文章于 2022-06-12 18:21:06 发布

阅读量3k

点赞数 2

分类专栏：学习笔记文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_43641035/article/details/105223077

版权

本文详细介绍了SQL语言的组成部分，包括数据定义语言(DDL)、数据操纵语言(DML)和数据控制语言(DCL)，并讲解了如何使用DDL创建和修改数据库及表，DML中的插入、查询、更新和删除操作，以及DCL中的权限管理和事务控制。此外，还探讨了MySQL内置函数、视图、索引、命令行操作以及数据完整性、触发器、并发控制等数据库管理的重要概念。

摘要由CSDN通过智能技术生成

一、SQL语句介绍

二、SQL组成

1.数据定义语言（Data Definition Language，简称DDL）

2.数据操纵语言（Data Manipulation Language，简称DML）

3.数据控制语言（Data Control Language，简称DCL）

5.元组的删除delete和更新update操作

6.更新表alter

7.子查询

7.1 标量子查询------子查询返回结果只有一行,一列

7.2 列子查询------子查询返回一列多行

7.3 表级子查询------子查询返回结果为多行,多列

3.mysql内置函数可以在where条件后面使用

4.left从字符串左侧截取指定数量字符

5.right从字符串右侧截取指定数量的字符

6.substring从字符串指定位置截取指定数量字符

7.内置函数可以用在select显示的字段名中

13.current_date返回系统日期

14.current_time返回系统时间

一、windows cmd 命令窗口连接到 mysql

4.游标：被select语句检索出来的结果集

一、SQL语句介绍

数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数据库的代码。结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

二、SQL组成

1.数据定义语言（Data Definition Language，简称DDL）

数据定义语言主要用于数据库及各种数据库对象的创建、删除修改等操作。主要的SQL语句包括以下3几个：

create用于创建数据库及数据库对象

alter用于更新数据库及数据库对象

delete用于删除数据库及数据库对象

2.数据操纵语言（Data Manipulation Language，简称DML）

数据操纵语言主要用于数据库中各种数据库对象的检索及数据的修改。主要SQL语句包括以下几个：

selecte查询表中数据

insert向表中插入数据

update更新表中数据

delete删除表中数据

3.数据控制语言（Data Control Language，简称DCL）

主要用于安全管理。主要SQL语句包括以下几个：

grank授予权限

revoke收回权限

三、数据定义语言DDL

1.数据库定义

创建数据库：

CREATE DATABASE SCT；

选择当前数据库：

USE SCT；

删除数据库：

DROP DATABASE SCT；

查看数据库：

SHOW　DATABASES；

2.表定义

创建表：

CREATE TABLE Student(
	Sno CHAR(9) NOT NULL,
	Sname CHAR(10),
	Ssex CHAR(2),
	Sage INT,
	Dno CHAR(2),
	Sclass CHAR(6));

用于表创建的常用关键字：auto_increment（列自增属性）、not null（非空约束）、primary key（主键约束）unique（唯一性约束）、default（默认值）

查看表的结构：

SHOW　CLOUMNS　IN Student;
//或者
DESC Student;

查看表：

SHOW TABLES;

四、数据操纵语言DML

１.插入数据

INSERT INTO Student VALUE(
	'98030101','张三','男',20,'03','980301');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Dno,Sclass) VALUE(
	'98030102','张四','女',20,'03','980301');
INSERT INTO Student VALUE(
	'98030103','张五七','男',19,'03','980301');
INSERT INTO Student VALUE(
	'98040201','王五','男',20,'04','980402');
INSERT INTO Student VALUE(
	'98040202','王四','男',21,'04','980402');

已知表的关系模式：

学生表：Student(Sno,Sname,Ssex,Sage,Dno,Sclass)

部门表：Dept(Dno,Dname,Dean)

教师表：Teacher(Tno,Tname,Dno,Salary)

课程表：Course(Cno,Cname,Chours,Credit,Tno)

选课表：SC(Sno,Cno,Score)

２.select查询

/*查找学生表中所有学生的信息*/
SELECT * FROM Student;
SELECT * FROM SC;
/*查找学生表中所有学生的年龄和姓名*/
SELECT Sname,Ssex FROM Student;
/*查找学生表中所有年龄小于等于19岁的学生的年龄和姓名*/
SELECT Sage,Sname FROM Student WHERE Sage<=19;
/*检索教师表中所有工资小于1100或者大于1200并且是03系的教师姓名*/
SELECT Tname FROM Teacher WHERE Salary<1100 OR Salary>1200 AND Dno='03';
/*检索教师表中所有工资小于1100并且大于1200并且是03系的教师姓名*/
SELECT Tname FROM teacher WHERE Salary BETWEEN 1100 AND 1200 AND Dno='03';
SELECT Tname FROM teacher WHERE Salary>1100 AND Salary<1200 AND Dno='03';
/*检索学过001号课或者学过002号课的学生的学号*/
SELECT Sno FROM SC WHERE Cno='001' OR Cno='002';
/*检索学过001号课又学过002号课的学生的学号 （空值）*/
SELECT Sno FROM SC WHERE Cno='001' AND Cno='002';
/*在选课表中，检索成绩大于80分的学生的学号（要求除去重复值）*/
SELECT DISTINCT Sno FROM SC WHERE Score>80;
/*检索002号课中成绩大于80分的所有学生的学号并按成绩升序显示*/
SELECT Sno,Score FROM SC WHERE Cno='002' AND Score>80 ORDER BY Score ASC;

WHERE子句

/*检索所有姓张的学生的学号和姓名*/
SELECT Sno,Sname FROM Student WHERE Sname LIKE '张%';
/*检索所有张某某的学生的学号和姓名*/
SELECT Sno,Sname FROM Student WHERE Sname LIKE '张__';
/*检索所有不姓姓张的学生的学号和姓名*/
SELECT Sno,Sname FROM Student WHERE Sname NOT LIKE '张%';

注意：（1）<>与!=效果相同（2）like后面的'_'代表一个字符，'%'代表多个字符。

3.多表联合查询

若一个查询涉及2个或2 个以上的表，则称之为多表连接查询。

1.交叉连接 cross join（笛卡尔积）

一张表的每一行与另一张表的每一行的笛卡尔乘积。返回的是查询结果集的记录行数，等于其所连接的两张表记录行数的乘积。

/*按001号课程的成绩由高到低显示所有学生的姓名（二表等值连接）*/
SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='001' ORDER BY Score DESC;
/*按数据库课程的成绩由高到低显示所有学生的成绩（三表等值连接）*/
SELECT Sname FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Cname='数据库' ORDER BY Score DESC;
/*查询薪水有差异的任意两位教师的姓名（自表不等值连接）*/
SELECT T1.Tname AS teacher1,T2.Tname AS teacher2 FROM Teacher AS T1,Teacher AS T2 WHERE T1.Salary>T2.Salary;
/*求年龄有差异的任意两位学生的姓名*/
SELECT S1.Sname AS student1,S2.Sname AS student2 FROM Student AS S1,Student AS S2 WHERE S1.Sage>S2.Sage;
/*既学过001号课程又学过002号课程的所有学生的姓名(自表连接，一个表查001另一个查002)*/
SELECT S1.Sno FROM SC AS S1,SC AS S2 WHERE S1.Sno=S2.Sno AND S1.Cno='001' AND S2.Cno='002';
/*求001号课成绩比002号课成绩高的所有学生的学号*/
SELECT S1.Sno FROM SC AS S1,SC AS S2 WHERE S1.Sno=S2.Sno AND S1.Cno='001' AND S2.Cno='002' AND S1.Score>S2.Score;

2.内连接 inner join

内连接在交叉连接的基础上设置连接条件来移除查询结果中某些记录。

分为：等值连接、非等值连接、自连接，其中等值连接中会包含一个主键和一个外键。

写SQL三部法：

/*查询数据库课程的所有学生信息，只显示姓名和成绩*/
/*第一步：搭框架*/
select * from student inner join scores on student.stuNo=scores.stuNo
inner join course on scores.courseNo=course.courseNo;
/*第二步：看条件*/
select * from student inner join scores on student.stuNo=scores.stuNo
inner join course on scores.courseNo=course.courseNo
where coursename='数据库';
/*第三步：显示字段*/
select name,score from 
student inner join scores on student.stuNo=scores.stuNo
inner join course on scores.courseNo=course.courseNo
where coursename='数据库';

3.外连接 outer join

左外连接（left outer join）：获取左表所有行的信息，即使右表中没有对应匹配的行的信息。右表中没有匹配的部分用NULL代替。

右外连接（right outer join）：获取右表所有行的信息，即使左表中没有对应匹配的行的信息。左表中没有匹配的部分用NULL代替。

/*内连接inner join(,) 外连接left/right/full outer join，连接条件用on*/
/*求所有教师的任课情况并按教师号进行排序（使用内连接会使没有任课教师的信息丢失）*/
/*隐式内连接*/
SELECT Teacher.Tno,Tname,Cname FROM Teacher,Course WHERE Teacher.Tno=Course.Tno ORDER BY Tno ASC;
/*标准内连接*/
SELECT Teacher.Tno,Tname,Cname FROM Teacher INNER JOIN Course ON Teacher.Tno=Course.Tno ORDER BY Tno ASC;
/*这里使用左外连接*/
SELECT Teacher.Tno,Tname,Cname FROM Teacher LEFT OUTER JOIN Course ON Teacher.Tno=Course.Tno ORDER BY Tno ASC;

inner join和outer join 的连接条件写在ON里。

4.自关联

是同一张表做连接查询, 自关联下,一定找到同一张表可关联的不同字段

/* 例 2：查询广东省的所有城市*/
SELECT * from areas a1 INNER JOIN areas a2 on a1.id = a2.pid WHERE a1.name = '广东省';

4.通过子查询批量插入元组

/*在新表中SCT插入成绩合格同学的记录*/
CREATE TABLE SC_pass(
		Sno CHAR(9) NOT NULL,
		Cno CHAR(3),
		Score INT);
INSERT INTO SC_pass(Sno,Cno,Score) SELECT Sno,Cno,Score FROM SC WHERE Score>=60;
SELECT * FROM SC_pass;

5.元组的删除delete和更新update操作

/*删除满足指定条件的元组*/
DELETE FROM SC_pass WHERE Sno='98030101';
/*删除所有元组*/
DELETE FROM SC_pass;

/*更新指定表中满足条件的元组指定列的值*/
/*将所有教师薪水上调5%*/
UPDATE Teacher SET salary=salary*1.05;
/*将所教001号课程的教师薪水上调10%*/
UPDATE Teacher SET salary=salary*1.1 WHERE Tno='001';
SELECT * FROM Teacher;

6.更新表alter

/*ADD子句：在表的最后增加一列
关键字：FIRST加在第一列， AFTER (column)加在某列之后
在学生表的基础上增加一列Saddr*/
ALTER TABLE Student ADD Saddr CHAR(40);
ALTER TABLE Student ADD Saddr CHAR(40) FIRST;
ALTER TABLE Student ADD Saddr CHAR(40) AFTER Ssex;
SHOW COLUMNS IN Student;

/*CHANGE子句：修改表中一列的名称及数据类型*/
ALTER TABLE Student CHANGE Sno Sno1 CHAR(20) DEFAULET '0';

/*ALTER子句：修改某一列的默认值*/
ALTER TABLE Student ALTER Sno1 SET DEFAULT '00';

/*MODIFY子句：修改某一列的数据类型
将学生表中Sname列的数据类型增加2个字符*/
ALTER TABLE Student MODIFY Sname CHAR(12);

/*DROP子句：删除表中多余的列
删除学生名的唯一约束*/
ALTER TABLE Student DROP UNIQUE(Sname);
/*删除Saddr这一列*/
ALTER TABLE Student DROP Saddr;

/*RENAME TO子句：表重命名*/
ALTER TABLE Student RENAME TO NewStudent;

/*撤消表和数据库*/
DROP TABLE SC_pass;
DROP DATABASE SCT;
/*切换数据库*/
USE SCT;
/*关闭数据库*/
CLOSE SCT;

7.子查询

/*列出张三、王三所有的信息*/
SELECT * FROM Student WHERE Sname IN ('张三','王四');/*直接用枚举的形式给出一个子集合*/
SELECT * FROM Student WHERE Sname='张三' OR Sname='王四';
/*请列出学过001号课程的学生的姓名和学号*/
SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno='001');
SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='001';
/*求没有学过001号课的学生的姓名和学号*/
SELECT Sno,Sname FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='001'); 
/*求既学过001号课又学过002号课的学生的学号*/
SELECT Sno FROM SC WHERE Cno='001' AND Sno IN (SELECT Sno FROM SC WHERE Cno='002');
SELECT S1.Sno FROM SC AS S1,SC AS S2 WHERE S1.Sno=S2.Sno AND S1.Cno='001' AND S2.Cno='002';

7.1 标量子查询------子查询返回结果只有一行,一列

/*例 1：查询大于平均年龄的学生记录 */
SELECT avg(age) from students; 
select * from students where age > 30.1667;
/*用子查询实现 */
select * from students where age > (SELECT avg(age) from students);

查询条件一般用比较运算符。

7.2 列子查询------子查询返回一列多行

/*例 2：查询 30 岁的学生的成绩 */
/*查询30岁学生的studentNO */
select studentNo from students where age = 30; 
SELECT * from scores where studentNo in ('001', '003', '011');
/*用子查询实现 */
SELECT * from scores where studentNo in (select studentNo from students where age = 30);

查询条件一般用in。

7.3 表级子查询------子查询返回结果为多行,多列

/*例 3：用子查询，查询所有女生的信息和成绩 */
/*用内连接实现 */
SELECT * from students INNER JOIN scores ON students.studentNo = scores.studentNo where sex = '女'; 
/*用子查询实现 */
select * from (SELECT * from students where sex = '女') stu INNER JOIN scores sc on stu.studentNo = sc.studentNo;

返回结果是一张表，一般用于和其他表联合查询。

8.结果计算

/*计算有差额的2位教师的薪水差额*/
SELECT T1.Tname,T2.Tname,T1.Salary-T2.Salary AS balance FROM Teacher T1,Teacher T2 WHERE T1.Salary>T2.Salary;
/*依据学生的年龄求学生的出生年月份*/
SELECT Sno,Sname,2020-Sage+1 AS Sborn FROM Student;

9.聚集函数

/*求教师工资的总和*/
SELECT SUM(Salary) FROM Teacher;
/*求计算机系教师工资的总和*/
SELECT SUM(Salary) FROM Teacher,Dept WHERE Teacher.Dno=Dept.Dno AND Dept.Dname='计算机';
/*求数据库课程的平均成绩*/
SELECT AVG(Score) AS 数据库平均分 FROM Course,SC WHERE Course.Cno=SC.Cno AND Course.Cname='数据库';

常用聚集函数：DISTINC对列数去重，COUNT统计总行数、AVG求平均值、SUM求和、MIN最小值、MAX 最大值。

/*count 求select返回的记录数*/
/*查询男同学的总数*/
select count(*) from student where sex='男';
select count(sex) from student where sex='男';
/*count(*)里面的*可以是任何字段，最后结果都是一致的*/
/*加入distinct是对字段去重在统计记录数，与不加distinct返回结果不同*/
select count(distinct sex) from student where sex='男';

AVG的字段如果有NULL，则NULL不参与运算。

/*age: 10 20 null，则结果为15*/
select avg(age) from student;
/*age: 10 20 0，则结果为10*/
select avg(age) from student;

聚集函数不能写在where子句当中，where子句是对每一条记录进行过滤，而聚集函数是对组，可以用在HAVING中。

10.分组查询GROUP BY

按照字段值分组，字段值相同的数据会放到一个组中；分组的目的是为了

最低0.47元/天解锁文章

C++小白、

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录