MySQL数据库

数据库的分类

关系型数据库和非关系类数据库

关系型数据库（RDBMS）

常见的关系型数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
1、oracle数据库
	Oracle前身叫sDL、由LarryE1lison和两个变成任意在1977创办，他们开发了主机的拳头产品，在市场上大量销售。0racle公司是最早开发关系型数据库的厂商之一，其产品支持最广泛的操作系统平台。目前oracle关系数据库产品	的市场占有率数一数二。
	oracle公司是目前全球最大的数据库软件公司，也是近年业务增长极为迅速的软件提供与服务商
	主要应用范围：传统大企业、大公司、政府、金融、证券等。
	版本升级：oracle8i，oracle9i，oracle10g，oracle11g，oracle12c
2、MySQL
	DBA:数据库管理员
	MySQL被广泛的应用在Internet上的大中小型网站中。由于体积小、速度快、总体拥有成本低，开放源代码
	
3、MariaDB数据库
	MariaDB数据库管理系统是MySQL数据库的一个分支，主要由开元社区维护，采用GPL授权许可。开发这个MariaDB的原因之一是：甲骨文公司收购了MySQL后，有MySQL闭源的潜在风险，因此MySQL开元社区采用分支的方式来避开这个风险。
	
4、SQL Server数据库
	MicrosoftsQL Server是微软公司开发的大型关系数据库系统。SQL Server的功能比较全面，效率高，可以作为中型企业或单位的数据库平台。SQL Server可以与winodws操作系统紧密集成，不论是应用程序开发速度还是系统事务处理运行速度，都得到较大的提升，对于在Windows平台上开发的各种企业级信息管理系统来说，不论是c/s（客户机/服务器）架构还是B/s（浏览器/服务器）架构。SQL Server都是一个很好的选择。SQL Server的缺点是只能在windows系统下运行，简称Mssql
	
5、Access数据库
	Access是入门级小型桌面数据库，性能安全性都很一般，可供个人管理或小型企业只用
	可以方便地生成各种数据对象，利用存储的数据建立窗体和报表
	作为office套件的一部分，可以与office集成，实现无缝连接
	Access主要适用于中小企业应用系统，或作为客户机/服务器系统中的客户端数据库。
6、sqlite:
	SQLite，是一款轻小型的数据库，一般测试一些功能的时候，会使用一下，但是这个数据库不支持多用户所以很少用在生产上。
7、PostgreSQL:
	MySQL与PostgreSQL都是开源、免费、强大、且功能丰富的数据库，PostgreSQL标榜自己是世界上最先进的开源数据库，很多企业也都喜欢用这个数据库。
	
6、其他不常用关系型数据库
	DB2、Informix、Sybase等。这些关系型数据库逐步的淡化了普通运维的实现，特别是互联网公司几乎见不到

非关系型数据库（DBMS）

非关系型数据库也被称为NoSQL数据库，NOSQL的本意是“Not Only SQL”。

NoSQL的产生并不是要彻底地否定关系型数据库，而是作为传统关系型数据库的一个有效补充。NOSQL数据库在特定的场景下可以发挥
出难以想象的高效率和高性能。随着互联网Web2.0网站的星期，传统的关系型数据库在应付web2,0网站，特别是对于规模日益扩大的海
量数据，超大规模和高并发的微博、微信、SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
1、memcached

	Memcaced是一个开源的、高性能的、具有分布式内存对象的缓存系统。通过它可以减轻数据库负载，加速动态的web应用，最初版本由
LiveJoumal的BradFitzpatrick在2003年开发完成。目前全球有非常多的用户都在使用它来架构主机的大负载网站或提升主机的高访问网站
的响应速度。
	官网：http://memcached.org/
	由于memcached为纯内存缓存软件。但是最近几年逐渐被其他的可以持久化产品替代如Redis。
	
2、Redis
	和Memcached类似：但redis支持的存储value类型相对更多，包括string（字符串）、list（链表）、Set（集合）和zset（有序集合）等。 
	它提供了Python、Ruby、Erlang、PHP客户端，使用方便。
	官方：http://www.redis.io/documentation
	redis特点：
	1）支持内存缓存
	2）支持持久化存储
	3）数据库类型更丰富
	4）支持主从集群、分布式
	5）支持队列等特殊功能
	应用：缓存从存取memcached更改存取redis
	
3、MongoDB (Document-oriented)
	MongoDB是一个介于关系型数据库和非关系型数据库之间的产品，是非关系型数据库当中功能最丰富，最像关系数据库的。
特点：
	高性能、易部署、易使用、存储数据非常方便
	
4、Cassandra
	ApacheCassndra是一套开源分布式存储系统。它最初由Facebook开发，用于存储特别大的数据。Facebook目前在使用此系统。

5、其他不常用非关系型数据库
	HBase、MemcacheDB、BerkeleyDB、Tokyo Cabinet\Tokyo Tyrant (ttserver)
	ttserver持久化输出，缺点存储2千万条性能下降（由日本人发明）

MySQL基础管理

数据库的操作单元

1
2
3
4
库
表
列
记录（行数据）

基础操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#查看数据库
	show databases;
#创建数据库：CREATE DATABASE数据库名charSetutf8；
	create database wordpress;
#删除数据库
	drop database wordpress;
#切换数据库
	use mysql
#查看表
	show tables;
#创建表，一会再说

#删除表
	drop table 表名；
#查看表结构
	desc 表名
	
#查看所有数据
	select * from user;
#查询指定列的数据
	select user,host,Password from user;
#查询指定列的数据的头3h
	Select user,host,Password from user limit 3 ;
#授权，默认情况下mysql和mariadb都是不允许root用户远程连接登录的。
#grant操作（增删改查，all） on 库名.表名 to 用户名@'%' identified by '密码'
		all代表所有权限（select，delete，create）
	grant all on *.* to root@'10.0.0.%' identified by '123456';
	grant a11 on wordpress.* to wordpress@'10.0.0.%' identified by '123456′;
	grant a11 on wordpress.menu to jaden@'10.0.0.%' identified by '123456'；#jaden用户只能对menu表进行操作
#网站代码中连接数据库的时候使用的是哪个用户，那个用户有什么权限，那么网站代码就能对数据库做什么操作。

#使用普通用户登录
mysql -u wordpress -p123456-h 10.0.0.7
#默认的数据文件存储位置是
/var/1ib/mysql/
#/root/.mysql_history 记录了我们做的历史sql指令

#修改用户密码
#安全初始化，可以修改root用户的密码：mysql_secure_installation
#但是这个动作做完之后，还需要给root设置远程登录权限才能远程用root用户登录

方式2：
	格式：mysql> set password for 用户名@localhost=password（'新密码'）；
	例子：mysql> set password for root@localhost=password('123');

Mysql数据类型

1
2
3
4
5
6
7
int 	整形 数字	适合存储：年龄，加减运算
float	浮点型		 适合存储：余额，加减运算
char	字符串		 适合存储：不做加减运算身份号码密码，单行信息
text	文本		  适合存储：适合多行信息，小说，商品描述
enum	枚举		  适合存储：固定选项，多选一
date	日期类型	 适合存储：时间，一般存储的是unix时间戳，从1970.1.1 0:0:0到现在过了多少秒，这个时间戳是可以转化为具体的时间日期的。
boolen  布尔类型	 true/false对应数字就是0/非0

创建表格

1
2
3
4
5
show create database 数据库名称
create table 表名（id int,name char(10),price float(5,2)）#5表示整数加小数一共五位，2表示小数点后最多两位
desc 表名	#查看表结构
insert into 表名 value(x,x,x)
create table 表名（id int,title char(10) default 'xxx'）#default将默认空值改为“xxx”，默认值类型与前面的char规定的类型有关

MYSQL完整性约束

1
2
3
4
5
6
not null	# 不能为空，默认是可以为空的
default		# default 100，意思是默认值为100
unique		# 唯一
auto_increment	#自增
primary key		#主键：not null+unique，还自带auto_increment自增属性，但是每个表里只能有一列为primary key主键列
unsigned	# 只能存正整数，默认是可以存正数和负数的
1
2
create table t1(id int,title char(10),name char(10)) default charset=utf8 #指定表的字符集编码
alter table t1 charset=utf8mb4 # 更改字符集编码类型

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#修改字段的长度
alter table s2 modify name char(10);
#查看创表语句
show create table s2;
#增加字段
alter table s2 add age int(3);
#删除字段
alter table s2 drop age;
#ALTER  TABLE  表名  ADD  字段名  数据类型 [完整性约束条件]  FIRST；#添加这个字段的时候，把它放到第一个字段位置
#ALTER  TABLE  表名  ADD  字段名  数据类型 [完整性约束条件]  AFTER 字段名；#after是放到后的这个字段的后面去了，我们通过一个first和一个after就可以将新添加的字段放到表的任意字段位置了。
#修改表的字符集
alter table 表名 charset=utf8mb4;

#使用where条件删除单条数据
delete from t5 where name='zhangsan';
#删除所有数据
delete from t5;、

#单条件修改：
update t5 set password='123' where name='wangwu';
#单条件修改多列：
update t5 set password='123',name='xxx' where name='wangwu';
#多条件修改
update t5 set password='123' where name='wangwu' and id=1;
update t5 set password='123' where name='wangwu' or id=1;
#修改所有数据
update t5 set password='123456';

添加用户、修改用户权限

1
2
3
4
5
6
create user wang@'%' identified by '123'  #添加新用户@后面%表示所有的域名都可以访问
show grants for wang@'%'  #查看wang这一用户的MySQL权限
# USAGE表示无权限		ALL PRIVILEGES表示所有的权限

grant all on wordpress（库名）.* to wang@'%'  #给予权限
revoke select on wordpress.* from wang@'%'  #回收权限

MySQL查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#一些mysql内置的函数，有特定的功能：count、as、sum、group_concat、concat等等

查询中国的城市数量？
select count（name） as 中国城市总数 from city where countrycode='cHN';

#查询世界的国家数量？
select count(name) from country;

#查询中国的总人口？
select sum(population) from city where countrycode='chn';

#把多行合并成一行
select group_concat(name) from city where countrycode='chn' and district='hebei';

#把多列合并成一列
select concat(Name,"#",CountryCode,"#",District) from city where countrycode='chn'and district='hebei'

#增加索引
alter table 表名 add primary key(id);

增加索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#创建表的时候，指定索引
create table zhu2(id int(8) primary key AUTO_INCREMENT ,name char(10),passwd char(10));
#createtable 表名9（id int,name char（10)，primarykey（id));

#增加主键索引（要求结果唯一，而且必须要有一个主键列）
alter table t10Ow add PRIMARY KEY（id)；#创建表之后再添加主键索引
#创建普通索引
alter table t1oow add index num_index(num);#num_index索引名字，num是列名
#创建联合索引
alter table t100w add index 1ianhe(k1,k2);
#唯一索引
create table t2（id int,title char(10) unique);


#查看索引
#desc t100w;  #MUL普通索引、PRI主键索引
show index from t100w;
#删除普通索引
alter table t100w drop index 1ianhe;
#删除主键索引
alter table t100w drop PRIMARY key；

UNION联合查询

1
2
3
4
5
6
7
#可以将两张表中的内容放到一起
select * from t2 union select * from t1;
select * from t2 union select user(),version(),database();
#注意：union要求前后查询数据的列数必须相同才可以，不然会报错

#sql注入漏洞监测中经常会使用
select * from t1 union select 1,2,3,user();

MySQL存储引擎

不同的引擎组织存储数据的时候，底层实现原理和磁盘控制有不同的方式，而且各有优劣，看自己的实际使用场景而定。

1
2
3
MyISAM:读性能好，写的性能差		每张表，三个文件
innodb:读性能微弱，写的性能好		每张表，两个文件（现在多用innodb，读性能也非常ok）
# create table c3(id int,name char(10)) ENGINE=myisam default charset=utf8mb4;