sql语句之DDL数据定义语言

SQL语句

一、sql语句的语义种类

DDL: 数据定义语言  Data Definition Language
DCL: 数据控制语言  Data Control Language
DML: 数据操作语言  Data Manipulate Language
DQL: 数据查询语言  Data Query Language

二、DDL: 数据定义语言（create、drop）

1.create针对库的操作

1）语法

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

2）创建库

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create SCHEMA db2;
Query OK, 1 row affected (0.00 sec)

3）建库时提示已存在

mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists

mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)

4）查看建库语句

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

5）创建数据库并指定字符集

mysql> create database db3 charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

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

#不指定校验规则默认就是 utf8_general_ci
mysql> create database db4 charset utf8;
Query OK, 1 row affected (0.00 sec)

6）删库

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

7）修改库

mysql> show create database db2;			# 查看数据库使用字符集

mysql> alter database db2 charset utf8 collate utf8_general_ci;          # 修改字符集

2.create针对表的操作

1）语法

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) }

2）建表

#1.进入一个库
mysql> use db2

#2.查看当前所在库
mysql> select database();

#3.建表，建表最少有一列
mysql> create table tb1;
ERROR 1113 (42000): A table must have at least 1 column

mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)

#4.查看表
mysql> show tables;		   # 查看库中有多少表

mysql> desc tb1;			# 查看表中字段

3）数据类型

int				整数  -2^31 - 2^31-1    (-2147483648 - 2147483647)   不能超出此范围
bigint			较大整数数据类型 （-2^63 - 2^63-1）	比int整数类型可用范围广
tinyint			最小整数   -128 - 127	#年龄  0 - 255
varchar			字符类型（变长）	#身份证
char			字符类型（定长）
enum			枚举类型	#给它固定选项，只能选则选择项中的值    性别
datetime		时间类型	年月日时分秒
注意： 如果使用datetime不是为now()当前时间，而是直接书写时间，如2020-03-01，则需要在配置文件中添加此行信息sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
否则数据可能插入完成后，并不会显示插入后的内容，而是00-00-00.

4）数据类型测试

#int类型
mysql> create table tb1(id int);			# 创建表名为tb1，字段名为id，类型为int

mysql> insert tb1 values(1);				# 在tb1表中插入对应id字段的值为1

#enum类型
mysql> create table qiudao(id int,sex enum('nan','nv'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into qiudao values(1,'nan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into qiudao values(1,'qiudao');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

5）建表测试

表名：student
id
name
age
gender
cometime

#1.建表
mysql> create table student(
    -> id int,
    -> name varchar(12),
    -> age tinyint,
    -> gender enum('M','F'),
    -> cometime datetime);
Query OK, 0 rows affected (0.01 sec)

#2.插入数据
mysql> insert into student values(1,'邱导',-18,'M',now());
Query OK, 1 row affected (0.00 sec)

6）建表数据属性

not null： 			#非空
primary key： 		#主键（唯一且非空的）
auto_increment： 	#自增（此列必须是：primary key或者unique key）
unique key： 		#唯一键，单独的唯一的
default： 			#默认值
unsigned： 			#非负数
comment： 			#注释

7）加上属性建表

#1.建表语句
create table students(
id int primary key auto_increment comment "学生id",
name varchar(12) not null comment "学生姓名",
age tinyint unsigned not null comment "学生年龄",
gender enum('M','F') default 'M' comment "学生性别",
cometime datetime default now() comment "入学时间");

#2.查看建表语句
mysql> show create table students;
| students | CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(12) NOT NULL COMMENT '学生姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别',
  `cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                   |
1 row in set (0.00 sec)

#3.插入数据
mysql> insert into students values(1,'qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#因为主键相同无法插入
mysql> insert into students values(1,'qiudao',18,'M',now());
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#应该
mysql> insert into students values('2','qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#主键已经设置自增没必要自己插入

#正规插入数据的写法
mysql> insert students(name,age) values('lhd',18);
Query OK, 1 row affected (0.00 sec)

mysql> insert students(name,age) values('lhd',12);
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | qiudao |  18 | M      | 2020-07-14 19:51:44 |
|  2 | qiudao |  18 | M      | 2020-07-14 19:52:19 |
|  3 | lhd    |  18 | M      | 2020-07-14 19:53:50 |
|  4 | lhd    |  12 | M      | 2020-07-14 19:53:58 |
+----+--------+-----+--------+---------------------+
4 rows in set (0.00 sec)

3.删除表

drop table student;

4.修改表

#1.新建表
mysql> create table linux(daijiadong tinyint);
Query OK, 0 rows affected (0.04 sec

#2.修改表名
mysql> alter table linux rename linux9;
Query OK, 0 rows affected (0.01 sec)

#3.插入新字段
mysql> alter table linux9 add rengyufeng int;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| daijiadong | tinyint(4) | YES  |     | NULL    |       |
| rengyufeng | int(11)    | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#4.插入多个新字段
mysql> alter table linux9 add liukong int,add wangzhangxing int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| daijiadong    | tinyint(4) | YES  |     | NULL    |       |
| rengyufeng    | int(11)    | YES  |     | NULL    |       |
| liukong       | int(11)    | YES  |     | NULL    |       |
| wangzhangxing | int(11)    | YES  |     | NULL    |       |
+---------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#5.插入字段到最前面
mysql> alter table linux9 add kangpeiwen varchar(100) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |
| daijiadong    | tinyint(4)   | YES  |     | NULL    |       |
| rengyufeng    | int(11)      | YES  |     | NULL    |       |
| liukong       | int(11)      | YES  |     | NULL    |       |
| wangzhangxing | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

#6.插入字段到指定字段后面
mysql> alter table linux9 add chenjianqing varchar(100) after daijiadong;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |
| daijiadong    | tinyint(4)   | YES  |     | NULL    |       |
| chenjianqing  | varchar(100) | YES  |     | NULL    |       |
| rengyufeng    | int(11)      | YES  |     | NULL    |       |
| liukong       | int(11)      | YES  |     | NULL    |       |
| wangzhangxing | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

#7.删除指定列
mysql> alter table linux9 drop daijiadong;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |
| chenjianqing  | varchar(100) | YES  |     | NULL    |       |
| rengyufeng    | int(11)      | YES  |     | NULL    |       |
| liukong       | int(11)      | YES  |     | NULL    |       |
| wangzhangxing | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#8.修改字段
mysql> alter table linux9 change rengyufeng congtianqi int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |
| chenjianqing  | varchar(100) | YES  |     | NULL    |       |
| congtianqi    | int(11)      | YES  |     | NULL    |       |
| liukong       | int(11)      | YES  |     | NULL    |       |
| wangzhangxing | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#9.修改字段属性
mysql> alter table linux9 modify congtianqi tinyint;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linux9;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| kangpeiwen    | varchar(100) | YES  |     | NULL    |       |
| chenjianqing  | varchar(100) | YES  |     | NULL    |       |
| congtianqi    | tinyint(4)   | YES  |     | NULL    |       |
| liukong       | int(11)      | YES  |     | NULL    |       |
| wangzhangxing | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

三、复制表结构

# 对数据库的表结构拷贝
mysql> create table ttt like student;                              # 创建一个新的表叫ttt，和student的表结构一致，但不会将student的数据拷贝过来
Query OK, 0 rows affected (0.10 sec)

# 对数据库数据拷贝
mysql> insert into ttt select * from student;                      # 将查询出来的结果，全部导入到ttt表中

# 对导入数据筛选
mysql> insert into ttt select * from student where age > 22;       # 对筛选结果导入到表ttt中

posted @ 2020-07-15 08:12 元气少女郭德纲！！阅读(355) 评论(0) 编辑收藏举报

刷新页面返回顶部

永不止步

sql语句之DDL数据定义语言

SQL语句

一、sql语句的语义种类

二、DDL: 数据定义语言（create、drop）

1.create针对库的操作

1）语法

2）创建库

3）建库时提示已存在

4）查看建库语句

5）创建数据库并指定字符集

6）删库

7）修改库

2.create针对表的操作

1）语法

2）建表

3）数据类型

4）数据类型测试

5）建表测试

6）建表数据属性

7）加上属性建表

3.删除表

4.修改表

三、复制表结构

公告

永不止步

sql语句之DDL数据定义语言

SQL语句

一、sql语句的语义种类

二、DDL: 数据定义语言 （create、drop）

1.create针对库的操作

1）语法

2）创建库

3）建库时提示已存在

4）查看建库语句

5）创建数据库并指定字符集

6）删库

7）修改库

2.create针对表的操作

1）语法

2）建表

3）数据类型

4）数据类型测试

5）建表测试

6）建表数据属性

7）加上属性建表

3.删除表

4.修改表

三、复制表结构

公告

二、DDL: 数据定义语言（create、drop）