[数据库] MySql

参考资料

[1] MySQL的高级玩法
[2] SQL 注入
[3] 安装教程

0. 简介

1. 安装

1.1 windows安装

windows安装教程

1.2 ubuntu安装

ubuntu安装教程

1.3 centos安装

(1) 在使用yum安装 MySQL 前，先更新 yum
 $ yum update -y
 
(2) 添加 MySQL 的 yum 仓库源
 # 使用 wget下载 mysql yum 源
 $ wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
 
 # 添加 mysql yum 源(在 wget 的下载目录,紧接着上一命令,则无需调整)
 $ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm
 
 # 安装yum工具 yum-utils
 $ sudo yum install -y yum-utils
 
(3) 查看可用的 mysql,此时会看到当前可用的 MySQL 版本为 mysql80, 这是因为最新 mysql 最新版为 8.0 版本
 $ yum repolist enabled | grep "mysql.*-community.*"
 
 # 查看所有的 mysql 版本,可以看到目前启用(enable)的是 mysql80,如果需要安装 mysql 5.7 ,
 # 则需要先禁用(disable) mysql8.0,然后启用(enable)mysql5.7
 $ yum repolist all | grep mysql
 
 # 禁用 mysql8.0
 sudo yum-config-manager --disable mysql80-community
 
 # 启用 mysql 5.7
 sudo yum-config-manager --enable mysql57-community
 
 # 校验当前启用(enable)的 mysql 版本是否为 5.7;从结果中可以看出，当前启用的 mysql 版本为 5.7,没有问题
 yum repolist enabled | grep mysql
 
(4) 安装 mysql
 $ sudo yum install -y mysql-community-server
 
 # 启动 mysql 服务
 $ sudo service mysqld start
 
 # 查看 mysql 服务状态
 $ sudo service mysqld status
 
(5) MySQL 初始化设置
 # 从 MySQL 5.7 开始，mysql 安装之后，会默认随机生成一个临时密码,因此登录之后需要修改密码查看生成的初始密码
 $ sudo grep 'temporary password' /var/log/mysqld.log
 
 # 使用初始密码进行登录
 $ mysql -u root -p
 
 # 登录成功之后设置新密码:注意: 设置的新密码必须包含大写字母、小写字母、数字、标点符号，且密码长度至少为 8密码设置之后安装即完成
 $ mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
 $ mysql> exit # 退出mysql
 
(6) 设置 MySQL开机启动
 # MySLQ 安装完成之后运行的服务名称为: mysqld, CentOS 7 使用systemctl 命令工具操作服务
 # 启动start、停止stop、重启restart、开机启动enable、关闭开机启动disable
 $ systemctl enable mysqld
 
(7) 设置远程连接
 # 登录 mysql(使用新设置的密码)
 $ mysql -u root -p
 
 # 登录成功之后执行以下命令,其中 yourNewPassword 即为刚才设置的新密码
 $ mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'MyNewPass4!' WITH GRANT OPTION;
 
 # 刷新权限
 $ mysql> FLUSH PRIVILEGES;
 
(8) 建立数据库
 # 先在/opt/db/src下编写db.sql文件，文件内容从initdb.sql内复制
 # 然后在mysql环境下执行文件：
 $ mysql> source /opt/db/src/db.sql;

2. 配置

3. 使用

完整的DQL语句

select  5
  ..
 from   1 
  ..
 where   2
  ..
 group by  3
  ..
 having  4
  ..
 order by  6
  ..

3.1 连接数据库

mysql -h 10.0.0.0 -P 3306 -u root -p
# -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
# -u : 登录的用户名;
# -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

dns: "root:123456@tcp(10.0.0.0:3306)/shujuku?charset=utf8"    

3.2 查询

use your_database      # 进入your_database数据库   
desc your_table        # 查看表的定义 
status;                # 查看当前信息
truncate your_table；  # 清空your_table表 

show
SHOW DATABASES;                         # 查询数据库
show tables;                            # 查看数据表

select
select * from 表名                                         # 查看所有的字段记录 
select id from 表名                                        # 查看单个的字段记录 
select id,age from 表名                                    # 查看多个字段的记录 
select * from 表名 where 条件                              # 按条件查询 
select * from 表名 order by 字段名 asc/desc                # 排序查询 
select * from 表名 limit 2,5                               # 限制查询 从第二个开始向后查五个(不包括第二个)
select * from 表名 limit 5                                 #查询五个
select * from 表名 where 字段 like '%2%'                   # 查询包含字符2的数据，%为全部
select * from 表名 where 字段 like '_2%'                   # 查询第二个字符为2的数据
select * from 表名 where 字段 is not null                  # 找出非null的数据
select * from 表名 where 字段<40 or age<60;                # or
select * from 表名 where 字段>'1988-1-2' and 字段<'1988-12-1';# and 且
select * from 表名 where 字段 between 40 and 60            # between
select * from 表名 where 字段 in (30,48,68,99);            # in 查询指定集合内的数据
select max/min/avg/count/sum(字段) from 表名group by 字段; #分组查询
select distinct 字段 from 表名                             # distinct去重
select concat(字段1, 字段2) as 拼接 from 名                # concat拼接两个字符串
select 100*2, 100+2, 100+'20', '100'+'210',null+'20';      # 200,102,120,310,null
select count(*) from 表名 group by 字段 having count(*)>2; # 数目大于2

别名中有中文
select ename,sal * 12 as 年薪 from emp;   # 错误
select ename,sal * 12 as '年薪' from emp; # 正确
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。

查询所有字段

select * from emp; # 实际开发中不建议使用*，效率较低。

找出工资不等于3000的？

select ename,sal from emp where sal <> 3000;
select ename,sal from emp where sal != 3000;
找出工资在1100和3000之间的员工，包括1100和3000？
  

select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000; // between...and...是闭区间 [1100 ~ 3000]
select ename,sal from emp where sal between 3000 and 1100; // 查询不到任何数据
select ename from emp where ename between 'A' and 'C';

3.3 创建

create
create database your_database           # 创建your_database数据库   
create table (字段1 类型，字段2 类型)   # 创建数据表   
create table new_table like src_table;  # 复制表


CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `group` VARCHAR(100) NOT NULL,
   `rank` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.4 修改

update
update 表名 set 字段名=值;   # 修改数据 不带条件更改字段的所有记录 
insert
insert into 表名 (字段名，字段名) value (值，值)       # 添加一条记录 
insert into 表名 values(值1，值2)(值1，值2)(值1，值2)  # 添加多条记录 
insert into 表名 values (值1，值2)                     # 添加不指定字段名
alter
alter table 表名 rename 新表名                         # 修改表名       
alter table 表名 add 字段定义                          # 添加字段     
alter table 表名 drop 字段名                           # 删除字段     
alter table 表名 change 旧字段名 字段定义              # 修改字段     
alter table 表名 modify 字段名 新的字段类型            # 修改字段类型 
alter table 表名 alter column字段名 set default默认值; # 修改默认值

# 如果想在一个已经建好的表中添加一列，可以用诸如：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null;
# 这条语句会向已有的表中加入新的一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;
# 注意，上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话，可以用：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;

长度需要更改为100
alter table t_student modify student_name varchar(100) ; 

3.5 删除

delete
delete from your_table where id=3       # 删除id为3的记录
delete from your_table where 字段=值;   # 删除数据：   
drop
drop database your_database             # 删除your_database数据库   
drop table                              # 删除数据表   

3.6 导入与导出sql文件

mysql导入导出sql文件

as关键字可以省略
select empno,ename,sal * 12 yearsal from emp;

4. 常见字符串函数

lower(column|str) # 将字符串参数值转换为全小写字母后返回
upper(column|str) # 将字符串参数值转换为全大写字母后返回
concat(column|str1, column|str2,...) # 将多个字符串参数首尾相连后返回
concat_ws(separator,str1,str2,...) # 将多个字符串以给定的分隔符separator首尾相连后返回
substr(str,pos[,len]) # 从源字符串str中的指定位置pos开始取一个字串并返回
length(str) # 返回字符串的存储长度
char_length(str) # 返回字符串中的字符个数
instr(str, substr) # 从源字符串str中返回子串substr第一次出现的位置
lpad(str, len, padstr) # 在源串的左填充字符padstr到指定的长度len，返回填充后的字符串
rpad(str, len, padstr) # 在源串的右填充字符padstr到指定的长度len，返回填充后的字符串
trim([{both | leading | trailing} [remstr] from] str), trim([remstrfrom] str) # 从源字符串str中去掉两端、前缀或后缀字符remstr并返回；如果不指定remstr，则去掉str两端的空格；不指定both \ leading\trailing ，则默认为both。
replace(str, from_str, to_str) # 在源字符串str中form_str（大小写敏感）被to_str替换
ltrim(str)，rtrim(str) # 去掉字符串的左边或右边的空格(左对齐、右对齐)
repeat(str, count) # 将字符串str重复count次后返回
reverse(str) # 将字符串str反转后返回
char(N,... [using  charset_name]) # 将每个参数N解释为整数（字符的编码），并返回每个整数对应的字符所构成的字符串(NULL值被忽略)。
format(X,D[,locale]) # 以格式‘#,###,###.##’格式化数字X;D指定小数位数locale指定国家语
space(N) # 返回由N个空格构成的字符串
left(str, len) # 返回最左边的len长度的子串
right(str, len) # 返回最右边的len长度的子串
strcmp(expr1,expr2) # 若相等则返回0；如果expr1小于expr2则返回-1；否则返回1

5. 常见数学函数

abs(x) # 返回x的绝对值
pi() # 返回圆周率 
mod(x,y) # 返回余数。也适合带有小数部分的数据值，返回除法运算后的精确余数。
ceil(X) # 返回不小X的最小整数值，返回值转为一个BIGINT.
ceilint(X) # 同ceil(X)
floor(X) # 返回不大于X的最大整数值，返回值转为一个BIGINT.
rand(X) 返回一个随机浮点值，范围在0~1之间，X为整数，它被称作种子值，用来产生重复序列。即当Ｘ值相同时，产生的随机数也相同；
round(X)和round(X,Y) # 四舍五入函数，对Ｘ值按照Ｙ进行四舍五入，Ｙ可以省略，默认值为０;若Ｙ不为０，则保留小数点后面指定Ｙ位。
truncate(X,Y) # 与round(X,Y)功能类似，但不进行四舍五入，只进行截取。
sign(X) # 返回参数Ｘ的符号，Ｘ的值为负、零或正数时返回结果依次为－１，０或１
pow(X,Y)与power(X,Y) # 功能相同，用于返回Ｘ的Ｙ次乘方的结果值
log(X)和log10(X) # 对数运算函数（Ｘ必须为正数），log(X)-返回Ｘ的自然对数(X相对于基数e的对数)　LOG10(X)-返回x的基数为10的对数：
radians(X) 和 degrees(X) # 角度与弧度转换函数 

6. 常见日期函数

now()              # 获得当前日期+时间（date + time）函数
sysdate()          # 获得当前日期+时间（date + time）
current_timestamp()# 获得当前时间戳函数
date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');# 日期/时间转换为字符串函数
str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s')#（字符串转换为日期）函数
to_days('2008-08-08') #（日期、天数）转换函数
time_to_sec('01:00:05'); -- 3605 # 时间、秒）转换函数
makedate(2001,32); -- '2001-02-01'# 拼凑日期、时间函数
maketime(12,15,30); -- '12:15:30''# 拼凑日期、时间函数
unix_timestamp('2008-08-08'); -- 1218124800 #（Unix 时间戳、日期）转换函数
unix_timestamp(1218124800); -- '2008-08-08 00:00:00'
set @dt = now();
date_add(@dt, interval 1 hour); -- add 1 hour # 为日期增加一个时间间隔
date_add(@dt,interval '01:15:30' hour_second) # 2008-08-09 13:28:03
date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) # 为日期减去一个时间间隔
datediff('2008-08-08', '2008-08-01'); -- 7 # 日期、时间相减函数
timediff('08:08:08', '00:00:00'); -- 08:08:08 # 两个日期相减 time1 - time2，返回 time 差值
timestamp('2008-08-08'); -- 2008-08-08 00:00:00
timestamp('2008-08-08 08:00:00', '01:01:01');  # -- 2008-08-08 09:01:01
timestamp('2008-08-08 08:00:00', '10 01:01:01'); #  -- 2008-08-18 09:01:01
timestampadd(day, 1, '2008-08-08 08:00:00'); # -- 2008-08-09 08:00:00
date_add('2008-08-08 08:00:00', interval 1 day); # -- 2008-08-09 08:00:00
timestampdiff(year,'2002-05-01','2001-01-01'); # -- -1
timestampdiff(day ,'2002-05-01','2001-01-01'); # -- -485
timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); # -- -12
convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); # -- 2008-08-08 04:00:00
date_add('2008-08-08 12:00:00',interval -8 hour); # -- 2008-08-08 04:00:00
date_sub('2008-08-08 12:00:00',interval 8 hour); # -- 2008-08-08 04:00:00
timestampadd(hour, -8, '2008-08-08 12:00:00'); # -- 2008-08-08 04:00:00

7.批量插入数据，单次插入多少条数据效率最高

批量插入数据，单次插入多少条数据效率最高

8.索引

mysql索引 多个单列索引和联合索引的区别详解