百木园-与人分享,
就是让自己快乐。

MySQL 基础

MySQL 基础

  • MySQL 基础
    • SQL 介绍
    • DDL(数据定义语言)
      • 数据库操作
      • 数据表操作
      • 常用约束
        • 约束操作
      • 索引操作
    • DML(数据操作语言)
      • SELECT - 从数据库表中获取数据
      • UPDATE - 更新数据库表中的数据
      • DELETE - 从数据库表中删除数据
      • INSERT INTO - 向数据库表中插入数据
        • 常规插入
        • 插入查询结果集
      • MySQL 计算列(虚拟列)
    • DCL(数据控制语言)
      • GRANT - 授权
      • REVOKE - 回收授权

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

SQL 介绍

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

  • SQL 是什么?

    • SQL 指结构化查询语言

    • SQL 使我们有能力访问数据库

    • SQL 是一种 ANSI 的标准计算机语言

      note: 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=\"字符编码\"];
    
  2. ALTER TABLE - 变更(改变)数据库表

    • 修改表名字

      ALTER TABLE old_table RENAME AS new_table;

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

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

    • 修改字段

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

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

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

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

    • 删除字段

      ALTER TABLE table_name DROP [COLUMN] column_name;

  3. DROP TABLE - 删除表

    DROP TABLE [IF EXISTS] table_name;

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

  4. 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 - 创建索引(搜索键)

    • 单列索引:基于单一的字段创建

      CREATE INDEX index_name ON table_name (column_name);

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

      CREATE UNIQUE INDEX index_name ON table_name (column_name);

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

      CREATE INDEX index_name ON table_name (column1, column2);

  2. DROP INDEX - 删除索引

    DROP INDEX table_name.index_name;

Tips:

  • 删除索引时应当特别小心,数据库的性能可能会因此而降低或者提高
  • 隐式索引由数据库服务器在创建某些对象的时候自动生成。(例如,对于主键约束和唯一约束,数据库服务器就会自动创建索引)
  • 索引创建原则:
    • 仅在被频繁检索的字段上创建索引;
    • 针对大数据量的表创建索引,而不是针对只有少量数据的表创建索引;
    • 尽量不要在有大量重复值得字段上建立索引(比如性别字段、季度字段等)
    • 不在频繁进行大批量的更新或者插入操作的表创建索引;
    • 不在频繁操作的列创建索引;

DML(数据操作语言)

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

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:

  • 全表插入时,值列表中需要为表的每一个字段指定值,并且值的顺序必须和数据表中字段定义时的顺序相同;
  • 插入指定字段时,在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 引入了生成列,支持虚拟和存储两种类型的生成列;

    • 语法:

      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;
      
  2. 其他 SQL 类型

    • 其他 SQL 类型的计算列

DCL(数据控制语言)

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

GRANT - 授权

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

  1. 查询

    • 查看用户自己权限

      SHOW GRANTS;

    • 查看其他用户权限

      SHOW GRANTS FOR \'username\'@\'host\';

    Tips: host 可以使用通配符 %;如 \'user\'@\'%\', \'user\'@\'192.168.0.%\';

  2. 授权

    • 语法

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

    • 栗子

      • 授予 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

  3. 刷新

    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/16160876.html
本站部分图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » MySQL 基础

相关推荐

  • 暂无文章