mysql基础(第39天)

DBMS
RMDBS：关系数据库管理系统，属于CS架构
数据库设计范式：第1范式，第2范式，第3范式



数据库包括表（table），索引，视图（虚表）
SQL：结构化查询语言，包括DDL（数据定义），DML（数据管理）
通过编程接口可以定义存储过程和存储函数，触发器（当插入或删除数据时，触发写好的语句等），事件调度器（定期完成某些操作），过程式编程（支持选择，循环语句）

   国内互联网去IOE



数据库的数据的三层模型：
物理层：由存储引擎决定
逻辑层：表现为数据库系统本身的表，索引，视图等等
视图层：用户看到的层面（比授权某用户对库，表等的权限（查看，管理等权限））



数据库软件有：
    Oracle：
    sybase：
    infomix：
    DB2：
    Postgresql：
    SQLite：
    MySQL(mariadb)：

MariaDB：
插件式存储引擎，InnoDB，myisam等    

    查看支持的引擎  SHOW ENGINES;
mysql使用单进程多线程结构：线程包括连接线程（连结用户，处理用户请求）和守护线程
配置文件：集中式的配置，能够为mysql的各个不同的应用程序提供配置信息
 [mysqld]
 [mysqld_safe]
 [mysqld_multi]
 [server]
 [mysql]
 [mysqldump]
 [client]
配置文件的参数设置形式如下：
parameter=value
比如skip_name_resolve

mysql配置文件的查找路径：如下：
/etc/my.cnf  -->  /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf -->   --default-extra-file=/path/to/somedir/my.cnf -->  ~/.my.cnf

其中--default-extra-file是启动mysql的时候传递的选项来指定额外的配置文件

配置文件的查找顺序如上所示，越后面文件的配置选项会覆盖前面文件的配置选项

mysql的安装方法：
通过rpm包安装：
展开mysql包即安装：
源代码定制安装：

安装好msyql后需要的设置：
1）为所有的root用户设置密码，有如下3中方法
       进入mysql后使用set命令   SET PASSWORD FOR
       进入mysql后更新mysql数据库中的user表中root用户
         update mysql.user SET password=PASSWORD('password_for_root') WHERE user='root';  flush privileges;
       在终端中使用mysqladmin命令，不需要进入mysql
     
2）删除所有的匿名用户，也就是名称为空的用户
     进入mysql后，先查询 SELECT user,password FROM mysql.user;  user列为空的用户全部删掉，比如  DROP USER ''@'localhost'; 

   1和2可以通过运行mysql_secure_installation 命令搞定

3)  建议关闭主机名反解功能



mysql数据库详解：
mysql数据库存放元数据



mysql的客户端程序有：
mysql            交互式的CLI工具
mysqldump  备份工具 ，基于mysql协议向mysqld发起查询请求，并将查的所有数据保存到文本文件中（转化为insert，creat等写语句）
mysqladmin  基于mysql协议管理mysqld
mysqlimport  数据导入工具

DataGrip：jetbrains公司产品

非客户端的管理工具：
myisamchk
myisampack



查看mysql默认加载的配置信息：

在终端中输入如下命令：

mysql    --print-defaults

mysqld  --print-defaults



客户端mysql的可用选项：
-u  --user=
-h  --host=
-p  --password=
-P  --port=
--protocol={tcp|sock}
-S   --socket=
-D  --database=   切换到指定的数据库
-C  --compress     加此选项表示传输的数据做压缩
-e  'SQL'  不进入mysql直接执行SQL命令，

        比如：在终端中输入mysql  -e  "SHOW DATABASES;" -u root  -p


mysql交互模式可运行的命令有客户端命令和服务器端命令：
客户端命令，\h  help    比如  help create   查看create语句用法

                     source /path/to/somefile.sql
服务器端命令，sql，需要语句结束符分号


mysql的脚本模式：

在终端中输入如下命令：
mysql -uUSERNAME -hHOST -pPASSWORD < /path/to/somefile.sql




服务器端mysqld的工作特性有多种定义方式：
命令行选项指定
配置文件参数
获取可用选项参数列表   在终端中输入命令 mysqld --help --verbose
获取运行中的myqld进程使用的各服务器参数及其值：
SHOW GLOBAL  VARIABLES;    全局性质
SHOW [SESSION] VARIABLES;    当前会话
有些参数支持运行时修改，并且立即生效，有些参数不支持，则只能通过修改配置文件并重启服务生效。


进入mysql后使用SET命令可以修改各服务器参数：
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=vaue;

让设置永久有效的方式：修改配置文件

[mysqld]

sql_mode = 'STRICT_ALL_TABLES'

状态变量：用于保存mysqld运行中的统计数据的变量：
SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

SQL：SQL接口
SQL-86，SQL-89，SQL-99， SQL-03


mysql的数据类型：
字符型：

    char（定长，不区分大小写），

    binary（定长，区分大小写, 二进制字符串），

    varchar（变长，不区分大小写，需要结束符），

    varbinary（变长，需要结束符,二进制字符串），

    text有tinytext, text, mediumtext, longtext，此些存储非二进制数据,不区分大小写

    blog有tinyblog, blog, mediumblog, longblog ，区分大小写,二进制大对象

    set 类型

    enum 枚举类型

字符型的修饰符号：

    NOT NULL

    NULL

    DEFAULT 'STRING'

    CHARACTER SET ''   指明字符集   （show character set;  查看支持的字符集）

    COLLATION   ''  指明排序规则     （show collation;  查看支持的排序规则）

    

数值型：

    整形 ：有INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

            修饰符有 not null, null, default NUMBER,  auto_increment, unsigned, 

    十进制型：DECIMAL  

    浮点 ：有 FLOAT, DOUBLE,近似存储

    位：BIT

    

日期时间：

    DATE             '0000-00-00'

    TIME              '00:00:00'

    DATETIME      '0000-00-00 00:00:00'

    TIMESTAMP   '0000-00-00 00:00:00'   时间戳，1970年1月1日0点0分0秒至今的秒数

    YEAR               0000

内建类型：

    set

    enum

SQL MODE:定义mysqld对约束的响应行为：进入mysql后使用set命令即可

    SET GLOBAL sql_mode="string";

    SET @@global.sql_mode="string";

    需要有修改权限：仅对修改后新创建的会话有效，对已经建立的会话无效

    SET [SESSION] SQL_MODE="string";

    SET @@session.sql_mode="string";

    

具体的sql模型有：traditional，strict_trans_tables,  strict_all_tables 

    SHOW VARIABLES like 'sql_mode';

SQL语句：DDL， DML

    DDL：数据定义语言， CREATE ，ALTER， DROP，该语言可以操作的组件有数据库， 表，索引，视图， 用户，存储过程，存储函数， 触发器，事件调度器等

    如下：

   CREATE DATABASE        

   CREATE EVENT           

   CREATE FUNCTION        

   CREATE FUNCTION UDF    

   CREATE INDEX           

   CREATE LOGFILE GROUP   

   CREATE PROCEDURE       

   CREATE SERVER          

   CREATE TABLE           

   CREATE TABLESPACE      

   CREATE TRIGGER         

   CREATE USER            

   CREATE VIEW            

   SHOW                   

   SHOW CREATE DATABASE   

   SHOW CREATE EVENT      

   SHOW CREATE FUNCTION   

   SHOW CREATE PROCEDURE  

   SHOW CREATE TABLE      

   SHOW CREATE USER       

   SPATIAL         

       

    DML：数据操作语言，INSERT，DELETE, UPDATE, SELECT,（进入mysql后使用help insert 查看帮助）

数据库：

    CREATE, ALTER, DROP

        {DATEBASE|SCHEMA}

        [IF EXISTS]

        [IF NOT EXISTS]

表：二维关系，设计表的时候必须遵循设计规范。定义字段，包括字段名，字段数据类型， 字段修饰符；

约束和索引：应该创建经常用作查询条件的字段上，索引是在存储引擎上实现的，

索引分类：

    稠密索引和稀释索引；

    B+树索引，hash索引，R树索引，FULLTEXT索引

    聚集索引，非聚集索引

    简单索引，组合索引

创建表：（进入mysql输入 help create table; 查看帮助）

(1)直接创建

(2)通过查询现存的表来创建表，新表会被直接插入查询而来的数据

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options]

    [partition_options]

    [IGNORE | REPLACE]

    [AS] query_expression

    

(3)通过复制现存的表的结构来创建表，不会复制其数据

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    { LIKE old_tbl_name | (LIKE old_tbl_name) }

注意：Storage Engine是指表类型，也即在表创建是指明其使用的存储引擎

查看表结构：

    DESCRIBE tbl_name;

查看表状态信息：(进入mysql输入help show table status;  )

   SHOW TABLE STATUS

    [{FROM | IN} db_name]

    [LIKE 'pattern' | WHERE expr][\G]

   

修改表：ALTER TABLE

删除表：DROP TABLE 

MySQL架构：

mysql数据文件类型：

数据文件、索引文件

重做日志，撤销日志，二进制日志，错误日志，查询日志，慢查询日志，（中继日志）

索引管理：

索引：B+树结构（多级）

聚集索引、非聚集索引：数据是否与索引存储在一起

主键索引、辅助索引

稠密索引、稀疏索引

B+树索引，hash索引，R树索引

左前缀索引：

覆盖索引：

管理索引的途径：

在mysql里输入help create index;

创建索引：创建表的时候在字段中指定索引

修改表的时候添加索引：help alter table;  查看帮助

删除索引：help drop index; 查看帮助

查看索引的命令： []表示可选，{} 表示必选  | 表示或者

SHOW {INDEX | INDEXES | KEYS}

    {FROM | IN} tbl_name

    [{FROM | IN} db_name]

    [WHERE expr]

EXPLAIN  接sql语句\G

视图：view

视图是存储下来的select语句（help create view;  查看创建视图的帮助）

CREATE

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

删除视图：

DROP VIEW [IF EXISTS]

    view_name [, view_name] ...

    [RESTRICT | CASCADE]

视图中的数据事实上存储于"基表"中，因此，其修改操作也会针对基表实现；其修改操作受基表限制

DML语句：

INSERT， DELETE， UPDATE， SELECT

INSERT：一次插入一行或者一次多行数据  （help insert;   查看帮助，善于使用help命令 ）

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [(col_name [, col_name] ...)]

    {VALUES | VALUE} (value_list) [, (value_list)] ...

    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET assignment_list

    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [(col_name [, col_name] ...)]

    SELECT ...

    [ON DUPLICATE KEY UPDATE assignment_list]

DELETE：一定要注意限制删除条件

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

UPDATE：更新数据 （help update）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

    SET assignment_list

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

SELECT语句： help select;  

完整的MySQL SELECT语句格式如下：

SELECT                                                         

    [ALL | DISTINCT | DISTINCTROW ]                            

      [HIGH_PRIORITY]                                          

      [STRAIGHT_JOIN]                                          

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]  

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]     是否把select结果缓存下来，必须先将query_cache_type设置为ON才有效（SET QUERY_CACHE_TYPE=ON），且必须select的结果符合缓存条件，否则无法缓存；显示指定SQL_NO_CACHE则不缓存；query_cache_type设置为DEMAND时，显示指定SQL_CACHE的select语句才会缓存。

    select_expr [, select_expr ...]                            

    [FROM table_references                                     

      [PARTITION partition_list]                               

    [WHERE where_condition]            WHERE 是过滤条件                      

    [GROUP BY {col_name | expr | position}         GROUP BY是分组            

      [ASC | DESC], ... [WITH ROLLUP]]                         

    [HAVING where_condition]           HAVING是用于过滤符合条件的组的                    

    [ORDER BY {col_name | expr | position}           ORDER BY是排序           

      [ASC | DESC], ...]                                       

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]    LIMIT 限制数量

    [PROCEDURE procedure_name(argument_list)]        PROCEDURE是存储过程

    [INTO OUTFILE 'file_name'                        INTO OUTFILE    写入到外部文件                

        [CHARACTER SET charset_name]                           

        export_options                                         

      | INTO DUMPFILE 'file_name'                              

      | INTO var_name [, var_name]]                            

    [FOR UPDATE | LOCK IN SHARE MODE]]        

SHOW GLOBAL STATUS LIKE 'Qcache%';    查看数据库状态

字段显示可以使用别名col1 AS alias1,  col2 AS alias2,  ....

WHERE expression: 比较表达式，内置函数；

    特殊的比较操作符：

    BETWEEN... AND ...: 

    IN：指定范围内值的存在性测试

    IN(list): 指定的字段取的取值在此list中，则表示符合条件；

    IS NULL

    IS NOT NULL

    LIKE：可使用通配符，%（匹配任意长度任意字符）, _（匹配单个字符）

    RLIKE或REGEXP：可使用正则表达式的模式

    逻辑操作符：

    AND

    OR

    NOT

GROUP BY：做聚合计算时使用

    表示根据指定的字段，对符合条件的行做分组，而后对每组分别做聚合计算；

    聚合计算：AVG(), SUM(), MAX(), MIN(), COUNT()

HAVING: 对聚合计算的结果做过滤；

ORDER BY col1[,col2] {ASC|DESC}

LIMIT [Offset,]count

                 

连接查询：事先将两张或多张表执行相应的join操作，而后根据join结果做查询；

    CROSS JOIN：笛卡尔积,不常用

     INNER JOIN：按照两张表的字段等值连接；

    OUTER JOIN：按照两张表的字段等值连接

    LEFT OUTER JOIN：左外，左表为准  FROM tb1 LEFT OUTER JOIN tb2 ON tb1.col == tb2.col   

    RIGHT OUTER JOIN：右外，右表为准 FROM tb1 RIGHT OUTER JOIN tb2 ON tb1.col == tb2.col   

    NATURAL JOIN：自然连接

select * from students, teachers where teachers.tid == students.teacherid;  

子查询：查询中嵌套着查询

基于某查询语句的结果再次进行的查询

用于WHERE子句的子查询

1、用于比较表达式中的子查询

要求子查询只能返回单个结果；

SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);

2、用于IN中的子查询

判断是否存在于指定的列表中

3、用于EXISTS中子查询

用于FROM中的子查询：

SELECT alias.col,... FROM (SELECT statement) AS alias WHERE clause

例如：SELECT s.Name FROM (SELECT * FROM students WHERE Age > 20) AS s WHERE s.Name LIKE 's%';

联合查询：

SELECT statement UNION SELECT statement

将两外或多个返回值字段相同的查询的结果合并输出