2. SQL语句——DDL库表管理

一、SQL语句分类

1. DDL 数据定义语言

   对库、表、用户定义的管理
   create, drop, alter
   

2. DML 数据管理/操作语言

   对表中的数据进行操作
   insert, delete、update、select
   

3. DCL 数据控制语言

   对数据库用户、权限进行管理
   grant, revoke
   

二、数据库管理操作

1、查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL首次启动初始化生成的数据库，作用说明 :

1、information_schema
保存数据库服务器上元数据信息(版本、支持的数据类型、存储引擎、字符集)

2、mysql
保存用户名、密码、权限

3、performance_schema
保存数据服务器性能相关的数据，例如连接数、进程、线程、

4、sys
对performance_schema数据库的简化，方便数据库管理员查看

2、创建数据库

语法:

> create database 数据库名称； 

示例1:

> create database caiwu; 

示例2:

> create database jishu charset utf8;

mysql> show create database testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

说明:
MySQL 5.7默认使用的字符集是latin，不支持存储中文； 可在创建数据库时通过选项charset指定字符集为utf8，方便在表中存储中文

可通过在/etc/my.cnf配置文件中添加如下参数修改字符集：

collation-server = utf8_general_ci
character-set-server = utf8

3、删除数据库

mysql> drop database caiwu;
Query OK, 0 rows affected (0.00 sec)	

4、切换数据库

mysql> select database();
+------------+
| database() |
+------------+
| testdb     |
+------------+
1 row in set (0.00 sec)

mysql> use game;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| game       |
+------------+
1 row in set (0.00 sec)

三、数据表操作

1、创建表

语法:

> create table 表名(字段名称 数据类型 [属性], 字段名称 数据类型 [属性], .........)

mysql> use game;
Database changed

mysql> create table account(
    -> id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    -> name CHAR(20) NOT NULL,
    -> password CHAR(30) NOT NULL,
    -> level INT UNSIGNED default 1);
Query OK, 0 rows affected (0.00 sec)

1) 数据类型

数字
整数， int， tinyint, smallint, int, mediumint, bigint
无符号整数 int unsigned
浮点数, float(5,3), double(5, 3)

字符
char(20), varchar(20), 最长支持255个字符
text 文本, 最长支持65535个字符
enum(“男”, “女”) 枚举

日期时间
datetime YYYY-MM-DD HH:MM:SS
date YYYY-MM-DD

2) 属性

• primary key 主键

• auto_increment 自动增长

• not null 不允许为空

• unique key 惟一键

• default “数据” 默认值

2、查看表

mysql> show tables;
+----------------+
| Tables_in_game |
+----------------+
| account        |
| tb01           |
+----------------+
2 rows in set (0.00 sec)

3、查看表结构

mysql> desc account;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| name     | char(20)         | NO   |     | NULL    |                |
| password | char(30)         | NO   |     | NULL    |                |
| level    | int(10) unsigned | YES  |     | 1       |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

4、删除表

mysql> drop table tb01;

5、表对应的数据文件

• xxxx.frm
  表结构文件，存储表结构 (字段名称、数据类型、属性)

• xxxx.ibd
  表数据文件，存储数据、索引

四、MySQL存储引擎 storage engine

支持多种不同的存储引擎，存储引擎不同，MySQL数据库支持的功能、特性是不一样的

1、查看所有的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2、MyISAM存储引擎

查询性能好
适用的场景: 适用于查多写少的场景
支持表级锁
一张表会映射出三个文件
xxx.frm 表结构文件
xxx.MYD 表数据文件
xxx.MYI 表索引文件

3、InnoDB存储引擎

支持行级锁
支持外键 Foreign Key
支持事务 Transaction， 针对写操作，确保数据安全性
一张表映射出2个文件
xxx.frm 表结构文件
xxx.ibd 表数据文件

4、修改数据库默认的存储引擎

• /etc/my.cnf

[mysqld]
default-storage-engine=InnoDB