MySQL Note

一 MySQL动作关键字

1.1 create
用途：创建index,procedure/function,schema,table
语法：

/* index
create [unique|fulltext|spatial] index index_name [using "index_type"]
on tbl_name(index_col_name,...)
[index_option]...
#index_col_name:
	col_name[(length)][asc|desc]
#索引类型
	unique  唯一索引，允许为null
	fulltext 全文索引
	spatial  空间索引
*/
mysql> create unique index index_name 
	using btree 
	on customers(customername(5) asc); 
	
/* procedure/function
create 
	[definer={user|current_user}]
	procedure sp_name([proc_parameter[,...]]
	[characteristic ...] routing_body
create
	[definer={user|current_user}]
	function sp_name([func_parameter[,...])
	returns type
	[characteristic ...] routine_body
#proc_parameter:
	[in|out|inout]param_name type
#func_parameter:
	param_name type
#routine_body:
	valid sql procedure statement
#delimiter定义分隔符
*/
mysql> delimiter //
 create 
 	definer = root
	procedure getallcustomers(inout test int)
	sql security definer
    	begin
  		select * from customers;
	 end//
delimiter ;

mysql> delimiter //
create
	definer = root
	function getallcustomers(test int)
	returns int
	sql security definer
	begin 
		select * from customers;
	end//
delimiter ;

/*schema
creart
	{database|schema} [if not exists] db_name
	[create_specification]...
#create_specication:
	[defualt]character set [=] charset_name	//字符集设置
	|[defualt]collate [=] collation_name		//校正设置
*/
mysql>create database test;

/*
create
	[or replace]
	[algorithm={undefined|merge|temptable}]	//算法
	[definer={user|current_user}]			
	[sql security{definer|invoker}]
	view view_name[(column_list)]
	as select_statement
	[with [cascaded|local] check option]
*/
mysql>create view [current product list] 
as select productid,productname from products where discontinued = no
mysql>select * from [current product list]

/*
create [temporary] table [if not exists] tbl_name
	{like old_tbl_name | (like old_tbl_name)}
*/
mysql>cteate table `new table` like old_test;

1.2 use
用途：选定database
语法：

//use database_name
mysql>use database_name;

1.3 drop
用途：删掉目标
语法

/*
drop [temporary] table [if exists]
	tbl_name [,tbl_name]...
	[restrict|caseade]	
 - restrict 确保无关联约束才删除
 - caseade  一并删除相关约束
*/
myspl>drop table table_name;

/*
drop view [if exists]
	view_name [,view_name]...
	[restrict|cascade]
*/
mysql>drop view view_name;

1.4 insert
用途：往表中添加新的数据
语法

/*
insert into table_name(field1,field2...) values(value1,value2...);
*/
mysql>insert into tbl_name(col1,col2) values('a',"abc");

1.5 select
用途：查询表
语法

/*
select
	column1,column2
from 
	table1
[inner | left | right] join table2 on conditions
where
	conditions
group by column1
having group_conditons
order by column1
limit offset,length;

from: sepecifies the table or view
join: gets related data from other tables based on specific join conditons
where: clause filters row
group by: clause groups a set of rows into groups and aggregate functions on each group
having: clause filters group based on groups defined by group by clause
order by: clause specifies a list of columns for sorting 
limit: constrains the number of returned rows
note-select and from clauses are required and other parts are optional
*/	
mysql>select `firstname`,`lastname`,`jobtitle` from `employees`;
mysql>select * from `employees`;
mysql>select
`officecode`,`city`,`phone`,`country`
from
`offices`
where
`country` in ('USA','France');  //key word "in"
mysql>select
`officecode`,`city`,`phone`,`country`
from
`offices`
where
`country`='usa' or `country`='france'; //key word "or"
mysql>select
`officecode`,`city`,`phone`,`country`
from
`offices`
where
`country` not in('usa','france'); //key word "not"
mysql>select
`ordernumber`,`customernumber`,`status`,`shippeddate`
from
`orders`
where
`ordernumber` in
(
select
`ordernumber`
from
(
select
`ordernumber`,sum(`quantityordered`*`priceeach`) as `totalcost`
from
`orderdetails`
group by
`ordernumber`
)
as a	//key word "as"
where
(a.`tatalcost`>60000)
)

1.6 like
用途：模板查询
语法

/*
%:match any srting
_:match any single character
*/
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`firstname` like 'a%';//key word '%'
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`lastname` like '%on';
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`lastname` like '%on%';
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`firstname` like 'T_m';
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`firstname` not like 'B%';//note '%' 不区分大小写
mysql>select
`productcode`,`productname`
from 
`products`
where
`productcode` like '%\_20%';//key word '\'
mysql>select
`productcode`,`productname`
from
`products`
where
`productcode` like '%$_20%' escape '$';//key word "escape"

二 MySQL内容关键字

2.1 index
用途：索引（在存储引擎层实现）
分类：

索引方法 \引擎	MyISAM	InnoDB	Memory
B-Tree	支持	支持	支持
HASH	不支持	不支持	支持
R-Tree	支持	不支持	不支持
Full-Text	支持	支持	不支持

例句解析：

mysql> show index from tbl_name;
/*result
table			表名称	
non_unique		索引不唯一
key_name		索引名称
seq_in_index	索引中列序列号
colummn_name	列名称
collation		列存储在索引的方式（A-升序；D-降序；null-无分类）
cardinality		索引中唯一值的数目的估计值（analyze table或myisamchk -a可以更新）
sub_part		如果列只是部分的编入索引，则是被编入索引的字符的数目。如果整列被编入索引，则为null
packed			指示关键字如何被压缩（null-没有压缩）
index_type		索引方法
comment			更多评注
*/

索引的原则：

• 较频繁的作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引
• 更新频繁的字段不适合创建索引
• 记录不超过2000的可以不考虑创建索引
  2.2 key
  用途：数据库的物理结构，用于约束（数据库的结构完整性）和索引（辅助查询）
  包含：primary key,unique key,foreign key等
  • primary key: 唯一标识数据库表中的每一条记录，非空唯一
  • unique key: 约束作用，可空唯一
  • foreign key: 约束作用（不能直接用，需要先用key定义）
    语法：

mysql> ... primary key(col_name),key user(userid),...

2.3 constraint
用途：设定约束
语法：

mysql>...constraint `customers_ibfk_l` foreign key (`salserepemployeenumber`) references `employees`(`employeenumber`)...

2.4 engine
用途：建表时制定存储引擎
语法：

mysql>...engine = InnoDB...

2.5 charset
用途：建表时指定字符集
语法：

mysql>...charset=latin1;

三 seciton querying data

3.1 select
3.2 select distinct

in order to：remove the duplicate rows
syntax:

select distinct
	columns
from 
	table_name
where
	conditions;

example:

//example1
mysql>select distinct
`lastname`
from
`employees`
order by `lastname`

//example 2
mysql>select distinct
`state`,`city`
from
`customers`
where
`state` is not null
order by `state`;

//example 3
mysql>select 
count(distinct `state`)
from
`customers`
where
`countyr`='usa`;

//example 4
mysql>select distinct
`state`
from
`customers`
limit 5;

similar syntax:
distinct vs group by
i distinct clause is a special case of the group by clause

note:
i null 也是distinct的操作对象之一
ii 多个列操作时，当且仅当每个列都一样时，才被disdinct视为重复的对象

四 section filtering data

4.1 where
order to:specify the search condition fro the rows returned by the query
syntax:

select
	select_list
from
	table_name
where
	search_conditon;

example:

//example1
mysql>select
`lastname`,`firstname`,`jobtitle`
from
`employees`
where
`jobtitle`='sales rep';

//example2
mysql>select
`lastname`,`firstname`,`jobtitle`
from
`employees`
where
`jobtitle`='sales rep' and `offcecode`='1';

note:
1 the follow table shows the comparision operators than you can use in where clause

operator	description
=	equal to
<> or !=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

4.2 and
order:logice operator
syntax:

where boolean_expression1 and boolean_expression2

example:

//example1
mysql>select
1=0 and 1/0;

//example2
mysql>select
`customername`,`country`,`state`
from
`customers`
where
`country`='usa' and `state`='ca';

note:
1 真值表

	true	false	null
true	true	false	null
false	false	false	false
null	null	false	null

4.3 or
in order to:logical operator
syntax:

boolean_expression_1 or boolean_expression_2

example:

//example1
mysql>select 1=1 or 1/0;

//example2
mysql>select
`customername`,`country`
from
`customers`
where
(`country`='usa' or `country`='france') and `creditlimit`>100000;

note:
1 真值表

	true	false	null
true	true	true	true
false	true	false	null
null	true	null	null

2 and 的优先级高于or
4.4 in
order to:logic operator
syntax:

select
	column1,column2...
from
	table_name
where
(expr|column1) in ('value1','value2'...);

example:

//example1
mysql>select
`officecode`,`city`,`phone`,`country`
from
`offices`
where
`country` in ('usa','france');

4.5 between
order to:logic operator
syntax:

expr [not] between begin_expr and end_expr

example:

mysql>select
`productcode`,productname`,`buyprice`
from
`products`
where
`buyprice` between 90 and 100;

//example2
mysql>select 
`ordernumber`,`requireddate`,`status`
from
`orders`
where
`requiredate` between ('2003-01-01' cast as date) and ('2003-01-31' cast as date)

note:
1 between 不能直接支持date数据，需要使用cast as将string转换成date

4.6 like
order to:模糊查询
i ‘_’ single character
ii ‘%’ any string
syntax:

expr where column1 like col_expr

example:

//example1
mysql>select
`employeenumber`,`lastname`,`firstname`
from
`employees`
where
`lastname` like '%a_';

//example2
mysql>select
`productcode`,`productname`
from
`products`
where
`productcode` like '%$_20%' escape '$';

note:
i \是转义符
ii escape可以定义其他符号为转义符

4.7 limit
order to:constrain the number of rows returned by select statement
syntax:

select
column1,column2,...
from
table
limit offset,count;

example:

//example1
mysql>select
`customernumber`,`customername`,`creditlimit`
from
`customers`
limit 1,10;

//get the highest and lowest values
mysql>select
`customernumber`,`customername`,`creditlimit`
from
`customers`
order by `creditlimit` desc
limint 5; 

note:
i the first offerset is 0,not 1
ii if there is only one argument,the argument must be count

4.8 is null
order to:test whether a value is null or not by using is null operator
syntax:

value is [not] null

example:

//example1
mysql>select 1 is null;

//example2
mysql>select `customername`,`country`,`salesrepemployeenumber`
from `customers`
where `salesrepemployeesnumber` is not null
order by `custumername`;

note:
i if the date or datetime column has not null constraint,you can use the is null to find the rows whitch is ‘0000-00-00’

section 4 sorting data
4.1 order by
order to:sort the result
syntax:

select column1,column2,..
from tbl1
order by column1 [asc|desc],column2 [asc|desc],...

example

//example1
mysql>select `contactfirstname`,`contactlastname`
from `customers`
order by `contactfirstname` asc,`contactlastname` desc;

//example2
mysql>select `ordernumber`,`orderlinenumber`,`quantityordere`*`priceeach` as `cost`
from `orderdetails`
order by `ordernumber`,`orderlinenumber`,`cost`;

//order by with custom sort order
mysql>select `ordernumber`,`status`
from `orders`
order by field(`status`,'inprocess','on hold','cancelled','resulved','disputed','shipped');

note:
1 field如果没有包含所有子项，则优先非被包含子项排列

section 5 joining tables

5.1 mysql alias
order to:alias and aliases to improve the readablility of complex queries
i table alias
ii column alias
syntax:

select [column1 | expression] as descriptive_name
from table_name;

table_name as table_alias;

example:

//example1
mysql>select concat_ws(' ',`firstname`,`lastname`)
as `full name`
from `employees`

//example2
mysql>select `ordernumber` `order no`,sum(`priceeach`* `quantityordered`) total
from `orderdetails`
group by `ordre no`
having `total` >60000;

//example3
mysql>select customername count(o.ordernumber) total
from `customers` c
inner join `orders` o on c.`customernumber`=o.`custmernumber`
group by `customername`
order by `total` desc;

5.2 mysql join
orderto:give an overview of joins

id	pattern
1	divot
2	brick
3	grid

id	pattern
A	brick
B	grid
C	diamond

i cross join

ii inner join

iii left join

iv right join

syntax

//cross join
select *
from t1
	cross join t2;
	
//inner join
select column_list
from t1
inner join t2 on join_conditon1
inner join t3 on join_condition2
...
where where_conditons;
	
//left join(左边为主表）
select t1.c1,t1.c2,t2.c1,t2.c2
from t1
	left join t2 on t1.c1=t2.c1;	

example:

//inner join
mysql>select productcode,productname,textdescription
from products t1
	inner join productlines t2 on t1.productline=t2.prodcutline;

mysql>select productcode,productname,textdescription
from products
	inner join productlines using(productline);

mysql>select ordernumber,productname,msrp,priceeach
from products p
	inner join orderdetails o using(productcode)
	and p.msrp>o.priceeach
where p.productcode='s10_1678'

//left join
mysql>select o.ordernumber,customernumber,productcode
from orders o
	left join orderdetails d using(ordernumber) and d.productcode='s10_1678'

note
i 不同表含有同名行需要点名主表
ii on后面带有and条件时，显示主表信息以及从表and条件下的信息，其他信息显示null

5.2 self join
orderto:joins a table to itself using table alias
syntax:

inner join

example:

mysql>select
concat(m.lastname,',',m.firstname) as `mannager`,
concat(e.lastname,',',e.firstname) as `derect report`
from employees e 
 	inner join employees m on m.employeenumber=e.reportsto
order by mannager;

mysql>select ifnull(caoncat(m.lastname,',',m.firstname),'top manager') as manager,concat(e.lastname,','e.firstname) as 'direct report'
from employees
	left join employees m on m.employeenumber = e.reportsto
order by manager desc;

section 6
6.1 group by
orderto:show you how to group rows into group based on columns or expressions
syntax:

select c1,c2....
from table
where conditon
gorup by c1,c2...

example:

mysql>select status
from orders
group by status;

mysql>select status,count(*)
from orders
group by status;

mysql>select status,sum(quantityordered*priceeach) amount
from orders
	inner join orderdetails using(ordernumber)
group by stutas;

mysql>select year(orderdate) 'year',sum(quantityordered*priceeach) total
from orders
	inner join orderdetails using(ordernumber)
where status='shipped'
group by year
having year>2003;

6.2 having
orderto:filter the groups by a specific conditon
syntax:

expr 
having conditon;

example

mysql>select ordernumber sum(quantityordered) itemscount,sum(priceeach*quantityordered) total
from orderdetails
group by ordernumber
having total>1000 and itemscount>600

6.3 rollup
orderto:generate multiple grouping sets considering a hierarchy between columns specified in the group by clause(总计）
syntax:

select list
from table
group by c1,c1,c3 with rollup;

example

mysql>select 
	if(grouping(orderyear),'all years',orderyear) order year,
	if(grouping(rpductline),'all product lines',productline) productline,
	sum(ordervalue) totalordervalue
from sales
group by orderyear,productline with rollup;

section 7
7.1 subquery
orderto:嵌套
syntax:…
example:

mysql>select lastname,firstname
from employees
where officecode in(
select officecode
from offices
where country='usa');

mysql>select customernumber,checknumber,amount
from payments
where amount=(
	select max(amount)
	from payments);

mysql>select customername
from customers
where customernumber not in(	
	select distinct customernumber
	from orders);

mysql>select max(items),min(items),floor(avg(items)),avg(items)
from (select ordernumber,count(ordernumber) as items
from orderdetails
group by ordernumber) as lineitems;

mysql>select ordernumber,count(ordernumber) as items
from orderdetails
group by ordernumber;

mysql>select productname,buyprice
from products p1
where buyprice>(select avg(buyprice)
	from products
	where productline=p1.productline);

mysql>select customernumber,customername
from customers
where exists(select ordernumber,sum(priceeach*quantityordered) total
	from orderdetails
		inner join orders using(ordernumber)
	where customernumber=customers.customernumber
	group by ordernumber
	having total>60000;

note:
i 嵌套表必须加以别名
7.2 derived table
orderto:introduce you the derived table concept and show you how to use it to simplify complex queries
syntax:…
example:

mysql>select productname,sales
(select productcode,round(sum(quantityordered*priceeach)) sales
from orderdetails
	inner join orders using(ordernumber)
where year(shippeddate)=2003
group by productcode
order by sales desc
limit 5) top5products2003
inner join products using(productcode)

mysql>select customergroup,count(cg.customergroup) groupcount
from(select customernumber,round(sum(quantityordered*priceeach)) sales,
(case
	when sum(quantityordered*priceeach)<10000 then 'silver'
	when sum(quantityordered*priceeach) between 10000 and 100000 then 'gold'
	when sum(quantityordered*priceeach)>100000 then 'platinum'
	end) customergroup
	from orderdetails
		inner join orders using(ordernumber)
	where year(shippeddate)=2003
	group by customernumber) cg
group by cg.customergroup;

7.3 mysql cte
orderto:explain you the common table expression concept and show you how to use cte for querying data from tables(预定义明确语句）
syntax:

with cte_name (column_list) as(
query
)
select * from cte_name;

example:

mysql>with customers_in_usa as(select customername,state
from customers
where country = 'usa')
select customername
from customers_in_usa
where state='ca'
order by customername;

mysql>with recursive cte_count(n) as (slect 1
union all
select n+1
from cte_count
where n<3)
select n
from cte_count;

mysql>with recursive cte_count (n) as(select 1
union all
select n+1
from cte_count
where n<3)
slect n
from cte_count;

mysql>WITH RECURSIVE employee_paths AS  ( SELECT employeeNumber,reportsTo managerNumber,officeCode, 1 lvl   
FROM employees   
WHERE reportsTo IS NULL     
UNION ALL     
SELECT e.employeeNumber,e.reportsTo,e.officeCode,lvl+1     
FROM employees e     
	INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,managerNumber,lvl,city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;

note:
i the number of columns in the query must be the same as the number of columns in the column_list

section8 using set operators
8.1 union and union all
orderto:combine tow or more result sets of multiple queries into a single resualt set
syntax:

select column_list
union [distinct|all]
select column_list
union [distinct|all]
...

example:

mysql>select concat(firstname,' ',lastname) from employees
union
select concat(contactfirstname,'',contactlastname) from customers;

8.2 intersect
orderto:
show you a couple of ways to simulate the intersect operator
syntax:

(select column_list from t1)
interset
(select column_list from t2);

example:

mysql>select distinct id from t1
where id in(select id from t2);

8.3 minus
orderto:
explain to you the sql minus operator and show you to simulate it in mysql

syntax:

select column_list_1 from table_1
minus
select columns_list_2 from table_2

example:

mysql>select id from t1
minus select id from t2;

Section 9. Modifying data in mysql
9.1 insert
orderto:
insert data into a table

syntax:

insert into table(colunm_list) values(value_list),(value_list2),...

example:

mysql>insert into tasks(title,priortity) values('learn mysql',default);
mysql>insert into suppliers(suppliername,phone)
select customername,phone from customers 
	where contry='usa' and state='ca';
//using warning insteads error
mysql>insert ignore into subscribers(email) value('jonh@gmail.com'),('jane@ibm.com');

9.2 update
orderto:
update data in database tables

intax:

update [low_priority][ignore] table_name 
set column1=expr1,column2=expr2,...
[where condition];

example:

mysql>update employees set 
lastname='hill',
email ='mary.patterson@classicmodelcars.com'
where employeenumber=1056;
mysql>update customers set
salesrepemployeenumber=(
select employeenumber from employees
where jobtitle='sales rep'
order by rand()
limit 1)
where salesrepemployeenumber is null;
mysql>update employees inner join merits using(performance) set
	salary=salary+salary*percentage;

note:
1 the low_priority modifier instructs the update statement to delay the update until there is no connection reading data from the table
2 the ignore modifier enables the update statement to continue updating cows even if errors occurrend.the rows that cause errors such as duplicate-key conflicts are not updated

9.3 Delete
orderto:remove data from one or more tables

syntax:

delete from table_name
[where conditon];

delete t1,t2 from t1
	inner join t2 on t2.ref=t1.id
where t1.id=1;

example:

mysql>delete from customers
	where country='france'
	order by customername
	limit 5;
mysql>create table roos(
	room_no int primary key auto_increment,
	room_name varchar(255) not null,
	building_no int not null,
	foreign key(building_no) references buildings(building_no) on delete cascade);

note:使用串联删除的前提是外键声明可串联删除
9.4 replace
orderto:replace the data to null

syntax:

replace into table_name(column_list) values(value_list)

replace into table_name	
	set col1=value1,col2=value2;

note:
i 如果表中含有声明的values，则对应的行其他values将改为null
ii 如果表中不含有声明的values，则等效于建立新的行
Section 10 mysql transaction
10.1 mysql transaction
orderto:learn the commit and rollback

sytax:

//set autocommit = off
start transaction;
...
commit;
rollback;

10.2 table locking
orderto:
locking the table

syntax:

lock tables table_name [read | write];

unlock tables;