当前位置： 首页 > news >正文

mysql--数据库表的创建及基础命令

news 来源：原创 2024/6/25 15:00:28

今日目标

  1. 表的创建

  2. 数据库的数据类型

  3. 表的约束

  4. 表结构的修改和调整

  5. 数据库的权限管理

  6. 单表的数据的CRUD

sql的一些规范

SQL的分类

SQL脚本注意点

SQL的注释

ISO提出SQL官方注释：

1、多行注释

/* */

2、当行注释

-- 注释 注意，空格是必不可少的！！！

MySQL自身提供一种注释

后面写注释

数据库相关内容的补充

# 查出数据库创建的一些信息
show create database db_name;
​
# 如果在命令行中，返回SQL很长，导致换行，看起来不太方便时

show create database db_name\G

show指令可以使用模糊查询

show xxx xxx xx [like 'xx%xxx'];
​

数据编码校验集

CREATE DATABASE  [if not exists ] `db_zm`  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

表的创建

基本语法：

create table  [if not exists ] t_name(column1 type [约束条件,]column2 type [约束条件,]……columnN type [约束条件]
)[engine=innodb];

表结构的查询

desc t_name;
describe t_name;
show columns from user;

查询创建表的SQL

show create table t_name;

数据库的数据类型

数据类型

数值型：int，float, double

Number 类型：

数据类型描述
TINYINT(size)-128 到 127 常规。 0 到 255 无符号*。在括号中规定最 大位数。
SMALLINT(size)-32768 到 32767 常规。 0 到 65535 无符号*。在括号中 规定最大位数。
MEDIUMINT(size)-8388608 到 8388607 普通。 0 to 16777215 无符号*。在 括号中规定最大位数。
INT(size)-2147483648 到 2147483647 常规。 0 到 4294967295 无 符号*。在括号中规定最大位数。
BIGINT(size)-9223372036854775808 到 9223372036854775807 常规。 0 到18446744073709551615 无符号*。在括号中规定最大位 数。
FLOAT(size,d)带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)作为字符串存储的 DOUBLE 类型，允许固定的小数点。

字符串

Text 类型：

数据类型描述
CHAR(size)保存固定长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)保存可变长度的字符串（可包含字母、数字以及特殊字 符）。在括号中指定字符串的最大长度。最多 255 个字 符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT存放最大长度为 255 个字符的字符串。
TEXT存放最大长度为 65,535 个字符的字符串。
BLOB用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
binary存储较小的二进制数据
MEDIUMTEXT存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET与 ENUM 类似， SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

时间和日期

数据类型描述
DATE()日期。格式： YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()日期和时间的组合。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是'1000-01-01 00:00:00' 到 '9999-12- 31 23:59:59'
TIMESTAMP()时间戳。 TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式： YYYY-MM-DD HH:MM:SS<br/>注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()时间。格式： HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()2 位或 4 位格式的年。<br/>注释： 4 位格式所允许的值： 1901 到 2155。 2 位格式所允许 的值： 70 到69，表示从 1970 到 2069
内容回顾：

  1. 回顾了第一天的相关知识

  2. SQL脚本的注释

  3. 编码相关的知识

  4. 数据库表的创建

  5. 数据库的数据类型

表的约束

create database 数据库名称 default charset="utf8mb4";
create database 数据库名称 default charset set utf8mb4;
​
create table `表名称` (`字段` 该字段的类型 [约束条件,]`字段` 该字段的类型 [约束条件,]-- ……`字段` 该字段的类型 [约束条件]
);
​
// char(20)

MySQL8.0数据库，存在如下约束条件

1、主键约束

2、外键约束【需要在后面讲解】

3、唯一约束

4、非空约束

5、默认值约束

6、检查约束

主键约束

主键（primary key）：数据库中一个独立无二的字段，该字段不允许数据重复，是一条记录的唯一标识符。

主键约束：强制规范这个字段，特点：不能重复、而且不能为空。

非空约束

非空约束（not null）：不允许为空，对应就是允许为空（is null）。

默认值约束

default 值，表示如果在插入数据库，没有这个字段对应的值，会插入对应的默认值

唯一约束

unique：该字段，不能存在相同的值

检查约束

检查约束：SQL标准中检查很早就出现了，但是MySQL在8.0之前没有检查约束（写上也不报错，但是也不生效），在8.0之后，MySQL真正实现了检查约束。

create table user(id int primary key auto_increment,name varchar(255) not null unique,age int default 18,gender varchar(10) check(gender in ("男", "女")),address varchar(255)
)

修改表结构

alter 指令，该指令，主要用来修改已存在的一种数据库对象的结构

主要使用在调整和修改表结构

  • 增加列

  • 删除列

  • 修改列类型或者条件

  • 修改列名称

alter table 表名称 add 字段名称 类型 [约束条件];
​
ALTER TABLE 表名称 drop 字段名称;
​
ALTER TABLE 表名称 modify 字段 新类型 [新的约束];
​
ALTER TABLE 表名称 change 旧字段名称 新的字段名称  新类型 [新的约束];
​
# 修改表名称
ALTER TABLE 表名称 rename 新名称;
RENAME TABLE 表名 TO 新表名;

复制表信息

复制表结构

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表
​
# 注意：会完整的复制表结构，并且连约束也会复制
# 表的数据不会复制

复制表结构和数据

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select {*|字段 [, 字段...]} from 源表
​
# 注意：约束中的主键和唯一约束复制不了

快速插入数据

方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select {*|字段 [, 字段...]} from 原表;

用户管理和权限问题

grant来管理权限

在MySQL8.0之前，grant是集授权、创建用户、修改密码到等一系列权限于一身的一个命令。

  grant 权限列表  on 库名.表名 to 用户名@'客户端主机' [identified by '密码'  with option参数];grant all on *.* TO ljh@'%' indentify by 'ljh';

在8.0之后，主要用来授权，像修改密码、创建用户、删除用户。

# 创建用户

create user [if not exists] 用户名称@‘localhost’ identified by 密码;
​
create user if not exists ljh@'%' identified by 'ljh';
​
grant all on db_zm.* to ljh@'%';flush privileges;

撤销权限

revoke指令

方法1：create和grant结合help CREATE USER;命令：CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;查看用户权限： help SHOW GRANTS;命令：show grants  for '用户名'@'地址'；授权：help GRANT;
​
方法2：直接grant收回权限：REVOKE删除用户：DROP USER username
    生产环境授权用户建议：
  1、博客，CMS等产品的数据库授权select,insert,update,delete,create库生成后收回create权限2、生产环境主库用户授权select,insert,update,delete3、生产环境从库授权select
​
​
创建用户方法（推荐使用方法三）:
方法一：CREATE USER语句创建
CREATE USER user1@’localhost’ IDENTIFIED BY ‘123456’;
    
方法二： INSERT语句创建
INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('user2','localhost',password('ABCabc123!'),'','','');

刷新权限： 

FLUSH PRIVILEGES;
    ​
方法三： GRANT语句创建
 GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘123456’;FLUSH PRIVILEGES;
    
语法格式：
grant 权限列表  on 库名.表名 to 用户名@'客户端主机' [identified by '密码'  with option参数];
        如：
 grant select on testdb.* to common_user@'%' grant insert on testdb.* to common_user@'%'  grant update on testdb.* to common_user@'%'  grant delete on testdb.* to common_user@'%' grant select, insert, update, delete on testdb.* to common_user@'%'grant create on testdb.* to developer@'192.168.0.%';  grant alter  on testdb.* to developer@'192.168.0.%';  grant drop   on testdb.* to developer@'192.168.0.%';  grant all    on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库  show grants;  -- 查看当前用户（自己）权限show grants for dba@localhost;  grant all on *.* to   dba@localhost; # 移除权限# revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可revoke all on *.* from dba@localhost; with_option参数GRANT OPTION： 授权选项MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数  

相关文章：

  • ACL的几种类型
  • linux：SElinux的实验之自动检查错误并提出解决方案
  • NB49 牛群的秘密通信
  • FFmpeg源码：bytestream_get_byte函数解析
  • linux中sysfs创建设备节点的方法和DEVICE_ATTR
  • Linux安装刻录软件
  • SpringBoot前置知识01-SPI接口
  • 谓词逻辑（一）
  • Vue3：可以使用.value获取ref（）包裹的值，为何还要存在unref（）
  • 基于Vue3 + js-tool-big-box工具库实现3个随机数字的小游戏动画，快来挑战你的非凡手气！
  • 列表的创建和删除
  • 别说废话！说话说到点上，项目高效沟通的底层逻辑揭秘
  • 。。。。。
  • 面试八股之MySQL篇2——索引篇
  • 31.@Anonymous
  • [原]深入对比数据科学工具箱：Python和R 非结构化数据的结构化
  • Go 语言编译器的 //go: 详解
  • gops —— Go 程序诊断分析工具
  • gulp 教程
  • HomeBrew常规使用教程
  • iOS筛选菜单、分段选择器、导航栏、悬浮窗、转场动画、启动视频等源码
  • Java｜序列化异常StreamCorruptedException的解决方法
  • JavaScript HTML DOM
  • JavaScript-Array类型
  • JavaScript异步流程控制的前世今生
  • JS专题之继承
  • PyCharm搭建GO开发环境(GO语言学习第1课)
  • Python语法速览与机器学习开发环境搭建
  • ReactNative开发常用的三方模块
  • SpingCloudBus整合RabbitMQ
  • spring security oauth2 password授权模式
  • supervisor 永不挂掉的进程 安装以及使用
  • text-decoration与color属性
  • uni-app项目数字滚动
  • 百度贴吧爬虫node+vue baidu_tieba_crawler
  • 关于使用markdown的方法（引自CSDN教程）
  • 技术：超级实用的电脑小技巧
  • 日剧·日综资源集合（建议收藏）
  • 实现简单的正则表达式引擎
  • 用Canvas画一棵二叉树
  • 远离DoS攻击 Windows Server 2016发布DNS政策
  • 分布式关系型数据库服务 DRDS 支持显示的 Prepare 及逻辑库锁功能等多项能力 ...
  • ​Linux Ubuntu环境下使用docker构建spark运行环境（超级详细）
  • # 日期待t_最值得等的SUV奥迪Q9：空间比MPV还大，或搭4.0T，香
  • # 学号 2017-2018-20172309 《程序设计与数据结构》实验三报告
  • # 执行时间 统计mysql_一文说尽 MySQL 优化原理
  • #define用法
  • #QT（一种朴素的计算器实现方法）
  • （3）(3.2) MAVLink2数据包签名(安全)
  • （二）基于wpr_simulation 的Ros机器人运动控制，gazebo仿真
  • （简单有案例）前端实现主题切换、动态换肤的两种简单方式
  • （算法设计与分析）第一章算法概述-习题
  • （学习日记）2024.03.12：UCOSIII第十四节：时基列表
  • *2 echo、printf、mkdir命令的应用
  • .gitattributes 文件