1、 Mysql概述:
1.1、 Mysql概述
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
MySQL 是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,它分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,所以在web网站的开发都选择 MySQL作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache/nginx 可组成良好的开发环境。
双授权政策:开放源代码版本和商业版本。 例如:很多公司出售应用软件,例如 Microsoft、Apple 和 Oracle;如 Red Hat 和 IBM,则通过为其开放源代码软件提供销售支援、托管或咨询等服务来进行赚钱。企业可以通过开放源代码发布他们的软件,也可以同时销售该软件的商业版本。
附:
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。
MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。
MariaDB由MySQL的创始人麦克尔·维德纽斯主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入Oracle的手中。
MariaDB名称来自麦克尔·维德纽斯的女儿玛丽亚(英语:Maria)的名字。
MariaDB直到5.5版本,均依照MySQL的版本。从2012年11月12日起发布的10.0.0版开始,不再依照MySQL的版号。10.0.x版以5.5版为基础,加上移植自MySQL 5.6版的功能和自行开发的新功能。
在存储引擎方面,10.0.9版起使用XtraDB(名称代号为Aria)来代替MySQL的InnoDB。
1.2、 SQL概述
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。
从上可以看出我们数据库相关工作职位大概两种:DBD和DBA
dba是数据库管理员database administrator
dbd是数据库开发人员database developer
SQL 是1986年10 月由美国国家标准局(ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。1989年4月,ISO提出了具有完整性特征的SQL89标准,1992年11月又公布了SQL92标准,在此标准中,把数据库分为三个级别:基本集、标准集和完全集。
1.3、 SQL语句结构
结构化查询语言包含6个部分
1.3.1、 数据查询语言(DQL)
其语句,也称为“数据检索语句”,就是从表中查询数据。
1.3.2、 数据操作语言(DML):
在表中添加,修改和删除数据记录。也称为动作查询语言。
1.3.3、 事务处理语言(TPL):
它由多条sql语句组成的整体,它的语句能确保被DML语句修改的表中的所有记录及时得到更新。
1.3.4、 数据控制语言(DCL):
设置数据库的访问权限。
1.3.5、 数据定义语言(DDL):
在数据库中创建表或删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。
1.3.6、 指针控制语言(CCL):
它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
2、 源码安装mysql
2.1、 准备实验环境
2.1.1、 准备源码包
方法一:
上传mysql安装包和boost安装包到我们的服务器上
[root@cong11 ~]# ls
anaconda-ks.cfg boost_1_59_0.tar.bz2 mysql-5.7.26.tar.gz
方法二:
mysql下载地址:
https://dev.mysql.com/downloads/mysql/5.7.html#downloads
注意:官网提供下载的源码包为mysql-community-5.7.26-1.el7.src.rpm以.src.rpm结尾的rpm包,需要rpm -ivh安装这个包,在当前目录下生成rpmbuild的目录文件,然后在rpmbuild/SOURCES/目录下才是我们.tar.gz结尾的源码包。
2.1.2、 配置本地yum源
[root@cong11 ~]# mount /dev/sr0 /mnt
[root@cong11 ~]# vim /etc/yum.repos.d/centos7.repo
[centos7]
name=CentOS7
baseurl=file:///mnt
enable=1
gpgcheck=0
上传mysql-community-5.7.26-1.el7.src.rpm到mysql服务器(执行lrzsz提供的rz命令)的相关目录,如/root目录中。
2.1.3、 卸载boost
MySQL从5.7.5版本之后,必须有boost库的支持(mysql源码中用到了C++的boost库,要求必须安装boost1.59.0或以上版本),建议把系统自带的boost库卸载,使用高版本。
[root@cong11 ~]# yum -y remove boost-*
[root@cong11 ~]# rpm -ivh mysql-community-5.7.26-1.el7.src.rpm
在当前目录下生成rpmbuild的目录文件,然后在rpmbuild/SOURCES/目录下才是我们.tar.gz结尾的源码包。
解压boost_1_59_0.tar.bz2到root目录下,待会安装直接指定boost目录。
安装bzip2解压工具,解压缩boost_1_59_0.tar.bz2
[root@cong11 ~]# yum install -y bzip2
[root@cong11 ~] cd /root/rpmbuild/SOURCES/
[root@cong11 SOURCES]# tar -jxvf boost_1_59_0.tar.bz2 -C /root/*
2.1.4、 卸载系统自带的mysql*
[root@cong11 ~]# yum -y remove mysql*
[root@cong11 ~]# yum -y remove mariadb*
2.2、 安装mysql
2.2.5、 安装MYSQL依赖包
Mysql需要使用cmake来进行编译,所以需要安装cmake,并且5.7版本cmake版本不低于2.8
[root@cong11 ~]# yum install -y cmake make gcc gcc-c++ bison ncurses ncurses-devel
2.2.6、 添加mysql用户和用户组
[root@cong11 ~]# groupadd mysql
[root@cong11 ~]# useradd -M -s /sbin/nologin -r -g mysql mysql
2.2.7、 创建mysql数据库存放位置并修改目录权限
[root@cong11 ~]# mkdir -p /data/mysql/{data,log}
[root@cong11 ~]# chown -R mysql:mysql /data/mysql
注意:生产环境中mysql的data数据目录会单独放在一块硬盘中
2.2.8、 解压源码包
[root@cong11 ~] cd /root/rpmbuild/SOURCES/
[root@cong11 SOURCES]# tar -zxvf mysql-5.7.26.tar.gz -C /root/
2.2.9、 预编译
预编译需要我们指定安装服务的参数或者模块,还会对编译环境进行测试,只有通过测试,才能进行编译,预编译失败一般都是缺少编译环境GCC或者其他的依赖包,需要我们手工安装完依赖包后,再次进行预编译。
[root@cong11 ~]# cd mysql-5.7.26
[root@cong11 mysql-5.7.26]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/root/boost_1_59_0
#指定我们刚才解压的boost目录
官网详细地址:
https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
参数注释:
DCMAKE_INSTALL_PREFIX:指定MySQL程序的安装目录,默认/usr/local/mysql
DEFAULT_CHARSET:指定服务器默认字符集,默认latin1
DWITH_EXTRA_CHARSET=all:支持扩展字符集
DEFAULT_COLLATION:指定服务器默认的校对规则,默认latin1_general_ci
DENABLED_LOCAL_INFILE:可以使用load data infile命令从本地导入文件,默认OFF。(-DENABLED_LOCAL_INFILE=1 支持将本地文件转换为数据库数据)
DWITH_READLINE=1 使用readline功能,即方便在命令行复制、粘贴命令。
DWITH_xxx_STORAGE_ENGINE:指定静态编译到mysql的存储引擎,MyISAM, MERGE,MEMBER以及CSV四种引擎默认即被编译至服务器,不需要特别指定。
DWITH_MYISAM_STORAGE_ENGINE=1 :添加MYISAM存储引擎支持
DWITH_INNOBASE_STORAGE_ENGINE=1 :添加INNOBASE存储引擎支持
DWITH_MEMORY_STORAGE_ENGINE=1 : 添加MEMORY存储引擎支持
DSYSCONFDIR:初始化参数文件目录
DMYSQL_DATADIR:数据文件目录
DMYSQL_TCP_PORT:服务端口号,默认3306
DMYSQL_UNIX_ADDR:socket文件路径,默认/tmp/mysql.sock
检测命令执行结果
[root@cong11 mysql-5.7.26]#echo $?
0
#0说明上条命令执行成功,其他执行失败
2.2.10、 编译
[root@cong11 mysql-5.7.26]# make -j $( grep processor /proc/cpuinfo | wc -l)
注:-j 用来指定CPU核心数,可加快编译速度。
查看服务器CPU核心数
[root@cong11 mysql-5.7.26]# grep processor /proc/cpuinfo | wc -l
4
检测命令执行结果
[root@cong11 mysql-5.7.26]# echo $?
0
2.2.11、 安装
[root@cong11 mysql-5.7.26]# make install
检测命令执行结果
[root@cong11 mysql-5.7.26]# echo $?
0
2.2.12、 修改mysql目录权限
[root@cong11 mysql-5.7.26]# chown -R mysql:mysql /usr/local/mysql/
2.2.13、 生成my.cnf配置文件
备份配置文件文件
[root@cong11 ~]# mv /etc/my.cnf{,.bak}
自行创建my.cnf配置文件
[root@cong11 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql #mysql安装目录
datadir=/data/mysql/data #mysql数据存放目录
port=3306 #mysql监听端口
socket=/usr/local/mysql/mysql.sock #mysql.sock文件存放目录
symbolic-links=0 #关闭mysql的符号链接
character-set-server=utf8 #指定mysql的字符集为utf8
log-error=/data/mysql/log/mysqld.log #指定mysql的错误日志存放路径
pid-file=/usr/local/mysql/mysqld.pid #mysql的pid文件存放目录
注意:所有my.cnf指定的目录mysql用户都要有读写权限。symbolic-links符号链接支持在别的目录下存放mysql的数据库文件,当我们mysql数据盘满了以后,可以启动symbolic-links=1,把mysql的数据放在别的目录下,然后链接到mysql的datadir目录下。
2.2.14、 生成服务启动脚本
[root@cong11 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@cong11 ~]# chmod +x /etc/init.d/mysqld #给脚本添加执行权限
2.2.15、 添加mysqld开机自启动
[root@cong11 ~]# chkconfig --add mysqld #把mysql添加到系统服务
[root@cong11 ~]# chkconfig mysqld on #把mysql添加到开机自启动
[root@cong11 ~]# chkconfig --list mysqld #查看mysql开机自启动状态
2.2.16、 初始化数据库
如果无法初始化先启动mysql
[root@cong11 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql \
--basedir=/usr/local/mysql --datadir=/data/mysql/data
2.2.17、 启动服务
[root@cong11 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
2.2.18、 添加path路径:
添加mysql命令路径到path环境变量中或者把mysql命令路径软连接到path环境变量里已经存在的目录,这样我们可以在系统任意位置直接执行mysql相关命令
[root@cong11 ~]# vim /etc/profile 添加下面2行 在文件的结尾
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
使修改生效
[
root@cong11 ~]# source /etc/profile
或者:
[root@cong11 ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
2.2.19、 修改mysql密码:
[root@cong11 ~]# mysqladmin -u root password "123456"
或者登陆到mysql修改密码
[root@cong11 ~]# mysql
mysql> set password for 'root'@'localhost'=password('123456');
2.2.20、 登陆mysql
[root@cong11 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
删除表中的记录
DELETE语句用于删除表中的记录。
语法:
DELETE FROM table_name WHERE some_column=some_value;
请注意 SQL DELETE 语句中的 WHERE 子句!
WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句,所有的记录都将被删除!
删除students表中id为3的行
mysql> delete from students where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students; #发现表中id为3的记录不见了
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
| 4 | hangl | NULL |
+------+--------+------+
3 rows in set (0.00 sec)
删除age为空的行
mysql> delete from students where age is null;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | zhangs | 21 |
| 2 | lis | 24 |
+------+--------+------+
2 rows in set (0.00 sec)
更新记录
update 语句用于修改表中的数据。
语法:
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
请注意 SQL UPDATE 语句中的 WHERE 子句!
WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句,所有的记录都将被更新!
把表中id为2的记录age更新为25
mysql> update students set age=25 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | zhangs | 21 |
| 2 | lis | 25 |
+------+--------+------+
2 rows in set (0.01 sec)
把表中所有的id都更新为2
mysql> update students set id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select * from students;
同时更新多个字段的值,请使用逗号隔开
mysql> update students set id=1,name='zhangsan' where age=21;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
SQL条件查询语句
首先往表中插入一些数据
mysql> insert into students values(2,'lisi',23),(3,'wange',26),(4,'libin',28),(5,'tom',30),(6,'sorry',24);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
查询STUDENTS表中的NAME,AGE
mysql> select name,age from students;
可以看到表中有重复的数据lisi
去重复查询distinct
mysql> select distinct name,age from students; #可以看见重复的行不在了
使用AND和OR进行多条件查询
查询表中id>3和age>25的记录
mysql> select id,name,age from students where id>3 and age>25;
查询表中id>3 或者 age>25的记录
mysql> select id,name,age from students where id>3 or age>25;
MYSQL区分大小写查询binary
Mysql默认查询是不区分大小写的
BINARY是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解为在字符串比较的时候区分大小写。
插入大写记录
mysql> insert into students values(7,'KILL',32),(8,'kill',32);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select name from students where name='kill';
区分大小写查询
mysql> select * from students where binary name='kill';
mysql> select * from students where binary name='KILL';
MYSQL查询排序
语法:select字段1,字段2 from 表名order by 字段名;
默认为升序asc
mysql> select id from students order by id asc;
降序desc
mysql> select id from students order by id desc;
关于MYSQL命令帮助
help会告诉我们很多使用方法和信息
mysql> help show;
mysql> help select;
Mysql的数据类型
MySQL数据类型
数据类型是数据的一种属性,其可以决定数据的存储格式,有效范围和相应的限制。mysql的数据类型包括整数类型,浮点数类型,日期和时间类型,字符串类型和二进制类型。
为什么定义数据类型?为什么要数据分类?
1、使系统能够根据数据类型来操作数据。
2、预防数据运算时出错。
例:通过强大的数据分类把每个类型与特定的行为联系在一起,执行这些行为时,数据分类可以预防错误。最长见的错误是字符与数字相加。
3、更有效的利用空间。数据分类,可以使用最少的存储来存放数据,同时提高性能。
数据类型解释
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
数值类型
日期和时间类型
字符串类型
在MySQL中支持的5个主要整数类型是 TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT。这些类型在很大程度是相同的,只有他们存储的值的大小是不同的。
使用举例:
如果用来保存用户的年龄 (举例来说,数据库中保存年龄是不可取的),用 TINYINT 就够了;
如果要用作一个肯定不会超过 16000000 行的表的 AUTO_INCREMENT 的 IDENTIFY 字段,当然用 MEDIUMINT 不用 INT,试想,每行节约一个字节,16000000 行可以节约 10 兆多。
注意:再MySQL5.7中,插入不符合数据类型的数据字段,服务被报错,不允许插入。
数据类型的测试
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
mysql> use test_db;
Database changed
测试取值范围
mysql> create table kdata ( fti tinyint,fsi smallint,fmi mediumint ,fi int, fbi bigint);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into kdata values (123456789,123456789,123456789,123456789,123456789);
ERROR 1264 (22003): Out of range value for column 'fti' at row 1
错误1264(22003):第1行“fti”列的值超出范围,对于不符合的数据报错
mysql> insert into kdata values (123,12345,1234567,123456789,123456789);
Query OK, 1 row affected (0.00 sec)
mysql> select * from kdata;
插入错误的值
mysql> create table kdata2 (age int) ;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into kdata2 values ('hello' );
ERROR 1366 (HY000): Incorrect integer value: 'hello' for column 'age' at row 1
int类型修饰符
unsigned 无符号整数,修饰符: 规定字段只能保存正的数据。它可以增大这个字段的正数支持的范围。
zerofill 修饰符: 规定0(不是空格 ) 填补输出的值。 使用这个值可以防止 mysql存储负值。
unsgined和zerofill使用方法
mysql> create table kdata3 (fi int,fiu int unsigned, fiz int zerofill,fiuz int unsigned zerofill);
Query OK, 0 rows affected (0.01 sec)
查看表结构:
mysql> desc kdata3;
注意:发现fiz和fiuz字段值是一样的。
查看原因:
mysql> show create table kdata3;
测试:
mysql> insert into kdata3 values (10,10,10,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into kdata3 values (-10,-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'fiu' at row 1
mysql> insert into kdata3 values (-10,10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'fiz' at row 1
mysql> insert into kdata3 values (-10,10,100,-10);
ERROR 1264 (22003): Out of range value for column 'fiuz' at row 1
mysql> insert into kdata3 values (-10,10,100,1000);
Query OK, 1 row affected (0.00 sec)
可以看见在fiu,fiz和fiuz中存储负数,直接报错,因为unsgined和zerofill不允许使用负值,zerofill会自动添加unsgined,并且在数值前面补0
注意:
int(M) 在 integer 数据类型中,M 表示最大显示宽度。
在 int(M) 中,M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。其实,除了显示给用户的方式有点不同外,int(M) 跟 int 数据类型是相同的。
如int的值为10
int(10)显示结果为0000000010
int(3)显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间,可以使用的空间也一样。当我们生成固定长度的序列号时,可以使用zerofill。
如:卡号,默认使用空格填充,不方便显示出来。现在以0来填充,查看一下显示的内容
mysql> create table azerofill (fi int(3), fiz int(3) zerofill,fiuz int(4) unsigned zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into azerofill values(11,11,11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from azerofill;
+------+------+------+
| fi | fiz | fiuz |
+------+------+------+
| 11 | 011 | 0011 |
+------+------+------+
1 row in set (0.00 sec)
测试,插入超过显示范围的值。
mysql> insert into azerofill values(123456,123456,123456);
Query OK, 1 row affected (0.01 sec)
mysql> select * from azerofill;
发现位数不够用0补齐,超过设定值,正常显示。
浮点型数据类型
float数值类型用于表示单精度浮点数值,而double数值类型用于表示双精度浮点数值,float和double都是浮点型,而decimal是定点型;
MySQL浮点型和定点型可以用类型名称后加(M,D)来表示,M表示该值的总共长度,D表示小数点后面的长度,M和D又称为精度和标度,如float(7,4)表示总长度是7位,小数点后面的长度是4,可显示为999.9999,MySQL保存值时进行四舍五入,如果插入999.00009,则结果为999.0001。
float:单精度浮点型,占字节数为4,用32位二进制描述,有符号是7个有效位,无符号是8个有效位
double:双精度浮点型,占字节数为8,用64位二进制描述,有符号是15个有效位,无符号是16个有效位
decimal:数字型,用128位二进制描述,不存在精度损失,常用于银行帐目计算。(28个有效位)
解释:单精度和双精度
精度是指计算机表达小数近似值的一种方式,单精度32位二进制,4个字节;双精度64位二进制,8个字节。