MySQL查询创建新的一列 mysql查询创建表语句

转载

代码工匠传奇 2024-06-24 09:42:36

文章标签 MySQL查询创建新的一列数据库 mysql oracle 字段 文章分类 MySQL 数据库

今日目标

表的创建
数据库的数据类型
表的约束
表结构的修改和调整
数据库的权限管理
单表的数据的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

内容回顾：

回顾了第一天的相关知识
SQL脚本的注释
编码相关的知识
数据库表的创建
数据库的数据类型

表的约束

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,delete
    3、生产环境从库授权
        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： 定义单个用户同时可以建立的连接数

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。