mysql definer_mysql 语句基础（一）

1、使用命令行客户端连接到MySQL

2、创建数据库

3、创建表

4、插入、更新和删除行

5、加载示例数据

6、查询数据

7、对结果排序

8、对结果分组(聚合函数)

9、创建用户

10、授予和撤销用户的访问权限

11、查询数据并保存到文件和表中

12、将数据加载到表中

13、表关联

14、存储过程

15、函数

16、触发器

17、视图

18、事件

19、获取有关数据库和表的信息

---

1、使用命令行客户端连接到MySQL

mysql -uroot -pmysql -h localhost -P 3306 -u root -pmysql --host=localhost --post=3306 --user=root --password=XXXXXmysql --host localhost --post 3306 --user root --password

-P (大写) 用于指定端口

-p (小写)用于指定密码

查看当前用户

whoami

断开连接

ctrl+D

SQL语句结尾 \g 对应的输出水平显示；\G 对应的输出垂直显示。

---

2、创建数据库

--反标记字符(`)用于引用标识符。当数据库名称包含特殊字符时，需要使用反标记字符。

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

查看数据目录

show variables like 'datadir';

查看数据文件

ls -lhtr

---

3、创建表

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\Gdesc test.mytable

克隆表结构

create table new_mytable like mytable;

---

4、插入、更新和删除行

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

查看等待事务

show warnings;

replace行存在则替换，不存在则插入。如果你想在行已经存在的情况下处理重复项，则需要使用on duplicate key update。如果指定了 on duplicate update选项，并且insert 语句在primary key中引发了重复值，则mysql 会用新值更新已有的行。

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

截断表

truncate table 

---

5、加载示例数据

mysql -u root -p

---

6、查询数据

in:between ...and not:

正则表达式：

* 零次或多次重复

+ 一个或多个重复

? 可选字符

. 任何字符

\. 区间

^ 以...开始

$ 以...结束

[abc]只有a、b、c

[^abc]非a 非b 非c

[a-z]字符a到z

[0-9]字符0到9

^....$开始和结束

\d 任何数字

\D 任何非数字字符

\s 任何空格

\S 任何非空白字符

\w 任何字母数字字符

\W 任何非字母数字字符

{m} m次重复

{m,n} m到n次重复

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

---

7、对结果排序

order by 1 desc  limit 5;

---

8、对结果分组(聚合函数)

group by   having distinct

---

9、创建用户

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

max_queries_per_hour 500 用户可以在一小时内执行的最大查询数为500

max_updates_per_hour 100 用户可以在一小时内执行的最大更新次数为100次

select password('XXXX');

---

10、授予和撤销用户的访问权限

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 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'@'%';

---

11、查询数据并保存到文件和表中

输出到文件

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

my.cnf 添加 secure_file_priv=/var/lib/mysql;

select first_name,last_name into outfile 'result.csv' fields terminated by ','optionally enclosed by '"' lines terminated by '\n' from employees where hire_date

加载文件

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

如果开头你想忽略的行，可以使用ignore n lines 指定。

可以使用replace ignore处理重复行

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'

13、表关联

---

14、存储过程

存储过程处理的是一组sql语句，且没有返回值。

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 ;

SECURITY_type 列值

definer：即使只有execute权限的用户也可以调用并获取存储例程的输出，而不管该用户是否具有对基础表的操作权限。

如果definer具有权限，那就足够了。

invoker：安全上下文被切换到调用存储例程的用户。在这种情况下，调用者应该可以访问基础表。

---

15、函数

创建存储函数是为了简化复杂的计算，函数应该有一个我返回值。并且可以在select中调用函数。

DROP function if EXISTS fun_employee;delimiter $$create function fun_employee(emp int) RETURNS VARCHAR(20)DETERMINISTICBEGINDECLARE 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;

DETERMINISTIC 确定性的(对于相同的输入总是输出相同的值)

默认是 not DETERMINISTIC 

---

16、触发器

触发动作时间可以是before 或after

触发事件可以是insert、delete或update

insert：无论何时通过insert、replace或load data语句插入新行，都会激活insert触发事件

update：通过update语句激活update触发事件

delete：通过delete或replace语句激活delete触发事件

一个表同时具有多个触发器。必须使用follows 或precedes指定先行的触发器。

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

检查所有触发器

show triggers \G

检查现有触发器的定义 

show create trigger

---

17、视图

#修改加密规则  

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

#更新密码(mysql_native_password模式)    

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;

VIEW  算法

merge：mysql将输入查询和视图定义合并到一个查询中，然后执行组合查询。

仅允许在简单视图上使用merge算法。

temptable：mysql将结果存储在临时表中，然后对这个临时表执行输入查询。

undefined(默认)：mysql自动选择merge或temptable算法。mysql把merge算法作为首选的tempable算法，

因为merge算法效率更高。

---

18、事件

事件调度线程是未启用的

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 begindelete from employee where id <6;end$$delimiter ;

show events \G

检查事件的定义

show CREATE event e_employee \G;

禁用、启用事件

alter event e_employee disable;

alter event e_employee enable;

访问控制

所有存储的程序(过程、函数、触发器和事件)和视图都有一个definer。如果未指定definer，则创建该对象的用户将被选为definer。

存储例程(包括过程和函数)和视图具有值为definer或invoker的sql security特性，来指定对象

是在definer还是在invoker上下文中执行。触发器和事件没有sql security 特性，并且始终在definer

上下文中执行。服务器根据需要自动调用这些对象，因此不存在调用用户。

---

19、获取有关数据库和表的信息

information_schema

静态表元数据：

table_schema,table_name,table_type 和engine 这些统计信息将直接从数据字典中读取。

动态表元数据：auto_increment,avg_row_length 和data_free 。动态元数据会频繁更改(例如，

auto_increment值将在每次insert后增长)。在很多情况下，动态元数据在一些需要精确计算的情况下也会产生

一些开销，并且准确性可能对常规查询不会有好处。考虑到data_free统计量的情况(该统计显示表中的空闲字节数)，

缓存值通常足够了。

set @@global.information_schema_stats='LATEST'

本公众号是个人学习工作笔记，希望大家发现问题能及时和我本人沟通，希望你与我共同成长。个人微信zgjt12306。

 

欢迎关注“自学Oracle”