Mysql从⼊门到精通整理
数据库是信息化产业的最基础的软件之⼀，各种管理系统，⽹站，在线游戏，背后基本都会有数据库的⽀持。
mysql基础
数据库基本概念
磁盘⽂件--> 层次模型 --> ⽹状模型 --> (Codd) 关系模型
DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核⼼
DBA：负责数据库的规划、设计、协调、维护和管理等⼯作
关系：关系就是⼆维表。并满⾜如下性质：表中的⾏、列次序并不重要
⾏row：表中的每⼀⾏，⼜称为⼀条记录
列column：表中的每⼀列，称为属性，字段
主键（Primary key）：⽤于惟⼀确定⼀个记录的字段；复合主键：多个字段组合成⼀个主键(NOT NULL)；
惟⼀键：⼀个或多个字段的组合，填⼊的数据必须能在本表中唯⼀标识本⾏；允许为NULL，⼀个表可以存在多个
约束：
主键约束：惟⼀、⾮空；⼀张表只能有⼀个；
惟⼀键约束：惟⼀，可以存在多个；
外键约束：参考性约束；
检查性约束：check；
三层模型：
物理层 --> SA (决定数据的存储格式，即RDBMS在磁盘上如何组织⽂件)
逻辑层 --> DBA(描述存储什么数据，以及数据间存在什么样的关系)
视图层 --> Coder(描述DB中的部分数据)
域domain：属性的取值范围，如，性别只能是‘男’和‘⼥’ 两个值
表：row, column；
关系运算：选择、投影
数据库：表、索引、视图（虚表）、SQL、存储过程procedure，过程⽆返回值、存储函数function，过程有返回值、触发器trigger、事件调度器event scheduler，任务计划；
事务transaction：多个操作被当作⼀个整体对待 ACID: 原⼦性（Atomicity）、⼀致性（Consistency）、隔离性（Isolation，⼜称独⽴性）、持久性（Durability）。
原⼦性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执⾏过程中发⽣错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执⾏过⼀样。
⼀致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：两个事务的执⾏是互不⼲扰的，⼀个事务不可能看到其他事务运⾏时，中间某⼀时刻的数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
数据的操作：• 数据提取：在数据集合中提取感兴趣的内容。（SELECT）• 数据更新：变更数据库中的数据。（INSERT、DELETE、 UPDATE）
应⽤程序指以数据库为基础的应⽤程序：DBMS --> RDBMS（关系型数据库管理系统）
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越⾼的范式数据库冗余越⼩。
⽬前关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，⼜称完美范式）。满⾜最低要求的范式是第⼀范式（1NF）。在第⼀范式的基础上进⼀步满⾜更多规范要求的称为第⼆范式（2NF），其余范式以次类推。⼀般说来，数据库只需满⾜第三范式(3NF）即可
RDBMS：
范式：第⼀范式、第⼆范式、第三范式；
1NF：⽆重复的列，每⼀列都是不可分割的基本数据项，同⼀列中不能有多个值
2NF：属性完全依赖于主键，第⼆范式必须先满⾜第⼀范式，要求表中的每个⾏必须可以被唯⼀地区分。
3NF：属性不依赖于其它⾮主属性，满⾜第三范式必须先满⾜第⼆范式。第三范式要求⼀个数据库表中不包含已在其它表中已包含的⾮主关键字信息，⾮PK的字段间不能有从属关系SQL: Structure Query Language：结构化查询语⾔--- SQL解释器---数据存储协议：应sq⽤层协议，C/S
olacle(tcp:1521),mysql(tcp:3306),sql server(tcp:1433)
mysql基础和应⽤结构
mysql特性：插件式存储引擎、单进程多线程
mysql体系结构：
安装：服务端mysql-server和客户端mysql，yum安装，⼆进制安装，源码编译安装
提⾼安全性：运⾏mysql_secure_installation
服务端程序：
mysqld, mysqld_safe, mysqld_multi
客户端程序：
mysql, mysqldump, mysqlbinlog, mysqladmin, ...
⾮客户端类管理程序：
myisamchk, myisampack, ...
配置⽂件： /etc/myf 和 /etc /myf.d/ *f
mysql命令选项：
-uUSERNAME: ⽤户名；默认为root
-
hHOST: 服务器主机; 默认为localhost
-pPASSWORD：⽤户的密码；建议使⽤-p，默认为空密码
mysql⽤户账号由两部分组成： 'USERNAME'@'HOST' HOST⽤于限制此⽤户可通过哪些远程主机连接mysql服务
⽀持使⽤通配符： % 匹配任意长度的任意字符 172.16.64.0/16 或 172.16.%.% _ 匹配任意单个字符
运⾏mysql前通常修改的参数
在配置⽂件的[mysqld]中添加两个选项：
innodb_file_per_table = on 启⽤innodb存储引擎
skip_name_resolve = on 禁⽌主机名解析
MYSQL语⾔基础
在数据库系统中，SQL语句不区分⼤⼩写(建议⽤⼤写) ，但字符串常量区分⼤⼩写
SQL语句可单⾏或多⾏书写，以“;”结尾，关键词不能跨多⾏或简写
⽤空格和缩进来提⾼语句的可读性，句通常位于独⽴⾏，便于编辑，提⾼可读性
注释： SQL标准： /*注释内容*/ 多⾏注释 -- 注释内容单⾏注释，注意有空格
MySQL注释： #
数据库对象的命名规则：1.必须以字母开头；2.可包括数字和三个特殊字符（# _ $）；3.不要使⽤MySQL的保留字；4.同⼀Schema下的对象不能同名MySQL的数据类型：字符型、数值型、⽇期时间型、内建类型
字符型：CHAR(#)， BINARY(#)：定长型；CHAR不区分字符⼤⼩写，⽽BINARY区分；
VARCHAR(#)， VARBINARY(#)：变长型TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
数值型：
浮点型：近似FLOAT、DOUBLE、REAL、BIT
整型：精确：INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT；DECIMAL
⽇期时间型：⽇期：DATE；时间：TIME；⽇期j时间：DATETIME；间戳：TIMESTAMP；年份：YEAR(2), YEAR(4)
内建：ENUM：枚举：ENUM('Sun','Mon','Tue','Wed')；SET：集合
类型修饰符：
字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED（使⽤unsigned会使数据可变长度增长⼀倍）
⽇期时间型：NOT NULL， NULL， DEFAULT
DML:
INSERT/REPLACE（增）, DELETE（删）, SELECT（查）, UPDATE（改），ALTER。
创建数据库：CREATE DATABASE |SCHEMA [IF NOT EXISTS] 'DB_NAME';
删除数据库：DROP DATABASE | SCHEMA [IF EXISTS] 'DB_NAME';
查看⽀持所有字符集：SHOW CHARACTER SET;
查看⽀持所有排序规则：SHOW COLLATION;
查看数据库列表：SHOW DATABASES;
创建表：CREATE TABLE 'DB_TABLE_NAME' ;
删除表：DROP TABLE [IF EXISTS] tb_name;
查看表： USE 'DB_NAME' ; SHOW TABLES ;
查看⽀持的engine类型：SHOW ENGINES;
查看表结构：DESC [db_name . ]tb_name;
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'\G
实例：
CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name)); 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;
ALTER TABLE students ADD gender ENUM('m','f') （枚举，gender的域值为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); （添加age的索引）
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;
索引：定义在查时作为查条件的字段 ----优点：提⾼查询速度；缺点：占⽤额外空间，影响插⼊速度（创建索引必须要有索引名称）创建索引： CREATE INDEX index_name ON tbl_name (index_col_name,...);
删除索引： DROP INDEX index_name ON tbl_name;
查看索引： SHOW INDEXES FROM [db_name.]tbl_name;
INSERT：（插⼊，增）
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...
UPDATE：（改）
UPDATE tbl_name SET col1=val1, col2=val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]]
[LIMIT [m,]n];
DELETE:：（删）
DELETE FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; 可先排序再指定删除的⾏数
SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n]; Limit m,n 跳过m个，要n个SELECT字段表⽰法： *：所有字段 as：字段别名， col1 AS alias1 例：
WHERE clause:
操作符： >, <, >=, <=, ==, != , BETWEEN ... AND ...
LIKE: %：任意长度的任意字符 ; _：任意单个字符；
RLIKE：正则表达式模式匹配
IS NULL ，IS NOT NULL IN （val1,val2,…）
条件逻辑操作： and，or，not
⽰例：
DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students (id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
mysql视图和存储过程SELECT * FROM students ORDER BY name DESC LIMIT 2; （符合条件的前两个）
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4;
SELECT * FROM students WHERE BETWEEN 2 AND 4 ;
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid , name as stuname FROM students
查询执⾏路径：
请求-->查询缓存
请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执⾏引擎-->存储引擎-->缓存-->响应
SELECT语句的执⾏流程：FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit
mysql⽤户与授权管理
⽤户创建、查看、删除与密码管理
创建⽤户： CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
查看当前⽤户: SELECT user();
查看⽤户： SELECT User,Host,Password FROM user;
删除⽤户：DROP USER 'username'@'host'; ⽰例：删除默认的空⽤户 DROP USER ''@'localhost';
更改⼝令： 1)SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
2) UPDATE user SET password=PASSWORD('magedu') WHERE User='root' ;
注意：上⾯修改表的命令不会马上⽣效，需执⾏FLUSH PRIVILEGES⽣效
3) /usr/local/mysql/bin/mysqladmin -u root –poldpassword password 'newpassword
4）删除mysql数据库⽬录/var/lib/mysql/mysql/下的mysql数据库，此时所有⽤户信息都丢失，且可以空⼝令登录
忘记管理员密码的解决办法：
(1) 启动mysqld进程时，使⽤--skip-grant-tables和--skip-networking选项；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通过UPDATE命令修改管理员密码；
(3) 以正常⽅式启动mysqld进程；
mysql授权
权限级别：管理权限、数据库、表、字段、存储例程
GRANT priv_type,... ON [object_type] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]; priv_type: ALL [PRIVILEGES] ,select,insert,update,delete,alter
⽰例： GRANT SELECT,DELETE on testdb.* TO 'testuser'@'%' IDENTIFIED BY 'testpass‘;(⼀步就包含创建⽤户和授权)
查看指定⽤户获得的授权：
Help SHOW
GRANTS SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER();
回收授权： REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host
⽰例： REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘
注意：MariaDB服务进程启动时会读取mysql库中所有授权表⾄内存
(1) GRANT或REVOKE等执⾏权限操作会保存于系统表中，MariaDB的服务进程通常会⾃动重读授权表，
使之⽣效
(2) 对于不能够或不能及时重读授权表的命令，可⼿动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;
练习与思考：
导⼊hellodb.sql⽣成数据库
(1) 在students表中，查询年龄⼤于25岁，且为男性的同学的名字和年龄；
SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M';
(2) 以ClassID为分组依据，显⽰每组的平均年龄；
SELECT ClassID,avg(age) FROM students GROUP BY ClassID;
(3) 显⽰第2题中平均年龄⼤于30的分组及平均年龄；
SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;
(4) 显⽰以L开头的名字的同学的信息；
SELECT * FROM students WHERE Name LIKE 'L%';
(5) 显⽰TeacherID⾮空的同学的相关信息；
SELECT * FROM students WHERE TeacherID IS NOT NULL;
(6) 以年龄排序后，显⽰年龄最⼤的前10位同学的信息；
SELECT * FROM students ORDER BY Age DESC LIMIT 10;
(7) 查询年龄⼤于等于20岁，⼩于等于25岁的同学的信息；⽤三种⽅法；
练习：导⼊hellodb.sql，以下操作在students表上执⾏
1、以ClassID分组，显⽰每班的同学的⼈数；
SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;
2、以Gender分组，显⽰其年龄之和；
SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
3、以ClassID分组，显⽰其平均年龄⼤于25的班级；
SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25;
4、以Gender分组，显⽰各组中年龄⼤于25的学员的年龄之和；
练习：导⼊hellodb.sql，完成以下题⽬：
1、显⽰前5位同学的姓名、课程及成绩；
2、显⽰其成绩⾼于80的同学的名称及课程；
3、求前8位同学每位同学⾃⼰两门课的平均成绩，并按降序排列；
4、显⽰每门课程课程名称及学习了这门课的同学的个数；
思考：
1、如何显⽰其年龄⼤于平均年龄的同学的名字？
2、如何显⽰其学习的课程为第1、2，4或第7门课的同学的名字？
3、如何显⽰其成员数最少为3个的班级的同学中年龄⼤于同班同学平均年龄的同学？
4、统计各班级中年龄⼤于全校同学平均年龄的同学。
mysql进阶
存储引擎
SHOW TABLE STATUS
常见的存储引擎：SHOW ENGINES;
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED InnoDB：InnoBase
SHOW ENGINE INNODB STATUS;
MyISAM：
⾏格式：{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
其它的存储引擎：
CSV：将CSV⽂件（以逗号分隔字段的⽂本⽂件）作为MySQL表⽂件；
MRG_MYISAM：将多个MyISAM表合并成的虚拟表；
BLACKHOLE：类似于/dev/null，不真正存储数据；
MEMORY：内存存储引擎，⽀持hash索引，表级锁，常⽤于临时表；
FEDERATED: ⽤于访问其它远程MySQL服务器上表的存储引擎接⼝；
并发控制：锁机制：Lock
事务
事务：⼀组原⼦性的SQL查询、或者是⼀个或多个SQL语句组成的独⽴⼯作单元；
事务⽇志：
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups
ACID测试：
A：AUTOMICITY，原⼦性；整个事务中的所有操作要么全部成功执⾏，要么全部失败后回滚；
C：CONSISTENCY，⼀致性；数据库总是应该从⼀个⼀致性状态转为另⼀个⼀致性状态；
I：ISOLATION，隔离性；⼀个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之⽬的，隔离有多种级别；D：DURABILITY，持久性；事务⼀旦提交，其所做出的修改会永久保存；
⾃动提交：单语句事务
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0；
⼿动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务⽀持savepoints：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
事务隔离级别：
READ-UNCOMMITTED：读未提交 --> 脏读；
READ-COMMITTED：读提交--> 不可重复读；
REPEATABLE-READ：可重复读 --> 幻读；
SERIALIZABLE：串⾏化；
mysql> SELECT @@_isolation;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论