MySQL—基本查询、内置函数
筛选分页结果
/*语法*/
-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET n;
/*按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页*/
mysql> create database exam;
mysql> CREATE TABLE exam_result (
-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL COMMENT '同学姓名',
-> yuwen float DEFAULT 0.0 COMMENT '语文成绩',
-> shuxue float DEFAULT 0.0 COMMENT '数学成绩',
-> yingyu float DEFAULT 0.0 COMMENT '英语成绩'
-> );
mysql> INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES
-> ('唐三藏', 67, 98, 56),
-> ('孙悟空', 87, 78, 77),
-> ('猪悟能', 88, 98, 90),
-> ('曹孟德', 82, 84, 67),
-> ('刘玄德', 55, 85, 45),
-> ('孙权', 70, 73, 78),
-> ('宋公明', 75, 65, 30);
mysql> SELECT * FROM exam_result;
mysql> select * from exam_result limit 0,3; //第一页
mysql> select * from exam_result limit 3,3; //第二页
mysql> select * from exam_result limit 6,3; //第三页
Update
/*语法*/
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
-- 更新值为具体值
-- 查看原数据
mysql> SELECT name, shuxue FROM exam_result WHERE name = '孙悟空';
-- 数据更新
mysql> UPDATE exam_result SET shuxue = 80 WHERE name = '孙悟空';
-- 一次更新多个列
-- 查看原数据
mysql> SELECT name, shuxue, yuwen FROM exam_result WHERE name = '曹孟德';
-- 数据更新
mysql> UPDATE exam_result SET shuxue = 60, yuwen = 70 WHERE name = '曹孟德';
-- 更新值为原值基础上变更
mysql> select name,(yuwen+shuxue+yingyu+30) total from exam_result order by total limit 3;
-- 没有 WHERE 子句，则更新全表
mysql> UPDATE exam_result SET yuwen = yuwen * 2;
Delete
删除数据：
/*语法*/
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
-- 删除数据
mysql> DELETE FROM exam_result WHERE name = '孙悟空';
--删除整张表
mysql> DELETE FROM exam_result;
仅删除表里的记录数据，不删除表结构，不加条件整张表的记录全部删除
截断表
/*语法*/
TRUNCATE [TABLE] table_name;
/**
注意：这个操作慎用
1. 只能对整表操作，不能像DELETE一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快
3. 会重置 AUTO_INCREMENT 项
**/
-- 截断整表数据，注意影响行数是0，所以实际上没有对数据真正操作
mysql> TRUNCATE for_truncate;
插入查询结果
/*语法*/
INSERT INTO table_name [(column [, column ...])] SELECT ...
--快速复制表结构
mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
//新表：no_duplicate_table ， 旧表：duplicate_table
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
/*去重：distinct*/
/**重点：
*like 的使用
*mysql> insert into table2_name select distinct*from table1_name;
**/
聚合函数
- COUNT([DISTINCT] expr)函数：返回查询到的数据的数量
- SUM([DISTINCT] expr)函数：返回查询到的数据的总和，不是数字没有意义
- AVG([DISTINCT] expr)函数：返回查询到的数据的平均值，不是数字没有意义
- MAX([DISTINCT] expr) 函数：返回查询到的数据的最大值，不是数字没有意义
- MIN([DISTINCT] expr) 函数：返回查询到的数据的最小值，不是数字没有意义
- null不计入计算
group by子句的使用
/*语法*/
select column1, column2, .. from table group by column;
/**
*在select中使用group by 子句可以对指定列进行分组查询
**/
--基于雇员信息表（Oracle9i测试表）显示平均工资低于2000的部门和它的平均工资
//分析题目，可进行下述两步操作：
/*1.统计各个部门的平均工资*/
select avg(sal) from EMP group by deptno
/*2.having和group by配合使用，对group by结果进行过滤*/
select avg(sal) as myavg from EMP group by deptno having myavg<2000;
/**
*注意：having必须是在group by内使用的，后面可以加表达式运算
**/
导入Oracle9i测试表SCOTT的基本方法：
//文件存放路径尽可能都是英文,
mysql> source 数据库备份存储的文件路径/SCOTT.sql;
//备份文件，需要断开MySQL连接
# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径/SCOTT1.sql
函数
日期函数
/*请查询在2分钟内发布的帖子*/
select * from msg where date_add(sendtime, interval 2 minute) > now();
//now()---当前时间
字符串函数
/*查看字段所用的字符集，等同于查看数据库的字符集*/
select charset (字段名) from table_name;
show create database scott; //查看数据库的字符集
--要求显示student表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
mysql> select concat (name,'的语文是',yuwen,'分，数学是',shuxue,'分，英语是',yingyu,'分') as '分数' from exam_result;
--将EMP表中所有名字中有S的替换成'上海'
select replace(ename, 'S', '上海') ,ename from EMP;
//查找'10,A,B'中逗号出现的次数
select length ('10,A,B') - length(replace('10,A,B,',',','')) as cnt;
//REPLACE(str,search_str,replace_str) :在str中用replace_str替换search_str
/*length(String):String 的长度，只看个数不看类型*/
数学函数
ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc','123');
mysql> select ifnull(NULL,'123');