linux mysql .a_Linux----------mysql基础

一、数据库介绍

数据库是数据的汇集，它以一定的组织形式存于存储介质上

DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心

DBA：负责数据库的规划、设计、协调、维护和管理等工作

应用程序指以数据库为基础的应用程序

1.1 数据库的优点

相互关联的数据的集合

较少的数据冗余

程序与数据相互独立

保证数据的安全、可靠

最大限度地保证数据的正确性

数据可以并发使用并能同时保证一致性

1.2 数据库的基本功能

数据定义

数据处理

数据安全

数据备份

1.3数据库的类型

关系型数据：mysql，mariadb,oracle为代表

非关系型数据库nosql :redis,memached,mongodb

1.4 关系型数据的组成

关系：关系是二维表，并满足如下性质：表中的行、列次序并不重要

行row：表中的的每一行，又称为一条记录

列coloumn：表中的每一列，称为属性，字段

主键(Primary key):用于唯一确定一个记录的字段

域domain：属性的取值范围，如性别只能是男和女

事务transaction：多个操作被当作一个整体对待

ACID：

A:原子性

B:一致性

C:隔离性

D:持久性

1.5 关系型数据库的常用组件

关系型数据库的常用组件有：

数据库： database

表：table，由行(row)和列(column)组成

索引：index

视图：view

用户：user

权限：privilege

存储过程：procedure

存储函数：function

触发器：trigger

事件调度器：event scheduler

1.6 SQL语句

SQ语句有三种类型：

DDL：Data Defination Langusage---数据定义语言

DML：Data Manipulation Language---数据操纵语言

DCL：Data Control Language---数据控制语言

SQL语句类型对应操作

DDL CREATE：创建

DROP：删除

ALTER：修改

DMLINSERT：向表中插入数据

DELETE：删除表中数据

UPDATE：更新表中数据

SELECT：查询表中数据

DCL GRANT：授权

REVOKE：移除授权

1.7 mysql命令使用

//语法：mysql [options] [databases]

//常用的option：

-u username //指定用户名，默认为root

-p password //指定用户的密码

-h host //指定服务器主机，默认为localhost，推荐使用IP地址

-V //查看当前使用的mysql版本

-P# //指定数据库监听的端口，这里#指的是实际的端口 ，默认为3306

-e //不登录mysql执行sql语句后退出，常用于脚本

1.8服务器监听的两种socket

ip socket : 默认监听在tcp的3306端口，支持远程通信

Unix socket： 监听在sock文件上(tmp/mysql.sock,/var/lib/mysql/mysql.mysql.socket),仅支持本地通信，server地址只能是：localhost，127.0.0.1

二、mysql安装和配置

mysql安装方式有三种：

源码：编译安装

二进制：二进制格式的程序包(已编译好)展开特定路径，并经过简单配置后即可使用

yum:yum install直接安装最简单，但是使用的模块不能按需配置，日志和配置文件统一管理不方便

这里主要讲下mysql的yum安装和源码编译安装

2.1 mysql-yum安装

//配置好自己的yum源，以及网络源

wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql57-community-release-el7-10.noarch.rpm

yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel

//启动mysql并初始化(必须修改初始密码)

systemctl start mysqld

//确保默认的3306端口已经监听起来

ss -ntlp

//日志文件中找出临时密码

grep "password" /var/log/mysqld.log

//登录mysql

mysql -uroot -p"临时密码"

//修改mysql登录密码

set global validate_password_policy=0;

set global validate_password_length=1;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'wang123';

//修改完退出测试能否进去

//为避免mysql自动升级，这里需要卸载最开始安装的yum源

mysql -v (记住mysql版本)

yum -y remove mysql57-community-release-el7-10.noarch

2.2 msyql编译安装

//配置好网络yum源，安装依赖软件包和开发包组,编译工具

yum gropuinstall "Development tools"

yum install yum install libaio-devel bison bison-devel zlib-devel

openssl-devel ncurses-devel libcurl-devel libarchive-devel

boost-devel gcc gcc-c++ cmake libevent-devel -y

//j检查本机是否安装mysql数据库，如果有就卸载

rpm -qa mysql

systemctl stop mysqld

rpm -ev mysql-server*

rpm -qa mysql

//编译安装mysql前需要先创建mysql用户和组，创建mysql安装目录和存放目录

groupadd mysql

useradd -M -s /sbin/nologin -g mysql mysql

mkdir -p /data/mysql

chown -R msyql.mysql /data/mysql

mkdir -p /usr/local/mysql

//编译源码包

cd /usr/local/

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz

tar -xvf mysql-5.7.26.tar.gz

cd mysql-5.7.26.tar.gz

cmake . \

DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

DMYSQL_DATADIR=/data/mysql/ \

DSYSCONFDIR=/etc \

DMYSQL_USER=mysql \

DWITH_INNOBASE_STORAGE_ENGINE=1 \

DWITH_ARCHIVE_STORAGE_ENGINE=1 \

DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

DWITH_PARTITION_STORAGE_ENGINE=1 \

DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

DWITH_DEBUG=0 \

DWITH_READLINE=1 \

DWITH_SSL=system \

DWITH_ZLIB=system \

DWITH_LIBWRAP=0 \

DENABLED_LOCAL_INFILE=1 \

DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

DDEFAULT_CHARSET=utf8 \

DDEFAULT_COLLATION=utf8_general_ci

make && make install

//准备环境变量

echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

//生成数据库文件

cd /usr/local/mysql/

scripts/mysql_install_db --datadir=/data/mysql/ --

user=mysql

//准备配置文件

cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

//准备启动脚本

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

//启动服务

chkconfig --add mysqld ;service mysqld start

三、SQL语言用法详解

3.1 DDL-数据库操作

//创建数据库：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';

CHARACTER SET 'character set name'

COLLATE 'collate name'

//删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

//查看支持所有字符集：SHOW CHARACTER SET;

//查看支持所有排序规则：SHOW COLLATION;

//获取命令使用帮助：

mysql> HELP KEYWORD;

//查看数据库列表：

mysql> SHOW DATABASES;

3.12 创建表

创建表： CREATE TABLE

(1) 直接创建

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

注意：

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎

同一个库中表建议要使用同一种存储引擎类型

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}

获取帮助：mysql> HELP CREATE TABLE;

3.13 表操作

查看所有的引擎：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’

查看库中所有表状态： SHOW TABLE STATUS FROM db_name

3.14 数据类型

数据类型：

数据长什么样？

数据需要多少空间来存放？

系统内置数据类型和用户定义数据类型

MySql支持多种列类型：

数值类型

日期/时间类型

字符串(字符)类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型

简单就好，简单数据类型的操作通常需要更少的CPU周期

尽量避免NULL，包含为NULL的列，对MySQL更难优化

(一)整型

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的一些交互工具(例如MySQL命令行客端)用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布尔型， 是TINYINT(1)的同义词。zero值被视为假。非zero值视为真

(二)浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际

数据库里存的是123.457，但总个数还以实际为准，即6位

(三)定点数

在数据库中存放的是精确值,存为十进制

decimal(m,d) 参数m<65 是总个数，d<30且 d

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比

较大的时候，可以考虑使用bigint代替decimal

(四)字符串

字符串(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

(五)二进制数据：BLOB

BLOB和text存储方式不同，TEXT以文本方式存储，英文

存储区分大小写，而Blob是以二进制方式存储，不分大小写

BLOB存储的数据只能整体读出

TEXT可以指定字符集，BLOB不用指定字符集

(六)日期时间类型

- 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 无符号

3.15 示例

1.创建students表，id为主键自动增长且不能为空，name 长度最多为20且不能为空，age最多为127

CREATE TABLE students (id int NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)NOT NULL,age tinyint UNSIGNED);

2.查看students表的类型

DESC students;

创建students2表，主键为id、name，id不能为空，name长度最多为20且不能为空，age最多为127

3,CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

3.16 修改表示例

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;

3.2 DML-增删改查

DML: INSERT,DELETE,UPDATE,SELECT

3.21 INSERT

INSERT:一次插入一行或多行数据

语法： INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...)；

- 简化学法：INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

- 简化示例： insert * from students where name = 'lisi';

示例：

- 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] ... ]

- INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

3.22 UPDATE

UPDATE :修改一列或多列的某个或整列数据

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

- 简化学法：update tbl_name values

- 简化示例：update student set age = 20 where nam = 'lisi';

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

限制条件：

WHERE

LIMIT

Mysql 选项：--safe-updates| --i-am-a-dummy|-U

3.23 DELETE

DELETE:删除具体某个或某行或某列或表的数据

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

- 简化学法：delete tbl_name values

- 简化示例：delete ftom studeng where name = 'lisi';

可先排序再指定删除的行数

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

限制条件：

WHERE

LIMIT

TRUNCATE TABLE tbl_name; 清空表

3.24 SELECT

SELECT:查询具体的数据

SELECT:SELECT [ALL | DISTINCT | DISTINCTROW ] [SQL_CACHE | SQL_NO_CACHE]

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}] [FOR UPDATE | LOCK IN SHARE MODE]

- 简化学法：select tbl_name values

- 简化示例：select * from student where name = 'lisi';

- 字段显示可以使用别名：

col1 AS alias1, col2 AS alias2, ...

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

过滤条件：布尔型表达式

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

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

BETWEEN min_num AND max_num

IN (element1, element2, ...)

IS NULL

IS NOT NULL

- LIKE:

%: 任意长度的任意字符

_：任意单个字符

- RLIKE：正则表达式，索引失效，不建议使用

- REGEXP：匹配字符串可用正则表达式书写模式，同上

- 逻辑操作符：

NOT

AND

OR

XOR

- GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

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

HAVING: 对分组聚合运算后的结果指定过滤条件

- ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC

降序：DESC

- LIMIT [[offset,]row_count]：对查询的结果进行输出行数

数量限制

- 对查询结果中的数据请求施加“锁”

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写

LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

3.3 DCL-授权

授权：grant

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]; priv_type: ALL [PRIVILEGES]

- object_type:TABLE | FUNCTION | PROCEDURE

- priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name

- tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)

with_option: GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

- 简化学法：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

- 简化示例： grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;

- 回收授权：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

示例：REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘

查看指定用户获得的授权：

Help SHOW GRANTS

SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER[()];

注意：msyql服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中，mysql的服务进程通常会自动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表：

mysql> FLUSH PRIVILEGES;

3.4 多表查询

交叉连接：笛卡尔乘积

内连接：

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

不等值连接

自然连接:去掉重复列的等值连接

自连接

外连接：

左外连接：

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外连接

FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

子查询：在查询语句嵌套着查询语句，性能较差

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

用在WHERE子句中的子查询：

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

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

用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

用于EXISTS

用于FROM子句中的子查询

使用格式：SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

示例： SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

- 联合查询：UNION

SELECT Name,Age FROM students UNION

SELECT Name,Age FROM teachers;

四、注意事项

1.truncate与delete的区别

delete：

delete删除表内容时仅删除内容，但会保留结构

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项

可以通过回滚事务日志恢复数据

非常占用空间

truncate

删除表中所有数据，且无法恢复

表结构、约束和索引等保持不变，新添加的行计数值重置为初始值

执行速度比DELETE快，且使用的系统和事务日志资源少

通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放

对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据

不能用于加入了索引视图的表

2.刚安装的mysql必须修改密码以及初始化

mysql主目录下/bin/mysqld 命令来初始化

/usr/local/mysql/bin/mysqld --user mysql --basedir /usr/local/mysql --datadir /usr/local/mysql/data --initialize

3.对远程客户端授权后flush privileges必须刷新权限

4.mysql和mariadb可以当成是同一个软件，源代码一样