mysql常用命令

一、查询select

1.查询以c开头的内容like

SELECT * from student_table s where sname like ‘c%‘ 

2.取唯一值distinct，只能对某一列值进行唯一取值，一般distinct用来查询不重复记录的条数

SELECT DISTINCT s.sname from student_table s LEFT JOIN stu_course sc on s.student=sc.student WHERE sc.c=1 or sc.c=2;

二、左右链接

1.左连接left join

左连接从左表产生一套完整的记录，和右表匹配的记录。如果右边没有匹配记录，右侧结果集字段为null

SELECT s.sname from student_table s LEFT JOIN stu_course sc on s.student=sc.student where sc.c=2;

三、求数据个数count(*)

SELECT COUNT(*) from stu_course WHERE stu_course.grage>20

四、已知成绩，求及格人数，不及格人数

SELECT class,
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) as ‘及格人数‘,
SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END) as ‘不及格人数‘
FROM tb1
GROUP BY class

五、关联

SELECT DISTINCT product.pname FROM product LEFT JOIN orders on product.productid=orders.productid LEFT JOIN customer on customer.customerid=orders.customerid and customer.cname=‘李四‘ WHERE customer.customerid is null;

SELECT t1.pname as ‘商品名称‘,t2.count as ‘数量‘,t2.count *(t1.oldprice-t1.nowprice) as ‘节省的钱‘ from product t1,orders t2,customer t3 where t1.productid=t2.productid and t2.customerid=t3.customerid and t3.cname=‘王五‘;

mysql常用命令

原文：https://www.cnblogs.com/daqieing/p/10558155.html