1. 数据库基础

1.1 MySQL是什么

1. mysql它是数据库服务的客户端
2. mysqld它是数据库服务的服务器端
3. mysql本质:基于C(mysql) S(mysqld)模式的一种网络服务
4. 一般的文件确实提供了数据的存储功能，但是文件并没有提供非常好的数据管理能力(用户角度)。数据库本质:对数据内容存储的一套解决方案，你给数据库字段或者要求，数据库直接给你结果。
5. mysql是一套给我提供数据存取的服务的网络程序，而数据库一般指的是，在磁盘或者内存中存储的特定结构组织的数据 - 将来在磁盘上存储的一套数据库方案
6. 数据库服务- mysqld
   

1.2 使用案例

样例:
使用mysql建立一个数据库，建立一张表结构，插入一些数据

1. 连接服务器

mysql -h 127.0.0.1 -P 3306 -u root -p
or
mysql -uroot -p

2. 显示数据库

mysql> show databases;

3. 建立数据库

mysql> create database helloworld;

4. 使用数据库

mysql> use helloworld;

5. 创建数据库表

create table student(
id int,
name varchar(32),
gender varchar(2)
);

6. 表中插入数据

insert into student (id, name, gender) values (1, '张三', '男');
insert into student (id, name, gender) values (2, '李四', '女');
insert into student (id, name, gender) values (3, '王五', '男');
or
insert into student (id, name, gender) values 
(1, '张三', '男'),
(2, '李四', '女'),                     
(3, '王五', '男');                   

7. 查询表中的数据

select * from student;

注意如果在第六步-----表中插入数据过程中出现了：

MySQL数据插入错误： Incorrect string value:"XXXXXX"for column'trurname' at row 1

那么在建库，建表的步骤都要标明utf8，如下：

4. 使用数据库

mysql> use helloworld charset utf8;

5. 创建数据库表

create table student(
id int,
name varchar(32),
gender varchar(2)
)charset utf8;

案例总结：

1. 建立数据库，本质就是Linux下的一个目录
2. 在数据库内建立表，本质就是在Linux下创建对应的文件即可!
3. 数据库本质其实也是文件! 只不过这些文件并不由程序员直接操作，而是由数据库服务帮我们进行操作

1.3 服务器，数据库，表关系

• 所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
• 为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。
• 数据库服务器、数据库和表的关系如下：
  

2. 库的操作

2.1 字符集和校验规则

2.1.1 查看系统默认字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

2.1.2 查看数据库的字符集和校验规则

字符集：

show charset;

字符集主要是控制用什么语言。比如utf8就可以使用中文。

校验规则：

show collation;

注意：在数据库对应的db.obj文件(路径为：/var/lib/mysql/数据库名/db.obj)中也可以看到该数据库的字符集和校对规则

2.1.3 修改数据库的字符集和校验规则

2.2 库的操作

2.2.1 创建数据库

• 创建名为 db1 的数据库：

create database db1;

• 在创建数据库时，我们可以设置它的字符集和校验集。当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ general_ ci（不区分大小写）
  

2.2.2 查看当前数据库

show databases;

2.2.3 显示创建语句

show create database 数据库名；

示例：

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */   |
+----------+----------------------------------------------------------------+

说明：

• MySQL 建议我们关键字使用大写，但是不是必须的。
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
• /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

2.2.4 数据库删除

drop database db_name;  //db_name指数据库名字

执行删除之后的结果:

• 数据库内部看不到对应的数据库
• 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

2.2.5 数据库的备份和恢复

2.2.5.1 备份

• 备份一个或多个数据库

//语法：
# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径
//实例：
# mysqldump -P3306 -u root -p -B mytest > /home/xl/MySQL/mytest.sql

若MySQL中没有mytest.sql文件夹，就会自动生成一个mytest.sql文件夹。

• 备份表

# mysqldump -u root -p 数据库名 表名1 表名2 > /home/xl/MySQL/mytest.sql(路径）

2.2.5.2 还原

mysql> source /home/xl/MySQL/mytest.sql(路径）;

还原操作后，原本备份在mytest.sql里的内容，全部会还原。
注意：如果备份数据库时，没有带上-B参数， 那么在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原

2.2.6 查看数据库被连接情况

语法：

mysql> show processlist;

示例：

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User |   Host    |  db  | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 2  | root | localhost | test |  Sleep  | 1386 |       | NULL             |
| 3  | root | localhost | NULL |  Query  |  0   |  NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

3. 表的操作

3.1 创建表

语法：

CREATE TABLE table_name (
   field1 datatype,
   field2 datatype,
   field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;
//也可以：charset=字符集 collate=校验规则 engine=存储引擎;

说明：

• field 表示列名
• datatype 表示列的类型
• 如果没有指定字符集 / 校验规则 / 存储引擎，则以所在数据库的 字符集 / 校验规则 / 存储引擎 为准
• 不同的存储引擎，创建表的文件不一样

实例：

create table users (
   id int,
   name varchar(20) comment '用户名',
   password char(32) comment '密码是32位的md5值',
   birthday date comment '生日'
) character set utf8 engine MyISAM;

3.2 查看表

3.2.1 查看当前数据库的全部表

show tables;

3.2.2 查看特定表的结构

方法一：

desc 表名;

实例：

方法二：
通过查看创建表时的详细信息。

3.3 修改表

实例：

create table users (
   id int,
   name varchar(20) comment '用户名',
   password char(32) comment '密码是32位的md5值',
   birthday date comment '生日'
) character set utf8 engine MyISAM;

• 在users表添加二条记录

mysql> insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
or 
mysql> insert into users (id,name,password,birthday) values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');   
//id,name,password,birthday可以打乱，只要一一对应就好。

• 改变表中某一行数据：改为 id=3

update users set id=3;  若只有一行数据，可改变某一列数据
update users set id=3 where name='张三';   指name='张三'的那一行

• 修改表名

alter table 原表名 rename to 新表名;
or
rename table old_table1 to new_table1, old_table2 to new_table2; 

 to：可以省掉

• 在users表添加一个字段

mysql> alter table users add image_path varchar(100) comment '这是图片的路径' after birthday;
//新增的一列名称为：image_path  位置在birthday后

• 修改name，将其长度改成60

mysql> alter table users modify name varchar(60);
or 
mysql> alter table tt3 change a name varchar(60);

• 删除password列

mysql> alter table users drop password;

• 将name列修改为xingming

mysql> alter table 表名 change name xingming varchar(60); --新字段需要完整定义

3.4 删除表

drop table 表名;

4. 数据类型

4.1 数据类型分类

4.2 数值类型

• 如果数据不合法，不符合数值范围，则插入会失败

4.2.1 bit类型

bit[(M)] : 位字段类型。
M表示每个值的位数(即最多有多少个比特位)，范围从1到64。如果M被忽略，默认为1。

bit使用的注意事项：

• bit字段在显示时，是按照ASCII码对应的符号显示。
• 如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间。

4.2.2 小数类型

4.2.2.1 float

语法：

float[(m, d)] [unsigned] : M指定显示长度（小数位数+整数位数），d指定小数位数

实例：

float(4,2) 表示的范围是-99.99 ~ 99.99。
float(4,2) unsigned 表示的范围是0.00~ 99.99。

注意：

• 如果传入的数精度过高，MySQL在保存值时会进行四舍五入。
• 当传入的数小数位数不够时，MySQL会自动在后面添0

4.2.2.2 decimal

语法：

decimal(m, d) [unsigned] : M指定显示长度（小数位数+整数位数），d指定小数位数

实例：

decimal(4,2) 表示的范围是-99.99 ~ 99.99。
decimal(4,2) unsigned 表示的范围是0.00~ 99.99。

注意：

• decimal 和 float 很像，但是有区别。 float和decimal表示的精度不一样。
• float 表示的精度大约是7位。decimal整数最大位数m为65。
• decimal 支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10
• 如果希望小数的精度高，推荐使用decimal。

4.3 字符串类型

4.3.1 char

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

实例：

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt9 values(100, 'ab');   //要加单引号
Query OK, 1 row affected (0.00 sec)

注意：

• 在utf8_ general_ ci校验规则中，一个汉字是一个字符。
• 添加时要加单引号，因为是字符串

4.3.2 varchar

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

• 在utf8字符集中，一个字符是3个字节
• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。有效字符数是65532%3=21844
• varchar 和 char 比较：
  

4.4 日期和时间类型

常用的日期有如下三个:

• date :日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，为记录的当前时间，占用四字节
• 添加数据时，时间戳自动补上当前时间；更新数据，时间戳会更新成当前时间

4.5 enum和set

4.5.1 enum

语法：
enum：枚举，“单选”类型；
enum(‘选项1’,‘选项2’,‘选项3’,…);
在插入时，可以插入枚举常量或枚举下标（下标从1开始）。

4.5.2 set

set：集合，“多选”类型；
set(‘选项值1’,‘选项值2’,‘选项值3’, …);

插入方法：

1. 直接插入集合常量：插入时，可以插入一个或多个集合常量，如果是多个的话，用逗号分隔。
2. 插入一个数字：使用数字标识每个有哪些集合常量的时候，采用位图的方法

mysql> create table votes(
-> username varchar(30),
-> hobby set('骑车','滑雪','篮球','武术'), 
-> gender enum('男','女'));

insert into votes values('张三', '滑雪, 武术', '男');
insert into votes values('李四', 3, 2);
insert into votes(username,gender) values('赵王', '女');
insert into votes(username,hobby,gender) values('张丽',0 ,'女');
select * from votes;
+----------+---------------+--------+
| username |     hobby     | gender |
+----------+---------------+--------+
|   张三   |    滑雪, 武术   |   男   |
+----------+---------------+--------+
|   李四   |    篮球，武术   |   女   |
+----------+---------------+--------+
|   赵王   |      NULL      |   女   |
+----------+---------------+--------+
|   张丽   |                |   女   |
+----------+---------------+--------+

李四hobby位图分析：

4.5.2.1 集合查询使用find_ in_ set函数

find_in_set(a,b) ：

• 如果 集合a 在 集合b 中，则返回下标（下标是从1开始）；如果不在，返回0；
• b是用逗号分隔的字符串。但是b只能是一个不含逗号的字符串

实例：