Mysql入门

什么是数据库

数据库就是存储数据的仓库，其本质就是一个文件系统，数据按照特定的格式存储起来，用户通过sql对数据库进行操作。

什么是DBMS

Database Management System的缩写，数据库管理系统，对数据进行统一管理和控制，以保证数据库的完整性和完整性。

常见的数据库管理系统

• Mysql，开源免费的小型数据库，被Oracle收购了，5.5版本后是由Oracle发布的版本。
• Oracle，收费的大型数据库。
• DB2，IBM公司的数据库产品，收费的，常用在银行系统中。
• SqlServer，微软收费的中型数据库，C#，.net等语言常使用该数据库。
• Sqlite，嵌入式的小型数据库，常用语移动端。

什么是SQL

Structured Query Language的缩写，是一种访问关系型数据库的标准语言。

• SQL发展简史
  1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86
  1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89
  1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）
  1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）
  2003年，ISO/IEC 9075:2003，SQL:2003
  2008年，ISO/IEC 9075:2008，SQL:2008
  2011年，ISO/IEC 9075:2011，SQL:2011
  如果要了解标准的内容，推荐泛读SQL92，因为它涉及了SQL最基础和最核心的一些内容，然后增量式的阅读其他标准。

SQL语言分类

• 数据查询语言(DQL: Data Query Language)
  用来查询数据库中表的记录。关键字：select，from，where等
• 数据操作语言(DML: Data Manipulation Language)
  用来对数据库中表的记录进行更新。关键字：insert，delete，update等
• 数据控制语言(DCL)
  用来定义数据库的访问权限和安全级别，及创建用户；关键字：grant等
• 数据定义语言(DDL)
  用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
• 事务处理语言(TPL)
  begin, transaction, commit和rollback
• 指针控制语言(CCL)
  declare cursor, fetch into, update where current用于对一个或多个单独行的操作

数据库有哪些岗位

dba: 数据库管理员
dbd: 数据库开发人员

Mysql相关产品介绍

• MySQL Community Server
  最流行的开源数据库管理软件，当前最新版本是5.7
• MySQL Cluster
  基于MySQL数据库而实现的集群服务，自身能提供高并发高负载等特性

Mysql主要版本

• MySQL4.0版本
  增加了子查询的支持，字符集增加UTF-8，GROUP BY语句增加了ROLLUP，mysql.user表采用了更好的加密算法，InnoDB开始支持单独的表空间
• MySQL5.0版本
  增加了Stored procedures、Views、Cursors、Triggers、XA transactions的支持，增加了INFORATION_SCHEMA系统数据库
• MySQL5.5版本
  默认存储引擎更改为InnoDB，提高性能和可扩展性，增加半同步复制
• MySQL5.6版本
  提高InnoDB性能，支持延迟复制
• MySQL5.7版本
  提升数据库性能和存储引擎，更健壮的复制功能，增加sys系统库存放数据库管理信息

安装

General Availability (GA) release（代表稳定版本，可在生产系统使用）

• 安装方式
  一般会选择二进制安装方式， 如果有特殊需求，比如修改一部分源码或修改MySQL深层次的配置，则会选择源码方式
• rpm方式安装mariadb

# 安装
# 1.安装mariadb-server(mysql数据库)和mariadb(mysql服务器linux下客户端)
yum -y install mariadb-server mariadb
# 2.查看mysql版本
msyql -V
# 3.启动mysql
systemctl start  mariadb
# 4.设置密码
mysql_secure_installation
会执行几个设置：
  a)Set root password?（为root用户设置密码）
  b)Remove anonymous users?（删除匿名账号）
  c)Disallow root login remotely? [Y/n] (是否取消root用户远程登录)
  d)Remove test database and access to it?（删除test库和对test库的访问权限）
  e)Reload privilege tables now?（刷新授权表使修改生效，直接回车）
# 5.登陆
mysql -uroot -p123456

# 卸载
# 1.先查看mariadb
rpm -qa|grep mariadb
# 2.卸载mariadb
yum remove mariadb
# 3.删除遗留目录
rm /etc/my.cnf
rm -rf /var/lib/mysql

• rpm方式安装mysql

# 先卸载老版本
# 1.查看系统上的mysql软件
rpm -qa|grep mysql
# 2. 有的话就卸载mysql
yum remove -y mysql mysql-libs mysql-common
rm -rf /var/lib/mysql/
rm /etc/my.cnf

# 安装
# 1.下载安装
wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
rpm -ivh mysql-community-release-el6-5.noarch.rpm
yum install -y mysql-community-server
# 2.配置mysql
[mysqld]
# MySQL设置大小写不敏感：默认：区分表名的大小写，不区分列名的大小写
# 0：大小写敏感 1：大小写不敏感
lower_case_table_names=1
# 默认字符集
default-character-set=utf8
# 3.启动mysql
systemctl start mysqld
# 我这边启动的时候报错
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details
我这里是因为/var/lib/mysql/文件夹已经存在，所以导致初始化失败
rm -rf /var/lib/mysql/ 
删除mysql文件夹再启动就起来老
# 登陆mysql, 默认密码为空
mysql -uroot -p
# 设置root用户密码
 /usr/bin/mysqladmin -u root password '123456';

• mysql远程连接授权

# grant 权限 on 数据库对象 to 用户
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
注:
all privileges : 表示授予所有的权限，这里可以指定具体的权限
*.* : 表示所有库中的所有表
'root'@'%' : root表示用户名，%表示任意的ip地址，可以指定具体的ip地址
identified by 'root' : root是数据库密码
with grant option : 这个选项表示该用户可以将自己拥有的权限授权给别人，
如果不想这个用户有这个grant的权限，可以不加这句。

MySQL语句

• 数据库操作

show databases; # 查看都有哪些数据库
create database dbname; # 创建数据库
use dbname; # 选择数据库
select database(); # 查看当前选中的数据库
drop database dbname; # 删除数据库
drop database if exists dbname;# 先判断是否存在再删除，防止出现mysql错误信息
# 或者直接到数据库存放目录移除也可以删除数据库
cd /var/lib/mysql
mv dbname /tmp

• 表操作

# 创建表
create table tbname(
   字段名 类型(长度) 约束
);
# 单表约束
主键约束: primary key
唯一约束: unique
非空约束: not null
注: 主健约束 = 唯一约束 + 非空约束

show tables; #查看都有哪些表
desc tablename; #查看表结构
show create table tablename; # 查看创建表执行了哪些命令
drop table tbname;  # 删除表
alter table tbname rename new_tbname; # 修改表名
alter table tbname modify 字段名 字段类型  # 修改表中的字段类型
alter table tbname change 原字段名 新字段名 新字段类型 # 修改表中字段类型和字段名
注: change和modify的区别
CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 
MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）
alter table tbname add 字段名 字段类型; # 添加字段
# 在指定位置添加字段
alter table tbname add id int(10) first;
alter table tbname add age int(10) after name;
alter table tbname drop name;  # 删除表中字段
alter table tbname character set 字符集;  #修改表的字符集

• 记录操作

# 增
insert into dbname (id, name, age) values (1, "zhangsan", 11);
insert into dbname values(1, "", 11);
insert into dbname values(1, "", 11), (2, "", 12);
# 查
select * from dbname;
# 删
delete from dbname where id = 1
# 改
update dbname set name="lisi" where id = 2;

# 去重
select distinct name, age from dbname;
# 区分大小写
select * from dbname where binary name = "zhangsan";

• 查询语句进阶

#逻辑运算符
and 且
or 或
not 非

# 算术运算符
= 等
<> 或 != 不等
> 大于
< 小于
>= 大于等于
<= 小于等于
in 在不在列表内
not in  与in相反

# 排序
order by 排序字段 asc #默认，降序
order by 排序字段 desc  #升序

# 范围运算
between and/or/in

# 模糊匹配
like 
not like

# limit限定
limit m,n

# 子查询
select * from tbname where id < (select id from .....)
# all表示小于子查询中返回全部值中的最小值
select * from tbname where id < all(select id from tbname)

# 连接查询
内连接：根据表中的共同字段进行匹配
外连接分两种：左外连接、右外链接

# 内连接(实际使用中inner可省略掉)
select 字段  from 表1 inner join 表2  on 表1.字段=表2.字段

# 外连接
# 左连接
select  字段 from a表 left join b表  on 连接条件
a表是主表，都显示。
b表从表
主表内容全都有，从表内没有的显示null
# 右连接
select 字段 from a表 right join  b表 on 条件 
a表是从表，
b表主表，都显示

# 聚合函数
# 算数运算函数
Sum()求和
avg()平均值
max() 最大值
min()最小值
count()统计记录数

# 算数运算
+ - * /
update books set price=price+5 where price<40;
update books set price=price*0.8 where price>70;

# 字符串函数
substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起
select substr(name,1,7) from tbname where id=1;

concat(str1,str2,str3.....) 拼接。 把多个字段拼成一个字段输出
select concat(name,age) from person;
select concat(name,"-",age) from person;

upper()大写 : 转为大写输出
select upper(name) from person where id=1

lower()小写 ：转为小写输出
select lower(name) from person where id=1

• 分组查询

# group by , having
select cid count(*) from tbname group by cid having cid > 10

• SQL解析顺序

SELECT DISTINCT
  < select_list >
FROM
  < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
  < where_condition >
GROUP BY
  < group_by_list >
HAVING
  < having_condition >
ORDER BY
  < order_by_condition >
LIMIT < limit_number >

# 执行顺序
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>   第二步和第三步会循环执行
4 WHERE <where_condition>       第四步会循环执行，多个条件的执行顺序是从左往右的。
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT                            分组之后才会执行SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>             前9步都是SQL92标准语法。limit是MySQL的独有语法。

数据类型

• 数值类型

类型	大小	范围(有符号)	范围(无符号)	描述
tinyint	1字节	-128~127	0~255	小整数值
smallint	2字节	-32768~32767	0~65535	大整数值
mediumint	3字节	-8388608~8388607	0~16777215	大整数值
int	4字节	-2147483648~2147483647	0~4294967295	大整数值
bigint	8字节	-9233372036854775808~9233372036854775807	0~18446744073709551615	极大整数值
float	4字节	~	~	单精度浮点数
double	8字节	~	~	双精度浮点数
decimal	decimal(m, d)	~	~	小数值

• 日期和时间类型

类型	大小	范围	格式	描述
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYYY	年份
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期和时间
timestamp	4	1970-01-01 00:00:00/2037	YYYY-MM-DD HH:MM:SS	日期和时间

• 字符串类型

类型	大小(字节)	描述
char	0~255	定长字符串
varchar	0~65535	变长字符串
tinyblob	0~255	不超过255字符的二进制字符串
tinytext	0~255	短文本字符串
blob	0~65535	二进制长文本数据
text	0~65535	长文本数据
mediumblob	0~16777215	二进制形式中等长度文本数据
mediumtext	0~16777215	中等长度文本数据
longblob	0~4294967295	二进制形式的极大文本数据
longtext	0~4294967295	极大文本数据

字段修饰符

• null 和 not null

create table person(
  id int not null,
  name varchar(20) not null,
  age int
);

# 报错: ERROR 1048 (23000): Column 'name' cannot be null
insert into person values(1, null, 11);
# 不报错
insert into person values(1, '', 11);
结论: not null的字段不能插入null，可以插入''

# 问题
1.not null， 为什么可以插入空值?

2.为什么not null的效率比null高?
null占用空间，所以在进行比较的时候，null会参与到字段比较，所以会对效率有一部分影响；索引时不会存储Null值，所有如果索引的字段为null, 索引的效率会下降很多。

3.判断字段不为空的时候，到底要  select * from table where column <> '' 还是要用 select * from table where column is not null

4.空值和null有什么区别?
空值是不占用空间的，null占用空间，比如：一个杯子，空值''代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是里面是有空气的

• default设置字段的默认值

如果字段没有设定default ,mysql依据这个字段是null还是not null，如果为可以为null，则为null。如果不可以为null，报错

• auto_increment

自动增长

常用select命令

# 当前日期和时间
select now();
# 当前日期
select curdate();
# 打印当前时间
select curtime();
# 打印当前数据库
select database();
# mysql版本
select version();
# 当前用户
select user();

# 系统信息
show variables;
show global variables;
show global variables like '%version%';
show variables like '%storage_engine%'; 默认的存储引擎
like模糊搜索还可用户where字句，例如
select * from students where stname like '%l%1%2%3%';
除了like 还有not like
show engines;查看支持哪些存储引擎

show status;
show global status like 'Thread%';

清空表

• delete

delete from tbname;

• truncate

truncate table tbname;

数据库导入导出

• 导入

# 方式1
mysql -uroot -p123456 book < book.sql

# 方式2
use book;
source /root/book.sql  #sql脚本的路径

• 导出

mysqldump -u root -p123456 book>book2.sql

-B : 导出整个库包含建库语句
-A：导出全部数据库

# 把select接口导出到文本文件中
select * into outfile '/tmp/123.txt' from books; 此处有个文件访问权限问题，mysql用户是可以访问/tmp路径的,所以这里放到tmp下
select * from books into outfile '/tmp/456.txt';
扩展：5.7版本导出报错，可以设置my.cnf 加上secure-file-priv="/ "