一、查询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]