sql cheat sheet

CREATE DATABASE DATA_NAME
drop database database_name


create table person
(
	lastname varchar,
	firstname varchar,
	address varchar,
	age int
)


alter tale table_name add column_name data_type
alter table table_name ddrop column_name datatype
drop table table_name


create index person_index
on person ( lastname, firstname )

creaete unique indeix personindex
on person( lastname desc )

drop index person personindex

insert into persons values ( 'hussein', 'saddam', ' white house' )
insert into persons( lastname, firstname, address )
values ( 'hussein', 'saddam', 'white house' )

update person set address = 'ups' whwere last name = 'hussein'


delete from person where lastname = 'hussein'

truncate table person

select lastname,firstname from persons
select * from persons
select distincet lastname,firstname from persons
select * from persons where sex='female'
select * from persons where year > 1970
select * from persons where firstname = 'saddam' and lastname = 'hussein'
select * from persons where firstname = 'saddam' or lastname = 'hussein'

select * from persons where ( firstname = 'tove' or firstname = 'stephen' )
and lastname = 'swendson'

select * from persons where firstname like '0%'
select * from persons where firstname like '%a'
select * from persons where firstname like '%1a%'

select * from persons where lastname in ( 'hansen', 'pettersen' )

select * from persons order by lastname
select firstname, lastname f rom persons order by lastname desc

select company, ordernumber from orders order by company
desc, ordernumber asc

select company, sum( amount) from sales group by company


select company, sum( amount ) from sales group by company having sum( amount ) > 1000

select lastname as family, firstname as name from persons
select lastname,firstname from person as  employees 



select employees.name, orders.product from employees inner join orders
on employees.employee_id = order.employee_id

select empoyees.name, orders.product from employees left join orders
on empoyees.employee_id = orders.employee_id

select employees.name, orders.product from employees right join ordoers on employees.employee_id = orders.employee_id


select e_name from employees_norway union 
select e_name from employees_usa

select e_name from empoyees_norway
union all
select e_name from employees_usa


select * into persons_backup from persons
select person.* into persons in 'backup.db' from persons where city='sandnes'

create view [current product list] as select productid, productname from products where discontinued = no

已有 0 人发表留言，猛击->>这里<<-参与讨论

ITeye推荐