sql常用语句

select * from person
select distinct name from person
select * from person where name='huangcongcong'
select * from person where year>1991
select * from person order by id
select * from person limit 5
select * from person name like "%cong%"
select * from person name like "[!hc]%"
select * from person where name in('huangcongcong','huangbingbing')
select * from person where name between 'huangbingbing' and 'huangcongcong'
select name as xingming from person
select * from person where name is null

select * into person_back from person //mysql create table person_back (select * from person)

insert into person (name,year) values('huangbingbing',1992)

update person set year=1990 where name='huangbingbing'

delete from person where name='huangcongcong'
delete * from person
delete from person

create database my_db
create table person
(
    id int not null primary key check(id>0) auto_increment,
    name varchar(50) not null,
    address varchar(50) default 'shanghai',
    city varchar(50),
    foreign key (id) references order(id)
)

alter table person
add unique (id)

alter table person
drop index id

alter table person
add primary key(id)

alter table person
drop primary key

alter table person
add foreign key (id) references order(id)

alter table person
drop foreign key

alter table person
add check(id>0)

alter table person
drop constraint check

alter table person
add constraint chk_person check(id>0)

alter table person
drop constraint chk_person

alter table person
alter city set default 'shenzhen'

alter table person
alter city drop default

create index personIndex
on person name

alter table person
drop index personIndex

alter table person
auto_increment=100

create view personView
select * from person

 

select e_name from employees_china
union
select e_name from employees_usa

select e_name from employees_china
union all
select e_name from employees_usa

select persons.lastname, persons.firstname, orders.orderno
from persons
inner join orders
on persons.id_p=orders.id_p
order by persons.lastname

select persons.lastname, persons.firstname, orders.orderno
from persons
left join orders
on persons.id_p=orders.id_p
order by persons.lastname


select persons.lastname, persons.firstname, orders.orderno
from persons
right join orders
on persons.id_p=orders.id_p
order by persons.lastname

select persons.lastname, persons.firstname, orders.orderno
from persons
full join orders
on persons.id_p=orders.id_p
order by persons.lastname