mysql表生成doris建表语句 mysql怎么创建表语句

转载

云端行者 2024-07-04 22:43:25

文章标签 mysql表生成doris建表语句主键外键表名 文章分类 MySQL 数据库

事物---功能---用途---实现条件---实现步骤/操作步骤（注意事项）---问题---解决

对已经创建的表进行添加、修改、删除、约束操作、数据类型、ddl语句、约束

DDL语句DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

库的管理

语法

创建库

CREATE DATABASE dbname;

查看已经存在的数据库

SHOW DATABASES;

显示数据库创建语句

SHOW CREATE DATABASE db_name;

选择数据库

USE dbname;

查看数据库中的表

SHOW TABLES;

删除数据库

DROP DATABASE [IF EXISTS] dbname;IF EXISTS判断是否存在而删除

建立一个数据库并制定编码格式

create database 数据库名 default charset utf8 collate utf8_general_ci;

修改、备份、恢复数据库

备份

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

导入

mysql>source 导入的文件名;

表的管理

创建表

CREATE TABLE IF NOT EXISTS stuinfo(
 stuId INT, 
stuName VARCHAR(20), 
gender CHAR, 
bornDate DATETIME 
);

修改表

ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名【字段类型】;
添加新列及其定义放在ADD COLUMN子句之后。请注意，COLUMN关键字是可选的，因此可以省略它。

修改字段名

ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;

修改表名

ALTER TABLE stuinfo RENAME [TO] studentinfo;
TO 为可选参数，使用与否均不影响结果。

修改表的字符集

altertable student character set utf8；

修改字段类型和列级约束

ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

添加字段

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

给表添加注释

ALTER TABLE `mydb1`.`employees` COMMENT='员工表';

`mydb1`.`employees`：这个可以用鼠标点2下数据库然后’.‘调用出表名

删除字段

ALTER TABLE studentinfo DROP COLUMN email;

删除表

DROP TABLE [IF EXISTS] studentinfo;

查看表结构（以表格形式展示表结构）

DESCRIBE <表名>;

以SQL语句的形式展示表结构

SHOW CREATE TABLE <表名>;

注意事项

创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象
添加新列及其定义放在ADD COLUMN子句之后。请注意，COLUMN关键字是可选的，因此可以省略它。
第三，MySQL允许通过指定FIRST关键字将新列添加到表的第一列。它还允许您使用AFTER existing_column子句在现有列之后添加新列。如果没有明确指定新列的位置，MySQL会将其添加为最后一列。

约束

常见约束

NOT NULL

非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。

DEFAULT

默认值，用于保证该字段有默认值。例如学生表的学生性别。

UNIQUE

唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。

CHECK

检查约束（MySql不支持），检查字段的值是否为指定的值。

PRIMARY KEY

主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。

FOREIGN KEY

外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号

列级约束

作用在一个列上
定义方式：列约束必须跟在列的定义里后面

表级约束除了非空、默认其他都支持

可以作用在多个列，也可以作用在一个列
定义方式：表约束不与列一起，而是单独定义。

实现步骤

创建表时添加约束

表级约束

语法: 
在各个字段的最下面 CONSTRAINT 约束名 约束类型(字段名) 
DROP TABLE IF EXISTS stuinfo; 
CREATE TABLE stuinfo( 
id INT, 
stuname VARCHAR(20), 
sex CHAR(1), 
seat INT, 
age INT, 
majorid INT, 
# 约束 
CONSTRAINT ys PRIMARY KEY(id),# 主键 
CONSTRAINT uq UNIQUE(seat),# 唯一键 
CONSTRAINT ck CHECK(sex='男'OR sex='女'),# 检查 
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)# 外键  );

列级约束

语法：
直接在字段名和类型后面追加约束类型即可 mysql只支持：默认、非空、主键、唯一 
USE students; 
CREATE TABLE stuinfo( 
id INT PRIMARY KEY,#主键 
stuName VARCHAR(20) NOT NULL,#姓名 
sex CHAR(1) CHECK(sex='男'OR sex='女'),
#性别 seat INT UNIQUE,#唯一 
age INT DEFAULT 19,#默认约束 
majorID INT /foreign key/ REFERENCES marjor(id)#外键 ); 
CREATE TABLE major( id INT PRIMARY KEY, majorName VARCHAR(20) )

修改表时

添加列级约束

alter table 表名 modify column 字段名字段类型新约束

添加表级约束

alter table 表名 add [ constraint 约束名] 约束类型 (字段名) [外键的引用]

添加约束

USE students; DROP TABLE IF EXISTS stuinfo;
 CREATE TABLE stuinfo(
 id INT, stuname VARCHAR(20), 
sex CHAR(1),
seat INT, 
age INT, 
majorid INT ) 
DESC stuinfo; 

1.添加非空约束 
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; 
#约束 
CONSTRAINT ys PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键 
CONSTRAINT ck CHECK(sex='男'OR sex='女'),#检查 
CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)#外键，references指向主键
2.添加默认约束 
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18; 
3.添加主键 
#列级约束 
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY; 
#表级约束 ALTER TABLE stuinfo ADD PRIMARY KEY(id); 
4.添加唯一键 
#列级约束 
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; 
#表级约束 
ALTER TABLE stuinfo ADD UNIQUE(seat); 
5.添加外键 
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

删除约束

#1.删除非空约束 
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL; 
#2.删除默认约束 
ALTER TABLE stuinfo MODIFY COLUMN age INT; 
#3.删除主键 
ALTER TBALE stuinfo DROP PRIMARY KEY; 
#4.删除唯一 
ALTER TABLE stuinfo DROP INDEX seat; 
#5.删除外键 
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; 

SHOW INDEX FROM stuinfo;

注意事项

在什么时候创建约束：创建表时；修改表时；约束的添加分类。
非空的约束只能定义在列上
请注意，COLUMN关键字是可选的，因此可以省略它。

1.查询约束：SELECT * FROM information_schema.`TABLE_CONSTRAINTS`;(可以通过where table_name=‘student‘来约定只查询某个表的约束)其实每次添加约束都是将添加的约束的信息存储到了information_schema这个schema的table_constraints表里；(mysql里``和bash的很像，也是执行``内的表达式而不是将里面作为字符串使用)
2.查看所有的触发器：select* from information_schema.triggers;

外键

用于限制两个表的关系，从表的字段值引用了主表的某字段值，外键列和主表的被引用列要求类型一致，意义一样,名称无要求；主表的被引用列要求是一个key (一般就是主键)；插入数据，先插入主表删除数据，先删除从表

数据类型

主要包括以下五大类

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

具体看这个去吧（菜鸟教程）

代码练习：

#选择到star数据库
USE star;
#创建一个student表
CREATE TABLE student(
    #定义id为int类型并设置主键
    id INT PRIMARY KEY,
    #定义name为varchar类型，位数为50，不能为空】
    NAME VARCHAR(50) NOT NULL,
    #定义age为int类型，位数为3
    age INT(3),
    #定义address为varchar类型，位数100
    address VARCHAR(100),
    #定义birth为datetime类型
    birth DATETIME    
)#设置储存引擎，设置编码格式
ENGINE=INNODB DEFAULT CHARSET=utf8;
#查询一个刚才创建的表   select * from 表名;
SELECT * FROM student;
#修改表名    rename table 表名 to 修改的表名;
RENAME TABLE student TO sys_student;
#查询修改后的表名看修改成功没
SELECT * FROM sys_student;#修改成功

#alter:对已经创建的表进行添加、修改、删除、约束操作

#增加列      语法：alter table 表名 add 列名 数据类型;
ALTER TABLE sys_student ADD sex CHAR(2);

#修改列   语法：alter table 表名 modify 要修改的列名 修改的数据类型;
ALTER TABLE sys_student MODIFY address VARCHAR(150) NOT NULL;

#删除列   语法：alter table 表名 drop 要删除的列名
ALTER TABLE sys_student DROP sex;
#查询下表看删除了没有
SELECT * FROM sys_student;#sex列已经被删除了



SELECT * FROM employees;

#创建员工表，并添加外键约束
CREATE TABLE employees(
employee_id INT,
first_name VARCHAR(50),
job_id VARCHAR(50) NOT NULL,
#定义一个salary，类型为double
salary DOUBLE,
dept_id INT,

 /*    约束英文：constraint
    约束实际上就是表中数据的限制条件
    表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)
    
    在定义完所有列之后指定主键，语法：constraint 约束的名字 约束类型 (作用列名)*/
    
#emp_id_pk是约束名字，employee_id是约束的列，primary key:主键约束     
CONSTRAINT emp_id_pk PRIMARY KEY(employee_id),

/*MySQL提供了另一种称为UNIQUE索引的索引，它允许您在一个或多个列中强制实现值的唯一性。与PRIMARY KEY索引不同，每个表可以有多个UNIQUE索引。
*/
#给first_name列约束值唯一
CONSTRAINT emp_first_name_uk UNIQUE(first_name),

/*CHECK <表达式>
    “表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。
    注意：若将CHECK约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。*/
    
#给salary约束值不能为负数
CONSTRAINT emp_salary_min CHECK(salary>0),

/*REFERENCES对象权限。
建立外键关系权限。
用户要在tb1上建立外键，外键指向tb2，那么该用户必须在tb2上有REFERENCES权限。
外键首先是表中的一个字段，它可以不是本表的主键 但对应另外一个表的主键！

主表与从表：若同一个数据库中，b表的外键与a表的主键相对应，则a表为主表，b表为从表
*/

#创建外键的语法：  [constraint<外键名>] FOREIGN KEY 字段1[,字段名2,...]
          #references<主键名> 主键列1[,主键列2,...]
#外键名 为定义外键约束的名称；字段名 表示子表需要添加外键约束的子段列

#给dept_id列添加外键并和主键建立关系，用references建立
CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id)REFERENCES department(dept_id)

)



#创建主表
CREATE TABLE department(
dept_id INT,
dept_name VARCHAR(50),
#在定义完所有列之后指定主键，语法：[constraint<约束名>] primary key[字段名]
CONSTRAINT dept_name_uk PRIMARY KEY(dept_id),
#用给(约束)给dept_name列约束唯一(UNIQUE)
CONSTRAINT dept_name_uk UNIQUE(dept_name)

)

SELECT *FROM employees;
SELECT * FROM department;

#delete删除 where条件   语法：delete from 表名 where 条件
#从employees表中删除employee_id 为1的行
DELETE FROM employees WHERE employee_id=1;
DELETE FROM employees WHERE salary;
#alter:对已经创建的表进行添加（add）、修改（modify）、删除(drop)、约束操作
SELECT * FROM employees;
#增加一个salary列，类型double
ALTER TABLE employees ADD salary DOUBLE;
#删除empoyees下的salary列
ALTER TABLE employees DROP salary;
#修改salary列类型为int not null
ALTER TABLE employees MODIFY salary INT NOT NULL;
#在department表里添加dept_count列并设置类型为int
ALTER TABLE department ADD dept_count INT;


#添加约束：  ALTER TABLE <表名> ADD <列定义>|<完整性约束>。
#由于使用此方式中 增加的新列 自动填充null值，所以不能为增加的新列指定not null约束。

#约束唯一  constraint 约束名 约束(字段名)
ALTER TABLE department ADD CONSTRAINT dept_count_uk UNIQUE(dept_count);

#添加外键为department表里的字段，并和department里的主键（dept_id）建立联系
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id)REFERENCES department(dept_id);
SELECT 
#删除外键约束名
ALTER TABLE employees DROP FOREIGN KEY emp_dept_fk;
#删除外键约束列
ALTER TABLE employees DROP dept_id;

SHOW CREATE TABLE employees;#show create table 表名：查看表的详细信息，
SHOW CREATE TABLE department;
#以表格形式展现结构：DESCRIBE <表名>;
DESCRIBE department;
DESCRIBE employees;

 

#查询约束：SELECT * FROM information_schema.`TABLE_CONSTRAINTS`;(可以通过where table_name=‘student‘来约定只查询某个表的约束)

#其实每次添加约束都是将添加的约束的信息存储到了information_schema这个schema的table_constraints表里；(mysql里``和bash的很像，也是执行``内的表达式而不是将里面作为字符串使用)

#查询定义约束的列
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE TABLE_NAME='employees';
#查询定义约束的列
SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'EMPLOYEES';

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。