MySQL基础学习

MySQL基础学习

• MySQL 由瑞典MySQL AB公司开发，目前属于Oracle公司
• MySQL是一个开源的关系型数据库管理系统。
• MySQL分为社区版和企业版

MySQL目录结构

• bin目录，存储可执行文件
• data目录，存储数据文件
• docs，文档
• include目录，存储包含的头文件
• lib目录，存储库文件
• share目录，错误消息和字符集文件

启动MySQL服务

    net start mysql

关闭MySQL服务

    net stop mysql

MySQL登录

        mysql 参数

      MySQL退出

        > exit;

        > quit;

        > \q;

修改MySQL提示符

      ① 连接客户端时通过参数指定

           shell>mysql -uroot -proot --prompt 提示符

      ② 连接上客户端后，通过prompt

           mysql>prompt 提示符

          参数及描述：

          \D  --  完整的日期;       \d  --  当前数据库；           \h -- 服务器名称；      \u-- 当前用户

显示当前服务器版本

      SELECT VERSION

MySQL语句的规范：

• 关键字与函数名称全部大写
• 数据库名称、表名、字段名称全部小写
• SQL语句必须以分号结尾

创建数据库：

    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name[DEFAULT] CHARACTER SET [=] charset_name

查看当前服务器下的数据表列表：

    SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

修改数据库：

       ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

删除数据库：

    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 

数据类型：是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础

打开数据库：  USE 数据库名称（所要使用的数据库必须是已经存在的）

创建数据表：

    CREATE TABLE [IF NOT EXISTS] table_name(

       column_name data_type, 

       ...

     )

查看数据表列表：

       SHOW TABLES [FROM da_name] [LIKE 'pattern' | WHERE expr ]

查看数据库表结构：

    SHOW COLUMNS FROM tbl_name

插入记录：

      INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)

记录查找：

    SELECT expr,...FROM tbl_name

空值与非空：

    NULL,字段值可以为空

    NOT NULL,字段值禁止为空

自动编号：

    AUTO_INCREMENT

    自动编号，且必须与主键组合使用

    默认情况下，起始值为1，每次的增量为1

主键约束：

    PRIMARY KEY

    主键约束

    每张数据表只能存在一个主键

    主键保证记录的唯一性

    主键自动改为NOT NULL

唯一约束：（可以有空值，但是一张表只能有一个空值）

    UNIQUE KEY

    唯一约束可以保证记录的唯一性

    唯一约束的字段可以为空值（NULL）

    每张数据表可以存在多个唯一约束

默认值：

    DEFAULT

    当插入记录时，如果没有明确的为字段赋值，则自动赋予默认值

约束：

1. 约束保证数据的完整性和一致性
2. 约束分为表级约束和列级约束
3. 约束类型包括：

            NOT NULL（非空约束）

            PRIMARY KEY（主键约束）

            UNIQUE KEY（唯一约束）

            DEFAULT（默认约束）

            FOREIGN KEY（外键约束）

外键约束：

       保持数据一致性，完整性

     实现一对一或一对多关系

外键约束的要求：

    ① 父表与子表必须使用相同的存储引擎，而且禁止使用临时表

    ② 数据表的存储引擎只能为InnoDB

    ③ 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同

    ④ 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引

编辑数据表的默认存储引擎：

    MySQL配置文件   default-storage-engine=INNODB

外键约束的参照操作：

    ① CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行

    ② SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

    ③ RESTRICT:拒绝对父表的删除或更新操作

    ④ NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

表级约束与列级约束：

    对一个数据列建立的约束，称为列级约束

    对多个数据列建立的约束，称为表级约束（只能在表定义之后再定义）

    列级约束既可以再列定义时声明，也可以在列定义后声明

    表级约束只能在列定义后声明

修改数据表：

    添加单列

        ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] 

        注：如不添加列的位置，则默认在列的最后

    添加多列：

        ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

    删除列：

        ALTER TABLE tbl_name DROP  [COLUMN] col_name

    添加主键约束：

        ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

    添加唯一约束：

        ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,..)

    添加外键约束：

        ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

    删除主键约束：

        ALTER TABLE tbl_name DROP PRIMARY KEY

    删除唯一约束：

       ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

    删除外键约束：

        ALTER TABLE tbl_name DROP  FOREIGN KEY fk_symbol

    修改列定义：

       ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

    修改列名称：

        ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

    数据表更名：

        方法1

           ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

        方法2

          ALTER TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new tbl_name2]...

插入记录：

    方法一

    INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE } ({expr | DEFAULT},...)

    方法二

     INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},....

     注：与第一种方式的区别在于，此方法可以使用子查询（SubQuery），第二种一次只能插进一条记录

    方法三

    INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

     注：此方法可以将查询结果插入到指定数据表。

更新记录（单表更新）：

    UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}] [WHERE where_condition]

    注：没有WHERE限制，将更新所有记录

删除记录（单表删除）：

    DELETE FROM tbl_name [WHERE condition]

    注：没有WHERE限制，将删除所有记录

查找记录：

    SELECT select_expr [,select_expr...]

    [

       FROM table_references

       [WHERE where_condition]

       [GROUP BY {col_name | position} [ASC | DESC],...]

       [HAVING where_condition]

       [ORDER BY {col_name | expr | position} [AS | DESC],...]

       [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    ]

  select_expr：查询表达式

      每一个表达式表示想要的一列，必须有至少一个。

      多个列之间以英文逗号分隔

      星号（*）表示所有列。tbl_name.*可以表示命名表的所有列

      查询表达式可以使用[AS] alias_name为其赋予别名

       别名可用于GROUP BY，ORDER BY或HAVING子句

  WHERE：（条件表达式）

      对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。在WHERE表达式中，可以使用MySQL支持的函数或运算符。

   GROUP BY：（查询结果分组）

       [GROUP BY {col_name | position} [ASC | DESC] , ...]

   HAVING:(分组条件)

       [HAVING where_condition]

       注：条件必须是一个聚合函数或条件字段必须是出现在SELECT中，否则会报错

  ORDER BY:(对查询结果进行排序)

      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]

  LIMIT:（限制查询结果返回的数量）

      [LIMIT {[offset,] row_count | row_count OFFSET offset}]

       注：offset：是从第几个开始，但是该计数是从0开始计数的

子查询：

    子查询（SubQuery）是指出现在其他SQL语句内的SELECT子句

    子查询指嵌套在查询内部，且必须始终出现在圆括号内

     子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY，LIMIT、函数等

     子查询的外层查询可以使SELECT、INSERT、UPDATE，SET或DO

    子查询返回值：可以返回标量、一行、一列或子查询

     使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>

     语法结构：operand comparison_operator subquery

    使用[NOT] EXISTS的子查询：

        如果子查询返回任何行，EXISTS将返回TRUE，否则返回FALSE

     INSERT...SELECT：

        将查询结果写入数据表：INSERT [INTO] tbl_name [(col_name,...)] SELECT...

多表更新：

    UPDATE table_references SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]...[WHERE where_condition]

    语法结构（表的参照关系）

      table_reference  {[INNER | CROSS ] JOIN| {LEFT | RHGHT} [OUTER] JOIN} table_reference ON conditional_expr

    连接类型：INNER  JOIN，内连接

        在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的

        LEFT [OUTER] JOIN,左外连接

        RIGHT [OUTER] JOIN,右外连接

    CREATE...SELECT：

       创建数据表同时将查询结果写入到数据表：

        CREATE  TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

连接：MySQL在SELECT语句、多表更新、夺标删除语句中支持JOIN操作 

    语法结构：

        table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON condidtional_expr

    数据表参照：

        table_reference tbl_name [[AS] alias | table_subquery [AS] alias]

        数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。

        table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

    连接类型：

        INNER JOIN，内连接（仅显示符合连接条件的记录）：在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。

        LEFT [OUTER] JOIN,左外连接（显示左表中的全部和右中符合条件的，如果由表中不存在的，则为空）

        RIGHT [OUTER] JOIN,右外连接（显示右表中的全部记录及左表符合条件的记录）

   连接条件：使用ON关键字来设定连接条件，也可以使用WHERE来代替

       通常使用ON关键字来设定连接条件

       使用WHERE关键字进行结果集记录的过滤

   外连接：

      A LEFT JOIN B join_condition

      数据表B的结果集依赖数据表A。数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

      如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：col_name IS NULL 时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索横多的行

    自身连接:(用于无限级分类表设计查询等)

        同一个数据表对其自身进行连接

多表删除：

    DELETE tbl_name[.*] [,tbl_name[.*]]...FROM  table_references [WHERE where_condition]

存储过程：（存储过程第一次是以前一样的，但是它编译的存在数据库中，以后每次访问都加快了）

    存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

     优点：

        ① 增强SQL语句的功能和灵活性

        ② 实现较快的执行速度

        ③ 减少网络流量

     注意事项：

        ① 创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。

        ② 如果函数体或过程体有多个语句，需要包含在BEGIN......END语句快中

        ③ 存储过程通过CALL来调用

创建存储过程：

    CREATE [DEFINER = { user | CURRENT_USNER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

     proc_parameter:

        [IN | OUT | INOUT] param_name type

     参数：IN             表示该参数的值必须在调用存储过程时指定

                OUT         表示该参数的值可以被存储过程改变，并且可以返回

                INOUT      表示该参数的调用时指定，并且可以被改变和返回

     过程体：

         ① 过程体由合法的SQL语句构成

         ② 过程体可以是任一SQL语句

         ③ 过程体如果为复合结构则使用BEGIN ... END语句

         ④ 复合结构可以包含声明，循环，控制结构

调用存储过程：

    CALL sp_name([parameter[,...]])

    CALL sp_name[()]

修改存储过程：（只能修改一些特性，并不能修改存储过程体，如果要修改过程体，只能先删除存储过程，再建立存储过程）

    ALTER  PROCEDURE sp_name [characteristic ...] COMMENT 'string' | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY { DEFINER | INVOKER}}

删除存储过程：

    DROP PROCEDURE [IF EXISTS] sp_name

存储过程与自定义函数的区别：

    ① 存储过程实现的功能要复杂一些；而函数的针对性更强

    ② 存储过程可以返回多个值；函数只能有一个返回值

    ③ 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现

存储引擎：

    MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎每一种存储引擎使用不同的存储机制，索引技巧、锁定水平，最终提供广泛且不同的功能。

MySQL支持的存储引擎：

     存储引擎：

        - MyISAM

        - InnoDB

        - Memory

        - CSV

        - Archive

      并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性

      锁：

         - 共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化

         - 排它锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作

      锁颗粒：

         - 表锁：是一种开销最小的锁策略

         - 行锁：是一种开销最大的锁策略（一个表可能加多个锁）

     事务：

         - 事务用于保证数据库的完整性

      事务特性：

         原子性、一致性、隔离性、持久性

      外键：

         - 是保证数据一致性

       索引：

         - 是对数据表中一列或多列的值进行排序的一种结构

      

    注：常用MyISAM和InnoDB引擎，MyISAM用于处理事务不多的操作

    存储引擎：

        MyISAM：存储限制可达256TB，支持索引，表级索引、表级锁定、数据压缩

        InnoDB：存储限制为64TB，支持事务和索引，锁颗粒为行锁。