一 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 更多评注
*/索引的原则：
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>6006.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;