1.安装部署:
1.1 上传mysql 安装包
1.2 1.解压 tar -xvf ./mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
2.卸载 mariadb 相关的东西
rpm -qa | grep mariadb
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
3.安装配置文件
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
4.有一个 配置文件 管控mysql
mysql运行日志文件:log-error=/var/log/mysqld.log
5.初始化mysql
mysqld --initialize --user=mysql
6.启动mysql
systemctl start mysqld
7.登录mysql 使用
mysql -uroot -p密码
8.修改mysql 软件 root用户 密码
set password = password('123456');
9.赋予权限
flush privileges;
1.3卸载
1.mysql 停掉
systemctl stop mysqldmysql
2.mysql卸载
rpm -qa | grep mysql
rpm -qa | grep mysql | xargs -n1 rpm -e --nodeps
3.linux存储目录删掉
find / -name "*mysql*
4.重装
mysql 全部卸载重装
2.MySQL
2.1查看数据库
show databases;
2.2切换数据库
use mysql;
2.3查看库下面有哪些表
show tables;
2.4查看表中数据
select host,user from user
5.修改表中数据
update mysql.user set host="%" where user="root";
6.刷新权限
flush privileges;
3.数据库
3.1查看数据库
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
{} 必选参数 | 可选 [] 可选
SHOW DATABASES;
mysql> SHOW DATABASES;
3.2切换数据库
use dbname;
3.3查看数据库下面的表
SHOW [FULL] TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TABLES;
mysql> show tables FROM sys;
3.4创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
CREATE DATABASE bigdata;
3.5创建表
mysql> CREATE TABLE user_table(
-> id int(3),
-> name varchar(10),
-> age int(3)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
3.6表中插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT into user_table (id,name,age)VALUES (1,'zhangsan',10);
3.7查看数据
select * from user_table;
select name,age from user_table;
4.查看表中描述 desc tablename;
5.创建表 create table 表名(字段);
建表规范:1.表名字 一定是英文
2.主键 使用表中第一个字段使用自增主键
create table user_info2(
id int(3) not null auto_increment,
name varchar(10) COMMENT '用户名称',
age int(3),
create_user varchar(10),
create_time timestamp not null default current_timestamp,
update_user varchar(10),
update_time timestamp not null default current_timestamp on update current_timestamp
,primary key(id)
);
6.插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
insert into user_info (name,age) VALUES('zs',18),('ls',20);
7.更新数据
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
update user_info set age='20'; =》 对整张表
update user_info set age='20' where name='zs' ; 对某条数据
8.删除一条数据
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
delete from user_info where id=1;
9.删除表
drop table user_info;
10.排序语法 order by
select * from user_info order by age;
1.默认是升序
2. asc 升序 desc 降序
11.模糊查询 like
select * from user_info where name like '%z%';
select * from user_info where name like "_s%";
12.合并表
表结构要相同
union:去重
select * from a
union
select * from b;
union all:不去重
select * from a
union all
select * from b;
13.聚合函数:
select
sum(age) as age_sum,//总和
max(age) as age_max//最大值
,min(age) as age_min//最小值
,avg(age) as age_avg//平均数
,count(age) as cnt//数量
from user_info
14.多表联查join
14.1.内连接
select *
from a1 join b1
on a1.id = b1.id
14.2.左连接 left join 以左表为主 数据是全的 右表来匹配 匹配不上就是null
select *
from a1 left join b1
on a1.id =b1.id;
14.3.右连接 right join 以右表为主 数据是全的 左表来匹配 匹配不上就是null
select *
from a1 right join b1
on a1.id =b1.id;
14.4.全连接 full join 不支持全连接左表右表数据是全的,而且没有重复数据
select
*
from a1 left join b1
on a1.id =b1.id
union
select
*
from a1 right join b1
on a1.id =b1.id