sql语句操作

SQL语句

数据定义

一个关系数据库管理系统的实例（instance）中可以建立多个数据库，一个数据库可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

一、表TABLE

1. 表定义（TABLE）

CREATE TABLE 表名 (
	列名     类型    完整性约束条件 ，
	列名     类型    完整性约束条件 ，
	列名     类型    完整性约束条件 ，
	列名     类型    完整性约束条件
);

例如：建立student表

CREATE TABLE student(
	SNO CHAR(4) PRIMARY KEY,
	SNAME CHAR(20) UNIQUE,
	SSEX CHAR(2),
	SAGE INT
);

PRIMARY KEY 主码
UNIQUE 取唯一值

SNO	SNAME	SSEX	SAGE

2. 修改基本表（ALTER TABLE）

ALTER TABLE 表名									 /* 添加新列，数据类型，完整性约束条件*/
ADD [COLUMN]  新列名 数据类型 完整性约束条件；

ALTER TABLE 表名									 /* 添表级约束条件*/
ADD  表级完整性约束条件；

ALTER TABLE 表名									 /* 删除列*/
DROP [COLUMN]  列名 [CASECADE/RESTRICT]；

ALTER TABLE 表名									 /* 删除约束条件*/
DROP CONSTRAINT 完整性约束条件 [CASECADE/RESTRICT]；

ALTER TABLE 表名									 /* 修改列名和对应数据类型*/
ALTER COLUMN]  列名 数据类型；

CASCADE 短语，会删除引用该列的其他对象。
RESTRICT 短语，若被其他列引用则拒接删除。

例如：向student表添加学生专业（SDEPT）

ALTER TABLE student ADD SDEPT CHAR(20);

将学生专业添加不可空的约束条件

ALTER TABLE student MODIFY COLUMN SDEPT CHAR(20) NOT NULL;

3. 删除基本表（DROP TABLE）

DROP TABLE 表名 [CASECADE/RESTRICT];

不同数据库标准的具体体现和处理不同，sql server中没有[CASECADE/RESTRICT]，oracle没有[RESTRICT]

二、模式 SCHEMA

1. 模式定义

CREATE SCHEMA  模式名 AUTHORIZATION 用户名;

2. 模式删除

DROP SCHEMA 模式名 <CASCADE|RESTRICT>;

三、视图 VIEW

1. 视图定义

CREATE VIEW 视图名 [(列名 ...)]
AS 子查询
[WITH CHECK OPTION];

例如：建立学生信息的视图

CREATE VIEW is_student
AS 
SELECT sno,sname,sage
FROM student ;

例如：创建反应学生出生年的视图

CREATE VIEW birth_student(sno,sname,sbirth)
AS
SELECT sno,sname,2019-sage
FROM student;

2. 视图查询

和表查询类似

3. 视图更新

和表更新类似，当然不同的数据库管理系统对应的更新规则不同

4. 视图删除

DROP VIEW 视图名 [CASCADE];

四、索引 INDEX

1. 索引建立

CREATE INDEX 索引名
ON 表名（列名，列名...）;

2. 索引名修改

ALTER INDEX 旧的索引名 RENAME TO 新的索引名;

3. 索引删除

DROP INDEX 索引名;

数据查询

一、单表查询

例子1：查询全体学生学号姓名

SELECT sno,sname
FROM student;

例子2：查询全体学生

SELECT *
FROM student;

例子3：查询学生出生年份（查询经过计算的数）

SELECT sname,2019-sage 
FROM student;

例子4：查询学生学习的专业

SELECT DISTINCT sdept
FROM student;

DISTINCT : 去除重复的行，不写默认ALL
例子5： 查询小于20岁的男学生

SELECT *
FROM student
WHERE sage<20 AND ssex='男';

例子6： 查询小于25岁大于20岁的学生的姓名

SELECT sname
FROM student
WHERE sage BETWEEN 20 AND 25;

例子7： 查询属于计算机专业，师范专业，数学专业的学生

SELECT *
FROM student
WHERE sdept IN('计算机专业','师范专业','数学专业');

例子8： 查询**名字里面有‘涵’**的学生

SELECT *
FROM student
WHERE sname LIKE '%涵%';

注：通配符%代表任意长度（长度可以为0）的字符串。
通配符_代表任意单个字符串。
若查询的字符串里面有 _ 或者%，又不想表示通配符，需要用ESCAPE '\'表示“\”为换码字符，用换码字符将通配符转义为普通字符。
e.gSELECT * FROM student WHERE sname LIKE 'LeBron/_James' ESCAPE '\';

例子8：查询学生分数，按降序排列

SELECT grade
FROM studentcourse
ORDER BY grade DESC;

ASC:升序
DESC:降序

聚集函数

COUNT	SUM	AVG	MAX	MIN
统计列中值的个数	列中值的总和	列中值的平均数	列中值的最大值	列中值的最小值

GROUP BY（组） 和 HAVING
GROUP BY查询结果按某一列或多列的值分组，值相等为一组。
HAVING 指定筛选条件
-----WHERE和HAVING短语的区别：where子句作用于基本表或视图，从中选择合适的行。having作用于组，从中选择满足条件的组。

二、连接查询

定义：同时涉及到两个及以上的表的查询，叫连接查询。
最简单的例子：查询学生选课信息(学生表student、选课表sc、学号sno)

SELECT student.*，sc.*
FROM student,sc
WHERE student.sno=sc.sno;

三、嵌套查询

定义：一个查询语句块嵌套在另一个查询语句块的WHERE或HAVING中。层层嵌套正是SQL语句中结构化的体现。
例如SELECT FROM WHERE 属性 IN(SELECT FROM WHERE.......);
注：子查询中不可使用ORDER BY 。
相关子查询：子查询的查询条件依赖于父查询；
不相关子查询：子查询的查询条件不依赖于父查询

子查询修饰

ANY(SOME)或者ALL: ANY表示任意一个；ALL表示全部；
EXISTS: 只返回逻辑真或者逻辑假；

四、集合查询

集合查询主要包括 并操作UNION 、 交操作INTERSECT 和 差操作 EXCEPT。

五、派生表查询

数据修改

一、修改

例如：将01号的年龄改为23；

UPDATE student
SET sage=23
WHRER sno='01';

例如：将所有学生年龄加1；

UPDATE student
SET sage=sage+1;

当然WHERE里面可以嵌套子查询。

二、插入

例如：插入学号01年龄22男，叫小明的学生信息；

INSERT
INTO student
VALUES('01','小明','男','22');

或者这样

INSERT
INTO student(sno,sname,ssex,sage)
VALUES('01','小明','男','22');

三、删除

例如：删除学号01的学生；

DELETE 
FROM student
WHERE sno='01';

注：sql删除，一删除就是一个元组（行），不能删单独的数据。

数据安全控制

一、授权 GRANT

GRANT 权限
ON 对象类型  对象名
TO 用户
[WITH GRANT OPTION];

例1：把查询student表的权限授予用户root

GRANT SELECT
ON TABLE student
TO root;

例2：把对student表全部的权限授予用户root

GRANT ALL PRIVILEGES
ON TABLE student
TO root;

例3：把查询student表的权限授予所有用户

GRANT SELECT
ON TABLE student
TO PUBLIC;

二、收回 REVOKE

ROVOKE 权限 
ON 对象类型 对象名
FROM 用户;

具体操作类似GRANT

三、数据库角色创建

创建角色：

CREATE ROLE 用户名;