命令类型
服务器端命令
获取命令帮助
数据库管理
查看数据库基础变量
SQL组成
创建
修改
删除
表管理
创建
示例一
示例二
示例三
一个常见的创建表结构的示例
表修改
改名
表结构修改
添加/删除字段
修改字段
键管理
索引
索引管理
视图
视图操作
命令类型
服务器端命令
DDL:数据定义语言,主要用于管理数据库组件,例如表、索引、视图、用户、存储过程
CREATE、ALTER、DROP
DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查;
INSERT, DELETE, UPDATE, SELECT
获取命令帮助
mysql> help CREATE...
mysql>HELP CREATE DATABASE
数据库管理
查看数据库基础变量
数据库的存储引擎类型:SHOW ENGINES;
查看mysql支持的字符集:show character set;
查看指定表的存储引擎:show table status [where Name='tab_name'|like '%...%'];
查看支持的所有字符集:SHOW CHARACTER SET;
查看支持的所有排序规则:SHOW COLLATION;
查看索引:show indexes from tab_name;
SQL组成
DDL:数据库模式定义语言,关键字:create、alter、dorp
DML:数据操纵语言,关键字:Insert、delete、update、alter、dorp
DCL:数据库控制语言 ,关键字:grant、remove
DQL:数据库查询语言,关键字:select
创建
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name #定义排序规则
# 创建数据库
[(none)]>create database if not exists cce charset = utf8 collate=utf8_general_ci;
# 创建表
[(none)]>show create database cce;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| cce | CREATE DATABASE `cce` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
修改
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name
# 默认字符集
[(none)]>alter database cce charset=latin1 collate=latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)
[(none)]>show create database cce;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| cce | CREATE DATABASE `cce` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
删除
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
# 删除库
[(none)]>drop database if exists cce;
Query OK, 0 rows affected (0.00 sec)
表管理
创建
语法一:create table [if not exists] tab_name (col_name data_type|index|key|foreign|unique|primary key) tab_option:engine[=]engine_name、charset[=]charset_name、collate[=]collation_name
语法二:create table [if not exists] tab_name (tab_definition,...)[tab_option] select_statement # 直接创建表,并将查询语句的结果得到的数据插入到新表中;
语法三:create table [if not exists] tab_name {like old_tab_name}# 复制某个存在的表的表结构来创建新表,只有结构没有数据;
示例一
[mysql]>create table if not exists cce(id int auto_increment,name char(32) not null,gender enum('M','G') not null,primary key(id)) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.41 sec)
[mysql]>desc cce;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| gender | enum('M','G') | NO | | NULL | |
+--------+---------------+------+-----+---------+----------------+
示例二
# 创建一个新表
[cce]>create table cce(id int);
Query OK, 0 rows affected (0.48 sec)
# 插入测试数据,可以看出都插入成功了
[cce]>insert into cce values(1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 基于上面的新表结构和数据再创建一个表
[cce]>create table new_cce select * from cce;
Query OK, 3 rows affected (0.73 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 查看数据
[cce]>select * from new_cce;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
示例三
# 创建一个新的数据库
[(none)]>create database cce;
Query OK, 1 row affected (0.01 sec)
[(none)]>use cce;
Database changed
# 查看要基于创建的表结构
[cce]>desc mysql.cce;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| gender | enum('M','G') | NO | | NULL | |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
# 根据已存在的表创建表
[cce]>create table cce like mysql.cce;
Query OK, 0 rows affected (0.61 sec)
# 查看创建后的表结构
[cce]>desc cce;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| gender | enum('M','G') | NO | | NULL | |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
一个常见的创建表结构的示例
id使用smallint字段类型来限制并且是无符号不能为空自增主键,name使用char来限制,并且长度只给3测试下,age使用tinyint来给定,因为一般这个字段都用不到太大的值,gender使用枚举类型并且默认值为m不能为空,最后将name字段定义为索引实现加速查询;
[cce]>create table cce(id smallint unsigned not null auto_increment,name char(3) not null,age tinyint unsigned not null,gender enum('m','f') default 'm' not null,index(name),primary key(id)) engine=innodb default charset=utf8;
[cce]>insert into cce(name,age) values ('蔡大爷蔡大爷蔡大爷',18)
ERROR 1406 (22001): Data too long for column 'name' at row 1 # 提示插入的name字段的数据太长,插入失败
表修改
改名
MariaDB [cce]> alter table cce rename to wj;
或者:
MariaDB [cce]> rename table wj to cce;
表结构修改
表修改语法:ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]
添加/删除字段
添加:ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ] #标红的表示在什么之后
MariaDB [cce]> alter table students add class varchar(100) not null after sex;
删除:DROP [COLUMN] col_name
MariaDB [cce]> alter table students drop class;
修改字段
change:可以将老的字段名改成新的字段名,然后还可以将字段定义修改;
语法:CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
示例:MariaDB [cce]> alter table cce change new_cce Name varchar(100) not null;
modify:指引修改字段定义的规则;
语法:MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] #
示例:MariaDB [cce]> alter table cce modify name varchar(100) not null;
# 字段相关
[cce]>desc cce;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(3) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('m','f') | NO | | m | |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 新增字段,在指定字段后面新增after,使用first无法在指定字段前面增加
[cce]>alter table cce add city char(32) not null after age;
Query OK, 0 rows affected (1.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看新增之后的结构
[cce]>desc cce;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(3) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| city | char(32) | NO | | NULL | |
| gender | enum('m','f') | NO | | m | |
+--------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
键管理
示例:MariaDB [cce]> alter table students add primary key(name,id);
删除:
主键:DROP PRIMARY KEY
外键:DROP FOREIGN KEY fk_symbol
示例:MariaDB [cce]> alter table students drop primary key;
直接创建表,并将查询语句的结果插入到新创建的表中;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
示例:
查看表的信息:
MariaDB [cce]> show table status where Name='test'\G
修改引擎:MariaDB [cce]> alter table cce engine='InnoDB';
复制某存在的表的结构来创建新的空表;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
示例: