数据库学习笔记 - code

# 数据库操作

# 创建数据库

	-- create database 数据库名
	create database hub

# 查看数据库

show databases

# 选中数据库

	-- use 库名
	use hub

# 查看数据库中的表

show tables

# 删除数据库

	-- drop database 库名
	drop database hub

# 数据表操作

# 创建表

	-- create table 表名 (字段名 1 字段类型，…. 字段名 n 字段类型 n);
	create table user(
	username varchar(20),
	password varchar(32)
	);
	-- 创建前判断表是否存在，存在则不创建
	create table if not exists user(
	username varchar(20),
	password varchar(32)
	);

# 查看表

查看表结构

	-- desc 表名
	desc user;

查看表的创建语句

	-- show create table 表名 \G
	show create table user \G;

“\G” 选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示

# 删除表

	-- drop table 表名
	drop table user;

# 数据字段操作

# 修改表字段类型

将 user 表的 username 的类型改为 varchar (20)

	-- alter table 表名 modify 字段名类型；
	alter table user modify username varchar(20);

# 增加表字段

	-- alter table 表名 add column 字段名类型；
	alter table user add clumn age int(3);

# 增加字段时控制字段顺序

在 username 后增加 email 字段

	-- ALTER TABLE 表名 ADD 字段名字段类型 AFTER 字段名；
	alter table user add email varchar(60) after username;

在表最前面增加字段

	-- ALTER TABLE 表名 ADD 字段名字段类型 first;
	alter table user add email varchar(60) first;

# 删除表字段

	-- alter table 表名 drop column 字段名；
	alter table user drop column age;

# 表字段改名

	-- alter table 表名 change 字段原名字段新名字段类型；
	alter table user change email em varchar(60);

# 修改表字段排列顺序

在增加和修改语句（add/change/modify）中，最后都可以加一个可选项 first|after。

	alter table user change email em varchar(60) first;
	alter table user modify em varchar(60) first;

# 修改表名

	-- alter table 旧表名 rename 新的表名
	alter table user rename new_user;

# 数据类型

# 整型

数据类型	所占字节	值范围
tinyint	1 字节	-128~127
smallint	2 字节	-32768~32767
mediumint	3 字节	-8388608~8388607
int	4 字节	-2147483648~2147483647
bigint	8 字节	+-9.22*10 的 18 次方

# 浮点类型

数据类型	所占字节	值范围
float(m, d)	4 字节	单精度浮点型，m 总个数，d 小数位
double(m, d)	8 字节	双精度浮点型，m 总个数，d 小数位
decimal(m, d)		decimal 是存储为字符串的浮点数

浮点是非精确值，会存在不太准确的情况
而 decimal 叫做定点数。在 MySQL 内部，本质上是用字符串存储的。实际使用过程中如果存在金额、钱精度要求比较高的浮点数存储，建议使用 decimal（定点数）这个类型。

# 字符类型

数据类型	所占字节	值范围
CHAR	0-255 字节	定长字符串
VARCHAR	0-255 字节	变长字符串
TINYBLOB	0-255 字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 字节	短文本字符串
BLOB	0-65535 字节	二进制形式的长文本数据
TEXT	0-65535 字节	长文本数据
MEDIUMBLOB	0-16 777 215 字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295 字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 字节	极大文本数据
VARBINARY(M)	允许长度 0-M 个字节的定长字节符串	值的长度 + 1 个字节
BINARY(M)	M	允许长度 0-M 个字节的定长字节符串

# 时间类型

数据类型	所占字节	值范围
date	3 字节	日期，格式：2014-09-18
time	3 字节	时间，格式：08:42:30
datetime	8 字节	日期时间，格式：2014-09-18 08:42:30
timestamp	4 字节	自动存储记录修改的时间
year	1 字节	年份

时间类型在 web 系统中用的比较少，很多时候很多人喜欢使用 int 来存储时间。插入时插入的是 unix 时间戳，因为这种方式更方便计算。在前端业务中用 date 类型的函数，再将 unix 时间戳转成人们可识别的时间。
上面的类型你可以根据实际情况实际进行选择
有些人为了在数据库管理中方便查看，也有人使用 datetime 类型来存储时间。

# 字段其他属性设置

UNSIGNED（无符号） 主要用于整型和浮点类型，使用无符号。即，没有前面面的 -（负号）。存储位数更长。tinyint 整型的取值区间为，-128~127。而使用无符号后可存储 0-255 个长度。

创建时在整型或浮点字段语句后接上 unsigned

ZEROFILL（0 填充） 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

创建时在整型或浮点字段语句后接上 zerofill

default default 属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为 MySQL 不允许插入函数或表达式值。此外，此属性无法用于 BLOB 或 TEXT 列。如果已经为此列指定了 NULL 属性，没有指定默认值时默认值将为 NULL，否则默认值将依赖于字段的数据类型。

创建时在整型或浮点字段语句后接上 default '值'

not null 如果将一个列定义为 not null，将不允许向该列插入 null 值。建议在重要情况下始终使用 not null 属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。

创建时在整型或浮点字段语句后接上 not null

null 为列指定 null 属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null 精确的说法是 “无”，而不是空字符串或 0。

创建时在整型或浮点字段语句后不要声明 not null 即可

# 字符集

# 常用字符集

字符集	说明	字节长度
ASCII	美国标准信息交换代码	单字节
GBK	汉字内码扩展规范	双字节
unicode	万国码	4 字节
UTF-8	Unicode 的可变长度字符编码	1 到 6 个字节

# 实际工作中要使用的编码

字符集	说明
gbk_chinese_ci	简体中文，不区分大小写
utf8_general_ci	Unicode (多语言), 不区分大小写

# 表引擎

引擎名称	特点
MyISAM	常用。读取效率很高的引擎
InnoDB	常用。写入，支持事处等都支持
Archive	不常用。归档引擎，压缩比高达 1:10，用于数据归档
NDB	不常用。主要在 MySQL 集群服务器中使用，不做介绍

# MyISAM

不支持事务，表锁 (表级锁，加锁会锁住整个表)，支持全文索引，操作速度快。常用于读取多的业务。

myisam 存储引擎表由 myd 和 myi 组成。.myd 用来存放数据文件，.myi 用来存放索引文件。
对于 myisam 存储引擎表，mysql 数据库只缓存其索引文件，数据文件的缓存由操作系统本身来完成。

# InnoDB

支持事务，主要面向在线事务处理 (OLTP) 方面的应用。
行锁设计，支持外键，即默认情况下读取操作不加锁。 InnoDB 是为处理巨大数据量时的最大性能设计。

注：行锁：写入、更新操作的时候将这一行锁起来，不让其他人再操作了。表锁：写入、更新操作时，将表给锁起来不让其他人再操作了。事务：同时操作多个数据，若其中的一个数据操作失败。可回滚到操作之前。常用于银行、电商、金融等系统中。

# 索引

# 索引类型

索引类型	功能说明
普通索引	最基本的索引，它没有任何限制
唯一索引	某一行企用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的
主键索引	它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，常用于用户 ID。类似于书中的页码
全文索引	对于需要全局搜索的数据，进行全文索引

# 普通索引

为 money 表的 username 字段增加索引

alter table money add index(username);

# 唯一索引

为 money 表的 email 字段增加唯一索引

alter table money add unique(email);

# 全文索引

为 money 表的 content 字段增加唯一索引

alter table money add fulltext(content);

# 主键索引

为 money 表的 id 字段增加主键索引

alter table money add PRIMARY KEY(id);

# 创建表时也可以声明索引

创建表时可在创建表语句后加上对应的类型即可声明索引

PRIMARY KEY(字段)INDEX [索引名] (字段)FULLTEXT [索引名] (字段)UNIQUE[索引名] (字段)

中括号中的索引名，代表可选

# 增删改查

# 插入

	-- insert into 表 values (值 1, 值 2, 值 n);
	insert into user values(2,'lisi','男');

	-- insert into 表 (字段 1, 字段 2, 字段 n) values (值 1, 值 2, 值 n);
	insert into user(id,username,sex) values(3,'zhangsan','男')

# 一次插入多条记录

insert into user(username,password,sex) values('黄晓明', 'abcdef', 1),( 'angelababy', 'bcdeef', 0),( '陈赫', '123456', 1),('王宝强', '987654', 1);

# 查询

基础查询

select * from user;

指定字段查询

select id,username from user;

as 别名

select id as ID,username as name from user;

查询单个字段不重复记录 distinct

select distinct age from user;

# where

select * from user where age > 18;

where 后可接的条件:
=、>、<、>=、<=、!= 等比较运算符
多个条件还可以使用 or 、 and 等逻辑运算符进行多条件联合查询

select * from user where age > 18 and sex = '男';

# 结果集排序

asc 升序排列，从小到大（默认）
desc 降序排列，从大到小

select * from user order by age desc;

# 多字段排序

按 age 排序，age 相同再按 class 排序

select * from user order by age desc,class desc;

# 结果集限制

显示前五个用户

select * from user limit 5;

# 限制结果集并排序

select * from user oder by age desc limit 5;

# 结果集区间选择

从第 1 条开始取 5 条记录

select * from user oder by age desc limit 0,5;

从第 5 条开始取 5 条记录

select * from user oder by age desc limit 5,5;

# 统计类函数使用

sum 求和
count 统计总数
max 最大值
min 最小值
avg 平均值

查询 user 表的 id 总数

select count(id) from user;

# 分组 group by

有相同的只会展示一条

SELECT * FROM moment GROUP BY user_id;

# 结果再过滤 having

having 子句与 where 有相似之处但也有区别，都是设定条件的语句。

having 是筛选组而 where 是筛选记录。

# 整体使用 SQL

	SELECT [字段1 [as 别名1],[函数(字段2) ,]……字段n] FROM 表名
	[WHERE where条件]
	[GROUP BY 字段]
	[HAVING where_contition]
	[order 条件]
	[limit 条件]

# 多表联合查询

# 内连接

基本语法一：

	-- select 表 1. 字段 [as 别名], 表 n. 字段 from 表 1 [别名], 表 n where 条件；
	select user.uid,user.username as username,goods.oid from user,goods where user.uid = goods.uid

由于表名太长，每次写的时候容易写错。我们可以在表后直接跟上一个简写英文字符串。在前面拼接字段时，直接使用简写字符串。字段即可。

select u.uid,u.username as username,o.oid from user u,goods o where u.uid = o.uid

基本语法二：

	-- select 表 1. 字段 [as 别名], 表 n. 字段 from 表 1 INNER JOIN 表 n on 条件；
	select user.uid,user.username as username,goods.oid from user inner join goods on user.uid = goods.uid

# 外连接

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

select * from user left join goods on user.uid = goods.uid;

右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录

select * from user right join goods on user.uid = goods.uid;

# 子查询

有时候，当我们查询的时候，需要的条件是另外一个 select 语句的结果，这时就需要使用子查询。用于子查询的关键字包括 in、not in、=、!=、exists、not exists 等。

	-- select 字段 from 表 where 字段 in (条件)
	select * from user where uid in (1,3,4);
	select * from user where uid in (select uid from goods);

# 记录联合

使用 union 和 union all 关键字，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示。两者主要的区别是把结果直接合并在一起，而 union 是将 union all 后的结果进行一次 distinct，去除重复记录后的结果。

	-- select 语句 1 union [all] select 语句 2
	select uid from user union select uid from goods;

# 更新

修改 money 表，将 balance 余额减 500。要求 userid 为 15

	-- update 表名 set 字段 1 = 值 1, 字段 2 = 值 2, 字段 n = 值 n where 条件
	update money set balance=balance-500 where userid = 15;

# 修改多个字段

update money set balance=balance-500,username='张三' where userid = 15;

# 同时对两个表进行更新

修改 money，将 money 表的别名设置为 m；user 表的别名设置为 u；将 m 表的余额改为 m 表的 balance * 用户表的 age。执行条件是：m.userid = u.id

	-- update 表 1, 表 2 set 字段 1 = 值 1, 字段 2 = 值 2, 字段 n = 值 n where 条件
	update money m,user u m.balance=m.balance*u.age where m.userid=u.id;

# 删除

删除掉用户表中 id 大于 10 的所有用户

	-- delete from 表 [where 条件];
	delete from user where id > 10;

# 清空表记录

清空表的数据，并且让自增的 id 从 1 开始自增

	-- TRUNCATE TABLE 表名；
	TRUNCATE TABLE user;

【切记】

删除时一定要记住加上 where 条件，不然会清空掉整个表的记录。
删除重要数据前一定要备份、备份、备份。

sql