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;
转载于:https://www.cnblogs.com/start20180703/p/10273209.html
