一、数据库信息管理
1、查看数据库信息：show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
2、查看数据库中的表信息：show tables;
show tables in mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
3、use 数据库名 -->切换到数据库
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
4、显示数据表的结构（字段）：describe user；
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)
Field：字段名称
type：数据类型
null：是否允许为空
key：主键
default：默认值
extra：扩展属性，例如：标志符列（标识了种子，增量/步长）id：1 3 5 7
5、\G -->以列表方式竖向显示
mysql> show tables\G;
*************************** 1. row ***************************
Tables_in_mysql: columns_priv
*************************** 2. row ***************************
Tables_in_mysql: db
*************************** 3. row ***************************
Tables_in_mysql: engine_cost
*************************** 4. row ***************************
Tables_in_mysql: event
*************************** 5. row ***************************
Tables_in_mysql: func
*************************** 6. row ***************************
Tables_in_mysql: general_log
*************************** 7. row ***************************
Tables_in_mysql: gtid_executed
*************************** 8. row ***************************
Tables_in_mysql: help_category
*************************** 9. row ***************************
Tables_in_mysql: help_keyword
*************************** 10. row ***************************
Tables_in_mysql: help_relation
mysql> describe user\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: Select_priv
Type: enum('N','Y')
Null: NO
Key:
Default: N
Extra:
*************************** 4. row ***************************
Field: Insert_priv
Type: enum('N','Y')
Null: NO
Key:
Default: N
Extra:
*************************** 5. row ***************************
Field: Update_priv
Type: enum('N','Y')
Null: NO
Key:
Default: N
Extra:
二、数据类型
常用的数据类型：
int：整型 --->用于定义整数类型的数据
float：单精度浮点4字节32位 --->准确表示到小数点后六位
double：双精度浮点8字节64位
char：固定长度的字符类型 --->用于定义字符类型数据
#如果char存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入数据的实际长度大于指定长度，低版本会被截取，高版本会报错
varchar：可变长度的字符类型
text：文本
image：图片
decimal（5，2）：5个有效长度数字，小数点后面有2位 指定长度数组
主键是唯一的，但主键可以由多个字段构成
MySQL数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”。
MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。
“.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。
MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件
（frm,myd,myi）。 每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。
另外还有“.ibd”和 ibdata 文件，这两种文件都是用来存放 Innodb 数据的，之所以有两种文件来存放 Innodb 的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储 方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。
三、SQL语句
SQL语句用于维护管理数据库，包括数据查询，数据更新，访问控制，对象管理等功能。
SQL语言分类：
DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）
1、DDL：数据定义语言，用于创建数据库对象，如库，表，索引等
create、drop、alter
1.创建新的数据库：CREATE DATABASE 数据库名；
mysql> CREATE DATABASE lzy
-> ;\
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzy |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.创建新的表：CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
mysql> CREATE TABLE XJJ(id int NOT NULL,name char(20)NOT NULL,CWH decimal(5,2),passwd char(50)DEFAULT'',PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzy |
+---------------+
| XJJ |
+---------------+
1 row in set (0.00 sec)
mysql> describe XJJ;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| passwd | char(50) | YES | | | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#NOT NULL 不允许为空值
#DEFAULT'' 默认值为空
PRIMARY KEY :主键一般选择没有重复并且不为空值的字段
3.删除指定的数据表
use 数据库名
DROP TABLE 表名
不进入库中直接删除：DROP TABLE 数据库名 表名；
删除指定数据库：DROP DATABASE 数据库名；
2、DML管理表中的数据记录 ---数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据
insert、update、delete
1.插入数据：INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);
INSERT INTO 表名 VALUES (字段1的值，字段2的值,...);
mysql> insert into XJJ (id,name,CWH,passwd) values(1,'jinjin',87,PASSWORD('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into XJJ VALUES(2,'yanyan',89,654321);
Query OK, 1 row affected (0.00 sec)2.查询表的数据记录：SELECT * FROM 表名 ;
mysql> select * from XJJ
-> ;
+----+--------+-------+-------------------------------------------+
| id | name | CWH | passwd |
+----+--------+-------+-------------------------------------------+
| 1 | jinjin | 87.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | yanyan | 89.00 | 654321 |
+----+--------+-------+-------------------------------------------+
2 rows in set (0.00 sec)
3.修改，更新数据表中的数据记录：UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
mysql> update XJJ set CWH=162,passwd='' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from XJJ
-> ;
+----+--------+--------+-------------------------------------------+
| id | name | CWH | passwd |
+----+--------+--------+-------------------------------------------+
| 1 | jinjin | 87.00 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 2 | yanyan | 162.00 | |
+----+--------+--------+-------------------------------------------+
2 rows in set (0.00 sec)
4.在数据表中删除指定的数据记录：DELETE FROM 表名 [WHERE 条件表达式];
mysql> delete from XJJ where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from XJJ;
+----+--------+--------+--------+
| id | name | CWH | passwd |
+----+--------+--------+--------+
| 2 | yanyan | 162.00 | |
+----+--------+--------+--------+
1 row in set (0.00 sec)
delete删除只删除表中的数据，表的框架不删除，有新数据还可以插入
3、DQL查询数据记录
select
格式：
SELECT 字段名1,字段名2[,...] FROM 表名[WHERE 条件表达式];
SELECT * FROM 表名; -----> 显示表中所有数据
SELECT id,name,score FROM 表名 WHERE id=2; -----> 显示id2的name，score数据
select name from 表名\G -----> 以列表方式竖向显示
select * from info limit 2; -----> 只显示头2行
select * from info limit 2,3; ----->显示第2行后的前3行
4、DCL
1.修改表名
ALTER TABLE 旧表名 RENAME 新表名；
mysql> show tables
-> ;
+---------------+
| Tables_in_lzy |
+---------------+
| XJJ |
+---------------+
1 row in set (0.00 sec)
mysql> alter table XJJ rename xjj;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzy |
+---------------+
| xjj |
+---------------+
1 row in set (0.00 sec)
2.扩展表结构（增加字段）
ALTER TABLE 表名 ADD 新字段 数据类型 ...;
mysql> select * from xjj
-> ;
+----+--------+--------+--------+
| id | name | CWH | passwd |
+----+--------+--------+--------+
| 2 | yanyan | 162.00 | |
+----+--------+--------+--------+
1 row in set (0.00 sec)
mysql> alter table xjj add address varchar(50) default '如家';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc xjj
-> ;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| passwd | char(50) | YES | | | |
| address | varchar(50) | YES | | 如家 | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3.修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
unique key:唯一键（特性:唯一，但可以为空，空值只允许出现一次)
Primary key:唯一且非空(主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。)
#CHANGE可修改字段名、数据类型、约束等所有项。（谨慎修改类型，可能会导致原有数据出错）
mysql> alter table xjj change name user_name varchar(20) unique key;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc xjj;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| passwd | char(50) | YES | | | |
| address | varchar(50) | YES | | 如家 | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4.删除字段
ALTER TABLE 表名 DROP 字段名;
mysql> alter table xjj drop passwd;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc xjj;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 如家 | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5.例：use school;
create table if not exists info (
id int(4) zerofill primary key auto_increment, #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
注：#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
5、克隆表
格式：create table 表名2 like 表名；
###通过LIKE方法，复制原表的表结构生成新的表2
mysql> create table xjj2 like xjj;
Query OK, 0 rows affected (0.01 sec)
mysql> desc xjj2
-> ;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 如家 | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
####这里只生成了表的结构，没有数据
insert into 表名2 select * from 表名；
###备份原表内容
mysql> insert into xjj2 select * from xjj;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from xjj2
-> ;
+----+-----------+--------+---------+
| id | user_name | CWH | address |
+----+-----------+--------+---------+
| 2 | yanyan | 162.00 | 如家 |
+----+-----------+--------+---------+
1 row in set (0.00 sec)
mysql> desc xjj2
-> ;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | UNI | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 如家 | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
####这种方法是完全复制，包含主键和唯一键
CREATE TABLE 表名2 （SELECT * from 表名）；
###复制原表中的数据到表2中
mysql> create table djj (select * from xjj);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from djj;
+----+-----------+--------+---------+
| id | user_name | CWH | address |
+----+-----------+--------+---------+
| 2 | yanyan | 162.00 | 如家 |
+----+-----------+--------+---------+
1 row in set (0.00 sec)
mysql> desc djj;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| user_name | varchar(20) | YES | | NULL | |
| CWH | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 如家 | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
这种方法可以复制数据，但是没有主键和唯一键，如果需要可以后面插入
show create table 表名2\G
SELECT * from 表名2；
###获取数据表的表结构，索引等信息.
6、清空表，删除表内的所有数据
方法一：
delete from yyy3;
#DELETE清空表后，返回的结果内有删除的记录条目;
DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM 删除
所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。
方法二：
truncate table test01;
#TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，
因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，
ID会从1开始重新记录
小结:
#删除类型
drop table table_name
1）属于DDL
2)不可回滚（无法恢复)
3)不可带where
4)表内容和结构删除
5)删除速度快
truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快
delete from table_name
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在，表内容要看where执行的情况
5)册删除速度慢，需要逐行删除
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
#删除
速度
drop> truncate > delete
安全性
delete 最好
8、创建临时表
临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。
PS：无法创建外键
CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[, ...] [, PRIMARY KEY (主键名)]);
sql 写入---->数据表中---->先把数据保存在内存中---->写入到磁盘
insert into info ---->info这张表，会先复制一份表数据到内存里面，给我们进行修改 ---->敲完回车之后，确定提交了，才会写入数据表中---->再保存在磁盘里面
create table
test01---->只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉
