MySQL必知必会_mysql catalog

一、数据库基础

• 什么是数据库
  数据库这个术语的用法有很多，有时，它可以表示存放数据的地方；有时又可以表示为数据库管理系统；还可以表示为安装数据库服务端的服务器。

什么是数据库
简单来说，数据库就是一个仓库，这个仓库是一个存放数据的物理位置，将数据按照一定的格式存储起来，你无须关心数据是如何在里面按照怎样的方式存储的。

• 库（database）
  保存有组织的数据的容器（文件夹）
• 表（table）
  我们将数据存入数据库时，不是随便的将数据往仓库内一丢就行了，而是要在仓库中创建一个小房间（文件），把数据放在这个房间（文件）中。
  表是一种结构化的文件，用来存储某种特定类型的数据。
• 行（row）
  表中的一条记录（record）表中的数据是按行存储的，每一行代表一条记录。
• 列（column）
  表中的一个字段（field）。所有的表都是由一个或多个列组成的

一张表就好像一个网格。网格中的每一列存储着相同种类的信息，每一行存储着同一个对象的不同信息。
表中的每个列都有相应的数据类型。数据类型限制了列可以存储的数据种类。还可以将数据正确排序，并优化磁盘使用。
在创建表时，必须对数据类型给与特别的关注。

• 主键（primary key）
  表中的每一个行（记录）都应该有一个可以唯一标识自己的一个列（或多个列联合）。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只修改或删除你想改的行。

主键不是强制要求你定义的，但是，作为数据库设计人员，应该保证创建的每个表都具有一个主键，以便于后期对数据的增删改查等。

定义为主键的列应该满足一下条件：
1.任意两行都不具有相同的主键值；
2.每个行都必须具有一个主键值
有时候，我们可以使用多个列联合作为主键（上面提到的多个列联合）。在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列的组合必须是唯一的（单个列的值可以重复）

关于主键的使用的一些好习惯：

• 不更新主键列中的值
• 不重用主键列的值
• 不在主键列中使用可能会更改的值。（栗子：使用薪资作为主键，薪资随时可能会更改，这种会随时更改的数据列不应该作为主键）

• SQL语句
  SQL（Structured Query Language）结构化查询语言。SQL是一种专门用来与数据库通信的语言。与其他语言相比，SQL由很少的词组成，这是有意而为的。设计SQL的目的就是为了很好地完成一项任务，提供一种从数据库中读写数据的简单有效的方法。

优点：

• SQL不是某个特定数据库管理系统独有的语言。几乎所有的DBMS都支持SQL。
• SQL简单易学。
• SQL是一种强有力的语言，灵活，可以进行非常复杂和高级的数据库操作。

---

二、MySQL简介

数据的存储、检索、管理和处理都是由数据库软件——数据库管理系统（DataBase Management System，DBMS）完成的。MySQL也是一种数据库管理系统。

MySQL的优点：

• 开源免费
• 高性能
• 简单易用

DBMS可分为两大类：

• 基于共享文件系统的DBMS
• 基于C/S架构的DBMS

MySQL、Oracle等数据库都是基于C/S架构的数据库。
S（服务器端）是负责所有数据存储、访问和处理的软件。运行这个软件的计算机称为数据库服务器。服务端接收客户端发来的数据操作请求，然后执行请求，并将执行结果返回给客户端。
C（客户端）是用于与用户打交道的软件。用户无需直接对数据库进行操作，只需要将操作需求提交给服务器端。

MySQL命令行程序
每个MySQL都有一个名为mysql的简单命令行程序。这个程序没有任何的图形界面、没有鼠标支持、没有菜单选型等类似的东西。
我们打开cmd/Terminal输入mysql（需要将mysql添加到环境变量中，如何添加环境变量请点这里）就会出现一个简单的错误提示：

我们在输入mysql的同时需要指定一些参数
mysql -h(mysql服务端ip地址, 不填默认为本级回环地址127.0.0.1) -p(端口号, 不填默认为3306) -u(用户名) -p(密码)
登录成功以后显示这样一个界面：

大家可以看到我这里并没有在mysql后面输入任何参数，这是因为修改了mysql配置文件，在配置文件中保存了用户名和密码，这样每次登录就免去输入用户名面膜的烦恼了。如何配置mysql

注意点：

• 命令输入在游标（mysql>）之后
• 以分号（；）或者\g作为结束标志，按enter只有换行效果，不会执行命令。
• 输入help或\h获得帮助
• 输入quit或者exit退出mysql命令行程序

尽管现在有很多的带图形界面的mysql客户端程序，作为编程人员，你应该熟悉mysql命令行程序的使用。

---

存储引擎

#待整理

---

三、使用MySQL

连接mysql服务器

为了连接到MySQL服务端，我们需要以下信息：

• 主机名（ip地址） 如果连接到本地MySQL服务器，可以为localhost或’127.0.0.1’;
• 端口号（默认端口号为3306）；
• 用户名
• 用户密码

这四个参数足够我们连接一个MySQL服务器。当我们进入服务器以后，可以对数据库进行一系列操作。

库相关

MySQL服务器自带数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库

• 语法
  CREATE DATABASE 数据库名;

• 数据库命名规范
  可以由字母、数字、下划线、＠、＃、＄组成
  区分大小写
  具有唯一性
  不能使用关键字如 create、select
  不能单独使用数字
  最长128位

• 数据库相关操作

查看数据库
	show databases;
	show create database db1;
	select database();
选择数据库
	USE 数据库名
删除数据库
	DROP DATABASE 数据库名;
修改数据库(不能修改库名！！！)
	alter database db1 charset utf8;
1
2
3
4
5
6
7
8
9
10

Ps：一定要先使用use选择一个数据库，再对数据库下的表进行操作。

表相关

创建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
1
2
3
4
5
6

注意：

• 在同一张表中，字段名是不能相同
• 宽度和约束条件可选
• 字段名和类型是必须的

MariaDB [(none)]> create database db1 charset utf8;  # 配置过文件以后默认字符编码为utf8，无需再次设置

MariaDB [(none)]> use db1;

MariaDB [db1]> create table t1(  
    -> id int, 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)
    -> );

MariaDB [db1]> show tables; #查看db1库下所有表名

# 查询表结构，desc为describe的缩写
MariaDB [db1]> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

# 查询语句
# 通过字段名查询
MariaDB [db1]> select id,name,sex,age from t1;  
Empty set (0.00 sec)

# 全部查询
MariaDB [db1]> select * from t1;
Empty set (0.00 sec)

# 部分字段名查询
MariaDB [db1]> select id,name from t1;
Empty set (0.00 sec)
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

---

向表中插入数据

MariaDB [db1]> insert into t1 values
    -> (1,'Catalog',18,'male'),
    -> (2,'asd',22,'female')
    -> ;

# 查询表中数据
MariaDB [db1]> select * from t1;
+------+---------+------+--------+
| id   | name    | age  | sex    |
+------+---------+------+--------+
|    1 | Catalog |   18 | male   |
|    2 | asd     |   81 | female |
+------+---------+------+--------+

MariaDB [db1]> insert into t1(id) values 
    -> (3),
    -> (4);
MariaDB [db1]> select * from t1;
+------+---------+------+--------+
| id   | name    | age  | sex    |
+------+---------+------+--------+
|    1 | Catalog |   18 | male   |
|    2 | asd     |   81 | female |
|    3 | NULL    | NULL | NULL   |
|    4 | NULL    | NULL | NULL   |
+------+---------+------+--------+
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

---

修改表

语法：
1. 修改表名
	ALTER TABLE 表名 
      	RENAME 新表名;

2. 增加字段
	ALTER TABLE 表名
        ADD 字段名  数据类型 [完整性约束条件…],
		ADD 字段名  数据类型 [完整性约束条件…];
	ALTER TABLE 表名
        ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
	ALTER TABLE 表名
        ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
    ALTER TABLE 表名 
        DROP 字段名;

4. 修改字段
	ALTER TABLE 表名 
		MODIFY  字段名 数据类型 [完整性约束条件…];
	ALTER TABLE 表名 
		CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
	ALTER TABLE 表名 
		CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
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

示例代码

eg：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;
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
45
46
47
48
49
50
51
52
53
54
55
56

---

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录，所以只会复制表结构
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;
1
2
3
4
5
6
7
8
9
10
11

---

删除表

DROP TABLE 表名;
1