基本数据库

2023-02-20

字数统计: 3.9k | 阅读时长≈ 17 分钟

SQL语言的查询格式

SELECT /子句用于来制定查询的字段/

SELECT [ALL|DISTINCT]  <**目标列**表达式> [别名] [,<目标列表达式> [别名]]...

FROM  <**表名**或视图名> [别名] [,<表名或视图名> [别名]]...

[WHERE <条件表达式>]

[[GROUP BY <列名> [HAVING <条件表达式>]]

[[ORDER BY <列名> [ASC|DESC]];

目标列表达式可选的格式

“* ”/通配符，表示查询全部/
<表名>.* /*后可以跟具体列名/
CONUT （[ALL|DISTINCT] * ）
<属性列名表达式> /可以是聚集函数，常量的任意算数计算（加减乘除）/

函数	说明
COUNT(*)	统计元组个数
COUNT([ALL\|DISTINCT] <列名>)	统计一列中值的个数
AVG([ALL\|DISTINCT] <列名>)	计算一列值的平均值
MAX([ALL\|DISTINCT] <列名>)	求一列值的最大值
MIN() ([ALL\|DISTINCT] <列名>)	求一列值的最小值
SUM([ALL\|DISTINCT] <列名>)	求一列值的总和

WHERE子句的条件表达式可选格式

类别	运算符	说明
比较运算符	=、>、<、>=、<=、<>	比较两个表达式
逻辑运算符	AND、OR、NOT	组合两个表达式的运算结果或取反
范围运算符	[NOT] BETWEEN AND	搜索值是否在范围内
列表运算符	IN、NOT IN	查询值是否属于列表值之一
字符匹配符	LIKE、NOT LIKE	字符串是否匹配
未知值	IS NULL、IS NOT NULL	查询值是否为NULL

union用法

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

mysql> select * from J;
+-----+----------+------+
| JNO | JNAME    | CITY |
+-----+----------+------+
| J1  | 三建     | 北京 |
| J2  | 一汽     | 长春 |
| J3  | 弹簧厂   | 天津 |
| J4  | 造船厂   | 天津 |
| J5  | 机车厂   | 唐山 |
| J6  | 无线电厂 | 常州 |
| J7  | 半导体厂 | 南京 |
+-----+----------+------+
7 rows in set (0.00 sec)

mysql> select * from spj;
+-----+-----+-----+------+
| SNO | PNO | JNO | QTY  |
+-----+-----+-----+------+
| S1  | P1  | J1  |  200 |
| S1  | P1  | J3  |  100 |
| S1  | P1  | J4  |  700 |
| S1  | P2  | J2  |  100 |
| S3  | P1  | J1  |  200 |
| S3  | P3  | J1  |  200 |
| S3  | P4  | J6  |  200 |
| S3  | P6  | J4  |  500 |
| S4  | P5  | J1  |  100 |
| S4  | P6  | J3  |  300 |
| S4  | P6  | J4  |  200 |
| S5  | P2  | J4  |  100 |
| S5  | P3  | J1  |  200 |
| S5  | P6  | J2  |  200 |
+-----+-----+-----+------+
14 rows in set (0.00 sec)

mysql> select JNO from spj
    -> union
    -> select JNO from J;
+-----+
| JNO |
+-----+
| J1  |
| J3  |
| J4  |
| J2  |
| J6  |
| J5  |
| J7  |
+-----+
7 rows in set (0.00 sec)

例题

&nbsp设有一个SPJ数据库，包括S、P、J及SPJ4个关系模式：

S（SNO，SNAME，STATUS，CITY）；

P（PNO，PNAME，COLOR，WEIGHT）；

J（JNO，JNAME，CITY）；SPJ（SNO，PNO，JNO，QTY）。

&nbsp供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。

工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

创建数据库

（1）建立S表：S（SNO，SNAME，STATUS，CITY）；
mysql> create table S
    -> (SNO char(2) unique,
    -> SNAMA char(6),
    -> STATUS char(2),
    -> CITY char(4));
S表插入数据：
INSERT INTO S VALUES
('S1','精益','20','天津'),
('S2','盛锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');
（2）.建立P表
mysql> create table P(
    -> PNO char(2) UNIQUE,
    -> PNAME char(8),
    -> COLOR char(2),
    -> WEIGHT int);
P表插入数据
INSERT INTO P VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

（3）.建立J表
CREATE table J(
JNO char(2) UNIQUE,
JNAME char(8),
CITY char(8));
J表查入数据
INSERT INTO J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');

（4）.创建SPJ表	//*并创建外键约束*/
CREATE TABLE SPJ(
SNO char(2),
PNO char(2),
JNO char(2),
QTY int,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
);

SPJ表插入数据
INSERT INTO SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);

4.1供应工程J1零件的供应商号码SNO对应的SQL语句为
mysql> select SNO
    -> from spj
    -> where JNO='J1';
+-----+
| SNO |
+-----+
| S1  |
| S2  |
| S2  |
| S3  |
| S3  |
| S4  |
| S5  |
+-----+
4.2供应工程J1零件的供应商号码SNO对应的SQL语句为：
mysql> select SNO
    -> from SPJ
    -> where JNO='J1' and PNO='P1';
+-----+
| SNO |
+-----+
| S1  |
| S3  |
+-----+
4.3.供应工程J1零件为红色的供应商号码SNO对应的SQL语句为：
解法发一
mysql> select SNO
    -> from SPJ
    -> where PNO in(select PNO from P where COLOR='红') and JNO='J1';
+-----+
| SNO |
+-----+
| S1  |
| S3  |
+-----+
解法二：
mysql> select SNO from P,SPJ
    -> where P.PNO=SPJ.PNO and color='红' and JNO='J1';
+-----+
| SNO |
+-----+
| S1  |
| S3  |
+-----+
4.4.没有使用天津供应商生产的红色零件的工程号JNO对应的SQL语句为：
法一：连表
mysql> select DISTINCT JNO from SPJ,S,P
    -> where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and CITY!='天津' and COLOR!='红';
+-----+
| JNO |
+-----+
| J1  |
| J2  |
| J4  |
| J5  |
+-----+
法二：嵌套
mysql> select DISTINCT JNO from SPJ
    -> where (SNO not in (select SNO from S where CITY='天津')) and (PNO not in (select PNO from P where COLOR='红'));
+-----+
| JNO |
+-----+
| J4  |
| J1  |
| J2  |
| J5  |
+-----+
4.5.求至少用了供应商S1所供应的全部零件的工程号JNO
解：
mysql> select JNO from SPJ
    -> where PNO='P1' and jno in (select JNO from spj where pno='P2');
mysql> select JNO from SPJ
    -> where PNO='P1' and jno in (select JNO from spj where pno='P2');
+-----+
| JNO |
+-----+
| J4  |
+-----+

5.针对习题4中的4个表试用SQL完成以下各项操作

（1）找出所有供应商的姓名和所在城市：
mysql> select SNAME,CITY from S;
+--------+------+
| SNAME  | CITY |
+--------+------+
| 精益   | 天津 |
| 盛锡   | 北京 |
| 东方红 | 北京 |
| 丰泰盛 | 天津 |
| 为民   | 上海 |
+--------+------+
（2）找出所有零件的名称、颜色、重量；
mysql> SELECT PNAME,COLOR,WEIGHT FROM P;
+--------+-------+--------+
| PNAME  | COLOR | WEIGHT |
+--------+-------+--------+
| 螺母   | 红    |     12 |
| 螺栓   | 绿    |     17 |
| 螺丝刀 | 蓝    |     14 |
| 螺丝刀 | 红    |     14 |
| 凸轮   | 蓝    |     40 |
| 齿轮   | 红    |     30 |
+--------+-------+--------+
（3）找出使用供应商S1所供应零件的工程号码；
mysql> select JNO from SPJ
    -> where SNO='S1';
+-----+
| JNO |
+-----+
| J1  |
| J3  |
| J4  |
| J2  |
+-----+
（4）找出工程项目J2使用的各种零件的名称及其数量；

mysql> select P.PNAME,SPJ.QTY from SPJ,P
    -> where P.PNO=SPJ.PNO and JNO='J2';
+--------+------+
| PNAME  | QTY  |
+--------+------+
| 螺栓   |  100 |
| 螺丝刀 |  200 |
| 凸轮   |  100 |
| 齿轮   |  200 |
+--------+------+
（5）找出上海厂商供应的所有零件号码；
mysql> select PNO from SPJ
    -> where SNO=( select SNO from S where CITY='上海');
+-----+
| PNO |
+-----+
| P2  |
| P3  |
| P6  |
| P6  |
+-----+
（6）找出使用上海产的零件的工程名称；
mysql> select JNAME from J
    -> where JNO in (select JNO from SPJ where SNO=( select SNO from S where CITY='上海'));
+--------+
| JNAME  |
+--------+
| 三建   |
| 一汽   |
| 造船厂 |
+--------+
（7）找出没有使用天津产的零件的工程号码；
mysql> select DISTINCT JNO from SPJ
    -> where SNO in (select SNO from S where CITY!='天津');
+-----+
| JNO |
+-----+
| J1  |
| J2  |
| J4  |
| J5  |
+-----+
（8）把全部红色零件的颜色改成蓝色；
mysql> update P
    -> set COLOR='蓝'
    -> where COLOR='红';
mysql> select * from P;
+-----+--------+-------+--------+
| PNO | PNAME  | COLOR | WEIGHT |
+-----+--------+-------+--------+
| P1  | 螺母   | 蓝    |     12 |
| P2  | 螺栓   | 绿    |     17 |
| P3  | 螺丝刀 | 蓝    |     14 |
| P4  | 螺丝刀 | 蓝    |     14 |
| P5  | 凸轮   | 蓝    |     40 |
| P6  | 齿轮   | 蓝    |     30 |
+-----+--------+-------+--------+
（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改；
mysql> update SPJ
    -> set SNO='S3'
    -> where SNO='S5' and JNO='J4' and PNO='P6';
（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；
因创建SPJ表时添加了外键约束（SNO），参照S表中的SNO字段，所以删除时应该先取消外键约束或者先删除SPJ表中的字段
mysql> DELETE FROM SPJ
    -> where SNO='S2';	/*先删除SNO表中的数据*/
mysql> DELETE FROM S
    -> where SNO='s2';
（11）请将（S2，J6，P4，200）插入供应情况关系。
mysql> insert into SPJ(SNO,JNO,PNO,QTY) values
    -> ('S3','J6','P4',200);
mysql> select * from SPJ
    -> where SNO='S3' and JNO='J6' and PNO='P4';
+-----+-----+-----+------+
| SNO | PNO | JNO | QTY  |
+-----+-----+-----+------+
| S3  | P4  | J6  |  200 |
+-----+-----+-----+------+

6.什么是基本表？什么是视图？两者的区别和联系是什么？
（1）基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一个(或多个)基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。
（2）视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。
（3）基本表与视图的区别和联系：
①区别：视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。
②联系：视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。所以基本表中的数据发生变化，从视图中查询出的数据也就随之发生改变。

7.视图有以下五个优点：
1）视图能够简化用户的操作：视图机制使用户可以将注意力集中在所关心的数据上，如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作；
（2）视图使用户能以多种角度看待同一数据：视图机制能使不同的用户以不同的方式看待同一数据，当许多不同种类的用户共享一个数据库时，这种灵活性是非常重要的；
（3）视图对重构数据库提供了一定程度的逻辑独立性：数据的逻辑独立性是指当数据库重构时，用户的应用程序不会受影响；
（4）视图能够对机密数据提供安全保护：有了视图机制，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。
（5）使用视图可以更清晰的表达查询

9.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：

（1）找出三建工程项目使用的各种零件代码及其数量；
为三建创建视图
mysql> create view V_SPJ AS
    -> select SNO,PNO,QTY
    -> from SPJ
    -> where JNO=
    -> ( select JNO from j where Jname='三建');
mysql> select PNO,QTY
    -> from V_SPJ;
+-----+------+
| PNO | QTY  |
+-----+------+
| P1  |  200 |
| P1  |  200 |
| P3  |  200 |
| P5  |  100 |
| P3  |  200 |
+-----+------+
（2）找出供应商S1的供应情况。
mysql> select * from V_SPJ
    -> where SNO='S1';
+-----+-----+------+
| SNO | PNO | QTY  |
+-----+-----+------+
| S1  | P1  |  200 |
+-----+-----+------+

查看用户

select user,host from mysql.user;
查看单个用户所有情况：
SELECT * FROM mysql.user WHERE user='root'\G
查看当前登录用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

mysql权限介绍

全局——可以管理整个MySQL
库——可以管理指定的数据库
表——可以管理指定数据库的指定表
字段——可以管理指定数据库的指定表的指定字段

权限存储在mysql库的user, db, tables_priv, columns_priv, procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

查看单个用户情况

SELECT * FROM mysql.user WHERE user='root'\G
说明：
在MySQL的sql语句后加上\g，效果等同于加上定界符，一般默认的定界符是分号;
在MySQL的sql语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例如
CREATE USER 'pig'@'192.168.1.101_' IDENTIFIED BY '123456';

关系数据库系统中的存取权限

对象类型	对象	操作类型
数据库模式	模式	CREATE SCHEMA
	基本表	CREATE TABLE, ALTER TABLE
	视图	CREATE VIEW
	索引	CREATE INDEX
	基本表和视图	SELECT, INSERT,UPDATE,DELETE,REFERENCES,ALL
数据	属性列	SELECT,INSERT,UPDATE,REFERENCES, ALL PRIVILEGES

授权语句

GRANT语句的一般格式为
GRANT<权限>[，权限]…
ON<对象类型><对象名>[<对象类型><对象名>]…
TO<用户>[用户>]…
[WITH GRANT OPTION];
如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。如果没有指定 WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。

例如
把对Student查询的权限授予U1
mysql> grant select
    -> on table Student
    -> to U1;

撤销用户权限

REVOKE<权限>[<权限>]…
ON<对象类型><对象名>[,对象类型><对象名>小…
FROM<用户>[,用户>]…
[CASCADE|RESTRICT];
级联（CASCADE)

版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！