Mysql 快速回顾

Linux命令：

--1、设置临时ip:
ifconfig eth0 192.168.10.1

--2、mysql登录命令：
/usr/local/mysql/bin/mysql -uroot -p123 databaseName;
后面可以直接指定数据库databaseName

--3、关闭mysql程序：
pkill mysql --mysql是服务名，要看/etc/init.d下服务名是mysql还是mysqld
--也可以:
kill -9 进程号pid

!ps  --查询最后一次ps的语句
ps -ef|grep tomcat-manage 

Linux数据库开启远程连接：

service iptables status   可以查看到iptables服务的当前状态。
1) 重启后生效
开启： chkconfig iptables on
关闭： chkconfig iptables off
2) 即时生效，重启后失效
开启： service iptables start
关闭： service iptables stop

防火墙添加3306的端口号，这样，远程服务器才能连上数据库

[root@centos6]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

两种方法使防火墙生效：

[root@centos6]# /etc/init.d/iptables restart
[root@centos6]# service iptables restart

创建用户并授权：

mysql> create user 'dhh'@'%' identified by '123456';  --设置用户dhh在任何ip都能访问
Query OK, 0 rows affected (0.07 sec)

mysql> grant all on *.* to 'dhh';
Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges;

这样，就可以用dhh账号远程登录了

mysql数据库字符集：

mysql>\s

Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8

# > vi  /etc/my.cnf
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

--重启mysql
#> pkill mysql
#> service start mysql

--显示全部编码字符集：
mysql>show character set;

Mysql命令：

查看当前使用什么数据库

MySQL [mysql]> select database();

查看所有数据库：

MySQL [mysql]> show databases;

查看账户信息：

MySQL [mysql]> select user,host,authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| root      | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+

查看授权：

MySQL [mysql]> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
show grants for 'dhh'@'192.168.10.2';

myssql详细信息显示：

MySQL [mysql]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:		4
Current database:	mysql
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MySQL
Server version:		5.7.17 Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			26 min 34 sec

mysql创建用户并授权：

创建用户:

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

说明:
username - 你将创建的用户名, 
host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. 
password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器. 

授权:

GRANT privileges ON databasename.tablename TO 'username'@'host' 

说明: 
privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等.如果要授予所的权限则使用ALL.;
databasename - 数据库名,
tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*

例子: 

GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; 
GRANT ALL ON *.* TO 'pig'@'%'; 

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令: 

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; 

最后注意刷新权限：

mysql>flush privileges;

mysql密码验证安全策略：
自己用于测试的数据库，安全性要求没那么严格，linux mysql5.7默认要求8位，而且还
必须包含大小写字母，数字和特殊符号

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

现在把安全策略调低，最低位4位密码：

mysql> set global validate_password_policy = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 4     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

MySql语法：

复制表结构+复制表数据：

mysql>create table t2 like t1;
mysql>insert into t3 select * from t1;  --字段名不一样需要指定字段名

mysql索引：

1、alter table用来创建普通索引，UNIQUE索引，PRIMARY KEY索引

alter table table_name add index index_name(column_list);
alter table table_name add unique(column_list);
alter table table_name add primary key(column_list);

alter table table_name drop index index_name;
alter table table_name drop primary key;  --如果字段是auto_increment，就不能删除主键索引

2、create index创建索引。不能创建primary key索引

create index index_name on table_name(colomn_list);
create unique index index_name on table_name(column_list);

drop index index_name on table_name;

查看索引：

show index from 表名;

总结：alter语法可以创建3种类型的索引，create index只能创建普通索引，和unique索引，主键索引不能创建

mysql视图：
创建：

mysql>create view v_t1 as select * from t1 where id >4 and id<10;

查看视图：

show tables;  --视图和表一起显示，视图名一般加个v_ ,这就是很多表设计命名用t_开头，用来区分表和视图

删除：

mysql>drop view v_t1;

视图场景：用于使用非常多的查询语句，筛选条件都一样，可以考虑视图，比如热门榜单的前十名。
视图特点：视图的数据会动态维护，即主表数据改了，视图的数据也会改，这和表的复制效果不同
如果主表被删除了，查询视图会报错，主表又恢复了，视图也就恢复了

mysql预处理语句：
设置per_sql预处理

mysql>prepare per_sql from 'select * from t1 where id>?';
--设置变量
mysql>set @i=1;
--执行预处理语句
mysql>execute per_sql using @i;

--再执行：
mysql>set @i=5;
mysql>execute per_sql using @i;

--删除预处理语句：
drop prepare per_sql;

总结：java中的PreparedStatement就是根据这个来的ps.setString(),ps.setInteger()

mysql事务(innodb存储引擎)：

--关闭自动提交
mysql>set autocommit =0;
--删除记录
delete from t1 where id= 11;
--做还原点
mysql>savepoint p1;
--删除记录
delete from t1 where id =12;
--做第二个还原点
mysql>savepoint p2;
--此时回复到p1,当然后面的p2这些还原点会自动失效
mysql>rollback to p1;
--退回最原始的还原点
mysql>rollback;

注意点：如果这时表的存储引擎用的是myisam，这些操作没有用，因为myisam不支持事务操作
修改存储引擎：

alter table t1 engine=innodb;

查看是否是数据库自动提交

mysql> select @@autocommit;

mysql存储过程

MySQL [test]> create procedure p1()
    -> begin
    -> set @i = 0;
    -> while @i<10 do
    -> select @i;
    -> set @i = @i + 1;
    -> end while ;
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

MySQL [test]> \d;
MySQL [test]> 
MySQL [test]> call p1();
+------+
| @i   |
+------+
|    0 |
+------+
1 row in set (0.02 sec)

+------+
| @i   |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

查看创建：

mysql>show create procedure p1\G;

mysql触发器：

--new 表里没有的数据，新的数据
--old 表里原来的数据
mysql>\d //
mysql>create trigger tg2 before delete on t1 for each row
>begin
>delete from t2 where id=old.id;
>end//
mysql>\d;


mysql>\d //
mysql>create tg3 before update on t1 for each row
>begin
>update t2 set name =new.name where name=old.name;
>end //
mysql>\d;

--查看：
mysql>show triggers;

mysql正则表达式：
正则表达式效率不高，耗CPU，不建议过多使用。

mysql>select name,email from t where email regexp "@163[,.]com$";
==
mysql>select name,email from t where email like "%@163.com" or email like "%@163,com";

mysql常用内置函数：

字符串函数：

concat("string2" [,.....])  -- 链接字符串
lcase(string)		    -- 转小写
ucase(string)		    -- 转大写
length(string)		    -- string长度
ltrim(string)		    -- 去除前端空格
rtrim(string)
repeat(string,count)	    -- 重复输入string，count次
substring(str,pos [length]) -- 从str的pos开始，取length个字符,1开始
space(count)		    -- 生成count个空格

数学函数：

bin(decimal_number)	    -- 十进制转二进制
ceiling(number)		    -- 向上取整
floor(number)
max(num1,num2)
min(num1,num2)
sqrt(number)		    -- 开平方
rand()			    -- 返回0~1内的随机数

rand()妙用：打乱表格顺序

select * from t1 order by rand();

日期函数：

curdate()		    -- 返回当前日期
curtime()		    -- 返回当前时间
now()
unix_timestamp(date)	    -- 返回当前date的UNIX时间戳
from_unixtime()		    -- 返回UNIX时间戳的date
week(date)		    -- 返回date日期为一年中的第几周
year(date)		    -- 返回date的年份
datediff(expr,expr2)        -- 返回起始时间expr和结束时间pxpr之间的天数

select 用于输入，相当于linux的echo,java的syso()

mysql>select now();
MySQL [test]> select datediff('2017-02-01','2017-03-01');
+-------------------------------------+
| datediff('2017-02-01','2017-03-01') |
+-------------------------------------+
|                                 -28 |
+-------------------------------------+

小技巧：

1、mysql的help功能：用mysql提供的？来获得语法

mysql> ? view
mysql> ? create view
mysql> ? % 获取mysql>里的命令
mysql> ? opti%  不记得optimize全称，可以用%来替代
mysql> ? reg%   不记得regexp用法
mysql> ? contents 可以看到帮助大纲，通过这个目录再用?继续往下细查

2、巧用RAND()提取随机行：
mysql数据库中有一个随机函数rand()是获取0~1之间的数，利用这个函数一起和order by 能够把数据随机排序

--打乱表格顺序
mysql>select * from t1 order by rand();

--随机取3条样本：
mysql>select * from stu order by rand() limit 3;

3、select输出：

相当于linux的echo,java的syso()

mysql>select now();
mysql>select datediff('2017-03-15', '2016-03-15');  --输出负数

mysql>set @i=1;
mysql>select @@i=1;  --输出设置的变量

4、删除数据：

--delete一行一行删，执行慢
mysql>delete from t1;

--truncate删除数据，格式化表，auto_increment都变成1
mysql>truncate t1;

--重置auto_increment
alter tablename auto_increment =1;

group by的with rollup的用法：

使用group by 的with rollup子句可以检索出更多的分组聚合信息,不能和order by一起使用

cname   pname
bj	hd
bj	hd
bj	hd
bj	xc
bj	xc
sh	dh
sh	dh
sh	dh
sh	rg

mysql>select cname,pname,count(*) from t group by cname,pname;
bj	hd	3
bj	xc	2
sh	dh	3
sh	rg	1

mysql>select cname,pname,count(*) from t group by cname,pname with rollup;
bj	hd	3
bj	xc	2
bj	null	5
sh	dh	3
sh	rg	1
sh	null	4
null	null	9