MySQL给表字段添加注释、查询、修改、添加字段等SQL

创建表:
create table teacher
(
     t_id varchar(8) ,
   t_name varchar(12),
   t_age int(6),
   t_title varchar(16),
   primary key (t_id)  
)
---------------------------------------
删除表:
drop table teacher
---------------------------------------
查询信息:
select * from teacher
-------------------------------------------------------------------------------------------
添加信息:
insert into teacher (t_id,t_name,t_age,t_title) value ('0001','李丽',28,'初级讲师');
insert into teacher (t_id,t_name,t_age,t_title) value ('0002','李丽',28,'初级讲师');
insert into teacher (t_id,t_name,t_age,t_title) value ('0003','王伟',32,'高级讲师');
insert into teacher (t_id,t_name,t_age,t_title) value ('0006','牛莉',32,'高级讲师');
insert into teacher (t_id,t_name,t_age,t_title,t_intro) value ('0005','上官',42,'高级讲师','任劳任怨,积极肯干,先进骨干');
insert into teacher (t_id,t_name,t_age,t_title,t_intro) value ('0004','王婷',32,'高级讲师','职教多年任劳任怨,无怨无悔,青春奉献给了自己的事业');

-----------------------------------------------------------------------
修改信息:
update teacher set t_name='马丽' where t_id='0001'

--------------------------------------------------------------------------
删除信息:
delete from teacher where  t_id='0002'

---------------------------------------------------------------------------

给表增加字段:
alter table teacher add t_intro varchar(2184);
alter table teacher add t_describe varchar(2184);
alter table 表名 add 字段名 字段类型(长度)
//批量增加字段
alter table 表名 add (字段名1 字段类型(长度),字段名2 字段类型(长度),...)
-------------------------------------------------------------
给表添加注释:
alter table teacher comment '教师表';
alter table 表名 comment '注释'; 
--------------------------------------------------------------
给表的字段添加注释:
alter table 表名 add 字段名 字段类型(长度)default null comment '备注'
alter table teacher add t_id varchar(8) default null comment '教师编号'
----------------------------------------------------------------
删除字段:
alter table 表名 drop column 字段名
alter table teacher drop column t_describe
//批量删除字段
alter table 表名 drop column 字段名1,drop column 字段名2
--------------------------------------------------------------
修改字段默认值:
alter table 表名 alter column 字段 set default  默认值
alter table teacher alter column t_sex set default '女'

------------------------------------------------------------
修改字段名称和类型:
alter table 表名 change 现有字段名称  修改后字段名称 数据类型
alter table teacher change t_describe t_sex varchar(2)
-------------------------------------------------------------
查看所有表的注释
SELECT
table_name 表名,
table_comment 表说明
FROM
information_schema.TABLES
WHERE
table_schema = ‘数据库名’
ORDER BY
table_name

//例子:
SELECT
table_name teacher,
table_comment 表说明
FROM
information_schema.TABLES
WHERE
table_schema = 'test'
ORDER BY
table_name

----------------------------------------------------
查询某表的所有字段的注释
select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns
where table_schema = ‘数据库名’
and table_name = ‘表名’ ;

查询某表的所有字段的注释例子:
select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns
where table_schema = 'test'
and table_name = 'teacher' ;


-------------------------------------------------------

修改表注释
alter table 表 comment = ‘修改后的表注释信息(用户信息表)’;
alter table teacher comment = '教师表';

修改字段注释(存在主键约束的)
alter table 数据库名称.表名 modify column 表字段 表类型()comment '修改的注释';
alter table t_user modify column id int comment ‘主键ID’;
alter table teacher modify column t_id int comment '教师编号';
alter table test.teacher modify column t_name varchar(12) comment '教师名称';
alter table test.teacher modify column t_age int(6) comment '教师年龄';
alter table test.teacher modify column t_title varchar(16) comment '教师职称';
alter table test.teacher modify column t_intro varchar(2184) comment '教师简介';
alter table test.teacher modify column t_sex varchar(2) comment '教师性别';
---------------------------------------------------------------------------------
新建表以及添加表和字段的注释
create table student(
s_id  INT(12) primary key auto_increment comment '学生编号',
s_name VARCHAR(300) comment '学生姓名',
s_sex VARCHAR(6) comment '学生性别',
s_enter date comment '入学时间'
)comment = '学生表';


-----------------------------------------------------------------------------------------
添加外键:
修改picturelibrary表的libraryid字段为表usercategory的外键
alter table usercategory add constraint libraryid foreign key(libraryid) references picturelibrary(libraryid)
例子:
alter table student add constraint t_id foreign key(t_id) references teacher (t_id)
-------------------------------------------------------------------------------------------------------------------

两表关联:
存在 left join 的是左连接
select *  from teacher a left join student b on a.t_id = b.t_id

select * from teacher a join student b on a.t_id=b.tid

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值