MySQL的表管理&数据类型&约束&补充

原创 于 2025-02-02 12:26:19 发布 · 726 阅读 · 26 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测

表管理&数据类型&约束

建、删库表&改表

  • 建库

create database if not exists 数据库名 //if not exists 避免建库重名报错

  • 删库

drop database if exists 数据库名 //if exists 避免删除的库名不存在而报错

  • 建表语法

create table 库名.表名

(

表头名1 数据类型,

表头名2 数据类型,

表头名3 数据类型,

......

);

注意：表必须存放在库里

mysql> create database 学生库;
Query OK, 1 row affected (0.41 sec)

mysql> create table 学生库.学生信息表(
    -> 姓名 char(10),
    -> 班级 char(10),
    -> 性别 char(4),
    -> 年龄 int
    -> );
Query OK, 0 rows affected (0.85 sec)

  • 删除表

mysql> drop table 学生库.学生信息表;
mysql> drop database 学生库;

  1. 修改表

操作命令

说明

add

添加新字段，一起添加多个字段使用，以,分隔add命令（first after）

modify

修改字段类型，也可以修改字段的位置

change

修改字段名，也可以同时修改字段类型

rename

修改表名

drop

删除字段，删除多个字段使用以,分隔drop命令

mysql> alter table studb.stuinfo
    -> add email char(30),
    -> add school char(10) after name,
    -> add number char (10) first;
Query OK, 0 rows affected (1.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.stuinfo;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(10) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| school | char(10) | YES  |     | NULL    |       |
| class  | char(10) | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| email  | char(30) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)

  • 复制表
  • //复制表结构及数据

语法格式：create table 库.表 select 列名 from 库.表 [where 条件]

注意：原表的key 不会复制给新表，新表数据与select语句决定

  • //仅复制表结构

语法格式：create table 库.表 like 库.表

注意：原表的key 同时复制给新表

数据类型

  • 字符类型

一般用来存储姓名，收货地址，工作单位，家庭住址

类型

名称

说明

char(字符个数)

定长类型

最多255个字符

varchar(字符个数)

变长类型

最多65532个字符

char类型说明：不能指定字符个数时在左右边用空格补全字符个数，超出时无法写入数据。

varchar类型说明：按数据实际大小分配存储空间，字符个数超出时无法写入数据。

  • 数型类型

仅存储数值的整数部分

类型

名称

有符号范围

无符号范围

tinyint

微小整数

-128~127

0~255

smallint

小整数

-32768~32767

0~65535

mediumint

中整数

-223~223-1

0~224-1

int

大整数

-231~231-1

0~232-1

bingint

极大整数

-263~263-1

0~264-1

unsigned

使用无符号存储范围

  • 浮点类型

存储有小数点的数

类型

名称

存储空间

float

单精度

4字节

double

双精度

8字节

  • 枚举类型

enum类型 单选

类型

说明

enum(值列表)

字段值仅能在范围内选择1个值

set类型 多选

类型

说明

set(值列表)

字段值能在范围内选择1个或多个值

mysql> create table studb.t8(name char(10),
    -> gender enum('male','female'),
    -> likes set('eat','drink','play','haha')
    -> );
Query OK, 0 rows affected (0.63 sec)

  • 日期类型

存储如生日，注册时间，出生年份，入职日期

类型

名称

范围

赋值格式

year

年

1901~2155

YYYY

date

日期

0001-01-01~9999-12-31

YYYYMMDD

time

时间

01:00:00-23:59:59

HHMMSS

datetime

日期时间

1000-01-01 00:00:00~9999-12-31 23:59:59

YYYYMMDDHHMMSS

timestamp

1970-01-01 00:00:00~2038-01-19 00:00:00

mysql> desc studb.t6;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| name       | char(6)  | YES  |     | NULL    |       |
| birth_date | date     | YES  |     | NULL    |       |
| birth_year | year     | YES  |     | NULL    |       |
| party_time | datetime | YES  |     | NULL    |       |
| class_time | time     | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into studb.t6 values
    -> ('hehe',20000228,2024,now(),142728),
    -> ('lisi',curdate(),year(now()),now(),curtime());
    
mysql> select * from studb.t6;
+------+------------+------------+---------------------+------------+
| name | birth_date | birth_year | party_time          | class_time |
+------+------------+------------+---------------------+------------+
| hehe | 2000-02-28 |       2024 | 2024-12-16 14:28:21 | 14:27:28   |
| lisi | 2024-12-16 |       2024 | 2024-12-16 14:28:21 | 14:28:21   |
+------+------------+------------+---------------------+------------+
2 rows in set (0.00 sec)

数据的导入&导出

  • 数据导入

操作步骤：

建表 ---> 拷贝文件到检索目录 ---> 导入数据

语法格式

mysql> load data infile '/目录名/文件名' into table 库名.表名
    -> fields terminated by '分隔符'
    -> lines terminated by '\n';
  • 数据导出

语法格式1：

mysql> select * from 库名.表名 into outfile '/目录名/文件名';

语法格式2：

mysql> select * from 库名.表名
    -> where 条件 into outfile '/目录名/文件名'
    -> fields terminated by ':'
    -> lines terminated by '/n';

约束分类

  • 约束是一种限制，设置在表头上，用来控制表头的赋值，包括以下几种：
  1. NOT NULL ：非空，用于保证该字段的值不能为空。
  2. DEFAULT：默认值，用于保证该字段有默认值。
  3. UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
  4. PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
  5. FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

 高级约束

 主键
  • 主键使用规则：
  • 表头值不允许重复，不允许赋NULL值
  • 一个表中只能有一个primary key 表头
  • 多个表头做主键，称为复合主键，必须一起创建和删除
  • 主键标志PRI
  • 主键通常与auto_increment连用
  • 通常把表中唯一标识记录的表头设置为主键[行号表头]
  • 创建主键的格式
  • 语法格式1：

create table 库.表 (

字段名 数据类型 primary key,

字段名 数据类型,

......);

  • 语法格式2：

create table 库.表 (

字段名 数据类型,

......

primary key (字段名)

);

  • 删除主键命令格式 alter table 库.表 drop primary key;
  • 添加主键命令格式 alter table 库.表 add primary key (字段名);
  • 复合主键
  • 多个字段一起做主键，相当于多个字段共享一个主键
  • 复合主键的值不允许同时重复
  • 创建复合主键语法格式：

create table 库.表(

字段名 数据类型,

字段名 数据类型,

......

primary key(字段名)

);

  • 删除复合主键：alter table 库名.表名 drop primary key;
  • 添加复合主键：alter table 库.表 add primary key (字段名);
  • 主键与auto_increment连用
  • 作用：自增长，通过表头自加1计算结果赋值
  • 通常与数据类型整型类型连用，如统计行号
  • 想要让表头有自增长，表头必须有主键设置才可以
  • 当给自增长表头赋值后，后面的自增长表头会以最后一条记录表头的值+1
  • 语法：

caret table 库名.表名(

字段名 int primary key auto_increment,

字段名 数据类型,

......

);

 外键
  • 使用规则
  • 表存储引擎必须是innodb
  • 字段类型要一致
  • 被参照字段必须要是索引类型的一种(primary key)
  • 作用：插入记录时，表头值在另一个表的表头值范围内选择
  • 创建外键语法格式：

create table 库.表(

字段名 数据类型,

......

foreign key(字段名) #指定外键

references 库.表(字段名) #指定参考的字段名

on update cascade #同步更新

on delete cascade #同步删除

)engine=innodb; #指定存储引擎

mysql> create table db1.yg(
    -> yg_id int primary key auto_increment,
    -> name char(20));
Query OK, 0 rows affected (0.49 sec)

mysql> create table db1.gz(
    -> gz_id int, pay float,
    -> foreign key(gz_id) references db1.yg(yg_id)
    -> on update cascade on delete cascade);
Query OK, 0 rows affected (1.02 sec)

mysql> desc db1.yg;desc db1.gz;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| yg_id | int      | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

+-------+-------+------+-----+---------+-------+
| Field | Type  | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| gz_id | int   | YES  | MUL | NULL    |       |
| pay   | float | YES  |     | NULL    |       |
+-------+-------+------+-----+---------+-------+
2 rows in set (0.00 sec)
  • 删除与添加
  • 查看外键

show create table 库.表 \G

  • 删除外键

alter table 库.表 drop foreign key 外键名;

  • 添加外键

alter table 库.表 add foreign key(字段名) references 库.表(字段名) on update cascade on delete cascade;

补充

Mysql索引

  • 什么是索引 (index)
  • 是帮助MySQL高效获取数据的数据结构
  • 为快速查找数据而排好序的一种数据结构。
  • 类似书的目录
  • 可以用来快速查询表中的特定记录，所有的数据类型都可以被索引。
  • Mysql索引主要有三种结构：Btree、B+Tree、Hash
  • 索引分类
  • 按作用效果:针对于字段值

普通索引:index

唯一索引:unique

主键:primary key

  • 按存储形式

聚簇索引:数据和索引在一起

非聚簇索引:数据和索引不在一起

  • 按数据结构:

树型索引:btree、b+tree

哈希索引:hash

全文索引:fulltext

空间索引

  • 按作用字段

单列索引:作用于单个字段

复合索引:作用于多个字段，使用时从左往右匹配

  • 索引优点
  • 可以大大提高MySQL的检索速度
  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引缺点
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 对于非常小的表，大部分情况下简单的全表扫描更高效。
  • 使用规则
  • 一个表中可以有多个index
  • 任何数据类型的表头都可以建索引
  • 字段的值可以重复，可以赋NULL值
  • 通常在where条件中的字段上配置index
  • index索引标志MUL
  • 创建索引

creare table 库.表(

字段名 数据类型,

字段名 数据类型,

......

index(字段名),

index(字段名),

);

  • 查看&添加&删除

show index from 库名.表名 \G //查看索引详细信息

mysql> show index from home.tea4 \G
*************************** 1. row ***************************
        Table: tea4    #表名
   Non_unique: 1
     Key_name: name    #索引名 （默认索引名和表头名相同，删除索引时，        使用的索引名）
 Seq_in_index: 1
  Column_name: name    #表头名
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE   #索引类型
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

create index 索引名 on 库.表(字段名); //添加索引

drop index 索引名 on 库.表; //删除索引

explain select 查询语句; //验证查询是否使用索引

mysql> explain select  * from tarena.user where name="sshd" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user    #表名
   partitions: NULL
         type: ref
possible_keys: name
          key: name  #使用的索引名
      key_len: 21
          ref: const
         rows: 1     #查找的总行数
     filtered: 100.00
        Extra: NULL  #额外说明
1 row in set, 1 warning (0.00 sec)

 用户管理

  • 创建用户
  • 用户名：授权时自定义，要有标识性，存储在mysql库的user表里
  • 客户端地址

% //所有主机

192.168.88.% //网段内的所有主机

192.168.88.52 //固定1台主机

localhost //数据库服务器本机

语法：create user 用户名@"客户端地址" identified by "密码";

#创建用户如下
mysql> create user tom@'localhost' identified by '123';
Query OK, 0 rows affected (0.39 sec)
#修改用户密码如下
mysql> alter user tom@'localhost' identified by 'tarena';
Query OK, 0 rows affected (0.08 sec)
#修改用户名如下
mysql> rename user tom@'localhost' to jerry@'localhost';
Query OK, 0 rows affected (0.07 sec)
#删除用户如下
mysql> drop user jerry@localhost;
Query OK, 0 rows affected (0.11 sec)
  • 用户授权
  • 授权是在数据库服务器里添加用户并设置权限及密码
  • 重复执行grant命令时如果库名和用户名不变时，是追加权限
  • 语法：grant 权限列表 on 库名 to 用户名@"客户端地址";
  • 权限表示方法

all //所有权限

usage //登陆权限或无权限

select,update,insert //个别权限

select,update(字段1,字段n) //指定字段

  • 库名

*.* //所有库所有表

库名.* //一个库

库名.表名 //一张表

mysql> grant select on *.* to tom@'localhost';
Query OK, 0 rows affected (0.06 sec)

mysql> grant delete on tarena.* to tom@'localhost';
Query OK, 0 rows affected (0.13 sec)

mysql> grant insert on tarena.departments to tom@'localhost';
Query OK, 0 rows affected (0.08 sec)

mysql> grant update(name) on tarena.user to tom@'localhost';
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for tom@'localhost';
+---------------------------------------------------------------+
| Grants for tom@localhost                                      |
+---------------------------------------------------------------+
| GRANT SELECT ON *.* TO `tom`@`localhost`                      |
| GRANT DELETE ON `tarena`.* TO `tom`@`localhost`               |
| GRANT INSERT ON `tarena`.`departments` TO `tom`@`localhost`   |
| GRANT UPDATE (`name`) ON `tarena`.`user` TO `tom`@`localhost` |
+---------------------------------------------------------------+
4 rows in set (0.00 sec)
  • 授权库

mysql库 存储用户权限信息，主要表如下:

  1. user表 保存已有的授权用户及用户对所有库的权限
  2. db表 保存已有授权用户对某一个库的访问权限
  3. tables_priv表 记录已有授权用户对某一张表的访问权限
  4. columns_priv表 记录已有授权用户对某一个字段的访问权限
  • 撤销权限
  • 注意
  1. 删除已有授权用户的权限
  2. 库名必须和授权时的表示方式一样
  • 语法：revoke 权限列表 on 库名 from 用户名@"客户端地址";
mysql> revoke drop,delete on *.* from  tom@'localhost';
Query OK, 0 rows affected (0.34 sec)
  • 用户管理相关命令

命令

作用

select user();

显示登录用户名和客户端地址

show grants;

用户显示自身访问权限

show grants for 用户名@"客户端地址";

管理员查看已有授权用户权限

set password for 用户名@"客户端地址"="密码";

管理员重置授权用户连接密码

drop user 用户名@"客户端地址";

删除授权用户(必须有管理员权限)

标签
#adb #mysql #运维 #数据库
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值