SQL语言的基本用法——学习笔记

SQL语言的基本用法

  结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
  sql是一种结构化很强的语言，语法较为很简单，但是功能强大，简单的关键语法搭配与函数的使用就可以实现对于各种需求的操作。
  sql分为： 数据定义语言(DDL)、数据操纵语言(DML)、数据控制语言(DCL)。

                  本文也将从以上三个角度出发

新的开始到结束——创建-修改-删除

sql的存储层级：

• database 数据库
  • tablespace 表空间
  • user 用户
  • schema 模式
    • table 表
    • view 视图
    • index 索引
  • schema 模式
    • ……

一个数据库可以建立多个模式，一个模式下通常包括多个表，视图和索引等数据库对象；

---

创建

# 单行注释
-- 单行注释 （-- 后跟有一个空格）
/* 
多行注释
*/  
create database <数据库名>;#创建数据库
create tablespace <表空间名>/*创建表空间*/ datafile '路径'/*表空间储存路径*/ size 100M /*表空间大小*/ autoextend on next 10M maxsize 1024M; /*自动增长*/
create user 用户名 identified by 密码 default tablespace 默认表空间;#创建用户，密码，储存空间
#grant connect,resource,dba to 用户名; #授权
create schema <模式名> [authorization <用户名>];#创建模式
create table <表名> (<列名> <数据类型> [列级完整性约束条件] [,<列名> <数据类型> [列级完整性约束条件……] [,表级完整性约束条件]);#创建表
create view <试图名> as 查询表结果;#创建视图
create [unique|bitmap|bitmapextent|bitslice] index <索引名>  on [table] 表名(列名);#创建索引

如果完整性约束涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在表级也可以定义在列级。

SQL语言不区分大小写
< > 内容为必写， [ ] 内容为选写（可写可不写）

高级用法：老A.MySQL独立表空间管理.虎书：2020-5-22，https://dbawsp.com/1853.html
推荐一个知乎大佬——姚鑫

修改

alter user 用户名 identified BY 密码 #修改用户密码
#修改基本表
alter table [数据库.][模式.] 表名 rename 新表名 #重命名
alter table 表名 Add [column] <列名> <数据类型> [完整性约束] #增加新列
				Add <表级完整性约束> #增加表级完整性约束
alter table [数据库.][模式.] 表名 column <列名> <数据类型> #修改原有列定义
alter table 表名 drop column 字段名 #删除表中的某一列
alter view 视图名 [(视图中包含的列的名称)] AS 查询表的查询结果 [WITH CHECK OPTION] #修改视图

sqlserver 修改字段数据类型时： alter
 alter table 表名 alter column 字段名 字段类型
mysql 修改字段类型 : modify
 alter table 表名 modify column 字段名 字段类型
oracle 修改字段类型： modify 如果不行参考 mysql 加上 column
 alter table 表名 modify 字段名 字段类型

sqlserver/mysql/oracle 不同数据库的语句略有差异，增加与删除基本一致

删除

drop database 数据库名称 #删除数据库
drop table 表名称 #删除数据表
truncate table 表名称 #清空数据表
alter table 表名 drop column 字段名 #删除表中的某一列
drop index index_name on table_name #删除数据表索引
drop user 用户名 #删除用户名
drop view 视图名 #删除视图名

---

数据操作

---

查询

select [distinct] <[表名.]列名> #distinct不查询重复列，选择（投影）查询列
from <表名> #选择表
[where <过滤条件>]
[group by <列名>  [having 聚集函数]] #分组与分类汇总
[order by <列名> [desc|asc]] #排序：desc降序，asc升序

有group by 不一定有 having，但有having一定有 group by

新增

insert into <表名> (<列名列表>) values (<值列表>) 

删除

delete from <表名>
[where <过滤条件>]

修改

update <表名> set <列名1=新值1> [,列名2=值2,...]
[where <过滤条件> ]

---

函数

在查询与过滤时，常常会有其他的一些操作，比如分组计算，求和等，这时函数的使用就使查询等操作丰富起来

SQL 函数包含了：

• 算术函数
• 字符串函数
• 日期函数
• 转换函数
• 聚集函数

窗口函数

聚集函数是对一组数据进行汇总的函数，输入是一组数据的集合，输出是单个值；其他函数是对单个数值
  count()：计数，count(列名)不计算空值，count()计入空值
  sum()：对一列数据求和，必须为数字
  avg()：对一列数据求均值。值为null时，计算平均值时会忽略带有null值的那一行
  min()：最小值
  max()：最大值
等

用在select 语句后或having语句后

---

连接、嵌套、集合操作

连接包括：等值连接、内连接、左外连接、右外连接

嵌套：from后接子查询

集合操作：将查询到的结果通过交、并、补的方式再次操作

select * from table1
[union|except|intersect] --except（补集）、intersect（交集）和union（并集）
select * from table2

注：查询列要一致，Mysql仅支持UNION

  这部分内容之后会单独总结到文章

---

数据控制

• commit（提交）
  在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。
  在Oracle 数据库中，在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成时才能看见。

#查看事务状态：
select @@autocommit; 
show variables like '%autocommit%'; #  %代替任意长度字符 _代替一位字符
#1或者ON表示自动提交；0或者OFF表示手动提交：需要commit命令提交事务。

• rollback（回滚）
  即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。

rollback;

• grant （授权）

grant  <权限>  on  表名[(列名)]  to  用户  With  grant  option
grant <权限> on <数据对象> from <数据库用户>  -- 另一种写法

数据对象可以是表名或列名
权限表示对表的操作，如select,update,insert,delete

授权命令是由数据库管理员使用的，若给用户分配权限时带With grant option子句，则普通用户获权后,可把自己的权限授予其他用户。

• 回收权限 revoke

revoke <权限> on <数据对象>  from <数据库用户名>

---

存储过程

创建一个存储过程

create procedure prod()
begin 
	select * from items; 
end;

调用存储过程

call prod();

删除存储过程

drop procedure if exists prod;

---

其他操作

show columns from class; #返回当前表的列
show tables;  #显示数据库中的列表
show databases; #显示MySQL中的列表
show create database school; #显示创建数据库的语句
show create table class; #显示创建数据表的语句
show grants; #显示授予用户的安全权限
show errors 或 show warnings; #用来显示服务器错误或警告消息

---

实践一下

安利一些MySQL在线模拟平台

1. http://sample.jimstone.com.cn/xsql/
2. http://sqlfiddle.com/
3. https://sqlbolt.com/lesson/select_queries_introduction
4. https://www.w3resource.com/sql-exercises/sql-retrieve-from-table.php

--MySQL测试语句--
#创建`goods`物料储存的数据库
create database goods
#存储表空间
-- create tablespace goodspace size 100M autoextend on next 10M maxsize 1024M --mysql没有此用法
#创建用户，密码，储存在`good space`表空间，用户名player，密码123456
create user player1 identified by "123456" --default tablespace goodspace;
#授权给player，可以connect、resouce,dba（数据库管理员）权限
-- grant connect,resource,dba to player1 
grant all privileges on connect,resource,dba to "player1"@"%" identified by '123456' with grant option;
#刷新权限
flush privileges;
#player1创建goodschema模式，！MySQL里没有这种写法; 只能在SQLServer里用
-- create schema goodschema authorization player1
#创建items表，字段id为整型、唯一不重复，item_code容量为20个字符的可变字符串 主键，name容量为128个字符的可变字符串,item_brand_code同上,商标编码，note备注，weight浮点，产品重量，isusing是否在用，1位用于判断（1在用，0未启用，-1停用）等
create table items (
id int unique,
item_code varchar(20) primary key,
item_name varchar(128),
brand_code varchar(20),
note varchar(512),
weight double,
isusing char(1) default 1,
CONSTRAINT isusing_check CHECK (isusing=0 or isusing=1 or isusing=-1));
#创建`brand`表
create table brand (
id int unique,
brand_code varchar(50) primary key,
brand_name varchar(128),
note varchar(512));
#创建销售表
create table sale (
id int unique,
sale_code varchar(50) primary key,
sale_time datetime,
item_code varchar(128),
item_brand_code varchar(20),
amount double,
count int);
#创建外键，做关联
alter table items add constraint items_check 
foreign key (brand_code) 
references brand(brand_code)
on update cascade;
#创建索引
create unique index good_index on items(id);#创建索引
#创建在用产品的视图
create view using_productions as (
select items.item_name, brand.brand_name ,items.note ,weight -- 避免字段冲突，用 表名.字段名 确定字段的来源
from items left join brand on items.brand_code=brand.brand_code -- 左连接
where isusing=1);
#查询所有品牌下在"2021-11-11" ——"2021-11-18"时的销量与销售额
select brand.brand_name,sum(amount)
from sale left join brand on sale.item_brand_code=brand.brand_code
where sale_time between "2021-11-11" and "2021-11-18"-- between and 包前不包后,等同于sale_time >="2021-11-11" and <"2021-11-18"
group by item_brand_code having sum(count)
#查询在"2021-11-11" ——"2021-11-18"时的购买情况
select sale_code,sale_time ,items.item_name ,brand.brand_name,amount,count 
from sale left join items on sale.item_code=items.item_code
left join brand on sale.item_brand_code=brand.brand_code
where sale_time between "2021-11-11" and "2021-11-18"

connect resource权限；
grant connect,resource to user;后用户包括的权限:
connect角色： --是授予最终用户的典型权利，最基本的
alter session --修改会话
create cluster --建立聚簇
create database link --建立数据库链接
create sequence --建立序列
create session --建立会话
create synonym --建立同义词
create view --建立视图
resource角色： --是授予开发人员的
create cluster --建立聚簇
create procedure --建立过程
create sequence --建立序列
create table --建表
create trigger --建立触发器
create type --建立类型
从dba_sys_privs里可以查到:sql> select grantee,privilege from dba_sys_privs where grantee=‘resource’ order by privilege;

dba：拥有全部特权，是系统最高权限，只有dba才可以创建数据库结构。
resource:拥有resource权限的用户只可以创建实体，不可以创建数据库结构。
connect：拥有connect权限的用户只可以登录oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于dba管理用户：授予connect，resource, dba权限。
且系统权限只能由dba用户授出：sys, system(最开始只能是这两个用户)。普通用户通过授权可以具有与system相同的用户权限，
但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。

on update cascade; 即在主表更新时，子表（们）产生连锁更新动作的“级联”操作。
除了 cascade外，还有 restrict(禁止主表变更)、set null(子表相应字段设置为空)等操作。

okk总结了一部分SQL知识，欢迎各位大佬批评指正不足之处，欢迎小白们一起讨论学习，任何问题与建议请多多评论哦