Mysql笔记
上节课作业
1、shell脚本：bak_log.sh
src_path='/opt/lampp/logs/access_log'
tar_path='/tmp/logs/'
time=`date +%Y%m%d%H%M%S`
cp -rf ${src_path} ${tar_path}access_log_${time}
>${src_path}
2、定时任务：crontab -e
59 23 * * * sh /tmp/bak_log.sh
补充内容
VIM命令，批量注释，先按Ctrl+v，然后选中多少行，然后Shift+i，写你要添加的东西，按下Esc即可。
ls *.tmp #查询以.tmp结尾的所有文件
ls luo* #查询以luo开头的所有文件
ls *luo* #查询名字包含luo的所有文件
测试环境搭建
①.下载以来软件安装好；
②.获取源代码(打包，根据不同语言来看是否需要编译、打包)；
③.导入基础数据；
④.修改配置文件(数据库IP等修改)；
⑤.启动。
java (tomcat、weblogic、jetty、resin、ws) 需要编译、打包
php、ruby、python (nginx、apache) 不需要编译、打包
日常部署
①.获取最新的代码(编译、打包)；
如果有表结构变更的话，要执行SQL。
②.修改配置文件；
③.重新启动程序。
Mysql
Mysql是众多关系型数据库中的一种。
SQL（Structured Query Language）语言的全称是结构化查询语言。数据库管理系统通过SQL语言来管理数据库中的数据。
SQL语言分为三个部分：数据定义语言（Data Definition Language，简称为DDL）、数据操作语言（Data Manipulation Language，简称为DML）和数据控制语言（Data Control Language，简称为DCL）。
DDL语句：create、alter、drop；
DML语句：update、insert、delete、select；
DCL语句：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant，deny，revoke等）语句。
数据类型
数据类型是数据的一种属性，其可以决定数据的存储格式、有效范围和相应的限制。MySQL的数据类型包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型和二进制数据类型。
整数类型、浮点数类型和定点数类型
整数类型：tinyint、smallint、mediumint、int、bigint。
浮点数类型包括单精度浮点数（FLOAT型）、双精度浮点数（DOUBLE型）、定点数类型就是DECIMAL型。
日期与时间类型：date、time、year、datetime与timestamp。
date表示日期，默认格式为YYYY-MM-DD；
time表示时间，格式为HH:ii:ss；
year表示年份；
datetime与timestamp是日期和时间的混合类型，格式为YYYY-MM-DD HH:ii:ss。
字符串类型：CHAR、VARCHAR、BLOB、TEXT、ENUM和SET。
二进制类型：binary、varbinary、bit、blob(tinyblob、blob、mediumblob、longblob)。
mysql -uroot -pxxx #进入mysql命令行
/s #查看mysql服务器状态信息
/h #显示命令清单，帮助文件
set @autocommitt=0; #关闭自动提交设置(mysql默认自动提交)
select @autocommitt; #查询是否自动提交设置
数据库
查看
show databases; #查看所有数据库
创建
create database 数据库 charset 字符集; #创建数据库
删除
drop database 数据库; #删除数据库
选择
use 数据库; #选择数据库
show create database 数据库; #查看创建数据库语句
表
auto_increment #自增长(整数列默认自增1)
not null #非空约束
default 'xx' #默认值
unique #唯一约束
charset 字符集 #指定字符集
primary key #主键约束，具有唯一性，不能为空
foreign key #外键约束，用来表示两个表的关系，外键影响性能
增
例子：
create table students (
id int auto_increment primary key,
name varchar(10) not null,
sex varchar(2) default '男',
addr varchar(50),
phone int not null unique
);
create table score (
id int auto_increment primary key,
s_id int not null,
grade float,
constraint 'score' foreign key ('s_id') refernces 'students' ('id')
);
删
drop table 表名; #删除表
改
alter table 表名 rename 新表名; #修改表名
alter table 表名 modify 字段名 数据类型; #修改字段的数据类型
alter table 表名 change 字段名 新字段名 数据类型; #修改字段的数据类型和新字段名
alter table 表名 add 字段名 新字段名 数据类型 约束等; #表新加字段
first; #新增字段加在第一位(放在表新加字段后)
alter 字段名; #新增字段加在哪个字段后(放在表新加字段后)
alter table 表名 drop 字段; #表删除字段
查
show tables; #查看所有表
desc 表名; #查看表结构
show create table 表名; #查看创建表语句
数据
增
insert into 表名 (字段1,字段2) values ('字段1值','字段2值') #指定字段，插入值与字段一一对应
insert into 表名 (字段1,字段2) values ('字段1值','字段2值'),('字段1值','字段2值') #指定字段，插入值与字段一一对应，插入多条数据
insert into 表名 values ('字段1值','字段2值') #不指定字段，插入值与表所有字段一一对应
删
truncate 表名; #清空整个表数据，物理删除，之前的约束失效，且无法恢复数据
delete from 表名; #删除整个表数据，逻辑删除，设置不自动提交，可以rollback恢复数据
truncate清空表数据，再插入表数据会自增长的字段会重新开始，delete删除表数据，再插入表数据会自增长的字段会继续增长。
改
update 表名 set 字段=字段值 where 条件; #修改指定的数据
update 表名 set 字段1=字段1值,字段2=字段2值 where 条件; #修改多个字段
upadte 表名 set 字段=字段+值 #在原来值的基础上做修改(例：upadte 表名 set money=money+100;)
查
select 字段1,字段2,字段3 from 表名; #查询指定字段
select * from 表名; #查询所有字段
select * from 表名 where 条件; #查询多条数据，指定条件满足
select * from 表名 where 条件1 and 条件2; #查询多条数据，多个条件必须同时满足
select * from 表名 where 条件1 or 条件2; #查询多条数据，多个条件只要有一个满足即可
select * from 表名 where 字段 like '%模糊值%'; #模糊查询，%代表的是通配符，用通配符必须使用like
select * from 表名 (as) 别名 where 别名.字段名=字段值; #给表起别名，as可以省略
select 别名.字段1 (as) 字段1别名 from 表名 (as) 别名 where 别名.字段名=字段值; #给列起别名，as可以省略
select * from 表名 (as) 别名 where 别名.字段名 in ('字段值1','字段值2'); #判断字段值是否在指定的集合中
select * from 表名 (as) 别名 where 别名.字段名 between 字段值1 and 字段值2; #判断是否在字段值1到字段值2之间(必须是数值范围大小)
select * from 表名 (as) 别名 where 别名.字段名='' or 别名.字段名 is null; #查询指定字段值为空的数据
select distinct 别名.字段名 from 表名 (as) 别名 ; #去除重复字段，只能对单个字段去重
select * from 表名 order by 字段名 desc; #根据字段desc降序，默认asc升序，order by放在最后
select 字段名,count(*) from 表名 group by 字段名; #分组，有几组数据就显示几条
select 字段名1,字段名2,count(*) from 表名 group by 字段名1,字段名2; #多个字段分组，有几组数据就显示几条
如果group by后面有条件的话，用having子句，并且having子句里的字段必须要出现在select后面，查询该字段；如果group by和order by一起用，order by必须写在group by 后面。
多表查询：多表查询是指从多张表中查询所需要的数据，一般查询的这几张表都有一个相同的字段关联这几张表。
select * from 表1,表2 where 表1.字段名 = 表2.字段名 and 条件; #查询多表满足条件的数据，两个表都存在的数据
多表连接可以通过join关键字来连接，也可以直接用关联表中相同的id来进行关联；
Join：
left join：左连接， 连接两张表，以左边表的数据匹配右边表中的数据，如果左边表中的数据在右边表中没有，会显示左边表中的数据。
select * from 表1 left join 表2 on 表1.字段名 = 表2.字段名; #左边表中所有数据，右边表匹配得到就查询出来
right join：右连接，连接两张表，以右边表的数据匹配左边表中的数据，如果左边表中的数据在左边边表中没有，会显示右边表中的数据。
select * from 表1 right join 表2 on 表1.字段名 = 表2.字段名; #右边表中所有数据，左边表匹配得到就查询出来
inner join：内连接，连接两张表，匹配两张表中的数据，和前面两个不同的是只会显示匹配的数据。
select * from 表1 inner join 表2 on 表1.字段名 = 表2.字段名; #查询两边表都匹配到的数据，两个表都存在的数据
select * from 表名1 where 表名1.字段名 = (select 表名2.字段名 from 表名2 where 条件); #子查询：把一条sql的结果作为另一条sql的条件
子查询的结果可以当成表用。
select * from 表名 limit 行数; #查询要求的行数数据，从0开始
例：
select * from 表名 limit 1,5; #限定查询数量，从第二条开始，查询5条数据
select * from 表名 limit 5; #查询5条数据
select 字段列表1 from 表名1 union select 字段列表2 from 表名2 #合并两条select语句结果，字段数量和数据类型要一致
uninon和union all的区别：union会在结果中去重，而union all不会去重；但是union all 的效率比union的效率高。
!= 和 <> #不等于
_ #_匹配的是任意单个字符，在条件里使用
% #%匹配的是任意字符，在条件里使用
聚合函数
select count(*) 列别名 from 表名; #统计行数，可以加where条件
select max(表别名.字段名) 列别名 from 表名 (as) 表别名; #查询某个字段的最大值
select min(表别名.字段名) 列别名 from 表名 (as) 表别名; #查询某个字段的最小值
select avg(表别名.字段名) 列别名 from 表名 (as) 表别名; #查询某个字段的平均值
select sum(表别名.字段名) 列别名 from 表名 (as) 表别名; #查询某个字段的值总和
备份数据库
mysqldump -u用户名 -p密码 #linux命令(mysql自带的命令)，备份数据库
mysqldump -u用户名 -p密码 数据库 > 文件名.sql #备份指定数据库
mysqldump -u用户名 -p密码 -A > 文件名.sql #备份所有数据库的所有表
备份的shell脚本
date=`date +%Y%m%d`
mysqldump -u用户名 -p密码 -A > /tmp/bak_sql/${time}_all.sql
恢复数据：先创建数据库，然后再恢复数据库
mysql -u用户名 -p密码 数据库 < 文件名.sql #linux命令(mysql自带的命令)，恢复数据库
既可以恢复数据库，也可以批量执行SQL
存储过程：批量的造数据
模板：
delimiter $$;
CREATE PROCEDURE 名称(参数列表)
begin
SQL语句块
end
$$;
delimiter;
例子1：
delimiter $$; #结束符改成$$
CREATE PROCEDURE big_data1(num int) #创建存储过程，num代表要造多少条数据
begin
declare i int; #定义一个计数器
set i=0; #从0开始计数
while i<num do #只要i小于num的时候，一直执行插入语句
insert into students (stu_name,money) values ('suan',20000); #插入数据
set i=i+1; #每执行一次+1
end while; #结束循环
end
$$;
Delimiter; #结束符改回成;
–Call big_data;
例子2：
delimiter $$; #结束符改成$$
CREATE PROCEDURE big_data2(num int) #创建存储过程，num代表要造多少条数据
begin
declare i int; #定义一个计数器
set i=0; #从0开始计数
while i<num do #只要i小于num的时候，一直执行插入语句
insert into students (stu_name,money) values (concat ('张三',i),20000); #插入数据，concat连接不同类型的数据，把字符串和数字拼接到一起
set i=i+1; #每执行一次+1
end while; #结束循环
end
$$;
Delimiter; #结束符改回成;
–Call big_data;
call 存储过程名(数量); #调用存储过程
数据库权限：授权就是为某个用户赋予某些权限。
update user set passwrd=password('密码') where user='账号'; #修改数据库密码，密文
例如，可以为新建的用户赋予查询所有数据库和表的权限。合理的授权能够保证数据库的安全。不合理的授权会使数据库存在安全隐患。
MySQL中使用GRANT关键字来为用户设置权限。
Grant 命令的格式
grant 权限 on 数据库对象 to 用户
比如说增加一个超级用户，拥有所有的权限，只允许本地登录
Grant all on *.* to 'andashu'@'localhost' IDENTIFIED BY '123456‘with grant option;
增加一个普通用户，只有对bugfree数据库查询的修改的权限，允许远程登录
Grant select, insert,update on bugfree.* to 'tester'@'%' IDENTIFIED BY '123456';
增加一个超级用户，限制登录ip为192.168.1.101
Grant all privileges on *.* to dba@‘192.168.1.101' IDENTIFIED BY '123456';
增加一个只有查询bugfree的bug表中的bugtitle的用户
Grant select(title) on bugfree.bf_bug_info to 'bugfree'@'%' IDENTIFIED BY '123456';
取消权限：用revoke 关键字
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可
Revoke 权限 on 数据库对象 from 用户
比如要取消andashu用户的超级权限
Revoke all on *.* from andashu@localhost;
取消dba用户查询权限
Revoke select on *.* from dba@localhost;
修改user表的数据
flush privileges; #刷新数据
添加%的一条数据后，除本机(localhost)所有IP都可以远程连接。
