一.DBA的职责
1.开发DBA：
负责数据库设计（E-R关系图）、sql开发、内置函数、存储历程（存储过程和存储函数）、触发器、时间调度器（event scheduler）
2.管理DBA：
负责安装、升级、备份、恢复、用户管理、权限管理、监控、分析、基准测试，语句优化（SQL语句）、数据字典，按需要配置服务器（服务器变量：MyISAM，InnoDB，缓存，日志）
二.SQL语言组成部分
1.DDL：
全称Data Defination,我们叫它数据定义语言，典型命令指令有CREAT/ALTER/DROP
2.DML：
全称Data Manipulation,我们叫它数据操作语言，典型命令有INSERT/DELETE/SELECT/UPDATE
3.完整性定义语言，DDL的一部分功能
主键约束、外键约束、唯一键约束、条件约束、非空约束、事务约束
4.视图定义：即虚表，它是存储下来的select语句
5.事务控制：
例如Transactions（在mysql交互界面执行“HELP content”可以查看相关信息。）
6.嵌入式SQL和动态SQL：
7.DCL：
我们叫它数据控制语言，如实现授权和权限收回的典型命令有GRANT/REVOKE.
三.数据类型的功用
MySQL的数据类型请参考：
1.存储的值类型;
2.占据的存储空间大小;
3.定长，变长;
4.如何被索引及排序;
5.是否能够被索引;
四.数据字典：依赖系统编目（花名册）（system catalog）
对于关系型数据库来讲，它的数据字典也是另外找个地方存起来的。对于MySQL数据库来讲，这个位置就是名称为mysql的数据库。我们在第一次启动MySQL时，它第一步工作就是初始化系统表，所谓初始化系统表就是用来创建mysql这个数据库的。我们也可以称这个mysql数据库叫做MySQL的数据字典。数据字典是用来保存数据库服务器上的元数据。那么什么是元数据呢？我总结有以下几点：
1>.保存关系（表）的名字
2>.保存每个关系（表）的各字段的名字
3>.保存各字段的数据类型和长度
4>.保存约束条件
5>.保存每个关系（表）上的视图的名字及视图的定义
6>.保存授权用户（user表）的名字
7>.保存用户的授权和账户信息等
8>.统计类的数据，如每个关系字段的个数，每个关系中行数，每个关系的存储方法
9>.保存元数据的数据库（ 即：information_schema，mysql， performance_schema）
五.MySQL内部组件
如下图所示，连接器（Connectors）和连接池他们之间是建立连接关系的。连接池（Connection Pool）的所有SQL语句都得发送给SQL接口（SQL Interface）进行接收，然后再由分析器（Parser）进行分析，由优化器（Optimizer）进行优化处理，最终我们有可能在缓存（Caches&Buffers）中获取数据，实在再不行在交由存储引擎（Pluggable Storage Engines）去执行SQL语句。
六.MySQL中字符大小写情况说明
1.SQL关键字及函数不区分大小写;
2.数据库、表及视图名称的大小写区分与否取决于底层OS(操作系统)及FS（文件系统）;
3.存储过程、存储函数及事件调度器的名字不区分大小写，但触发器区分大小写;
4.表别名区分大小写;
5.对字段中的数据，如果字段类型为binary类型，则区分大小写，非binary不区分大小写;
七.SQL指令详解-数据库操作
1.数据库的创建
a>.查看创建库时的帮助信息
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/5.6/en/create-database.htmlb>.创建不存在的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> create database yan;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yan |
+--------------------+
4 rows in set (0.00 sec)c>.已经存在数据库的如何使用创建命令
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yan |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database if not exists yan;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yan |
+--------------------+
4 rows in set (0.00 sec)2.数据库的删除
a>.查看删除数据库时的帮助信息
mysql> help drop database;
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the
database. Be very careful with this statement! To use DROP DATABASE,
you need the DROP privilege on the database. DROP SCHEMA is a synonym
for DROP DATABASE.
*Important*:
When a database is dropped, privileges granted specifically for the
database are not automatically dropped. They must be dropped manually.
See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the database
does not exist.
URL: https://dev.mysql.com/doc/refman/5.6/en/drop-database.htmlb>.删除数据库操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yan |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database if exists yan;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)3.修改数据库的字符集和排序字符以及数据字典
mysql> help alter database
Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_option ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
alter_option: {
[DEFAULT] CHARACTER SET [=] charset_name #设置字符集
| [DEFAULT] COLLATE [=] collation_name #修改排序方式
}
ALTER DATABASE enables you to change the overall characteristics of a
database. These characteristics are stored in the db.opt file in the
database directory. This statement requires the ALTER privilege on the
database. ALTER SCHEMA is a synonym for ALTER DATABASE.
The database name can be omitted from the first syntax, in which case
the statement applies to the default database. An error occurs if there
is no default database.
o https://dev.mysql.com/doc/refman/5.6/en/alter-database.html#alter-dat
abase-charset
o https://dev.mysql.com/doc/refman/5.6/en/alter-database.html#alter-dat
abase-upgrading
Character Set and Collation Options
The CHARACTER SET clause changes the default database character set.
The COLLATE clause changes the default database collation. For
information about character set and collation names, see
https://dev.mysql.com/doc/refman/5.6/en/charset.html.
To see the available character sets and collations, use the SHOW
CHARACTER SET and SHOW COLLATION statements, respectively. See [HELP
SHOW CHARACTER SET], and [HELP SHOW COLLATION].
A stored routine that uses the database defaults when the routine is
created includes those defaults as part of its definition. (In a stored
routine, variables with character data types use the database defaults
if the character set or collation are not specified explicitly. See
[HELP CREATE PROCEDURE].) If you change the default character set or
collation for a database, any stored routines that are to use the new
defaults must be dropped and recreated.
Upgrading from Versions Older than MySQL 5.1
The syntax that includes the UPGRADE DATA DIRECTORY NAME clause updates
the name of the directory associated with the database to use the
encoding implemented in MySQL 5.1 for mapping database names to
database directory names (see
https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). This
clause is for use under these conditions:
o It is intended when upgrading MySQL to 5.1 or later from older
versions.
o It is intended to update a database directory name to the current
encoding format if the name contains special characters that need
encoding.
o The statement is used by mysqlcheck (as invoked by mysql_upgrade).
For example, if a database in MySQL 5.0 has the name a-b-c, the name
contains instances of the - (dash) character. In MySQL 5.0, the
database directory is also named a-b-c, which is not necessarily safe
for all file systems. In MySQL 5.1 and later, the same database name is
encoded as a@002db@002dc to produce a file system-neutral directory
name.
When a MySQL installation is upgraded to MySQL 5.1 or later from an
older version,the server displays a name such as a-b-c (which is in the
old format) as #mysql50#a-b-c, and you must refer to the name using the
#mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to
explicitly tell the server to re-encode the database directory name to
the current encoding format:
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
After executing this statement, you can refer to the database as a-b-c
without the special #mysql50# prefix.
URL: https://dev.mysql.com/doc/refman/5.6/en/alter-database.html help alter database
4.数据库改名操作
如果我们在创建数据库的时候把数据库的名字起错了，这个时候你想要讲数据库名字改正过来。其实目前修改数据库名称基本上没有什么很好的办法，一个比较明智的做法就是讲该数据库的数据全部备份出来，然后将该库删除掉，创建你想要的数据库名称然后再把数据导入进去。还有一种非常暴力的做法就是去MySQL数据库目录下将创建错的目录进行改名操作，这种做法虽然是把数据库名称改正过来了，但是对于该库的数据字典并没有修改哟，因此这种做法我是不推荐去这样做的。
八.SQL指令详解-表的基本操作
1.MyISAM和InnoDB存储的区别概要
MyISAM表，每个表有三个文件，都位于数据库目录中
tb_name.frm:表结构定义
tb_name.MYD:数据文件
tb_name.MYI:索引文件
InnoDB表，有两种存储方式
第一种(默认方式)：每表有一个独立文件和一个多表共享的文件
tb_name.frm：表结构的定义，位于数据库目录中
ibdata#：共享的表空间文件，默认位于数据目录（datadir指向的目录）中
第二种（独立的表空间文件，推荐使用这一种方式）：
tb_name.frm：每表有一个表结构文件
tb_name.ibd：一个独立的表空间文件
应该修改innodb_file_per_table为ON，我们可以在mysql的配置文件中的[msyqld]下的字段修改它的值为NO即可完成永久生效。2.表的第一种方式，即自定义新表格式
CREATE [TEMPORARY（临时表，保存在内存中）] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
(create_definition,...)
字段的定义：字段名、类型和类型修饰符
键、索引和约束
primary key，unique key，foreign key，check
{index|key}
[table_options]
engine [=] engine_name
AUTO_INCREMENT [=] value 指定AUTO_INCREMENT的起始值
[DEFAULT] CHARACTER SET [=] charset_name 指定默认字符集
CHECKSUM [=] {0 | 1} 是否使用校验值
[DEFAULT] COLLATE [=] collation_name 排序规则
COMMENT [=] 'string' 注释
DELAY_KEY_WRITE [=] {0 | 1} 是否启用键延迟写入
ROW_FORMAT [=] {DEFAULT（默认）|DYNAMIC（动态）|FIXED（静态）|COMPRESSED（压缩）|REDUNDANT（冗余）|COMPACT（紧致）} 表格式
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 表空间
mysql> help create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| CHECK (expr)
}
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
data_type:
(see https://dev.mysql.com/doc/refman/5.6/en/data-types.html)
key_part:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
}
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENGINE [=] engine_name
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.
For information about the physical representation of a table, see
https://dev.mysql.com/doc/refman/5.6/en/create-table-files.html.
URL: https://dev.mysql.com/doc/refman/5.6/en/create-table.html查看创建表的帮助信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> create database if not exists yanhuihuang;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yanhuihuang |
+--------------------+
4 rows in set (0.00 sec)
mysql> use yanhuihuang
Database changed
mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)创建一个表包含主键的表
mysql> show table status like 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2020-11-20 17:08:26
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age)) engine='MyISAM';
Query OK, 0 rows affected (0.00 sec)
mysql> show table status like 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2020-11-20 17:09:10
Update_time: 2020-11-20 17:09:10
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified创建表的存储引擎
3.表的第二种创建方式，即复制表数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement #将select的结果来作为字段创建新表的字段，但是可能失去属性定义的
mysql> select * from t1;
Empty set (0.00 sec)
mysql> insert into t1(Name,Age) values ("yanhuihuang",18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-------------+-----+
| Name | Age |
+-------------+-----+
| yanhuihuang | 18 |
+-------------+-----+
1 row in set (0.00 sec)
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table t2 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+-------------+-----+
| Name | Age |
+-------------+-----+
| yanhuihuang | 18 |
+-------------+-----+
1 row in set (0.00 sec)
mysql> desc t2; #我们可以发现t2的表中的字段属性和t1的并不一致哟！只是数值一致而已
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)案例展示
4.表的第三种创建方式，即复制表结构
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
mysql> select database();
+-------------+
| database() |
+-------------+
| yanhuihuang |
+-------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| t1 |
| t2 |
+-----------------------+
2 rows in set (0.01 sec)
mysql> create table t3 like t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3;
Empty set (0.00 sec)
mysql> desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)用法展示
5.创建一张表的思想
我们可以自定义表，重新创建表的结构，当然也可以通过第三种方式找一个符合我们要求的表结构复制出来，然后通过insert语句将数据插入到这个心创建的表中即可
6.表的删除
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yanhuihuang |
+--------------------+
4 rows in set (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| t1 |
| t2 |
| t3 |
+-----------------------+
3 rows in set (0.00 sec)
mysql> drop table t2,t3;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| t1 |
+-----------------------+
1 row in set (0.00 sec)用法实例
7.表的修改操作
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
修改字段定义：
插入新字段：
ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
删除字段
DROP [COLUMN] col_name
修改字段
修改字段名称
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
修改字段类型及属性等
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
表改名：
rename to|as new tb_name
修改存储引擎
engine =
指定排序标准的字段
ORDER BY col_name [, col_name] ...
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t1 add ID tinyint unsigned not null;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>插入新字段用法展示
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t1 add Gender enum('boy','girl') not null default 'boy' after Age; #插入的字段我们用关键字“after”指定在“Age”之后。（注意，如果你使用first则表示插入在第一个字段）
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| Gender | enum('boy','girl') | NO | | boy | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>插入指定的位置用法展示
mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| Gender | enum('boy','girl') | NO | | boy | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table t1 drop Gender;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)删除字段案例展示
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
| ID | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t1 modify ID tinyint unsigned not null first;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| ID | tinyint(3) unsigned | NO | | NULL | |
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>修改字段类型及属性等（改变字段的位置）
mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| ID | tinyint(3) unsigned | NO | | NULL | |
| Name | varchar(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t1 change Name StudentName char(50) not null;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| ID | tinyint(3) unsigned | NO | | NULL | |
| StudentName | char(50) | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | PRI | NULL | |
+-------------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)修改字段名称
mysql> select database();
+-------------+
| database() |
+-------------+
| yanhuihuang |
+-------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| t1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> alter table t1 rename to mysql_test_table; #可以用alter命令进行修改标明
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| mysql_test_table |
+-----------------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| mysql_test_table |
+-----------------------+
1 row in set (0.00 sec)
mysql> rename table mysql_test_table to t1 #当然我们也可以直接用rename命令进行修改
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_yanhuihuang |
+-----------------------+
| t1 |
+-----------------------+
1 row in set (0.00 sec)
mysql>修改表名的两种常见的姿势
mysql> use yanhuihuang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 1
Avg_row_length: 53
Data_length: 53
Max_data_length: 14918173765664767
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2020-11-20 18:22:25
Update_time: 2020-11-20 18:22:25
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> alter table t1 engine=MyISAM;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 1
Avg_row_length: 53
Data_length: 53
Max_data_length: 14918173765664767
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2020-11-20 18:32:01
Update_time: 2020-11-20 18:32:01
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>修改表选项案例展示
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | StudentName | A | NULL | NULL | NULL | | BTREE | | |
| t1 | 0 | PRIMARY | 2 | Age | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> alter table t1 add index(StudentName);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show indexes from t1;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | StudentName | A | NULL | NULL | NULL | | BTREE | | |
| t1 | 0 | PRIMARY | 2 | Age | A | 1 | NULL | NULL | | BTREE | | |
| t1 | 1 | StudentName | 1 | StudentName | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> alter table t1 drop INDEX StudentName;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | StudentName | A | NULL | NULL | NULL | | BTREE | | |
| t1 | 0 | PRIMARY | 2 | Age | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
mysql>创建索引和删除索引用法展示
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
