数据库中的相关基本知识
数据库执行顺序
- from
- join
- on
- where
- group by(开始使用select中的别名,后面的语句中都可以使用)
- avg,sum…
- having
- select
- distinct
- order by
- limit
数据类型
- ENUM:枚举类型中的类型,可以用数字来选择;
- 时间类型:
- TimeStamp:距离1970-1-1的秒数
- 普通类型:
- char:不可变长度
- varchar:可变长度
- Char相对于varchar增加了数据内存的使用;
- 链接数据库:
mysql -uroot -p
mysql -uroot -p123456(跟密码) - 退出数据库:
Exit
Quit
CTRL+D - 列叫字段,行叫记录;
- WHERE对原表进行判断,HAVING对查询结果的条件判断
数据库的设计
一、三范式
- 第一范式:列必须是原子,不能再拆分;
- 第二范式:在第一范式的基础上,必须有主键,除了主键之外的非主键字段必须完全依赖于主键,而不能依赖于主键的一部分(主键可能由多个字段组成);
- 第三范式:在第二范式的基础上,除了主键之外的非主键字段必须直接依赖于主键;
二、ER模型
- 多对一模型在多的表里面添加字段;
- 多对多模型,新建一个表格,在新表格中添加两个表格的对应字段;
数据库的操作
Sql语句都有分号
- 显示数据库版本:
SELECT VERSION(); - 显示当前数据库的时间:
SELECT NOW(); - 显示所有数据库:
SHOW DATABASES; - 创建数据库:NAME是数据库的名字
CREAT DATABASE NAME; - 查看创建数据库的类型的语句:NAME是数据库的名字
SHOW CREATE DATABASE NAME - 更改创建后的数据库的语句:NAME是数据库的名字
SHOW CREATE DATABASE NAMENEW CHARSET=’UTF8; - 查看新创建的数据库的类型的语句:
SHOW CREATE DATABASE NAMENEW; - 删除数据库:当数据库的名字出现无法识别的时候可以尝试加上`(这个是Tab上边的撇的符号)的符号;NAME是数据库的名字
DROP DATABASE NAME; - 查看当前使用的数据库:
SELECT DATABASE(); - 使用数据库:NAME是数据库的名字
USE NAME;
数据表的操作
一、查看当前数据库中所有的表
- SHOW TABLES;
二、创建表:NAME是数据表的名字
- AUTO_INCREMENT 表示自动增长
- NOT NULL 表示不能为空
- PRIMARY KEY 表示主键
- DEFAULT 默认值
- CREATE TABLE 数据表名字(字段 类型 约束,字段 类型 约束);
– CREATE TABLE NAME (ID INT PRIMARY KEY , NAME VARCHAR (30)); - 创建一个表,其中新建表的数据来自另一个表
– CREATE TABLE 新建表 AS SELECT 字段名1,字段名2…… FROM 已建表; - 复制表结构,创建新表(只有结构,没有内容)
– CREATE TABLE NEW_NAME LIKE OLD_NAME
三、查看表的结构:NAME是数据表的名字
- DESC ‘NAME’;
图 1 插入数据并查询
四、向数据表里面插入数据:NAME是数据表的名字
批量插入:
- INSERT INTO NAME VALUES(根据数据表字段名的顺序填入相应的数据),(根据数据表字段名的顺序填入相应的数据)
- INSERT INTO NAME(字段名1,字段名2……) VALUES (对应字段名1的值,……),(对应字段名1的值,……)
- INSERT OR IGNORE INTO NAME VALUES(根据数据表字段名的顺序填入相应的数据),(根据数据表字段名的顺序填入相应的数据)
五、删除表:NAME是数据表的名字
- DROP TABLE NAME;
- 存在则删除,否则不做处理:DROP TABLE IF EXISTS NAME;
六、数据表的增删查改:NAME是数据表的名字
增
- ALTER TABLE NAME ADD 字段名 类型以及约束;
- 对于已经存在的数据表,更新外键约束
ALTER TABLE NAMEa ADD FOREIGN KEY (字段名) REFERENCES NAMEb(字段名)
删
- 物理删除:真正意义上的删除
DELETE FROM NAME;
DELETE FROM NAME WHERE 字段名 = xxxx; - 逻辑删除:增加字段,标记是否删除了
ALTER TABLE NAME ADD IS_DELETE BIT DEFAULT 0;
ALTER TABLE NAME DROP 字段名;
查(NAME是数据表的名字)
- SHOW CREATE TABLE NAME;
获取当前数据库中所有表的名字:
----SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE = ‘NAME’; - 查询数据表里面的数据:
图 2 STUDENTS表里面的数据
- 无条件全部查询:SELECT * FROM NAME;
- 有条件全部查询:SELECT * FROM NAME WHERE 字段名1 =or xxxxx;
- 查询指定字段:SELECT 字段名1,字段名2,…… FROM NAME;
- 查询指定字段且显示名称更改:
SELECT 字段名1 AS xxx,字段名2 AS xxx,…… FROM NAME;
SELECT NAME.字段名1,NAME.字段名2 FROM NAME;
— 数据表一旦改名,原来的名不可索引字段名
SELECT N.字段名1,N.字段名2 FROM NAME AS N;
— DISTINCT用于去重
可以直接使用 DISTINCT(字段名) 放在SELECT后面相当于一个被筛选的字段
SELECT DISTINCT 字段名 FROM NAME; - 条件查询
①比较运算符:’>’ OR ‘<’ OR ‘=’ OR ‘!=’
单个否定:SELECT * FROM NAME WHERE (NOT) 条件1,(NOT)条件2,……
全部否定:SELECT * FROM NAME WHERE (NOT)( 条件1,条件2,……) - 模糊查询
like: - %替换一个或者多个或没有;_替换一个
- 在查询条件前加就是前面有内容,后面加就是后面有内容
1)查询以“小”字开头的
SELECT 字段名 FROM NAME WHERE 字段名 LIKE “小%”
2)查询有“小”字的
SELECT 字段名 FROM NAME WHERE 字段名 LIKE “%小%”
3)查询有两个字的
SELECT 字段名 FROM NAME WHERE 字段名 LIKE “__ ”
4)查询至少有两个字的
SELECT 字段名 FROM NAME WHERE 字段名 LIKE “__%” - rlike:使用正则表达式
1)查询以“周”开头的
SELECT 字段名 FROM NAME WHERE 字段名 RLIKE “^周.*”
2)查询以“周”开头,以“伦”结尾的
SELECT 字段名 FROM NAME WHERE 字段名 RLIKE “^周.*伦$” - 范围查询
1)IN(1,3,8)表示在一个非连续的范围内
—SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 = XX OR 字段名 = XX OR ……
在一个范围内:
SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 IN (x,x,x)
不在一个范围内:
SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 NOT IN (x,x,x)
2)BETWEEN … AND … 表示在一个连续的范围内
在一个范围内:
SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 BETWEEN … AND …
不在一个范围内:
SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 NOT BETWEEN … AND …
3)判断空:
SELECT 字段名1 = XXXX,…… FROM NAME WHERE 字段名 IS (NOT) NULL; - 排序
①ORDER BY :
—若后面只有一个字段,按照字段排序后,若字段相同,默认按照主键降序排列
—若后面多个字段,前一个字段相同,则按照后一个字段排序,以此类推,若前面都相同,则默认按照主键排列
SELECT * FROM NAME WHERE 条件1 AND 条件2 …… ORDER BY 字段名1 ASC(升序)/DESC(降序),字段名2 ASC(升序)/DESC(降序) - 聚合函数(会忽略空值)
①总数:COUNT
1)SELECT COUNT(*) FROM NAME WHERE 条件;
②最大值:MAX
1)SELECT MAX(字段名) FROM NAME WHERE 条件;
③最小值:MIN
1)SELECT MIN(字段名) FROM NAME WHERE 条件;
④求和:SUM
⑤求平均值:AVG
⑥四舍五入:ROUND(参数1,参数2) 参数1是用来处理的数据,参数2是保留的小数的位数 - 分组:重点是要和聚合函数合起来使用,才能展现分组的好处,注意顺序
①GROUP BY:按照字段名来分组
1)SELECT 字段名,聚合函数 FROM NAME GROUP BY 字段名
②GROUP_CONCAT:将多个字段的值连接起来
1)SELECT GENDER,GROUP_CONCAT(NAME," “,AGE,” ",ID) FROM STUDENTS WHERE GENDER = 1 GROUP BY GENDER;
2)group_concat( [distinct] <要连接的字段> [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
③HAVING:不看分组
1)SELECT GENDER,GROUP_CONCAT(NAME) FROM STUDENTS GROUP BY GENDER HAVING AVG(AGE) > 30 ; - 分页:将数据分为多组,可以查询任意一组
①LIMIT:START/NUMBER + 1的值就是页数
1)限制查询出来的数据最大个数:LIMIT 10
SELECT * FROM NAME LIMIT NUMBER;
2)LIMIT START,NUMBER
SELECT * FROM NAME LIMIT START, NUMBER;
②LIMIT用法详解:
https://blog.csdn.net/a934079371/article/details/103760101
注:START的位置不能用算术式;LIMIT的位置在语句最后; - 链接查询:多个表的关联查询
①内连接:取两个表的交集
1)SELECT … FROM 表A INNER JOIN 表B ON 条件;
2)—按要求显示内容
SELECT NAME1.字段名,NAME2.字段名 FROM 表A INNER JOIN 表B ON 条件 WHERE 条件A AND 条件B;
3)SELECT A.字段名,C.字段名 FROM 表A AS S INNER JOIN 表B AS C ON 条件;
②左连接:以LEFT JOIN左边的表格为基准
1)SELECT A.字段名,C.字段名 FROM 表A AS S LEFT JOIN 表B AS C ON 条件;
③右链接:以RIGHT JOIN右边的表格为基准
1)SELECT A.字段名,C.字段名 FROM 表A AS S RIGHT JOIN 表B AS C ON 条件; - 自关联:表内部的连个字段相关联
①SELECT … FROM NAME WHERE 条件1;
②将一个表当作两个表用,将一张表用AS取两个名字即可; - 子查询
①在SELECT语句里面嵌套SELECT语句; - 窗口函数
①窗口函数:<窗口函数> OVER ([PARITITION BY <列清单>] ORDER BY <排序用列清单>) [PARITITION BY <列清单>]可以省略。
②能够作为窗口函数的聚合函数(sum,avg,count,max,min)
1)专用窗口函数(RANK,DENSE_RANK,ROW_NUMBER):这三个函数均无需传参
a.RANK() OVER (ORDER BY <列名>):计算排序,如果存在相同位次的记录,则会跳过之后的位次。如1,1,1,4
b.DENSE_RANK () OVER (ORDER BY <列名>):计算排序,即使存在相同位次的记录,也不会跳过之后的位次。如1,1,1,2
c.ROW_NUMBER () OVER (ORDER BY <列名>):计算排序,赋予唯一的连续位次。如:1,2,3,4
2)窗口函数 (NTILE(number))分组函数
a.NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值,n:切分的片数
NTILE不支持ROWS BETWEEN
3)Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)
作为 独立的列。
在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag和Lead函数的应用就显得尤为重
要。- lag(exp_str,offset,defval) over(partion by …order by …)
- lead(exp_str,offset,defval) over(partion by …order by …)
- exp_str是字段名称。 offset是偏移量,即是上1个或上N个的值,假设当前行在表中排在第5
行,则offset 为3,则表示我 们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。
defval默认值,当两个函数取上N/下N个值,当在表中从当前行位置向前数N行已经超出了表的
范 围时,lag()函数将defval这个参数值作为函数的返回值,若没有指定默认值,则返回NULL,
那么 在数学运算中,总要给一个默认值才不会出错。
- Exists语句:
①Exists谓词返回的是一个布尔值,在嵌套的子查询中,查询的字段存在,则exists返回True,否则返回false
1)例如:select e.* from employees as e
where not exists (select dee.dept_no from dept_emp as dee where dee.emp_no = e.emp_no);
这里就指的是,子查询中,如果满足了where条件的dee.dept_no存在,就返回True,并且主查询返回令子查询中where条件成立的e.emp_no所在的那条记录。
https://www.cnblogs.com/netserver/archive/2008/12/25/1362615.html - 并(UNION)、交(INTERSECT)、差(minus)、除去(EXCEPT)
①详解:https://www.cnblogs.com/elves/p/3653771.html - case when 字段名 then
①ase when <列名> then …的搜索case表达式,最后要记得使用end结束case;
4.改
ALTER TABLE NAME CHANGE 字段名 类型以及约束;
ALTER TABLE NAME MODIFY 字段名 类型以及约束;
- 修改数据:NAME是数据库的名字
①全部修改:UPDATE NAME SET 字段名1 = xxxxx,字段名2 = xxxxx;
②部分修改:UPDATE NAME SET 字段名1 = xxxxx,字段名2 = xxxxx WHERE 主键 = xxxxx; - 同步表数据
①UPDATE NAMEa INNER JOIN NAMEb ON 条件 SET 需要同步的字段; - Repace:
①网址:
https://blog.csdn.net/zhangjg_blog/article/details/23267761
② MySQL replace into 有三种形式:
1.replace into <表名>(<列名>) values(…)
2.Replace into <表名>(<列名>) select …
3.replace into <表名> set <列名>=value, … - (4)更改数据表的名字:
①rename table <变更前的名称> to <变更后的名称>
七、零碎知识 NAME是数据表名
1.IF NOT EXISTS判断是否存在,不存在就执行,存在则不执行;
2.Select选出来的是数值,而且是原原本本的数值,并非有指的是这个数有一个、两个;
3.Left join 后面的条件接在on还是where后面输出的结果不同,要注意;
4.Not in表示筛选的条件不在这个范围里面,而!=则表示符号前后两个值不相同;
5.多个数据表之间关联的时候不一定要用join将其关联成新表,只要能在多个表之间建立筛选关系即可;
八、数据库函数
1.Extract()函数,用于提取时间,可以提取年月日时分秒,还可以组合提取,用法:https://www.runoob.com/sql/func-extract.html
2.Length()函数,用于计算字符串的长度;
3.subtring(对象字符串,截取的起始位置,截取的字符数);
4.
使用python操作数据库
一、基本流程
from pymysql import *
def main():
#链接数据库
conn = connect(host = 'localhost',
port = 3306,
user = 'root',
password = '123465',
database = 'jing_dong',
charset = 'utf8')
#获取游标对象
cursor = conn.cursor()
#通过execute执行sql语句
cursor.execute("select * from goods;")
#通过游标对象获取数据
cursor.fetchall() #获取全部数据
#关闭游标对象
cursor.close()
#关闭数据库
conn.close()
if __name__ == '__main__':
main()
二、增、删、改
1.在执行完sql语句之后一定要Conn.commit(),数据库里才会更新数据;
2.在commit之前可以使用conn.rollback语句撤销上一个commit之后的所有操作;
3.防止SQL注入,使用execute自行获取字符串
Mysql高级
一、视图 NAME是新建的数据表名
1.一张虚拟的表:CREATE VIEW NAME AS + SQL语句;
2.视图无法更新,仅仅用来方便查询数据;
3.删除视图:DROP VIEW NAME;
4.作用:
(1)提高了重用性;
(2)对数据库重构,却不影响程序的运行;
(3)提高了安全性能,可以对不同的用户;
(4)让数据更加清晰;
(5)在原表更新了数据以后,视图也会更新数据;
二、事务----所用于事务的增、删、改
事务的四大特性(简称ACID)
- 原子性A:一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性。
- 一致性C:数据库总是从一个致性的状态转换到另一个一致性的状态。(在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200美元。因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。)
- 隔离性I:通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。(在前面的例子中 ,当执行完第三条语句、第四条语句还未开始时,此时有另外的一个账户汇总程序开始运行,则其看到支票帐户的余额并没有被减去200美元。)
- 持久性D:一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。)
开启事务
- START TRANSACTION;
- BEGIN;
提交事务
- COMMIT
回滚事务;
- ROLLBACK;
三、MYSQL中常用的强制性操作
- 网址:https://www.jb51.net/article/49807.htm
四、触发器
- 在指定数据库事件发生时自动执行
- 网址:https://www.runoob.com/sqlite/sqlite-trigger.html
五、索引
- 一种包含着对数据表所有记录的引用指针的特殊文件;
- 目的:提高查询效率;
- 建立过多的索引会影响更新和插入的速度、占用过多的存储空间,而且没必要所有字段都建立索引;
- 开启运行时间监测NAME是新建的数据表名
(1)SET PROFILING = 1; - 查看执行的时间
(1)SHOW PROFILES; - 为表建立索引NAME是新建的数据表名
(1)建立普通索引:CREATE INDEX 索引名 ON NAME(字段名(字段名的长度));
(2)建立唯一索引:CREATE UNIQUE INDEX 索引名 ON NAME(字段名(字段名的长度));
注:字段为字符串的时候要写字段长度,非字符串可以不写; - 查看索引NAME是新建的数据表名
(1)SHOW INDEX FROM NAME; - 删除索引NAME是新建的数据表名
(1)DROP INDEX 索引名称 ON NAME;
六、账户管理
- 创建账户&授权
(1)授予权限:GRANT 权限列表 ON 数据库 TO ‘用户名’@’访问主机’ IDENTIFIED BY ‘密码’;
①ALL PRIVILEGES/SELECT/UPDATE…
②LOCALHOST(本地)/%(任意机器都可以登录)
(2)修改权限:GRANT 权限名称 ON 数据库 TO ‘用户名’@’访问主机’ WITH GRANT OPTION;
(3)刷新权限:FLUSH PRICILEGES;
(4)修改密码:UPDATE USER SET AUTHENTICATION_STRING = PASSWORD(‘xxxxxx’) WHERE USER = ‘数据库名’; - 远程连接(危险慎用)
(1)mysql -uXXX -pXXXXX -h域名 - 删除账户
(1)DROP USER ‘用户名’@’主机’;
七、MySQL主从数据库
- 作用
(1)读写分离
(2)数据备份
(3)负载均衡 - 备份
(1)mysqldump -uroot -p 数据库名 > python.sql - 恢复
(1)mysql -uroot -p 数据库名 < python.sql