创建表:
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