mysql 语句基础（一）

mysql -uroot -p
mysql -h localhost -P 3306 -u root -p
mysql --host=localhost --post=3306 --user=root --password=XXXXX
mysql --host localhost --post 3306 --user root --password
复制

whoami
复制

ctrl+D
复制

create database `test.mytable`;
use `test.mytable`;
select database();
复制

show variables like 'datadir';
复制

ls -lhtr
复制

create table if not exists `test`.`mytable`(
id int unsigned auto_increment primary key,
name varchar(20),
country varchar(20)
)engine=innodb;
复制

show engines\G
复制

show create table test.mytable\G
desc test.mytable
复制

create table new_mytable like mytable;
复制

insert ignore into test.mytable(name,country)values('Mike','USA'),('Andy','India'),('Ravi','China')
复制

show warnings;
复制

replace into test.mytable values(1,'maink','USA'); 
insert into payments values('mysql','US') on duplicate key update  name=name+values(name);
复制

truncate table 
复制

in:
between ...and 
not:
复制

select count(*) from mytable where name regexp '^m' limit 5;
复制

order by 1 desc  limit 5;
复制

group by   having 


distinct
复制

create user if not exists 'test'@'%' identified with mysql_native_password by 'test' with max_queries_per_hour 500 max_updates_per_hour 100;
复制

grant select on test.* to 'test'@'%';
复制

grant insert on test.* to 'test'@'%' identified by 'XXXXX';
grant insert ,delete,update on test.* to 'test'@'%' identified with mysql_native_password as 'XXXXX';
grant select(id,name) on test.mytable to 'test'@'%' identified with mysql_native_password as 'XXXXX';
create user  'test'@'%' identified with mysql_native_password by 'test';
grant all privileges on *.* to 'mycat'@'%';
复制

grant grant option on *.* to 'mycat'@'%';
复制

show grants for 'test'@'%'\\G
复制

revoke delete on test.* from 'test'@'%';
复制

alter user 'mycat'@'%' identified with mysql_native_password by 'XXXX'
复制

alter user 'mycat'@'%' password expire interval 90 day;
复制

alter user 'mycat'@'%' account lock;
alter user 'mycat'@'%' account unlock;
复制

create role 'app_read_only','app_writes','app_developer';
grant select on test.* to 'app_read_only';
grant insert,update,delete on test.* to 'app_writes';
grant all on test.* to 'app_developer';
grant 'app_developer' to 'test'@'%';
复制

grant file on *.*  to 'root'@'%';
复制

select first_name,last_name into outfile 'result.csv' fields terminated by ','optionally enclosed by '"' lines terminated by '\n' from employees where hire_date<'1986-01-01' limit 10;
复制

load data infile 'result.csv' into table employee_names fileds terminated by ',' optionally enclosed by '"' lines terminated by '\n'
复制

load data infile 'result.csv'  replace/ignore into table employee_names fileds terminated by ',' optionally enclosed by '"' lines terminated by '\n'
复制

load data local infile 'result.csv'  ignore into table employee_names fieds terminated by ',' optionally enclosed by '"' lines terminated by '\n'
复制

create table employee (id int,name varchar(20),age int);
drop procedure if exists employees;
/*修改分隔符*/
delimiter $$
/*in 指定作为函数的变量，out 指定输出变量*/
create procedure employees(out new_id int,in name varchar(20),in age int )
begin 
/*声明变量*/
/*declare n_id char(4);*/
select max(id) into new_id from employee;
set new_id=new_id+1;
insert into employee values (new_id,name,age);
end 
$$
delimiter ;


grant execute on *.* to 'root'@'%';
insert into employee values(1,'aa',10),(2,'bb',20);
commit;
select * from employee;
call employees(@new_id,'cc',50);


select @new_id;
show procedure status ;
复制

DROP function if EXISTS fun_employee;


delimiter $$
create function fun_employee(emp int) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE n_age int;
select avg(age) into n_age from employee where id=emp;
return (n_age) ;
END
$$
delimiter ;


select * from employee;
select fun_employee(2);
show create function fun_employee \G;
复制

drop trigger if exixts t_age;
delimiter $$
create trigger t_age before insert on employee for each row
begin
 set new.age=round(new.age);
end
$$
delimiter ;
复制

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; 
复制

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword';
drop VIEW v_employee;
create ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW v_employee as select * from employee where id>5;
INSERT into employee values (6,'hh',60)
COMMIT;
select * from v_employee;
复制

show full tables where table_type like 'VIEW';
复制

show create view v_employee \G;
复制

set global event_scheduler=on;
drop event if exists e_employee;
delimiter $$
create event if not exists e_employee on SCHEDULE every 1 week  starts CURRENT_DATE do 
begin
delete from employee where id <6;
end
$$
delimiter ;
复制