建立简单表
use wxdb;
create table wx_student(
id int ,
name varchar(30)
);
select database();
show tables;
select * from wx_student;
use wxdb;
create table t1(t int);
create table t2(t int);
create table t3(t int);
create table t4(t int);
create table t5(t int);
作业:
-
建立数据库hbcf
-
建立一个表cf_news
show create table cf_news; create table `cf_news` ( `id` varchar(36) not null, `subject` varchar(200) default null, `content` longtext, `category` varchar(20) default null, `logoUrl` varchar(300) default null, `times` int default '0', `adddate` datetime default null, `ispublish` tinyint default '0' comment '0未发布 1已发布', `publishdate` datetime default null, `isdel` tinyint default '0' comment '0未删除 1删除 ', primary key (`id`) comment '注释' ) engine=innodb default charset=utf8
删除表
-- 删除表
drop table wx_student;
drop table if exists wx_student;
-- 批量删除表
drop table if exists t1,t2,t3,t4,t5;
修改表及列及数据类型
-- 修改表名 student cf_student cf_user cf_admin cf_car
rename table w_student to cf_student;
alter table cf_student rename st;
-- 查看表结构
describe st;
desc st;
-- 查看建立表语句
show create table st;
CREATE TABLE `st` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`gender` enum('男','女') DEFAULT NULL,
`course` set('计算机英语','高数','离散数学','线性代数') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
-- 增加一列(字段)
-- 删除一列(字段)
-- 修改列名
-- 修改列的数据类型 int bigint varchar int varchar(30) varchar(5)
select * from st;
-- 增加一列
alter table st add address varchar(255) not null default '郑州市';
alter table st add age tinyint unsigned not null default 18 first;
alter table st add age tinyint unsigned default 0 after name;
-- 删除一列
alter table st drop address;
alter table st drop column age;
-- 修改列名 name StudentName sname
alter table st rename column name to sname;
alter table st change sname name varchar(30) first;
alter table st change name name varchar(30) after id;
-- 修改列的数据类型及精度
alter table st modify name varchar(100) after address;
alter table st modify name varchar(50) after id;
-- 复制表的结构
-- create table t6(t int);
create table st2 like st;
show create table st2;
select * from st2;
-- 插入数据
insert into st2 select * from st;
show tables;
-- 根据查询的结构,建立一个没有相关约束的表,可以直接插入数据
create table st3 as select id,name from st;
create table st4 as select id,name,address from st where name like '李%';
select * from st3;
show create table st3;
drop table st3;
select * from st4;
表相关的约束(PK-FK)
-
主键约束 PK
-- 表的相关约束 -- 1. PK primary key 一个表只有一个主键约束, create table t1( id int unsigned auto_increment primary key, name varchar(30) ); create table t2( id int unsigned auto_increment, name varchar(30), primary key(id) ); create table t3( id int, name varchar(30) ); alter table t3 add primary key(id); show create table t3; create table t4( id char(36), name varchar(30), primary key(id) ); insert into t4 value(uuid(),'jack'); insert into t4 values(uuid(),'jack'),(uuid(),'李四'),(uuid(),'james'); select * from t4; show create table mysql.user;
-
唯一约束 unique
-- 表的相关约束 -- 建立唯一约束 create table t( id int unsigned auto_increment, name varchar(15) unique , /* 此字段,值不能重复,但可以null重复*/ age tinyint unsigned default 18, primary key(id) ) insert into t(name) values('lisi'),('jack'),(null),(null),(null); insert into t values(null,'aaa',29),(null,'fff',29); select * from t; truncate t;
-
默认值 default 18
create table w_student( id char(36) , name varchar(30) not null, gender enum('男','女') default '男', age tinyint unsigned default 18, birthday date default '1000-01-01', regtime datetime default now(), updatetime timestamp default current_timestamp on update current_timestamp, primary key(id) );
-
非空 not null 不写 default null
-
外键约束 FK
foreign key constraint 约束 references 关联
一个表的外键,一般要指向另一个表主键或唯一约束键
-- 表的相关约束 -- 外键约束 foreign key show tables; drop table t,w_student; -- 建立老师表 create table teacher( id int unsigned auto_increment, name varchar(30), primary key(id) ); insert into teacher values(null,'李老师'),(null,'周老师'),(null,'郑老师'),(null,'刘老师'); select * from teacher; delete from teacher where id = 3; -- 建立学生表,学生表的teacher_id 建立一个外键,关联到teacher(id) create table student( id int unsigned auto_increment, name varchar(30), teacher_id int unsigned, primary key(id), constraint stfk foreign key(teacher_id) references teacher(id) ); insert into student (name) values('张三'),('李四'); drop table teacher; select * from student; show engines; show tables; select * from teacher; delete from teacher where id = 1; select * from student; drop table student,teacher; -- 删除外键 alter table student drop constraint stfk; -- 删除一列 alter table student drop teacher_id; alter table student add tid int unsigned; create table teacher ( id int unsigned, name varchar(30), primary key (id) ); create table student ( id int unsigned, name varchar(30), tid int unsigned, primary key (id) ); -- 建立外键 alter table student add constraint stfk foreign key (tid) references teacher (id); -- 建立外键,如果主表删除,自动设置关联表设置null,如果主表修改,自动设置关联表更新 alter table student add constraint stfk foreign key (tid) references teacher (id) on delete set null on update cascade; insert into teacher values (1, '李老师'), (3, '赵老师'); insert into student values (6, '李四', 1); select * from student; select * from teacher; delete from teacher where id = 1; update student set tid = 3 where id = 6; update teacher set id = 110 where id = 3;
数据类型
int tinyint bigint float double decimal(p,2) char varchar date datetime timestamp set enum text longtext longblob
- 数字类型int decimal
Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
---|---|---|---|---|---|
TINYINT | 1 | -128 | 127 | 255 | |
SMALLINT | 2 | -32768 | 32767 | 65535 | |
MEDIUMINT | 3 | -8388608 | 8388607 | 16777215 | |
INT | 4 | -2147483648 | 2147483647 | 4294967295 | |
BIGINT | 8 | -263 | 263-1 | 264-1 |
型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
-- ZEROFILL UNSIGNED
-- BIT[(M)] 1-64
-- TINYINT[(M)] [UNSIGNED] [ZEROFILL] -128 - 127 0-255
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767.
The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is -8388608 to 8388607.
The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This type is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The
unsigned range is 0 to 18446744073709551615.
-- 整数类型 bit tinyint smallint mediumint int integer bigint 8byte
-- 小数 decimal(p,s) numeric(p,s) float double
-- cf_student
create table cf_student(
id int(3) zerofill unsigned auto_increment,
name varchar(30) not null,
age tinyint unsigned default 18,
money decimal(10,2) comment '金额',
primary key(id)
)engine=innodb default charset=utf8 auto_increment=202101;
drop table cf_student;
insert into cf_student values(null,'andy',38,9);
insert cf_student(name) values('李四'),('王五'),('赵六七');
select * from cf_student;
-
字符字符串char varchar
char(0-255)
varchar(0-21835)
text
longtext 4GB 字符串
如下类型是存储字节,使用的不多
blob
longblob
Value CHAR(4) Storage Required VARCHAR(4) Storage Required '' ' ' 4 bytes '' 1 byte 'ab' 'ab ' 4 bytes 'ab' 3 bytes 'abcd' 'abcd' 4 bytes 'abcd' 5 bytes 'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes 类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串varchar(21835) TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字节 TINYTEXT 0-255 bytes 短文本字符串 BLOB文件 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB文件 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB文件 0-4 294 967 295 bytes 二进制形式的极大文本数据 4G 文件字节 LONGTEXT 0-4 294 967 295 bytes 极大文本数据 4GB -
日期时间date datetime
date 日期 ‘yyyyy-MM-dd’
datetime 日期时间 ‘yyyy-MM-dd HH:mm:ss’
timestamp 时间戳
Data Type Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4 YEAR 1 byte 1 byte DATE 3 bytes 3 bytes TIME 3 bytes 3 bytes + fractional seconds storage DATETIME 8 bytes 5 bytes + fractional seconds storage TIMESTAMP 4 bytes 4 bytes + fractional seconds storage Data Type “Zero” Value DATE '0000-00-00' java.util.time.LocalDate TIME '00:00:00' java.util.time.LocalTime DATETIME '0000-00-00 00:00:00' java.util.Date TIMESTAMP '0000-00-00 00:00:00' YEAR 0000 类型 大小 ( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值,时间戳 -- 建立表 create table w_student( id int unsigned auto_increment comment '学号', name varchar(30) not null, gender enum('男','女'), birthday date, regtime datetime default now(), updatetime timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '每次数据都会自动更新当前时间', primary key(id) ); show tables; create table t(t int); insert into w_student(name,birthday) values('李四','2002-09-20'); select * from w_student; update w_student set name = '李强2' where 0;
-
布尔BOOL, BOOLEAN
-- bool boolean tinyint(1) 0-255 bit tinyint create table t2( isA bit, isB bool, isC boolean, isD tinyint(1) unsigned, ns varchar(30) ) -- show create table t2; create table `t2` ( `isA` bit(1) default null, `isB` tinyint(1) default null, `isC` tinyint(1) default null, `isD` tinyint unsigned default null, `ns` varchar(30) default null ) engine=innodb default charset=utf8mb3 create table t3(t bit); insert into t3 values(1),(0) select * from t3; select * from t2; insert t2 values(true,true,false,false,'jack'); insert t2 values(false,false,false,false,'jack'); select * from t2 where not isC; -- 0 false 非0就是true select * from t2 where null is null;
-
复合类型set enum
-- 学习 set 集合 enum 枚举 select database(); create table wxdb.t1(t int); use wxdb; show tables; drop table if exists t,t1,t2,t3,w_student; /* 在wxdb 数据库建立表w_studdnet */ create table w_student( id int unsigned auto_increment primary key, name varchar(30) not null, gender enum('男','女'), course set('计算机英语','高数','离散数学','线性代数') not null ); select * from w_student; insert into w_student(name) values('李四'),('jack'); insert into w_student value(null,'lisi','女','线性代数,高数,离散数学,计算机英语');