数据库知识整理

查询语句

1.查询班级男女生人数

select count(*) from class group by sex

2.从数据库中删除多条重复数据

如果是每个字段都重复：

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

如果是某几个字段重复：

1.select  min(id) id,aa from test GROUP BY aa

2.select id from(

SELECT
    min(id) id
FROM
    test
GROUP BY
    aa ) tempt

3.DELETE
FROM
test
WHERE
id NOT IN (
SELECT
id /,aa,count(aa)/
FROM
(
SELECT
min(id) id
FROM
test
GROUP BY
aa
) t
)

 

3.查询表中姓张的同学的信息

SELECT * FROM STUD WHERE 姓名LIKE“张％”

4.查询表中所有科目分数大于60的学生姓名

select tblstudent.StuId,tblstudent.StuName from tblstudent where
tblstudent.StuId NOT IN
(
select  tblscore.StuId from tblscore where tblstudent.StuId=tblscore.StuId AND tblscore.Score<=60)

5.多表查询-三个表，表A：用户id，用户name;表B：权限id,权限name；表C：用户id 权限id 显示用户Id,权限id,权限name

select a.id,c.id,b.name

from a,b,c

where a.id=c.id AND c.id=b.id

6.分页查询：如查询第五页数据，每页10条数据

select top 10 *  /*页数*/

from (select row_number() over (order  by id) as RowNumber,* from table) as A

where RowNumber >40  /*页大小*（页数-1）*/

详见https://blog.csdn.net/scholar_man/article/details/80782324?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase

7.给一张创意表，有id name show(曝光度)等字段 查找曝光度前十的创意的信息

select top 10* 

from table

order by show desc

8.查询平均成绩大于90的学生

select sname

from student.sscore

where student.sno=sscore.sno

group by sscore.sno

having avg(score)>90

9.全班的数学成绩总和

select sum(成绩) from 学生表 where 学科='数学' group by 学科

10.成绩排名在10到20的人的名字

 

左连接、右连接、内连接

inner join内连接是一种一一映射关系，就是两张表都有的才能显示出来

LEFT JOIN左（外）连接是左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分只能补空显示，所谓的左边表其实就是指放在left join的左边的表

RIGHT JOIN右（外）连接正好是和左连接相反的，这里的右边也是相对right join来说的，在这个右边的表就是右表

慢查询

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

SQL Server 和 Oracle 以及 MySQL 数据库的区别

https://www.cnblogs.com/fengsantianya/p/5864809.html

oracle和mysql区别：mysql:表-数据库-用户 oracle:表-用户-数据库

https://blog.csdn.net/fhkkkbfgggjk/article/details/86064703

范式

1NF：数据库表的每一列都是不可分割的原子数据项

2NF：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

3NF：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

BCNF：在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）/ 在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

具体例子可见 https://blog.csdn.net/jsj13263690918/article/details/79796275

sql注入

SQL是操作数据库数据的结构化查询语言，网页的应用数据和后台数据库中的数据进行交互时会采用SQL。而SQL注入是将Web页面的原URL、表单域或数据包输入的参数，修改拼接成SQL语句，传递给Web服务器，进而传给数据库服务器以执行数据库命令。如Web应用程序的开发人员对用户所输入的数据或cookie等内容不进行过滤或验证(即存在注入点)就直接传输给数据库，就可能导致拼接的SQL被执行，获取对数据库的信息以及提权，发生SQL注入攻击。 

数据库视图

视图是从一个或几个基本表（或视图）中导出的虚拟的表。在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

具体可参考 https://blog.csdn.net/duoyu779553/article/details/86579296

表和视图的区别

• 视图是虚拟的表，视图展示的数据来自于 基本表。
• 视图中不存储具体的数据，而存储的是sql逻辑，基本表的数据发生改变，视图的展示结果也会随着发生改变，它占用少量的物理空间；而表中保存的是数据，占用大的物理空间；
• 对视图的操作跟普通表是一样的，如：创建视图，删除视图。。视图的建立create和删除drop只影响视图本身，不影响对应的基本表。
• 若视图中的字段数据是来自于基表的话，一般是可以对视图中的数据 进行更新的，对视图数据进行添加、删除和修改操作会直接影响基本表。其他情况不允许更新，如：若视图中的字段使用了函数avg(age)等，就不能进行更新；
• 视图的使用：一般都是把基表的子查询sql语句 封装成视图，方便使用，而且更高效。
• 隐藏一些不想展示给用户的数据。只展示必要的数据。如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等。

来自 https://blog.csdn.net/qq_38737586/article/details/100112132

 

如何提高数据库搜索速度

1.索引优化：如果是数据量比较大的情况下，应该在where和order by等查询子句涉及的列上建立索引。

2.查询语句优化：有一些语句可能会让引擎放弃使用索引而进行全表扫描，应该避免使用。比如在 where 子句中对字段进行 null 值判断，在 where 子句中使用 or 来连接条件等等。

3.索引也不是越多越好，应该避免建立无用的索引。

4.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型。因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

其他还有很多，详见https://blog.csdn.net/search_forever/article/details/78891507

乐观锁和悲观锁

并发控制

当程序中可能出现并发的情况时，就需要通过一定的手段来保证在并发情况下数据的准确性，通过这种手段保证了当前用户和其他用户一起操作时，所得到的结果和他单独操作时的结果是一样的。这种手段就叫做并发控制。并发控制的目的是保证一个用户的工作不会对另一个用户的工作产生不合理的影响。

没有做好并发控制，就可能导致脏读、幻读和不可重复读等问题

悲观锁

当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制

悲观锁主要分为共享锁或排他锁

• 共享锁【Shared lock】又称为读锁，简称S锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁【Exclusive lock】又称为写锁，简称X锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。

实现：select...for update

乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

具体实现见：https://www.jianshu.com/p/d2ac26ca6525

 

数据库建表语句

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)

实例

CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

数据库数据类型

char（固定长度）,varchar（可变长度）

binary,varbinary二进制

int,float

date(仅日期),datetime（精确到秒）

具体详见 https://blog.csdn.net/wjn2000414/article/details/82141765

 

MySQL 模糊查询

使用Like查询子句

%：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百 分号（%%）表示。*注：like '%龙%'：查询出所有含有“龙”的记录。

_： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。实例：查询出姓林的姓名(名字三个字) select * from user where realname like'林__'(这里有两横杠)

[ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

select * from user where realname like '[张蔡王]杰'

查询出“张杰”，“蔡杰”，“王杰”（而不是“张蔡王杰”） 

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e” 

select * from user where realname like '林[1-9]'将会查询出“林1”“林2”......“林9”

[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

select * from user where realname like '[^张蔡王]杰'

查询出不姓“张”，“蔡”，“王”的“林杰”，“赵杰”等

 

select * from user where realname like '林[^1-4]'

将排除“林1”到“林4”，寻找“林5”、“林6”、…… 

 

关系型数据库与非关系型数据库

关系型数据库:sql server, mysql,oracle

非关系型数据库：mongoDB,nosql （redis / hbase /mongoDB /CouchDB /Neo4J）

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库严格上不是一加粗样式种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等

优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

 

常用的sql语句

创建一个数据库:
create database 数据库名 [其他选项];
提示: 可以使用 show databases; 命令查看已经创建了哪些数据库。

选择要使用的数据库

use 数据库名;

创建表 

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)

往表中添加数据

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

查询表中的数据

select 列名称 from 表名称 [查询条件];

更新表中的数据

update 表名称 set 列名称=新值 where 更新条件;

删除表中的数据

delete from 表名称 where 删除条件;

创建后表的修改

添加列 alter table 表名 add 列名 列数据类型 [after 插入位置];

修改列 alter table 表名 change 列名称 列新名称 新数据类型;

删除列 alter table 表名 drop 列名称;

重命名表 alter table 表名 rename 新表名;

删除整张表

drop table 表名;

删除整个数据库

drop database 数据库名;

具体详见 https://blog.csdn.net/qq_36763293/article/details/81476142

 数据库表去重

delete from user where name in (select name from (select name from user group by name having count(name) > 1) as s1)
and id not in (select id from (select min(id) as id from user group by name having count(name)>1) as s2);

在进行去重时，数据库无法进行边查询边删除，所以删除的时候必须是第三张临时表作为匹配删除，这样的操作可以省略创建临时表直接删除原表中数据，推荐这种方式进行去重操作。

 

数据库和缓存的区别

数据库具有持久化的存储能力，缓存是中间的、额外的数据保持机制

来自https://segmentfault.com/q/1010000010613859/a-1020000010659313

缓存

【1】缓存就是数据交换的缓冲区（称作：Cache），当某一硬件要读取数据时，会首先从缓存中查询数据，有则直接执行，不存在时从磁盘中获取。由于缓存的数据比磁盘快的多，所以缓存的作用就是帮助硬件更快的运行。

来自 https://blog.csdn.net/zhengzhaoyang122/article/details/82184029

在使用MySQL建表时应该考虑到什么？

 1.字段类型：合适，尽量小，尽量避免null

null的列不容易进行查询优化；null列需要更多的存储空间（mysql做特殊处理）；null列被索引时，每个索引需要一个额外的字节，在myisam里还可能会导致固定大小的索引。 如果计划在该列上建立索引，那么，建议避免设置null

详见 https://blog.csdn.net/iteye_2113/article/details/82576688

2.库名、表名、字段名见名知意,建议使用名词而不是动词。

更多详见 https://www.cnblogs.com/yyjie/p/7299043.html

 

数据库事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

数据库事务的四种隔离级别

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。

Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。这就是脏读

Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。