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)
