mysql基础（1）

最新推荐文章于 2022-07-18 23:02:16 发布

Java～develop

最新推荐文章于 2022-07-18 23:02:16 发布

阅读量183

点赞数

分类专栏： linux mysql 文章标签： mysql基础（1）

本文链接：https://blog.csdn.net/matengbing/article/details/80292531

版权

linux 同时被 2 个专栏收录

43 篇文章 1 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

mariadb or mysql：

DBMS

RDBMS：关系型数据库管理系统

C/S：通过专有协议

关系模型：表（行、列），二维关系

范式：第一范式、第二范式、第三范式

关系运算：

选择

投影

数据库：表，索引，视图（虚表）

SQL：structurequery language

DDL，DML

编程接口：

存储过程

存储函数

触发器

事件调度器

过程式编程：选择，循环

三层模型：

物理层

逻辑层

视图层

解决方案：

oracle，sybase，infomix，DB2

mysql，mariadb，postgreSQL

mariaDB

插件式多线程

连接线程

守护线程

配置文件：集中式的配置，能够为msql的个应用程序提供配置信息

[mysqld]
[mysqld_safe]
[mysqld_multi]	
[server]
[mysql]
[mysqldump]
	parameter=value
	skip-name-resolve
	skip_name=resolve

查找路径/etc/my.cnf-->/etc/mysql/my.cnf-->$MYSQL_HOME/my.cnf-->--default-extra-file=/path/to/somedir/my.cnf-->~/.my.cnf

安装方法：

osvendor：rpm

MYSQL：

rpm

展开即用

源码

centos7安装mariadb：

yum -y install mariadb-server mariadb  	
mysql_secure_installation

安装后的设定：

（1）为所有root用户设定密码

mysql>SET PASSWORD
mysql>update mysql.user SET password=PASSWORD(‘your password’) WHERE clause;

（2）删除所有匿名用户

mysql>DROP USER ‘’@’localhost’;

上述两步骤可运行命令：mysql_sercure_installation替换

授权主机：

GRANT ALL PRIVILEGES ON *.* to ‘root’@'%' IDENTIFIED BY '密码';

*.*：所有数据库所有表

‘root@%’：所有主机可通过root用户登录

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

元数据数据库：mysql

user，host等

mysql-->mysqld

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

如何获取程序默认使用的配置：

mysql --print-defaults
mysqld --print-defaults

客户端类应用程序的可用选项：

-u，--user=
-h，--host=
-p，--password=
-P，--port=
--protocol=
-S，--socket=
-D，--database=
-C，--compress
mysql –e “SQL”

mysql的使用模式：

交互式模式：

可运行命令有两类：

\h,help

服务端命令：

SQL，需要语句结束符

脚本模式：

#mysql –uUSERNAME –pPASSWORD < /path/from/somefile.sql
#mysql> soure /path/from/somefile.sql

服务端（mysqld）：工作特性有多重定义方式

命令行选项

配置文件参数

获取可用参数列表

mysqld –help –verbose

获取运行中的mysql进程使用各服务器参数及其值

mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;

注意：其中有些参数支持运行时修改，会立即生效，有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效。

修改服务器变量的值：

mysql> help SET
	全局：
            mysql> SET GLOBAL system_var_name=value;
	    mysql> SET @@global.system_var_name=value;
	会话：
            mysql>SET [SESSION] system_var_name=value;
            mysql>SET @@[session.] system_var_name=value;
	状态变量：用于保存mysqld运行中的统计数据的变量
	    mysql>SHOW GLOBAL STATUS;
            mysql>SHOW [SESSION] STATUS;

MySQL数据类型：

字符型
	数值型
	日期时间型
	内建类型

	字符型：
		CHAR,BINARY：定长数据类型
		VARCHAR,VARBINARY：变长数据类型，需要结束符
		TEXT:TINTEXT,TEXT,MEDIUMTEXT,LONGTEXT
		BLOB：TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
		ENUM，SET
	数值型：
		精确数值型：
			整型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
			十进制型：DECIMAL
			近似数值型：
			浮点型：
				FLOAT
				DOUBLE
				BIT
	日期时间型：
		DATE
		TIME
		DATETIME
		TIMESTAMP
		YEAR(2),YEAR(4)
	字符类型修饰符：
		NOT NULL:非空约束
		NULL：
		DEFAULT ‘STRING’：指明默认值
		CHARACTER SET ‘’：使用的字符集
		COLLATION：使用的排序规则
				
		mysql>SHOW CHARACTER SET;
		mysql>SHOW COLLECTION;
	整型数据修饰
		NOT NULL 
		NULL
		DEFAULT NUMBER
				
		AUTO_INCREAMENT:
			UNSIGNED
			PRIMARY KEY|UNIQUE KEY
			NOT NULL

			mysql>SELECT LAST_INSERT_ID();
	日期时间类型修饰符;
		NOT NULL
		NULL
		DEFAULT

SQL MODE：定义mysqld对约束等的响应行为：

修改方式：
	mysql>SET GLOBAL sql_mode=’MODE’;
	mysql>SET @@global.sql_mode=’MODE’;
	需要修改权限：进队修改后新创建的会话有效，对已经建立的会话无效
	mysql>SET SESSION sql_mode=’MODE’;
	mysql>SET @@session.sql_mode=’MODE;
常用MODE：TRADTTIONAL,STRICT_TRANS_TABLES,OR STRICT_ALL_TABLES;

SQL:DDL,DML

DDL:数据定义语言

CREATE,ALTER,DROP

DB组件：数据库、表、索引、视图、用户、存储过程、触发器、事件调度器等

CREATE的常用命令：

CREATE DATABSE
	CREATE EVENT
	CREATE FUNCTION
	CREATE FUNCTION UDF
	CREATE INDEX
	CREATE PROCEDURE
	CREATE SERVER
	CREATE TABLE
	CREATE TABLESPACE
	CREATE TRIGGER
	CREATE USER
	CREATE VIEW

DML:数据操作语言

INSERT,DELETE,UPDATE,SELECT

数据库：

CREATE,ALTER,DROP
	{DATABASE|SCHEMA}
	[IF EXISTS]
	[IF NOT EXISTS]

表：二维关系

设计表：遵循规范

定义：字段，索引

字段L字段名，字段数据类型，修饰符

约束，索引：应该创建在经常用作查询条件的字段上

索引：实现级别在存储引擎

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

聚焦索引，非聚焦索引

创建表：CREATE,TABLE

获取帮助：

在mysql交互环境中

MariaDB [(none)]>help create table

（1）直接创建

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

（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) }

注意：storageengine实质表类型，也即在表创建时指明其使用的存储引擎

同一个库中标要使用同一种存储引擎类型

查看表结构

describe tablename;

查看表状态信息

show tables;
	SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

修改表：alter table

删除表：drop table

MYSQL基础

单进程多线程

用户连接：连接线程

MYSQL数据文件类型

数据文件、索引文件

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

DDL & DML：

按特定数据结构存储的数据

索引类型：

聚焦索引、费聚焦索引：数据是否与索引存储在一起

主键索引、辅助索引

稠密索、稀疏索引：是否索引了每一个数据项

B+ TREE、HASH、R TREE

简单索引、组合索引

左前缀索引

覆盖索引

管理索引的途径

创建索引：创建表时指定：create index

创建或删除索引：修改表的命令

删除索引：drop index

查看表上的索引：

show  {index | indexes | keys}
      {from | in } tab_name
      [from | in] db_name]
      [where expr]
      explain

视图：VIEW

虚表：

创建方法：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    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：一次插入一行或多行

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    	[INTO] tbl_name [(col_name,...)]
    	{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    	[ ON DUPLICATE KEY UPDATE
     	 	col_name=expr
       	 	[, col_name=expr] ... ]

		Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

update：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段

where

limit

delete：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意：一定要有限制条件，否则将清空表中的所有数据

where

limit

查询：

查询执行路径中的组件：查询花村、解析器、预处理器、优化器、查询执行引擎、存储引擎

select语句的执行流程

from clause-->where clause-->group by -->having clause-->order by-->select-->limit

单表查询：

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_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}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

distinct：数据去重

SQL_CACHE：显式指定存储查询结果与缓存之中
SQL_NO_CACHE：显式指定查询结果不予缓存
show global variables like ‘query%’;
query_cache_type的值为’ON’时，查询缓存功能打开
	select的结果符合缓存条件即会缓存，否则，不予缓存
	显式指定SQL_NO_cache，不予缓存
quer_cache_type的值为’DEMAND’时，查询缓存功能按需进行
	显式指定SQL_CACHE的select语句才会缓存，其他均不会缓存

字段显示可以使用别名：

col1 as name1，col1 name1

where字句：指明过滤条件实现“选择”功能

过滤条件：布尔型表达式
算术操作符：+,-,*,/,%
比较操作符：=,!=,>,<,<>,>=,<=,<=>
begin min_num and max_num
in (element1,element2,…)
is null	
is not null 
like：
    %：任意长度的任意字符
    _：任意单个字符
rlike：
regepx：匹配字符串可用正则表达式书写模式
逻辑操作符：
	not
	and
	or
	xor
group：根据指定的条件吧查询结果进行“分组”以用于做“聚合”
	avg(),max(),min(),count(),sum()
order by：根据指定字段对查询结果进行排序
	升序：ASC，默认
	降序：AESC
limit[[offset,]row_count]：对查询的结果进行输出行行数限制
对查询结果中的数据请求施加“锁”。
	for update：写锁
	lock in share mode：读锁，共享锁

多表查询：

交叉连接：笛卡尔积

内连接：

等值连接：让表之间的字段以“等值”建立连接关系

不等值连接

自然连接

外连接：

左外连接

from tb1 left join tb2 on tb1.col=tb2.col

右外连接

from tb1 right join tb2 on tb1.col=tb2.col

子查询：在查询语句嵌套者查询语句

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

用在where字句中的子查询：

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

select name,age from students where age>(selsct avg(age) from students);

（2）用于in中的子查询：

select name ,age from students where age in (select age from teachers);

（3）用于exists

用于from子句中的子查询：

使用格式：select tb_alias.col,… from (select clause) as tb_alias where clause;
	示例：select s.aage,s,classid from select avg(age) as aage,classid from 
             students shere classid is not null group by classid) as s shere s.aage>30;

联合查询：union,合并结果

select name,age from students 
union
select name,age from teachers;

执行语句时前面加上explain,后面加上\G会显示语句的详细信息。

如explain select* from wp_users\G

注意后面没有分号。

MariaDB [worldpress]> explain select * from wp_users\G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: wp_users
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set (0.00 sec)

MariaDB [worldpress]>