DDL 语句(对数据库和表结构进行操作) -- 操作数据库:
查询:
查询所有数据库: show databases;
查询当前数据库: select database();
创建:
create database[if not exists] (如果它存在就不创建,不存在就创建) 数据库名 [default charset 字符集] [ collate 排序规则]
mysql> create database if not exists aaa default charset utf8mb4;
Query OK, 1 row affected (0.02 sec)
删除: drop database [if exists](如果存在就删除,如果不存在就不报错) 数据库名;
mysql> drop database abc;
Query OK, 0 rows affected (0.02 sec)
使用: use 数据库名;
mysql> use abc;
Database changed
DDL 语句 -- 操作表结构:
查询当前数据库所有表:show tables;
mysql> use bjpowernode;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept |
| emp |
| salgrade |
+-----------------------+
3 rows in set (0.00 sec)
查询表结构: desc 表名;
mysql> desc emp;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
| idcard | varchar(18) | YES | | NULL | |
| workaddress | varchar(30) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
查询指定表的建表语句: show create table 表名; #详细信息
mysql> use aaa;
Database changed
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| dept |
| emp |
| salgrade |
| sysion |
+---------------+
4 rows in set (0.00 sec)
mysql> create table abc(
-> id int,
-> name varchar(20),
-> age tinyint,
-> gender char(1)
-> );
Query OK, 0 rows affected (0.03 sec)
查询指定的建表语句:
mysql> show create table abc;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` tinyint DEFAULT NULL,
`gender` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
表创建:
create table 表名(
字段1 ,字段1类型[comment 字段1注释]#方括号中comment为注释,
字段2, 字段2类型[comment 字段2注释])
)
mysql> create table user(
-> id int,
-> name varchar(50),
-> age tinyint,
-> gender varchar(1)
-> );
Query OK, 0 rows affected (0.05 sec)
create table tb_user(
-> id int comment '编号',
-> name varchar(10) comment '姓名',
-> age int comment '年龄'
-> ) comment '用户表';
Query OK, 0 rows affected (0.04 sec)
删除表:
drop table [if exists -- 存在] 表名;
mysql> drop table if exists abc;
Query OK, 0 rows affected (0.02 sec)