MySQL建表和增删改相关SQL语句

MySQL建表和增删改相关SQL语句

#使用test逻辑空间
USE test;
#新建表
CREATE TABLE student(
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex CHAR(1) NOT NULL,
birthday DATE NOT NULL,
tel CHAR(11) NOT NULL,
remark VARCHAR(200)
);
#往表中字段插入value值
INSERT INTO student VALUES(1,"李强","男","1995-05-15","15240543995",NULL);
#显示当前逻辑空间所有的表
show TABLES;
#显示student表详情
DESC student;
#显示建student表语句
SHOW CREATE TABLE student;
#删除student表
DROP TABLE student; 
#增加student表字段
ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;
#修改表字段
ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL;
#修改表字段名字address修改成home_address
ALTER TABLE student
CHANGE address home_address VARCHAR(200) NOT NULL;
#删除表字段 
ALTER TABLE student
DROP home_address,
DROP home_tel;

Navicat 数据库导航视图如下：

WechatIMG26.jpeg WechatIMG27.jpeg