# MYSQL 常用语句

创建数据库 (以名为 test_db 为例)
create database test_db DEFAULT CHARACTER SET utf8;
删除数据库
drop database test_db
打开数据库
use test_db;
显示当前打开数据库
SELECT DATABASE();
创建数据表
create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2));
create table instructor( ID char(5), name varchar(20) not null, dept_name varchar(20),. salary numeric(8,2), primary key (ID));
显示表
show tables;
显示表结构
desc instructor;
插入
insert into instructor values ('10211', 'Smith', 'Biology', 66000);
查找
SELECT * FROM instructor

字段重命名
SELECT a as b;

修改表结构
alter table instructor add sex char(2) (添加字段）
alter table instructor modify column sex char(4) (修改字段）
alter table instructor drop column sex (删除字段）
查询所有
select * from instructor
查询字段记录
select sex from instructor
select DISTINCT sex from instructor (删除重复)
select DISTINCT sex from instructor LIMIT 2,3 (从第三行开始，三条)
子查询
select DISTINCT sex from instructor where xxx in(select )
分组查询

SELECT category,sum(book_total) FROM book GROUP BY category

条件查询
select sex from instructor where salary>8000
自然连接
select sex from instructor where natural join teacher
一般连接
select sex from instructor,teacher where instructor.id=teacher.id
排序
select sex from instructor where salary>8000 ORDER BY salary (默认升序）

select sex from instructor where salary>8000 ORDER BY salary DESC (降序）

like 用法

例：查询书名中含有 “数据” 字样的图书信息；

SELECT * FROM book WHERE book_name like "%数据%"

%: 一位或多位 _ : 一位

删除

delete from instructor where salary=1500

插入

Insert into 表名（列） values（值）

更新

update 表名 set 列名=xxx where 条件

case 语句
update 表名 set 列名=case when salary<=1000 then salary*1.5 else salary end;

创建视图

CREATE VIEW 视图名(列) AS SELECT 列 from 表名

# 索引

唯一索引

CREATE UNIQUE index 索引名 on 表名 (列)

联合索引

alter table 表名 add INDEX 索引名 (列名，列名)

删除索引

drop index unique_index_name on reader

# 授权

创建用户

create user user1@'localhost' identified by '123';

功能：创建一个用户，用户名为 “user1”，密码为 “123”。

@'localhost'：指定该用户只能在本地访问数据库；

@'%'：指定该用户可远程访问数据库；

@'111.222.33.44'：从指定 ip 的机器上才能访问数据库。

查看已有用户

select * from mysql.user

功能：查看已有用户的用户名、有无密码、权限等详细情况。

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

功能：查看已有用户的用户名

删除用户

drop user user2@'localhost';

查询用户权限

show grants for user1@'localhost';

授权

grant delete on book to user1@'localhost'

收回权限

revoke delete on book from user1@'localhost'

all：表示一切权限；

.：表示全部数据库

# 函数

CREATE FUNCTION `func1`(`publisher` char(30)) RETURNS double
BEGIN
	declare price DOUBLE;
	select avg(book.price) into price
	from book
	where book.publisher=publisher;
	return price;
END
select func1('机械工业出版社')  //调用

# 存储过程

CREATE PROCEDURE `func3`(IN `reader_number` char(10),IN `book_number` char(10),OUT `s` char(100))
BEGIN
	select concat('读者编号:',reader_number,'时间:',borrow_time) into s
	from borrow_book
	where borrow_book.reader_number=reader_number && borrow_book.book_number=book_number;
END
call func3('1002','1001',@s);
SELECT @s as s;

游标

CREATE PROCEDURE `func4`()
BEGIN
	DECLARE temp char(10);
	DECLARE cur CURSOR for SELECT reader_number from borrow_book;//游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET temp = 'NULL';
	open cur;
	FETCH cur into temp;
	WHILE (temp!='NULL') 
	DO
	SELECT temp;
	UPDATE reader
	set total_borrowbook=total_borrowbook+1
	where reader_number=temp;
	FETCH cur into temp;
	END WHILE;
	close cur;
END
call func4();

# 触发器

CREATE TRIGGER number
AFTER INSERT on o for each row
begin
UPDATE g
set num=num-new.number
where id=new.gid;
end