MySQL数据类型及表操作

建立简单表

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	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','女','线性代数,高数,离散数学,计算机英语');

秒客网

MySQL数据类型及表操作

建立简单表

删除表

修改表及列及数据类型

表相关的约束(PK-FK)

数据类型

相关文章