andrew7676
- 浏览: 188567 次
- 性别:
- 来自: 北京
-
最新评论
文章列表
1. 使用触发器
创建触发器(mysql5之前的版本)
create trigger newproduct after insert on products for each row select 'Product added';
删除触发器
drop trigger newproduct;
创建insert触发器(mysql5之前的版本)
create trigger neworder after insert on orders for each ro ...
1. 使用游标
创建游标
create procedure processorders()
begin
declare ordernumbers cursor for select order_num from orders;
end;
打开游标
open ordernumbers;
关闭游标
close ordernumbers;
创建带声明的游标
create procedure processorders()
begin
-- declare the cursor
declare ordernumbers cursor fo ...
1. 存储过程
存储过程：就是为了以后的使用而保存的一条或者多条SQL语句的集合。
1. 创建无参存储过程
create procedure productpricing()
begin
select avg(prod_price) as priceaverage from products;
end;
使用无参存储过程
call productpricing();
删除存储过程
drop procedure productpricing;
2. 创建含参存储过程
create procedure productpricing(
out ...
1. 视图
创建视图
create view productscustomers as select cust_name, cust_contact, prod_id from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num;
使用视图
select cust_name, cust_contact from productscustomers where prod_id = 'TNT2';
用视图 ...
1. 创建表
创建表
SQL> create table customers
(
cust_id int not null auto_increment,
cust_name char(50) not null ,
cust_address char(50) null ,
cust_city char(50) null ,
cust_state char(5) null ,
cust_zip char(10) null ,
cust_country char(50) null , ...
1. 更新数据
更新数据
SQL> update customers set cust_email = 'elmer@fudd.com' where cust_id = 10005;
更新多列
SQL> update customers set cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' where cust_id = 10005;
更新为null
SQL> update customers set cust_email = null where cust_id = 10005;
1 ...
1. 插入数据
插入完整的行
SQL> insert into customers values(null, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);
按字段插入行
SQL> insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values('Pep E. ...
1. 全文本搜索
使用全文本搜索
SQL> select note_text from productnotes where match(note_text) against('rabbit');
使用like也可以实现
SQL> select note_text from productnotes where note_text like '%rabbit%';
全文本搜索扩展查询
没有扩展查询
SQL> select note_text from productnotes where match(note_text) against('anvils ...
1. 组合查询
组合查询union
SQL> select vend_id, prod_id, prod_price from products where prod_price <= 5 union select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002);
组合查询union all
SQL> select vend_id, prod_id, prod_price from products where prod_price <= 5 union al ...
1. 联结表
创建联结，有where子句
SQL> select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
创建联结，没有where子句，返回的结果并不匹配
SQL> select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;
...
1. 使用子查询
使用子查询
1) select order_num from orderitems where prod_id = 'TNT2';
2) select cust_id from orders where order_num in (20005, 20007);
SQL> select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
SQL> select cust_name, cust_contact f ...
1. 分组数据
分组计数
SQL> select vend_id, count(*) as num_prods from products group by vend_id;
rollup关键字
SQL> select vend_id, count(*) as num_prods from products group by vend_id with rollup;
having过滤分组
SQL> select cust_id, count(*) as orders from orders group by cust_id having count(*) ...
1. 聚集函数
聚集函数
avg()函数
SQL> select avg(prod_price) as avg_price from products;
SQL> select avg(prod_price) as avg_price from products where vend_id = 1003;
count()函数
所有行计数(包括空值NULL)
SQL> select count(*) as num_cust from customers;
但一行计数(不包括空值NULL)
SQL> select count(cust_email ...
1. 数据处理函数
文本处理函数
upper()转换大写函数
SQL> select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;
soundex()发音类似函数
SQL> select cust_name, cust_contact from customers where cust_contact = 'Y. Lie';
SQL> select cust_name, cust_contact from customers where s ...
1. Java中static关键字
可以修饰类，属性，方法。
1. static修饰属性时，无论多少引用都公用一个属性。
一个成员变量是static的，可以通过 类名.成员变量名 来使用。
public class StaticTest {
public static void main(String[] args){
MyStatic myStatic = new MyStatic();
MyStatic.a = 10; // 类名，不是引用名
System.out.println(myStatic.a);
...