数据类型：integer varchar float double char text

SQL命令语句

1.创建表

1
2
create table 表名 (字段名称 数据类型 约束，字段名称 数据类型 约束.....)
create table person(id integer primary key,name varchar(10),age integer not null)

2.删除表

1
2
drop table 表名
drop table person</pre>

3.插入数据

1
2
3
insert into 表名[字段,字段...] values(值1,值2...)
insert into person(id,age) values(2,20)
insert into person values(2,"jane",20)

4.修改数据

1
2
update 表名 set 字段 = 新值 where 条件语句
update person set name = "jim" where id = 2

5.删除数据

1
2
delete from 表名 where 条件语句
delate from person where id = 2</pre>

6.查询语句

1
2
3
4
5
6
7
8
9
10
11
12
select 字段名 from 表名 where 查询语句 group by 分组的字段 having 筛选条件 order by 排序字段
select * from person
select id,name from person
select * from person where id = 1
select * from person where id < > 1
select * from person where id = 1 and age > 18
select * from person where name like "%小%"
select * from person where name like "_小%"
select * from person where name is null
select * from person where age between 10 and 20
select * from person where age >18 order by id
select * from person where age >18 order by desc</pre>

  1. group by分组与统计函数
    1
    2
    3
    4
    5
    select count(*) from people;
    select avg(age) from people;
    select sum(age) from people;
    select max(age) from people;
    select min(age) from people;

8.limit

1
2
3
4
//取前三条
select * from people order by age desc limit 3;
//从第三条取到第五条
select * from people order by age desc limit 2,3;

9.子查询

1
select * from categery where exists (select * from goods.cat_id = categery.cat_id);

10.内连查询

1
select boy.hid.boy.name.girl.hid.girl.name from boy inner join girl on boy.hid=girl.hid;

11.左连接右连接

1
2
select boy.hid.boy.name.girl.hid.girl.name from boy left join girl on boy.hid= girl.hid;
select boy.hid.boy.name.girl.hid.girl.name from boy right join girl on boy.hid= girl.hid;

12.union查询