MySQL(一)

---

1|0一、概述

1、什么是数据库 ？
　答：数据的仓库。

2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ？
　答：他们均是一个软件，都有两个主要的功能：

• a. 将数据保存到文件或内存
• b. 接收特定的命令，然后对文件进行相应的操作

PS：如果有了以上软件，无须自己再去创建文件和文件夹，而是直接传递 命令 给上述软件，让其来进行文件操作，他们统称为数据库管理系统（DBMS，Database Management System）

3、什么是SQL ？
　答：上述提到MySQL等软件可以接受命令，并做出相应的操作，由于命令中可以包含删除文件、获取文件内容等众多操作，对于编写的命令就是是SQL语句。SQ，是结构化语言（Structured Query Language）的缩写，SQL是一种专门用来与数据库通信的语言。

2|0二、下载安装

　　MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

想要使用MySQL来存储并操作数据，则需要做几件事情：

1. 安装MySQL服务端
2. 安装MySQL客户端
3. 【客户端】连接【服务端】
4. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等)

2|1Window版本

1|01、下载

http://dev.mysql.com/downloads/mysql/

1|02、解压

　　如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:\mysql-8.0.11-winx64

1|03、初始化

　　MySQL解压后的 bin 目录下有一大堆的可执行文件，执行如下命令初始化数据：(第一步先进入 bin 目录下，如果已经加了环境变量，则可以直接下面一行。)

cd c:\mysql-8.0.11-winx64\bin
 
mysqld --initialize-insecure

　　初始化之后，C:\mysql-8.0.11-winx64 目录下会多出一个 data 目录。如果没有data则自己手动创建一个。

1|04、启动MySQL服务

　　执行命令从而启动MySQL服务。

# 进入可执行文件目录
cd c:\mysql-8.0.11-winx64\bin
 
# 启动MySQL服务
mysqld              #mysqld表示服务端，mysql表示客户端

1|0

　　此时会hang住，就是光标会停留在下面一行不停闪烁。如果要启动客户端，则需要打开另一个 cmd 命令提示符。（之后有解决方法。第5步方法b）

1|05、启动MySQL客户端并连接MySQL服务

　　由于初始化时使用的【mysqld --initialize-insecure】命令，其默认未给root账户设置密码。

# 进入可执行文件目录
cd c:\mysql-8.0.11-winx64\bin mysqld# 连接MySQL服务器 mysql -u root -p # 提示请输入密码，直接回车mysql -h远程IP -P 端口（3306）-u root -p # 提示请输入密码，直接回车

　　到此为止，MySQL服务端已经安装成功并且客户端已经可以连接上，以后再操作MySQL时，只需要重复上述4、5步骤即可。但是，在4、5步骤中重复的进入可执行文件目录比较繁琐，如想日后操作简便，可以做如下操作。

 

a. 添加环境变量

　　将MySQL可执行文件添加到环境变量中，从而执行执行命令即可。

【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用 ； 分割】
 
如：
C:\Program Files (x86)\Parallels\Parallels Tools\Applications;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Python27;C:\Python35;C:\mysql-5.7.16-winx64\bin

　　如此一来，以后再启动服务并连接时，仅需：

# 启动MySQL服务，在终端输入
mysqld
 
# 连接MySQL服务，在终端输入：
mysql -u root -p

b. 将MySQL服务制作成windows服务，需要用管理员权限操作

　　上一步解决了一些问题，但不够彻底，因为在执行【mysqd】启动MySQL服务器时，当前终端会被hang住，那么做一下设置即可解决此问题：

# 制作MySQL的Windows服务，在终端执行此命令：
"c:\mysql-8.0.11-winx64\bin\mysqld" --install
 
# 移除MySQL的Windows服务，在终端执行此命令：
"c:\mysql-8.0.11-winx64\bin\mysqld" --remove

　　注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：

# 启动MySQL服务
net start mysql   注意：没有分号
 
# 关闭MySQL服务
net stop mysql

2|2linux版本

一、安装yum repo

 

　　1、由于centos的yum源中没有mysql，需要到mysql的官网中下载yuum repo配置文件。

　　https://dev.mysql.com/downloads/repo/yum/

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

　　2、然后进行安装

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

　　执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo mysql-community-source.repo

二、使用yum命令即可完成安装

　　注意：必须进入到 /etc/yum.repos.d/目录后再执行以下脚本

　　1、安装命令

yum install mysql-server

报错

　

 

 

大致意思就是MySQL的GPG升级了，需要更新，如果是新安装的MySQL，执行以下脚本即可：

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

　2、启动mysql

systemctl start mysqld #启动MySQL

　　3、获取安装时的临时密码

grep 'temporary password' /var/log/mysqld.log

　　4、如果没有获取到临时密码，先删除原来安装过的mysql残留的数据

rm -rf /var/lib/mysql

　　再次启动

systemctl start mysqld #启动MySQL

三、登录

mysql -u root -p

四、修改密码

alter user'root'@'%' IDENTIFIED BY 'MyNewPass@123'; 
一开始有可能是
alter user'root'@'localhost' IDENTIFIED BY 'MyNewPass@123'; 

 　　然后执行 mysql -uroot -p ，输入上面的到的密码进入，用该密码登录后，必须马上修改新的密码，不然会报如下错误：

mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

　　如果你想要设置一个简单的测试密码的话，比如设置为123456，会提示这个错误，报错的意思就是你的密码不符合要求

mysql> alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

　　这个其实与validate_password_policy的值有关。

　　validate_password_policy有以下取值：

　　默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

　　有时候，只是为了自己测试，不想密码设置得那么复杂，譬如说，我只想设置root的密码为123456。
　　必须修改两个全局参数：

　　首先，修改validate_password_policy参数的值

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

　　validate_password_length(密码长度)参数默认为8，我们修改为1

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

　　完成之后再次执行修改密码语句即可成功

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

 5.7 版本的话，alter不生效，会报错

set password for 'root'@'localhost' = Password('D90');

五、非localhost远程登录

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
或者
update user set Host='%' where User='root';  

最后注意刷新

flush privileges;

六、赋予所有权限

grant all privileges on *.* TO '用户名'@'IP'flush privileges

还不行的话检查服务器 端口是否开放：telnet ip port 

七、其他配置

　　1、设置安全选项：

mysql_secure_installation

　　2、关闭MySQL

systemctl stop mysqld 

　　3、重启MySQL

systemctl restart mysqld 

　　4、查看MySQL运行状态

systemctl status mysqld 

　　5、设置开机启动

systemctl enable mysqld 

　　6、关闭开机启动

systemctl disable mysqld

7、配置默认编码为utf8：

vi /etc/my.cnf 
#添加 
[mysqld] 
character_set_server=utf8 init_connect='SET NAMES utf8'

其他默认配置文件路径： 

配置文件：
/etc/my.cnf 

日志文件：
/var/log//var/log/mysqld.log 

服务启动脚本：
/usr/lib/systemd/system/mysqld.service socket

文件：
/var/run/mysqld/mysqld.pid

-

3|0三、数据库操作

3|11、显示数据库

show databases;

默认数据库：
　　mysql - 用户权限相关数据
　　test - 用于用户测试数据
　　information_schema - MySQL本身架构相关数据

3|22、创建数据库

create database 数据库名;

create database test;

# utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utfmb4 COLLATE utf8mb4_general_ci;
 
# gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

　create database test:创建一个名为“test”的数据库；

　default charset utf8：设置数据库的字符集编码默认为utf8mb4，是utf8mb4而不是utf-8;

　collate utf8_general_ci：数据库的校验规则，ci是case insensitive的缩写，意思是大小写不敏感；相对的是cs，即case sensitive，大小写敏感；还有一种是utf8_bin，是将字符串中的每一个字符用二进制数据存储，区分大小写。

utf8和utf8mb4的区别

　　MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

　　那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等(utf8的缺点)。

3|33、删除数据库

drop database <数据库名>;

3|44、使用数据库

use 数据库名字;

　　功能就是切换到那个数据库。之后再操作，比如显示当前使用的数据库中所有表：show tables；显示的就是这个数据库中的表格。

3|55、用户管理

用户名在mysql数据库中的user表，中有user列、host列是主机，比如localhost或者IP。

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')        在8版本中，password函数废弃。（据说在5.8就已经废弃。本人没验证过）请使用以下方法更改。　　alter user'root'@'%' IDENTIFIED BY 'MyNewPass@123'; 
  
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

　　如果本地用户，IP地址处填写 “ localhost ” 。

显示用户：（用 root 权限）

select user, host, password from mysql.user;

3|66、授权管理

show grants for '用户'@'IP地址'                  -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

            all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用

        对于目标数据库以及内部其他：
            数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库

            用户名@IP地址         用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)

            grant all privileges on db1.tb1 TO '用户名'@'IP'

            grant select on db1.* TO '用户名'@'IP'

            grant select,insert on *.* TO '用户名'@'IP'

            revoke select on db1.tb1 from '用户名'@'IP'

 特殊的：

flush privileges，将数据读取到内存中，从而立即生效。

# 启动免授权服务端
mysqld --skip-grant-tables

# 客户端
mysql -u root -p

# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;

4|0四、数据表基本

4|11、创建表

create table tb1(nid int,name varchar(20));desc 表名；查看表中所有的列describe 表名；show create table tb1;查看这个表示怎么创建的。show create table tb1 \G; 当命令窗口太小的时候，加入\G可以正常显示

create table 表名(
    列名  类型  是否可以为空，
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4# 为什么要用innodb引擎，因为有些操作，比如A少1000元，B多1000元，是两步操作，但是如果中途断了操作，这钱就没了。需要把两个合起来，叫做原子，任意一个失败就回滚。innodb支持事物，myisam不支持# 简单来说innodb支持事务和原子性操作。

        是否可空，null表示空，非字符串
            not null    - 不可空
            null        - 可空

        默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )

 -

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
     create table tb1(
         nid int not null auto_increment primary key,
         num int null
     )
     或
     create table tb1(
         nid int not null auto_increment,
         num int null,
         index(nid)
     )
简单地自增，alter table tb1 AUTO_INCREMENT=20; 这样就把主键自增改成20了。

注意：1、对于自增列，必须是索引（含主键）。
      2、对于自增可以设置步长和起始值

MySQL的步长和SQL Server的步长的比较。
SQL Server的步长是基于表级别的就是一个表设置好的之后，其他所有有权限操作这个表的用户来操作，步长都是设置的数字。

MySQL是基于会话级的。一个登陆就是一个会话。当登陆后设置完步长后，那么这个会话中对于所有的表的操作都是设置的步长。且其他会话可以设置不一样的步长。

show session variables like 'auto_inc%';
这句话可以查看步长，有auto_increment_increment，这个就是步长。auto_increment_offset是起初位置

以下是设置会话级的步长和起始值
set session auto_increment_increment=2;
set session auto_increment_offset=10;

以下是全局的。就是多个会话都会默认成设置值。（没事最好别用）
show global  variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;

-

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
create table tb1(
        nid int not null auto_increment primary key,
        num int null
        )
或
create table tb1(
        nid int not null,
        num int not null,
        primary key(nid,num)
    )  

当多个主键的时候，外键才能一次性设置多个。
    constraint fk_cc foreign key (color_id，two) references color(nid,two)
color 的 nid 和 two 都必须是主键

 -

外键，一个特殊的索引，只能是指定内容(外键类型一定要一样)
create table color(
    nid int not null primary key,
    name char(16) not null
);

create table fruit(
    nid int not null primary key,
    smt char(32) null ,
    color_id int not null,
    constraint fk_cc foreign key (color_id) references color(nid)   # color_id和color.nid类型一定要一样，比如都是bigint
);
外键可以多个
    constraint fk_one foreign key (color_id1) references color1(nid)
    constraint fk_two foreign key (color_id2) references color2(nid)

普通索引

1、创建索引

CREATE INDEX indexName ON mytable(username(length)); 
CREATE KEY indexName ON mytable(username(length));    (KEY比较常用）

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2、修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName);
ALTER table tableName ADD KEY indexName(columnName)

3、创建表的时候指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX `indexName` (`username(length)`)  
);  

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
KEY `indexName` (`username(length)`)  
);  

4、删除

ALTER table mytable drop key indexName;

5、查看

 

SHOW INDEX FROM `mytable`

 

唯一索引（主键不能为空，唯一索引可以为空）

1、创建索引

CREATE UNIQUE INDEX `indexName` ON mytable(`username(length)`) ;
CREATE UNIQUE KEY `indexName` ON mytable(`username(length)`) ;

 

2、修改表结构（添加索引）

ALTER table mytable ADD UNIQUE `indexName` (`username(length)`);
ALTER table mytable ADD UNIQUE `indexName` (`username(length)`);

3、创建表的时候指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE `indexName` (`username(length)`)  
);  

这样，nun里面的值不能重复。
create table t1(
    id int ...,
    num int,
    xx int,
    unique uq1 (num,xx)
)
这样写，num和xx联合唯一

-

4|22、删除表

drop table 表名

4|33、清空表

delete from 表名
truncate table 表名  连同主键的自增编号也一起归零

4|44、修改表

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop 列名            --alter table 表名 drop column 列名；
修改列： 
        alter table 表名 modify 列名 类型;  -- 只修改 类型  alter table 表名 modify column 列名 类型;
        alter table 表名 change 原列名 新列名 类型; -- 同时修改 列名，类型
  
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
外键是用来约束的，一张表里的数据必须是另一张表里包含的数据，如果不在另一张表上，报错。
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
  
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

　mysql每新建一个连接，就会自动生成一些库，其中一个库叫做information_schema，这个库里有张表叫做table_constraints。这张表记录了连接里的所有约束信息。比如是否是主键约束，外键约束，唯一性约束，约束名，表名等信息。还有张表叫key_column_usage，他记录了每个表字段的信息，包括了这个字段是否是外键，对应那张表的主键。也就是说，想查看是否有某外键约束，可以去table_constraint查，如果想查看该外键的具体信息，就去key_column_usage表查，比如你想查homework表参考user表的外键信息，可以用以下sql语句查询。

　　select * from information_schema.key_column_usage where  REFERENCED_TABLE_NAME='teacher' and table_name='course';

　　外键的名字 constraint_name - fk_从表_主表

　　从表名字 table_name 

　　从表添加外键的列 column_name 

　　主表的名字 referenced_table_name

　　主表的主键字段 referenced_column_name

　　

SELECT
　　table_name,
　　column_name,
　　referenced_table_name,
　　referenced_column_name,
　　constraint_name
FROM
　　information_schema.key_column_usage
WHERE
　　REFERENCED_TABLE_NAME = 'teacher'
　　AND table_name = 'course';

外键的变种

1. 外键的变种
            
===> 一对一(一个职员一个部门) 或者 一对多(比如一个用户有多个博客文章，关联的时候就是一对多)
        
create table userinfo1(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8mb4

create table admin(
    id int not null auto_increment primary key,
    username varchar(64) not null,
    password VARCHAR(64) not null,
    user_id int not null,
    unique uq_u1 (user_id),
    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8mb4
        
        
===》多对多(用户表、主机表、用户主机关系表)     


create table userinfo2(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8mb4

create table host(
    id int auto_increment primary key,
    hostname char(64)
)engine=innodb default charset=utf8mb4

create table user2host(
    id int auto_increment primary key,
    userid int not null,
    hostid int not null,
    unique uq_user_host (userid,hostid),
    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8mb4

主键、外键和索引的区别？

	主键	外键	索引
定义	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键, 外键可以有重复的, 可以是空值	该字段没有重复值，但可以有一个空值
作用	用来保证数据完整性	用来和其他表建立联系用的	是提高查询排序的速度
个数	主键只能有一个	一个表可以有多个外键	一个表可以有多个索引

外键的作用：

　　保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。

阻止执行：

1、从表插入新行，其外键值不是主表的主键值便阻止插入；

2、从表修改外键值，新值不是主表的主键值便阻止修改；

3、主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；

4、主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行：

1、主表删除行，连带从表的相关行一起删除；

2、主表修改主键值，连带从表相关行的外键值一起修改。两种方法提供给用户选择。无论选取哪种方法，从表里都不会有多余行。从另一个角度理解，用拒绝同一事物在从表中的标志与主表不一致来实现与主表中的标志一致。

FOREIGN KEY(id)REFERENCES tabley(id) on delete cascade on update cascade   
        意思是从表会跟随主表的改变而改变。

 

扩展资料：

外键使用原则：

1、 为关联字段创建外键。

2、 所有的键都必须唯一。

3、避免使用复合键。

4、外键总是关联唯一的键字段。

4|55、基本数据类型

 MySQL的数据类型大致分为：数值、时间和字符串。

 

CREATE TABLE `tables` (
      `id` int(11) DEFAULT NULL,
      `tid` int(10) DEFAULT NULL,
      `t1` int(10) unsigned zerofill DEFAULT NULL

      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
--unsigned 标识有无符号，m表示长度，在zerofill没有填写的情况下，填不填都一样。填了zerofill则不满足长度的会用0填充。


bit[(M)]
    二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

tinyint[(m)] [unsigned] [zerofill]

    小整数，数据类型用于保存一些范围的整数数值范围：
    有符号：
        -128 ～ 127.
    无符号：
        0 ～ 255

    特别的： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

    整数，数据类型用于保存一些范围的整数数值范围：
        有符号：
            -2147483648 ～ 2147483647
        无符号：
            0 ～ 4294967295

    特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002

bigint[(m)][unsigned][zerofill]
    大整数，数据类型用于保存一些范围的整数数值范围：
        有符号：
            -9223372036854775808 ～ 9223372036854775807
        无符号：
            0  ～  18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]
    准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

    特别的：对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储。

float[(M,D)] [UNSIGNED] [ZEROFILL]
    单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
        无符号：
            -3.402823466E+38 to -1.175494351E-38,
            0
            1.175494351E-38 to 3.402823466E+38
        有符号：
            0
            1.175494351E-38 to 3.402823466E+38

    **** 数值越大，越不准确 ****

double[(M,D)] [UNSIGNED] [ZEROFILL]
    双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

        无符号：
            -1.7976931348623157E+308 to -2.2250738585072014E-308
            0
            2.2250738585072014E-308 to 1.7976931348623157E+308
        有符号：
            0
            2.2250738585072014E-308 to 1.7976931348623157E+308
    **** 数值越大，越不准确 ****


char (m)
    char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
    PS: 即使数据小于m长度，也会占用m长度
varchar(m)
    varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

    注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

text
    text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

mediumtext
    A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext
    A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

如果longtext不够的话，就不应该用数据库存储，应该用文件存储。然后数据里里面存储文件的url。

enum
    枚举类型，
    An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
    示例：
        CREATE TABLE shirts (
            name VARCHAR(40),
            size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
        );
        INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

set
    集合类型
    A SET column can have a maximum of 64 distinct members.
    示例：
        CREATE TABLE myset (name char(10),col SET('a', 'b', 'c', 'd'));
        INSERT INTO myset (name，col) VALUES ('dy1','a,d'), ('dy2','d,a'), ('dy3','a,d,a'), ('dy4','a,d,d'), ('dy5','d,a,d');

DATE
    YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
    HH:MM:SS（'-838:59:59'/'838:59:59'）

YEAR
    YYYY（1901/2155）

DATETIME

    YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

    YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

 

二进制数据：TinyBlob、Blob、MediumBlob、LongBlob

如果数据太大，就直接存文件，然后在数据库里保存文件的url

更多参考：

• http://www.runoob.com/mysql/mysql-data-types.html
• http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

 

 

那么，这个M值到底代表什么意思呢？

到这里，我们已经可以发现，M值即使设置为1，它也可以存入字符长度大于1的值，那么，如果存入的字符长度小于1会怎么样？我们来试一试：

先将id1的类型更改为int(2)，然后插入数据id1=1：

mysql> ALTER TABLE test Modify id1 int(2);
mysql> INSERT INTO test(id1) values(1);//运行成功，说明值1已经插入到test表中

我们查询一下表中的数据，看看结果具体如何：

 

mysql> SELECT * FROM test;
+------+
|  id1 |
+------+
|   1  |
+------+

接下来，我们再修改一下id1的填充数据类型zerofill（表示用0填充），这里先知道如何操作即可，我们再从结果得出结论：

mysql> ALTER TABLE test MODIFY id1 int(2) zerofill;
mysql> SELECT * FROM test;
+------+
|  id1 |
+------+
|  01  |
+------+

现在是不是有些清楚了。我们设置的M值是2，没有设置zerofill用0填充时，对于操作没有任何影响，而设置了zerofill后，我们可以清楚地看到值1字符数不足M值，左前位置补0。我们也可以将M值设置成别的大小进行多次测试，这里就不进行测试了。

需要强调的是，不同的数据类型中的M值意义是不一样的，我们这里仅讨论整型中的M值。

从上面我们可以得到如下的结论：

1、整数型的数值类型已经限制了取值范围，有符号整型和无符号整型都有，而M值并不代表可以存储的数值字符长度，它代表的是数据在显示时显示的最小长度；

2、当存储的字符长度超过M值时，没有任何的影响，只要不超过数值类型限制的范围；

3、当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。

 

总结：int(11)，tinyint(1)，bigint(20)，后面的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。

所以我们在设计mysql数据库时，建表时，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

所以，就用这些默认的显示长度就可以了。不用再去自己填长度，比如搞个int(10)、tinyint(1)之类的，基本没用。而且导致表的字段类型多样化。

 

 

5|0五、表内容操作

5|11、增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表1 (列名,列名...) select (列名,列名...) from 表2  # 把表2中的某些列写进表1中的某些列insert into 表 (列名,列名...) select (列名,列名...) from 表 where nid>2;

5|22、删

delete from 表
delete from 表 where id＝1 and name＝'alex'

5|33、改

update 表 set name ＝ 'alex' where id>1

5|44、查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1  # 临时显示的时候改成gg。 下一次依旧是gender

5|55、其他

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
 
c、分页（限制）
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
    取后十条数据
    select * from 表 order by 列 desc limit 10;


e、分组 select num from 表 group by num 

select num,nid from 表 group by num,nid # selec之后的是显示的。 group之后的，先按num分组，如果num相同，则再按nid分组，如果nid不同，则num相同也不分组。 

select num,nid from 表 where nid > 10 group by num,nid order by nid desc 

select num,nid,count(*),sum(score),max(score),min(score) avg(score) from 表 group by num,nid 

select num,nid as a,count(*) as b,sum(score) as c,max(score) as d...
select num from 表 group by num having max(id) > 10 对聚合条件进行筛选得用having 不能用where
 
    特别的：group by 必须在where之后，order by之前
 
f、连表（优先使用left。效率高）
　　无对应关系 结果为笛卡尔积
　　select * from A,B;  # 结果为笛卡尔积

    有对应关系
    select A.num, A.name, B.name from A,B Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name from A inner join B on A.nid = B.nid
    用了inner之后不会出现left之中的NULL

    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name from A left join B on A.nid = B.nid
　　left outer join 是全称，但是一般left join就够了
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name from A right join B on A.nid = B.nid

　　 多张表连表
　　select A.num, A.name, B.name from A 
　　left join B on A.nid = B.nid
　　left join C on B.nid = C.nid
 g. 临时表
select * from (select * from score where num > 60) as B;

 

5|66、联合

联合（组合）
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

下面是选自 "Websites" 表的数据：
mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+
 　　下面是 "apps" APP 的数据：

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
　　下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

带有 WHERE 的 SQL UNION ALL

5|77、数据库备份

导出现有数据库数据：

mysqldump -u 用户名 -p密码 数据库名称 > 导出文件路径      # 结构+数据
mysqldump -u 用户名 -d -p密码 数据库名称 > 导出文件路径      # 结构

导入现有数据库数据：

mysqldump -u 用户名 -p 密码 数据库名称 < 文件路径

5|88、备注

#创建表的时候写注释
CREATE TABLE userinfo(
    id INT COMMENT '编号',
    uname VARCHAR(40) COMMENT '用户名',
    address VARCHAR(120) COMMENT '家庭住址',
    hobby VARCHAR(200) COMMENT '爱好'
 
)COMMENT = '用户信息表';
 
#修改表的注释
ALTER TABLE userinfo COMMENT '用户信息资料表';
 
#修改字段的注释，注意：字段名和字段类型照写就行
ALTER TABLE userinfo MODIFY COLUMN uname VARCHAR(40) COMMENT '姓名';
 
#查看表注释的方法，在生成的SQL语句中看
SHOW CREATE TABLE userinfo;
#在元数据的表里面看
USE information_schema;
SELECT * FROM TABLES WHERE TABLE_SCHEMA='shoppingcart' AND TABLE_NAME='userinfo';
 
#查看字段注释的方法
SHOW FULL COLUMNS FROM userinfo;
#在元数据的表里面看
SELECT * FROM COLUMNS WHERE TABLE_SCHEMA='shoppingcart' AND TABLE_NAME='userinfo';

5|99、auto_increment

1.一般数据表的id都是设置成auto_increment的，所以当插入一条记录后，可以使用下面的命令来获取最新插入记录的id值

select last_insert_id();

   注意：1. 必须是在使用Insert语句后，紧接着使用select last_insert_id()才有效，在没有使用过Insert语句的情况下，查询返回的结果为0;

            2.如果在同一条Insert语句插入多条记录，返回的结果是第一条记录对于的id

insert into school.student
(name, age) values
('s1', 18),
('s2', 18),
('s3', 28),
('s4', 19),
('s5', 18);


返回的是 s1 的id

2. 为什么不直接使用

select max(id) from tableName;

    因为：如果手动删除了最新的数据，使用 max(id)查询的结果是当前剩下数据中最大的记录，

    而新插入数据则不一定从这个数字开始计数。

3. 所以为了准确的获取下一条插入记录的id，应该查询的是auto_increment, 对应的SQL语句如下：

SELECT auto_increment FROM information_schema.tables where table_schema="dbName" and table_name="tableName";

注意：auto_increment返回的是下一条插入记录的id值，而不是当前的最大id值

4、修改 auto_increment

alter table tb1 AUTO_INCREMENT=20; 

6|0一些归纳

针对数据库级别的操作：
    1.显示数据库  show databases;
    2.创建数据库  create database 数据库名字 default charset utf8 collate utf8_general_ci;
    3.删除数据库  drop database 数据库名字;
    4.使用数据库  use 数据库名字;

针对数据表级别的操作:
    1.显示数据表 利用 use 数据库名字; 使用数据库，show tables;
    2.创建数据表 create table 数据表名字(
                    nid int not null auto_increment primary key,
                    name varchar(20)
                )engine=innodb default charset=utf8;
    3.删除表 drop table 表名;
    4.清空表 delete from 表名;
            truncate table 表名;   这种清空方式连同主键的自增编号也一起归零
    5.添加列 alter table 表名 add 列名 类型;
    6.删除列 alter table 表名 drop column 列名;
    7.修改列 alter table 表名 modify column 列名 类型; -- 只修改类型
            alter table 表名 change 原列名 新列名 类型; -- 同时修改 列名，类型;
    8.添加主键： alter table 表名 add primary key(列名);
    9.删除主键： alter table 表名 drop primary key;
                alter table 表名  modify  列名 int, drop primary key;
    10.添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
    11.删除外键：alter table 表名 drop foreign key 外键名称
    12.修改默认值：alter table 表名 alter i set default 1000;
    13.删除默认值：alter table 表名 alter i drop default;

针对表内容的操作：
    1.增    insert into 表 (列名,列名...) values (值,值,值...);
            insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);
            insert into 表1 (列名,列名...) select (列名,列名...) from 表2;   # 把表2中的某些列写进表1中的某些列
            insert into 表 (列名,列名...) select (列名,列名...) from 表 where nid>2;
    2.清空  delete from 表 where id＝1 and name＝'alex'；　　　　　　　truncate table 表;
    3.改    update 表 set name ＝ 'alex' where id>1；
    4.查    select * from 表; 
            select * from 表 where id > 1;
            select nid,name,gender as gg from 表 where id > 1;    # 临时显示的时候改成gg。 下一次依旧是gender

导出现有数据库数据：

• mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据
• mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构 

导入现有数据库数据：

• mysqldump -uroot -p密码  数据库名称 < 文件路径  

7|0针对navicat无法连接问题。

原因是因为新版 MySQL8 默认采用了caching_sha2_password验证方式。和旧版不一样，和navicat默认的验证方式也不一样。

1、进入CMD命令符窗口（win+R）

2、进入mysql安装目录的bin路径下运行mysql -uroot -p命令进行登录（密码是安装mysql时设置的密码）

3、输入ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';（root是用户名，localhost是ip地址127.0.0.1都是特指本机，mysql_native_password是旧的密码验证机制，password是密码，最后别忘了分号；）

4、进入Navicat测试连接

 Navicat 快捷键

https://www.cnblogs.com/457248499-qq-com/p/7449492.html

1、ctrl + Q ，新建查询

2、ctrl + /  ，注释行

3、shift + ctrl + /  ，取消注释行

4、CTRL+R ，运行

5、SHIFT+CTRL+R，运行已经选择的

__EOF__

本文作者：😎
本文链接：https://www.cnblogs.com/dongye95/p/9110234.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！