sql

sql-基础
sql-基础查询-1
sql-基础查询-2
sql-更新

概览

image.png

1. 数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

2 .数据库管理系统（Batabase Management System，DBMS）：用来管理数据库的计算机系统。

3. 关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。

4. 结构化查询语言（Structured Query Language，SQL）：专门用于操作 RDB。

5. 常见的 5 种关系型数据库管理系统（Relational Database Management System，RDBMS）：
   ①Oracle Database：甲骨文公司 ②SQL Server：微软公司 ③DB2：IBM 公司 ④PostgreSQL：开源⑤MySQL：开源

SQL语句种类：

（1）DDL（Data Definition Language，数据定义语言）：创建、删除或修改数据库以及数据库中的表等对象。
①CREATE：创建数据库和表等对象
②DROP：删除数据库和表等对象
③ALTER：修改数据库和表等对象

（2）DML（Data Manipulation Language，数据操作语言）：查询或修改表中的记录。DML 使用最频繁。
①SELECT：查询表中的数据
②INSERT：向表中插入数据
③UPDATE：修改表中的数据
④DELETE：删除表中的数据

（3）DCL（Data Control Language，数据控制语言）：确认或取消对数据库中的数据变更的执行操作，以及对用户的操作数据库中的对象权限进行设定。
①COMMIT：确认对数据库中的数据进行的变更
②ROLLBACK：取消对数据库中的数据进行的变更
③GRANT：赋予用户的操作权限
④REVOKE：取消用户的操作权限

SQL 语句书写规范：

①以分号结尾；
②大小写不敏感，不区分关键字的大小写，建议关键字大写；插入到表中的数据是区分大小写的，如“HI”、“Hi”和“hi”都不同。
③建议“关键字大写，表名小写和列名的首字母大写，多个单词用下划线分隔”的格式。
④单词使用半角空格或换行符隔开
⑤字符串、日期用单引号括起来（'），如'Hello'，'2016-09-24'；数字直接书写，不用加单引号，如5。

基本注意项

1. 命名规则：

①只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称；
②规范要求命名以半角英文字符开头；
③名称不能重复。

2. 数据类型的指定：声明该列的数据类型，是一种约束。

3. 数据类型介绍：
   ①INTEGER：整型，意味不能存储小数；
   ②CHAR：字符串型，如 CHAR（10）和 CHAR（100）中的括号表名该列可以存储的字符串的最大长度。它是“定长字符串”，如 CHAR(8) 表示在列中插入 'abc' 时会自动保存成 'abc '（后面5个半角空格）的形式；
   ③VARCHAR：字符串型，类似 CHAR，不同的是它是 “可变长字符串”，如 VARCHAR(8) 在插入 'abc'，保存的就是字符串 'abc'；
   ④DATE：日期类型；

4. 约束：
   ①非空约束：NULL 是空白（无记录）的意思的关键字，NOT NULL 表示必须输入的约束。
   ②主键约束：主键是可以确定一行数据的列，一般通过它取特定行的数据，它是唯一的，不允许重复。
   NOT NULL
   UNIQUE，每个表可以有多个 UNIQUE 约束，但是每个表至多有一个 PRIMARY KEY 约束。
   PRIMARY KEY
   FOREIGN KEY
   CHECK
   DEFAULT

数据库操作

• show databases;展示所有数据库
• create database dbName新建数据库
• drop database dbName删除数据库
• use dbName切换数据库

表操作

• 建表

-- 语法：
-- CREATE TABLE <表名>
-- (
--        <列名1> <数据类型> <约束>,
--        <列名1> <数据类型> <约束>,
--        ...
--        <表约束1>,
--        <表约束2>,
--        ...
-- )
-- 创建名为 Shohin 的表
CREATE TABLE Shohin
(
    shohin_id     CHAR(4)      NOT NULL,
    shohin_mei    VARCHAR(100) NOT NULL,
    shohin_bunrui VARCHAR(32)  NOT NULL,
    hanbai_tanka  INTEGER ,
    shiire_tanka  INTEGER ,
    torokubi      DATE ,
    PRIMARY KEY (shohin_id)
 );

• 删除表

-- 语法：DROP TABLE <表名>
-- 删除名为 Shohin 的表，被删的表，无法恢复。
DROP TABLE Shohin; 

• 修改表

-- 语法：ALTER TABLE <表名> ADD <列名> <类型>;
-- 在 Shohin 中添加名为 Shohin_mei_Kana 类型为 VARCHAR(100) 的列
ALTER TABLE Shohin ADD Shohin_mei_Kana VARCHAR(100);
-- 语法：ALTER TABLE <表名> DROP COLUMN <列名>;
-- 删除 Shohin 表中名为 Shohin_mei_Kana 的列
ALTER TABLE Shohin DROP COLUMN Shohin_mei_Kana; 

• 展示所有表
  show tables;
• 展示表结构
  describe tableName;

基础查询

image.png

• like（%表示0或多个任意字符，_表示单个任意字符）和in模式

• AND 运算优先于 OR 运算，想要优先执行 OR 运算时可以使用括号

  
  image.png
  

  where：对行进行条件筛选
  group by：按组聚合（聚合键或聚合列）
  having：对聚合进行条件筛选
  order by：排序，默认升序

• 【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING
• 【执行顺序】FROM --> WHERE --> GROUP BY --> SELECT-->ORDER BY
• 回顾之前说的执行顺序，SELECT 子句是在 GROUP BY 子句之后执行。所以执行到 GROUP BY 子句时无法识别别名
• 只有 SELECT 子句和 HAVING 子句以及ORDER BY子句中能够使用聚合函数。
• 除了 COUNT 函数，其它函数不能将星号作为参数。COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT(<列名>) 会得到 NULL 之外的数据行数。select count(1);
• WHERE 子句的执行速度比 HAVING 快。WHERE 子句指定行所对应的条件，HAVING 子句指定组所对应的条件

高级查询

• union
  UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名，默认会去重，不去重请使用union all

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

• join
  有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join

//不使用join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 
//使用join，效果一样
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

JOIN：同inner join，在两个表中都有匹配才返回该行，否则不返回该行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

更新

image.png

• select columnName...into new_table [in anther_db] from old_table
• 如果要删除表全部数据时请使用 TRUNCATE <表名>，它比 DELETE 子句更高效。
• 格式
  select columnName1 columnName2... from tableName where...
insert into tableName (columnName...) values(value...)
delete from tableName where...
update tableName set columnName1=value1,columnName2=value2 where...
• 多个字段更新，逗号隔开

--示例
UPDATE dbo.Shohin
    SET hanbai_tanka = hanbai_tanka * 10,　　　　--逗号分割
        shiire_tanka = shiire_tanka / 2
WHERE shohin_bunrui = '厨房用具'

一些SQL语句

select top 100 * from tb_name ，选择前100行数据

select * from tb_name where c1 like '%U%' ，查询c1字段包含U的行，通配符--%替代0个或多个字符，_替代一个字符

select * from tb_name where c1 in ('hello'，‘hi’) ，查询c1字段是hello或者是hi的行

select * from tb_name where c1 between 1 and 20 ，查询c1字段在1和20之间的列（not between不在此范围内）

select c1 as newname from tb_name,创建别名，一般是为了让列的可读性更强

select * from tb_name as newname ，表的创建别名法。在下面的情况下，使用别名很有用：在查询中涉及超过一个表，在查询中使用了函数，列名称很长或者可读性差，需要把两个列或者多个列结合在一起

快速理解

from指定了数据来源，可能是一张表，可能是多张表join后的结果，是一个行集
where对这些行集进行条件筛选，得到的让然是行集
group by根据聚合键对行集进行分组，得到组集
having对组集进行条件筛选
select选择哪些列，或聚合函数
order by进行排序

索引

可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

CREATE INDEX index_name
ON table_name (column_name)

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

存储过程

存储过程
mysql存储过程
Transact-SQL中的存储过程，非常类似Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。SQL语句执行需要编译，优化，存储过程store procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。