数据库SQL基础操作手册 | SLie's Blog|琴弦之轮

常用数据库

MySQL

SQLite

安装：sudo apt-get install sqlite3
检查版本: sqlite3 -version
图形界面: 可以选择sqlitebrowser(qt3)

1 2	sudo apt-get install sqlitebrowser #安装 sqlitebrowser #启动

创建

本文章对基本语句的使用参数将用一些特殊括号包括，实际编写语句时是不需要加上括号的。其中对于括号有如下规定：

<>表示必选
[]表示可选

建库

1	CREATE DATABASE <Database Name>

注：并不是所有 $DBMS$ 都要上述语句进行数据库的建立，针对不同管理系统有着不一样的语法也是可能。

例如 $SQLite3$ 建库的方式是在命令行窗口进行如下操作：

1 2	$ sqlite3 #进入sqlite3管理窗口 .open <Database Name>.db #打开(如果不存在则创建)

示例

1	CREATE DATABASE StuSourceInf;

建表

CREATE TABLE [IF NOT EXISTS] <Table Name>
(
<Column Name 1> <Data Type> [Constraints],
<Column Name 2> <Data Type> [Constraints],
<Column Name 3> <Data Type> [Constraints],
....
)

上述语句表示：建立一个名为Table Name的表。

表中拥有诸多字段(Field)，由对应的字段名（也叫列名Column Name）所表示。

每一个字段下此后可以存放对应的数据，该数据的数据类型由Data Type指定，该数据的约束条件由Constraints指定。

注：对于约束的设置方式，不同系统也稍有差异，这将在后面的约束中详细阐述

常见数据类型

数据类型	描述
`integer(size)` `int(size)` `smallint(size)` `tinyint(size)`	仅容纳整数。在括号内规定数字的最大位数。
`decimal(size,d)` `numeric(size,d)`	容纳带有小数的数字。 `size` 规定数字的最大位数。`d` 规定小数点右侧的最大位数。
`char(size)`	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
`varchar(size)`	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
`date(yyyymmdd)`	容纳日期。

常见约束

约束	描述
`NOT NULL`	强制字段始终包含值。
`UNIQUE`	UNIQUE 约束唯一标识数据库表中的每条记录。
`PRIMARY KEY`	约束唯一标识数据库表中的每条记录，主键必须包含唯一值。每个表都应该有一个主键，并且每个表只能有一个主键。
`FOREIGN KEY`	一个表中的 `FOREIGN KEY` 指向另一个表中的 `PRIMARY KEY`。
`CHECK`	用于限制列中的值的范围。
`DEFAULT`	用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

示例

模型实例

CREATE TABLE StudentInfo(
	SNo CHAR(6) NOT NULL PRIMARY KEY, #设置主键(方式1)
	SName VARCHAR2(20),
    SBirth DATE,
    SDepart VARCHAR2(20)
)
CREATE TABLE CourseInfo(
	CNo CHAR(6) NOT NULL,
	CName VARCHAR2(40),
	Credit NUMBER(3,1),
	CPNo CHAR(6),
    PRIMARY KEY(CNo) #设置主键(方式2)
)
CREATE TABLE StudyCourseRec (
	SNo CHAR(6) NOT NULL,
	CNo CHAR(6) NOT NULL,
    Grade NUMBER(5,1),
    FOREIGN KEY (SNo) REFERENCES StudentInfo(SNo), #设置外键
    FOREIGN KEY (CNo) REFERENCES CourseInfo(CNo),
    PRIMARY KEY (SNo,CNo) #设置复合主键
)

注：该数据库模型将会在后续示例中多次出现

插入

INSERT INTO <Table Name> 
VALUES (<Key 1>, <Key 2>,....)
# or
INSERT INTO <Table Name> 
(<Column Name 1>, <Column Name 2>,...) 
VALUES (<Key 1>, <Key 2>,....)

第一种：必须每一个字段都赋值，第二种可指定字段进行插入

示例：

1 2	INSERT INTO StudentInfo VALUES (19061101,"张三",20001001,"自动化"); INSERT INTO CourseInfo (CNo,CName,Credit) VALUES ("06C066","人工智能",3);

删除

查询

SELECT [ALL][Distinct]
	   <Result Column Expression>[,<Result Column Expression>,...]
FROM <Table Name or View Name>[,<Table Name or View Name>]
[WHERE <condition Expression>]
[GROUP BY <Result Column Expression> [HANVING <condition Expression>]]
[ORDER BY BY <Result Column Expression> [ASC|DESC] ]

单表查询

# 问题1
SELECT * FROM StudentInfo 
WHERE SDepart='自动化' AND SName like '王%'
ORDER BY SName DESC
# 问题2
SELECT C.CNo,C.CName,C.Credit,SC.Grade
FROM CourseInfo C,StudyCourseRec SC
WHERE SC.SNo="006021" AND SC.CNo=C.CNo
ORDER BY C.CNo
# 问题3
SELECT S.SNo,S.SName,S.SSex,S.SDepart,SC.Grade
FROM StudentInfo S,StudyCourseRec SC
WHERE SC.CNo="996A11" AND SC.SNo=S.SNo AND SC.Grade < 60
ORDER BY S.SNo
# 问题4
SELECT count(SC.Grade),avg(SC.Grade),max(SC.Grade),min(SC.Grade)
FROM StudyCourseRec SC,CourseInfo C
WHERE C.CName="现代控制理论" AND SC.CNo = C.CNo
# 问题5
SELECT C.CNo,C.CName,count(SC.Grade),avg(SC.Grade),max(SC.Grade),min(SC.Grade)
FROM StudyCourseRec SC,CourseInfo C,StudentInfo S
WHERE C.CNo = SC.CNo AND S.SNo = SC.SNo AND S.SDepart="自动化"
GROUP BY C.CNo
# 问题6
INSERT INTO CourseInfo (CNo,CName,Credit) VALUES ("216B01","人工智能的伦理问题",2);
# 问题7
UPDATE CourseInfo SET CPNo = "206B04" WHERE CNo = "216B01"
# 问题8
DELETE FROM StudyCourseRec WHERE CNo = "06C032" AND Grade IS NULL

多表查询 & 等值联接

嵌套查询

IN嵌套

EXISTS嵌套

示例：查询所有自动化学院的学生都修读的课程，按课程号顺序，列出课程号、课程名称

本问题其实可以分解为如下问题：

查询这样的课程，不存在学生不修读这个课程

SELECT CNo,CName
FROM CourseInfo C
WHERE NOT EXISTS(
			SELECT * FROM StudentInfo S
			WHERE S.SDepart = "自动化"
			AND NOT EXISTS(
							SELECT * FROM StudyCourseRec SC 
							WHERE SC.CNo = C.CNo
							AND SC.SNo = S.SNo
							)
			)
ORDER BY C.CNo

分组查询

示例：查询所有自动化学院的学生都修读的课程，按课程号顺序，列出课程号、课程名称

与之前提到的其实是同一个问题，他还可以分解为如下问题：

查询这样的课程，该课程有自动化学生修读，并且修读人数等于自动化学生人数

SELECT C.CNo,C.CName
FROM CourseInfo C,StudentInfo S,StudycourseRec SC 
WHERE C.CNo = SC.CNo AND S.SNo = SC.SNo
AND S.SDepart = "自动化"
GROUP BY C.CNo
HAVING count(C.CNo) = (
					SELECT count(*) FROM StudentInfo S
					WHERE S.SDepart = "自动化")
ORDER BY C.CNo

修改

Reference

SQL 基础教程|W3school