数据库常用指令关键词

例子以表table_test，数据为id，name，age

1.create table:创建表

例

create table table_test(
    `id` int(11) not null auto_increment,
    `name` varchar(255) not null,
    `age` int(11) not null,
    primary key(`id`)
)engine=innodb default charset=utf8 auto_increment=0;

2.insert into:插入数据

例

insert into table_test values(id,name,age);

3.where:条件判断

例

select * from table_test where age > 10;

4.between:在a与b值之间

例

select * from table_test where age between 10 and 20;

5.distinct:去重

例

select distinct name from table_test;

6.and:且

例

select * from table_test where age>10 and age<20;

7.or:或

例

select * from table_test where id<10 or id>20;

8.in:在...之中

例

select * from table_test where id in (10,20,30);

9.order by ... desc:降序排列，从高到低

例

select * from table_test order by age desc;

10.order by ... asc:升序排列，从低到高

例

select * from table_test order by age asc;

11.count:统计

例

select count(*) from table_test where age = 10;

12.max:最大值

例

select max(age) from table_test;

13.min:最小值

例

select min(age) from table_test;

14.limit r,n:表示从r行开始，查询n条数据

例

select * from table_test order by id desc limit 10,10;

15.limit n offset r:表示查询n条数据，从第r行开始

例

select * from table_test order by age asc limit 1 offset 5;

16.avg:平均值

例

select avg(age) from table_test;

17.group by:分组查询

例

select name,avg(age) from table_test group by name;

18.having:表示持有

例

select id,name,avg(age) from table_test group by id having avg(age)>10;

19.like:like与通配符搭配使用

例

select * from table_test where name like 'z%'; ——>name以z开头

20.多表查询，需要注意两个表的键值不能相同，如果相同需要做具体的区分

例

select table_test.id table_test2.id table_test.name table_test2.name from table_test,table_test2 where table_test.name = table_test2.name;

21.多层嵌套查询：

例

select * from table_test where name = (select mame from table_test where name = 'a');

22.not:非

例

select * from table_test where id not in (1,3,5);

23.any:符合SQL语句中的任意条件

例

select * from table_test where id>6 and id>any(select id from table_test where id = 4);

24.all:符合SQL语句中的所有条件

例

select * from table_test where id>6 and id<all(select id from table_test where id = 8);

25.not like:模糊查询，取反

例

select * from table_test where name not like 'z%';

26.year与now函数:

例

select name,year(now())-year(birthday) as age from table_test;

27.内连接:inner join为内连接，将两张表连接在一起；on表示执行条件

例

select * from table_test inner join table_test1 on table_test.id = table_test1.id;
（inner可以省略）
select * from table_test join table_test1 on table_test.id = table_test1.id;

28.左外连接:完整显示左边的表（table_test）,右边的表（table_test1）满足条件的显示不满足的显示NULL

例

select * from table_test left join table_test1 on table_test.id=table_test1.id;

29.右外连接:完整显示右边的表（table_test1）,左边的表（table_test）满足条件的显示不满足的显示NULL

例

select * from table_test right join table_test1 on table_test.id=table_test1.id;

30.union:将两张表合并

例

select * from table_test left join table_test1 on table_test.id=table_test1.id
union 
select * from table_test right join table_test1 on table_test.id=table_test1.id;

 

最后，感谢，总结来源https://blog.csdn.net/csdnnews/article/details/106798808

只为学习归纳，资源共享！