Mysql简介及sql语句

一、数据库的概述

Mysql: DBMS(数据库管理系统 DataBase Manager System)

概述：数据库(Database)是按照数据结构组织、存储和管理数据的仓库，它产生于距今六十多年前，数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统，在各个方面得到了广泛的应用。在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。

二、数据库的分类

关系型数据库： sql数据库
非关系型数据库： nosql数据库，not only sql数据库

关系型数据库：数据拥有固定的存储结构，通过库–表--行–列的方式存储，存储时会有表的结构化关系，过程如下：解析sql语句–连接层–磁盘存取–结构化成表，；

优势：

1.容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解；
2.使用方便，通用的sql语句使得操作关系型数据库非常方便；
3.易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率；

瓶颈：

1.海量数据的读写效率低，对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/O是一个很大的挑战。
2.高扩展性和可用性，在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

关系型数据库到非关系型数据库的演变：关系型数据库的最大优点就是事务的一致性，这个特性，使得关系型数据库中可以适用于一切要求一致性比较高的系统中。比如：银行系统。但是在大部分网页应用中，对这种一致性的要求不是那么的严格，允许有一定的时间间隔，所以关系型数据库这个特点不是那么的重要了。相反，关系型数据库为了维护一致性所付出的巨大代价就是读写性能比较差。而像微博、facebook这类应用，对于并发读写能力要求极高，关系型数据库已经无法应付。所以必须用一种新的数据结构存储来替代关系型数据库。所以非关系型数据库应用而生。
非关系型数据库，就是为了加快客户端的访问速度才出现的，因为所有的非关系型数据库都是尽可能的将数据放到内存当中；非关系型数据库是以key:value的形式存储的；
非关系型数据库：NoSQL主要指那些非关系型的、分布式的，主要代表MongoDB，Redis、CouchDB。NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可，所以避免了关系型数据库复杂的查询关系，可以大大增加查询的效率；
变量名=变量值
键(key)=值(value) name=dgf name=1

---

三、Mysql的概述：

概述：MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下公司。是一个真正的多用户、多线程SQL数据库服务器。SQL（结构化查询语言）是世界上最流行的和标准化的数据库语言。MySQL是以一个客户机/服务器结构的实现,它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。 SQL是一种标准化的语言,它使得存储、更新和存取信息更容易；

create database dgf character set utf8;

四、Mysql存储原理

Mysql整体架构：

1.连接池：最上层负责和客户端进行连接，比如jdbc，odbj这样的数据库连接的API，在这一层有连接池的概念，类似于线程池，连接池可以同时处理很多个数据库请求。同时这一层有SSL的安全概念，可以确保连接是安全的；
2.SQL接口：当SQL语句进入MySQL后，会先到SQL接口中，这一层是封装层，将传过来的SQL语句拆散，将底层的结果封装成SQL的数据格式；
3.解析器：这一层负责将SQL语句进行拆分，验证，如果语句有问题那么就返回错误，如果没问题就继续向下执行；
4.优化器：对SQL查询的结果优化处理，产生多种执行计划，最终数据库会选择最优化的方案去执行，尽快返会结果。比如select a,b from c where d。在这里会先查询c表中符合d的数据并将他们的ab项进行投影，返回结果，并不会直接把整张表查出来；
5.缓存：对要查询的SQL语句进行hash后缓存，如果下一次是相同的查询语句，则在SQL接口之后直接返回结果；
6.存储引擎：MySQL有很多种存储引擎，每一种存储引擎有不同的特性，他们负责组织文件的存放形式，位置，访问文件的方法等等。比较常用的有innoDB，MyISAM，MEMORY等；
7.文件系统：真正存放物理文件的单位；

---

五、案例：centos 7搭建Mysql 5.7

案例环境：

系统	系统
ip	192.168.1.1
主机名	www.zps.com
所需软件	mysql-5.7.12.tar.gz 和 boost_1_59_0.tar.gz
硬件
CPU核心：2	内存：4G

Mysql下载方式：
https://downloads.mysql.com/archives/community/

案例步骤：

下载安装Mysql软件程序
需要使用windows下载，不能使用linux下载

wget http://dev.mysql.com/Downloads/MySQL-5.7/mysql-5.7.12.tar.gz
wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download

点击下载mysql-5.7.12.tar.gz
提取码:bvab

点击下载boost_1_59_0.tar.bz2
提取码:qzdf

修改主机名

hostname www.zps.com

vi /etc/hosts
192.168.1.1 www.zps.com

yum -y remove mysql-* boost-*
rpm -qa mysql
rpm -qa boost
yum -y install gcc gcc-c++ ncurses bison libgcrypt perl cmake ncurses-devel
tar -jxvf boost_1_59_0.tar.bz2
mv boost_1_59_0 /usr/local/boost/
groupadd mysql
useradd -r -g mysql mysql
tar zxvf mysql-5.7.12.tar.gz -C /usr/src/
cd /usr/src/mysql-5.7.12/
##编译多次时，需要删除CMAKE的缓存，rm -rf /usr/src/mysql-5.7.12/CMakeCache.txt
cmake  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DSYSCONFDIR=/etc

注释：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql                [MySQL安装的根目录]
-DMYSQL_DATADIR=/mydata/mysql/data                     [MySQL数据库文件存放目录]
-DDEFAULT_CHARSET=utf8                                 [设置默认字符集为utf8]
-DDEFAULT_COLLATION=utf8_general_ci                    [设置默认字符校对]
-DMYSQL_TCP_PORT=3306                                  [MySQL的监听端口]
-DMYSQL_USER=mysql                                     [MySQL用户名]    
-DWITH_MYISAM_STORAGE_ENGINE=1                         [安装MySQL的myisam数据库引擎]
-DWITH_INNOBASE_STORAGE_ENGINE=1                       [安装MySQL的innodb数据库引擎]
-DWITH_ARCHIVE_STORAGE_ENGINE=1                        [安装MySQL的archive数据库引擎]
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 					   [安装MySQL的blackhole数据库引擎]
-DWITH_MEMORY_STORAGE_ENGINE=1                         [安装MySQL的memory数据库引擎]
-DENABLE_DOWNLOADS=1                                   [编译时允许自主下载相关文件]
-DDOWNLOAD_BOOST=1 									   [允许下载BOOST]
-DWITH_BOOST=/usr/local/boost 						   [指定系统中存在的BOOST]

-DSYSCONFDIR=/etc                                      [MySQL配置文件所在目录]
-DWITH_READLINE=1                                      [MySQL的readline library]
-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock            [MySQL的通讯目录]
-DENABLED_LOCAL_INFILE=1                               [启用加载本地数据]
-DWITH_PARTITION_STORAGE_ENGINE=1  					   [启动mysql的分区存储结构]
-DEXTRA_CHARSETS=all                                   [使MySQL支持所有的扩展字符]
-DWITH_DEBUG=0                                         [禁用调试模式]
-DMYSQL_MAINTAINER_MODE=0 
-DWITH_SSL:STRING=bundled                              [通讯时支持ssl协议]
-DWITH_ZLIB:STRING=bundled                             [允许使用zlib library]

make -j `cat /proc/cpuinfo | grep processor| wc -l`
make install
ls /usr/local/mysql

---

优化调整Mysql程序

chown -R mysql:mysql /usr/local/mysql
cp /usr/src/mysql-5.7.12/support-files/my-default.cnf /etc/my.cnf
cp /usr/src/mysql-5.7.12/support-files/mysql.server /etc/init.d/
chmod +x /etc/init.d/mysql.server

编写服务控制脚本

cat <<END >>/usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqldapi
After=network.target

[Service]
Type=forking
PIDFile=/usr/local/mysql/logs/mysqld.pid
ExecStart=/etc/init.d/mysql.server start
ExecReload=/etc/init.d/mysql.server restart
ExecStop=/etc/init.d/mysql.server stop
PrivateTmp=Flase

[Install]
WantedBy=multi-user.target
END

注解：

PrivateTmp=Flase		##此配置必须关闭，不然mysql连接文件mysql.sock文件会默认生成在以下位置/tmp/systemd-private-83bba738e8ff4837b5ae657eff983821-mysqld.service-BPxWpJ/tmp/mysql.sock，导致数据库无法连接，将此配置项关闭后，则文件正常生成在/tmp/mysql.sock

echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile
source /etc/profile

---

初始化Mysql数据库服务

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注解：

--initialize-insecure 				##禁用mysql的密码策略（密码复杂性等），--initializeaize是开启密码策略，自动生成密码在mysqld.log文件中
--user=mysql 						##运行的账户
--basedir=/usr/local/mysql 			##mysql的安装位置
--datadir=/usr/local/mysql/data		##mysql数据库服务数据的物理存放路径

cat <<END >/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
skip-name-resolve
END

注解：

[mysqld]					                            ##声明区域
basedir = /usr/local/mysql			                    ##mysql的安装位置
datadir = /usr/local/mysql/data		                    ##mysql的物理文件存放位置
port = 3306						                        ##mysql服务监听的端口
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES		##mysql的模式
character_set_server=utf8			                    ##字符集
init_connect='SET NAMES utf8'		
log-error=/usr/local/mysql/logs/mysqld.log			    ##指定日志文件位置
pid-file=/usr/local/mysql/logs/mysqld.pid			    ##指定运行服务所产生的pid文件位置
skip-name-resolve					                    ##跳过mysql的域名反向解析

mkdir /usr/local/mysql/logs
chown mysql:mysql /usr/local/mysql/logs/
systemctl start mysqld
systemctl enable mysqld
netstat -utpln |grep mysqld

---

测试连接访问数据库

mysqladmin  -u root -p password "123456"            ##登录mysql设置密码
mysql -uroot -p123456
mysql> exit

报错：

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

解决方法：

mysqladmin -h localhost -uroot password "123456"

---

六、Sql语句操作之增删改查

Sql语句分类：

DDL：数据定义语言，用来建立数据库，数据对象和定义其列，如create、alter、drop；
DML：数据操纵语言，用来查询、插入、删除、修改数据库中的数据，如select、insert、update、delete；
DCL：数据控制语言，用来控制数据库组件的存取许可，存取权限等，如commit、rollback、grant、revoke；

数据记录类型：
日期：

小数类型：

字符串：

SQL
什么是SQL？

Structured Query Language：结构化查询语言
Sql是执行在客户端（windows在命令行下执行，linux在终端执行）下或者通过java代码执行在JDBC

打开客户端并登陆mysql

linux下在任何位置直接打开终端即可
windows下使用各种客户端打开 登陆mysql命令 直接在终端下执行以下代码 然后输入密码
mysql -uroot -p
退出：
exit

SQL语句规范

1.以;(分号)结尾
2.关键字之间有空格 通常一个空格 但是有多个也没问题
3.sql语句中可以添加换行
4.SQL 不区分大小写

数据库相关SQL
1、查看所有数据库命令：

show databases;

2、创建数据库 通常一个项目只对应1个数据库 格式：create database 数据库名称;

create database db1;
指定字符集的创建格式 ：create database 数据库名称 CHARACTER SET utf8;
  create database db2 character set gbk;

3、查看数据库详情 -格式：show create database 数据库名称;

  show create database db1;

4、删除数据库 -格式：drop database 数据库名称;

  drop database db2;

5、选中数据库

  use db2;

表相关SQL
什么是表

-表是数据库中的数据组成单位 类似于Java中的对象 表中的字段 对应对象中的属性

创建表

格式： create table 表名 (字段1名 字段类型，字段2名 字段类型);
create table person (name varchar(10),age int);

创建表的原理：