MySQL初级

MYSQL初级

1 mysql初识

mysql官网

MySQL是一个轻量级关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

目前MySQL被广泛地应用在Internet上的中小型网站中，由于体积小、速度快、总体拥有成本低，开放源码、免费，一般中小型网站的开发都选择Linux + MySQL作为网站数据库。

MySQL是一个关系型数据库管理系统，MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，就增加了速度并提高了灵活性。

由于分布式和集群的出现，mysql也用于大型的网站上。

**数据库的好处：**可以持久化数据到本地、结构化查询

数据库的常见概念：

• DB：数据库，存储数据的容器
• DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
• SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

公司名称	数据库名称
IBM公司	DB2数据库
微软公司	SQLServer数据库 Accesss数据库(Office办公软件)
Oralce公司	Oracle数据库 MySQL数据库(最早是一家瑞典的公司MySQL) SUN并购了(Java语言 收购MySQL)

数据库存储数据的特点：

• 数据存放到表中，然后表再放到库中
• 一个库中可以有多张表，每张表具有唯一的表名用来标识自己
• 表中有一个或多个列，列又称为“字段”，相当于java中“属性”
• 表中的每一行数据，相当于java中“对象”

**常见的数据库管理系统：**mysql、oracle、db2、sqlserver

MySQL的优点：

• 开源、免费、成本低
• 性能高、移植性也好
• 体积小，便于安装

MySQL服务的启停、登出

# 启动
net start 服务名

# 停止
net stop 服务名

# 登录
mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码

-- 退出
exit、quit、\q （三者都可以）

2 sql语言分类及常用命令

SQL分类：

名称	解释	命令
DDL（data definition language）	数据库定义语言， 数据库、表、视图、索引、存储过程	create,drop,alter
DML（data manipulation language）	数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE	insert,delete,update
DQL（Data Query Language ）	用于查询数据库对象所包含的数据	select
DCL（Data Control Language）	数据库控制语言	grant,commit,rollback

show databases -- 显示所有数据库
use dbname     -- 打开某个数据库
describe user  -- 显示表mysql数据库中user表的列信息
create database  [if not exists] student -- 创建数据库
use databasename  -- 选择数据库
drop database [if exists] student -- 删除数据库
SET NAMES GBK     -- 字符乱码！

注意：

操作sql语句时不区分大小写

但是这个跟数据库排序规则有关

假设SELECT * FROM table WHERE txt = ‘a’

如果使用utf8_bin的排序规则，你就找不到 txt = ‘A’ 的那一行, 而 utf8_general_ci的排序规则就可以。

3 数据值和列类型

3.1 数据类型总览

MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等

数据类型描述使用以下约定：

• M表示整数类型的最大显示宽度。M表示整数类型的最大显示宽度。对于浮点和定点类型， M是可以存储的总位数（精度）。对于字符串类型， M是最大长度。允许的最大值M取决于数据类型。
• D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。
• **[ ]**表示类型定义的可选部分。

3.2 约束条件

约束条件就是在给字段加一些约束，使该字段存储的值更加符合我们的预期。

常用约束条件：

约束名	解释
UNSIGNED	无符号，值从0开始，无负数
ZEROFILL	零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL	非空约束，表示该字段的值不能为空
DEFAULT	表示如果插入数据时没有给该字段赋值，那么就使用默认值
PRIMARY KEY	主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id
AUTO_INCREMENT	自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY	唯一值，表示该字段下的值不能重复，null除外。比如身份证号是一人一号的，一般都会用这个进行约束
FOREIGN KEY	外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系

3.3 数值型

数值类型包括整数型、浮点型、定点型

整型数据类型包括：

• tinyint ：微整型
• smallint ：小整型
• mediumint ：中整型
• int ：整型
• bigint ：大整型

这些不同大小范围的整型信息如下：

默认整数类型是带符号的，即可以有正负值，比如：

create table zs(num1 int, num2 tinyint);

此时，num1和num2中都可以存储负数（但都不能超出范围）

不带符号的整数类型设置形式如下：

create table zs(num1 int unsigned, num2 tinyint unsigned);

有符号无符号的简单理解：

• 有符号值可以表示负数，0以及正数
• 无符号值只能为0或正数

如果不手动指定UNSIGNED，那么默认就是有符号的。

UNSIGNED小例子：

• 首先创建一个表

CREATE TABLE int_db(
    a TINYINT,
    b SMALLINT,
    c MIDDLEINT,
    d INT,
    e BIGINT
);

• 查看表结构

分析：

为什么每个字段类型后面的括号都有数值，这个数值实际上就是字段的显示宽度，也就是M的值，M表示整数类

型的最大显示宽度。最大显示宽度为255.显示宽度与类型可包含的值范围无关

我们在创建表的时候并没有指定字段类型的显示宽度，那么，默认的显示宽度则是该字段类型最大的显示宽度

例如字段a的显示宽度为4，是因为TINYINT有符号值的范围是-128到127，

-128的长度为4（负号、1、2、8共四位），所以默认的显示宽度最大为4，其他的以此类推

下面我们再新建一个表，将字段a的修改为无符号类型的。再看看a字段的默认显示宽度

可以看到，默认显宽度就变成3了，因为无符号的TINYINT的值范围为0-255，没有负号，所以最多是3位。

ZEROFILL

下面我们来试试ZEROFILL约束，前面的博客中我们知道。使用该约束后当数据的长度比我们指定的显示宽度小的时候会使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

下面我们新建个表试一下，这次我们来指定一下显示宽度

CREATE TABLE int_db2(
    a TINYINT(8) ZEROFILL,
    b TINYINT(5) UNSIGNED
);

然后插入一条记录：

INSERT int_db2() VALUES(12,12);

可以看到，12变成了00000012，自动在前面补了0，这是因为指定的显示宽度是8，但是12只有两位，所以在前面补0，使长度为8。这就是ZEROFILL的效果

3.4 浮点型

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38，0以及1.175494351E-38 到3.402823466E+38。

M是总位数，D是小数点后面的位数。

占用4字节存储空间，可称为“单精度浮点数”，约7位有效数字。

浮点数存在精度丢失的问题，如果涉及到小数运算，尽量不要用浮点型

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308。

0以及 2.2250738585072014E-308到 1.7976931348623157E+308。

M是总位数，D是小数点后面的位数

占用8字节存储空间，可称为“双精度浮点数”，约17位有效数字。

3.5 定点型

• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

定点小数是“精确的小数”——它通过内部技巧，突破了“有些小数无法用二进制精确表示”的局限。

常用于存储精确的小数，M是总位数，D是小数点后的位数。

小数点和（负数） -符号不计入 M。如果 D为0，则值没有小数点或小数部分。

最大位数（M）为 65.，最大支持小数（D）为30，如果D省略，则默认值为0。

如果M省略，则默认值为10。M的范围是1到65。D范围为0到30，且不得大于M。

DECIMAL也在存储时存在精度丢失的问题（四合五入）。

演示：

定义三个字段分别为float、double和decimal类型，并都插入数字“123456789.123456789123456789”，显示结果。

超出范围和溢出处理:

当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：

如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。

如果未启用限制模式，MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告

3.6 字符串类型

• CHAR[(M)]

一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M的范围为0到255.如果M省略，则长度为1，存储时占用M个字节

• VARCHAR(M)

可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用**L+1（L<=M,L为实际字符的长度）**个字节

• TINYTEXT[(M)]

不能有默认值，占用L+1个字节，L<2^8

• TEXT[(M)]

不能有默认值，占用L+2个字节，L<2^16

• MEDIUMTEXT[(M)]

不能有默认值，占用L+3个字节，L<2^24

• LONGTEXT[(M)]

不能有默认值，占用L+4个字节，L<2^32

• ENUM(‘value1’,‘value2’,…)

ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素

• SET(‘value1’,‘value2’,…)

字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员

注意：

CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节。

3.7 text长文本类型

适用于存储“较长的文本内容”，比如文章内容。最长可存储65535个字符。

如果还需要存储更长的文本，可以使用mediumtext（1600万左右）或longtext（40亿左右）。

设定形式：

字段名称 text

text类型的字段不能设置默认值。

text类型虽然是字符类型，但不能设置长度！！！

text类型的数据不存在行中。

3.8 enum和set类型

enum类型和set类型都是用于存储“有给定值的可选字符”，比如类似表单中的单选，多选，下拉列表。

enum（单选类型/枚举类型）

enum类型通常用于存储表单中的“单选项”的值。

设定形式：

enum(‘选项值1’, ‘选项值2’, ‘选项值3’, …)

这些选项值都对应了相应的“索引值”，类似索引数组的下标，但是从1开始的。

即这些选项的索引值分别为：1， 2， 3， 4， …

enum类型最多可设定65535个选项。

create table tab1 (id int, edu enum(‘大学’, ‘中学’, ‘小学’, ‘其他’ ) );

# 常规插入数据的方式
insert into tab1 (id, edu) values (1, ‘大学’);

# 使用enum插入数据
insert into tab1 (id, edu) values (1, 2); -- 表示中学

set类型（多选类型）

set类型通常用于存储表单中的“多选项”的值。

设定形式：

set(‘选项值1’, ‘选项值2’, ‘选项值3’, …)

这些选项值都对应了相应的“索引值”，其索引值从1开始，并“依次翻倍”。

即这些选项的索引值分别为：1， 2， 4， 8， 16， … &#