mysql深度讲解

作者：蓝眼泪

背景介绍

cmake make configure
软件名称：mysql  mysql-server
服务：mysqld
主配置文件 /etc/my.cnf
启动命令 mysqld_safe
套接字 /var/lib/mysql/mysql.sock
数据目录 /var/lib/mysql
初始化脚本 mysql_install_db
进程文件 /var/run/mysqld.pid

第一 给root用户创建密码

grep 'temporary password' /var/log/mysqld.log
ulYrHuuU2t-U #获取临时密码
mysql -uroot -pulYrHuuU2t-U
alter user root@localhost identified by '123456';

关闭密码复杂性
 

set global validate_password_policy=0;       # 关闭密码复杂性策略
set global validate_password_length=1;      # 设置密码复杂性要求密码最低长度为1
select @@validate_password_policy;          # 查看密码复杂性策略
select @@validate_password_length;          # 查看密码复杂性要求密码最低长度大小
alter user 'root'@'localhost' identified by '123456';

mysqladmin -uroot password 123456
mysql -uroot -p123456
create user zhangsan@'%' identified by '123456';
set password=password('123456');
set password for zhangsan@'%' =password('123456');

第二 root密码忘记解决方案

systemctl stop mysqld
vim /etc/my.cnf
skip-grant-tables
update mysql.user set password=password('123456') where user='root';

vim /etc/my.cnf
#skip-grant-tables
systemctl restart mysqld

第三 创建数据库和表

mysql -uroot -p123456
create database web;
use web
create table a1(id int,name char(30));
describe a1;

create table a2(
id int unsigned not null auto_increment,
name char(30) not null default '',
age int not null default 0,
primary key (id));

describe a2;
insert into a2 (id,name,age) values (1,'zhangsan',21）；
select * from a2;
insert into values (2,'lisi',20);
insert into values (3,'wangwu',30);

第四 将表a2的数据复制到表a1

select * from a1;
insert into a1(id,name) select id,name from a2;
select * from a1;

 

第五 删除数据库，表，记录

create dataabase abc;
create table a3(id int,name char(30));
drop database abc;
drop table a3;

库和表删除用drop，记录删除用delete。

delete from a1 where id=1;
update a2 set age=35 where id=3;
alter table a2 rename a3;
alter table a2 modify name char(50);
describe a2;

第六 修改字段，数据，添加字段。

1 修改表字段详情

alter table a3 change name username char(40) not null default '';
describe a3;

2 添加字段

alter table a3 add time datetime;
describe a3;
alter table a1 add time datetime;
alter table a1 add brithday year first;
alter table a1 add sex nchar(1) after id;
alter table a1 drop brithday;

第七 授权和收回权限

select user from web.a1;
select user from myql.user;
grant all on web.* to zhangsan@'%';
grant all on web.* to lisi@'%' identified by '123456';
show grants for zhangsan@'%';
delete from a1 where age between 23 and 40;

grant all on mysql.* to wangwu@'%' identified by '123456';
mysql -uwangwu -p123456;
show databases;
show grants for wangwu@'%';

revoke drop,delete on mysql.* from lisi@'%';
show grants for lisi@'%';