mysql

最新推荐文章于 2021-07-15 19:23:15 发布

小代码2016

最新推荐文章于 2021-07-15 19:23:15 发布

阅读量257

点赞数

分类专栏： km km

本文链接：https://blog.csdn.net/xiaodaima2016/article/details/93890273

版权

km 同时被 2 个专栏收录

33 篇文章 0 订阅

订阅专栏

3 篇文章 0 订阅

订阅专栏

sql 语句分类

DQL语句　　数据查询语言　　select

DML语句　　数据操作语言　　insert delete update

DDL语句　　数据定义语言　　create drop alter

TCL语句　　事务控制语言　　commit rollback

show 语句

show databases 显示所有数据库
show tables 显示当前数据库所有表，可以带like条件

mysql> show tables like 'blog%';
+-----------------------------+
| Tables_in_kmbootdev (blog%) |
+-----------------------------+
| blog_base_info              |
| blog_cat_sys                |
| blog_cat_user               |
| blog_catblog                |
| blog_column                 |
| blog_comment                |
| blog_comment_content        |
| blog_content                |
| blog_module                 |
| blog_score_history          |
| blog_self_module            |
| blog_setting                |
| blog_tag                    |
| blog_tagblog                |
+-----------------------------+
14 rows in set (0.00 sec)

mysql>

show create dabatase 数据库名 显示创建数据库的语句

mysql> show create database kmbootdev;
+-----------+-----------------------------------------------------------------------+
| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| kmbootdev | CREATE DATABASE `kmbootdev` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

show create table 表名 显示建表语句

mysql> show create table blog_tag\G
*************************** 1. row ***************************
       Table: blog_tag
Create Table: CREATE TABLE `blog_tag` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL COMMENT '用户id',
  `name` varchar(50) NOT NULL COMMENT '标签名',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '最后更新时间',
  `version` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='博客标签表'
1 row in set (0.00 sec)

mysql>

show table status from 数据库名 like 表名 查看表信息

mysql> show table status from kmbootdev like 'blog_tag'\G
*************************** 1. row ***************************
           Name: blog_tag
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-06-27 08:49:19
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 博客标签表
1 row in set (0.00 sec)

mysql>

show full columns from 表名 查看列信息

mysql> show full columns from blog_tag;
+-------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+--------------------+
| Field       | Type        | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment            |
+-------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+--------------------+
| id          | bigint(20)  | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |                    |
| user_id     | bigint(20)  | NULL               | NO   | MUL | NULL    |                | select,insert,update,references | 用户id             |
| name        | varchar(50) | utf8mb4_general_ci | NO   |     | NULL    |                | select,insert,update,references | 标签名             |
| create_time | datetime    | NULL               | YES  |     | NULL    |                | select,insert,update,references | 创建时间           |
| update_time | datetime    | NULL               | YES  |     | NULL    |                | select,insert,update,references | 最后更新时间       |
| version     | int(255)    | NULL               | YES  |     | NULL    |                | select,insert,update,references |                    |
+-------------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+--------------------+
6 rows in set (0.00 sec)

mysql>

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

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

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

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql>

show keys from 表名 查看表的索引

mysql> show keys from blog_tag;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| blog_tag |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| blog_tag |          1 | userid   |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

show index from 表名 查看表的索引

mysql> show index from blog_tag;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| blog_tag |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| blog_tag |          1 | userid   |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

表的处理

建表

在这里插入图片描述

修改表

alter table <旧表名> rename [to] <新表名> 修改表名
修改字段
alter table <表名> modify <字段名> <数据类型> <列约束 default、not null、unique> <comment> after <字段名> 可以修改字段的类型、约束、默认值、位置
alter table <表名> change <字段名> <字段新名称> <字段的类型> 修改字段名称
alter table 表名 add column 字段名字段类型 添加字段
alter table <表名> drop <字段名>; 删除字段