Mysql/MariaDB基础

1. Mysql/MariaDB的使用模式：

• 交互模式：

  可运行命令有两类：

  • 客户端命令：
    h，help
  • 服务器端命令：
    SQL，需要语句结束符（冒号；）

• 非交互模式：

  • mysql -e “SQL语句”

  • 导入sql脚本，mysql -uUSERNAME -hHOST -pPASSWORD < /PATH/TO/FILE.sql

    也可在交互模式下导入脚本：mysql> source /PATH/TO/FILE.sql

• 客户端程序：

  • mysql：交互式的CLI工具；
  • mysqldump：逻辑备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中，速度慢；
  • mysqladmin：基于mysql协议管理mysqld；

• 客户端命令：

      常用选项：
    --host=host_name, -h host_name：服务端地址；
    --user=user_name, -u user_name：用户名；
    --password[=password], -p[password]：用户密码；
    --port=port_num, -P port_num：服务端端口；
    --protocol={TCP|SOCKET|PIPE|MEMORY}：
    	本地通信：基于本地回环地址进行请求，将基于本地通信协议；
    		Linux：SOCKET
    		Windows：PIPE，MEMORY
    	非本地通信：使用非本地回环地址进行的请求； TCP协议；
    --socket=path, -S path
    --database=db_name, -D db_name：
    --compress, -C：数据压缩传输
    --execute=statement, -e statement：非交互模式执行SQL语句；
    --vertical, -E：查询结果纵向显示；
  

  客户端命令：
  ? (?) Synonym for `help'.
  help (h) Display this help.
  clear (c) Clear the current input statement.
  connect ( ) Reconnect to the server. Optional arguments are db and host.
  delimiter (d) Set statement delimiter.
  edit (e) Edit command with $EDITOR.
  ego (G) Send command to mysql server, display result vertically.
  exit (q) Exit mysql. Same as quit.
  go (g) Send command to mysql server.
  nopager ( ) Disable pager, print to stdout.
  notee ( ) Don't write into outfile.
  pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
  print (p) Print current command.
  prompt (R) Change your mysql prompt.
  quit (q) Quit mysql.
  rehash (#) Rebuild completion hash.
  source (.) Execute an SQL script file. Takes a file name as an argument.
  status (s) Get status information from the server.
  system (!) Execute a system shell command.
  tee (T) Set outfile [to_outfile]. Append everything into given outfile.
  use (u) Use another database. Takes database name as argument.
  charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
  warnings (W) Show warnings after every statement.
  nowarning (w) Don't show warnings after every statement.

• 参数设定

  服务器端（msyqld）工作特性有多种定义方式：

  • 启动mysql服务时的附带选项

  • 配置文件中设定的参数

    配置文件启动查找路径：/etc/my.cnf 、 /etc/mysql/my.cnf 、 $MYSQL_HOME/my.cnf、 ~/.my.cnf

    同一项设置多个文件中都存在的话，以最后查找的配置文件为准。

  • 运行中的mysql进程使用命令修改参数及其值；

    • 全局参数：

      1. mysql> SET GLOBAL system_var_name=value；

      2. mysql> SET @@global.system_var_name=value；

    • 会话参数：

      1. mysql> SET [SESSION] system_var_name=value;

      2. mysql> SET @@[session.]system_var_name=value;

      注意：两种修改方式均可；会话参数（顾名思义，仅本次会话生效）和部分全局参数支持运行时修改，会立即生效；有些全局参数不支持，且只能通过修改配置文件，并重启服务器程序生效。

2. MariaDB初始化操作

安装后的设定：

1. 建议关闭主机名反解功能；

   skip_name_resolve = ON

2. 开启InnoDB的每数据库文件单独目录存放；

   innodb_file_per_table = ON

   注：MariaDB10.3已经默认开启

3. 设定服务端默认字符集

   character_set_server=utf8

4. 设定默认存储引擎；

   default-storage-engine=InnoDB

   存储引擎是表级概念，即同数据库在创建表时可用type=VALUE的形式为不同表指定不同存储引擎，但不推荐这样做。

以上项目写入配置文件的mysqld项

1. 为所有root用户设定密码；

   mysql>SET PASSWORD FOR

   mysql> update mysql.user SET password=PASSWORD(‘your_pass’) WHERE clause；

2. 删除所有匿名用户

   Mysql>DROP USER ‘’@’localhost’；

上述两步骤建议通过运行安全初始化完成：mysql_secure_installation

3.SQL语言

3.1DDL:数据定义语言

DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用如，CREATE,ALTER,DROP等命令

相关的常用操作对象：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度器等。

3.2 DML：数据操作语言

就象它的名字一样，是用来对数据库里的数据进行操作的语言。

• SELECT - retrieve data from the a database 查询
  • 有的分类方式会将Select语句单独作为DQL语言分类
• INSERT - insert data into a table 添加
• UPDATE - updates existing data within a table 更新
• DELETE - deletes all records from a table, the space for the records remain 删除
• CALL - 调用存储过程
• LOCK TABLE - control concurrency 锁，用于控制并发
• EXPLAIN PLAN - explain access path to data
  RDBMS（关系型数据管理系统）执行每一条SQL语句，都必须经过优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

语句格式

数据库：
	CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name  COLLATE [=] collation_name

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

	DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

表：
CREATE

(1) 常规方式创建表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]

CREATE TABLE [IF NOT EXISTS] tble_name (col_name data_type|INDEX|CONSTRAINT);

	table_options：
  		ENGINE [=] engine_name
	查看支持的所有存储引擎：
 		mysql> SHOW ENGINES;
	查看指定表的存储引擎：
		mysql> SHOW TABLE STATUS LIKE clause;

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

(2) 直接创建表，并将查询语句的结果插入到新创建的表中；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options][partition_options] select_statement

(3) 复制某存在的表的结构来创建新的空表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

DROP:
	DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name];

ALTER：
	ALTER  TABLE tbl_name [alter_specification [, alter_specification] ...]
	可修改内容：
		(1) table_options
		(2) 添加定义：
			ADD	字段、字段集合、索引、约束
		(3) 修改字段：
			CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
			MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
		(4) 删除操作：
			DROP 字段、索引、约束

表重命名：
	RENAME [TO|AS] new_tbl_name

查看表结构定义：
	DESC tbl_name；

查看表定义：
	SHOW CREATE TABLE tbl_name

查看表属性信息：
	SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

3.3 DCL：数据控制语言

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

• GRANT：授权

• REVOKE：收回授权

• ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点
  回滚---ROLLBACK
  回滚命令使数据库状态回到上次最后提交的状态。

• COMMIT [WORK]：提交。

  在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

  • 显式提交
    用COMMIT命令直接完成的提交为显式提交。其格式为：

  • 隐式提交
    用SQL命令间接完成的提交为隐式提交。这些命令是：
    ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
    EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

  • 自动提交
    若把autocommit参数设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

    SET @@[GLOBAL|SESSION].autocommit=ON；

4.Mysql数据类型

4.1字符型

          VARCHAR, VARBINARY：变长数据类型；需要结束符；

          TEXT：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

          BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

          ENUM, SET

字符型修饰符：

          NOT NULL：非空约束；

          NULL：

          DEFAULT ‘STRING’：指明默认值；

          CHARACTER SET ‘’：使用的字符集；

          COLLATION：使用的排序规则；

4.2 数值型

精确数值型：

整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT,DECIMAL

整形修饰符：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED

近似数值型:

浮点型：FLOAT,DOUBLE,BIT,REAL

4.3 日期时间型

日期：DATE

时间：TIME

日期&时间：DATETIME

时间戳：TIMESTAMP

年份：YEAR(2), YEAR(4)

日期时间型修饰符：NOT NULL， NULL， DEFAULT

4.4 内建数据类型

ENUM：枚举

如：ENUM('Sun','Mon','Tue','Wed')

SET：集合

4.5 SQL MODE

定义mysqld对约束等违反时的响应行为等设定。

常用的MODE：

TRADITIONAL

STRICT_TRANS_TABLES

STRICT_ALL_TABLES

MODE的具体介绍请移步http://www.cnblogs.com/ainiaa/archive/2010/12/31/1923002.html

修改方式：mysql> SET GLOBAL sql_mode='MODE';

5. 查询语句详解

查询执行路径：

1. 命中缓存：

   请求-->查询缓存

   缓存以K-V形式存储查询的执行结果；key：查询语句的hash值；value：查询语句的执行结果；

2. 未命中缓存：

3. 请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应

SELECT语句的执行流程：

FROM  --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit

5.1 单表查询

单表查询：

            SELECT

                [ALL | DISTINCT | DISTINCTROW ]

                [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

                select_expr [, select_expr ...]

                [FROM table_references

                [WHERE where_condition]

                [GROUP BY {col_name | expr | position}

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

                [HAVING where_condition]

                [ORDER BY {col_name | expr | position}

                [ASC | DESC], ...]

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

            用法：

                SELECT col1, col2, ... FROM tble_name；   **无限定条件的查询极其危险，慎用**；

                SELECT col1, col2, ... FROM tble_name WHERE clause；

                SELECT col1, col2, ... FROM tble_name  [WHERE clause] GROUP BY col_name [HAVING clause]； 

            DISTINCT：数据去重；

            SQL_CACHE：显式指定缓存查询语句的结果；

            SQL_NO_CACHE：显式指定不缓存查询语句的结果；

            query_cache_type服务器变量有三个值：

                ON：启用；

                    SQL_NO_CACHE：不缓存；默认符合缓存条件都缓存；

                OFF：关闭；

                DEMAND：按需缓存；

                    SQL_CACHE：缓存；默认不缓存；​    

            字段可以使用别名 ：

                col1 AS alias1, col2 AS alias2, ...

            WHERE子句：指明过滤条件以实现“选择”功能；

                过滤条件：布尔型表达式；

                [WHERE where_condition]

                    算术操作符：+, -, *, /, %

                    比较操作符：=, <>, !=, <=>, >, >=, <, <=

                        IS NULL， IS NOT NULL

                        区间：BETWEEN min AND max

                        IN：列表；

                        LIKE：模糊比较，%和_；

                        RLIKE或REGEXP​     

                    逻辑操作符：

                        AND， OR， NOT​      

            GROUP BY：根据指定的字段把查询的结果进行“分组”以用于“聚合”运算；

                avg(), max(), min(), sum(), count()

                HAVING：对分组聚合后的结果进行条件过滤；

            ORDER BY：根据指定的字段把查询的结果进行排序；

                升序：ASC

                降序：DESC

            LIMIT：对输出结果进行数量限制

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

5.2 多表查询：

连接操作：

交叉连接：笛卡尔乘积；

    内连接：

    等值连接：让表之间的字段以等值的方式建立连接；

    不等值连接：

自连接

例子：

内连接

select st.name,sc.score from students as st,scores as sc where st.stuid=sc.stuid;
select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid;

外连接
左：select st.name,sc.score from students as st left outer join scores as sc on st.stuid=sc.stuid;
右：select st.name,sc.score from scores as sc right outer join students as st on st.stuid=sc.stuid;

自连接
select e2.name,e1.name as 上司的名字 from emp as e1 inner join emp as e2 on e1.id=e2.leaderid;

    crossjoin 交叉连接

select c.course,t.name," " as 评分 from teachers as t cross join courses as c;

5.3 子查询：在查询中嵌套查询；

            用于WHERE子句中的子查询；

                (1) 用于比较表达式中的子查询：子查询仅能返回单个值；

                (2) 用于IN中的子查询：子查询可以返回一个列表值；

                (3) 用于EXISTS中的子查询：

            用于FROM子句中的子查询；

                SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;​     

例子：
select * from students where age < ( select age from students where stuid=9 ) ;

5.4 联合查询：将多个查询语句的执行结果相合并；

SELECT clause UNION SELECT cluase；

如：select t.tid,t.name from teachers as t union select s.stuid,s.name from students as s;

5.5 select练习

导入hellodb.sql，完成以下题目：

1. 显示前5位同学的姓名、课程及成绩；

   MariaDB [hellodb]> SELECT s.Name,c.Course,sc.Score FROM (select * from students limit 5) AS s  LEFT JOIN scores AS sc ON sc.StuID = s.StuID LEFT JOIN courses AS c ON sc.CourseID =c.CourseID;
   +-------------+----------------+-------+
   | Name        | Course         | Score |
   +-------------+----------------+-------+
   | Shi Zhongyu | Kuihua Baodian |    77 |
   | Shi Zhongyu | Weituo Zhang   |    93 |
   | Shi Potian  | Kuihua Baodian |    47 |
   | Shi Potian  | Daiyu Zanghua  |    97 |
   | Xie Yanke   | Kuihua Baodian |    88 |
   | Xie Yanke   | Weituo Zhang   |    75 |
   | Ding Dian   | Daiyu Zanghua  |    71 |
   | Ding Dian   | Kuihua Baodian |    89 |
   | Yu Yutong   | Hamo Gong      |    39 |
   | Yu Yutong   | Dagou Bangfa   |    63 |
   +-------------+----------------+-------+
   10 rows in set (0.000 sec)
   

2. 显示其成绩高于80的同学的姓名及课程与成绩；

   MariaDB [hellodb]> SELECT s.Name,c.Course,sc.Score FROM students  AS s  LEFT JOIN scores AS sc ON sc.StuID = s.StuID LEFT JOIN courses AS c ON sc.CourseID =c.CourseID WHERE sc.Score >80; 
   +-------------+----------------+-------+
   | Name        | Course         | Score |
   +-------------+----------------+-------+
   | Shi Zhongyu | Weituo Zhang   |    93 |
   | Shi Potian  | Daiyu Zanghua  |    97 |
   | Xie Yanke   | Kuihua Baodian |    88 |
   | Ding Dian   | Kuihua Baodian |    89 |
   | Shi Qing    | Hamo Gong      |    96 |
   | Xi Ren      | Hamo Gong      |    86 |
   | Xi Ren      | Dagou Bangfa   |    83 |
   | Lin Daiyu   | Jinshe Jianfa  |    93 |
   +-------------+----------------+-------+
   8 rows in set (0.101 sec)
   

3. 求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

   MariaDB [hellodb]> SELECT s.Name,AVG(Score) FROM  (select Name,StuID from students limit 8)   AS s  LEFT JOIN scores AS sc ON s.StuID = sc.StuID  GROUP BY Name ORDER BY AVG(Score) DESC;
   +-------------+------------+
   | Name        | AVG(Score) |
   +-------------+------------+
   | Shi Qing    |    96.0000 |
   | Shi Zhongyu |    85.0000 |
   | Xi Ren      |    84.5000 |
   | Xie Yanke   |    81.5000 |
   | Ding Dian   |    80.0000 |
   | Lin Daiyu   |    75.0000 |
   | Shi Potian  |    72.0000 |
   | Yu Yutong   |    51.0000 |
   +-------------+------------+
   8 rows in set (0.000 sec)
   

4. 显示每门课程课程名称及学习了这门课的同学的个数,并降序排列；

   MariaDB [hellodb]> SELECT c.Course AS 课程名称,count(s.StuID) AS 学生数量  FROM  courses  AS c  LEFT JOIN scores AS s ON c.CourseID = s.CourseID  GROUP BY s.CourseID ORDER BY count(s.StuID) DESC;
   +----------------+--------------+
   | 课程名称       | 学生数量     |
   +----------------+--------------+
   | Kuihua Baodian |            4 |
   | Hamo Gong      |            3 |
   | Daiyu Zanghua  |            2 |
   | Dagou Bangfa   |            2 |
   | Weituo Zhang   |            2 |
   | Taiji Quan     |            1 |
   | Jinshe Jianfa  |            1 |
   +----------------+--------------+
   7 rows in set (0.000 sec)
   

5. 如何显示其年龄大于平均年龄的同学的名字和年龄？

   MariaDB [hellodb]> select name,age from students where age >(select avg(age) from students) order by age desc; 
   +--------------+-----+
   | name         | age |
   +--------------+-----+
   | Sun Dasheng  | 100 |
   | Xie Yanke    |  53 |
   | Shi Qing     |  46 |
   | Tian Boguang |  33 |
   | Ding Dian    |  32 |
   +--------------+-----+
   5 rows in set (0.001 sec)
   

6. 如何显示其学习的课程为第1、2，4或第7门课的同学的名字及课程名？

   MariaDB [hellodb]> select s.Name, s.CourseID, courses.Course from (select students.Name,scores.CourseID from students left join scores on students.StuID = scores.StuID where scores.CourseID in (1,2,4,7)) as s left join courses on s.CourseID=courses.CourseID;
   +-------------+----------+----------------+
   | Name        | CourseID | Course         |
   +-------------+----------+----------------+
   | Shi Zhongyu |        2 | Kuihua Baodian |
   | Shi Potian  |        2 | Kuihua Baodian |
   | Xie Yanke   |        2 | Kuihua Baodian |
   | Ding Dian   |        2 | Kuihua Baodian |
   | Yu Yutong   |        1 | Hamo Gong      |
   | Yu Yutong   |        7 | Dagou Bangfa   |
   | Shi Qing    |        1 | Hamo Gong      |
   | Xi Ren      |        1 | Hamo Gong      |
   | Xi Ren      |        7 | Dagou Bangfa   |
   | Lin Daiyu   |        4 | Taiji Quan     |
   +-------------+----------+----------------+
   10 rows in set (0.104 sec)
   

7. 如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

   MariaDB [hellodb]> SELECT students.name,students.age,s.classid,s.pj FROM students,(SELECT classid,COUNT(stuid)  AS cs,AVG(age) AS pj FROM students GROUP BY classid HAVING cs >=3) AS s WHERE  students.age>s.pj AND students.classid =s.classid;
   +---------------+-----+---------+---------+
   | name          | age | classid | pj      |
   +---------------+-----+---------+---------+
   | Shi Potian    |  22 |       1 | 20.5000 |
   | Xie Yanke     |  53 |       2 | 36.0000 |
   | Ding Dian     |  32 |       4 | 24.7500 |
   | Yu Yutong     |  26 |       3 | 20.2500 |
   | Yuan Chengzhi |  23 |       6 | 20.7500 |
   | Xu Zhu        |  21 |       1 | 20.5000 |
   | Lin Chong     |  25 |       4 | 24.7500 |
   | Hua Rong      |  23 |       7 | 19.6667 |
   | Huang Yueying |  22 |       6 | 20.7500 |
   +---------------+-----+---------+---------+
   9 rows in set (0.017 sec)
   

8. 统计各班级中年龄大于全校同学平均年龄的同学。

select name,age,classid from students where age > (select avg(age) as a from students);
此种写法简单但不能过滤students表中classid = NULL的条目
MariaDB [hellodb]> SELECT s.Name,s.Age FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID WHERE s.ClassID=c.ClassID AND Age > (SELECT AVG(Age) FROM students);
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
+--------------+-----+
4 rows in set (0.000 sec)