# hostnamectl --static set-hostname mysql   //修改主机名
# exit
?
# cd /tmp
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# yum install mysql80-community-release-el7-3.noarch.rpm
# ls /etc/yum.repos.d/
# yum repolist      //刷新yum源
# yum repolist all |grep mysql      //默认开启mysql80
?
# yum -y install yum-utils      //yum管理包
# yum-config-manager --disable mysql80-community
# yum repolist all |grep mysql
# yum-config-manager --enable mysql57-community
# yum repolist enabled |grep mysql      //只允许开启一个版本

# vim /etc/yum.repos.d/mysql-community.repo
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# yum -y install mysql-community-server mysql
# systemctl start mysqld
# ls /var/lib/mysql         //查看mysql安装目录
# systemctl status mysqld
# systemctl enable mysqld
# grep ‘tqfeduorary password‘ /var/log/mysqld.log
xxxxxx
?
# mysql -uroot -p‘xxxxxx‘
> show database;
需先修改密码
> alter user ‘root‘@‘localhost‘ identified by ‘A.123com‘;
> show database;
> grant select,drop,insert on *.* to ‘zhangsan‘@‘localhost‘ identified by ‘A.123com‘;
> select user,host from mysql.user;
> flush privileges;     //刷新权限
> show grants for ‘zhangsan‘@‘localhost‘;   //查看用户权限

# mysql -uroot -p‘xxxxxx‘
> use mysql;
> show tables;
?
> create database db1;
> drop database db1;        //删库
?
> create database db1 default character set utf8;
> use db1;
?
> create table t1(id int(3),name varchar(20));
> show tables;
?
> insert into t1 values(1,‘user1‘);
> select * from t1;
> desc t1;      //查看表结构
?
> alter table t1 add(age int(3));       //添加字段
> alter table t1 add(age int(3)) first; //字段添加到前面
> desc t1;
> insert into t1 values(2,‘user2‘,10);
?
?
> alter table t1 drop id;           //删除表字段
> alter table t1 modify age varchar(2);     //修改表字段类型格式
> alter table t1 change age plage int(3);   //修改字段名称
> alter table t1 rename per;            //修改表名
> truncate table t1;        //清空表结构
> drop table t1;            //删除表结构

> create table t1(id int,name varchar(20),birth data,address varchar(50));
> show tables;
> desc t1;
?
//插入数据
> insert into t1 values(1,‘user1‘,null,‘shanghai‘);
> insert into t1(id,name,address) values(2,‘user2‘,‘shanghai‘);
> insert into t1(id,name,address) values(3,‘user3‘,‘shanghai‘),(4,‘user4‘,‘guangzhou‘),(5,‘user5‘,‘beijing‘);
?
//删除数据
> delete from t1 where id=3;        
> delete from t1 where id=3 and address=shenzheng;
> delete from t1 where id=3 or address=shenzheng;
> delete from t1;
?
//修改数据
> insert into t1(id,name,address) values(3,‘user3‘,‘shanghai‘),(4,‘user4‘,‘guangzhou‘),(5,‘user5‘,‘beijing‘);
> update t1 set address=‘china‘;    
> update t1 set address=‘shanghai‘,name=‘lisi‘ where id=3;
> update t1 set name=‘zhangsan‘ where birth is null;

//创建表
> create table t1(id int,name varchar(10),job varchar(10),mgr int(4),hiredate DATE,sal int(7),comm int(7),deptid int(2));
> insert into t1(id,name,job,mgr,hiredate,sal,comm,deptid) values
(0001,‘zhangsan‘,"xxx",1234,‘1980-12-12‘,800,NULL,1),
(0002,‘lisi‘,"xxx",1235,‘1981-2-1‘,1800,100,1),
(0003,‘wangwu‘,"xxx",1236,‘1982-4-5‘,1200,NULL,1),
(0004,‘zhaoliu‘,"xxx",1234,‘1989-2-2‘,800,NULL,1)
> select name,job,sal,deptid from t1;
?
//查询
> select name as ‘mingzi‘,job as ‘zhiwei‘ from t1;
> select id,name,job,sal,deptid from t1 where deptid=10 or deptid=20;
> select * from t1 where deptid<>10 and deptid=20;
> select * from t1 where deptid in(10,20);
> select * from t1 where deptid not in(10,20);
?
//All|Any与集合连用
?
//查询大于这三名员工sal的信息
> select * from t1 where sal>all(select  sal from t1 where name in(‘zhangsan‘,‘lisi‘,‘wangwu‘));
//查询工资大于等于1500且小于等于2500的员工
> select * from t1 where sal between 1500 and 2500;
> select * from t1 where sal not between 2000 and 2500;
?
//like模糊搜索
> select * from t1 where name like ‘%a%‘ and name like ‘%s%‘;
> select * from t1 where name like ‘%a%s%‘ and name like ‘%s%a%‘;

//排序查询order by子句
> select * from t1 order by sal DESC;       //倒序
> select * from t1 order by sal ASC;        //升序，默认

> insert into t1(id,name,job,mgr,hiredate,sal,comm,deptid) values(0005,‘zhangsan‘,"xxx",1234,‘1980-12-12‘,800,NULL,1);
> select distinct name from t1;     //去重

//分组查询，select子句中的字段，除了聚合函数外，只能写分组字段。
> select deptid,avg(sal) as av from t1 group by deptid;

//分组查询添加条件Having子句
> select deptid,avg(sal) as av from t1 group by deptid having avg(sal)>2000;

select..from..[where..][group by][having..][order by..]

//avg(ifnull(sal,0))，如果为空，使用指定的值，
> select deptid,avg(ifnull(sal,0)) as av from t1 group by deptid;   //赋值给b
//查询表中各部门人员中大于部门平均工资的人
> select name,sal,a.deptid,b.av from t1 a,(select deptid,avg(ifnull(sal,0)) as av from t1 group by deptid) b where a.deptid=b.deptid and a.sal>b.av order by deptid ASC;

> create user username@IP identified by newPwd;
> create user ‘zhangsan‘@‘192.168.0.2‘ identified by ‘A.123com‘;
> create user ‘zhangsan‘@‘192.168.0.%‘ identified by ‘A.123com‘;

> select user,host from mysql.user;
> show grants for username@ip;

> create database test;
> use test;
> create table t1(id int,name varchar(20));
> grant select,drop,insert on test.* to ‘zhangsan‘@‘192.168.0.2‘;
> \q
# mysql -h192.168.0.2 -uzhangsan -p‘A.123com‘
> show databases;

> drop user ‘zhangsan‘@‘192.168.0.2‘;
> flush privileges;
> select user,host from mysql.user;

> use test;
?
> create table t1(num int auto_increment primary key,
name varchar(10),
job char(10),
age int,
salary int,
descrip char(128)not null default ‘‘
)charset=utf8;
?
> insert into t1(name,job,age,salary,descrip) values
(‘zhangsan‘,‘teacher‘,30,10000,‘level2‘),
(‘lisi‘,‘teacher‘,20,10000,‘level2‘),
(‘wangwu‘,‘teacher‘,29,10000,‘level3‘),
(‘zhangsan‘,‘teacher‘,25,10000,‘level4‘);

> create table t2(id int,age int)charset=utf8;
> alter table t2 add name varchar(20) not null default ‘‘;      //添加字段
> alter table t2 add name3 varchar(20) not null default ‘‘ first;       //字段添加到前面
> alter table t2 add name4 varchar(20) not null default ‘‘ after id;    //添加到指定字段后面
?
//删除字段
> alter table t2 drop name4;
?
//修改字段类型
> alter table t2 modify name char(20);
?
//修改字段名
> alter table t2 change name name2 varchar(20) not null default ‘‘;

//删除表
> drop table t2;
> show tables;
?
//复制表结构（t1新表）
> create table t1 like t2;

//插入数据
> insert into t2(id,name) values(1,‘zhangsan‘);
//
> insert into t1(name) select name from t2;
?
//删除数据
> delete from t2 where id=1;
> truncate
?
//修改数据
> update t2 set name=‘fugui‘ where id=1;
?
//查询,between..and..:取值范围是闭区间
> select * from t1;
> select * from t1 where id between 2 and 3;
?
//DISTINCT
> insert into t2(id,name) values(1,‘zhangsan‘);
> select distinct name from t1;
?
//四则运算查询(不建议用)
> alter table t2 add age int;
> insert into t2(id,name) values(2,‘lisi‘);
> select name,age*10 as age from t2;
?
//查询
> select * from t1 where id in (3,5,6);
> select * from t1 where name like ‘q%‘;
?
//查询用name is null作为条件
> create table t3(id int auto_increment primary key,name varchar(20),email varchar(20))charset=utf8;
> insert into t3(email) values(‘123‘);
> select * from t3 where name is null;

> create table t1(id int auto_increment primary key,name varchar(32) not null default ‘‘)charset=utf8;
> insert into t1(name) values(‘lisi‘),(‘zhangsan‘),(‘wangwu‘);
?
//t2表(dep_id)关联t1(id)
> create table t2(
id int auto_increment primary key,
name varchar(32) not null default ‘‘,
depart_id int not null default 1,
constaraint fk_user_depart foreign key (depart_id) references t1(id)) charset=utf8;
> insert into t2(name,depart_id) values(‘lisi1‘,1),(‘zhangsan2‘,2),(‘zhangsan3‘,3);
?
> insert into t2(name,depart_id) values(‘zhangsan4‘,4);     //插入失败，主表只有id在1-3，depart_id只能在1-3内,t2表中的id和t1的id没关联
?
> insert into t2(name,depart_id) values(‘zhangsan4‘,1);

//男生表
> create table boy(id int auto_increment primary key,bname varchar(32) not null default ‘‘)charset=utf8;
> insert into boy(bname) values(‘a‘),(‘b‘),(‘c‘);
?
//女生表
> create table girl(id int auto_increment primary key,bname varchar(32) not null default ‘‘)charset=utf8;
> insert into girl(gname) values(‘d‘),(‘e‘),(‘f‘);
?
//创建关联表
> create table b2g(id int auto_increment primary key,
bid int not null defautl 1,
gid int not null default 0,
constraint fk_b2g_boy foreign key (bid) references boy(id),
constraint fk_b2g_girl foreign key (gid) references girl(id))charset utf8;
?
> insert into b2g(bid,gid) values(1,1),(1,2),(2,3),(3,3),(2,2);
> select * from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;
> select bname,gname from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;

//创建员工信息表
> create table user(id int auto_increment primary key,name varchar(32) not null default ‘‘)charset=utf8;
> insert into user(name) values(‘a‘),(‘b‘),(‘c‘);
?
//创建员工工资表
> create table priv(
id int auto_increment primary key,
salary int not null default 0,
uid int not null default 1,
constraint fk_priv_user foreign key (uid) references user(id),unique(uid))charset=utf8;     //unique(uid)唯一约束
> insert into priv(salary,uid) values(2000,1),(2500,2),(3000,3);

> select t2.name as uname,t1.name as dname from t2 left join t1 on depart_id=t1.id;

> create table t1(id int,name varchar(32));
> insert into t1 values(1,‘zhangsan‘);
?
//备份
# mysqldump -u root -p‘A.123com‘ --all-databases > /data/mysql.sql
# mysqldump -u root -p‘A.123com‘ --databases test > /data/mysql2.sql
# mysqldump -u root -p‘A.123com‘ test t1 > /data/mysql3.sql
?
//数据库内导入
# mysql -uroot -p‘A.123com‘
> drop table t1;
//导入数据
> source /data/mysql3.sql
?
//命令行导入
> drop databases test;
# mysql -uroot -p‘A.123com‘ < /data/mysql2.sql

> create table test_tinyint(num tinyint) engine=innodb charset=utf8;
> insert into test_tinyint1 values(127);
?
//unsigned 不区分符号
> create table test_tinyint(num tinyint unsigned) engine=innodb charset=utf8;
> insert into test_tinyint1 values(255);

> create table test_int_width(
a int(4),
b int(5) unsigned,
c int(5) unsigned zerofill,
d int(5) unsigned zerofill) engine=innodb charset=utf8;
> insert into test_int_width values(1,1,1,11111111);
> select * from test_int_width;
1   1   00001   11111111

> create table test_float(num float(5,2)) engine=innodb charset=utf8;
> insert into test_float values(1.2345);
> insert into test_float values(1000,123);
> select * from test_float;

> create table test_decimal(float_num float(10,2),double_num double(20,2),decimal_num decimal(20,2)) engine=innodb charset=utf8;
> insert into test_decimal values(1234512.111,12345678912345678.66,123456789123456789.22)
1234512.12  12345678912345678.00    123456789123456789.22

> create table test_varchar(varchar_value varchar(21844))) engine=innodb charset=utf8;
> create table test_varchar(varchar_value varchar(32761))) engine=innodb charset=utf8mb4;

> create table test_time(
date_value date,
time_value time,
year_value year,
datetime_value datetime,
timestamp_value timestamp) engine=innodb charset=utf8;
> insert into test_time values(now(),now(),now(),now(),now());
> select * from test_time;

> create table test_enum(id int,gender enum(‘male‘,‘female‘)) charset=utf8;
> insert into test_enum(id,gender) values(1,‘male‘);
> insert into test_enum(id,gender) values(2,‘female‘);

//约束username不能为空
> create table test_null(id int,username varchar(20) not null) charset=utf8;
> insert into test_null values(1,‘zhangsan‘)
?
//添加唯一约束
> alter table test_null add unique(id);
?
//主键
> create table user(id int primary key,name varchar(20),number int) charset=utf8;
?
//自增
> alter table user change id id int not null auto_increment;    //新增自增，主键任存在
?
//删除自增长
> alter table user change id id int not null;
?
//修改默认值
> alter table user alter number set default 0;
> desc user;
> insert into user values(1,‘lisi‘);

> show engines;

# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
# systemctl restart mysqld
> create table teacher(id int primary key auto_increment,name varchar(20) not null) charset=utf8;
?
> create table student(id int primary key auto_increment,name varchar(20) not null,teacher_id int,foreign key (teacher_id) references teacher(id)) charset=utf8;
?
> insert into teacher(id,name) values(1,‘zhangsan‘);
> insert into student(id,name,teacher_id) values(1,‘zhangsan‘,1);

//创建表添加索引
> create table student(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,index sindex (name(50)));
//查看索引
> show index from student\G
?
//基于表结构创建索引
> create index tindex on strdent(id);
?
//修改表结构创建索引
> alter table student add index xindex(name(50));

//创建表加唯一索引
> create table student3(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,unique idname(id));
> show index from student3\G
?
//复制表结构(不会复制索引)
> create table student4 like student3;
> desc student4;
?
//添加唯一索引
> create unique index idindex on student4(id);

> create table student5(id int not null,name varchar(50) not null,birthday date,sex char(1) not null,primary key (id));
> show index from student5\G
?
> create table student6 like student3;
> alter table student6 add primary key (id);
> show index from student6\G
?
//删除索引
> alter table student drop primary key;

> drop index idindex on student;
> alter table student drop index idindex;