Mysql 数据库

最新推荐文章于 2024-09-15 08:03:51 发布

eus12

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量395

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/sue1214/article/details/120519113

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

Mysql 数据库
Mysql服务端架构
数据库基本操作
列属性

Mysql 数据库

Mysql 是一个关系型数据库管理系统，由瑞典Mysql AB公司开发，目前属于Oracle旗下产品，Mysql是最流行的关系型数据库管理系统之一，在WEB应用方面，Mysql是最好的RDBMS(Relational Databasement System,关系数据库管理系统)应用软件。
Mysql是一种开源免费的数据库产品
Mysql对PHP的支持是最好（wanp/lamp）
Mysql中用到的操作指令就是SQL指令

启动和停止Mysql服务

Mysql是一种C/S结构，客户端和服务端
服务端对应的软件，Mysqld.exe
- 命令行方式
  通过Windows下打开cmd控制器，然后使用命令进行管理
  Net start mysql，开启服务
  net stop mysql，关闭服务
- 系统服务方式
  前提：在安装的Mysql的时候将Mysql添加到Windwo的服务中去
  进入服务
  运行输入：services.msc 启用Mysql服务
  通过服务对Mysql服务器进行管理
  方案1：右键服务，然后选择开启或者停止
  方案2：双击服务，进入到服务详情界面，可以点击开启或者停止按钮

登录和退出Mysql系统

通过客户端（mysql.exe）与服务器进行连接认证，就可以进行操作
通常：服务端和客户端不在同一台电脑上

找到mysql.exe（通过cmd控制台，如查在安装的时候指定了mysql.exe所在的环境变量，就可以直接访问。若无，就必须进入到mysql.exe所在路径）
输入对应的服务器地址：-h:host -h[IP 地址/域名]
输入服务器中Mysql监听的端口：-P:port -P:3306
输入用户名：-u:username -u:root
输入密码：-p:password -p:root
连接认证基本语法：

Mysql.exe/mysql -h主机地址 -p端口 -u用户名 -p密码

注意
- 通常端口都可以默认：mysql端口号通常都是3306
- 密码的输入可以先输入-p,直接换行，然后再以密文方式输入密码

退出

断开与服务器的连接，通常Mysql提供的服务数量有限，一旦客户端用完，建议就应该断开连接
使用SOL提供的指令

选项	功能
quit;、exit;、q	退出Mysql

Mysql服务端架构

Mysql服务端架构有以下几层构成：
- 数据库管理系统（最外层）：DBMS,专门管理服务器的所有内容
- 数据库（第二层）:DB, 专门用于存储数据的仓库（可以有很多个）
- 二维数据表（第三层）：Table,专门用于存储具体实体的数据
- 字段（第四层）：Field,具体存储某种类型的数据（实际存储单元）
  数据库中常用的几个关键字
- Row:行
- Column:列（field）

数据库基本操作

数据库是数据存储的最外层（最大单元）
查看当前数据库的所有表
show TABLES;
匹配显示表
show tables like ‘c%’;
查看表结构
desc 表名;
describe 表名;
show columns form 表名;
查看表的创建语句
show CREATE table 表名;
查看当前所使用的数据库
SELECT DATABASE();
创建数据库
create database classtest charset=utf8;
库选项：数据库的相关属性
字符集：charset字符集，代表着当前数据库下的所有表存储的数据默认指定的字符集（如当前不指定，采用DBMS默认的）
校对集：collate校对集
显示数据库
每个数据库文件夹下都有一个opt文件，保存的是对应的数据库选项
- 显示所有数据库
  show databases;
- 显示部分
  show databases like ‘匹配模式’ ;
  _:匹配当前位置单个字符
  %:匹配指定位置多个字符
  获取以my开头的全部数据库： ‘my%’
  获取m开头，后面第一个字母不确定，最后为database的数据库：
  ‘m_database’;
  查看数据库创建语句
  show creat database 数据库名;
  删除数据库
  drop database 数据库名;
  修改数据库
  mysql5.5之前是可以修改的rename命令的，5.5之后就不可以了
  alter database 数据库名 charset=字符集;
  一旦修改成功，对应的opt文件中就会体现
  创建数据表
  create table 表名(字段名字段类型[字段属性],…)
  将表挂入数据库中：
在数据表名前面加上数据库名字，用“.”连接即可，数据库.数据表
在创建数据表之前先进入到具体的数据库即可，use 数据库
复制已有表结构
从已经存在的表复制一份（只复制结构，如果表中有数据不复制）
create table 新表名 like 表名；
设置表属性
表属性指的是表选项：engine, charset和collate
alter table 表名表选项 [] 值;
alter table 表名 charset=gbk;
注意：数据库有很多数据，不要轻易修改表选项
修改表结构
数据库中数据表名字通常有前缀：取数据库的前两个字母加上下划线
rename table student to my_student;
给学生表增加age字段
- alter table 表名 add column age int;
增加字段：放到第一个字段
- alter table 表名 add id int first;
修改字段名
- ALTER TABLE 表名 CHANGE 旧字段名新字段名新数据类型;

插入数据

单行插入
insert into 表名(列名1,列名2) values(数据1,数据2);
多行插入
insert into 表名(列名1,列名2) values(数据1,数据2),(数据1,数据2);
查询数据
查询表中全部数据
select * from 表名;
查询表中部分数据
select 列名1,列名1 from 表名;
简单条件查询数据
select 列名/* from 表名 where 条件;
删除数据
删除表
DELETE from 表名;
删除表中某条数据
delect from 表名 where 条件;
更新操作
更新，将数据进行修改（通常是修改部分字段数据）
若无where条件，那么所有表中对应的那个字段都会被修改成统一值
updata 表名 set 列名=新值 where 列名=旧值;
创建视图(本质是对查询的封装)
create view dept_view as select * from dept de
inner join emp em on de.DEPTNO=em.deptno
查看视图
SELECT * from dept_view where DEPTNO=‘20’;
删除视图
DROP view dept_view;
选择数据库
use dctest;
当前日期
SELECT CURRENT_DATE();
当前时间
SELECT CURRENT_TIME();
当前日期时间
SELECT now();
日期格式化
SELECT date_format(‘2011-09-27’,’%Y %m %d’);

四舍五入
SELECT round(1.25)

x的y次幂
SELECT pow(2,3)

圆周率PI
SELECT PI();
随机数 0-1.0浮点数
SELECT rand();
0-100随机数 0-1.0浮点数
SELECT 100*rand();
包含字符个数
select length(‘abcd’);
截取字符串
SELECT left(‘abcd’,2); --左
SELECT right(‘abcd’,2); --右
SELECT substring(‘abcd’,2,3); 返回字符串str的位置2起3个字符
拼接字符串
SELECT concat(12,34,‘ab’);
去除空格
SELECT ltrim(’ bar '); --返回删除了左空格的字符串str
SELECT rtrim('bar ');— 返回删除了右空格的字符串str
大小写转换
SELECT lower(‘ABC’); --大写转小写
SELECT upper(‘abc’); --小写转大写

字符集

字符编码概念
字符（character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。
在计算机中所看到的任何内容都是字符构成的。
字符编码（character code）是计算机针对各种符号，在计算机中的一种二进制存储代号

字符集概念

字符集（character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。
常见字符集名称：ASCII字符集、GB2312字符集、GB18030字符集、Unicode字符集等。计算机要准确的处理各种字符集文字，需要进行字符编码，以便计算机能够识别和存储种种文字，中文文字数目大，而且还分为简体中文和繁体中文两种不同书写规则的文字，而计算机最初是按英语单字节字符设计的，因此，对中文字符进行编码，是中文信息的技术基础

查看系统保存的三种关系处理字符集
show variables like ‘character_set%’
修改变量
set character_set_client=gbk;
set character_set_connection=gbk;
set character_set_results=gbk;

整数

linyint
迷你整形，系统采用一个字节来保存的整形：一个字节=8位，最大能表示的数值是-128-127
smallint
小整形，系统采用两个字节来保存的整形，能表示0-65535之间
mediumint
中整形，采用三个字节来保存数据
int
整形（标准整形），采用四个字节
bigint
大整数
无符号标识设定
无符号：表示存储的数据在当前字段中，没有负数（只有正数，区间为0-255）
在类型之后加上一个unsigned
alter table my_int add int_6 tinyint unsigned first;
zerofill: 从左侧开始填充0（左侧不会改变数值大小）
数据显示的时候，zerofill会在左侧填充0到指定位，如果不足3位，那么填充到3位，如果本身已经

小数类型

专门用来存储小数的
在Mysql中将小数类型分为两类：浮点型和定点型
浮点型
浮点型又称为精度类型：是一种有可能丢失精度的数据类型，数据有可能不那么准确（由其是在超出范围的时候）
浮点数之所以能够存储较大的数值（不精确），原因就是利存储数据的位来存储指数
float
float又称之为单精度类型：系统提供4个字节用来存储数据，但是能表示的数据范围比整数大的多，大概10^38;只能保证大概7个左右的精度（如果数据在7位以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）
基本语法：
float:表示不指定小数位的浮点数
float(M,D): 表示一共存储M个有效数字，其中小数部分占D位
float(10,2):整数部分为8位，小数部分为2位
浮点数的应用：通常是用来保存一些数量特别大，大到可以不用那么精确
double 又称之为双精度：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右
定点数
定点数：能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入）
Decimal
- Decimal定点数：系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的
- Decimal(M,D): M表示总长度，最大值不能超过65，D代表小数部分
- 定点数的应用：如果涉及到钱的时候有可能使用定点数

时间日期类型

Date
- 日期类型：系统使用三个字节来存储数据，对应的格式为：YYYY-mm-dd，能表示的范围是从1000-01-01到9999-12-12,初始值为0000-00-00
Time
时间类型，能够表示某个指定时间，但是系统同样是提供3个字节来存储，对应的格式为：HH:ii:ss，但是mysql中time类型能够表示时间范围要大的多，能表示从-838:59:59~838:59:59，在mysql中具体用处是用来描述时间段。
time 类型特殊性：本质是用来表示时间区间，能表示的范围比较大
在进行时间类型录入的时候（time）还可以使用一个简单的日期代替时间，在时间格式之前加一个空格，然后指定一个数字（可以是负数），系统会自动将该数字转换成天数*24小时，再加上后面的时间
php中有着非常强大的时间日期转换函数，date将时间戳转换成想要的格式，strtotime又可以将很多格式转换成对应的时间戳。php通常不需要数据库来帮助处理这么复杂的时间日期所以通常配合php的时候，时间的保存通常使用时间戳（真正），从而用整型来保存。
insert into md_data VALUES(‘1990-01-02’,‘5 12:23:23’,‘1990-01-02 12:23:23’,‘1990-01-02 12:23:23’,‘86’)
Datetime
日期时间类型：就是将前面的data和time合并起来，表示的时间，使用8个字节存储数据，格式为YYYY-mm-dd HH:ii:ss
Timestamp
时间戳类型：mysql中的时间戳只是表示从格林威治事件开始，但是其格式依然是：YYYY-mm-dd HH:ii:ss
timestamp当对的数据被修改的时候，会自动更新（这个被修改的数据不是自己）
Year
年类型：占用一个字节来保存，能表示1900_{2155,但是year有两种数据插入方式：0}99和四位数的具体年份插入
year进行两位数插入的时候，有一个区间划分，零界点为69和70：当输入69以下，那么系统时间20+数字，如果是70以上，那配系统时间为19+数字

字符串型

char
-定长字符：指定长度之后，系统一定会分配指定的空间用于存储数据
基本语法：char(L),L代表字符数（中文与英文字母一样），L长度为0到255
varchar
变长字符：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度）
基本语法：varchar(L)，L代表字符数，L的长度理论值位0到65535
因为varchar要记录数据长度（系统根据长度自动分配空间），所以每varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销，是用来保存数据所占用的空间长度如果数据本身小于127个字符，额外开销一个字节;如果大于127个，就开销两个字节
char和varchar数据存储对比（utf8, 一个字符都会占用3个字节）

存储数据	char(2)	varchar(2)	char所占字节	varchar所占字节
A	A	A	2*3=6	1*3+1=4
AB	AB	AB	2*3=6	2*3+1=7

char和varchar的区别：
- char一定会使用指定的空间，varchar是根据数据来定空间
- char的数据查询效率比Varchar高，varchar是需要通过后面的记录数来计算
如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar
text
文本类型：本质上mysql提供了两种文本类型
text：存储普通的字符文本
blob:存储二进制文本（图片，文件），一般都不会使用blob来存储文件本身，通常是使用一个链接来指向对应的文件本身。
text:系统中提供的四种text
tinytext:系统使用一个字节来保存，实际能够存储的数据为：2^8+1
text:使用两个字节保存，实际存储为2^16+2
mediumtext：使用三个字节保存，实际存储为2^24+3
longtext:使用四个字节保存，实际存储为2^32+4
注意：
在选择对应的存储文本的时候，不用刻意去选择text类型，系统会自动根据存储的数据长度来选择合适的文本类型。
在选择字符存储的时候，如果数据超过255个字符，那么一定选择text存储
enum
- 枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最张出现的数据结果
- 如果确定某个字段的数据只有那么几个值，如性性、男、女、保密，系统就可以设定字段的时候规定当前字段只能存放固定的几个值，使用枚举
- 基本语法：emum(数据值1，数据值2……)
- 系统提供了1到2个字节来存储枚举数据，通过计算enum列举的具体值来选择实际的存储空间，如果数据值列表在255个以内，那么一个字节就够，如果255但小于65535，那么系统采用两个字节保存
- create table my_enum(gender enum(‘男’,‘女’,‘保密’)charset utf8);
插入数据：合法数据，字段对应的值必须是设定表的时候所确定的值
enum有规范数据的功能，能够保证插入的数据必须是设定的范围，其他类型都不可以
枚举enum的存储原理，实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标，当系统设定枚举类型的时候，会给枚举中每个元素定义一个下标，这个下标规则从1开始
enum(1=>‘男’,2=>'女,3>=‘保密’)
特性：在mysql中系统是自动进行类型转换的，如果数据碰到“+、-、*、/”系统就会自动将数据转换成数值，而普通字符串转换成数值为0
将字段按照数值输出
SELECT 字段名 + 0 from 表名;
enum字段存储的结果是数值，那么在进行数据插入的时候，就可以使用对应的数值来进行
insert into my_enum VALUES(3);
枚举意义
- 规范数据本身，限定只能插入规定的数据项
- 节省存储空间
set
集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制来进行控制：1表示该选项被选中，0表示该选项没有被选中
基本语法：set(“值1”,“值2”…)
系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元
1个字节：set只能有8个选项
2个字节：set只能有16个选项
3个字节：set只能表示24个选项
8个字节：Set可以表示64全选项
set和enum一样，最终存储到数据字段中的依然是数字而不是真正的字符串
create table dc_set(hoby set(‘篮球’,‘足球’,‘羽毛球’))charset utf8;
插入数据：可以插入多个数据，就是在数据插入的字符串中，使用对应的逗号“，”将选项进行隔开
insert into dc_set VALUES(‘篮球,足球,羽毛球’);
数据选项所在的数据与数据插入顺序无关，最终都会变成选项对应的顺序
分析数据存储的方式，系统将对应的数据选项（设计）按照顺序进行编排，从第一个开始进行占位，每一个都对应一个二制位
数据在存储的时候，如果被选中，那么对应的位的值就为1，否则为0
系统在进行存储的时候会自动将得到的最终的二进制颠倒过来，然后再进行转换成十进制存储
查看数据,按照自动转换成数值来查看
SELECT 字段名 + 0 from 表名;
-
既然是数值，那么就可以插入数值来代替
注意：数字插入的前提是对应的二进制位上都有对应的数值项
set集合的意义
规范数据
节省存储空间
enum:单选框
set:复选框

mysql 记录长度

在mysql中，有一项规定：mysql的记录长度（record == 行 row）总长度不能超过65535个字节
varchar能够存储的理论为65535个字符：字符在不同的字符集下可能占用多个字节
创建表：证明varchar在mysql中能够达到理论值（utf8和GBK）
create table dc_varchar(name varchar(65535)charset uft8);
varchar除了存储的数据本身占用空间：还需要额外的空间来保存记录长度
计算在utf8和gbk下对应的varchar能够存储的长度
uft8最多只能存储21844个字符
gbk最多只能存储32776个字符

列属性

列属性又称之为字段属性，在mysql中一共有6个属性：null，默认值，列描述、主键、唯一键和自动增长
Null 属性
- null属性：代表字段为空
- 如果对应值为yes表示该字段可以为null
- 注意：
  - 在设计表的时候，尽量不要让数据为空
  - mysql的记录长度为65535个字节，如果一个表中有字段允许为null，那么系统就会设计保留一个字节来存储null，最终有效存储长度为65534个字节
默认值
- default，默认值，录字段被设计的时候，如果允许默认条件下，用户不进行数据插入，那么就可以使用事先准备的数据来填充，通常填充的是null
- create table dc_default(name varchar(10)not null,age int default 18)charset utf8;
default关键字的另外一层使用，显示的告知字段使用默认值;在进行数据插入的时候，对字段值直接使用default
insert into cd_default values(‘jack’,‘default’)
列描述
列描述：comment,是专门用于给开发人员进行维护的一个注释说明
查看comment：必须通过查看表创建语句
- create table dc_comment(name varchar(10)not null comment ‘当前是用户名，不能为空’, pass varchar(50) not null comment ‘当前是密码，不能为空’)charset utf8;

主键

主要的键，primary key,在一张表中，有且只有一个段，里面的值具有唯一性
创建主键
系统提供了两种增加主键的方式
方案1：直接在需要当做主键的字段之后，增加primary key 属性来确定主键
方案2：在所有字段之后增加primary key 选项，primary key(字段信息)
- create table dc_pri1(username varchar(10) primary key)charset utf8;
- create table my_pri2(username varchar(10),primary key(username))charset utf8;
表后增加
- 基本语法：alter table 表名 add primary key(字段);
删除主键
基本语法：alter table 表名 drop primary key;
复合主键
案例: 有一张学生选修课表：一个学生可以选修多个选修课，一个选修课也可以由多个学生来选，但是一个学生在一个选修课里只有一个成绩
create table dc_score(student_no char(10),course_no char(10),score tinyint not null,primary key(student_no,course_no))charset utf8;
主键约束
主键一旦增加，那么对对应的字段有数据要求
当前字段对应的数据不能为空
当前字段对应的数据不能有任何重复
主键分类
主键分类采用的是主键所对应的字段的业务意义分类
业务主键：主键所在的字段，具有业务意义（学生ID,课程ID）
逻辑主键：自然增长的整型

自动增长

自动增长：auto increment,当给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据
通常自动增长用于逻辑主键
原理
自动增长的原理
- 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记住当前对应的数据值，再给定一个指定的步长
- 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据
- 自动增长的触发，给定属性的字段没有提供值
- 自动增长只适用于数值
使用自动增长
基本语法：在字段之后增加一个属性auot_increment
- create table dc_auto(id int primary key auto_increment,name varchar(10) not null comment ‘用户名’,pass varchar(50) not null comment ‘密码’)charset utf8;
insert into dc_auto VALUES(null,‘usr’,‘123’);
修改自动增长
修改auto_increment
- alter table dc_auto auto_increment=10;
删除自增长，就是在字段属性之后不再保留auto_increment，当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长
alter table dc_auto modify id int;
查看自增长初始变量
show variables like ‘auto_increment%’;
细节问题
一张表只有一个自增长：自增长会上升到表选项中
如果数据插入中没有触发自增长（给定）
alter table dc_auto modify id int auto_increment;

唯一键

唯一键：unique key, 用来保证对应的字段中的数据唯一的。
主键也可以用来保证字段数据唯一，但是一张表只有一个主键，
- 唯一键在一张表中可以有多个
- 唯一键允许字段数据为null，null可以有多个（null不能与比较）
创建唯一键
创建唯一键与创建主键非常类似
直接在表字段之后增加唯一键标识符：unique[key]
在所有的字段之后使用unique key[字段列表]
在创建完表之后也可增加唯一键
CREATE table dc_unique2(id int primary key auto_increment,username varchar(10),unique key(username))charset utf8;
CREATE table dc_unique1(id int primary key auto_increment,username varchar(10) unique)charset utf8;
在创建完表之后也可以增加唯一键
- alter table 表名 add unique key[字段列表]
查看唯一键
唯一键是属性，可以通过查看表结构来实现
Desc 表示
唯一键效果：在不为空的情况下，不允许重复
在查看表创建语句的时候，会看到与主键不同的一点：多出一个名字
删除唯一键
一个表中允许存在多个唯一键：假设命令为主键一样：
index关键字：索引，唯一键是索引一种（提升查询速度）
alter table 表名 drop index 唯一键名字;
修改唯一键，先删除后增加
复合唯一键
唯一键与主键一样可以使用多个字段来共同保证唯一性
一般主键都是单一字段（逻辑主键），而其他需要唯一性的内容都是由唯一性来处理