SQL命令

第一步、下载Mysql安装包

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

安装mysql源：

yum -y localinstall mysql57-community-release-el7-11.noarch.rpm 

第二步、在线安装mysql

yum -y install mysql-community-server

第三步、启动mysql服务

systemctl start mysqld

第四步、设置开机自启动

systemctl enable mysqld
 
systemctl daemon-reload

第五步、修改root密码

mysql安装完成之后，会在/var/log/mysqld.log文件中给root生成了一个临时的默认密码。

vim /var/log/mysqld.log

进入musql修改root密码

第六步、防火墙开放3306端口

 cd /etc/sysconfig/
 vim iptables
#添加代码如下
-A INPUT -p tcp --dport 3306 -j ACCEPT

重启防火墙：

service iptables  restart

Mysql设置权限

进入数据库

use mysql;

创建用户

create user 用户名 identified by '密码';

设置权限

grant all privileges on 数据库名.* to 用户名;

刷新权限

flush privileges;

更改用户密码

ALTER USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql的密码';

MySQL创建用户

1.创建新用户

//　创建新用户，用户名为testuser，密码为123456 ；
grant all privileges on *.* to testuser@localhost identified by “123456” ;

设置用户远程访问权限

grant all privileges on *.* to testuser@'%' identified by “123456” ;

左连接

select FirstName, LastName, City, State from Persion Left join Address Persion.PersionId = Address.PersionId;

读取第二高数据

select ifnull((select distinct Salary from Employee order by Salary 
 desc limit 1,1),null) as SecondHighestSalary;

distinct 返回唯一不同的值
desc 逆序
limit 1,1 = limit 1 offset 1；跳过一条记录读取一条记录

排名

select Score ,(select count(distinct Score) from Scores where )

排名

select a.Score as Score,(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'Rank' from Scores a order by a.Score DESC;

ct b.Score) from Scores b where b.Score >= a.Score) as ‘Rank’ from Scores a order by a.Score DESC;