sql使用手册

todo:sql 食用手册

------> From Albert

注意：不同数据库，关键字和语句会不一样，若不能运行，可单独百度‘xxx数据库 xxx关键字’

todo:

博客编辑版本：

20201122----->第一次编辑创作并发布

20201212----->第二次编辑 [新增数据库简要介绍，以及使用工具，和sql函数]

…

本博客大部分皆为博主亲测，有问题快来骚扰博主吧!他有点闲！！！

‘’’

-----------------------------------<=简介=>------------------------------------------

–什么是数据库：

概括：能联网的Excel，可用于数据共享、修改。

看见银行里的钱了吗，你的账户信息，钱财信息等等就是使用数据库存储起来的呢

（还有很多例子，可自信谷歌百度检索，亲亲）

–数据库主要分为两种：关系型数据库 和 非关系型数据库

1.关系型数据库：主流，常用（我们一般都是学习使用这个），处理结构化的数据库和表；

2.非关系型数据库：不常用，目前一般用来处理大数据（收集到的数据源，含有垃圾信息，并未做数据清洗）

–数据库引擎产品：

1.关系型数据库：Oracle 、SqlServer 、 mysql 、 sqlite 等等

            （博主目前用到这几个较多，其中Oracle综合性能最好，但也是价格最昂贵的QAQ

            淘宝网目前也是用的mysql（社区版开源，被Oracle的甲骨文公司收购了，因为开源免费，所以使用流行。

            PS：听说淘宝已经在研发自己的数据库了

            PPS: 听说国产的操作系统和CPU和数据库都在搞）

2.非关系型数据库：Redis（学后端的必学，数据库缓存） 等等

–本博客操作的数据库是sqlite，轻量级，本地部署即可（鼠标右键新建文本文件，修改文件后缀为 .db 即可）

–连接数据库并使用sql语句练习的途径有：

一.原始大法（进阶使用）：cmd连接。

    1.下载好数据库客户端

    2.将客户端配置在环境变量

    3.输入端口、ip、账号、密码 连接数据库

    4.操作数据库（注：博主在终端操作mysql的时候，写sql语句，都要皆为加上 ；

二.数据库软件（主流、推荐）：直接连接数据库和操作数据库

    1.打开数据库软件

    2.点击 连接 按钮输入 ip、端口、账号、密码 然后选择连接

三.代码连接（进阶使用）：用更原始的代码数据库引擎，调用数据库的api进行连接

    博主使用的是第三种，

    使用的python里的一个内置库，是一个sqlite3数据库引擎

    IDE使用的pycharm

四.maybe还有很多其他的稀奇古怪的连接方法，谁知道呢

无论使用的哪种方法，sql语句都是一样的，本博客会详细全面介绍sql语句以及数据库原理

‘’’

import time

import sqlite3

db_path = r’C:\Users\86131\Desktop\ndhd_cw_001.db’

conn = sqlite3.connect(db_path)

c = conn.cursor()

1.select：查询

(1)查询某列

c.execute(‘select 字段 from 表’)

(2)查询全部

c.execute(‘select * from 表’)

2.distinct：去重

(1)某列去重

c.execute(‘select distinct 字段 form 表’)

(2)多列去重（合并多列完全相同的）

c.execute(‘select dintinct 字段01 字段02 … from 表’)

(3)全去重（合并整行完全相同的）

c.execute('select distinct * from 表 ')

3.where：条件

select 字段 from 表 where 字段 运算符 值

‘’’

运算符：

等于： = 

不等于： <> (某些版本sql中，写为： ！= ）

大于： >

小于： <

大于大于： >=

小于等于： <=

在某个范围内： between

搜索某种模式： like 

‘’’

4.AND 和 OR 运算符:用于基于一个以上的条件对记录进行过滤

c.execute(‘select 字段 from 表 where （条件01 AND 条件02） OR 条件03’)

5.order：排序（默认升序，asc升，desc降）

c.execute(‘select 字段01, 字段02, 字段03 from 表 order by 字段01, 字段02 asc, 字段03 desc’)

6.insert:插入

(1)直接插入

c.execute(‘insert into 表 values (值01, 值02,)’)

(2)按列插入

c.execute(‘insert into 表 (列01, 列02, 列03) values (值01, 值02, 值03)’)

7.update:更新

c.execute(‘update 表 set 字段 = 值 where 条件’)

8.delete:删除

(1):删除表数据

c.execute(‘delete from 表’)

(2):删除行数据

c.execute(‘delete from 表 where 条件’)

9.limit：限制查询行数

c.execute(‘select 字段 from 表 limit 数字’)

10.like + 通配符

c.execute(‘select * from 表 where 字段 like 通配符公式’)

‘’’

通配符

替代一个或多个字符： %

替代一个字符： -

列表（不加逗号)里的任一字符： [charlist]

非列表（不加逗号)里的任一字符： [^charlist] 或 [!charlist]

'''

11.between/in：范围

c.execute('select * from 表 where 字段 between 值01 and 值02 ')

c.execute(‘select * from 表 where 字段 in （值01, 值02, 值03, …）’)

12.Alias:

(1):表 名称 指定别名

c.execute(‘select a.id,a.name,b.money from sql_notebook as a,sql_notebook001 as b where a.id = b.id’)

(1):字段 名称 指定别名

c.execute(‘SELECT LastName AS Family, FirstName AS Name FROM Persons’)

13.join:

c.execute(‘select a.id,a.name,b.money from sql_notebook as a,sql_notebook001 as b from a inner join b on a.id = b.id’)

等效于

c.execute(‘select a.id,a.name,b.money from sql_notebook as a inner join sql_notebook001 as b on a.id = b.id’)

‘’’

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

‘’’

14.union:合并两个或多个

SELECT 语句的结果集(SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。)

如果允许重复的值，使用 UNION ALL

c.execute('select a.id from sql_notebook as a UNION select b.id from sql_notebook001 as b ')

15.select into:创建表的备份复件

c.execute('SELECT Persons.LastName,Orders.OrderNo \

       INTO Persons_Order_Backup \

       FROM Persons \

       INNER JOIN Orders \

       ON Persons.Id_P=Orders.Id_P')

ps:sqlite不适用该语法，等效：

c.execute(‘CREATE TABLE newtable AS SELECT * FROM oldtable’)

16.creat database:创建数据库

c.execute(‘craeat database db_name’)

17.creat table:创建表

c.execute('CREATE TABLE 表名称( \

        字段01 数据类型,        \

        字段02 数据类型,        \

        字段03 数据类型,        \

        ....                  \

                         )')

todo 数据类型：（暂列举sqlite）

‘’’

sqlite:

    1.  NULL，值是NULL

    2.  INTEGER，值是有符号整形，根据值的大小以1,2,3,4,6或8字节存放

    3.  REAL，值是浮点型值，以8字节IEEE浮点数存放

    4.  TEXT，值是文本字符串，使用数据库编码（UTF-8，UTF-16BE或者UTF-16LE）存放

    5.  BLOB，只是一个数据块，完全按照输入存放（即没有准换）

大多数的数据库引擎（到现在据我们所知的除了sqlite的每个sql数据库引擎）都使用静态的、刚性的类型，使用静态类型，数据的类型就由它的容器决定，这个容器是这个指被存放的特定列。

Sqlite使用一个更一般的动态类型系统，sqlite中，值的数据类型跟值本身相关，而不是与它的容器相关。

Sqlite的动态类型系统和其他数据库的更为一般的静态类型系统相兼容，但同时，sqlite中的动态类型允许它能做到一些传统刚性类型数据库所不可能做到的事。

‘’’

18.constraints:约束

(1)not null:不向字段添加值，无法插入新记录或者更新记录

c.execute(’’'CREATE TABLE Kzz

       (Code TEXT NOT NULL,

       Total_Investment_Amounts REAL NOT NULL);''')

(2)unique:约束唯一标识数据库表中的每条记录

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

(2.1)创建 表 的时候 创建unique约束

c.execute(’’'CREATE TABLE Persons(

            Id_P int NOT NULL,

            LastName varchar(255) NOT NULL,

            FirstName varchar(255),

            Address varchar(255),

            City varchar(255),

            UNIQUE (Id_P)

                             )''')

(2.2)表 已存在 创建unique约束

c.execute(‘ALTER TABLE 表 ADD UNIQUE (字段)’)

(2.3)命名 UNIQUE 约束，创建 多列约束

c.execute(’’'ALTER TABLE 表

ADD CONSTRAINT 约束名 UNIQUE (字段01,字段02)’’’)

(2.3)撤销 UNIQUE 约束

c.execute(’’'ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID’’’)

(3):PRIMARY KEY:主键 约束

(3.1)创建

c.execute(’’'CREATE TABLE Persons

(

Id_P int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

‘’’)

(3.2)命名 PRIMARY KEY 约束，为多个列定义 PRIMARY KEY 约束

c.execute(’’'CREATE TABLE Persons

(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

)

‘’’)

(3.3)表 已存在 创建unique约束

c.execute(’’'ALTER TABLE Persons

ADD PRIMARY KEY (Id_P)’’’)

(3.4)撤销约束

c.execute(’’'ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID’’’)

(4):FOREIGN KEY:外键 约束（参照完整性）

外键：外来的主键

删除：从表记录不存在时，主表才可以删除。删除从表，主表不变

更新：从表记录不存在时，主表才可以更新。更新从表，主表不变

(4.1)创建

c.execute(’’'CREATE TABLE Orders

(

Id_O int NOT NULL PRIMARY KEY,

OrderNo int NOT NULL,

Id_P int FOREIGN KEY REFERENCES Persons(Id_P)

)’’’)

(4.2)FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束

c.execute(’’'CREATE TABLE Orders

(

Id_O int NOT NULL,

OrderNo int NOT NULL,

Id_P int,

PRIMARY KEY (Id_O),

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)

REFERENCES’’’)

(4.3)表已存在的情况下为 “Id_P” 列创建 FOREIGN KEY 约束

c.execute(’’'ALTER TABLE Orders

ADD FOREIGN KEY (Id_P)

REFERENCES Persons(Id_P)’’’)

(4.4)撤销 FOREIGN KEY 约束

c.execute(’’'ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders’’’)

‘’’

还有sql关键字：

Check

Default

Create Index：创建索引

Drop：删除字段、表或者数据库

Alter：修改

Increment

View：视图函数

Date

Nulls：是否为零

isnull()：如果值为零，赋予其中值

还有sql函数：

avg()：平均值

count()：选中的总行数

first()：第一行

last()：最后一行

max()：最大值

min()：最小值

sum()：求和

Group By

Having

ucase()

lcase()

mid()

len()

round()：小数保留位数

now()：当前时间

format()

还有：

数据库约束

存储过程：将预先写好的sql语句存储起来（已转化为010101的机器码），下次直接使用提升性能

触发器：一定的增删改查条件触发，调用存储过程

‘’’

start_time = time.time()

look = c.execute(sql)

end_time = time.time()

continuous_time = end_time - start_time

print(‘运行该sql语句的时间为{}’.format(str(continuous_time)) + ‘s’)

for i in look:

print(i)

ji