目录
1、创建数据库
查看创建数据库详情
CREATE DATABASE | SCHEMA [ IF NOT EXISTS ] 'DB_NAME'CHARACTER SET 'character set name'COLLATE 'collate name' ;
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
mysql> show create database test1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
mysql> create database if not exists db1 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
[root@vms10 ~]# cat /data/mysql/db1/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
1.1、修改数据库
mysql> alter database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
1.2、删除数据库
mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected (0.01 sec)
2、创建表
2.1、直接创建
:41: (root@localhost) [db1]> create table student(
-> id int unsigned auto_increment primary key,
-> name varchar(20) not null,
-> age tinyint unsigned,
-> gender enum('M','F') default 'M'
-> )engine=innodb auto_increment=10 default charset=utf8;
:42: (root@localhost) [db1]> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);
:08: (root@localhost) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
:09: (root@localhost) [db1]> insert into testdate (id) values(1);
:09: (root@localhost) [db1]> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2022-04-08 16:09:39 |
+----+---------------------+
2.2、利用现有的表创建(但是会缺失主键等必要的属性)
:09: (root@localhost) [db1]> create table testdate2 select * from testdate;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
:34: (root@localhost) [db1]> select * from testdate2;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2022-04-08 16:09:39 |
+----+---------------------+
2.3、只复制现存的表结构,不复制数据
:46: (root@localhost) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
2 rows in set (0.00 sec)
:46: (root@localhost) [db1]> create table testdate3 like testdate;
Query OK, 0 rows affected (0.01 sec)
:46: (root@localhost) [db1]> desc testdate3;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
3、查看表
查看支持的存储引擎
mysql> show engines;
查看表
mysql> show tables;
查看表结构
mysql> desc user;
查看表创建命令
mysql> show create table user;
查看表状态
mysql> show table status like 'user'\G;
查看库中所有表状态
mysql> show table status from mysql\G;
4、修改和删除表
删除表
[db1]> drop table if exists testdate2;
修改表
ALTER TABLE 'tbl_name'# 字段:# 添加字段: addADD col1 data_type [ FIRST | AFTER col_name]# 删除字段: drop# 修改字段:alter (默认值) , change (字段名) , modify (字段属性)
# 重命名
ALTER TABLE students RENAME s1;
# 添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
# 修改字段类型
ALTER TABLE s1 MODIFY phone int;
# 修改字段名称
ALTER TABLE s1 CHANGE phone mobile char(11);
# 删除字段
ALTER TABLE s1 DROP COLUMN mobile;
# 设置字符集
ALTER TABLE s1 character set utf8;
#新建表无主键,添加和删除主键
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;