数据库笔记

1、数据库(Database)是按照数据结构来组织、存储和管理数据的仓库 ，古代人用书本来记录数据，分门别类进行存放，计算机的发展，让数据有了新的存储方式--计算机，这就是现代的数据库，例如myql、oracle等

2、操作数据库主要有两种操作：读（取）、写（存），细化为增删查改（增加、删除、查询、修改），而每个操作无论使用任何种类的数据库，都是一样的关键字 insert into（增加）、delete（删除）、select（查询）、update（更新）

3、数据库理论上可存储任何数据：文字、图片、音乐、视频，当然存储文字是最常见的功能，因为文字其占用的内存相对而言非常小，而图片、音乐、视频虽然数据库支持直接存储，但是一般不会直接存储，而是使用折中方式，存储对应文件的链接地址，这样可以减轻数据库的压力

对于文字而言：一个汉字占用两个字节（B），一个英文汉字或者数字占用一个字节（B）【UTF-8编码】

看下面的等式：

每个汉字占2B，而1M=1024KB，1KB=1024B，所以一M是512*1024个汉字，五十多万字

计算机界有一句话：所有东西都是由0和1组成的，那么任何东西都可以转成字节流，当然包括了图片和音频，图片和音频非常复杂，转成字节流，占用的空间很大，并不适合直接存储在数据库里面，所以内容大的文件一般都会存储在文件服务器，而数据库只保持一个链接地址。

4、举个例子：假设你有一个房子（数据库），房子里面对方很多琐碎的小东西（文字内容），这个时候你买了一辆大货车，ok，你需要地方来放这个大货车（大图片或者音频），直接放在房子里？可以，不过这样，你房子估计都没其他空的地方了，这个时候你到房子外建了车库（文件服务器），把货车放进去，用钥匙（文件链接地址）锁起来，这样，你只需要在你的房子里面存放一把钥匙就可以，大大节省了你的房子空间

5、数据库的规则

项目开发一般都会有数据库，而每个人可能都会有自己使用数据库不同的习惯，当大家在一个团队的时候，就需要遵循一些约定成俗的规则（一个人的时候可以不遵守，爱咋咋的），目前在数据库方面，已经有了一些成熟且大家都认同的规则，当大家都遵循这种规则，自然可以更好的协同开发，更高效轻松的进行工作，下面就讲讲数据库的一些基本规则（个人觉得）

（1）表的设计和创建

a）表（table），约定单个表中必须有一个唯一主键（相当于身份证号）来标识每一条数据，

b)单个数据库的字段风格必须统一，尽量不要用中文拼音（当然你也可以用），所谓风格统一即：每一个字段名的开头字母是否大写（Name）、连接字母用驼峰还是下横杠（is_delete or isDelete）,反正就是统一一种即可

c)每一条数据最好有创建时间以及修改时间（修改时间可看数据是否可修改来判断是否需要该字段）

d)如果是数字类型的自动尽量不要用字符型，查询的时候数字型速度比字符型速度快（例如手机号）

e)外键使用要统一，如果数据库用外键，那就都用，如果不用，那就都不用（看选择）

f)数据库尽量不存储文件流

（2）sql方面的[其中会包括sql优化部分内容]

a）增加/插入（insert into）数据，单条数据插入，直接插入即可，当插入多条数据的时候，网上说每次插入10条是最高效的

-- 首先来创建一个用户表users(user是数据库的关键字，表名和字段名尽量不用数据库关键字)
CREATE TABLE `users` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`username` BIGINT (255) NOT NULL COMMENT '登陆账号',
	`nickname` VARCHAR (50) NULL COMMENT '别名',
	PRIMARY KEY (`id`)
);
-- 插入一条数据 
INSERT INTO users (username, nickname)
VALUES
	(12345678910, '月生');
-- 批量插入多条数据 
INSERT INTO users (username, nickname)
VALUES
	(12345678910, '月生1'),(12345678910, '月生2'),(12345678910, '月生3');

b）关键字REPLACE,可以替换insert，其功能一样，可新增数据，在sql语句包括主键的时候，直接替换数据（先删除，后插入，相当于insert与delete的结合）

-- 换成replace同样可以插入数据，与普通insert一样的功能 
REPLACE INTO users (username, nickname)
VALUES
	(12345678910, '月生');

-- 但是如果加入主键，则会替换原数据
REPLACE INTO users (id, username, nickname)
VALUES
	(1, 12345678910, '替换');

-- 在查询结果返回中替换掉返回内容，并不修改原数据库内容，这里就把查询返回的所有包含“月生”的nickname替换成了“替换内容”
SELECT
	REPLACE (
		nickname,
		'月生',
		'替换内容'
	) AS nickname
FROM
	users;

-- REPLACE与UPDATE的区别
-- UPDATE 有记录则更新，无则不操作，可选择性更新一部分字段
-- REPLACE 有记录则更新，没有则插入，同时REPLACE更新 == 先删除原来记录，然后再插入
-- 再执行该sql，会发现nickname没有值了
REPLACE INTO users (id, username)
VALUES
	(1, 12345678910);

-- 这就是为什么说REPLACE相当于insert与delete的结合

c）删除（delete）， DELETE FROM 表名[ 删除条件子句]（没有条件子句，则会删除全部）

-- 删除表内所有记录 
DELETE FROM users;

-- 删除id为2的记录
DELETE FROM users WHERE id = 2;

-- mysql的truncate也可以删除表记录,但是没有日志记录，无法恢复数据，慎用，但删除速度会更快
truncate users;

-- 一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。
-- 因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。 

-- 索引的存在会导致在增删改，都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。
-- 删除百万级以上数据可以先删除索引，再删除数据，最后重新添加索引

d）更新（update）， UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

-- 更新单条记录
UPDATE users
SET nickname = '更新名字'
WHERE
	id = 3;

-- 批量更新(同时更新三条记录的两个字段)
UPDATE users
SET username = CASE id
WHEN 1 THEN
	1111
WHEN 2 THEN
	2222
WHEN 3 THEN
	33333
END,
 nickname = CASE id
WHEN 1 THEN
	'名字1'
WHEN 2 THEN
	'名字2'
WHEN 3 THEN
	'名字3'
END
WHERE
	id IN (1, 2, 3)

-- 避免UPDATE将要复制到其他数据库的列。
-- 避免UPDATE建有很多索引的列。
-- 避免UPDATE在WHERE子句条件中的列。

e）查询（select）

-- 使用EXPLAIN关键字可以分析mysql是如何处理自己的sql的
EXPLAIN SELECT
	*
FROM
	users;

-- 不要滥用select * ，需要多少个字段就查几个
SELECT
	username
FROM
	users;

-- 需要查询的时候尽量多加查询限制，例如需要查询是否有人使用“名字1”作为nickname，加上limit 1，
-- 这样mysql在找到一条数据后就停止搜索，而不是全文搜索完再停止。
SELECT
	nickname
FROM
	users
WHERE
	nickname = '名字1'
LIMIT 1;

-- 多表查询
-- 内联（inner join），相当于取交集,通过某一个列或者多个列来关联，两个表必须同时存在该列值对应的数据
SELECT
	*
FROM
	A
INNER JOIN B ON A.b_id = B.id;

-- 左联以左边表为基础，左边表的数据全部查询，右边表的所有相关数据查询，但是右边表无关数据不会出现
SELECT
	*
FROM
	A
LEFT JOIN B ON A.b_id = B.id;

-- 右联与左联倒过来
SELECT
	*
FROM
	A
RIGHT JOIN B ON A.b_id = B.id;

-- 合并多次查询结果，使用条件，多次查询结果的列数以及列名必须一致，结果集中的列名总是等于第一个 SELECT 语句中的列名
-- UNION 合并并且去掉重复的,如下面例子，查询的结果会去掉重复的名字
(SELECT nickname FROM A)
UNION
	(SELECT alias AS nickname FROM B);

-- UNION ALL 合并,可重复，查询的结果重复的名字不会去掉
(SELECT nickname FROM A)
UNION ALL
	(SELECT alias AS nickname FROM B);

-- 数据库表设计尽量不用null作为默认值，null也是占用空间的，数字可以使用一个值作为默认值，字符串可用“”，空串作为默认值

-- between 比in 更快
select * from users where id in (1,2,3);
select * from users where id BETWEEN 1 and 3;

 

转载于:https://my.oschina.net/yueshengwujie/blog/1610637