MySQL 基础

MySQL 基础

SQL 介绍

SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统(RDBMS)。 SQL 的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。

  • SQL 是什么?
  • SQL 指结构化查询语言
  • SQL 使我们有能力访问数据库
  • SQL 是一种 ANSI(American National Standards Institute,美国国家标准化组织)的标准计算机语言
  • SQL 能做什么?
  • SQL 面向数据库执行查询
  • SQL 可从数据库取回数据
  • SQL 可在数据库中插入新的记录
  • SQL 可更新数据库中的数据
  • SQL 可从数据库删除记录
  • SQL 可创建新数据库
  • SQL 可在数据库中创建新表
  • SQL 可在数据库中创建存储过程
  • SQL 可在数据库中创建视图
  • SQL 可以设置表、存储过程和视图的权限
  • 名词解释
  • SQL(Structured Query Language)结构化查询语言, 分为 DDL, DMLDCL
  • DDL(Data Definition Language)数据库定义语言,声明用于定义数据库结构或模式。主要包括三个关键字: CREATE, ALTER, DROP, 主要操作对象 有数据库、表、索引、视图等。
  • DML(Data Manipulation Language)数据操纵语言,用于管理模式对象中的数据。以 INSERT, UPDATE, DELETE 三种指令为核心,分别代表插入、更新与删除。
  • DQL(Data Query Language, DQL)数据查询语言,是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有FROM,WHERE,GROUP BY,HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。
  • DCL(Data Control Language)数据库控制语言,由 GRANTREVOKE 两个指令组成;DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。
  • TCL(Transaction Control Language)事务控制语言。
  • RDBMS(Relational Database Management System)关系型数据库管理系统。

DDL(数据定义语言)

用于定义SQL模式、基本表、视图和索引的创建和删除操作。

数据库操作

  • CREATE DATABASE – 创建新数据库

    CREATE DATABASE [IF NOT EXISTS] db_name; Tips: IF NOT EXISTS 判断数据库是否存在,不存在则创建数据;存在则忽略创建语句,不再创建数据库。

  • DROP DATABASE – 删除数据库

    DROP DATABASE [IF EXISTS] db_name; Tips: IF EXISTS 判断数据库是否存在,存在则删除,不存在就结束,不会报错。(没有该关键字,删除不存在的数据库时会报错)

数据表操作

  1. CREATE TABLE – 创建新表

    CREATE TABLE [IF NOT EXISTS] table_name(col1 type,col2 type); Tips: IF NOT EXISTS 判断当前数据库中是否存在该表,不存在则创建数据表;存在在则忽略建表语句,不再创建数据表。

CREATE TABLE [IF NOT EXISTS] 表名(
  字段名1 数据类型 [约束条件] [默认值],
  字段名2 数据类型 [约束条件] [默认值],
  字段名3 数据类型 [约束条件] [默认值],
  ...

) [chrset="字符编码"];
  1. ALTER TABLE – 变更(改变)数据库表
  2. 修改表名字

    ALTER TABLE old_table RENAME AS new_table;

  3. 新增字段,并排在某一字段后面

    ALTER TABLE 表名 ADD [COLUMN] 字段名 数据类型 [完整性约束条件…] [FIRST | AFTER 字段名];

  4. 修改字段
    • MODIFY 只能修改 -数据类型- 及其 -完整性约束条件-

      ALTER TABLE 表名 MODIFY 字段名 新数据类型 [完整性约束条件…];

    • CHANGE 能修改 -字段名-、-数据类型- 及其 -完整性约束条件-

      ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

  5. 删除字段

    ALTER TABLE table_name DROP [COLUMN] column_name;

  6. DROP TABLE – 删除表

    DROP TABLE [IF EXISTS] table_name; Tips: IF EXISTS 判断当前数据库中是否存在该表,存在则删除数据表;不存在则忽略删除语句,不再执行删除数据表的操作。

  7. TRUNCATE TABLE – 截断表(清空表)

    TRUNCATE TABLE table_name;

常用约束

约束(Constraint)是Microsoft SQL Server 提供的自动保持数据库完整性的一种方法,定义了可输入表或表的单个列中的数据的限制条件。在SQL Server 中有5 种约束:主关键字约束(Primary Key Constraint)、外关键字约束(Foreign Key Constraint)、惟一性约束(Unique Constraint)、检查约束(Check Constraint)和缺省约束(Default Constraint)

  • 主键约束 (Primary Key Constraint) 要求主键列唯一,并且不允许为空
  • 唯一约束 (Unique Constraint) 要求该列唯一,允许为空,但只能出现一个空值
  • 检查约束 (Check Constraint) 某列取值范围限制、格式限制等。(如:年龄,性别)
  • 默认约束 (Default Constraint) 某列 的默认值(如:男性学员比较多,性别默认设为男)
  • 外键约束 (Foreign Key Constraint) 用于在两表之间建立关系,需要指定引用主表的哪一列 Tips:
  • 主键约束:指定表的 一列或几列 的组合的值在表中具有惟一性,即能惟一地指定一行记录。且IMAGE 和TEXT 类型的列不能被指定为主关键字,也不允许指定主关键字列有NULL 属性。
  • 多列组成的主键叫 联合主键,而且联合主键约束只能设定为表级约束;单列组成的主键,既可设定为列级约束,也可以设定为表级约束。
  • 唯一约束:指定 一个或多个列 的组合的值具有惟一性,以防止在列中输入重复的值。惟一性约束指定的列可以有NULL 属性。由于主关键字值是具有惟一性的,因此主关键字列不能再设定惟一性约束。惟一性约束最多由16 个列组成。
  • 检查约束:对输入列或整个表中的值设置检查条件,以限制输入值,保证数据库的数据完整性。可以对每个列设置复合检查。
  • 默认约束:通过定义列的缺省值或使用数据库的缺省值对象绑定表的列,来指定列的缺省值。

约束操作

  1. 添加约束

    ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束条件;

  2. 删除约束

    ALTER TABLE 表名 DROP CONSTRAINT 约束名;

  3. 关闭约束

    ALTER TABLE 表名 DISABLE CONSTRAINT 约束名 CASCADE; Tips: 如果没有被引用则不需 CASCADE 关键字

  4. 打开约束

    ALTER TABLE 表名 ENABLE CONSTRAINT 约束名; Tips: 打开一个先前关闭的被引用的主键约束, 并不能自动打开相关的外部键约束

  5. 栗子
-- 添加主键约束(将 stuNo 作为主键)
Alter Table stuInfo
Add Constraint  PK_stuNO primary Key(stuNo);

-- 添加唯一约束(stuID 唯一)
Alter Table stuInfo
Add Constraint UQ_stuID unique(stuID);

-- 添加默认约束(stuAddress 的默认值为 "地址不详")
Alter Table stuInfo
Add Constraint DF_stuAddress default('地址不详') for stuAddress;

-- 添加检查约束(对 stuAge 加以限制,15~20之间)
Alter Table stuInfo
Add Constraint CK_stuAge check(stuAge between 15 and 20);
Alter Table stuInfo
Add Constraint CK_stuSex check(stuSex='男' or stuSex='女');

-- 添加外键约束(主表 stuInfo 和从表 stuMarks 建立关系,关联字段 stuNo)
Alter Table stuMarks
Add Constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo);

Tips:约束名的命名规则推荐采用 约束类型_约束字段 的形式

索引操作

索引是一种特殊的查询表,可以被数据库搜索引擎用来加速数据的检索。简单说来,索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。

  1. CREATE INDEX – 创建索引(搜索键)
  2. 单列索引:基于单一的字段创建

    CREATE INDEX index_name ON table_name (column_name);

  3. 唯一索引:不止用于提升查询性能,还用于保证数据完整性。(不允许向表中插入任何重复值)

    CREATE UNIQUE INDEX index_name ON table_name (column_name);

  4. 聚簇索引:在表中两个或更多的列的基础上建立

    CREATE INDEX index_name ON table_name (column1, column2);

  5. DROP INDEX – 删除索引

    DROP INDEX table_name.index_name;

Tips

  • 删除索引时要特别注意,数据库性能可能会因此而降低或提高。
    [En]

    Special care should be taken when deleting indexes, as database performance may be reduced or improved as a result.*

  • 隐式索引是数据库服务器在创建某些对象时自动生成的。(例如,对于主键约束和唯一约束,数据库服务器会自动创建索引)
    [En]

    implicit indexes are automatically generated by the database server when some objects are created. (for example, for primary key constraints and unique constraints, the database server automatically creates indexes)*

  • 索引创建原则:
  • 仅在被频繁检索的字段上创建索引;
  • 针对大数据量的表创建索引,而不是针对只有少量数据的表创建索引;
  • 尽量不要在有大量重复值得字段上建立索引(比如性别字段、季度字段等)
  • 不在频繁进行大批量的更新或者插入操作的表创建索引;
  • 不在频繁操作的列创建索引;

DML(数据操作语言)

数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。

CURD 是一个数据库技术中的缩写词,一般的项目开发的各种参数的基本功能都是CURD。作用是用于处理数据的基本原子操作。它代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作。

SELECT – 从数据库表中获取数据

SELECT column_name(s) FROM table_name WHERE condition AND|OR condition;

UPDATE – 更新数据库表中的数据

  • 常规更新

    UPDATE table_name SET col_name1=value1 WHERE condition;

  • 更新日期
  • NOW() 函数

    UPDATE table_name SET birthday=NOW() WHERE condition;

  • STR_TO_DATE() 函数

    UPDATE table_name SET birthday=STR_TO_DATE(‘2022-04-18′,’%Y-%m-%d’) WHERE condition;

  • 把原表(src_table)的值设置到本表(tar_table)中

    UPDATE src_table st,tar_table tt SET tt.tar_column=st.src_column WHERE tt.tar_condition | tt.tar_column=st.src_column;

DELETE – 从数据库表中删除数据

  • 请空表

    DELETE FROM table_name [WHERE condition];

  • 截断表

    TRUNCATE TABLE table_name;

INSERT INTO – 向数据库表中插入数据

常规插入

  • 单条记录插入
  • 全表插入

    INSERT INTO table_name VALUES (value1, value2, value3, …);

  • 插入指定字段

    INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

  • 批量插入
  • 批量全表插入
INSERT INTO table_name
VALUES
  (value1, value2, value3, ...),
  (value1, value2, value3, ...),
  ...

  (value1, value2, value3, ...);
  • 批量插入指定字段
INSERT INTO table_name (column1, column2, column3)
VALUES
  (value1, value2, value3),
  (value1, value2, value3),
  ...

  (value1, value2, value3);

Tips:

  • 插入整表时,需要为值列表中的每个字段指定值,并且值的顺序必须与数据表中字段的定义顺序相同
    [En]

    when inserting a full table, values need to be specified for each field in the values list, and the order of values must be the same as the order in which the fields in the data table are defined*

  • 插入指定字段时,在INSERT语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值;
  • 在 INSERT 子句中随意列出列名,但是一旦列出,VALUES中要插入的value1,…valuen需要与column1,…columnn 列一一对应。如果类型不同,将无法插入,并且MySQL会产生错误。
  • INSERT语句可以同时向数据表中插入多条记录,插入时指定多个值列表,每个值列表之间用逗号分隔开;

插入查询结果集

  • 将查询的结果集插入表中
INSERT INTO tar_table (tar_column1, tar_column2, ...)SELECT (src_column1, src_column2, ...) FROM src_table[WHERE src_condition];

Tips:
– 在 INSERT 语句中加入子查询;
– 不必书写 VALUES 子句;

MySQL 计算列(虚拟列)

  1. MySQL 5.7 引入了生成列,支持虚拟和存储两种类型的生成列;
  2. 语法:
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED]
  [NOT NULL | NULL]
  [UNIQUE [KEY]]
  [[PRIMARY] KEY]
  [COMMENT 'string']

Tips:
+ GENERATED ALWAYS 可以省略;
+ AS (expr) 用于生成计算列值的表达式;
+ VIRTUAL 或 STORED 关键字表示是否存储计算列的值:
* VIRTUAL:列值不存储,虚拟列不占用存储空间,默认设置为 VIRTUAL
* STORED:在添加或更新行时计算并存储列值; 存储列需要存储空间,并且可以创建索引;
+ 如果表达式的结果类型与字段定义中的数据类型不同,将会执行隐式的类型转换
+ 生成列支持 NOT NULLUNIQUE主键CHECK以及 外键 约束,但是不支持 DEFAULT 默认值;
+ Generated column 表达式必须遵循以下规则:
* 允许使用常量、确定性的内置函数以及运算符;
确定性函数 意味着对于表中的相同数据,多次调用返回相同的结果,与当前用户无关;
非确定性函数 包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等;
* 不允许使用存储函数和自定义函数;
* 不允许使用存储过程和函数的参数;
* 不允许使用变量(系统变量、自定义变量或者存储程序中的本地变量);
* 不允许子查询;
* 允许引用表中已经定义的其他生成列;允许引用任何其他非生成列,无论这些列出现的位置在前面还是后面;
* 不允许使用 AUTO_INCREMENT 属性;
* 不允许使用 AUTO_INCREMENT 字段作为生成列的基础列;
* 可以在计算列上创建索引,但不能在 VIRTUAL 类型的计算列上创建聚集索引;
– 栗子

-- 创建表 t_test ,其中计算列 sum 的值为 (a + b)
CREATE TABLE t_test (
  a INT NOT NULL,
  b INT NOT NULL,
  sum INT GENERATED ALWAYS AS (a + b) [VIRTUAL]
);

-- 添加计算列 area 值为 (a * b)
ALTER TABLE t_test ADD area INT AS (a * b) STORED;
  1. 其他 SQL 类型
  2. 其他 SQL 类型的计算列

DCL(数据控制语言)

DCL 包括对基本表和视图的授权,完整性规则的描述,事务控制等内容。用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

GRANT – 授权

将指定 操作对象 的指定 操作权限 授予指定的 用户; 发出该 GRANT语句的可以是数据库管理员,也可以是该数据库对象的创建者;

  1. 查询
  2. 查看用户自己权限

    SHOW GRANTS;

  3. 查看其他用户权限

    SHOW GRANTS FOR ‘username’@’host’; Tips: host 可以使用通配符 %;如 ‘user’@’%’, ‘user’@’192.168.0.%’;

  4. 授权
  5. 语法

    GRANT 权限 ON 数据库对象 TO 用户 [WITH GRANT OPTION];

  6. 栗子
    • 授予 super用户所有权限

      GRANT ALL [PRIVILEGES] ON . TO username;

    • 授予用户 INSERT 权限

      CREATE INSERT ON db_name.* username@’localhost’ IDENTIFIED BY ‘newpasswd’;

    • 授予用户 SELECT, UPDATE, DELETE 权限

      GRANT SELECT, UPDATE, DELETE ON db_name.* TO username

  7. 刷新

    flush privileges;

Tips:

  • WITH GRANT OPTION: 表示是否能传播其权限;(授权命令是由数据库管理员使用的)
  • 指定 WITH GRANT OPTION,则获得该权限的用户可以把这种权限授予其他用户;但不允许循环传授,即被授权者不能把权限在授回给授权者或祖先;
  • 未指定,则获得某种权限的用户只能自己使用该权限,不能传播该权限;
  • GRANT 关键字之后指定 一个或多个特权。如果要授予用户多个权限,则每个权限都将以 逗号分隔(见下表中的特权列表);
  • 指定确定特权应用级别的privilege_level;
  • MySQL支持全局(*.*),数据库(database.*),表(database.table)和列级别;
  • 如果您使用列权限级别,则必须在每个权限之后使用 逗号分隔列的列表;
  • 如果授予权限的用户已经存在,则GRANT语句修改其特权; 如不存在,则GRANT语句将创建一个新用户; 可选的条件 IDENTIFIED BY 允许为用户设置新密码;

REVOKE – 回收授权

  • 语法

    REVOKE 权限 ON 数据库对象 FROM 用户 [CASCADE | RESTRICT];

  • 栗子
  • 回收全部权限

    REVOKE ALL [PRIVILEGES] ON . FROM username;

  • 回收 INSERT 权限

    REVOKE INSERT ON db_name.* FROM username;

Tips

  • CASCADE | RESTRICT 当检测到关联的特权时,RESTRICT(默认值) 导致REVOKE失败;
  • CASCADE 可以回收所有这些关联的特权;(如U1授权U2, U2授权U3,此时使用级联(CASCADE)收回了U2和U3的权限,否则系统将拒绝执行该命令)

更多GRANT和REVOKE参考 https://www.cnblogs.com/librarookie/p/16160252.html

Original: https://www.cnblogs.com/librarookie/p/16160876.html
Author: Librarookie
Title: MySQL 基础

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505470/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • Nginx 反向代理、Rewrite

    Rewrite功能配置 Rewrite是Nginx服务器提供的一个重要基本功能,是Web服务器产品中几乎必备的功能。主要的作用是用来实现URL的重写。www.jd.com注意:Ng…

    数据库 2023年6月6日
    092
  • TypeScript语言基础

    一、什么是TypeScript 编程语言包括动态类型语言和静态类型语言。动态类型语言是指在程序运行阶段才检查变量数据类型的语言,在定义变量时不需要指定变量的数据类型,通常在编译时不…

    数据库 2023年6月14日
    097
  • Tomcat的安装和使用

    一·Tomcat 安装 1、Toncat下载 通过访问Tomcat官方网站下载Tomcat文件。 Tomcat提供了压缩版和安装版,还区分 32 位和 64 位系统版。下载的时候注…

    数据库 2023年6月11日
    099
  • mac(m1)配置my.cnf

    今天开始学习了数据库,在安装MySQL之后启动一直报错,然后在网上找了很多解决方法,最后用以下方法解决 对于习惯了windows的小伙伴来说,直接去安装目录里边修改my.ini就可…

    数据库 2023年6月16日
    079
  • [LeetCode]26. 删除排序数组中的重复项

    给定一个排序数组,你需要在 原地 删除重复出现的元素,使得每个元素只出现一次,返回移除后数组的新长度。 不要使用额外的数组空间,你必须在 原地 修改输入数组 并在使用 O(1) 额…

    数据库 2023年6月9日
    0128
  • MySQL 卸载与安装

    MySQL 卸载与安装 以Windows10操作系统为例: 一、 先看看如何卸载 1、首先,停止window的MySQL服务,【windows键+R 】打开运行框,输入【servi…

    数据库 2023年5月24日
    079
  • MSQL–>存储引擎

    概述 MySQL体系结构图 Innodb引擎是在mysql的5.5版本之后的默认存储引擎。 Index是在引擎层次的,不同的存储引擎index的用法不同。 存储引擎就是存储数据,建…

    数据库 2023年6月14日
    089
  • 得体的注释,让我总能想起TA

    作为一个技术TL,在排查生产问题时,我经常要周转于各个工程里。系统和服务多起来后,要我了解每一段代码具体的来龙去脉逐渐几乎不可能了。 例如,今天,我要查一下调用某个三方接口所配置的…

    数据库 2023年6月9日
    0177
  • postman自动化测试

    postman做接口的自动化测试case 记录一次自动化测试的工作,以及该过程中对于测试设计的一些思考。 postman工具 简单介绍,这个工具无论是开发还是测试,使用来调试接口的…

    数据库 2023年6月6日
    0125
  • Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually.

    用pycharm连接mysql报错改变serverTimezone改了之后确实可以连接上mysql数据库 人生很长,一起加油 Original: https://www.cnblo…

    数据库 2023年6月6日
    093
  • MySql数据库备份与还原

    备份(mysqldump) 实现功能: 1、备份指定的数据库 2、删除指定天数前的备份文件,默认设定了1天 脚本示例(mysql_bak.sh) 数&…

    数据库 2023年6月16日
    0100
  • Docker Bridge 网络原理

    Docker 的网络子系统是可插拔驱动式的,默认情况下存在或支持多种网络接口,如 bridge、host、overlay、macvlan 以及 none 类型的网络接口。 brid…

    数据库 2023年6月6日
    099
  • django-ckeditor配置html5video上传视频

    参考信息 为Django ckeditor配置上传视频:https://www.byincd.com/bobjiang/article-01128/ 使用 1. 手动下载插件 ht…

    数据库 2023年6月9日
    097
  • idea tags

    总结IDEA开发的26个常用设置https://zhuanlan.zhihu.com/p/108172369idea跳转到指定行列快捷键https://blog.51cto.com…

    数据库 2023年6月11日
    092
  • IntelliJ IDEA community 安装教程

    jetbrains官网下载 IntelliJ IDEA安装包 此处选择社区版的zip文件 下载完成后解压安装包,此处解压目录为 E:\IntelliJ IDEA\ 开始安装首先添加…

    数据库 2023年6月11日
    0110
  • 2022-8-11 网络编程(网络通信)

    网络协议 通过计算机网络可以使多台计算机实现连接,位于同一个网络中的计算机在进行连接和通信时需要遵守一定的规则,这就好比在道路中行驶的汽车一定要遵守交通规则一样。在计算机网络中,这…

    数据库 2023年6月14日
    0109
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球