sql语句的语义
DDL：Data Definition Language #数据定义语言 （create、alter）
DCL：Data Control Language #数据控制语言 （grant、revoke、commit、rollback）
DML：Data Manipulate Language #数据操作语言 （insert、delete、update）
DQL：Data Query Language #数据查询语言 （select、desc）
DDL --数据定义语言
1.create 操作库
查看语法
|
1 2 3 4 5 6 |
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 |
创建库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#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) |
查看建库语句
|
1 2 3 4 5 6 7 |
mysql> show create database db01; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) |
创建库指定字符集和校验规则
|
1 2 3 4 5 6 7 8 9 10 |
#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) |
修改库
|
1 2 3 4 5 6 7 8 9 10 11 |
#只需要修改字符集和校验规则 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) |
删库
|
1 2 |
mysql> drop database db05; Query OK, 0 rows affected (0.00 sec) |
2. create 操作表
建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#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) |
数据类型
|
1 2 3 4 5 6 |
int 整数 -2^31 --- 2^31-1 (-2147483648 - 2147483647) tinyint 最小整数 -128 --- 127 #一般用在定义年龄 varchar 字符类型（变长） #身份证或者名字 char 字符类型（定长） enum 枚举类型 #性别 datetime 时间类型 |
测试int数据类型
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
#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) #原因：插入的数据超过数据类型的长度 |
测试char和varchar类型
|
1 2 3 4 5 6 7 8 9 10 11 |
#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 #字符超过数据类型的限制值 |
测试enum类型
|
1 2 3 4 5 6 7 8 9 10 11 |
#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 #如果是枚举类型，必须插入类型指定的值，不能插入额外的值 |
建表测试
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#建学生表 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 | 邱导 | 男 | -18 | 2020-10-23 18:40:15 | +--------+--------+------+------+---------------------+ 1 row in set (0.00 sec) |
建表的数据属性
|
1 2 3 4 5 6 7 |
not null： 非空 primary key： 主键（唯一且非空的） auto_increment： 自增（此列必须是：primary key或者unique key） unique key： 唯一键（单独的唯一的） default： 默认值 unsigned： 非负数 comment： 注释 |
加上数据属性建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
#1.建表 mysql> create table student( -> id int unsigned primary key auto_increment comment '学生id', -> name varchar(10) not null comment '学生姓名', -> sex enum('男','女') default '男' comment '学生性别', -> age tinyint unsigned comment '学生年龄', -> cometime datetime default now() comment '入学时间', -> class varchar(12) not null comment '学生班级', -> status enum('0','1') default 1 comment '学生状态'); Query OK, 0 rows affected (0.01 sec) #2.查看建表语句 mysql> show create table student; | student | CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', `name` varchar(10) NOT NULL COMMENT '学生姓名', `sex` enum('男','女') DEFAULT '男' COMMENT '学生性别', `age` tinyint(3) unsigned DEFAULT NULL COMMENT '学生年龄', `cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', `class` varchar(12) NOT NULL COMMENT '学生班级', `status` enum('0','1') DEFAULT '0' COMMENT '学生状态', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 1 row in set (0.00 sec) #3.插入数据 mysql> insert into student(name,class) values('邱到','运维组'); Query OK, 1 row affected (0.00 sec) mysql> insert into student(name,class) values('刘大维','运维组'); Query OK, 1 row affected (0.00 sec) mysql> insert into student(name,class) values('虞陈宇','运维组'); Query OK, 1 row affected (0.00 sec) mysql> insert into student(name,age,class) values('周昊','18','运维组'); Query OK, 1 row affected (0.00 sec) #4.查看数据 mysql> select * from student; +----+-----------+------+------+---------------------+------------+--------+ | id | name | sex | age | cometime | class | status | +----+-----------+------+------+---------------------+------------+--------+ | 1 | 邱到 | 男 | NULL | 2020-10-23 19:06:25 | 运维组 | 0 | | 2 | 邱到 | 男 | NULL | 2020-10-23 19:07:31 | 运维组 | 0 | | 3 | 刘大维 | 男 | NULL | 2020-10-23 19:07:57 | 运维组 | 0 | | 4 | 虞陈宇 | 男 | NULL | 2020-10-23 19:08:17 | 运维组 | 0 | | 5 | 周昊 | 男 | 18 | 2020-10-23 19:09:44 | 运维组 | 0 | +----+-----------+------+------+---------------------+------------+--------+ 5 rows in set (0.00 sec) |
删除表
|
1 2 |
mysql> drop table student; Query OK, 0 rows affected (0.00 sec) |
修改表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
#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; |