Mysql学习——数据库操作语言(DML) (1)

数据库操作语言DML
查询表中的所有数据

SELECT * FROM 表名;

查询指定的列

SELECT 列名1,列名2 FROM 表名;

插入数据

INSET INTO 表名（列名１，列名２）VALUES(列值１，列值２);

插入多条

INSERT INTO 表名(列名1,列名2…) VALUES(列值1,列值2),(列值1,列值2)…;

更新操作
UPDATE 表名　SET 列名1=列值1, 列名2=列值2… WHERE 列名=值;

修改数据库密码

mysqladmin -u root -p password 123456

删除制定某一条数据

DELETE FROM 表名　WHERE name=‘zwz’;

删除所有数据

TRUNCATE TABLE 表名;

DELETE 与 TRUNCATE的区别:
DELETE可以删除指定数据也能删除所有数据 TRUNCATE只能删除所有数据
DELETE删除表中的数据,表结构还在;删除后数据还可以找回
TRUNCATE删除是把表直接DROP掉,然后再创建一个同样的新表.删除的数据不能找回.执行速度比DELETE快

条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用一些运算符以及关键字

BETWEEN…AND; 值在什么范围
IN(set);
IS NULL; (为空)
IS NOT NULL; (不为空)
AND; 与
OR; 或

NOT; 非

举例：
查询性别为男,并且年龄为20的学生记录

SELECT * FROM students WHERE gender=‘男’ AND age=20;

查询学号为1001 或者 名为 zs 的记录

SELECT * FROM students WHERE id=1001 OR name=‘zs’;

查询学号为1001 , 1002 ,1003的记录

SELECT * FROM students WHERE id IN(1001,1002,1003) ;

查询年龄为null的记录

SELECT * FROM students WHERE age IS NULL;

查询年龄在18-20之间的学生记录

SELECT * FROM students WHERE age>= 18 AND age<=20;

SELECT * FROM students WHERE age BETWEEN 18 AND 20;

查询性别非男的学生记录

SELECT * FROM students WHERE gender != ‘男’;

查询姓名不为null的学生记录

SELECT * FROM students WHERE name IS NOT NULL;

模糊查询
根据指定的关键字进行查询
使用LIKE关键字后跟通配符

通配符
_:任意一个字母
%:任意0~n个字母

查询姓名由5个字母构成的学生记录
可以事先自己添加好各种数据
5个字母就是5个下划线

SELECT * FROM students WHERE name LIKE ‘_____’;

查询姓名由5个字母构成,并且第5个字母为’s’的学生记录
SELECT * FROM students WHERE name LIKE ‘____s’;

查询姓名以’m’开头的学生记录
SELECT * FROM students WHERE name LIKE ‘m%’;

查询姓名中第二个字母为’u’的学生记录
SELECT * FROM students WHERE name LIKE ‘_u%’;

查询姓名中包含’s’字母的学生记录
SELECT * FROM students WHERE name LIKE ‘%s%’;

正则表达式匹配查询
基本语法:

字段名 REGEXP ‘匹配方式’

^ 匹配以特定字符或者字符串开头的记录
$ 匹配以特定字符或字符串结尾的记录
. 匹配任意字符串中任意一个字符,包括回车和换行符
[字符集合] 匹配’字符集合’中任意一个字符
[^字符集合] 匹配除’字符集合’以外任意一个字符
S1|S2|S3 匹配S1、S2、S3中的任意一个字符串

• 匹配多个该符号之前的字符,包括0个或者1个

• 匹配多个该符号之前的字符，包括1个（至少有1个）
  字符串{N} 匹配字符串出现N次
  字符串{M,N} 匹配字符串出现至少M次,最多N次

匹配指定字符中的任意一个
select * from students where name regexp ‘[zsl]’;

匹配以指定的字符开头和结束的字符
正则表达式中,^表示字符串的开始位置,$表示字符串的结束位置。selectaddressfromstudentswherenameregex{p}^{\prime l}..i$’;

字段控制查询
去除重复记录
查询所有学生name信息,去除重复信息

SELECT DISTINCT name FROM students;

把查询字段的结果进行运算,必须都要是数值型

SELECT *,字段1+字段2 FROM 表名;

排序
关键字 ORDER BY
排序类型(默认是ASC))
升序ASC 从小到大
降序DESC 从大到小

查找工作表按工资从大到小,id从小到大

SELECT * FROM employee ORDER BY salary DESC,id ASC;

聚合函数
对查询的结果进行统计计算
常用聚合函数
COUNT():统计指定列不为NULL的记录行数;
MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

COUNT 使用
查询employee表中记录数:
SELECT COUNT(*) FROM employee;

查询员工表中月薪大于2500的人数:

SELECT COUNT(*) FROM employee WHERE salary > 2500;

统计月薪与绩效之和大于5000元的人数:

SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;

查询有绩效的人数，和有管理费的人数:

SELECT COUNT(performance),COUNT(manage) FROM employee;

SUM 使用
查询所有雇员月薪和
SELECT SUM(salary) FROM employee;

查询所有雇员月薪和,以及所有雇员绩效和

SELECT SUM(salary),SUM(performance) FROM employee;

查询所有雇员月薪+绩效和

SELECT SUM(salary+IFNULL(performance,0)) FROM employee;

AVG
统计所有员工平均工资
SELECT AVG(salary) FROM employee;

MAX和MIN
查询最高工资和最低工资
SELECT MAX(salary),MIN(salary) FROM employe

分组查询　GROUP BY
按性别分组查询
SELECT gender FROM employee GROUP BY gender;

GROUP_CONCAT(字段名)可以作为一个输出字段来使用
表示分组之后,根据分组结果,使用group_concat()来 放置每一组的某字段的值的集合
SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;

GROUP BY +聚合函数
查看部门平均薪资
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;

查看每个部门的最高薪资

SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;

查询每个部门的部门名称和每个部门的人数

SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;

查询每个部门的部门名称以及每个部门工资大于1500的人数

SELECT department,GROUP_CONCAT(salary),COUNT(*) FROM employee WHERE salary>1500 GROUP BY department;

GROUP BY + having
用分组查询后指定一些条件来输出查询结果
having作用和where一样,但是having只能用于GROUP BY
筛选出工资大于9000的同事，部门，总和

SELECT department,GROUP_CONCAT(salary),SUM(salary)
FROM employee
GROUP BY department
HAVING SUM(salary)>9000;