Many help items for your request exist. To make a more specific request, please type'help <item>', where <item> is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE RESOURCE GROUP CREATE ROLE CREATE SCHEMA CREATE SERVER CREATE SPATIAL REFERENCE SYSTEM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE SCHEMA SHOW CREATE TABLE SHOW CREATE USER SPATIAL INDEXES
创建、删除、查看数据库
创建默认字符集的数据库（默认是拉丁字符集）
1
create database test_data;
1
show databases like "test%";
创建gbk字符集的数据库
1
create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
查看创建数据库的语句
1
show create database test_gbk;
删除数据库
1
drop database test_data;
连接数据库
进入指定数据库操作
1
use test_gbk;
查看当前连接的数据库
1
select database();
查看当前连接数据库的用户
1
select user();
创建用户
1
create user if not exists 'testuser'@'localhost' identified by '123456';
1
create user if not exists 'company_read_only'@'localhost' identified with mysql_native_password by 'company_pass' with max_queries_per_hour 500 max_updates_per_hour 100;
上述声明将为用户创建以下内容:
用户名 : company read only
仅从localhost访问
可以限制对 IP范围的访问，例如 10.148.%.%。 通过给出%，用户可以从任何主机访问
密码：company_pass
使用 mysql_native_password（默认）身份验证
还可以指定任何可选的身份验证，例如 sha256_password、LDAP 或 Kerberos
用户可以在一小时内执行的最大查询数为 500
用户可以在一小时内执行的最大更新次数为 100次
授予和撤销用户的访问权限
你可以限制用户访问特定数据库或表，或限制特定操作，如 SELECT 、 INSERT 和UPDATE。 你需要拥有 GRANT 权限，才能为其他用户授予权限 。
授予权限
将READ ONLY (SELECT)权限授予testuser用户
1
grant select on company.* to 'testuser'@'localhost';
限制查询指定的表。 将testuser用户限制为仅能查询employees 表
1
grant select on employees.employees to 'testuser'@'localhost';
grant select(first_name, last_name) on employees.employees to 'testuser'@'localhost';
扩展授权。可以通过执行新授权来扩展授权。
1
grant select(salary) on employees.salaries to 'company_read_only'@'localhost';
创建 SUPER 用户 。 需要一个管理员账户来管理该服务器 。 ALL 表示除 GRANT 权限之外的所有权限 。
1
create user 'super_admin'@'%' identified with mysql_native_password by 'super@admin';
1
grant all on *.* to 'super_admin'@'%';
授予 GRANT特权。 用户拥有 GRANT OPTION权限才能授予其他用户权限。 可以将 GRANT 特权扩展到 super_admin 超级用户
1
grant grant option on *.* to 'super_admin'@'%';
检查授权
1
show grants for 'super_admin'@'%';
1
show grants for 'company_read_only'@'localhost';
1
show grants for 'testuser'@'localhost';
撤销权限
1 2 3 4 5 6 7 8 9 10 11
revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';
# 回收后查看权限 mysql> show grants for 'testuser'@'localhost'; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for testuser@localhost | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `testuser`@`localhost` | | GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_gbk`.* TO `testuser`@`localhost` | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
撤销 company_read_only 用户对薪水列的访问权限
1
revoke select(salary) on employees.salaries from 'company_read_only'@'localhost';
create role 'app_read_only', 'app_writes', 'app_developer';
使用 GRANT 语句为角色分自己权限
1
grant select on employees.* to 'app_read_only';
1
grant insert, update, delete on employees.* to 'app_writes';
1
grant all on employees.* to 'app_developer';
创建用户。如果你不指定主机，则将采用 %(任意主机):
1 2 3 4
create user emp_read_only identified by '123'; create user emp_write identified by '123'; create user emp_develpoer identified by '123'; create user emp_read_write identified by '123';
使用 GRANT 语句为用户分配角色 。你可以为用户分配多个角色 。
1 2 3 4
grant 'app_read_only' to emp_read_only; grant 'app_writes' to emp_write; grant 'app_developer' to emp_develpoer; grant 'app_read_only', 'app_writes' to emp_read_write;
mysql> create table test( id int(4) not null, name char(20) not null );
另一种方式
1 2 3 4 5 6
mysql> create table if not exists test_gbk.customers( id int unsigned AUTO_INCREMENT PRIMARY KEY, first_name varchar(20), last_name varchar(20), country varchar(20) ) engine=InnoDB;
mysql> desc employees\G *************************** 1. row *************************** Field: emp_no Type: int(11) Null: NO Key: PRI Default: NULL Extra: *************************** 2. row *************************** Field: birth_date Type: date Null: NO Key: Default: NULL Extra: *************************** 3. row *************************** Field: first_name Type: varchar(14) Null: NO Key: Default: NULL Extra: *************************** 4. row *************************** Field: last_name Type: varchar(16) Null: NO Key: Default: NULL Extra: *************************** 5. row *************************** Field: gender Type: enum('M','F') Null: NO Key: Default: NULL Extra: *************************** 6. row *************************** Field: hire_date Type: date Null: NO Key: Default: NULL Extra: 6 rows in set (0.00 sec)
查询数据
查询所有列
1
select * from departments;
选择列
选择 dept_manager 的 emp_no 和 dept_no 列：
1
select emp_no,dept_no from dept_manager;
计数
从 employees表中查找员工的数量
1
select count(*) from employees;
条件过滤
1
select emp_no from employees where first_name='Georgi' and last_name='Facello';
操作符
IN: 检查一个值是存在一组值中
1
select count(*) from employees where last_name in ('Christ', 'Lamba', 'Baba');
BETWEEN …AND:检查一个值是否在一个范围内
1
select count(*) from employees where hire_date between '1986-12-01' and '1986-12-31';
NOT 你可以简单地用 NOT 运算符来否定结果
1
select count(*) from employees where hire_date not between '1986-12-01' and '1986-12-31';
简单模式匹配
可以使用 LIKE 运算符来实现简单模式匹配。 使用下画线( _ )来精准匹配一个字符，使用( % ) 来匹配任意数量的字符 。
找出名字以 Christ开头的所有员工的人数
1
select count(*) from employees where first_name like 'christ%';
找出名字以 Christ开头并以 ed结尾的所有员工的人数
1
select count(*) from employees where first_name like 'christ%ed';
找出名字中包含 sri的所有员工的人数
1
select count(*) from employees where first_name like '%sri%';
找到名字以 er 结尾的所有员工的人数
1
select count(*) from employees where first_name like '%er';
找出名字以任意两个字符开头、后面跟随 ka、再后面跟随任意数量字符的所有员工的人数
1
select count(*) from employees where first_name like '__ka%';
正则表达式
你可以利用 RLIKE 或 REGEXP 运算符在 WHERE 子句中使用正则表达式
找出名字以 Christ开头的所有员工的人数
1
select count(*) from employees where first_name rlike '^christ';
找出姓氏以 ba结尾的所有员工的人数
1
select count(*) from employees where last_name regexp 'ba$';
查找姓氏不包含元音 (a、 e、 i、 o和u)的所有员工的人数
1
select count(*) from employees where last_name not regexp '[aeiou]';
限定结果
查询hire date在1986年之前的任何10名员工的姓名
1
select first_name, last_name from employees where hire_date < '1986-01-01' limit 10;
使用表别名
使用自别名来更改 COUNT (*)
1 2 3 4 5 6 7
mysql> select count(*) as count from employees where hire_date between '1986-12-01' and '1986-12-31'; +-------+ | count | +-------+ | 3081 | +-------+ 1 row in set (0.09 sec)
对结果排序
查找薪水最高的前 5名员工的员工编号
1
select emp_no, salary from salaries order by salary desc limit 5;
你可以在 SELECT 语句中提及列的位置，而不是指定列名称
1
select emp_no, salary from salaries order by 2 desc limit 5;
对结果分组(聚合函数)
COUNT
分别找出男性和女性员工的人数
1
select gender, count(*) as count from employees group by gender;
如果你希望查找员工名字中最常见的10个名字，可以使用 GROUP BY first_name 对所有名字分组，然后使用COUNT(first_name)在各组内计数，最后使用ORDER BY计数对结果进行排序 并将返回结果行数限制为前 10行
1
select first_name,count(first_name) as count from employees group by first_name order by count desc limit 10;
SUM
查找每年给予员工的薪水总额并按薪水高低对结果进行排序。 YEAR ()函数将返回给定日期所在的年份
1
select year(from_date) as date, sum(salary) as sum from salaries group by date order by sum desc;
AVERAGE
查找平均工资最高的10名员工
1
select emp_no, avg(salary) as avg from salaries group by emp_no order by avg desc limit 10;
mysql> show variables like '%secure%'; +--------------------------+---------------+ | Variable_name | Value | +--------------------------+---------------+ | require_secure_transport | OFF | | secure_file_priv | /private/tmp/ | +--------------------------+---------------+ 2 rows in set (0.01 sec)
以下语句会将输出结果保存为 csv 格式
1
select first_name, last_name into outfile '/tmp/result.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from employees where hire_date<'1986-01-01' limit 10;
mysql> load data infile '/tmp/result.csv' into table emplyee_names fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines;
可以用 REPLACE或者 IGNORE来处理重复的行
1 2 3 4 5
mysql> load data infile '/tmp/result.csv' replace into table emplyee_names fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines;
1 2 3 4 5
mysql> load data infile '/tmp/result.csv' ignore into table emplyee_names fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines;
select dept_name, avg(salary) as avg_salary from salaries join dept_emp on salaries.emp_no=dept_emp.emp_no join departments as dept on dept_emp.dept_no=dept.dept_no group by dept_emp.dept_no order by avg_salary desc ;
通过与自己关联来识别重复项
1 2 3 4 5 6 7 8 9 10 11
select empl1.* from employees as empl1 join employees empl2 on empl1.first_name=empl2.first_name and empl1.last_name=empl2.last_name and empl1.gender=empl2.gender and empl1.hire_date=empl2.hire_date and empl1.emp_no!=empl2.emp_no order by first_name, last_name;
使用子查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
select first_name, last_name from employees where emp_no in ( select emp_no from titles where title="Senior Engineer" and from_date="1986-06-26" ) ;
找到工资最高的员工
1 2 3 4 5 6 7 8 9 10 11 12
select emp_no from salaries where salary=( select max(salary) from salaries ) ;
查找表之间不匹配的行
1
create table employees_list1 as select * from employees where first_name like 'aa%';
1
create table employees_list2 as select * from employees where emp_no between 400000 and 500000 and gender='F';
我们已经知道如何找到两个列表中都存在的员工了 ，代码如下
1 2 3 4 5 6 7
select l1.* from employees_list1 l1 join employees_list2 l2 on l1.emp_no=l2.emp_no ;
/* 删除已存在的存储过程 */ drop procedure if exists create_employee; /* 分隔符修改为 $$ */ delimiter $$
/* IN 指定作为参数的变量，INOUT指定输出的变量 */ create procedure create_employee ( Out new_emp_no INT, IN first_name varchar(20), IN last_name varchar(20), IN gender enum('M', 'F'), IN birth_date date, IN emp_dept_name varchar(40), IN title varchar(50) ) BEGIN /* 为emp_dept_no 和 salary 声明变量 */ declare emp_dept_no char(4); declare salary int default 60000;
/* 查询employees表中emp_no的最大值，赋值给变量 new_emp_no */ select max(emp_no) into new_emp_no from employees;
/* 增加 new_emp_no */ set new_emp_no = new_emp_no + 1;
mysql> select * from employees where emp_no=500001; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 500001 | 1984-06-19 | John | Smith | M | 2020-07-15 | +--------+------------+------------+-----------+--------+------------+ 1 row in set (0.01 sec)
mysql> select * from salaries where emp_no=500001; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 500001 | 100000 | 2020-07-15 | 9999-01-01 | +--------+--------+------------+------------+ 1 row in set (0.01 sec)
mysql> select * from titles where emp_no=500001; +--------+-------+------------+------------+ | emp_no | title | from_date | to_date | +--------+-------+------------+------------+ | 500001 | Staff | 2020-07-15 | 9999-01-01 | +--------+-------+------------+------------+ 1 row in set (0.00 sec)
触发器
触发器用于在触发器事件之前或之后激活某些内容。
如何操作
例如：假设你希望在将薪水插入 salaries 表之前对其进行四舍五入 。 NEW 指的是正在插入的新值 :
1 2 3 4 5 6 7 8 9
drop trigger if exists salary_round; delimiter $$ create trigger salary_round before insert on salaries for each row BEGIN set NEW.salary=ROUND(NEW.salary); END $$ delimiter ;
1
source /tmp/before_insert_trigger.sql
1 2 3 4 5 6 7 8 9
mysql> insert into salaries values(10002, 100000.79, curdate(), '9999-01-01'); Query OK, 1 row affected (0.01 sec) mysql> select * from salaries where emp_no=10002 and from_date=curdate(); +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10002 | 100001 | 2020-07-15 | 9999-01-01 | +--------+--------+------------+------------+ 1 row in set (0.01 sec)
假设你要记录 salaries 表中新增的薪水记录：
创建审计表：
1
create table salary_audit (emp_no int, user varchar(50), date_modified date);
delimiter $$ create trigger salary_audit BEFORE INSERT on salaries for each row precedes salary_round BEGIN insert into salary_audit value(NEW.emp_no, USER(), curdate()); END;$$ delimiter ;
mysql> insert into salary_view values(10001, 100001); ERROR 1423 (HY000): Field of view 'employees.salary_view' underlying table doesn't have a default value
如果该表有 一个默认值，即使它不符合视图中的过滤器条件，你也可以向其中插入一行 。 为了避免这种情况，为了只允许插入符合视图条件的行，必须在定义里面提供 WITH CHECK OPTION。
VIEW算法:
MERGE: MySQL 将输入查询和视图定义合并到一个查询中，然后执行组合查询。 仅允许在简单视图上使用 MERGE 算法 。
drop EVENT if exists purge_salary_audit; delimiter $$ create EVENT if not exists purge_salary_audit on SCHEDULE every 1 week starts current_date DO BEGIN delete from salary_audit where date_modified < date_add(curdate(), interval - 7 day); END;$$ delimiter ;
检查事件:
1
show EVENTS\G
检查事件定义：
1
show create EVENT purge_salary_audit\G
禁用/启用事件:
1 2
ALTER EVENT purge_salary_audit DISABLE; ALTER EVENT purge_salary_audit ENABLE;
获取有关数据库和表的信息
TABLES
例如，假设你想知道 employees 数据库中的 DATA LENGTH 、 INDEX LENGTH 和DATE FREE，代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
select sum(data_length)/1024/1024 as data_size_mb, sum(index_length)/1024/2014 as index_size_mb, sum(data_free)/1024/1-24 as data_free_mb from information_schema.tables where table_schema='employees'; +--------------+---------------+----------------+ | data_size_mb | index_size_mb | data_free_mb | +--------------+---------------+----------------+ | 142.85937500 | 2.82025819 | 20456.00000000 | +--------------+---------------+----------------+ 1 row in set (0.00 sec)
COLUMNS
列出每个表的所有列及其定义:
1
select * from columns where table_name='employees'\G
FILES
1
select * from files where file_name like './employees/employees.ibd'\G
INNODB_TABLESPACES
1
select * from innodb_tablespaces where name='employees/employees'\G
INNODB_TABLESTATS
1
select * from innodb_tablestats where name='employees/employees'\G
select balance INTO @a.bal from account where account_number='A';
update account set balance=@a.bal-100 where account_number='A';
select balance INTO @b.bal from account where account_number='B';
update account set balance=@b.bal+100 where account_number='B';
COMMIT;
如果遇到错误并希望中止事务， 可以发送 ROLLBACK 语句而非 COMMIT 语句 。
1 2 3 4 5 6 7
start TRANSACTION select balance into @a.bal from account where account_number='A'; update account set balance=@a.bal-100 where account_number='A'; select balance into @c.bal from account where account_number='C'; show warnings; select @c.bal; rollback;
DDL 语句，如数据库的 CREATE 或 DROP 语句，以及表或存储例程的 CREATE、DROP或 ALTER 语句，都是无法回滚的 。
使用保存点
使用保存点可以回滚到事务中的某些点，而且无须中止事务。你可以使用 SAVEPOINT 标识符为 事务设置名称，并使用 ROLLBACK TO 标识语句将事务回滚到指定的保存点而不中止事务。
1 2 3 4 5 6 7 8 9
BEGIN; select balance INTO @a.bal from account where account_number='A'; update account set balance=@a.bal-100 where account_number='A'; update account set balance=balance+100 where account_number='B'; SAVEPOINT transfer_to_b; select balance INTO @a.bal from account where account_number='A'; update account set balance=balance+100 where account_number='C'; ROLLBACK TO transfer_to_b; commit;