Linux----------mysql基础

目录

• 一、数据库介绍
  • 1.1 数据库的优点
  • 1.2 数据库的基本功能
  • 1.3数据库的类型
  • 1.4 关系型数据的组成
  • 1.5 关系型数据库的常用组件
  • 1.6 SQL语句
  • 1.7 mysql命令使用
  • 1.8服务器监听的两种socket
• 二、mysql安装和配置
  • 2.1 mysql-yum安装
  • 2.2 msyql编译安装
• 三、SQL语言用法详解
  • 3.1 DDL-数据库操作
    • 3.12 创建表
    • 3.13 表操作
    • 3.14 数据类型
    • 3.15 示例
    • 3.16 修改表示例
  • 3.2 DML-增删改查
    • 3.21 INSERT
    • 3.22 UPDATE
    • 3.23 DELETE
    • 3.24 SELECT
  • 3.3 DCL-授权
  • 3.4 多表查询
• 四、注意事项

一、数据库介绍

数据库是数据的汇集，它以一定的组织形式存于存储介质上
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：修改
DML	INSERT：向表中插入数据
                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<m 是小数位
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可以当成是同一个软件，源代码一样