3.MySQL-DDL语句建库、建表-修改表结构-数据类型-添加数据类型选项-临时表-copy table-列选项-表选项-存储引擎-约束-主键-字符集-外键

MySQL-DDL语句建库、建表-修改表结构-数据类型-添加数据类型选项-临时表-copy table-列选项-表选项-存储引擎-约束-主键-字符集-外键

Mysql操作官方文档

MySQL :: MySQL 5.7 Reference Manual

一、使用DDL语句创建库、表

1.1 Create建库

root@(none) 14:42  mysql>create database zkj
    -> create database zkj;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create database zkj' at line 2
root@(none) 14:47  mysql>create database zkj;
Query OK, 1 row affected (0.01 sec)

root@(none) 14:47  mysql>show create database zkj;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| zkj      | CREATE DATABASE `zkj` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.01 sec)

1.2 Create建表

关键字（Mysql里的命令） 不区分大小写

中括号内 可省略

root@(none) 14:47  mysql>use zkj;	#使用zkj库
Database changed
root@zkj 14:57  mysql>show tables;	#查看库里有什么表
Empty set (0.00 sec)

root@zkj 14:57  mysql>create table z(id int, name varchar(20));		#创建表z varchar--可变长字符串类型
Query OK, 0 rows affected (0.02 sec)

root@zkj 14:59  mysql>show tables;
+---------------+
| Tables_in_zkj |
+---------------+
| z             |
+---------------+
1 row in set (0.00 sec)

root@zkj 14:59  mysql>desc z;	#查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

root@zkj 15:09  mysql>insert into z(id,name) values(1, 'zengkaijie');	# insert 是往表里插入数据
Query OK, 1 row affected (0.01 sec)

root@zkj 15:09  mysql>insert into z(id,name) values(2, 'tanghaomin');
Query OK, 1 row affected (0.01 sec)

root@zkj 15:09  mysql>select * from z;	# select--是查询表操作 *对应的是表的字段，代表任意字段 from--来自（哪个表）
+------+------------+
| id   | name       |
+------+------------+
|    1 | zengkaijie |
|    2 | tanghaomin |
+------+------------+
2 rows in set (0.01 sec）

# 注意：查询表中的几个字段 需要用逗号隔开
root@zkj 15:09  mysql>select id name from z;
+------+
| name |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

root@zkj 15:12  mysql>select id,name from z;
+------+------------+
| id   | name       |
+------+------------+
|    1 | zengkaijie |
|    2 | tanghaomin |
+------+------------+
2 rows in set (0.00 sec)

示例：建库、建表

root@(none) 15:17  mysql>show daatabases;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'daatabases' at line 1
root@(none) 15:17  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

root@(none) 15:18  mysql>create database sc;
Query OK, 1 row affected (0.00 sec)

root@(none) 15:18  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sc                 |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

root@(none) 15:18  mysql>use sc
Database changed
root@sc 15:18  mysql>show tables;
Empty set (0.00 sec)

root@sc 15:18  mysql>create table student_info;
ERROR 1113 (42000): A table must have at least 1 column
root@sc 15:18  mysql>create table student_info(id int,name varchar(20),sex char(1));
Query OK, 0 rows affected (0.01 sec)

root@sc 15:19  mysql>show tables;
+--------------+
| Tables_in_sc |
+--------------+
| student_info |
+--------------+
1 row in set (0.00 sec)

root@sc 15:19  mysql>desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

primary key 表示这个字段为主键列 不允许出现重复数据

not null 表示这个字段不允许为空

integer （int）整数

decimal(7,2) 定点型 – 长度为7，保留2位小数

浮点型 float

1.3 获取Create操作方法

root@zkj 15:12  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
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
}

data_type:
    (see https://dev.mysql.com/doc/refman/5.7/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'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | 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]
        [(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]

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.7/en/create-table-files.html.

# 官方文档
URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

1.4 Mysql删除表、库

# 删除表
root@zkj 15:14  mysql>drop table z;
Query OK, 0 rows affected (0.01 sec)
# 删除库
root@mysql 16:07  mysql>drop database zkj;
Query OK, 0 rows affected (0.01 sec)

二、Mysql修改表结构

help alter table --》查看帮助

2.1 增加表字段

2.2 删除字段

drop

2.3 修改字段名

三、Mysql的数据类型

3.1 列的数据类型

varchar char

可变长字符串

固定字符串

增加两行数据

四、添加数据类型选项

4.1 添加数据 – 自动增长 – int

删除数据

IF NOT EXISTS选项

创建临时表

临时表：只是临时放在内存里的

show tables不能看见临时表，单能对临时表进行操作

copy table

列选项 – DEFAULT

列选项 – Comment

表选项 – Engine

innodb 存储引擎支持事务 外键 行锁

存储引擎

show engines；

约束

唯一性约束，不允许出现一样的，但是可以出现很多NULL值

主键（待补充）

字符集（待补充）

外键（待补充）