MySQL数据类型及表操作

时间:2022-10-20 14:56:20

建立简单表

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);

作业:

  1. 建立数据库hbcf

  2. 建立一个表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)

  1. 主键约束 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;
    
    
  2. 唯一约束 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;
    
  3. 默认值 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)
    );
    
  4. 非空 not null 不写 default null

  5. 外键约束 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

  1. 数字类型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;
   
  1. 字符字符串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
  2. 日期时间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;
    
  3. 布尔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;
    
  4. 复合类型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','女','线性代数,高数,离散数学,计算机英语');