【MYSQL】MYSQL里的基本操作（1）

初识数据库

数据库就是一种软件，帮助用户更方便的进行数据管理。

数据库还是要自己去访问文件的。站在软件的角度：

• 数据库也是用文件的方式进行数据保存的。

• 数据库中直接和文件打交道是数据库的存储引擎。

数据库分为数据库服务器，数据库库+表结构，数据库客户端。

数据库是一个网络服务器。有自己的客户端 (mysql)和服务端 (mysqld)。CS模式，Client-Server 模式。

数据库属于应用层。协议就是sql 协议。mysql 是一个需要我们自己安装的，底层是直接或者间接访问OS 的文件接口。

Mysql 有很多种客户端，今天我们学习的是命令行客户端。除此之外还有很多种客户端，比如图形化客户端。

登陆客户端的命令：

 mysql -uroot -p

安装数据库服务器，知识安装一个数据库管理系统程序，一般开发人员会针对每个应用创建一个数据库。

为保存实体中的数据，一般都会创建多个表。

所谓的创建数据库，本质就是在Linux下创建目录。

创建数据库表，本质就是在特定目录下创建特定文件。

常用的sql命令行：

 show databases;
 use database_name;
 show tables;
 desc table_name;
 ​
 CREATE TABLE IF NOT EXISTS TABLE_NAME (NAME varchar(16) not null, AGE int not null);
 insert into TABLE_NAME (NAME, AGE) values ('name',age);
 select * from TABLE_NAME;
 ​

MYSQL架构：应用层程序。

为什么有不同的SQL分类？因为有不同的用途。

• DDL data definition language 数据定义语言，用来维护存储数据的结构。

• DML data manipulation language 数据操纵语言 用来对数据进行操作

• DCL 数据控制语言，主要负责权限管理和事务。

创建数据库

 CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, craete_specification]

创建一个数据库，名为db

 create database db;

创建一个使用utf8 字符集的数据库

 create database db2 charset=utf8;

创建一个使用utf8 字符集而且带校对规则的数据库

 create database db3 charset=utf8 collate utf8_general_ci;

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

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

查看数据库支持的字符集

 show charset;

查看数据库支持的校验规则

 show collation;

不同的校验规则会对数据的筛选有影响，比如utf8 默认的校验规则是utf8_general_ci，不区分大小写。而另一个校验规则：utf8_bin 则会区分大小写。

查看数据库

 show databases;

显示创建的数据库的详情

 show create database db_name;

修改数据库

 ALTER DATABASE db_name ...

可以改，但是最好不要修改数据库，强烈不推荐改。

数据库的删除

 drop database db_name;

查看访问数据库的列表

 show processlist

查看表结构

 desc table_name;

修改表结构（增加一列）最好就是在后面默认添加，添加到前面的话很容易出错。

 alter table table_name add column_name column_type;

修改表结构（删除一列）删除完数据和字段全部没了

 alter table table_name drop column_name;

修改表结构（修改）

 alter table table_name modify column_name column_new_type;

修改表名

 alter table table_name rename (to) new_table_name;

删除表

 drop table table_name;

数据类型

数据类型是一种约束。一般可以分类为：数值类型，文本二进制类型、时间类型、string 类型。

分类	数据类型	说明
数值类型	BIT （M）	位类型，M指定尾数，默认1
	TINYINT [unsigned]	0 ～ 255 无符号 带符号 -128～127 默认有符号
	BOOL	0 or 1
	SMALLINT [unsigned]	带符号的 -215~215-1，无符号 0~ 216-1
	INT [unsigned]	带符号的 -231~231-1，无符号 0~ 232-1
	BIGINT [unsigned]	带符号的 -263~263-1，无符号 0~ 264-1
	FLOAT （M, D) [unsigned]	M指定显示长度，d指定小数位数，占用4字节
	DOUBLE (M, D) [unsigned]	表示比float 精度更大的小数， 占用空间8字节
	DECIMAL (M, D) [unsigned]	定点数M指定长度， D表示小数点的位数。
文本、二进制类型	CHAR(size)	定长字符串，最大255
	VARCHAR(size)	变长字符串，最大65535
	BLOB	二进制数据
	TEXT	大文本，不支持全文索引，不支持默认值
时间日期	DATE/DATETIME/TIMESTAMP	日期类型：(yyyy-mm-dd) (yyyy-mm-dd hh:mm:ss) timestamp时间戳
string类型	ENUM 类型	字符串对象
	SET 类型	字符串对象

如果我们进行插入时，如果数据越界（插入了不符合对应数据类型范围的数据），此时sql 直接终止。

但是如果用C语言越界，如char = 2555; 不会报错，最多是一个告警，会自动帮我们截断。这是因为数据库对数据的约束级别更强。可以插进来的一定是正确范围内。

bit类型：bit [(M)] 在显示的时候 按照ascii 码值显示。

float类型 ： float(4,2) 代表总共四位，其中两位是小数。如果超过两位小数，自动四舍五入。

在学习c++的时候，不会有无符号的，但是mysql可以，就是不让插入负数了。

Double 和float 完全相同。

decimal 和float 很像，但是精度不一样，（float表示的精度大约在7位）decimal 的精度更准确，如果希望某个数据表示更精确，就选择decimal。

decimal 整数的最大位数m = 65，支持小数最大位数d 是30，如果d被省略，默认为0。如果m 被省略，默认是10。如果想小数的精度更高，推荐使用decimal。建议不要省略。

char 类型：char(L) 固定长度字符串，L时可以存储的字符长度，单位为字符，最大长度为255.

不要认为字符就是字节，比如一个字符可以是'a' （英文字符），也可以是'中' 中文字符，但是在C++中，用strlen 求的是字节数，用strlen('中'), 得到的结果是3，就表明字符和字节不同。

varchar(L) 可变长度字符串，L代表字符长度，最大字节数65535。变长字符串和定长字符串的区别在于，比如char(8) 和varchar(8)，如果没有存储满，比如存储了四个字符，那么char 仍然占8字符（8 * 3 = 24字节），而varchar 占4字符。

如何选择定长和变长字符串？

• 数据确定长度一样，用定长。有变化，用变长。

• 定长的磁盘空间比较浪费，但是效率高。变长的磁盘空间节省，但是效率低。

• 定长代表直接开辟好对应的空间，变长意义是不超过自定义范围的情况下，用多少，开辟多少。。

日期和时间类型：

• date 'yyyy-mm-dd'占用三字节。

• datetime 时间日期格式 从'yyyy-mm-dd HH:ii:ss' 表示范围从1000 到9999 占用8字节。

• timestamp 时间戳，从1970年开始的，格式与datetime 完全一致，占用四字节。不插入时，默认都是当前时间。

enum 枚举类型，（单选类型）比如

 create table if not exists test(
 name varchar(16),
 gender enum('male','female','unknown')
 );

表示test 的gender 中只可以插入 male 或者 female 或者unknown 三个里面的一个，否则都会失败。也可以插入 1，2，3中的一个，和前面一一对应，但是不推荐用数字。

set 集合类型，可以多选，比如

 create table if not exists test(
 name varchar(16),
 gender enum('male','female','unknown'),
 hobby set('swimming','coding','basketball')
 )engine=INNODB default charset=utf8;

也同样必须在限定的值范围内选择，可以多选，用逗号隔开。可以用数字，0代表什么都不选，但是后面的数字和枚举不同，先转换成二进制序列，二进制序列的第几位为1 就代表选择第几个。

选择 用where找到的只能是完全符合要求（hobby 只有swimming）的，不会选择在集合中所有包含swimming的：

 select * from test where hobby='swimming';

应该用 find_in_set 函数：

 select * from test where find_in_set('swimming', hobby);

Mysql 为了让自己的表结构里面的数据完整性和一致性非常强，它要给我们提供强约束。MYSQL 和数据打交道，会尽量保证数据不出现偏差。

约束 mysql 里面会提供很多，比如数据类型，还有接下来的表的约束：

表的约束

真正的约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性。

空属性

两个值 null（默认的） not null （不为空）放在创建表的字段之后，表示此字段不为空。

数据库默认字段基本上都是字段为空，但是实际开发中，尽量保证字段不为空，因为数据为空没办法参与运算。

MYSQL 里面的null 不同于c/c++ 的null，代表的是 既不为""，也不为0，就是代表空。

除此之外可以添加默认值 default，比如

 create table test(
 name varchar(16) not null,
 age int default 18,
 sex char(1) default '1'
 );

同时也可以 同时用default 加 not null 来修饰字段，会以default 为准。

使用comment 来修饰字段，表明字段的含义。

 create table test(
 name varchar(16) not null,
 age int default 18 comment '用户年龄',
 sex char(1) default '1'
 );

此时如果使用 desc 指令，不能看到comment，只能通过show create table table_name 的方式，如下：

 show create table table_name;

因为这是创建表的完整信息。

zerofill

int(5)，如果插入的数据不足5位，用0 补齐，如果多于5 位，正常显示。这只是最后现实的结果，在MYSQL的存储不变。就如同显示按照

 select hex(b) from test;

其实是按照十六进制去显示，并不会影响b 在mysql 中的存储形式。

主键

数据库存入数据不是唯一目的，也要方便提取。一般而言，要求数据库表中的数据记录，有一个特定的唯一标识，标定唯一性。

主键 ： primary key，用来唯一的约束该字段里面的数据，不能重复也不能为空，一张表中最多只能有一个主键；主键的列通常是整数类型。

• 可以在创建表的时候直接在字段上指定主键。

   create table test(
   id int unsigned primary key comment '学号不能为空',
   name varchar(16) not null
   )engine=INNODB default charset=utf8;

  经过主键约束，id 这个字段绝对不能重复。

• 主键的新增和删除：

   alter table test add primary key(id);
   alter table test delete primary key;

• 复合主键

   create table test(
   id int unsigned,
   course char(10) comment '课程代码',
   score tinyint unsigned default 60 comment '成绩',
   primary key(id, course)
   );

  复合主键里的字段都不能为空，复合主键必须保证字段不全部相同，可以部分相同。

自增长

auto_increment: 当对应的字段不给值，会自动地被系统触发，系统会从当前的字段中已经有的最大值增长到一个新的不同的值，通常和主键搭配使用，作为逻辑主键。

• 任何一个字段要做自增长，前提是本身是一个索引（必须要保证是唯一性的一个字段，我们现阶段学的就是主键，还有唯一键等其他的键）。

• 自增长字段必须是整数。

• 一张表最多只能有一个自增长。

 mysql> create table if not exists t2(
    -> id int unsigned primary key auto_increment comment '自增字段',
    -> name varchar(20) default ''
    -> )engine=innodb default charset=utf8;

如果指明了自增字段，一般不要手动设置他的值，而是让其自动增长（从1开始）。如果插入了一个值，只要不冲突也可以插入，但是会从新值开始加1.

唯一键

一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不作唯一性的比较。二者都不能出现重复。

唯一键的定义：

 create table if not exists test(
 id int unsigned unique comment 'unique',
 name char(20) default ''
 )engine=innodb default charset=utf8;

但是如果我们这样设置：

 alter table modify id int unsigned unique not null;

如果打开desc 去查看表的属性，可以发现，id 字段变成了主键。(如果之前设置过主键，那字段还是会保持unique)

这是因为主键的约束是 一个唯一性，一个非空；唯一键只约束了唯一性，所以唯一键加上约束非空，就等于主键。

主键和唯一键的区别：主键和唯一键并不冲突，在一个表里可以有一个主键，也可以同时具有唯一键；表其实就是mysql 级别的 类和对象的集合，表中保存的其实就是某些场景下的属性字段。

数据库本身就是一个对数据约束非常严格的技术，我们生活中并非只有一个属性具有唯一性（有很多）。也有不存在唯一性的列，主键不是设置了主键属性才成为主键的，而是对应的属性被选择为主键。如果没有被选择，但是仍然要求唯一性的属性，就需要被设置为唯一键。

外键

外键用来定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须有主键或者unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或者为null。

 foreign key(id) references main_table(comlumn);

外键和外键约束的区别： 通过外键产生关系的表，除了在逻辑上要有关联，mysql 在操作上也要维护这种逻辑关系的正确性，就是如果在主表里没有这个字段，那么从表中的外键不能直接插入在主表中不存在的字段 （这就是外键约束）。

外键定义示例；

 mysql> create table if not exists class(
     -> class_id int primary key,
     -> class_name varchar(20) not null)engine=innodb default charset=utf8;
     
 mysql> create table if not exists stu(
     -> id int unsigned primary key,
     -> name varchar(20) not null,
     -> class_id int,
     -> foreign key(class_id) references class(class_id)
     -> )engine=innodb;
     
 mysql> insert into stu values(1, 'amor', 1);
 Query OK, 1 row affected (0.02 sec)
 ​
 mysql> insert into stu values(2, 'nelo', 2);
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> insert into stu values(2, 'nelo', 3);
 ERROR 1062 (23000): Duplicate entry '2' for key 'stu.PRIMARY'

外键是用来实现表和表之间关系的字段，其实只有设置的人自己知道。只要是人操作就可能会犯错，为了约束表之间的关系，需要添加外键约束，从而让mysql 帮助我们去维护表之间的逻辑关系。