1. create 操作库
1）查看语法
mysql> help create database;
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2）创建库
#1.建库
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)
#2.建库2
mysql> create schema db02;
Query OK, 1 row affected (0.00 sec)
#3.创建已存在的库不报错
mysql> create database db01;
ERROR 1007 (HY000): Can't create database 'db01'; database exists
mysql> create database IF NOT EXISTS db01;
Query OK, 1 row affected, 1 warning (0.00 sec)
3）查看建库语句
mysql> show create database db01;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
4）创建库指定字符集和校验规则
#1.指定字符集建库
mysql> create database db03 charset utf8;
Query OK, 1 row affected (0.00 sec)
#2.指定字符集和校验规则建库
mysql> create database db04 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> create database db06 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
5）修改库
#只需要修改字符集和校验规则
mysql> alter database db05 character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db05;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| db05 | CREATE DATABASE `db05` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
6）删库
mysql> drop database db05;
Query OK, 0 rows affected (0.00 sec)
2. create 操作表
1）查看语法
mysql> help create table;
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) }
2）建表
#1.进入库
mysql> use test;
Database changed
#2.查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
#3.建表
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.01 sec)
3）数据类型
int 整数 -2^31 --- 2^31-1 (-2147483648 - 2147483647)
tinyint 最小整数 -128 --- 127 #一般用在定义年龄
varchar 字符类型（变长） #身份证或者名字
char 字符类型（定长）
enum 枚举类型 #性别
datetime 时间类型
4）测试int数据类型
#1.建表
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.01 sec)
#2.指定字符集建表
mysql> create table tb2(id int) charset utf8;
Query OK, 0 rows affected (0.01 sec)
#3.查看建表语句
mysql> show create table tb2;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| tb2 | CREATE TABLE `tb2` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
#4.查看表结构
mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
#5.插入数据
mysql> insert tb1 values('11111111111');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values('-2147483648');
Query OK, 1 row affected (0.00 sec)
mysql> insert tb1 values('2147483648');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values('-2147483649');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert tb1 values('2147483647');
Query OK, 1 row affected (0.00 sec)
#原因：插入的数据超过数据类型的长度
5）测试char和varchar类型
#1.建表
mysql> create table tb1(id int,name varchar(12));
Query OK, 0 rows affected (0.06 sec)
#2.插入数据
mysql> insert into tb1 values('1','邱导');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 values('1','zengzhigaoxiang');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
#字符超过数据类型的限制值
6）测试enum类型
#1.建表
mysql> create table tb4(id int,name varchar(10),sex enum('man','woman'));
Query OK, 0 rows affected (0.03 sec)
#2.插入数据
mysql> insert into tb4 values('1','邱导','man');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb4 values('2','刘大维','nan');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
#如果是枚举类型，必须插入类型指定的值，不能插入额外的值
7）建表测试
#建学生表
id
name
sex
age
cometime
#1.建表
mysql> create table student(id int,name varchar(10),sex enum('男','nv'),age tinyint,cometime datetime);
Query OK, 0 rows affected (0.03 sec)
#2.插入数据
mysql> insert into student values('-12573','邱导','男','-18',now());
Query OK, 1 row affected (0.00 sec)
#3.查看数据
mysql> select * from student;
+--------+--------+------+------+---------------------+
| id | name | sex | age | cometime |
+--------+--------+------+------+---------------------+
| -12573 | xxxx | 男 | -18 | 2020-10-23 18:40:15 |
+--------+--------+------+------+---------------------+
1 row in set (0.00 sec)
8）建表的数据属性
not null： 非空
primary key： 主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 唯一键（单独的唯一的）
default： 默认值
unsigned： 非负数
comment： 注释
9）删除表
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
10）修改表
#1.修改表字符集
mysql> alter table play charset utf8mb4;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#2.修改表名 将play表修改为student表
mysql> alter table play rename student;
Query OK, 0 rows affected (0.01 sec)
#3.给表插入字段 给student表插入name列
mysql> alter table student add name varchar(10);
#4.给表插入多个字段
mysql> alter table student add sex enum('男','女'),add age tinyint;
#5.插入字段到第一列
mysql> alter table student add id int first;
#6.插入字段到指定字段后面
mysql> alter table student add class varchar(12) after id;
#7.删除字段
mysql> alter table student drop qiudao;
#8.修改列属性
mysql> alter table student modify name varchar(20);
#9.修改列的名字和属性
mysql> alter table student change sex xingbie enum('男','女');
#10.修改库的字符集
mysql> alter database taobao charset utf8mb4;