What is InnoDB ?
InnoDB is a storage engine for the database management system MySQL ---------- from Wiki
翻译过来就是:
或许,你和我一样会问,What is storage engine ? 什么是存储引擎 ?
这段话,我只能看懂,不能精准的翻译出来,谈谈我的理解:
我们使用的MySQL是一款数据库管理系统,数据库管理系统就是用来管理数据的,管理数据的前提是,先存储数据,如同仓库的作用是存储货物,和管理货物,可将存储引擎类比于仓库。由存储引擎来决定如何对数据进行存取,如何查找,我们只需要使用SQL语句下命令即可。引擎几乎是MySQL最核心的部分。
MySQL的存储引擎在my,ini文件里:
MySQL服务的启动:
net start mysql
如果是Windows10的OS,需要使用管理员权限,启动mysql;也可以使用mysql自带的工具启动;还可以在服务中启动。MySQL 服务必须先启动,才能和其他的客户端连接,MySQL中应该有类似于这样的一段代码(实际是C/C++写的),完成对客户端的监听:
ServerSocket server = new ServerSocket(3306)//从my.ini文件中读取
while(true){
Socket socket = service.accept();
//每一个客户端一个线程
}
以上,我们完成了对MySQL服务端的操作,下面我们来看看对MySQL客户端的操作
MySQL的登录:
$ mysql -uroot -p
Enter password: ***********
这里简单的介绍一下,如果你想像我一样,使用git来操作MySQL的话,你需要使用下面的命令登录mysql:
$ winpty mysql -uroot -p
Enter password: ***********
除了命令行,MySQL客户端还有:JDMC程序和Navicat,Sqlyog等可视化的工具
几个重要的文件夹和文件:
- Linux下的配置文件:/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
- Windows下的配置文件:C:\ProgramData\MySQL\MySQL Server 5.7/my.ini(在我的机器)
# CLIENT SECTION
# ----------------------------------------------------------------------
[client]
# pipe
# socket=0.0
port=3306
[mysql]
no-beep
default-character-set=utf8
# SERVER SECTION
# ----------------------------------------------------------------------
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
Windows下MySQL的配置文件很长,这里只是截取部分。
D:\Program Files\MySQL\MySQL Server 5.7\bin 可执行文件(.exe结尾)
SQL的分类:
MySQL常用的数据类型:
int:整型
double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
decimal:浮点型,在表示钱方面使用该类型,因为不会出现精度缺失问题;
char:固定长度字符串类型;char(4) 范围是0-255
varchar:可变长度字符串类型;效率低,每次要计算占的字节数,省内存
text:字符串类型;
blob:字节类型;//jp9+g mp3 avi
date:日期类型,格式为:yyyy-MM-dd;
time:时间类型,格式为:hh:mm:ss
timestamp:时间戳类型;日期+时间
基本的常见操作(一):
mysql> show databases;
mysql> show tables;
mysql> show create table stu;#查看表的创建的时候使用的语句
mysql> show variables like '%dir%';#查看和dir相关的变量的信息
mysql> desc stu;#显示表结构的信息
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid | char(6) | YES | | NULL | |
| sname | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
mysql> use mydb1;
mysql> create database db;
mysql> create table tb_name(
-> id char(5),
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into tb_name values('10001','Andy');
mysql> select * from tb_name;
+-------+------+
| id | name |
+-------+------+
| 10001 | Andy |
| 10001 | 张飞 |
+-------+------+
常见的基本操作(二)
mysql> alter table tb_name rename to tea;#修改表名
mysql> alter table tea add(age int);#增加字段
mysql> alter table tea add primary key(id);#增加完整性约束主键。注意,已经修改了'张飞'的id值
mysql> alter table tea add unique(name);#增加完整性约束条件unique
mysql> alter table tea modify age char(1);#修改字段的属性
mysql> alter table tea change age years_old char(2);#修改字段的名字或属性
mysql> alter table tea drop years_old;#删除某字段
辨析一下change 和 modify 的区别
DQL(Data Query Language)
DQL是作为数据操纵语言的存在的,但是由于查询操作的使用评率是极高的,因此被单独的抽离出来。关于数据查询,我们来看看query查询的格式:
select [all | distinct] <目标列表达式> [<目标列表达式>] ...
from <表名或视图名> [,<表名或视图名>...] | (<select>)[as] <别名>
[where <条件表达式>]
[group by <列名1> [having<条件表达式>]];
[order by <列名2> [ASC | DESC]];
这个表达式不需要强记,写的多了,可能脑子没想到,查询语句就“跃然纸上”了,但是接下来的这段话,就要理解了,而且是必须要理解:
接下来,介绍一些值得记录的查询,有些太过简单的,这里就不累赘了,之前,先进行数据准备:
mysql> create database firstdb;
mysql> use firstdb;
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'FanBingBing', 35, 'female');
INSERT INTO stu VALUES('S_1002', 'LiBingBing', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'LiuDeHua', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'ZhouXinChi', 65, 'male');
INSERT INTO stu VALUES('S_1005', 'WangJiaWei', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'ZhaoLiYin', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'GuoFuChen', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'ZhangZiYi', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'WuJin', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'ZhaoWei', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
OK,到目前为止,我们建立了三张表,接下来我们基于这三张表进行实验。
必要的SQL查询语句(一)
#查询学生表中,年龄不是null的元组,这里使用的是 is 不是 =
mysql> select * from stu where age is null;
mysql> select * from stu where sname like '_a%';
#SQL 中的字符串匹配不同于正则表达式,_ 表示匹配任意单个字符,% 表示匹配0个或多个字符。
查看雇员的月薪和佣金的和
mysql> select sal + comm as income from emp;
+---------+
| income |
+---------+
| NULL |
| 1900.00 |
| 1750.00 |
| NULL |
| 2650.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 1500.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------+
mysql> select sal + ifnull(comm,0) income from emp;
+---------+
| income |
+---------+
| 800.00 |
| 1900.00 |
| 1750.00 |
| 2975.00 |
| 2650.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+---------+
查询员工表中的员工编号,姓名,工资,按照工资降序,编号升序
mysql> select empno,ename,sal from emp order by sal desc,empno asc;
#select empno,ename,sal from emp order by sal desc,empno; # 默认是升序的
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7839 | KING | 5000.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7499 | ALLEN | 1600.00 |
| 7844 | TURNER | 1500.00 |
| 7934 | MILLER | 1300.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7369 | SMITH | 800.00 |
+-------+--------+---------+
必要的SQL查询语句(二)聚合与分组
首先来看看有哪些聚合函数和这些聚合函数对应的作用
count(*) 的和count(1)结果效果,这和存储引擎相关,如果是myisam类的引擎,没有什么区别,因为这种引擎内部有一个计算器在维护这行数;如果是InnoDB引擎的话,是会去数一遍而不是直接读值,所以效率较低。
举个例子:查询emp表有comm的员工人数
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
MySQL是如何进行查询的呢?首先,会进行如下的操作:
mysql> select comm from emp;
+---------+
| comm |
+---------+
| NULL |
| 300.00 |
| 500.00 |
| NULL |
| 1400.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 0.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------+
然后,在对数据进行统计,并且
比如,求工资大于平均工资的员工:
mysql> select * from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
正确的求解方法应该是这样写的:
mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.05 sec)
或者使用最原始的方法:先将平均工资求出来,在使用where子句,在这里就不在演示。
接着,我们来谈论一下group by子句,下面的这段话将有助于我们理解并运用聚集函数:
例子:查询部门表中,每个部门的部门编号和每个部门的工资和:
mysql> select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 10 | 8750.00 |
| 20 | 10875.00 |
| 30 | 9400.00 |
+--------+----------+
为了帮助我们更加深刻的理解group by 子句,我们来看看下面的一个例子:首先查询一下emp表中有哪些记录:
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
可以看出,deptno中有10,20,30,那么接下来我们对emp表按照deptno分组,并select出所有的字段,可以预见,分组之后,我们只会得到三条数据(因为只有三组),我们使用下面的这条查询语句:
mysql> select * from emp group by deptno;
那么除了deptno字段信息,其他字段会有什么信息呢?我们拭目:
mysql> select * from emp group by deptno;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+-------+-------+----------+------+------------+---------+--------+--------+
我们发现,MySQL随机从本组中select了一条记录,来填充除了deptno字段之外的信息,但是这些信息都没有意义,因为这三条记录只是组中某一个元组的信息,而不是整个组的信息。因此正确的查询姿势是:
mysql> select deptno from emp group by deptno;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
在将having 加入到 group by中,来对分组之后的数据进行筛选。来个例子,查询工资总和大于9000的部门编号以及工资和:在写查询语句之前,我们来分解一下题目的意思,:
OK,又到了表演的时刻:
mysql> select deptno,sum(sal) from emp group by deptno having sum(sal) > 9000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 20 | 10875.00 |
| 30 | 9400.00 |
+--------+----------+
到此,我们发现where子句和having子句的作用都是过滤,我们来辨析二者区别
分页技术:
后端会对前段的搜索结果给出反馈,接触最多的就是搜索引擎,当我们键入关键词之后,服务器返回结果就是经过分页处理的,对于MySQL来说,我们看例子:对emp表中的数据进行分页查询(共14条数据,分成三页)
mysql> select * from emp limit 0,5;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)
mysql> select * from emp limit 5,5;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+-------+--------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from emp limit 10,5;
+-------+--------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+---------+------+------------+---------+------+--------+
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)
我想你应该看明白了。
至此,我认为的MySQL基础差不多就要结束了,关于MySQL,请看下一篇: