MySQL | 凉薄的自动书记人偶

登录准备

# -h表示主机,-u表示用户名,-p表示密码
mysql -hlocalhost -uroot -p5KVp2y7,k96o

describe acglist;

检索数据

# 查找唯一值
select DISTINCT col from table2

# 检索从第5行(不含)开始的6行(就是6-11行)
select name from new_list limit 5, 6;

排序

# 首要关键字是page,次要关键字是tags,再次关键字是id
select id,languages from commic_list order by page,tags,id

# 两个关键字, from_date升序,emp_no为降序
# 注意要有逗号
select * from titles order by emp_no DESC ,from_date limit 5;

使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。 eg:找出最昂贵物品的值

过滤

where过滤

# 注意要加引号,一个等号(不是两个等号)
select * from titles where title='Staff' limit 5;
select * from titles where Date(to_date) = "9999-01-01" limit 5;

# 空值检查使用is null
select id from commic_list where artists is null

# 使用序列的in
select id,page from commic_list where languages in ('english','japanese');

使用通配符进行过滤

1 2	# _替代一个字符 select * from titles where emp_no like "_000_";

使用正则表达式进行过滤

1 2	# 查找所有以(开头的记录 select id,title from commic_list where title regexp "^\\(";

计算字段

1	select concat(emp_no,"-",title) as new_col from titles limit 5;

使用函数

1 2	# 截取前面5个字符 select left(title,5) as new_col from titles limit 5;

汇总数据

# 获取行数
select count(emp_no) from titles where title is not null;

select avg(distinct salary) as avg_salary from salaries ;

分组数据

1	select emp_no ,avg(salary) as avg_salary from salaries group by emp_no limit 5;

如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出

过滤分组:HAVING子句

 select emp_no , avg(distinct salary) from salaries group by emp_no having emp_no > 10088 limit 5;
    
# 查找作品的语言是英语,同时总页数大于60的作者
# 然后按照总页数升序排列
select artists,languages,sum(page) 
    from commic_list 
    where languages = 'english' 
    group by artists 
    having sum(page) > 60 
    order by sum(page);

总结 SELECT子句顺序

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在表中选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

子查询

外查询where的内容其实就是内查询select的内容

select dept_no from departments where dept_no in 
	(select dept_no from dept_emp 
     	where Date(to_date)="9999-01-01")
limit 5;

select count(dept_no) from departments where dept_no in 
	(select dept_no from dept_emp 
     	where Date(to_date)="9999-01-01") ;

联结表

select d1.dept_no,d1.dept_name,d2.from_date 
	from departments as d1 left join dept_emp as d2 
	on d1.dept_no=d2.dept_no
    limit 5;

高级联结

自联结、自然联结和外部联结。

select name from table1 where
	(select price from table1 where name = '惠惠')

# 将上面的代码改为
select t1.name from table1 t1 , table1 t2 where t1.name = '惠惠' and t1.price < t2.price

1
2
3

# 查找页数比id为258126这本书 还有多的书的id
select t2.id,t2.page from commic_list t1,commic_list t2 
	where t1.id=258126 and t1.page < t2.page

组合查询

1
2
3

select dept_no from departments where dept_name = "Finance"  
union 
select dept_no from dept_emp where to_date = "9999-01-01";

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）

全文本搜索

fulltext(note_text)

# 在定义表时使用fulltext
creat table product_notes
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null,
	primary key (note_id),
	fulltext(note_text)
) engine = MyISAM;
# 在定义之后，MySQL自动维护note_text索引。在增加、更新或删除行时， 索引随之自动更新。

插入数据

# 各个列必须以它们在表定义中出现的次序填充。
insert into less_table values (50219,'hyl','http:xxx.jpg','2019-04-08 14:35','hh')

# 在表名后的括号里明确地给出了列名
insert into less_table(id,name,img,created_time,text) 
	values (50220,null,null,'2019-04-08 14:35','hh')
	
# 省略列。这表示可以只给某些列提供值，给其他列不提供值。
insert into less_table(id,text) values (50221,'hh')

# 一次性插入多条数据
insert into less_table(id,text) values
	(50221,'hh'),
	(50222,'xx'),
	(20223,'ww');

使用省略列需要注意:

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT
1
2
3
4
5
6
7
8
9
10
11
# 使用insert into从custnew表中的数据导入customers表
insert into customers(cust_id,
	cust_contact,
	cust_email,
	cust_name)
select cust_id,
	cust_contact,
	cust_email,
	cust_name
from custnew
where cust_id > 10000;
需要注意的是:insert select使用的并不是关键词参数,而是位置参数:

更新和删除数据

# 更新特定行
update less_table set name = 'hyl',img = 'http://xx.jpg' where id = 50221

delete from less_table where id = 50221

# 删除原来的表并重新创建一个表
truncate less_table

创建和操纵表

creat table product_notes
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null,
	primary key (note_id),
	fulltext(note_text)
) engine = InnoDB;


# if not exists
creat table product_notes if not exists
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null      default 'xxx',
	primary key (note_id),
	fulltext(note_text)
) engine = InnoDB;

更改表

# 添加新的字段
alter table less_table add new_column char(20)
# 删除字段
alter table less_table drop column new_column

# 定义外键
alter table orderitems 
	add constraint fk_orderitems_orders
	foreign key (order_num) references orders (order_num);

删除表:

1	drop table less_table

重命名表:

# 重命名单个表
rename table less_table to new_table
# 重命名多个表
rename table old_table1 to new_table1
			old_table2 to new_table2
			old_table3 to new_table3

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
	and orderitems.order_num = orders.order_num
	and prod_id = 'TNT2'

现在，假如可以把整个查询包装成一个名为product_customers的虚拟表，则可以如下轻松地检索出相同的数据：

1 2	select cust_name,cust_contact from product_custimers where prod_id = 'TNT2'

product_customers就是一个视图.
作为视图，本身并不包含数据。==它包含的是一个SQL查询==（与上面用以正确联结表的相同的查询）
为什么要使用视图？
视图的常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。

使用规则:

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图

# 用视图重新格式化检索出的数据(就是创建了一个新的name列)
create or replace view name2dept as 
select e.emp_no,concat(e.first_name,' ',e.last_name) as name,d1.dept_name
	from employees e,departments d1,dept_emp d2 
	where e.emp_no = d2.emp_no and d2.dept_no = d1.dept_no 
	order by e.emp_no 

# 复用视图
select * from name2dept where emp_no < 10005;

# 使用视图来创建并且存储计算字段
create view sumPrice as 
select item_prict*quantity as tot_price from orderitems

存储过程

简单来说,存储过程就是数据库语言层面的代码封装与重用。
或者说存储过程就是一组可编程的函数，是为了完成特定功能的SQL语句集

# 存储过程就是函数,所以product_pricing()后面的括号就是放参数的地方
# begin和end中间就是函数体,不过这里称为"存储过程体"
create procedure product_pricing()
	begin
	select avg(prod_price) as price_average from products;
	end;

游标

有时，需要在检索出来的行中前进或后退一行或多行,这时就可以使用游标了。

游标不是一条SELECT语句，而是被该语句检索出来的结果集。

# 定义名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders
end;

触发器

触发器可在一个操作发生之前或之后执行

# 每次成功插入行，显示Product added消息。 
create trigger newproduct after insert on products
for each row select 'Product added';

# 删除触发器
drop trigger newproduct;

事务

并非所有引擎都支持事务处理 :
MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。

事务处理保证操作的原子性

select * from ordertotals;

start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;

select * from ordertotals;