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 (\r) 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 (\n) Disable pager, print to stdout.
notee (\t) 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初始化操作

安装后的设定：

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

skip_name_resolve = ON
开启InnoDB的每数据库文件单独目录存放；

innodb_file_per_table = ON

注：MariaDB10.3已经默认开启
设定服务端默认字符集

character_set_server=utf8
设定默认存储引擎；

default-storage-engine=InnoDB

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

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

为所有root用户设定密码；

mysql>SET PASSWORD FOR

mysql> update mysql.user SET password=PASSWORD(‘your_pass’) WHERE clause；
删除所有匿名用户

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. 查询语句详解

查询执行路径：

命中缓存：

请求-->查询缓存

缓存以K-V形式存储查询的执行结果；key：查询语句的hash值；value：查询语句的执行结果；
未命中缓存：
请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应

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，完成以下题目：

显示前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)

显示其成绩高于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)

求前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)

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

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)

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

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)

如何显示其学习的课程为第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)

如何显示其成员数最少为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)

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

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)

posted @ 2018-09-04 21:15 望月胡杨阅读(199) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部