MySQL 和 sql 语句:
DML:数据操纵语言,insert、delete 、update、select 即增删改查操作
DDL :数据定义语言,create 、alter、drop 操作 ,不能roolback操作,一旦执行无法回退
DCL : grant 、deny 、revoke
第三方GUI工具: SQLyog、 Navicat、datagrip
1、基本查询:
查询所有的列:select * from vendors;
查询指定的列:select * from vend_id,vend_name,vend_address,vend_city from vendors;
查询时需要显示表中的所有列,尽量避免使用通配符 (*),而要采用写出所有列名的方式进行查询,因为采用通配符查询会降低程序的查询性能。
去除重复记录:select distinct vend_id from products;
分页: select * from products limit 5;
select * from products limit 0,5; 第一页
select * from products limit 5,5; 第二页
排序:降序: select * from products order by prod_price desc;
升序: select * from products order by prod_price asc ;
多列排序:select * from products order by prod_price asc, prod_name asc;
2、过滤查询:
查询产品价格在2-10之间的产品:select * from products where prod_price >= 2 and prod_price <= 10;
select * from products where prod_price between 2 and 10;
查询产品价格不等于2.5的所有产品:select * from products where prod_price <> 2.5;
selecr * from products where prod_price != 2.5;
查询没有电子邮件信息的客户:select * from customers where cust_email is null;
查询有电子邮件信息的客户:selecrt * from customers where cust_email is not null;
查询由供应商1001和1003制造并且价格在10元以上的产品:
select * from products where (vend_id = '1001' or vend_id = '1003') and produce_price > 10;
select * from products where vend_id in ('1001','1003') and prod_price >10;
查询不是由供应商1001和1003制造的产品:
select * from products where vend_id not in ('1001','1003');
3、模糊查询:
"_" 通配符代表一个字符;
“%” 通配符代表0个或一个或任意多个字符
查询产品名称中以jet开头的产品: select * from products where prod_name like 'jet%';
不要过度使用 like 通配符如果其他操作符可以完成就使用其他操作符;
通配符搜素使用的时间比其他搜索的时间长;
4、更多基本查询:
列的别名: select vend_id as '供应商编号' from products;
算数运算:select quantity, item_price, quantity * item_price as '总价' from orderitems;
5、文本处理函数:
left() 返回左边指定长度的字符:select prod_name, left(prod_name , 2) from products;
right()返回右边指定长度的字符:select prod_name, right(prod_name , 5)from products;
length() 返回字符串的长度:select prod_name , length(prod_name) from products;
lower() 将字符串转换为小写:select prod_name , lower(prod_name) from products;
upper() 将字符串转换为大写:select prod_name , upper (prod_name) from products;
ltrim() 去掉字符串左边的空格:selecrt prod_name ,ltrim(prod_name) from products;
rtrim() 去掉字符串右边的空格:selecrt prod_name ,rtrim(prod_name) from products;
trim() 去掉左右两边的空格:select prod_name , trim(prod_name) from products;
字符串连接:select concat ('i love ' , cust_name) as 'Message' from customers;
6、聚合函数 min() max() count() sum() avg()
聚合函数常用于统计数据使用
聚合函数统计时忽略值为null 的记录;
查询商品价格最高的产品:select max (prod_price) from products;
查询商品价格最低的产品:select min (prod_price) from products;
查询商品价格总和:select sum (prod_price) from products;
查询平均价格:select avg (prod_price) from products;
查询客户数量:select count(*) from customers;
select count (cust_email) from customers;
7、分组统计
获取每个供应商提供的产品数量:select vend_id,count(*) from pruducts group by vend_id;
获取提供产品数量大于2的供应商:select vend_id,count(*) from pruducts group by vend_id having count( *)>2
having 语句用于group by的过滤,where用于分组前过滤
获取产品提供产品数量大于等于2并产品价格大于10的供应商:
select vend_id,count(*) from products where prod_price >10 group by vend_id having count( *) >= 2
8、查询语句顺序: select 、from、 where、 group by、 having、 order by 、 limit
9、子查询:
从订单表中获取订单编号:
select order_num from orderitems where prod_id = 'TNT2';
根据订单编号获取客户id:
select cust_id from orders where order_num in('20005','20007');
子查询:
SELECT cust_name FROM customers WHERE cust_id
IN (SELECT cust_id FROM orders WHERE order_num
IN(SELECT order_num FROM orderitems WHERE prod_id = "TNT2")
);
获取每个客户下的订单数量:
select cust_id,cust_name,
(select sount(*) from orders where orders.cust_id = customers.cust_id)
from customers;
10、联接查询
等值查询:select ts.id as 'stuid' , stu_name, tc.id as 'class_id' ,class_name
from t_stuednt as ts, t_class as tc
where ts.class = tc.id;
内联接查询:select ts.id as 'stuid' , stu_name ,tc.id as 'class_id' , class_name
from t_student as tc
inner join t_class as tc
on ts.class_id = tc.id;
左联接查询:select ts.id as 'stuid' , stu_name,tc.id as 'class_id' , class_name
from t_student as ts
left join t_class as tc
on ts.class_id = tc.id;
右联接查询:select ts.id as 'stuid' , stu_name,tc.id as 'class_id' , class_name
from t_student as ts
right join t_class as tc
on ts.class_id = tc.id;
11、组合查询:
查询所有的用户和公司,并在一个结果集中显示:
select id,name,createmine from t_user
union
select id, name, createtime from t_company;
查询所有的用户和公司,并在一个结果集中按照创建时间(createtime)降序显示:
select id, name, createtime from t_user
union
select id,name ,createtime from t_company
order by createtime desc;
- union 必须由两条或两条以上的select 语句组成,语句之间使用union 分割;
- union 的每个查询必须包含相同的列,表达式或聚合函数;
- 列的数据类型必须兼容:类型不必完全相同,但是必须时相互可以转换的;
- union查询会自动去除重复的行,如果不需要此特性,可以使用union all;
- 对union 结果进行排序,order by 语句必须在最后一条 select 语句之后;