mysqll

web架构指mysql服务器1.概述MySQL 是一个关系型数据库管理系统2SQL概述dba是数据库管理员database administratordbd是数据库开发人员database developer3语句结构数据查询语言其语句，也称为“数据检索语句”，就是从表中查询数据。 数据操作语言在表中添加，修改和删除数据记录。也称为动作查询语言事务处理语言它由多条sql语句组成的整体，它的语句能确保被DML语句修改的表中的所有记录及时得到更新。数据控制语言设置数据库的访问权限。数据定义语言数据库中创建表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。指针控制语言它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。 安装mysql服务器方法一：上传mysql安装包和boost安装包到我们的服务器上[root@cong11 ~]# lsanaconda-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卸载boost[root@cong11 ~]# yum -y remove boost-[root@cong11 ~]# rpm -ivh mysql-community-5.7.26-1.el7.src.rpm[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/卸载系统自带mysql[root@cong11 ~]# yum -y remove mysql[root@cong11 ~]# yum -y remove mariadb安装依赖包[root@cong11 ~]# yum install -y cmake make gcc gcc-c++ bison ncurses ncurses-devel添加用户和用户组[root@cong11 ~]# groupadd mysql[root@cong11 ~]# useradd -M -s /sbin/nologin -r -g mysql mysql 创建mysql数据库存放位置并修改权限[root@cong11 ~]# mkdir -p /data/mysql/{data,log}[root@cong11 ~]# chown -R mysql:mysql /data/mysql注意：生产环境中mysql的data数据目录会单独放在一块硬盘中解压源码包[root@cong11 ~] cd /root/rpmbuild/SOURCES/[root@cong11 SOURCES]# tar  -zxvf mysql-5.7.26.tar.gz  -C  /root/预编译[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目录参数注释：DCMAKE_INSTALL_PREFIX：指定MySQL程序的安装目录，默认/usr/local/mysqlDEFAULT_CHARSET：指定服务器默认字符集，默认latin1DWITH_EXTRA_CHARSET=all：支持扩展字符集DEFAULT_COLLATION：指定服务器默认的校对规则，默认latin1_general_ciDENABLED_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：服务端口号，默认3306DMYSQL_UNIX_ADDR：socket文件路径，默认/tmp/mysql.sock编译make &&make install修改目录权限[root@cong11 mysql-5.7.26]# chown -R mysql:mysql /usr/local/mysql/生成my.cnf配置文件自行创建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的字符集为utf8log-error=/data/mysql/log/mysqld.log     #指定mysql的错误日志存放路径pid-file=/usr/local/mysql/mysqld.pid #mysql的pid文件存放目录生成启动脚本[root@cong11 ~]#  cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@cong11 ~]# chmod +x /etc/init.d/mysqld   #给脚本添加执行权限添加开机自启[root@cong11 ~]# chkconfig --add mysqld      #把mysql添加到系统服务[root@cong11 ~]# chkconfig mysqld on        #把mysql添加到开机自启动[root@cong11 ~]# chkconfig --list mysqld      #查看mysql开机自启动状态初始化数据库[root@cong11 ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql \ --basedir=/usr/local/mysql --datadir=/data/mysql/data启动服务[root@cong11 ~]# /etc/init.d/mysqld  start添加PATH路径[root@cong11 ~]# vim /etc/profile 添加下面2行 在文件的结尾export MYSQL_HOME=/usr/local/mysqlexport PATH=$PATH:$MYSQL_HOME/bin使修改生效[root@cong11 ~]# source /etc/profile或者：[root@cong11 ~]# ln -s /usr/local/mysql/bin/ /usr/local/bin/修改mysql密码[root@cong11 ~]# mysqladmin -u root password “123456"或者登陆到mysql修改密码[root@cong11 ~]# mysql mysql> set password for ‘root’@‘localhost’=password(‘123456’);登录mysql[root@cong11 ~]# mysql -uroot -p123456mysql> show databases;±-------------------+| Database           |±-------------------+| information_schema || mysql              || performance_schema || sys                |±-------------------+4 rows in set (0.00 sec)  mysql的错误日志错误日志默认情况是开启的，大概记录的：服务器的启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）查看mysql错误日志的定义信息mysql> show global variables like ‘%log_error%’; 其中log_error定义为错误日志文件路径。log_error_verbosity:The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity。There are three possible values, as documented in the manual:Verbosity ValueMessage Types Logged1Errors only2Errors and warnings3Errors, warnings, and notes (default)更改错误日志位置可以使用log-error来设置形式如下#vi  /etc/my.cnflog-error = /data/mysql/log/mysqld.log为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。删除错误日志：在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –p  flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：方式如下： mysql语句mysql数据库操作查看数据库mysql> show databases;±-------------------+| Database           |±-------------------+| information_schema || mysql              || performance_schema || sys                |±-------------------+4 rows in set (0.00 sec)1：information_schema这个数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型，访问权限等。 [ˈskimə]2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表，3：mysql库是系统库，里面保存有账户信息，权限信息等。4：mysql5.7增加了sys 系统数据库，sys数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息,元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。以行的方式显示mysql>  show databases \G  #以行的方式显示在shell中查看mysql -e后面直接跟sql语句，这种方式一般是在shell脚本中用到[root@cong11 ~]# mysql -e ‘show databases’ -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.创建数据库语法create 数据库名创建格式mysql> create database HA;mysql> create database HA-test;查看数据库mysql> show databases;     可以去数据目录下查看新创建的目录ls  /data/mysql/data/选择操作数据库use 数据库名；查看自己所处在的位置mysql> select database();在命令行选择默认的数据库我们也可以在命令行直接选择我们需要进入的数据库[root@cong11 ~]# mysql -uroot -p123456 HA-testmysql> select now(),user(),database(); 删除数据库移动数据库目录[root@cong11 ~]# mkdir /db_backup[root@cong11 ~]# mv /data/mysql/data/HA  /db_backup  [root@cong11 ~]# mysql -uroot -p123456mysql> show databases;     #发现HA数据库已经不见了 使用if exists子句避免删除不存在的数据库是出现mysql错误信息mysql> drop database if exists HA-test; #如果存在则删除也可以在创建数据库时使用mysql> create database if not exists HA;  #if not exists 如果不存在则创建表的操作创建表语法：create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);mysql> use HA;Database changedmysql> create table student(id int(20),name char(40),age int);Query OK, 0 rows affected (0.02 sec)查看信息，mysql> use HA;Database changedmysql> show tables; 查看表结构使用desc 命令来查看表的结构mysql> desc student; 可以用以下命令查看表结构，会一种就可以。mysql> explain mysql.user;mysql> show columns from mysql.user;mysql> show fields from mysql.user;mysql> show columns from mysql.user like ‘%user’;查看创建表执行了哪些命令mysql>  show create table student \G指定默认存储引擎和字符集新建一个表，指定默认的存储引擎为InnoDB,编码为utf8mysql> create table student2(id int(20),name char(40),age int) ENGINE=InnoDB DEFAULT CHARSET=utf8; 删除表mysql> drop table student2;禁止遇读表信息没有禁止前转换数据库会有提示信息mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with –A解决这个问题可以在登陆mysql的时候添加参数-Amysql> use mysql;     Database changed    #发现没有提示信息了修改表名称语法：alter table 表名 rename 新表名;修改HA库中student表名为studentsmysql> alter table student rename students;Query OK, 0 rows affected (0.02 sec)mysql> show tables; 修改表中的字段类型语法：alter table 表名 modify 要修改的字段名 要修改的类型;修改字段id 的int（20）字段类型为int（10）mysql> alter table students modify id inalt(10);desc 表名 查看修改是否成功修改表字段类型和字段名称语法：alter table 表名 change 原字段名 新字段名 新字段类型;mysql> alter table students change name stname char(20);表中添加字段语法：alter table 表名 add字段名 字段类型；enum  #枚举类型，比如性别，只能在男女选择，是男非女，是女非男mysql>  alter table students add sex enum(‘M’,‘W’);  表中指定位置添加字段第一列添加一个字段mysql> alter table students add uid int(10) first;在age后面添加一个address字段mysql> alter table students add addremysqss char(40) after age; 删除表中字段语法：alter table 表名 drop 字段名 ;mysql>  alter table students drop address;插入表中记录操作插入记录时values所指定的值要和表中字段的个数、顺序以及类型要一一对应。mysql> insert into students values(1,‘zhangs’,21);插入多行mysql>  insert into students values(2,‘lis’,24),(3,‘wange’,26);分开插入我们也可以指定所要插入数据的字段：语法：INSERT INTO table_name (字段1, 字段2,…) VALUES (字段值1, 字段值2,…)例如：向students表中的id,name字段插入数据mysql> insert into students (id,name)values(4,‘hangl’);查询表中记录查询表中所有记录mysql>  select * from students;只查询表中某个字段或某些字段的内容语法：select 字段 from 表名mysql>select name from students;查询多个字段mysql>select id,name from student;查看别的数据库表语法：select  字段 from 数据库名，表名；mysql>select * from HA.students; 删除表中的记录语法：delete from 表名 where id=3id=3表示删除表id字段的第三行删除age为空的行mysql> delete from students where age is null;更新记录updata语法用于修改表中的记录语法：updata  表名 set age=25  where id=2;把表中id为2的记录age更新为25同时更新多个字段，用逗号隔开mysql> updata 表名 set id=1,name=’zhangsan’where age=21;SQL条件查询语句首先表中插入数据mysql>inset into students values(2,‘lisi’,23),(3,‘wange’,26),(4,‘libin’,28),(5,‘tom’,30),(6,‘sorry’,24);查询表中的name，age中的数据mysql> select name,age from students;去重查询distinctmysql> 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默认查询不区分大小写mysql> select name from students where name=‘kill’;此命令默认查询kill，查询结果不区分大小写区分大小写查询mysql> select * from students where binary name=‘kill’;   mysql查询排序语法：select字段1，字段2 from 表名order by 字段名; 升序ascmysql> select id from students order by id asc;降序descmysql> select id from students order by id desc;mysql的数据类型数据类型是数据的一种属性，其可以决定数据的存储格式，有效范围和相应的限制。mysql的数据类型包括整数类型，浮点数类型，日期和时间类型，字符串类型和二进制类型。为什么定义数据类型？为什么要是数据分类1、使系统能够根据数据类型来操作数据。2、预防数据运算时出错。例：通过强大的数据分类把每个类型与特定的行为联系在一起，执行这些行为时，数据分类可以预防错误。最长见的错误是字符与数字相加。3、更有效的利用空间。数据分类，可以使用最少的存储来存放数据，同时提高性能。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。             数值类型 日期和时间类型 字符串类型 测试取值范围想创建表和字符类型mysql> create table kdata ( fti tinyint,fsi smallint,fmi mediumint ,fi int, fbi bigint);进行插入数据mysql> insert into kdata values (123456789,123456789,123456789,123456789,123456789);错误1264（22003）：第1行“fti”列的值超出范围，对于不符合的数据报错mysql> insert into kdata values (123,12345,1234567,123456789,123456789);进行查询mysql> select * from kdata;         float数值类型例如：float(3,1)  ：表示此字段有效位数为3位，小数点后面1位数字。小数点后超过1位，mysql自动给四舍五入。例如：mysql> use db1;mysql> create table kdata6(test float(3,1)) engine=innodb default charset=utf8;mysql> insert into kdata6 values (123.455);ERROR 1264 (22003): Out of range value for column ‘test’ at row 1mysql> insert into kdata6 values (23.5);Query OK, 1 row affected (0.00 sec)mysql> insert into kdata6 values (3.455);Query OK, 1 row affected (0.00 sec)mysql>  insert into kdata6 values (23.455);Query OK, 1 row affected (0.00 sec)mysql> select * from kdata6; 发现整数+小数一共只能有3位，整数2位，小数1位，小数点后超过1位自动四舍五入。存储精确的小数: double和decimal数值类型decimal、double要比float存储小数更精确。mysql> create table ckdata4(tf float(5,2),td double(5,2),tdc decimal(5,2));Query OK, 0 rows affected (0.01 sec)mysql> insert into ckdata4 values(56.346,56.346,56.346);Query OK, 1 row affected, 1 warning (0.01 sec)注意：数据也插入成功，但是有一个警告提示：mysql> select * from ckdata4; mysql> create table ckdata6(tf float(10,2),td double(10,2),tdc decimal(10,2));Query OK, 0 rows affected (0.01 sec)mysql>  insert into ckdata6 values(1234567.89,1234567.89,1234567.89);Query OK, 1 row affected (0.00 sec)mysql> select * from ckdata6;那么有把握的情况下，推荐使用decimal。char 和 varcharchar char ：后面括号中必须有数值，来确认字符串的范围。  大小范围 ：0-255. char(10) ;  指定了一个长度为10的字符值。 旧版本小于长度，空格自动补齐，大于长度 ，自动截短。 新版本超过长度自动报错。 mysql>  create table kdata8 (aaa char(10));Query OK, 0 rows affected (0.01 sec) mysql>  insert into kdata8 values (‘1234567890111’);ERROR 1406 (22001): Data too long for column ‘aaa’ at row 1 mysql> insert into kdata8 values (‘abcdefghijklmn’);ERROR 1406 (22001): Data too long for column ‘aaa’ at row 1 mysql> insert into kdata8 values (‘1234567890’);Query OK, 1 row affected (0.00 sec) mysql>  insert into kdata8 values (‘abc’);Query OK, 1 row affected (0.01 sec)mysql> select * from kdata8;binary 修饰符:  区分字符大小写mysql>  alter table kdata8 modify aaa char(10) binary;Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0 mysql>  insert into kdata8 values (‘ABC’);Query OK, 1 row affected (0.01 sec) mysql> select * from kdata8 where aaa=‘abc’; mysql> select * from kdata8 where aaa=‘ABC’;varchar ： 字符串可变长在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节的数据，起始位和结束位占去了3个字节。mysql> create table kdata88 (aaa varchar(4));Query OK, 0 rows affected (0.01 sec)mysql> insert into kdata88 values (”");Query OK, 1 row affected (0.00 sec)mysql>  insert into kdata88 values (“ab”);Query OK, 1 row affected (0.00 sec)mysql>  insert into kdata88 values (“abcd”);Query OK, 1 row affected (0.01 sec)mysql>  insert into kdata88 values (“abcdefg”);ERROR 1406 (22001): Data too long for column ‘aaa’ at row 1超过长度自动报错，最大支持长度65535.date 日期mysql> create table kdata10 (birthday date);mysql> insert into kdata10 values (‘2018-01-23’) ,(20190304);mysql> select * from kdata10; time时间mysql> create table kdata11(showtime  time);mysql> insert into kdata11 values (‘11:10:23’),(‘11:23’),(112456);mysql> select * from kdata11;Yearyear ： 00-69自动转为： 2000-2069 , 70-99自动转为1970-1999mysql> create table kdata13 (test year);mysql> insert into kdata13 values (2018),(04),(9),(69),(70);mysql> select * from  kdata13; datatime 或 timestampdatetime类型能保存的最大范围的值为1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYY-MM-DD HH:MM:SS的整数中，与时区无关，使用8个字节的存储空间。timestamp类型保存了从1970年1月1日（格林尼治时间）以来的秒数，它和linux的时间戳相同，只是用了4个字节的存储空间，因此它的范围比datetime的范围小了很多，只能表示从1970年到2038年，我们可以使用它提高空间利用率。mysql> create table kdata14 ( f_datatime datetime,f_timestamp timestamp);mysql> insert into kdata14 values (‘1999-11-12 23:23:45’,19991112232345);mysql> select * from kdata14;