MySQL基础知识

1、SQL：结构化查询语言(Structured Query Language)：

DDL：Data Definition Language（数据定义语言）： 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

DML：Data Manipulation Language（数据操作语言） ：其语句包括动词 INSERT， UPDATE和 DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

编程接口

存储过程：procedure

存储函数：function

触发器：trigger

事件调度器：event scheduler

2、三层模型

物理层：决定数据的存储格式，即如何将数据组织成为物理文件；

逻辑层：描述DB存储什么数据，以及数据间存在什么样的关系；

视图层：描述DB中的部分数据

3、MySQL客户端交互式CLI工具：mysql

常用选项：

-u, –user=username：用户名，默认为root

-h, –host=hostname：远程主机（即mysql服务器）地址，默认为localhost; 客户端连接服务端，服务器会反解客户的IP为主机名，关闭此功能(skip_name_resolve=ON)

-p, –password[=PASSWORD]：USERNAME所表示的用户的密码； 默认为空

注意：mysql的用户账号由两部分组成：‘USERNAME’@’HOST’; 其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务

HOST的表示方式，支持使用通配符

%：匹配任意长度的任意字符

172.16.%.%,  172.16.0.0/16

_：匹配任意单个字符

-P, –port=#：mysql服务器监听的端口；默认为3306/tcp

-S, –socket=/PATH/TO/mysql.sock：套按字文件路径

-D, –database=DB_name：连接到服务器端之后，设定其处指明的数据库为默认数据库

-e, –execute=’SQL STATEMENT’：连接至服务器并让其执行此命令后直接返回

4、MySQL数据类型：

• 字符型：

                定长字符型：

CHAR(#)：不区分字符大小写（0-255字节）

BINARY(#)：区分字符大小写

                 变长字符型：

VARCHAR(#)：多占一个或两个字符空间（最后要有一个字符串结束标记）不区分字符大小写（0-65535 字节）

VARBINARY(#)：区分字符大小写

                对象存储：

TEXT：不区分字符大小写；TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT

BLOB：Binary Large OBject，区分字符大小写；TINYBLOB，SMALLBLOB，MEDIUMBLOB，BLOB，LONGBLOB；

• 数值型：

              精确数值型：

              近似数值型：

                            FLOAT

                            DOBULE

• 日期时间型：

• 字符类型修饰符：

              NOT NULL：非空约束

              NULL：允许为空

              DEFAULT value：指定默认值

CHARACTER SET：使用的字符集

              COLLATION：使用的排序规则

AUTO_INCREMENT：自动增长

• 整型数据修饰符

              NOT NULL：非空约束

              DEFAULT NUMBER：指定默认值

              PRIMARY KEY：主键

              UNIQUE KEY：惟一键

AUTO_INCREMENT：自动增长

5、数据定义语言（DDL）：

（1）数据库

创建：

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;

CREATE  {DATABASE 或SCHEMA}后面指明数据库名称

[DEFAULT]  CHARACTER SET [=] charset_name

定义默认字符集

[DEFAULT]  COLLATE [=] collation_name

定义排序规则

查看支持的所有字符集：SHOW CHARACTER SET

查看支持的所有排序规则：SHOW  COLLATION

修改：

ALTER {DATABASE | SCHEMA}  [db_name]

[DEFAULT]  CHARACTER SET [=] charset_name

[DEFAULT]  COLLATE [=] collation_name

删除：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

查看：

SHOW DATABASES LIKE ‘  ’;

（2）表

创建：

CREATE TABLE [IF NOT EXISTS] [db_name.]tbl_name (create_defination) [table_options]

字段：col_name  data_type

添加：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]

删除：DROP  [COLUMN] col_name

修改：

CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name]

MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]

键：

PRIMARY KEY (col1, col2, …)

UNIQUE KEY  (col1, col2,…)

FOREIGN KEY (column)

添加：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,…)

删除：

主键：DROP PRIMARY KEY

外键：DROP FOREIGN KEY fk_symbol

查看数据库支持的所有存储引擎类型

mysql> SHOW  ENGINES；

查看某表的状态信息：

mysql> SHOW  TABLES  STATUS  [LIKE  ‘tbl_name’][WHERE clause]

修改：

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] …]

删除：

DROP  TABLE  [IF EXISTS]   tbl_name [, tbl_name] …    （不推荐使用）

表的引用方式：

tbl_name

db_name.tbl_name

（3）索引管理：

索引是特殊的数据结构；

索引：要有索引名称；

创建：

CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX  index_name  [BTREE|HASH]  ON tbl_name (col1, col2,,…)

删除：

DROP  INDEX index_name ON tbl_name

查看表上的索引的信息：

mysql> SHOW INDEXES FROM tbl_name;

（4）视图

创建

CREATE VIEW view_name AS select_statement;

删除

DROP VIEW view_name;

注意：视图不能插入数据，受基表（创建视图用到的表）限制，视图的数据事实上存储与”基表“中

6、数据操作语言（DML）：

【 INSERT、DELETE、SELECT、UPDATE】

      1.INSERT语句：一次插入一行或多行
  insert into table_name[(column[column…])]values(value[,value…]);默认情况下，一次插入操作只插入一行
  一次性插入多条记录：
    insert into table_name[(column[,column…])]values(value[,value…]),(value[,value…]);
  如果为每列都指定值，则表名后不需要列出插入的列名
  如果不想在表名后列出列名，可以为那些无法指定的值插入NUll
  如果需要插入其他特殊字符，应该采用\转义字符做前缀

      2.DELETE语句
  语法：delete [from] table_name [where condition];
  删除不需要指定列名，因为删除总是整行整行删除，where子句是一个条件表达式，只有符合该条件的行才会被删除。没有where子句意味着删除所有数据
  删除可以一次删除多行，删除哪些行采用where子句限定

      3.UPDATE语句
  语法：update table_name set column = value[,column = value]…[where condition];
    修改可以一次修改多行数据，修改的数据可用where子句限定，where子句是一个条件表达式，只有符合该条件的行才会被修改。没有where子句意味着where子句表达式值为true
    也可以同时修改多列，多列的修改中采用逗号隔开

      4.SELECT语句
  语法：select_expr [, select_expr ...] [FROM table_references [WHERE where_condition]
  where子句：指明过滤条件以实现选择的功能
     where子句限定返回的记录，where子句在from子句后
     where中的字符串和日期值，字符串和日期要用单引号括起来，字符串是大小写敏感的，日期值是格式敏感的
     比较操作符：>, >=, <, <=, =, <>, BETWEEN min_num AND max_num,
               in(element1,...)：匹配所有列出的值
               like:模糊匹配（%：任意值，_:任意单个字符
               IS NULL是空值
     逻辑运算符：ONT, AND, OR, XOR（相同为假，不同为真）
  group by子句
      group by子句的真正作用在于与各种聚合函数配合使用，它用来对查询出来的数据进行分组
      分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录
      分组函数忽略空值
      结果集隐式按升序排列，如果需要改变排序方式可以使用order by子句
      count(*|列名)  统计行数 avg(数值类型列名)  平均值 sum(数值类型列名)  求和
      max(列名)   最大值      min(列名)   最小值
      having子句用来对分组后的结果再进行条件过滤
  在select语句最后可以用limit来限定查询结果返回的起始记录和总数量
  对查询结果中的数据请求施加锁
       for update：写锁，独占锁
       lock in share mode：读锁，共享锁

7、数据库的设计
-当数据库比较复杂时我们需要设计数据库
-良好的数据库设计
-节约数据的存储空间
-能够保证数据的完整性
-方便进行数据库应用系统的开发

☆数据库设计规范化
-仅有好的DBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构
-数据库规范化的三个级别，范式是具有最小冗余的表结构，如下：
-第一范式：(1stNF first normal formate)
-第二范式：(2stNF second normal formate)
-第三范式：(3stNF third normal formate)

◆第一范式(1NF)（第一范式必须有主键）
-在关系模式R中的每一个具体关系r中，必须要有主键，并且每个属性都是不可再分的最小数据单位

◆第二范式(2NF)
-如果关系模式R中的所有非主属性都完全依赖于主关键字，则称关系R是属于第二范式

◆第三范式(3NF)
-关系模式R中的非主关键字不能依赖于其他非主关键，即非主关键字之间不能有函数(传递)依赖关系，则称关系R是属于第三范式

8、一个存储系统是否支持事务，测试标准：

ACID：

ACID是数据库事务正确执行的四个基本要素的缩写，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库系统，必需要具有ACID特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

A：原子性

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

C：一致性

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性

I：隔离性

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据

D：持久性

在事务完成以后，该事务对数据库所作的更改持久的保存在数据库之中，并不会被回滚。

由于一项操作通常会包含许多子操作，而这些子操作可能会因为硬件的损坏或其他因素产生问题，要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕，但实际上并不可行。 目前主要有两种方式实现ACID：第一种是Write ahead logging，也就是日志式的方式(现代数据库均基于这种方式)。第二种是Shadow paging。

9、编译安装mariadb-5.5.44

# yum install cmake
# yum groupinstall "Development Tools" "Server Platform Development"
# groupadd -r -g 306 mysql
# useradd -r -g 306 mysql -u 306 mysql
# tar xf mariadb-5.5.44.tar.gz
# cd maeradb-5.5.44
# cmake
DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-5.5.44\    安装路径
DMYSQL_DATADIR=/mydata/data\                        数据目录
DSYSCONFDIR=/etc                                    指定安装文件的安装路径
DMYSQL_UNIX_ADDR=/tmp/mysqld.sock\                  sock文件
DDEFAULT_CHARSET=utf8\                              默认字符集
DDEFAULT_COLLATION=utf8_general_ci\                 默认排序规则
DWITH_INNOBASE_STORAGE_ENGINE=1\                    默认编译的存储引擎
DWITH_ARCHIVE_STORAGE_ENGINE=1\
DWITH_BLACKHOLE_STORAGE_ENGINE=1\
DWITHOUT_READLINE=1\
DWITH_ZLIB=system\
DWITH_SSL=system\
DWITH_LIBWRAP=0\
# make
# make install
# cd /usr/local
# ln -sv mariadb-5.5.44 mysql
# chmod -R root.mysql ./*
# ll
# scrips /mysql_install_db --user=mysql --datadir=/mydata/data 
# cp support-file/my-large.cnf /etc/mysql/my.cnf
# vim /etc/mysql/my.cnf
     datadir=/mydata/data
     innodb_file_per_table = ON
     skip_name_resolve = ON
# cp support-file/mysql.server /etc/rc.d/init.d/mysqld
# chmod +x /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# service mysqld start
# /usr/local/mysql/bin/mysql_secure_installation     给所有用户设定密码，删除匿名用户
# /usr/local/mysql/bin/mysql