MySql 笔记

进入服务端命令:

 windows :进入安装目录 bin 目录下(设置了环境变量就不用cd到目录下) 

启动 :        net start mysql

停止:         net  stop mysql

Mac: 进入安装目录 bin 目录下(设置了环境变量就不用cd到目录下) 

启动:        mysql.server start

停止:        mysql.server stop

javaGHui bin % mysql.server start
Starting MySQL
... SUCCESS! 
javaGHui bin % mysql.server stop
Shutting down MySQL
. SUCCESS! 
javaGHui bin % 

进入客户端命令:

 进入安装目录 bin 目录下(设置了环境变量就不用cd到目录下) 

        mysql -uroot -p        输入密码方式

        mysql -uroot -proot

      mysql -hlocalhost -P3306 -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.29 Homebrew

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 

退出命令:     

          \q        exit;        quit;

创建数据库命令:

create database mydatabase;

设置字符编码        create database mydatabase2 charset utf8;        

成功提示:        Query OK, 1 row affected, 1 warning (0.02 sec)

数据库db:

库选项:

字符集: charset        字符集

校对集: collate        校对集

显示数据库:

show databases;

 like  _:        匹配某一个字符

like  %:        匹配任意个字符

show databases like 'm_data%';

show databases like 'my%';

显示创建数据库语句:

show create database 数据库名字;

show create database mydatabase;

mysql> show create database mydatabase;
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                                                      |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| mydatabase | CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

进入数据库db:

use 数据库;        use mydatabase;

mysql> use mydatabase;
Database changed
mysql> 

修改数据库字符集:

alter database 数据库名字 charset = 字符集;

alter database 数据库名字 charset 字符集;

alter database mydatabase charset gbk;

mysql> alter database mydatabase charset gbk;
Query OK, 1 row affected (0.02 sec)

mysql> 

删除数据库:

drop database 数据库名;

mysql> drop database mydatabase2;
Query OK, 0 rows affected (0.17 sec)

mysql> 

表table:

显示表:

show tables;

匹配表 :        show tables like 'm_table%';

创建数据表:table

  create table class(name varchar(30));

创建表时要需要进入某个数据库中 可以用 use           use mydatabase;

或者:  数据库.表      create table mydatabase.class(name varchar(30));

mysql> create table mydatabase.class(name varchar(30));
Query OK, 0 rows affected (0.10 sec)

mysql> 

表选项:

engine:存储引擎 

        MyisAM引擎：（非聚集）索引文件和数据文件是分离的

        (默认)InnoDB引擎：（聚集）索引文件和数据文件放在一起

charset:字符集,只对当前自己表有效,(级别高于数据库)

collate: 校对集

create table student(name varchar(30))engine myisam charset utf8;

create table student(name varchar(30))engine=myisam charset=utf8;

mysql> create table student(name varchar(30))engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)

设置表属性:

alter table 表名 表选项 [=] 值        |        alter table 表名 表选项 值  

mysql> alter table class charset utf8;
Query OK, 0 rows affected, 1 warning (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 1

复制表结构:(不会复制内容)

create table 新的表名字 like 要复制的表名;

还可以使用        create table 新的表名字 like 数据库.要复制的表名;

create table studenttmp like mydatabase.student;

mysql> create table studenttmp like mydatabase.student;
Query OK, 0 rows affected (0.04 sec)

删除表:

drop table 表名;

显示表结构:

describe 表名;

desc 表名;

show columns from 表名;        3中方法 效果一样

mysql> describe class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.04 sec)

///
Field : 字段名字
Type  : 字段类型
Null  : 值是否允许为空
Key   : 索引
Default : 默认值    NULL 表示默认值为NULL
Extra : 额外的属性

显示表创建语句

show create table 表名;

mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

结束符排列:

;         \g

\G

mysql> show create table class\G
*************************** 1. row ***************************
       Table: class
Create Table: CREATE TABLE `class` (
  `name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

修改表结构:

修改表名:

rename table 表名 to 新名字;

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| student              |
+----------------------+
2 rows in set (0.02 sec)

mysql> rename table class to my_class;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| my_class             |
| student              |
+----------------------+
2 rows in set (0.01 sec)

修改表选项:

alter table 表名 表选项 [=] 新值

新增字段: 

      alter table 表名 add [column] 新字段名 列类型[列属性] [位置 first/after 字段名]

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> alter table my_class add column age int;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

字段 默认是添加到最后面

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table my_class add id int first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

first: 最前面添加

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table my_class add sex char(3) after name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
after 字段名 : 在哪个字段的后面添加

修改字段名:
        alter table 表名 change 旧字段名 新字段名 字段类型 [列属性] [新位置  ]

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table my_class change age nj int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| nj    | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段类型(属性):

        alter table 表名 modify 字段名 新类型 [新属性] [新位置]

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| nj    | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table my_class modify name varchar(40);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| nj    | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除表字段:

        alter table 表名 drop 字段名;

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
| nj    | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table my_class drop nj;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| sex   | char(3)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除表结构:

        drop table 表名 [, 表名...] 可以删除多个表

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| my_class             |
| student              |
+----------------------+
2 rows in set (0.01 sec)

mysql> drop table  my_class,student;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
Empty set (0.00 sec)

表字段类型(列类型):

整数类型:

tinyint        : 迷你整形,采用一个字节保存,1个字节 = 8位  0 ~ 255, 实际范围:        -128 ~ 127

smallint      : 小整形, 2个字节

mediumint  : 中整形,3个字节;

int               :        4个字节;

bigint          :    8个字节

mysql> create table my_int(
    -> int_1 tinyint,
    -> int_2 smallint,
    -> int_3 mediumint,
    -> int_4 int,
    -> int_5 bigint)charset utf8;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> desc my_int;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| int_1 | tinyint   | YES  |     | NULL    |       |
| int_2 | smallint  | YES  |     | NULL    |       |
| int_3 | mediumint | YES  |     | NULL    |       |
| int_4 | int       | YES  |     | NULL    |       |
| int_5 | bigint    | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
--插入数据
mysql> insert into my_int values(100,10000,100000,10000000,1000000000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_int;
+-------+-------+--------+----------+------------+
| int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+--------+----------+------------+
|   100 | 10000 | 100000 | 10000000 | 1000000000 |
+-------+-------+--------+----------+------------+
1 row in set (0.00 sec)
-- 插入数据 超出范围
mysql> insert into my_int values(255,255,255,255,255);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from my_int;
+-------+-------+--------+----------+------------+
| int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+--------+----------+------------+
|   100 | 10000 | 100000 | 10000000 | 1000000000 |
|   127 |   255 |    255 |      255 |        255 |
+-------+-------+--------+----------+------------+
2 rows in set (0.00 sec)
-- 插入数据 
mysql> insert into my_int values(-255,-255,-255,-255,-255);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from my_int;
+-------+-------+--------+----------+------------+
| int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+--------+----------+------------+
|   100 | 10000 | 100000 | 10000000 | 1000000000 |
|   127 |   255 |    255 |      255 |        255 |
|  -128 |  -255 |   -255 |     -255 |       -255 |
+-------+-------+--------+----------+------------+

无符号标识设定:

即:没有负数(只有正数);

在列类型 之后加上一个 unsigned

mysql> alter table my_int add int_6 tinyint unsigned first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_int;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| int_6 | tinyint unsigned | YES  |     | NULL    |       |
| int_1 | tinyint          | YES  |     | NULL    |       |
| int_2 | smallint         | YES  |     | NULL    |       |
| int_3 | mediumint        | YES  |     | NULL    |       |
| int_4 | int              | YES  |     | NULL    |       |
| int_5 | bigint           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

--插入数据
mysql> insert into my_int values(255,100,255,255,255,255);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_int;
+-------+-------+-------+--------+----------+------------+
| int_6 | int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+-------+--------+----------+------------+
|  NULL |   100 | 10000 | 100000 | 10000000 | 1000000000 |
|  NULL |   127 |   255 |    255 |      255 |        255 |
|  NULL |  -128 |  -255 |   -255 |     -255 |       -255 |
|   255 |   100 |   255 |    255 |      255 |        255 |
+-------+-------+-------+--------+----------+------------+
4 rows in set (0.00 sec)
-- 插入负数
mysql> insert into my_int values(-255,100,255,255,255,255);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from my_int;
+-------+-------+-------+--------+----------+------------+
| int_6 | int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+-------+--------+----------+------------+
|  NULL |   100 | 10000 | 100000 | 10000000 | 1000000000 |
|  NULL |   127 |   255 |    255 |      255 |        255 |
|  NULL |  -128 |  -255 |   -255 |     -255 |       -255 |
|   255 |   100 |   255 |    255 |      255 |        255 |
|     0 |   100 |   255 |    255 |      255 |        255 |
+-------+-------+-------+--------+----------+------------+
5 rows in set (0.00 sec)

zerofill 

满足指定长度,从左侧开始填充0,(左侧不会改变数值大小),所已负数不能使用zerofill,

超出字段类型范围,显示类型最大数值,不会不零

一旦使用 zerofill 就相当于 确认该字段 为 unsigned

mysql> alter table my_int add int_7 tinyint zerofill first;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc my_int;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| int_7 | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
| int_6 | tinyint unsigned             | YES  |     | NULL    |       |
| int_1 | tinyint                      | YES  |     | NULL    |       |
| int_2 | smallint                     | YES  |     | NULL    |       |
| int_3 | mediumint                    | YES  |     | NULL    |       |
| int_4 | int                          | YES  |     | NULL    |       |
| int_5 | bigint                       | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


--tinyint(3)  3位 表示只能是正数

--填充数据
mysql> insert into my_int values(1,1,1,1,1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_int;
+-------+-------+-------+-------+--------+----------+------------+
| int_7 | int_6 | int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+-------+-------+--------+----------+------------+
|  NULL |  NULL |   100 | 10000 | 100000 | 10000000 | 1000000000 |
|  NULL |  NULL |   127 |   255 |    255 |      255 |        255 |
|  NULL |  NULL |  -128 |  -255 |   -255 |     -255 |       -255 |
|  NULL |   255 |   100 |   255 |    255 |      255 |        255 |
|  NULL |     0 |   100 |   255 |    255 |      255 |        255 |
|  NULL |     0 |   100 |   255 |    255 |      255 |        255 |
|   001 |     1 |     1 |     1 |      1 |        1 |          1 |
+-------+-------+-------+-------+--------+----------+------------+
7 rows in set (0.00 sec)

--超出范围
mysql> insert into my_int values(2555,1,1,1,1,1,1);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from my_int;
+-------+-------+-------+-------+--------+----------+------------+
| int_7 | int_6 | int_1 | int_2 | int_3  | int_4    | int_5      |
+-------+-------+-------+-------+--------+----------+------------+
|  NULL |  NULL |   100 | 10000 | 100000 | 10000000 | 1000000000 |
|  NULL |  NULL |   127 |   255 |    255 |      255 |        255 |
|  NULL |  NULL |  -128 |  -255 |   -255 |     -255 |       -255 |
|  NULL |   255 |   100 |   255 |    255 |      255 |        255 |
|  NULL |     0 |   100 |   255 |    255 |      255 |        255 |
|  NULL |     0 |   100 |   255 |    255 |      255 |        255 |
|   001 |     1 |     1 |     1 |      1 |        1 |          1 |
|   255 |     1 |     1 |     1 |      1 |        1 |          1 |
+-------+-------+-------+-------+--------+----------+------------+

小数类型:

又分两类 浮点型 和 定点型

浮点型

又称 精度类型 ,是一种可能丢失精确度,数据不那么准确,(尤其超出范围时)

浮点数之所以能欧存储较大的数值(不精确),原因是用存储数据的位来存储指数

float        :

单精度类型  4个字节  范围比整数大的多,大概是10^38, 只能保证大概7位左右精度(7位数以内是比较准确的,超过7位数就不准确了)

注: 如果数据精度丢失,那么浮点数是按照 四舍五入方式计算的

用法:

float 表示不指定小数的浮点数;

float(M,D) 表示一共存储M个有效数字,其中小数部分占D位

float(10,2) : 整数部分,8位,小数部分2位 ,共10位;

mysql> create table my_float(
    -> f1 float,
    -> f2 float(10,2))charset utf8;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> desc my_float;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f1    | float       | YES  |     | NULL    |       |
| f2    | float(10,2) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

数据不精确,精度大概在7左右:

--插入数据
mysql> insert into my_float values(123.123,12345678.90);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_float;
+---------+-------------+
| f1      | f2          |
+---------+-------------+
| 123.123 | 12345679.00 |
+---------+-------------+
1 row in set (0.00 sec)

超出限定大小:

--超出指定位数
mysql> insert into my_float values(123.1234567,123456789.00);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from my_float;
+---------+--------------+
| f1      | f2           |
+---------+--------------+
| 123.123 |  12345679.00 |
| 123.123 | 100000000.00 |
+---------+--------------+
2 rows in set (0.00 sec)

可以使用科学计数法来存储数据

mysql> insert into my_float values(123.123,10e5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_float;
+---------+--------------+
| f1      | f2           |
+---------+--------------+
| 123.123 |  12345679.00 |
| 123.123 | 100000000.00 |
| 123.123 |   1000000.00 |
+---------+--------------+
3 rows in set (0.00 sec)

double    :

双精度 8个字节 ,表示范围 10^308,但精度也只有 15位左右

定点数:

能够保证数据精确的小数 (小数部分可能不精确,超出长度四舍五入,)整数部分一定精确;

decimal   :

系统自动根据存储的数据来分配存储空间,每大概9个数就会分配4个字节存储数据,同时整数和小数部分都是分开的;

decimal(M,D) : M 表示总长度,最大不能超过65,D 代表小数长度,最大不能超过30

mysql> create table my_decimal(
    -> fl float(10,2),
    -> d1 decimal(10,2))
    -> charset utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> desc my_decimal;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| fl    | float(10,2)   | YES  |     | NULL    |       |
| d1    | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 插入数据 对比
mysql> insert into my_decimal values(12345678.90,12345678.90);
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_decimal;
+-------------+-------------+
| fl          | d1          |
+-------------+-------------+
| 12345679.00 | 12345678.90 |
+-------------+-------------+
1 row in set (0.00 sec)

--插入最大数
mysql> insert into my_decimal values(99999999.99,99999999.99);
Query OK, 1 row affected (0.02 sec)

mysql> select * from my_decimal;
+--------------+-------------+
| fl           | d1          |
+--------------+-------------+
|  12345679.00 | 12345678.90 |
| 100000000.00 | 99999999.99 |
+--------------+-------------+
2 rows in set (0.00 sec)
-- 超出位数
mysql> insert into my_decimal values(99999999.99,99999999.999);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from my_decimal;
+--------------+-------------+
| fl           | d1          |
+--------------+-------------+
|  12345679.00 | 12345678.90 |
| 100000000.00 | 99999999.99 |
| 100000000.00 | 99999999.99 |
+--------------+-------------+
3 rows in set (0.00 sec)

时间日期类型

date

日期类型,使用3个字节存储数据,对应的格式为: YYYY-mm-dd

表示的范围: 从1000-01-01 到 9999-12-12, 初始值为 0000-00-00

time

 时间类型, 能够指定某个时间,3个字节存储,对应的格式为:  HH:ii:ss

表示的范围: -838:59:59 ~ 838:59:59 ,用来描述时间断,本质用来表示时间区间

可以在时间格式之前加一个空格,然后指定一个数字,可以为负数

系统会自动将该数字转换成 天数 * 24 小时 在加上后面的时间

mysql> insert into my_date values('2024-4-30','512:12:12','2024-4-30 12:12:12','2024-12-12 12:12:12',24);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_date;                                                   +------------+-----------+---------------------+---------------------+------+
| d1         | d2        | d3                  | d4                  | d5   |
+------------+-----------+---------------------+---------------------+------+
| 2024-04-30 | 12:12:12  | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
| 2024-04-30 | 512:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
+------------+-----------+---------------------+---------------------+------+
2 rows in set (0.00 sec)
-- 5 12:12:1
mysql> insert into my_date values('2024-4-30','5 12:12:12','2024-4-30 12:12:12','2024-12-12 12:12:12',24);
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_date;                                                   +------------+-----------+---------------------+---------------------+------+
| d1         | d2        | d3                  | d4                  | d5   |
+------------+-----------+---------------------+---------------------+------+
| 2024-04-30 | 12:12:12  | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
| 2024-04-30 | 512:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
| 2024-04-30 | 132:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
+------------+-----------+---------------------+---------------------+------+
3 rows in set (0.00 sec)

detetime

日期时间类型, 就是将前面的data 和 time 合并起来,表示的时间,8个字节 

对应的格式为: YYYY-mm-dd HH:ii:ss

表示的范围: 从1000-01-01 00:00:00 到 9999-12-12 23:59:59, 可以为0值,初始值为 0000-00-00 00:00:00 

timestamp

时间戳类型, 表示从格林威治时间开始,对应的格式为: YYYY-mm-dd HH:ii:ss

初始值为 0000-00-00 00:00:00;

year

年类型,一个字节, 能表示1900 ~ 2155,两种插入方式,0~99 和 4位具体年

两位数零界点 69 和 70 ,69 以下 系统时间为20+数字,70以上 系统时间为19+数字

--创建表
mysql> create table my_date(
    -> d1 date,
    -> d2 time,
    -> d3 datetime,
    -> d4 timestamp,
    -> d5 year)charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> desc my_date;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| d1    | date      | YES  |     | NULL    |       |
| d2    | time      | YES  |     | NULL    |       |
| d3    | datetime  | YES  |     | NULL    |       |
| d4    | timestamp | YES  |     | NULL    |       |
| d5    | year      | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
--插入数据
mysql> insert into my_date values('2024-4-30','12:12:12','2024-4-30 12:12:12','2024-12-12 12:12:12',90);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_date;
+------------+----------+---------------------+---------------------+------+
| d1         | d2       | d3                  | d4                  | d5   |
+------------+----------+---------------------+---------------------+------+
| 2024-04-30 | 12:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 1990 |
+------------+----------+---------------------+---------------------+------+
1 row in set (0.00 sec)

--插入数据 年 69 70
mysql> insert into my_date values('2024-4-30','12:12:12','2024-4-30 12:12:12','2024-12-12 12:12:12',24);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_date;                                                   +------------+----------+---------------------+---------------------+------+
| d1         | d2       | d3                  | d4                  | d5   |
+------------+----------+---------------------+---------------------+------+
| 2024-04-30 | 12:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 1990 |
| 2024-04-30 | 12:12:12 | 2024-04-30 12:12:12 | 2024-12-12 12:12:12 | 2024 |
+------------+----------+---------------------+---------------------+------+

字符串类型:

char

定长字符:指定长度之后,系统一定会分配指定的空间用于存储数据

基本语法:        char(L) ,L代表字符数,L长度为0 到255

varchar

变长字符:指点长度之后,系统会根据实际存储的数据来计算长度,分配合适的长度(数据没有超过长度)

基本语法:        char(L) ,L代表字符数,L长度为0 到65535

因为varchar 要记录数据长度()系统根据数据长度自动分配空间,所以每个varchar数据产生后,

系统都会在数据后面增加1~2 个字节的额外开销:是用来保存数据所占用的空间长度,

如果数据本身小于127个字符:额外开销一个字节;如果大于127个,就开销2个字节

char 和 varchar 对比:(utf ,一个字符占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区别:

1. char 一定会使用指定的空间,varchar是根据数据来定空间

2. char 的数据查询效率比 varchar高,varchar 是需要通过后面的记录数来计算

如果确定数据长度,使用char

如果不确定数据长度,使用varchar

如果数据长度超过了255 个字符,不论是否固定长度,都会使用 text,

text

文本类型: 本质上mysql 提供了两种文本类型

text: 存储普通字符文本

blob: 存储二进制文本(图片,文件),一般都不会使用bolo 来存储文件本身,通常是使用一个连接来指向对应的文件本身;

text : 系统中提供的四种 text:

tinytext : 系统使用一个字节来保存 ,实际能储存的数据为 2 ^ 8 +1

text : 使用两个字节保存 ,实际能储存的数据为 2 ^ 16 +2

mediumtext : 使用三个字节保存 ,实际能储存的数据为 2 ^ 24+3

longtext : 使用四个字节保存, ,实际能储存的数据为 2 ^ 32 +4

注:

1. 在存储文本时,不用刻意去选择text 类型,系统会自动选择

2. 在存储文本时,如果数据超过255 个字符,那么要选中text存储

enum(单选)

枚举类型: 在数据插入之前,先设定几个固定的数据,这几个数据就是最终的值

基本语法: enum(数据1,数据2, ...)

系统提供了1 到 2 个字节来存储枚举数据, 通过计算enum枚举的具体值来选择实际的存储空间,

如果数据值列表在255个字符以内,那么一个字节就够,如果超过255个字符,小于65535那么系统采用两个自己保存

mysql> create table my_enum(sex enum('男','女'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc my_enum;
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| sex   | enum('男','女')   | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into my_enum values('女');
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_enum values('男');
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_enum values('保密');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from my_enum;
+------+
| sex  |
+------+
| 女   |
| 男   |
|      |
+------+
3 rows in set (0.00 sec)

枚举存储原理:实际上字段上所存储的值并不是真正的字符串,而是字符串对应的下标,从1开始

enum(1=>'男',2=>'女')

特性:在mysql中系统是自动进行类型转换的:如果数据碰到 (加减乘除) +  -  *  / ,系统就会自动将数据转换成数值,而普通的字符串转换成数值为0

mysql> select sex + 0 from my_enum;
+---------+
| sex + 0 |
+---------+
|       2 |
|       1 |
|       0 |
+---------+
3 rows in set (0.00 sec)
--插入对应的下标
mysql> insert into my_enum values(2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_enum;
+------+
| sex  |
+------+
| 女   |
| 男   |
|      |
| 女   |
+------+
4 rows in set (0.00 sec)

set(多选)

集合: 将多个数据项同时保存,本质是将指定的项按照对应的二进制位来进行控制:

1表示该选项被选中,0 表示该选项没有被选中

基本语法: set('值1','值2','值3'...)

系统为 set 提供了多个字节进行保存,但是系统会自动计算,来选择具体的存储单元

1个字节: set 只能有8 个选项

2个字节: set 只能有16个选项

3个字节: set 只能有24 个选项

8个字节: set 只能有64 个选项

set 和 enum 一样,最终存储到数据字段中的依旧是数字,而不是真是的字符串

mysql> create table my_set(hobby set('看书','写字','听歌','玩游戏','运动', '唱歌','跳舞','旅游')) charset utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

-- 插入数据
mysql> insert into my_set values('看书,唱歌,跳舞');
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_set;
+----------------------+
| hobby                |
+----------------------+
| 看书,唱歌,跳舞       |
+----------------------+
1 row in set (0.00 sec)

--会排好序
mysql> insert into my_set valuess('唱歌,跳舞,看书,旅游');
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_set;
+-----------------------------+
| hobby                       |
+-----------------------------+
| 看书,唱歌,跳舞              |
| 看书,唱歌,跳舞,旅游         |
+-----------------------------+
2 rows in set (0.00 sec)

-- 实际是数字存储
mysql> select hobby +0 from my_set;
+----------+
| hobby +0 |
+----------+
|       97 |
|      225 |
+----------+
2 rows in set (0.00 sec)

mysql> insert into my_set values('255');
Query OK, 1 row affected (0.00 sec)
--全部选中
mysql> select * from my_set;
+------------------------------------------------------------+
| hobby                                                      |
+------------------------------------------------------------+
| 看书,唱歌,跳舞                                             |
| 看书,唱歌,跳舞,旅游                                        |
| 看书,写字,听歌,玩游戏,运动,唱歌,跳舞,旅游                  |
+------------------------------------------------------------+
3 rows in set (0.00 sec)
-- 插入错误数据
mysql> insert into my_set values('看书,爬山')
    -> ;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from my_set;
+------------------------------------------------------------+
| hobby                                                      |
+------------------------------------------------------------+
| 看书,唱歌,跳舞                                             |
| 看书,唱歌,跳舞,旅游                                        |
| 看书,写字,听歌,玩游戏,运动,唱歌,跳舞,旅游                  |
| 看书                                                       |
+------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql记录长度:(mysql 5.0)

在mysql 中, 记录长度( record == 行 row) 总长度不能超过 65535 个字节

varchar 能够存储的理论值为 65535 个字符,在不同的字符集下所占的字符不同,可能多占用字节

utf8 : varchar(21844)        65535 / 3 = 21845

gbk : varchar(32766)        65535 / 2 = 32767

列属性(字段属性 6个)

| Field | Type       | Null | Key | Default | Extra |

null 属性: 

代表属性是否为空; not null  不能为空

注: 在设计表的时候,尽量不要让数据为空;

mysql 的记录长度为65535 个字节,如果表中有字段允许为null,那么系统会保留一个字节来存储null,最终有效存储长度为 65534 个字节;

默认值:

Default 

mysql> create table my_default(name varchar(30) not null, age int default 18);
Query OK, 0 rows affected (0.01 sec)

mysql> desc my_default;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   |     | NULL    |       |
| age   | int         | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into my_default(age) values(20);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from my_default;
+------+------+
| name | age  |
+------+------+
|      |   20 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into my_default(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_default;
+------+------+
| name | age  |
+------+------+
|      |   20 |
| tom  |   18 |
+------+------+
2 rows in set (0.00 sec)

列描述:

comment : 是专门用于给开发人员进行维护的一个注释说明

基本语法 : comment '描述'

查看列描述 :    show create table 表名     

mysql> create table my_comment(name varchar(30) not null comment '名字,不能为空',
    -> age int default 18 comment '年龄,可以为空,默认值为18');
Query OK, 0 rows affected (0.01 sec)

mysql> show create table my_comment;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_comment | CREATE TABLE `my_comment` (
  `name` varchar(30) NOT NULL COMMENT '名字,不能为空',
  `age` int DEFAULT '18' COMMENT '年龄,可以为空,默认值为18'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

主键:primary key

在一张表中,只有一个字段,里面的值具有唯一性

创建主键:

随表创建:

1. 直接在需要当做主键的字段之后 , 添加 primary key 来确认主键

2. 在所有字段之后增加 primary key 选项, primary key(字段名 )

mysql> create table my_prim1(name varchar(20) primary key)charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table my_prim2(name varchar(20),primary key(name))charset utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

表后创建:

基本语法 : alter table 表名 add primary key(字段名)

查看主键:

1. 查看表结构:        show 表名;

2. 查看表创建语句:        show create table 表名;

mysql> create table my_prim3(name varchar(30));
Query OK, 0 rows affected (0.08 sec)

mysql> alter table my_prim3 add primary key(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_prim3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show create table my_prim3;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| my_prim3 | CREATE TABLE `my_prim3` (
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除主键:

基本语法: alter table 表名 drop primary key;

mysql> alter table my_prim3 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_prim3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

复合主键:

mysql> create table my_core(class char(10),
    -> name varchar(20),
    -> age int,
    -> primary key(class,name))charset utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> desc my_core;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| class | char(10)    | NO   | PRI |         |       |
| name  | varchar(20) | NO   | PRI |         |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into my_core values('高一','张三',25);
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_core values('高一','张三',25);
ERROR 1062 (23000): Duplicate entry '高一-张三' for key 'PRIMARY'
mysql> insert into my_core values('高一','李四',25);
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_core values('高一','李四',25);
ERROR 1062 (23000): Duplicate entry '高一-李四' for key 'PRIMARY'
mysql> insert into my_core values('高二','李四',25);
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_core;
+-------+------+------+
| class | name | age  |
+-------+------+------+
| 高一  | 张三 |   25 |
| 高一  | 李四 |   25 |
| 高二  | 李四 |   25 |
+-------+------+------+
3 rows in set (0.00 sec)

mysql> show create table my_core;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------+
| Table   | Create Table

                                                         |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------+
| my_core | CREATE TABLE `my_core` (
  `class` char(10) NOT NULL DEFAULT '',
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`class`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)

自动增长: auto_increment

自动增长的原理：

1. 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记住当前对应的数据值，在给定一个指定的步长

2. 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据

3. 自动增长的触发： 给定属性的字段没有提供值

4. 自动增长只适用于数值

使用自动增长：

mysql> create table my_auto(id int primary key auto_increment,
    -> name varchar(20))charset utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> desc my_auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> insert into my_auto(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_auto;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

自动增长一旦触发使用之后，会自动的在表选项中增加一个选项（一张表最多只能拥有一个自动增长）

mysql> show create table my_auto;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| Table   | Create Table

                           |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| my_auto | CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

修改自动增长：  AUTO_INCREMENT=2           AUTO_INCREMENT=8

表选项 可以通过修改表结构来实现

alter table 表名 auto_increment = 值；

mysql> alter table my_auto auto_increment = 8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table my_auto;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| Table   | Create Table

                           |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| my_auto | CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

删除自动增长：

alter table 表名 modify 字段名 类型；

mysql> alter table my_auto modify id int;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table my_auto;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Table   | Create Table

       |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| my_auto | CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)

查看自动增长初始变量：show variables like 'auto_increment%';

mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

auto_increment_increment ：步长
auto_increment_offset    ：初始值

增加自动增长：

alter table 表名 modify 字段名 数据类型 auto_increment;

alter table 表名 modify 字段名 数据类型 auto_increment;

mysql> alter table my_auto modify id int auto_increment;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table my_auto;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| Table   | Create Table

                           |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| my_auto | CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

唯一键:

数据基础操作:

插入数据:

inster into 表名 [(字段列表)] values(对应字段列表)

inster into 表名 values (对应表结构)

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into class values(1,'张三');
Query OK, 1 row affected (0.03 sec)

mysql> insert into class(name,id) values('李四',2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into class(name) values('tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
| NULL | tom    |
+------+--------+
3 rows in set (0.00 sec)

数据查询:

查询表中全部数据:        select * from 表名;

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
| NULL | tom    |
+------+--------+
3 rows in set (0.00 sec)

查询表中部分字段:        select 字段列表 from 表名;

mysql> select id from class;
+------+
| id   |
+------+
|    1 |
|    2 |
| NULL |
+------+
3 rows in set (0.01 sec)

简单条件查询:        selcet 字段列表/*from 表名 where 字段名 = 值

mysql> select * from class where name='李四';
+------+--------+
| id   | name   |
+------+--------+
|    2 | 李四   |
+------+--------+
1 row in set (0.00 sec)

删除操作:

delete from 表名 [where 条件];        没有带条件会删除全部数据;

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
| NULL | tom    |
+------+--------+
3 rows in set (0.00 sec)

mysql> delete from class where name='tom';
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

更新操作:

update 表名 set 字段名 = 新值 [where 条件];        没有带条件会更新对应的字段列表全部数据;

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

mysql> update class set name = '张三丰' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张三丰    |
|    2 | 李四      |
+------+-----------+
2 rows in set (0.00 sec)

字符集:

查看字符集         show variables like 'character_set%';

mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8mb4                                              |
| character_set_connection | utf8mb4                                              |
| character_set_database   | gbk                                                  |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8mb4                                              |
| character_set_server     | utf8mb4                                              |
| character_set_system     | utf8mb3                                              |
| character_sets_dir       | /usr/local/Cellar/mysql/8.0.29/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.09 sec)

 character_set_client     : 客户端传入
character_set_connection  : 连接层
character_set_database    : 当前数据库存储方法是
character_set_results     : 放回结果的字符集 服务端server

设置字符集:

set names 字符集

set names gbk

mysql> set names gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'character_set%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | gbk                                                  |
| character_set_connection | gbk                                                  |
| character_set_database   | gbk                                                  |
| character_set_filesystem | binary                                               |
| character_set_results    | gbk                                                  |
| character_set_server     | utf8mb4                                              |
| character_set_system     | utf8mb3                                              |
| character_sets_dir       | /usr/local/Cellar/mysql/8.0.29/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.04 sec)

修改某一个变量:

set 变量名 = 字符集

set character_set_client = gbk;

标记

数据结构：B+TREE 

二叉树：顺序增长依次查询效率低

红黑树： 数据多了深度越深，效率自然低了

HASH： 查询条件限制

B-TREE：度（degree）-节段的数据存储个数，叶节点具有 相同的深度，叶节点的指针为空，节点的数据key从左到右递增排列，度有上限，查找数据时会受到内存与硬盘的交互限制

B+TREE：非叶子节点不存储数据，只存储key,可以增大度，叶子节不存储指针，叶子节点存储数据，顺序访问指针提高区间访问的性能，叶子节点有指针支持条件搜索；度一般会超过100，因此深度非常小（一般为3-5之间）

算法演示网站：Data Structure Visualization

索引引擎 默认：InnoDB

MyisAM引擎：（非聚集）索引文件和数据文件是分离的

InnoDB引擎：（聚集）索引文件和数据文件放在一起

性能调优：Explain

EXPLAIN SELECT (SELECT id FROM actor LIMIT 1) FROM film;

id

id 列的编号是 SELECT 的序列号，有几个 SELECT 就有几个 id，并且 id 的顺序是按 SELECT 出现的顺序增长的。MySQL 将 SELECT 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
复杂查询分为三类：简单子查询、派生表（FROM 语句中的子查询）、UNION 查询。
id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行

select_type

表示对应行是简单还是复杂的查询
SIMPLE：简单查询。查询不包含子查询和 UNION
PRIMARY：复杂查询中最外层的 SELECT
SUBQUERY：包含在 SELECT 中的子查询（不在 FROM 子句中）
DERIVED：包含在 FROM 子句中的子查询。MySQL 会将结果存放在一个临时表中，也称为派生表UNION：在 UNION 中的第二个和随后的 SELECT
UNION RESULT：从 UNION 临时表检索结果的 SELECT

table

这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，
于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1 和 2 表示参与 union 的
select 行 id。

type

这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概
范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到 range 级别，最好达到 ref

NULL：mysql 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在
索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const, system：mysql 能对查询的某部分进行优化并将其转化成一个常量（可以看 show
warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多
有一个匹配行，读取 1 次，速度比较快。system 是 const 的特例，表里只有一条元组匹配时
为 system

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条
件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种
type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引
要和某个值相比较，可能会找到多个符合条件的行。

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定
范围的行

index：扫描全表索引，这通常比 ALL 快一些。（index 是从索引中读取的，而 all 是从硬盘中
读取）

ALL：即全表扫描，意味着 mysql 需要从头到尾去查找所需要的行。通常情况下这需要增加索
引来进行优化了

possible_keys

这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数
据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。
如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以
创造一个适当的索引来提高查询性能

key

这一列显示 mysql 实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制 mysql 使用或忽视 possible_keys 列中的索
引，在查询中使用 force index、ignore index。

key_len

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len 计算规则：
字符串
char(n)：n 字节长度
varchar(n)：2 字节存储字符串长度，如果是 utf-8，则长度 3n + 2
数值类型
tinyint：1 字节
smallint：2 字节
int：4 字节
bigint：8 字节
时间类型
date：3 字节
timestamp：4 字节
datetime：8 字节
如果字段允许为 NULL，需要 1 字节记录是否为 NULL
索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半
部分的字符提取出来做索引。

ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常
量），字段名（例：film.id）

rows

这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra

这一列展示的是额外信息。常见的重要值如下：
Using index：查询的列被索引覆盖，并且 where 筛选条件是索引的前导列，是性能高的
表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于 innodb 来说，如果是辅助索
引性能会有不少提高

Using where：查询的列未被索引覆盖，where 筛选条件非索引的前导列

Using where Using index：查询的列被索引覆盖，并且 where 筛选条件是索引列之
一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据

NULL：查询的列未被索引覆盖，并且 where 筛选条件是索引的前导列，意味着用到了索
“ ”引，但是部分字段未被索引覆盖，必须通过 回表 来实现，不是纯粹地用到了索引，也不
是完全没用到索引

Using index condition：与 Using where 类似，查询的列不完全被索引覆盖，where
条件中是一个前导列的范围；

Using temporary：mysql 需要创建一张临时表来处理查询。出现这种情况一般是要进行优
化的，首先是想到用索引来优化。

Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取
行。此时 mysql 会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后
排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

索引实践：

1. 全值匹配 （如 联合索引，index(a,b,c)  全部用上）

2.最佳左前缀法则 （如 联合索引，index(a,b,c)  按照顺序 a,b,c全部用上）

        如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索
引中的列。

3.不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效
而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select *语句

6.mysql 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描

7.is null,is not null 也无法使用索引

8.like 以通配符开头（'$abc...'）mysql 索引失效会变成全表扫描操作
    问题：解决 like'%字符串%'索引不被使用的方法？
         SELECT name form tb_user where name like '%Lei%';

9.字符串不加单引号索引失效

10.少用or或in,用它查询时，非主要字段的索引会失效，主键索引有时生效，有时不生效，根据数量有关，具体还得看mysql的查询优化结果