小宋的笔记-数据库篇

小棉羊羊
149 阅读5分钟

本文已参与[新人创作礼]活动，一起开启掘金创作之路。 @TOC

一、SQL是什么？

  • SQL 指结构化查询语言，全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
  • SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

SQL语句就是操作数据库的语言，虽然数据库有很多都不一样。但是SQL为了与ANSI标准相兼容，它们的一些主要命令还是很相似的。（比如，select，update，delete，insert，where等等）

二、基本使用语法

写这个就是怕我以后忘了来看看

1. 查询数据（select）

select * from `表名` where `条件` order by 分组的字段名

在查询语句前面加上explain可以查看sql语句的信息，可以看看索引是否有使用。 这里*号改写成字段名的话，查询速度会变快。小优化

group by：给字段分组

2. 插入数据（insert）

插入数据

insert into `表名` (字段,字段,字段) values (value1,value2,values3)

如果表的字段东要插入的话，表名后面的括号可以省略。

3. 修改数据（update）

修改数据库的数据

update `表名` set 字段名 = 新的信息 where 条件

4. 删除（delete）

删除数据库的表

delete from `表名` where 条件

普通语法

高级查询：

1. 规定要返回的行数,返回前五行的数据

在数据量上千条的表里，还是非常有用的。

SQL Server：select top 5|percent 字段名 from `表名` 
MySQL：select ＊ from `表名` where limit 5　 
Oracle：select * from  `表名` where rownum <= 5

2. 模糊查询(like)

查询符合匹配要求的列

select * from `表名` where name like '李%';  -- 以李开头的数据

'%李'：以李结尾的数据 '%三%'：中间带三的数据 查询不符合匹配要求的列

select * from `表名` where not like '李%';  --查询不是以李开头的数据

3. 通配符

通配符用于代替字符串中的任何其他字符。

通配符描述
%代替0个或多个字符
_代替一个字符
[charlist]字符列中的如何单一字符
[^charlist]不在字符列中的如何单一字符
_: select * from `表名` where name like '_三';    --李四符合要求，因为前面一个李字
[charlist]: select * from `表名` where name regexp '^[GFs]';  --以Gfs开头的name
[^charlist]: select * from `表名` where name regexp '^[^A-H]';  --name 不以 A 到 H 字母开头的数据
select * from `表名` where name regexp '[e]$';   --以e结尾的name

4. in操作符

in操作符可以在where子句中规定多个值 条件值比较多的时候用起来很方便

select * from `表名` where name in ('张三','李四',...);   --查询叫张三或李四的数据
select * from `表名` where name not in ('张三','李四',...); --查询不叫张三或李四的数据

5. between在什么之间

选取两个值之间的数据范围内的值这些值可以是数值，文本或日期

select * from `表名` where id between 5 and 10    --如果是10and5，这样的话不会报错但是查询不到数据

6. 别名

  • 在查询中涉及超过一个表
  • 在查询中使用了函数
  • 列名称很长或者可读性差
  • 需要把两个列或者多个列结合在一起
select column_name AS alias_name from table_name AS alias_name

7. 连接（JOIN）

  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL OUTER JOIN：只要其中一个表中存在匹配，则返回行
  1. inner join内连接 on后面的条件通过时，才能返回数据
select * 
from table1
inner join table2
on table1.id=table2.aid
  1. left join左连接 left join和inner join有一点不一样，条件没有匹配时，返回左边表的数据
select * 
from table1
left join table2
on table1.id=table2.aid
  1. right join右连接 right join和left join的区别是，左边表换成了右边表
select * 
from table1
right join table2
on table1.id=table2.aid
  1. full outer join外连接 full outer join只要左表和右表其中一个表存在匹配，就返回数据 相当于是left join和right join的结合版
select * 
from table1
full outer join table2     --MySQl不支持这个关键字 可以用union来进行外连接查询
on table1.id=table2.aid

在使用join时，on和where条件的区别： 5. on条件实在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的数据。 6. where条件是在临时表生成好后，再对临时表进行过滤的条件。

8.union操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

select country from `表名1`
union 
select country from `表名2`
order by country;

从table1和table2中选取不同的country。（只有不同的值）

select country from `表名1`
union all
select country from `表名2`
order by country;

从table1和table2中选取不同的country。（也有重复的值）

9. SELECT INTO一个表复制信息到另一个表

mysql不支持select into语句，但支持 insert into ... select SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称。

INSERT INTO SELECT INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

INSERT INTO table2
SELECT * FROM table1;

select into from 和 insert into select 都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

10. 创建数据库，创建表，约束

创建数据库

CREATE DATABASE dbname;   --创建一个dbname的数据库

创建表 AUTO INCREMENT自动增长属性

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)              --可以使用insert into向表里面插入数据
);

SQL约束（Constraints）

  • SQL 约束用于规定表中的数据规则。
  • 如果存在违反约束的数据行为，行为会被约束终止。
  • 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
关键字描述
NOT NULL指示某列不能存储 NULL 值
UNIQUE保证某列的每行必须有唯一的值
PRIMARY KEYNOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识
FOREIGN KEY保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK保证列中的值符合指定的条件
DEFAULT规定没有给列赋值时的默认值

***foreign key：**一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

**check：**约束

  • CHECK 约束用于限制列中的值的范围。
  • 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
  • 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

如需命名 CHECK 约束，并定义多个列的 CHECK 约束。 P_id必须大于0，City必须等于Sandnes

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

如果表已经创建了，可以使用这个语句

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

如果想删除这个约束，可以用下面的命令

ALTER TABLE Persons     --MySQL
DROP CHECK chk_Person

ALTER TABLE Persons    --SQL Server / Oracle / MS Access
DROP CONSTRAINT chk_Person

default:约束 如果这个字段在插入时没有数据，default可以给他一个默认值。

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes',   
    OrderDate date DEFAULT GETDATE()
)

如果表已经创建了，添加默认值约束

--mysql: 
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
--SQL Server / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City
--Oracle：
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

如果想删除这个约束

--MySQL
ALTER TABLE Persons
ALTER City DROP DEFAULT
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

索引

  • 优点：用户无法看到索引，在数据库查询数据的时候，效率会变高。
  • 缺点：在更新有索引的表比更新没有索引的表，花费更多时间。因为索引也会需要更新。所以个人建议只需要在被搜索的列或者表上面创建索引就可以了。

create index（创建普通索引）

在表上面创建一个简单的索引 mysql里是Normal普通索引

create index index_name
on table_name (column_name)

varchar的话，这里规定一下索引的长度，可以提高增删改的效率。 计算索引长度的语法 select count(distinct left(nickname, 5))/count(*) from userinfo

create index index_name
on table_name (column_name(20))

create unique index（创建唯一索引）

在表上面创建一个唯一索引，不允许重复的值。 mysql里是唯一索引,可以在身份证或者电话号码上面用到

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

创建索引的语法在不同的数据库中不一样，因此，检查各个数据库创建索引的命令。

FULLTEXT （全文索引）

全文索引只能使用 InnoDB或 MyISAM表格，并且只能用于创建CHAR， VARCHAR或 TEXT列。 适用于一大段文字查找。

ALTER TABLE `表名`
ADD FULLTEXT INDEX `索引名`(`字段名`) USING BTREE;

SPATIAL（空间索引）

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

ALTER TABLE `表名`
ADD SPATIAL INDEX `索引名`(`字段名`) USING BTREE;

B-tree索引方法和hash索引方法的区别

  • B-tree索引方法（B树（可以是多叉树））{主流使用}
  • hash（key，value）这种方式对范围查询支持得不是很好

Btree：是mysql最常用的索引算法，可以用于like操作符。只要它的查询条件是一个不以通配符开头的常量，例如：

select * from user where name like ‘jack%’;
select * from user where name like ‘jac%k%’;
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如：
select * from user where name like ‘%jack’;
select * from user where name like simply_name;

hash：

  • Hash索引只支持"=","IN"和"<=>"查询，不能使用范围查询。
  • hash索引遇到大量hash值相等的情况后性能并不一定会比Btree高
hashBtree
基本上能一次定位到数据在节点上挨个查找
是等值计算不支持like等范围查找支持范围查找
不支持索引排序查询支持索引排序查询
  1. hash查找数据基本上能一次定位数据，碰到很多hash值相等的情况下性能胡下降。btree索引在节点上挨个查找，所以在精确查找方面hash索引比较btree效率高。
  2. 如果不需要精确查找的话，因为hash是等值计算，所以对于”like“等范围查找是无效的。
  3. hash不支持索引排序，索引值和计算出来的hash值大小并不一定一致。

撤销索引，撤销表以及撤销数据库。

撤销索引：

-- 用于 MS Access 的 DROP INDEX 语法：
DROP INDEX index_name ON table_name
-- 用于 MS SQL Server 的 DROP INDEX 语法：
DROP INDEX table_name.index_name
-- 用于 DB2/Oracle 的 DROP INDEX 语法：
DROP INDEX index_name
-- 用于 MySQL 的 DROP INDEX 语法：
ALTER TABLE table_name DROP INDEX index_name

撤销表

-- DROP TABLE 语句用于删除表。
DROP TABLE table_name

撤销数据库

-- DROP DATABASE 语句用于删除数据库。
DROP DATABASE database_name

删除表内数据，不删除表本身

TRUNCATE TABLE table_name

向已有的表中添加，删除或修改列

在表中添加列

alter table `表名`
add column `列名` varchar(255)

修改列的属性

alter table `表名`
modify column `列名` char(20)

删除列

alter table `表名`
drop column `列名`
avatar
文章
阅读
粉丝