03数据的增删改查

1、增加数据

　普通的插入

INSERT INTO info1 VALUES(1,'爱因斯坦','相对论')

有一种情况是只插入部分数据，如下：

INSERT INTO info1(id,name) VALUES(2,'爱迪生')

一次插入多条数据

INSERT INTO info1 VALUES(3,'图灵','人工智能'),(4,'达芬奇','美学'),(5,'尼采','美学');

查询数据

SELECT * FROM info1 WHERE id=2;

查询出来的数据可以作为数据添加到列表中

2、删除数据

　　删除部分数据

DELETE FROM info1 WHERE name='小泽玛利亚';

　　删除表中全部的数据1，表的结构不变，但不删除表的自增主键

DELETE FROM info1；

　　删除表中全部的数据2，表的结构不变。删除表的自增主键。它不像delete那样还要逐条看看再删，而是直接清空掉，可以联想到磁盘的格式化。如下：

TRUNCATE info1;

3、修改数据

　　有选择的修改1

UPDATE info1 SET contribution='哲学、美学' WHERE id=5;

 　　多列时以逗号隔开

UPDATE info1 SET named='达·芬奇',contribution='艺术家、发明家、医学家' WHERE id=4;

4、查询数据

4.1、简单查询

4.1.1查询全部

SELECT * FROM info1  

但是*的效率通常比写表头慢，正常开发一般不写*

4.1.2查询指定项

SELECT named,country FROM info1 

使用别名

SELECT named,contribution AS '贡献' FROM info1;

 可以进行数据列运算

SELECT named,lifetime-20 FROM info1;

去重复查询

SELECT DISTINCT contribution FROM info1;

 　

4.2、条件查询

4.2.1比较运算符　　>　　<　　>=　　<=　　=　　<>不等于

SELECT * FROM info1 WHERE lifetime>60;

4.2.2NULL关键字 查询

注意这里不能用=或<>来判断是否为空

SELECT * FROM info1 WHERE country IS NOT NULL;

SELECT * FROM info1 WHERE country IS NULL;

 查询空字符串

SELECT * FROM info1 WHERE named='';

 4.2.3逻辑运算符

SELECT * FROM info1 WHERE lifetime=67 AND country='意大利';

SELECT * FROM info1 WHERE lifetime=67 OR country='英国';

SELECT * FROM info1 WHERE NOT(lifetime=67 AND country='意大利');

 4.3区间查询

SELECT * FROM info1 WHERE lifetime BETWEEN 50 and 60;

4.4、集合查询

SELECT * FROM info1 WHERE id in(1,3,5);

  4.5 模糊查询

SELECT * FROM info1 WHERE named LIKE '%爱%'     #包含什么

SELECT * FROM info1 WHERE named LIKE '爱%'    #以什么开头

SELECT * FROM info1 WHERE named LIKE '%爱'    #以什么结尾

SELECT * FROM info1 WHERE named LIKE '__爱%'    #单个下划线表示一个字符

4.6 排序查询

SELECT * FROM info1 ORDER BY lifetime ASC; #ASC可以省略，默认正序

SELECT * FROM info1 ORDER BY lifetime DESC;    #倒序

SELECT * FROM info1 ORDER BY CONVERT(named USING gbk) DESC; #中文排序

#　注意：utf8编码中，中文不能作为排序依据；gbk支持中文排序

 　　附：查询编码集

SHOW VARIABLES LIKE 'char%';

 

 4.7分组查询 

SELECT SUM(income),country FROM info1 GROUP BY country;

SELECT SUM(income) AS sums,country FROM info1 GROUP BY country HAVING sums>100000;    #加入条件

SELECT MAX(income),country FROM info1 GROUP BY country;    #最大值

#SELECT AVG(income),country,GROUP_CONCAT(expr) FROM info1 GROUP BY country;    #平均,并且看看跟income与avg数相同的还有谁

WHERE 与 HAVING区别：

　　执行优先级从高到低：WHERE>GROUP BY>HAVING

4.8分页查询　　

SELECT * FROM info1 LIMIT 2,2;

4.9多表联合查询

SELECT * FROM person p,dept d WHERE p.dept_id=d.did;

　

　　如果不符合后面的条件，就不会显示出来。

4.10多表连接查询

SELECT * FROM person LEFT JOIN dept ON person.dept_id=dept.did;#左连接查询：左边表的数据都出来，即使跟右边不匹配

#右连接查询：LEFT换成RIGHT

#内连接查询：LEFT换成INNER，类似于多表联合查询

全连接查询

SELECT * FROM person LEFT JOIN dept ON person.dept_id=dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.dept_id=dept.did;
#龙洋与梦想部都出来了

 例题

#查询每个部门中最高工资和最低工资是多少，显示部门名称
SELECT MAX(salary),MIN(salary),dname FROM person LEFT JOIN dept on person.dept_id=dept.did GROUP BY dept_id;

 

 4.11子语句查询

1、用结果集作为表名来查询的形式

SELECT * FROM (SELECT * FROM person) AS aaa;

 2、求最大值行的其他列，例如求最大工资的那个人的姓名和薪水

SELECT * FROM person WHERE salary=(SELECT MAX(salary) FROM person);

3、求工资大于平均工资的人员

SELECT * FROM person WHERE salary>(SELECT AVG(salary) FROM person);

4、关键字

　　ANY

假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，

SELECT ...FROM ... WHERE a > ANY(...);
->
SELECT...FROM ... WHERE a > result1 OR a > result2 OR a > result3;

　　ALL

ALL关键字与any关键字类似，只不过上面的or改成and。即:

SELECT ...FROM ... WHERE a > ALL(...);
->
SELECT ...FROM ... WHERE a > result1 AND a > result2 AND a > result3;

　　EXISTS

SELECT ... FROM table WHERE  EXISTS (subquery);
该语法可以理解为：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行。

 4.12其他查询

　　1、临时表查询

　　#查询高于本部门平均工资的人员

SELECT * FROM person p1,
(SELECT dept_id,AVG(salary) as '平均工资' FROM person GROUP BY dept_id) as p2
WHERE p1.dept_id=p2.dept_id AND p2.`平均工资`<p1.salary; 

　　2、判断查询

#根据工资高低，将人员划分为两个级别，分别为高端人群和低端人群
#显示效果：姓名、年龄、性别、工资、级别

SELECT p.name,p.age,p.sex,p.salary, 
IF(salary>10000,'高端人群','低端人群') AS '级别'
FROM person p;

多条件判断

语法如下

#语法一:
SELECT
    CASE WHEN STATE = '1' THEN '成功'
         WHEN STATE = '2' THEN '失败'
         ELSE '其他' END 
FROM 表;
 
#语法二:
SELECT CASE age
           WHEN 23 THEN '23岁'
           WHEN 27 THEN '27岁'
           WHEN 30 THEN '30岁'
        ELSE '其他岁' END
FROM person;

#根据工资高低，统计每个部门人员收入情况，划分为富人、小资、平民、屌丝 四个级别，要求统计四个级别分别有多少人？

SELECT dname,
    SUM(CASE WHEN person.salary>10000 THEN 1 ELSE 0 END) AS '富人',
    SUM(CASE WHEN person.salary BETWEEN 5000 AND 10000 THEN 1 ELSE 0 END) AS '小资',
    SUM(CASE WHEN person.salary BETWEEN 3000 AND 5000 THEN 1 ELSE 0 END) AS '平民',
    SUM(CASE WHEN person.salary<3000 THEN 1 ELSE 0 END) AS '屌丝'
FROM dept,person WHERE dept.did=person.dept_id GROUP BY dept.did;