mysql 的查询

语法：
select 属性列表
where 条件表达式 ———–按指定的条件查询
group by 属性名1 HAVING条件表达式2———–对指定的字段分组
order by 属性名2 asc|desc————-对指定的字段排序
属性列表和属性名:要查询的字段名称
where 表示指定查询条件
group by 对指定的字段分组
order by 对指定的字段进行排序

1、在单表上查询所有数据

select * from orders; *号代表查询所有

2、查询某个指定字段

select cust_id,order_date from orders;

3、where 查询指定字段

等于

select cust_id,order_date from orders where cust_id=10001;

小于或者等于

select cust_id,order_date from orders where cust_id<=10003;

大于或者等于

select cust_id,order_date from orders where cust_id>=10003;

大于

select cust_id,order_date from orders where cust_id>10003;

小于

select cust_id,order_date from orders where cust_id<10003;

不等于

select cust_id,order_date from orders where cust_id != 10003;

排除掉

select cust_id,order_date from orders where cust_id <> 10005;

指定范围

select cust_id,order_date from orders where cust_id between 10001 and 10004;

指定集合

select cust_id,order_date from orders where cust_id in(10001,10004); 包含10003和10004

select cust_id,order_date from orders where cust_id not in(10001,10004); 不包含10001和10004

匹配字符

select cust_id,order_date from orders where cust_id like ‘10004’;

select cust_id,order_date from orders where cust_id not like ‘10004’;

判断字符值空或者非空

select * from vendors where vend_state is null;

select * from vendors where vend_state is not null;

多条件查询

select * from orders where cust_id=10003 or cust_id=10004; 或

select * from orders where cust_id>10003 and cust_id<10005; 与

去掉重复—-distinct

select distinct cust_id from orders;

对单个字段进行分组—-group by

select * from student group by address;

使用group_concat将字段分组显出来

select group_concat(name),address from student group by address; 按地址分组，显示属于这个地址的所有人

集合函数

count() 统计记录的条数

select count(*) from vendors;

sum() 计算字段值的总和

select sum(vend_id) from vendors;

avg() 计算字段的平均值

select avg(vend_id)_ from vendors;

max() 查询字段的最大值

select max(vend_id)_ from vendors;

min() 查询字段的最小值

select min(vend_id)_ from vendors;

多表查询

(1)内链接

select cust_name,cust_address,order_date,order_num from customers a ,orders b where a.cust_id=b.cust_id;

(2)外链接

select 属性列表名 from 表名1 left | right join 表名2 on 表名1.属性名=表名2.属性名

左链接
可以查询表名1,表名2里面所有的数据，表名1为主表，而表名2只能去匹配表名1的记录，查询之后会存在主表里面所有的记录

select customers.cust_id,order_date,order_num from customers left join orders on customers.cust_id = orders.cust_id;

custmoers为主表，orders为匹配表
通俗例子：orders为IT员工—>去customers财务部领取工资，进行匹配。

右链接
可以查询表名1，表名2里面所有的数据,表名2为主表，而表名1只能去匹配表名2的记录，查询之后会存在主表里面所有的记录

select orders.cust_id,cust_name,cust_address from customers right join orders on orders.cust_id=customers.cust_id；

orders为主表，customers为匹配表
通俗例子：customers为IT员工—>去orders财务部领取工资，进行匹配。

子查询

select * from customers where cust_id in(select cust_id from orders where cust_id=10003);

合并查询使用union 和union all
union 系统会将重复的去掉

select vend_id from vendors union select vend_id from products;

union all恰恰相反。不去重复

select vend_id from vendors union all select vend_id from products;

为表和字段名称取别名

select vend_id as userid from vendors 字段名称取别名
select vend_id from vendors a where a.vend_id=’1001′; 为表取别名

正则表达式regexp

select * from vendors where vend_name regexp ‘^An’; 以An开头的
select * from vendors where vend_city regexp ‘s$’; 以s结尾的
select * from vendors where vend_city regexp ‘.d’; 任何字符后面包含d
select * from vendors where vend_city regexp ‘d.’; d后面包含的任意字符
select * from vendors where vend_city regexp ‘[London]’; 只要包含中括号里面任意一个字符的都会被显示出来
select * from vendors where vend_city regexp ‘[^Paris]’; 匹配除了Paris以外的所有字符，也就是说Paris将被过滤掉了。
select * from vendors where vend_state regexp ‘MI|OH’; 匹配MI或者OH任意一个满足都会被显示出来。
select * from vendors where vend_state regexp ‘M*’; 匹配以包含M后面的任何字符。
select * from vendors where vend_name regexp ‘ll+’; 代表多个字符前面或者后面的任何字符
select * from vendors where vend_city regexp ‘d{1}’; 查询d出现过1次 或者N次re vend_city regexp 'd{1}'; 查询d出现过1次 或者N次