mysql表级别操作

目录

mysql表级别操作

数据基本操作(增删改查)

增加表

  • create table 表名(字段名 数据类型(长度) 约束) ；
    • create table t1(id int ,name char(18) not null);
    • 在同一张表中字段名不能重复，字段名和数据类型是必须的，长度和约束可选
  • create table 表名(字段名 数据类型(长度) 约束) engine=innodb;
    • 在建表时设置表的存储引擎为innodb

删除表

  • drop table 表名;
    • drop table t2;

修改表

  • alter table 表名 rename 另一个表名;
    • 重命名表
  • alter table 表名 **add **字段名 类型(长度) 约束 first/after 字段名;
    • 增加表中字段，并把增加字段放在first表的最前面或者after某个字段的后面
    • first/after 字段名可选
  • alter table 表名 drop 字段名;
    • 删除表中字段
  • alter table 表名 modify 字段名 数据类型(长度) 约束 first/after 字段名;
    • 修改表中某一字段，并把修改后的字段放在first表的最前面或者after某个字段的后面
    • first/after 字段名可选
  • alter table 表名 change 旧字段名 新字段名 数据类型(长度) 约束 first/after 字段名;
    • 修改表中某一字段以及字段名，并把修改后的字段放在first表的最前面或者after某个字段的后面

查看表

查看库下的表

  • show tables;
    • 查看库下的所有表

查看表结构

  • desc 表名;
    • 排版清晰简单
    • 可以查看字段名、数据类型、长度和部分约束，但不能看到表的存储结构以及编码方式
  • show create table 表名;
    • 排版比较粗糙，不易观察
    • 但可以查看字段名、数据类型、长度、部分约束、表的存储结构以及编码方式
#第一种方式
mysql>desc t1;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | char(18)              | NO   |     | NULL    |                |
| gender | enum('man','felmale') | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

#第二种方法
mysql>show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(18) NOT NULL,
  `gender` enum('man','felmale') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

存储引擎

  • 存储引擎也就是存储数据的方法
  • 将数据存储在硬盘中的存储引擎
    • innodb
      • mysql5.5.8版本后的默认存储引擎
      • 存储数据时会使用两张表
        • 一张表存储数据和索引，后缀名为.ibd
        • 一张表用于存储表结构，后缀名为.frm
      • 支持事务
        • 确保数据的完整性，保证数据的安全
      • 支持行级锁
        • 当对少数行进行操作或对一行数据频繁操作的时候使用行级锁
      • 支持表级锁
        • 当对表中数据进行批量操作时，为了确保数据准确性时使用
      • 支持外键
        • 使用外键关联的两张表的字段不能随意添加/删除/修改
        • 能够大大降低数据增删改的出错率
    • myisam
      • mysql5.5.8版本之前的默认存储引擎
        • 储存数据时会在data文件夹下使用三张表
          • 一张表用于存储数据.后缀名为.MYD
          • 一张表用于存储索引.后缀名为.MYI
          • 一张表用于存储表结构后缀名为.frm
        • 支持表级锁
  • 将数据存储在内存中的存储引擎
    • memory
      • 将数据存放在内存中，如果数据库重启或发生崩塌，表中数据将全部消失
      • 非常适合存储临时数据的临时表，默认为哈希索引
      • 存储数据时只会使用一张表
        • 用于存储表结构，后缀名为.frm
      • 只支持表级锁，并发性较差

数据类型

  • 数字类型
    • int
      • 默认为11个字长
    • float(m,n)
      • m为浮点数的总长度，n为小数点后的长度，所以注意小数点前的长度为m-n
类型 大小 范围（有符号） 范围（无符号）unsigned约束 用途
tiny int 1 字节 (-128，127) (0，255) 小整数值
small int 2 字节 (-32 768，32 767) (0，65 535) 大整数值
medium int 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
int或integer 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
big int 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
float 4 字节 float(255,30) (-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 字节 double(255,30) (-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 double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值
  • 时间类型
    • date
      • 使用now()函数输出为年-月-日
      • 比较常用，如生日，入职日期等
    • time
      • 使用now()函数输出为时-分-秒
      • 比较常用，如跑步运动员记时等
    • datetime
      • 使用now()函数输出为年-月-日 时-分-秒
      • 比较常用，如下班打卡等
    • year
      • 使用now()函数输出为年
      • 使用不太多
    • timestamp
      • 不太常用，因为它最长的时间也只到2038-1-19 11:14:07
      • 使用now()函数输出为年-月-日 时-分-秒
类型 大小 (字节) 范围 格式 用途
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 混合日期和时间值，时间戳

  • 字符串数据类型

    比较常用的有char和varchar

    • char
      • 定长字符串，不写长度时默认为1个字符
      • 特点：效率高，但浪费空间
    • varchar
      • 变长字符串，必须要写长度
      • 特点：效率低，但节省空间
类型 大小 用途
char 0-255字节 定长字符串
varchar 0-65535 字节 变长字符串
tiny blob 0-255字节 不超过 255 个字符的二进制字符串
tiny text 0-255字节 短文本字符串
blob 0-65 535字节 二进制形式的长文本数据
text 0-65 535字节 长文本数据
medium blob 0-16 777 215字节 二进制形式的中等长度文本数据
medium text 0-16 777 215字节 中等长度文本数据
long blob 0-4 294 967 295字节 二进制形式的极大文本数据
long text 0-4 294 967 295字节 极大文本数据
  • enum/set
    • enum(单选)
      • sex enum('man','women')
        • 写入值时只能二选一，不能选择其他的值
    • set(多选)
      • hobby set('抽烟','喝酒','烫头')
        • 写入值时只能从这几个爱好中选择，重复选mysql会自动去重，写了其他的非设置里的值mysql不会将其写入数据库中
类型 大小 用途
enum 对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。 单选：选择性别
set 1-8个成员的集合，占1个字节; 9-16个成员的集合，占2个字节 ; 17-24个成员的集合，占3个字节; 25-32个成员的集合，占4个字节; 33-64个成员的集合，占8个字节 多选：兴趣爱好

约束

  • 无符号：unsigned

    • 主要放在数字类型数据后面

  • 非空：not null

    • 当设置了not null但依旧可以插入空值怎么办，解决方法如下：
    # 1.直接在mysql中添加(重启后失效)
mysql>set sql_mode='strict_trans_tables,no_auto_create_user,no_engine_substi
tution;

# 2.直接在配置文件my.ini中添加(永久有效)
sql_mode='strict_trans_tables,no_auto_create_user,no_engine_substitution;

  • 默认值：default

    • default 值

  • 唯一：unique

    • 联合唯一
      • 先写完字段名、类型及约束再写unique()
      • unique(字段名1，字段名2,……)

  • 主键：primary key

    • 自带not null unique(非空且唯一)
    • 一张表只有一个主键，当未主动写入primary key时会默认第一个not null unique(非空且唯一)的字段为主键
    • 联合主键
      • primary key(字段名1，字段名2,……)

  • 自增：auto_increment

    • 只对数字类型有效
    • 写自增约束前的字段必须是唯一unique
    • 一般自增都会与主键写一起：primary key auto_increment
    • 一般是在id后面加该约束

  • 外键：foreign key

    • 将本表中的字段设置为外键，与外表中的字段进行连接
    • 可以在foreign key前加constraint 外键名
      • constraint fk_1 foreign key…….
    • foreign key(本表中的字段) references 外表(字段名)
      • 外键后面可以接如下方式
    #cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
on delete cascade  # 级连删除，一般不用
on update cascade # 级连更新

#set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  
on delete set null  
on update set null 

#No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

#Restrict方式
同no action, 都是立即检查外键约束

#Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

表与表之间的关系

  • 一对多/多对一
    • 直接设置外键
  • 多对多
    • 建立第三张表，表中设置两个外键，分别关联另外两张表的字段
  • 一对一
    • 直接设置外键，外键关联的字段必须约束为unique
posted @ 2020-05-31 18:32  yyyzh  阅读(151)  评论(0)  编辑  收藏  举报