mysql基础命令

1、创建/编辑数据库
# 创建字符集为utf8的数据库testuser
mysql> create database testuser character set 'utf8';
Query OK, 1 row affected (0.01 sec)

# 创建数据库不指定字符集
mysql> create database testuser1;
Query OK, 1 row affected (0.01 sec)

# 判断创建的数据库存不存在，若不存在创建数据库
mysql> create database if not exists testuser;
Query OK, 1 row affected, 1 warning (0.00 sec)

# 修改数据库字符集
mysql> alter database deluser character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database deluser;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| deluser  | CREATE DATABASE `deluser` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
2、删除数据库
mysql> drop database deluser;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| bidding_db           |
| inverstment_projects |
| invest               |
| invest_authority     |
| jira                 |
| mysql                |
| performance_schema   |
| shengli              |
| sys                  |
| testuser             |
+----------------------+
11 rows in set (0.00 sec)
3、查看数据库
# 切换数据库
mysql> use testuser;
Database changed

# 查询所有的数据库
mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| bidding_db           |
| inverstment_projects |
| invest               |
| invest_authority     |
| jira                 |
| mysql                |
| performance_schema   |
| shengli              |
| sys                  |
| testuser             |
+----------------------+
11 rows in set (0.00 sec)

# 查看某个已创建的数据库
mysql> show create database testuser;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| testuser | CREATE DATABASE `testuser` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

# 查看当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| testuser   |
+------------+
1 row in set (0.00 sec)

# 查看用户
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
4、创建/编辑表
# 创建一张用户信息表
mysql> create table user(
    -> name varchar(10),
    -> gender varchar(10),
    -> hobby varchar(50));
Query OK, 0 rows affected (0.02 sec)

# 修改表名称
mysql> rename table user to sys_user;
Query OK, 0 rows affected (0.01 sec)

# 默认最后一列追加字段，关键词add
mysql> alter table user add phone int(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 在首列追加字段，关键词first
mysql> alter table user add id int(11) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 在某列后面追加字段，关键词after
mysql> alter table user add age int(3) after name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除字段，关键词drop
mysql> alter table user drop hobby ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改字段名称，顺带修改字符类型
mysql> alter table user change gender addre varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 只修改字段属性
mysql> alter table user modify addre varchar(255);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
5、删除表
mysql> drop table user1;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
+--------------------+
1 row in set (0.00 sec)
6、查看表
# 查看支持的引擎类型
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
| Engine             | Support | Comment                                           | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
| InnoDB             | DEFAULT | Supports transactions,...                         | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful ...          | NO           | NO   | NO         |
...
+--------------------+---------+----------------------------------------------------------------+--------------+------+--
9 rows in set (0.00 sec)

# 查看当前库中所有的表
mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
+--------------------+
1 row in set (0.00 sec)

# 查看某个库中所有的表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
...
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 查看表状态
mysql> show table status like 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-05 14:22:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

# 查看表结构
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| hobby  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 查看创建的表
mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(10) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `hobby` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 复制查看的表同事复制数据
mysql> show tables;
+--------------------+
| Tables_in_testuser |
+--------------------+
| user               |
| user1              |
+--------------------+
2 rows in set (0.00 sec)
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • mysql 基础命令
    首先要安装MySQL ： apt updatesudo apt-get install mysql-server ...
    糖炒栗子_01c5阅读 308评论 0赞 0
  • Mysql基础命令
    普通用户自己更改密码（普通用户可以自己私自改） root用户给其他mysql用户更改密码 查看数据库 查看表单 查...
    ScoTing阅读 53评论 0赞 0
  • MySQL基础命令
    数据库操作 显示数据库 默认数据库 mysql - 用户权限相关数据 test - 用于用户测试数据 inform...
    二歪求知iSk2y阅读 453评论 0赞 0
  • MySQL基础命令收集（持续更新）
    一、命令收集： 1、建表create table t1 (name char(5));2、表中插入数据insert...
    我要笑阅读 521评论 0赞 3
  • 送你一个晚安故事
    我是黑夜里大雨纷飞的人啊 1 “又到一年六月，有人笑有人哭，有人欢乐有人忧愁，有人惊喜有人失落，有的觉得收获满满有...
    陌忘宇阅读 8,610评论 28赞 53