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;

3、左右链接

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

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

4、求数据个数count(*)

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

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

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

6、关联

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='王五';

7、排序

SELECT field1, field2,...fieldN table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]]

8、分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

二、更新

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

三、删除

DELETE FROM table_name [WHERE Clause]