mysql基础强化

个人blog，欢迎关注加收藏

连接mysql数据库

1. cmd打开黑屏
2. mysql -u 用户名 -p密码 -h 主机名 -P 端口
   mysql -u root -p -h localhost -P 3306

• 数据库端口号：默认是3306
• -p密码：给密码时-p后不要加空格

3. 退出mysql：quit;

mysql管理（windows版）

1. 启动mysql服务：net start 服务名

• net start MySQL

2. 停止mysql服务：net stop 服务名

• net stop MySQL

3. 服务名，直接本地搜索服务，找到mysql的服务名（安装时自定义的，默认MySQL）
   [外链图片转存失败(img-o3dpnSUz-1565689410358)(./images/mysqlServer.png)]
4. 在linux系统下命令不一致

mysql数据库的三层结构

[外链图片转存失败(img-soq89hj1-1565689410359)(./images/mysql三层数据结构.png)]

sql语句分类

1. DDL:数据定义语句

• create、drop、alter

2. DML:数据操作语句

• insert into 、update、delete

3. DQL:数据查询语句

• select

4. DCL:数据控制语句:用于管理数据库和用户

• commit、savepoint、rollback、grant、revoke

创建数据库的细节讨论

1. 字符集：通过charset来指定字符集，常用有utf8/gbk/big5，主要用utf8
2. 校验规则：关键字collation，创建数据库时，用collate关键字指定，如utf8_general_ci，一般对查询时字母是否区分小大写和排序有影响

create database `dbl` charset utf8;

3. ``反引号用于控制关键字，比如你希望用关键字来作为数据库名称
4. 查看所有字符集：show charset;
5. 查看所有校验规则： show collation;

数据库常用操作：

1. 显示数据库：mysql->show databases;
2. 显示数据库的创建指令：mysql->show create database 数据库名;
3. 删除数据库：mysql->drop database [if exists] 数据库名;

• [if exists]表示if exists可写可不写，if exists表示如果存在才执行命令，不存在也不会报错

4. 查看当前mysql数据库的连接进程情况：
   show processlist;:可以查看当前有多少个客户端连接到mysql情况

数据库的修改

1. 只能修改数据的字符集和校验规则
2. alter database 数据库名 charset 新的字符集 collate 新的校验规则;

数据库的备份与恢复前的操作

• 如果表中有中文，则请先执行mysql->set names gbk;

数据库的备份和恢复(单库)

1. 备份语法：cmd->mysqldump -u root -p 数据库名 > 备份路径

• cmd->mysqldump -u root -p test2 > D:\mysql\test2.bak

2. 恢复：

• 先创建一个新的数据库： mysql->create database 数据库名;
• eg:create database test2;
• 使用数据库：use 数据库名;
• eg:use test2;
• 执行恢复指令：source 备份路径;，路径建议写绝对路径就好
• eg:source D:\mysql\test2.bak（正常sql语句句末要加;，但是加了’;'报错如下：

ERROR:
Unknown command '\m'.
Outfile disabled.
ERROR:
Failed to open file 'D:\mysqlest2.bak', error: 2

，不加;后执行成功）

数据库的备份和恢复(多库)

1. 备份语法：cmd->mysqldump -u root -p -B 数据库名1 数据库名2 数据库名3 > 备份路径

• cmd->mysqldump -u root -p -B test2 mysql > D:\mysql\testB.bak

2. 恢复：

• mysql->set names gbk;
• 恢复指令：source 备份路径
• eg:source D:\mysql\testB.bak

数据表备份和恢复（某个库的某些表）

1. 备份语法：cmd->mysqldump -u root -p 数据库名 数据表名1 数据表名2 数据表名3 > 备份路径

• cmd->mysqldump -u root -p test2 idol > D:\mysql\test2.idol.bak

2. 恢复数据表：

• 如果库不在了，先创建库，如果库还存在，使用库操作：use 库名;
• eg:use test2;
• 恢复操作：source 备份路径;
• eg:source D:\mysql\test2.idol.bak

数据库建表

1. 创建表：

create table 表名(
  字段名 字段类型 字段选项,
  字段名 字段类型 字段选项,
  字段名 字段类型 字段选项,
  ) charset 字符集 collate 校验规则 engine 引擎

2. 表字段名命名规范：下划线法、小驼峰法
3. 引擎：主要MyISAM,InnoDB,meomery三种
4. 数据类型：

• 整型：
  • 表示数范围：tinyint<smallint<mediumint<int<bigint
  • 数据类型占用的大小不一样:tinyint(1个字节)<smallint(2个字节)<mediumint(3个字节)<int(4个字节)<bigint(8个字节)
  • 每个整型数据类型都分为两种，一种是有符号的（包含负数范围），一种是没有符号的，字段属性写上unsigned表示没有符号的，不能表示负数
  • zerofill:0填充，如int(4)指定字段时，当填入的数字不够4位时，左边会以0填充;int(4)不够4位以0填充，但是可以超过4位数;并且会默认该字段为无符号数值(unsigned)，不能为负数;如果直接int zerofill默认为int(10)
  • bit:位类型，create table test3(id bit(1));表示id字段只能存一个字节,如数字0或者1;只能存无符号数值，但不能用unsigned修饰（了解）
• 浮点型：
  • 主要类型：float(M,D)、decimal(M,D)，M表示显示的最大长度，包含小数位，D表示有几位小数
  • 都可以定义为有符号的和无符号的
  • 高精度数值选择decimal,单精度选择float
• 字符串：
  • 主要类型：char、varchar、text
  • char(n):n的范围是1-255，指字符数
  • varchar(n)的n范围跟字符集有关，字符集为utf8，则n = (65535 - 3)/3 = 21844；字符集为gbk，则n = (65535 - 3)/2 = 32766，varchar最大是65535个字节，但是要预留3个字节，其中2个字节用于记录varchar的数据大小，一个字节标识是否允许为null，utf8一个字符占3个字节，gbk一个字符占2个字节
  • char(n)：是定长，如char(4)字段添加’aa’，占用了4个字符的空间
  • varchar(n)：是变长，如varchar(4)字段添加’aa’，占用了2个字符的空间 + 1
  • 当某个字段的长度不确定时，使用varchar
  • 当某个字段时定长的，如手机号、邮编、身份证号等，就用char定长
  • char存放空格时会自动去除，varchar会保留空格
  • text：跟varchar一样使用，但是不用指定字符数的大小
  • 一个表的所有自定义的字段，他们定义的长度和不能超过65535个字节

  create table test2(id int(4),name varchar(21844)) charset=utf8 engine=myisam;
  

  会报错：
  ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  由于自定义字段的字节和>65535：
  varchar:21844 * 3 + 3 = 65535
  int:4
  共：65535 + 4 >65535
  • 如果自定义字段的字节和要超过65535，则可以用text代替varchar
• 日期：
  • 主要类型：date(xxxx-yy-dd)、datetime(xxxx-yy-dd xx:xx:xx)、timestamp(xxxx-yy-dd xx:xx:xx)
  • date只接受日期，datetime和timestamp接受日期—时间，如果没有给时间，默认是00:00:00
  • timestamp在insert和update时会自动更新，比如插入和更新的操作时，timestamp的字段不给值时会自动更新成当前时间
• 枚举enum和集合set
  • 多选使用set数据类型，单选使用enum数据类型

  create table test6(
    id int unsigned not null default 1,
    hobby set('苹果','西瓜','菠萝') not null default '苹果' comment'爱好',
    sex enum('男','女','保密') default '保密' not null comment'性别'
    ) charset=utf8 engine=myisam;
  

  insert into test6 values(1,'苹果','男');
  
  insert into test6 values(2,'苹果,西瓜','男');
  

  • 注意：
    • 首先你添加的选项都是表中规定好的，添加set字段时：‘选项1,选项2,选项3,…’，选项间不要有空格
    • enum数据字段的每一个选项都有一个标识，1,2,3…，可以用标识来替代选项
    • set数据字段的每一个选项也都有一个标识，如(‘苹果’,‘西瓜’,‘菠萝’)对应的就是(1,2,4)，如果填入3，则代表1+2，‘苹果,西瓜’,填入5,则代表1+4,‘苹果,菠萝’,以此类推，所以set的标识是1,2,4,6,8,…
    • 查询set中值：

    select * from test6 where find_in_set('苹果',hobby);
    

    //返回苹果在hobby字段中的第几个位置
    select find_in_set('苹果',hobby) from test6;
    

5. 存放图片、电影、音频：数据库存放的不是真的资源，而是记录该文件的路径，然后通过路径读取该文件
6. 查看数据库数据时，需要数据对齐：

• cmd->mysql -u root -p --default-character-set=latin1
• mysql->set names gbk;