2017-1-22(MySQL)

笔记

MySQL体系结构

安装MySQL

安装方式：
1、源代码：编译安装
2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
3、程序包管理器管理的程序包
    CentOS安装光盘
    项目官方：https://downloads.mariadb.org/mariadb/repositories/
4、安装和使用MariaDB
    CentOS 7直接提供
    CentOS6

MariaDB的特性

MariaDB的特性：
    单进程，多线程
    插件式存储引擎：存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择
    存储引擎也称之为“表类型”
(1) 更多的存储的存储引擎
    MyISAM==> Aria
    InnoDB==> XtraDB
    Mysql5.5.8开始innoDB引擎是MYSQL默认引擎，InnoDB对比MyISAM的最大特点就是InnoDB支持事务
(2) 诸多扩展和新特性
(3) 提供了较多测试组件
(4) 开源

MariaDB包组

Centos7的MariaDB数据库包括两个包组：
    Mariadb mariadb-client
mariadb组包括下面包：
    mariadb-server：mandatory package
    mariadb-bench：optional package
    mariadb-test：optional package
mariadb-client组包括下面包:
    Mariadb：mandatory package
    MySQL-python：default package
    mysql-connector-odbc：default package
    libdbi-dbd-mysql：optional package
    mysql-connector-java：optional package
    perl-DBD-MySQL：optional package
配置文件：
    /etc/my.cnf和/etc/my.cnf.d/*.cnf

MariaDB安装

安装
    yum groupinstall mariadb mariadb-client 
    systemctl start mariadb
    systemctl enable mariadb
提高安全性
    mysql_secure_installation(执行此脚本)
    设置数据库管理员root口令
    禁止root远程登录
    删除anonymous用户帐号
    删除test数据库

MariaDB配置

侦听3306/tcp端口可以在绑定有一个或全部接口IP上
vim /etc/my.cnf
[mysqld]加一行：
skip-networking=1 关闭网络连接，只侦听本地客户端，所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock）可在/etc/my.cnf修改
firewall-cmd --permanent  --add-service=mysql
firewall-cmd --reload

二进制安装MariaDb

MariaDB程序

MariaDB的程序组成：
    Client: 
        mysql：CLI交互式客户端程序
        mysqldump, mysqladmin...
    Server:
        mysqld_safe
        mysqld
        mysqld_multi：多实例
服务器监听的两种socket地址：
    ip socket: 监听在tcp的3306端口，支持远程通信
    unix sock: 监听在sock文件上(/tmp/mysql.sock, /var/lib/mysql/mysql.sock)，仅支持本机通信
    server: localhost, 127.0.0.1 自动使用unix sock

客户端工具

命令行交互式客户端程序mysql选项：
    --print-defaults   默认选项
    --verbose  显示祥细信息
    -uUSERNAME: 用户名；默认为root
    -hHOST: 服务器主机; 默认为localhost
    -pPASSWORD：用户的密码;建议使用-p,默认为空密码
mysql用户账号由两部分组成：
    'USERNAME'@'HOST'
    HOST用于限制此用户可通过哪些远程主机连接mysql服务
    支持使用通配符：
        % 匹配任意长度的任意字符
            172.16.0.0/255.255.0.0 或172.16.%.%
        _  匹配任意单个字符

执行命令

运行mysql命令：默认空密码登录
    show databases; 数据库列表
    use mysql 进入mysql数据库
    select user();查看当前用户
    create user wang@'172.18.2.%' identified by 'centos' 创建用户
    select user,host,password from mysql.user; //表状查看用户和密码 
    select * from mysql.user\G 列表查看用户和密码等详细信息
    show processlist; 查看当前进程
    show databases; 查看数据库列表
    update mysql.user set host='172.18.2.27' where user='wang' 修改用户列表
    flush privileges; 刷新权限
安全初始化
    /usr/local/mysql/bin/mysql_secure_installatoin
登录系统：mysql –uroot –p 
客户端命令：本地执行
    mysql> help
    每个命令都完整形式和简写格式
    mysql> status 或\s
服务端命令：通过mysql协议发往服务器执行并取回结果
    每个命令都必须命令结束符号；默认为分号
    SELECT VERSION();

关系型数据库的常见组件

SQL语言的兴起与语法标准

• 20世纪70年代，IBM开发出SQL，用于DB2
• 1981年，IBM推出SQL/DS数据库
• 业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
• SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。这个标准在1992年进行了修订(SQL-92)，1999年再次修订(SQL-99)，目前最新的是SQL-2003

SQL语句构成

Keyword（关键词）组成clause（语句）
多条clause组成语句
    SELECT *                  SELECT子句
    FROM products             FROM子句
    WHERE price>400           WHERE子句
这是一组语句，由三个子句构成，SELECT,FROM和WHERE都是关键字

SQL语句分类

SQL语句：
    DDL: Data Defination Language（数据定义语言）
        CREATE, DROP, ALTER  //创建，删除，更改
    DML: Data Manipulation Language(通过数据操作语句)
        INSERT, DELETE, UPDATE //插入、删除、更新
    DCL：Data Control Language (授权)
        GRANT, REVOKE //批准，撤销 授权
    DQL：Data Query Language (数据查询：数据查询语言)
        SELECT //选择

SQL语言规范

在数据库系统中，SQL语句不区分大小写(建议用大写)
但字符串常量区分大小写
SQL语句可单行或多行书写，以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
注释：
    SQL标准：
        /*注释内容*/   多行注释
        --注释内容单行注释，注意有空格
    MySQL注释：#

数据库对象的命名规则

• 必须以字母开头
• 可包括数字和三个特殊字符（# _ $）
• 不要使用MySQL的保留字
• 同一database(Schema)下的对象不能同名

数据库操作

创建数据库：
    CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; 
    show variables like "%character_set%";//查看字符集
    CHARACTER SET 'character set name' //指定字符集
    COLLATE 'collate name'
    show create database feidb； //查看这个数据库当时怎么创建的
删除数据库
    DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 查看支持所有字符集：
        SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;
获取命令使用帮助：HELP KEYWORD;
查看数据库列表：SHOW DATABASES; 

创建表

CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)
字段信息
    col type1 
    PRIMARY KEY(col1,...)
    INDEX(col1, ...)
    UNIQUE KEY(col1, ...)
表选项：
    ENGINE [=] engine_name
        SHOW ENGINES;查看支持的engine类型
    ROW_FORMAT [=]     
    {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}获取
帮助：HELP CREATE TABLE;

表操作

查看所有的引擎：SHOW ENGINES;
查看表：SHOW TABLES [FROM db_name];
查看表结构：DESC [db_name.]tb_name;
删除表：DROP TABLE [IF EXISTS] tb_name;
查看表创建命令：SHOW CREATE TABLE tbl_name;
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'\G

数据类型

• 数据类型：
  
  • 数据长什么样？
  • 数据需要多少空间来存放？
• 系统内置数据类型和用户定义数据类型
• MySql支持多种列类型：
  
  • 数值类型
  • 日期/时间类型
  • 字符串(字符)类型
• 选择正确的数据类型对于获得高性能至关重要，三大原则：
  
  • 更小的通常更好，尽量使用可正确存储数据的最小数据类型
  • 简单就好，简单数据类型的操作通常需要更少的CPU周期
  • 尽量避免NULL，包含为NULL的列，对MySQL更难优化

1、整型
    tinyint(m)    1个字节 范围(-128~127)
    smallint(m)   2个字节 范围(-32768~32767)
    mediumint(m)  3个字节 范围(-8388608~8388607)
    int(m)        4个字节 范围(-2147483648~2147483647)
    bigint(m)     8个字节 范围(+-9.22*10的18次方)
    取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)
    int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围
    MySQL可以为整型类型指定宽度，例如Int(11)，对绝大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
2、浮点型(float和double)，近似值
    float(m,d)单精度浮点型8位精度(4字节) m总个数，d小数位
    double(m,d)双精度浮点型16位精度(8字节) m总个数，d小数位
    设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
3、定点数
    在数据库中存放的是精确值,存为十进制
    decimal(m,d) 参数m<65 是总个数，d<30且d<m 是小数位
    MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
    浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
    因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
4、字符串(char,varchar,_text)
    char(n)固定长度，最多255个字符
    varchar(n)可变长度，最多65535个字符
    tinytext可变长度，最多255个字符
    text可变长度，最多65535个字符
    mediumtext 可变长度，最多2的24次方-1个字符
    longtext可变长度，最多2的32次方-1个字符
    BINARY(M) 固定长度，可存二进制或字符，允许长度为0-M字节
    VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
    内建类型：ENUM枚举, SET集合
    char和varchar：、
        1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
        2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节。
        3.char类型的字符串检索速度要比varchar类型的快
    varchar和text：
        1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
        2.text类型不能有默认值•3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
5、二进制数据：BLOB
    BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写
    BLOB存储的数据只能整体读出
    TEXT可以指定字符集，BLOB不用指定字符集
6、日期时间类型
    date     日期'2008-12-2'
    time     时间'12:25:36'
    datetime 日期时间'2008-12-2 22:06:44'
    timestamp自动存储记录修改时间
    YEAR(2), YEAR(4)：年份
    timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

所有类型：
    NULL数据列可包含NULL值
    NOT NULL数据列不允许包含NULL值
    DEFAULT默认值
    PRIMARY KEY主键
    UNIQUE KEY  唯一键
    CHARACTER SET name指定一个字符集
数值型
    AUTO_INCREMENT自动递增，适用于整数类型
    UNSIGNED无符号

示例：

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

DROP TABLE [IF EXISTS] 'tbl_name';
ALTER TABLE 'tbl_name' 
    字段：
        添加字段：add
        ADD col1 data_type[FIRST|AFTER col_name]
        删除字段：drop
        修改字段：
        alter（默认值）, change（字段名）, modify（字段属性）
    索引:
        添加索引：add index
        删除索引: drop index
    表选项
        修改:
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助：Help ALTER TABLE

修改表示例

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER  TABLE s1 MODIFY phone int;
ALTER  TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER  TABLE s1 DROP COLUMN mobile;
Help ALTER TABLE 查看帮助
ALTER TABLE students ADD gender ENUM('m','f')
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students  ADD INDEX(age);
DESC students;
SHOW INDEXES FROM students;ALTER TABLE students DROP age;

索引

索引是特殊数据结构；定义在查找时作为查找条件的字段
优点：提高查询速度
缺点：占用额外空间，影响插入速度创建索引必须要有索引名称
创建索引：
    CREATE INDEX index_nameON tbl_name(index_col_name,...);
    help CREATE INDEX
删除索引：DROP INDEX index_nameON tbl_name;
查看索引：SHOW INDEXES FROM [db_name.]tbl_name;

DML语句

DML: INSERT, DELETE, UPDATE
    INSERT
        INSERT [INTO] tbl_name[(col_name,...)] {VALUES | VALUE} (val1,...),(...),...
    UPDATE:
        UPDATE tbl_nameSET col1=val1, col2=val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];
    DELETE: 
        DELETE FROM tbl_name[WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 
    可先排序再指定删除的行数
TRUNCATE TABLE tbl_name; 清空表
DQL：SELECT
    SELECT col1,col2,... FROM tbl_name[WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];Limit m,n跳过m个，要n个
字段表示法：
    *: 所有字段 
    as：字段别名，col1 AS alias1
WHERE clause:
    操作符：=, != >, <, >=, <=
    BETWEEN ... AND ...
    LIKE:
        %：任意长度的任意字符
        _：任意单个字符；
    RLIKE：正则表达式模式匹配
    IS NULL ，IS NOT NULL
    IN （val1,val2,...）NOT IN（val1,val2,...）条件逻辑操作：
    AND，OR，NOT

示例

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students 

用户账号

用户账号：'user'@'host'
    user: 用户名
    host: 允许用户通过哪些主机远程连接mysqld服务
        IP、网络地址、主机名、通配符(%和_)
创建用户：
    CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
查看当前用户:
    SELECT user();
查看用户：
    SELECT User,Host,Password FROM user;
删除用户：DROP USER 'username'@'host';
    示例：删除默认的空用户DROP USER ''@'localhost';
更改口令：
    1)SET PASSWORD FOR 'user'@'host' = PASSWORD('password');
    2) UPDATE user SET password=PASSWORD('magedu') WHERE User='root' ;
        注意：上面修改表的命令不会马上生效，需执行FLUSH PRIVILEGES生效
    3) /usr/local/mysql/bin/mysqladmin -u root –poldpassword password  'newpassword'

授权

权限级别：管理权限、数据库、表、字段、存储例程
Help GRANT
GRANT priv_type,... ON [object_type] db_name.tb_nameTO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
db_name.tb_name:
    *.*: 所有库的所表
    db_name.*: 指定库的所有表
    db_name.tb_name: 指定库的指定表
    db_name.routine_name：指定库的存储过程和函数
示例：GRANT SELECT,DELETE on testdb.* TO 'testuser'@'%'IDENTIFIEDBY 'testpass';
查看指定用户获得的授权：
    Help SHOW GRANTS
    SHOW GRANTS FOR 'user'@'host'; 
    SHOW GRANTS FOR CURRENT_USER[()];
回收授权：
    REVOKE priv_type, ... ON db_name.tb_nameFROM 'user'@'host
    示例：REVOKE DELETE ON testdb.* FROM 'testuser'@'%'
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;