SQL(MySQL) 操作详记

SQL语言的特点

SQL是一种通用的、功能强大同时又简单易学的关系数据库语言，集数据查询、数据操纵、数据定义和数据控制四大功能于一体。

主要特点：

• 综合统一
• 高度非过程化
• 面向集合的操作方式
• 用同一种语法结构提供两种使用方式
• 语言简洁，易学易用

SQL语言的基本概念

SQL语言支持关系数据库三级模式结构，外模式对应于视图和部分基本表；模式对应于基本表；内模式对应于存储文件

（1）可以用SQL语句对视图和基本表进行查询等操作。
（2）基本表是本身独立存在的表，是实际存储在数据库中的表。
（3）视图是从基本表或其他视图中导出来的表，它本身不独立存储在数据库中。视图是一个虚表。
（4）存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构是任意的，对用户是透明的。

数据定义语句

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。

基本表的定义

1.建表

create table <表名>(
	<列名><数据类型>[列级完整性约束条件]
	[,  <列名><数据类型>[列级完整性约束条件]] .......
	[,  <表级完整性约束条件>]
);

示例1

例如建立Test表

create table Test(
	prj_num   char(8)   PRIMARY KEY,
	prj_name  varchar(50),
	start_date  datetime,
	end_time  datetime,
	prj_status  bit
);

插入数据：
INSERT INTO test (prj_num, prj_name, start_date, end_time, prj_status) VALUES (‘20122215’, ‘西丽站电缆短路烧毁抢修工程’, ‘2020-11-27 00:00:00’, ‘2020-11-29 00:00:00’, b’1’);

示例2

create table Stock
(
	mat_num char(8) PRIMARY KEY,
   mat_name  varchar(50)  NOT NULL,  /*mat_num不允许为空值*/
   speci varchar(20)  NOT NULL,   /*speci不允许取空值*/
   warehouse char(20),
   amount int,
   unit decimal(18,2),
   total int as (`amount`*`unit`)  /*as为自动计算字段，不能输入值，表示总金额=数量*单价*/
);

示例3

create table Out_stock
(
	prj_num char(8),
   mat_num char(8),
   amount int,
   get_date datetime default now(),  /*get_date属性的默认值为系统时间*/
   department char(20),
   PRIMARY KEY(prj_num, mat_num), /*主键由两个属性组成，必须作为表级完整性约束*/
   FOREIGN KEY(prj_num) REFERENCES test(prj_num), /*表级完整性约束条件，prj_num是外键，被参照表是test*/
  	FOREIGN KEY(mat_num) REFERENCES stock(mat_num) /*表级完整性约束条件，mat_num是外键，被参照表是stock*/
);

2.基本表的修改

ALTER TABLE <表名>
[ADD <新列名><数据类型> | [完整性约束]]
[DROP COLUMN <列名> | <完整性约束>]
[ALTER COLUMN <列名><数据类型>];

示例1
向test表中添加一列

alter table test add prj_director varchar(10);

新添加的字段一律为空值null

示例2
向test表中删除特定列

alter table test drop COLUMN prj_director;

3.基本表的删除

DROP TABLE <表名>

删除out_stock表

drop table out_stock;

查询

SELECT [ALL | DISTINCT]<目标列表达式>[,<目标列表达式>]...  ---需要哪些列
FROM <表名或视图>[, <表名或视图名>]...   ---需要哪些表
[WHERE <条件表达式>]   ---根据什么条件
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]] ;

单表查询

（1）查询指定列

select mat_num, mat_name, speci from stock;

（2）查询全部的列

select * from stock;

（3）查询表获得时间间隔

select prj_name, start_date, end_time, datediff(end_time, start_date) AS DiffDate from test;

（4）消除表中的若干元组

select distinct warehouse from stock;

消除后

（5）查找满足条件的元组
常用的查询条件

stock表：

• 比较大小查询

select mat_num, mat_name, speci, amount from stock where warehouse='1#仓库';

select mat_name, amount, unit from stock where unit<80;

• 确定查询范围

select mat_name, amount, unit from stock where unit between 50 and 100;

或者

select mat_name, amount, unit from stock where unit>=50 and unit<=100;

• 确定集合的查询

select mat_name, speci, amount from stock where warehouse in ('1#仓库','2#仓库');

或者

select mat_name, speci, amount from stock where warehouse='1#仓库' or warehouse='2#仓库';

• 字符匹配的查询

select * from stock where warehouse like '1#仓库';

select * from stock where warehouse like '1#%';

• 涉及空值的查询

select mat_num, mat_name from stock where unit IS NULL;

IS不能用等号代替

• 多重条件查询

select mat_num, warehouse, amount from stock where mat_name='护套绝缘电线' and speci='BVV-120';

（6）对查询结果进行排序

ORDER BY <列名>[ASC | DESC][,...n]

示例1：
将查询结果按降序排列

select mat_name, unit from stock where mat_name='护套绝缘电线' order by unit DESC;

示例2：
查询结果按所在仓库名降序排列，同一仓库的物资按库存量升序排列

select * from stock order by warehouse DESC, amount;

（7）TOP子句用法
TOP n子句：在查询结果中输出前面的n条记录。
TOP n PERCENT 子句：在查询结果中输出前面占结果记录的n%条记录。

示例1：
显示stock表中库存量最大的两条记录

select * from stock order by amount DESC limit 0,2;

（8）聚集函数
常用聚集函数：

示例1：
统计项目数：

SELECT COUNT(*) FROM stock;

示例2：
查询1#仓库，amount最大数量，最小数量，平均值

select MAX(amount), MIN(amount), AVG(amount) from stock where warehouse='1#仓库';

结果：

（9）对查询结果进行分组
GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的为一组。分组的目的是为了细化聚集函数的作用对象。

一般形式：

GROUP BY<分组依据列>[, ...n]
[HAVING<组提取条件>];

示例1：
查询使用两种及两种以上物资的工程号：

select prj_num 项目号, COUNT(*)物资种类 from out_stock group by prj_num;

原表：

查询结果：

（10）COMPUTE BY 子句
T-SQL提供了COMPUTE BY子句，允许在结果集内生成控制中断和小计，得到更详细的或总的记录。它把数据分成较小的组，然后在每组建立详细记录结果数据集，也可为每组产生总的记录。
其语法格式：

[COMPUTE {AVG|COUNT|MAX|MIN|SUM}(表达式)[, ...n]]
[BY 表达式[,...n]]

注：MySQL不支持COMPUTE BY