一、sql与计算机
把ziduan、信息存入数据库中，其关系是：
1、存放数据的是计算机
2、计算机里有两种主流存数据的手段：
2.1在磁盘中进行I/O操作，刻录文件，把电脑关了，再启动，数据还在
2.1数据库==>在磁盘中刻录数据
（两者其实都是刻录到磁盘中）
3、DBMS：这个会很合理的去给磁盘中写入数据存入数据，连接mysql这个服务
二、sql举例
SQL语句：
1、查询数据所有字段，指定多少条数据
SELECT * FROM goos LIMIT 20,5 ==>从第21行开始，找出5条
SELECT * FROM goos WHERE title like “包包” LIMIT 20,5
2、查询数据所有字段，指定多少条数据和按照关键字查询
SELECT * FROM goods WHERE price 500
3、%有任意的文字，like就是包含
SELECT * FROM goods WHERE title LIKE "%衣服%"
4、排序
SELECT * FROM goods WHERE title LIKE "%衣服%" ORDER BY pricee
SELECT * FROM goods WHERE title LIKE "%衣服%" ORDER BY pricee ASC
SELECT * FROM goods WHERE title LIKE "%衣服%" ORDER BY pricee DESC
5、关联==>基于一个语句的结果，再查另一个语句 on 是条件
select * from jdgoods inner join car on car.gid=jdgoods.gid where car.userid=${this.ctx.session.userid};
三、常用sql语句
1、增加数据
字段没有顺序要求，后面的值必须要和字段一一对应；
文本类型对应的值需要加引号，数字不需要引号。
INSERT INTO table_name (name, sex, age) VALUES('karen', '女', 25)
字段可以省略：
INSERT INTO table_name VALUES('karen', '女', 25)
2、删除数据
扫描整个表，如果当前记录能够让判断条件为true，则当前记录会被删除
比较符: = OR AND NOT
DELETE FROM table_name WHERE age=25;
DELETE FROM table_name WHERE age=25 and sex='女';
3、更新数据
UPDATE table_name SET age=18,sex='男' WHERE id=2;
4、查询数据
select * from jdgoods inner join car on car.jdgoodsid=jdgoods.id where car.userid=${this.ctx.session.userid};
查询表中所有数据, *是指所有字段，也可以写上具体的字段，多个字段之间使用逗号分开。
SELECT * FROM table_name;
SELECT sex FROM table_name;
SELECT name,age FROM table_name;
//where后面跟查询条件
//查询name为'karen'的所有数据
select * from teachers WHERE name='karen';
//模糊查询:
//包含关键词的: like'%关键词%'
//以关键词开头的: like'关键词%'
//以关键词结尾的: like'%关键词'
select * from tablename WHERE name LIKE '%karen%'
//具体可选值:in(88,66,22)
//年龄是10或者20或者30的数据
select * from tablename WHERE age in(10,20,30)
//查询后返回数据量:
//LIMIT 8,10; 从第8条开始返回10个
//LIMIT 20; 从第1条开始返回20个
select * from table_name WHERE name='karen' LIMIT start, num;;
//结果排序,默认升序:ORDER BY age ASC|DESC
//按照年龄从小到大
SELECT * FROM table_name WHERE 判断条件 ORDER BY age
SELECT * FROM table_name WHERE 判断条件 ORDER BY age ASC
//按照年龄从大到小
SELECT * FROM table_name WHERE 判断条件 ORDER BY age DESC
//最大值:max(字段)
//最小值:min(字段)
//平均值:AVG(字段)
//求和值:SUM(字段)
//计数:COUNT(字段)
//返回yuwen最大值
SELECT max(yuwen) FROM student
//返回yuwen是最大值的所有数据
SELECT * FROM student where yuwen=(SELECT max(yuwen) FROM student)
//返回yuwen是最大值的第一个数据
SELECT * FROM student where yuwen=(SELECT max(yuwen)FROM student) limit 1
//返回yuwen是小于平均值的所有数据
SELECT * FROM student where yuwen<(SELECT avg(yuwen) FROM student)
//返回yuwen是小于平均值的前10个数据
SELECT * FROM student where yuwen<(SELECT avg(yuwen) FROM student)
limit 10
//返回yuwen是小于平均值的前10个数据并按语文排序
SELECT * FROM student where yuwen<(SELECT avg(yuwen) FROM student) order by yuwen limit 10
//求所有员工的工资和
SELECT sum(gongzi) FROM user
//一共有多少条数据
SELECT COUNT(id) FROM user
//别名:as
//select 字段 as 别名 from tablename
//select 计算表达式 as 别名 from tablename
//区间: between start and end
查询总分
select *,yuwen+yingyu+shuxue+zonghe as total from student
查询总分并按总分排序
select *,yuwen+yingyu+shuxue+zonghe as total from student order by total DESC
zonghe在50到150分之间的人 查询总分并按总分排序
select *,yuwen+yingyu+shuxue+zonghe as total from student WHERE zonghe BETWEEN 50 AND 150 order by total DESC
zonghe在50到150分之间的人 查询总分并按zonghe排序
select *,yuwen+yingyu+shuxue+zonghe as total from student WHERE zonghe BETWEEN 50 AND 150 order by zonghe DESC
//分组查询: GROUP BY 字段
//查询学生分别来自哪些城市
SELECT city FROM student GROUP BY city
//查询每个城市的学生有多少
SELECT city,COUNT(id) FROM student GROUP BY city
//查询每个城市的语文最高分
SELECT city,max(yuwen) FROM student GROUP BY city
//查询每个城市的总分最高分
SELECT city,max(yuwen+shuxue+yingyu+zonghe) as total FROM student GROUP BY city
//查询男生yuwen总分和女生yuwen总分
SELECT sex,sum(yuwen) as yuwen FROM student GROUP BY sex
//查询男生yuwen平均分和女生yuwen平均分
SELECT sex,avg(yuwen) as yuwen FROM student GROUP BY sex
//多表查询
//内连接: 两个公司的员工表中 身份证一样的数据
select *from company1 inner join company2 on company1.idcard=company2. idcard;
//左连接：将join左边表全部数据都查询处理，join右边表不匹配的数据NULL填充
/* 将左边表所有数据都显示 */
select *from company1 left join company2 on company1.idcard=company2. idcard;
//右连接：将join右边表全部数据都查询处理，join左边表不匹配的数据NULL填充
/* 将左边表所有数据都显示 */
select *from company1 right join company2 on company1.idcard=company2. idcard;
//子查询
select city from ((SELECT name,yuwen,shuxue,city FROM student where city in('四川省','北京')) as xxx) group by city
