用于表的操作
-- 删除表
DROP TABLE user_info;
-- 创建表
CREATE TABLE IF NOT EXISTS user_info (
-- 11 表示列的默认显示宽度
id INT (11) PRIMARY KEY AUTO_INCREMENT,
-- 唯一约束
username CHAR (200) UNIQUE,
-- 总共3位, 小数2位
tall FLOAT (3, 2),
-- 枚举
gender ENUM ('男', '女') NOT NULL DEFAULT '男',
-- 5位字符, 定长字符串, 最多255位, 数据不足时会填充
`name` CHAR (5),
-- 100位字符, 变长字符串, 最多65535位, 数据不足时不会填充
signature VARCHAR (100),
-- 文本
address TEXT,
-- 日期时间
birthday DATETIME,
-- 时间戳
upd_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
/*
* TRUNCATE TABLE 语句用来删除/截断表里的所有数据
* 和 DELETE 删除所有表数据在逻辑上含义相同,但性能更快
* 类似执行了 DROP TABLE 和 CREATE TABLE 两个语句
*/
TRUNCATE TABLE user_info;
-- 添加字段
ALTER TABLE user_info ADD age INT;
-- 删除字段
ALTER TABLE user_info DROP age INT;
-- 修改字段
ALTER TABLE user_info CHANGE age newage TINYINT;
-- 修改表名
ALTER TABLE user_info RENAME TO new_user_info;
用于数据的操作
增删改
-- 插入一条数据
INSERT INTO user_info
VALUES
(
1,
'zhangsan',
1.69,
"男",
"张三",
"这是张三的个性签名",
"地址",
"2020-01-01",
NOW()
);
-- 插入多条数据, NOW()是当前时间
INSERT INTO user_info VALUES
(NULL, 'lisi', NULL, '男', '李四', NULL, NULL, NULL, NOW()),
(NULL, 'waner', NULL, '女', '婉儿', NULL, NULL, NULL, NOW()),
(NULL, 'xiaoying', NULL, '女', '小颖', NULL, NULL, NULL, NOW());
-- 按照指定字段插入数据
INSERT INTO user_info (id, NAME) VALUES (2, "李四");
-- 按条件修改/更新
UPDATE user_info SET tall = 1.68, address = '洛杉矶' WHERE id = 3;
-- 按条件删除
DELETE FROM user_info WHERE id=2;
-- 清空
DELETE FROM user_info;
/*
UPDATE 与 DELETE 语句可以使用 WHERE 与 LIMIT 子句进行筛选
*/
查找语句格式
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
[WHERE condition /*筛选数据行的条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的筛选行的条件*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start, row_count] /*结果限定*/
WHERE 子句查找
-- 建立学生表
CREATE TABLE `stu` (
`sid` char(6) NOT NULL COMMENT '学生学号',
`sname` varchar(50) DEFAULT NULL COMMENT '学生姓名',
`age` int(11) DEFAULT NULL COMMENT '学生年龄',
`gender` varchar(50) DEFAULT NULL COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
-- 插入数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
/*
= != <> < <= > >=
<=> 当比较的的两个值相等或者都为 NULL 时返回 true
BETWEEN...AND 是否满足一个区间范围, 等价于 >= <=
IN(data1,data2...) 条件的集合
AND
OR
NOT
IS NULL
IS NOT NULL
在 MySQL 中,NULL 值与任何其它值的比较(即使是 NULL)永远返回 NULL,即 NULL = NULL 返回 NULL 。
*/
-- 查询学生性别为女,并且年龄小于50的记录
SELECT sid, sname, age, gender FROM stu WHERE gender='female' AND age<50;
-- 查询学生性别为女,并且年龄大于等于50的记录
SELECT sid, sname, age, gender FROM stu WHERE gender='female' AND age>=50;
-- 查询学号为S_1001,S_1002,S_1003的记录
SELECT sid, sname, age, gender FROM stu WHERE sid IN('s_1001', 's_1002', 's_1003');
-- BINARY 来设定 WHERE 子句的字符串比较是区分大小写的
SELECT sid, sname, age, gender FROM stu WHERE BINARY sid IN('s_1001', 'S_1002');
-- 查询学号不是S_1001,S_1002,S_1003的记录
SELECT sid, sname, age, gender FROM stu WHERE sid NOT IN('s_1001', 's_1002', 's_1003');
-- 查询年龄为null的记录
SELECT sid, sname, age, gender FROM stu WHERE age IS NULL;
-- 查询年龄在20到40之间的学生记录
SELECT sid, sname, age, gender FROM stu WHERE age BETWEEN 20 AND 40;
-- 查询性别非男的学生记录
SELECT sid, sname, age, gender FROM stu WHERE gender!='male';
-- 查询姓名不为null的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname IS NOT NULL;
LIKE 子句模糊匹配
/*
LIKE 子句中使用百分号 % 来表示0个或多个任意字符
有些情况下若是中文,请使用两个百分号(%%)表示。
下划线 _ 表示单个任意字符。它常用来限制表达式的字符长度
单双引号的转义: \' \" '' ""
可加\表示, 也可“以二表一”
'%a' -- 以a结尾的数据
'a%' -- 以a开头的数据
'%a%' -- 含有a的数据
'_a_' -- 三位且中间字母是a的
'_a' -- 两位且结尾字母是a的
'a_' -- 两位且开头字母是a的
*/
-- 查询姓名由5个字母构成的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname LIKE '_____';
-- 查询姓名以“z”开头的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname LIKE 'z%';
-- 查询姓名中包含“l”字母的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname LIKE '%l%';
-- 查询姓名中包含“L”字母 (区分大小写) 的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname LIKE BINARY '%L%';
-- 查询姓名中第2个字母为“i”的学生记录
SELECT sid, sname, age, gender FROM stu WHERE sname LIKE '_i%';
/*
可通过 REGEXP 和 RLIKE 语句使用正则匹配
*/
字段控制查询
-- 创建员工表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
-- 插入员工数据
INSERT INTO emp values(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp values(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp values(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp values(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp values(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp values(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp values(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp values(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp values(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp values(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp values(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp values(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp values(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp values(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
/*
去除重复记录(两行或两行以上记录中系列的上的数据都相同),
例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,
那么会出现重复记录,那么想去除重复记录,需要使用 DISTINCT
*/
SELECT DISTINCT(sal) FROM emp;
/*
查看员工的月薪与奖金之和
因为sal和comm两列的类型都是数值类型,所以可以做加运算。
如果sal或comm中有一个字段不是数值类型,那么会出错。
*/
SELECT ename, sal, comm, sal+comm FROM emp;
/*
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,
所以结算结果可能会出现NULL。下面使用了把NULL换成数值0的函数IFNULL
*/
SELECT ename, sal, comm, sal+IFNULL(comm, 0) FROM emp;
/*
在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,
现在我们给这一列给出一个别名,为total
给列起别名时,是可以省略AS关键字的
*/
SELECT ename, sal, comm, sal+IFNULL(comm, 0) AS total FROM emp;
-- 别名的使用
SELECT e.ename, e.sal, e.comm FROM emp AS e;
/*
COALESCE(a,b,c)
与IFNULL(a,b)类似
*/
排序 ORDER BY
-- 创建部门表
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
-- 插入数据
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
-- 查询所有员工,按月薪升序排序【ORDER BY 字段 ASC】
SELECT * FROM emp ORDER BY sal ASC;
-- 查询所有员工,按月薪降序排序【ORDER BY 字段 DESC】
SELECT * FROM emp ORDER BY sal DESC;
-- 查询所有员工,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC, empno ASC;
-- 查询所有员工,按月薪升序排序,如果月薪相同时,按编号降序排序
SELECT * FROM emp ORDER BY sal, empno DESC;
-- WHERE 可以参与函数运算,ORDER BY 可以按别名排序
-- 先执行 WHERE 筛选, 再执行起别名, 最后排序
SELECT *, (sal+IFNULL(comm, 0)) total FROM emp WHERE (sal+IFNULL(comm, 0))>2000 ORDER BY total DESC;
-- 查询所有学生记录,按年龄升序排序
SELECT * FROM stu ORDER BY age;
-- 查询所有学生记录,按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
聚合函数
/*
聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
*/
/*
COUNT()
当需要纵向统计时可以使用COUNT()
COUNT(*)返回检索行的数目,不论其是否包含NULL值。
统计行数时可以用COUNT(1)或COUNT(主键)
*/
-- 查询emp表中记录数
SELECT COUNT(*) AS cnt FROM emp;
-- 查询emp表中有奖金的人数
-- 因为COUNT()函数中给出的是comm列,那么只统计comm列非NULL的行数。
SELECT COUNT(comm) cnt FROM emp WHERE comm > 0;
-- 查询emp表中月薪大于2500的人数
SELECT COUNT(*) FROM emp WHERE sal > 2500;
-- 统计月薪与奖金之和大于2500元的人数
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm, 0) > 2500;
-- 查询有奖金的人数,以及有领导的人数
SELECT COUNT(comm), COUNT(mgr) FROM emp;
/*
SUM()和AVG()
当需要纵向求和时使用SUM()函数
*/
-- 查询所有员工月薪和
SELECT SUM(sal) FROM emp;
-- 查询所有员工月薪和,以及所有员工奖金和
SELECT SUM(sal), SUM(comm) FROM emp;
-- 查询所有员工月薪+奖金和
SELECT SUM(sal+IFNULL(comm, 0)) FROM emp;
-- 统计所有员工平均工资
SELECT AVG(sal) FROM emp;
/*
MAX()和MIN()
*/
-- 查询最高工资和最低工资
SELECT MAX(sal), MIN(sal) FROM emp;
分组 GROUP BY 与 HAVING 子句
/*
当需要分组查询时需要使用GROUP BY子句,
例如查询每个部门的工资和,这说明要使用部分来分组
*/
-- 查询每个部门的部门编号和每个部门的工资和
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门的人数
SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno, COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
/*
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计
COALESCE(a,b,c) 如果a==null,则选择b;如果b==null,则选择c;
如果a!=null,则选择a;如果a b c 都为null ,则返回为null
*/
-- 查询每个部门的部门编号以及每个部门的人数,及总人数
SELECT IFNULL(deptno, '合计'), COUNT(empno) FROM emp GROUP BY deptno WITH ROLLUP;
-- 查询每个部门的部门编号和每个部门的工资和,及总工资和
SELECT COALESCE(deptno, '合计'), SUM(sal) FROM emp GROUP BY deptno WITH ROLLUP;
/*
WHERE与HAVING作用一致,都是对不满足条件的记录进行过滤。
二者也是有区别的:
HAVING是在分组后对数据进行过滤. WHERE是在分组前对数据进行过滤
HAVING后面可以使用分组函数(统计函数) WHERE后面不可以使用分组函数
*/
-- 查询工资总和大于9000的部门编号以及工资和
SELECT deptno, SUM(sal) salsum FROM emp GROUP BY deptno HAVING salsum > 9000;
/*
注意:WHERE是对分组前记录的条件,
如果某行记录没有满足WHERE子句的条件,
那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
*/
关联查询 (关联的是多张表)
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。图片来自菜鸟教程。
等值连接:连接的条件是一个等号
/*
等值连接, 只取交集
*/
-- 显示各个员工的部门名
SELECT e.empno, e.ename, e.sal, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
ORDER BY e.deptno;
不等值连接:连接条件不是等号
/*
不等值连接
*/
-- 显示各个员工的薪资等级
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal >= s.low_sal AND e.sal <= s.hi_sal
ORDER BY e.sal DESC;
内连接
/*
内连接 INNER JOIN 表 ON 条件, INNER 可省略
获取两个表中字段匹配关系的记录, 只取交集
*/
-- 显示各个员工的部门名
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
ORDER BY e.deptno;
左右连接
/*
左连接 LEFT JOIN 表 ON 条件
获取左表所有记录,即使右表没有对应匹配的记录
*/
SELECT e.*, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
/*
右连接 RIGHT JOIN 表 ON 条件
获取右表所有记录,即使左表没有对应匹配的记录
*/
SELECT e.*, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;
自连接
/*
自连接
一张表作多张表看待
*/
-- 查询员工姓名和其直系老板的姓名
SELECT e.empno, e.ename, e2.empno mgrno, e2.ename mgrname
FROM emp e
LEFT JOIN emp e2
ON e.mgr = e2.empno;
-- 查找各个员工的直系下级和直系上级
SELECT low.ename '下级', e.ename '员工', up.ename '上级' FROM emp e
LEFT JOIN emp low
ON low.mgr = e.empno
LEFT JOIN emp up
ON e.mgr = up.empno
ORDER BY e.ename;
子查询
/*
子查询
查询结果可作子表
*/
-- 显示各个部门中薪资最高的人并按薪资降序显示
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.sal IN
(
SELECT MAX(sal) FROM emp GROUP BY deptno
)
ORDER BY e.sal DESC;
-- 查询工资不低于(20号部门平均工资)的员工信息
SELECT *
FROM emp
WHERE sal >= (SELECT AVG(sal) FROM emp WHERE deptno = 20);
-- 按部门统计员工数,部门号,部门名称,总人数
SELECT COUNT(1) emp_count, e.deptno, d.dname, (SELECT COUNT(1) FROM emp) total
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
GROUP BY e.deptno;
集合运算
/*
UNION DISTINCT 合并多张表并去除重复记录, DISTINCT 可省略
UNION ALL 合并多张表但不去重
*/
-- 部门编号为10或薪资高于2500的员工信息
SELECT * FROM emp WHERE sal > 2500
UNION
SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE sal > 2500
UNION ALL
SELECT * FROM emp WHERE deptno = 10;
分页查询 LIMIT
/*
LIMIT 分页查询
LIMIT start,rows 查询以start开始的rows行
LIMIT rows 查询rows行, 默认以0开始
*/
-- 查询薪资最高的前三位员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
-- 查询emp的第一页数据.每页2条
SELECT * FROM emp LIMIT 0,2;
-- 第二页
SELECT * FROM emp LIMIT 2,2;
-- 第三页
SELECT * FROM emp LIMIT 4,2;
索引
EXPLAIN SELECT * FROM `emp` where empno = 7499;
/*
索引失效的条件
使用不等于或者前 LIKE (前面使用%进行模糊匹配)时会失效
索引不是越多越好, 多了会影响增改数据的效率.
*/
-- 添加索引
ALTER TABLE emp add INDEX index_emp_ename(ename);
EXPLAIN SELECT * FROM `emp` where ename = 'WARD'; -- 索引有效
EXPLAIN SELECT * FROM `emp` where ename != 'WARD'; -- 索引失效
EXPLAIN SELECT * FROM `emp` where ename LIKE '%D'; -- 索引失效
-- 联合索引
ALTER TABLE emp add INDEX index_emp_enamejob(ename,job);
常用函数
字符串函数
CONCAT(str1, str2, …) | 连接字符串 |
INSERT(str, pos, len, newstr) | 字符串str从第pos位置开始的len个字符替换为新字符串newstr |
LOWER(str) | 转成小写 |
UPPER(str) | 转成大写 |
LENGTH(str) | 返回字符串str的长度 |
CHAR_LENGTH(str) | 返回字符串str的长度 |
LPAD(str, len, padstr) | 返回字符串str,其左边由字符串padstr填补到len字符串长度 |
RPAD(str, len, padstr) | 返回字符串str,其左边由字符串padstr填补到len字符串长度 |
TRIM(str) | 去掉字符串str前缀和后缀的空格 |
REPEAT(str, count) | 返回str重复count次的结果 |
REPLACE(str, from_str, to_str) | 用字符串to_str替换字符串str中所有的字符串from_str |
SUBSTRING(str, pos, len) | 从字符串str的pos位置起len个字符长度的子串 |
数值函数
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回不小于x的最小整数 |
FLOOR(x) | 返回不大于x的最大整数 |
MOD(x, y) | 返回x/y的模 |
RAND() | 返回0~1的随机浮点数 |
ROUND(x, y) | 返回x的四舍五入的有y位小数的值 |
TRUNCATE(x, y) | 返回数字x截断y位小数的结果 |
日期和时间函数
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
WEEK(date) | 返回指定日期为一年中的第几周 |
YEAR(date) | 返回日期的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time的分钟值 |
MONTHNAME(date) | 返回date的月份名 |
DATEDIFF(expr, expr2) | 返回起始时间expr和结束时间exrp2之间的天数 |
DATE_FORMAT(date, fmt) | 返回按字符串fmt格式化日期date值 |
UNIX_TIMESTAMP() | 返回当前时间戳的毫秒数 |
FROM_UNIXTIME(unix_timestamp, "%Y-%m-%d %H:%i:%S") | 常用来将毫秒数转换为时间格式 |
%M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。) %Y 年, 数字, 4 位 %y 年, 数字, 2 位 20 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) | %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ) %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”。
|