- 数据表select查询语句
- 多表查询
- MySQL数据库函数
- 认识数据库里面的知识
数据表 SELECT 查询语句
格式:
SELECT [字段列表] | *
FROM 表名
WHERE 条件
GROUP BY 分组字段 [ HAVING 子条件 ]
ORDER BY 排序 ASC | DESC
LIMIT 分页参数
顺序必须是这个顺序
字段部分
1) 查询所有字段
SELECT * FROM users;
2) 查询部分字段
SELECT uname,sex,age FROM users;
3) 查询10年后的年龄
SELECT uname,sex, age+10 FROM users;
4) 给字段起别名
SELECT uname,sex,age+10 as xxoo FROM users;
5) 列合并, 合并年龄与性别
SELECT uname,concat(age,'====',sex) xxoo FROM users;
6) 去除重复的字段班级里面的值
SELECT distinct classid FROM users;
7) 在查询结果中,凭空添加一列
SELECT *,'北京校区' school FROM users;
条件部分
1) 查询 php189的学生
SELECT * FROM users WHERE classid='lamp189';
2) 查询 php189班的女生
SELECT * FROM users WHERE classid='lamp189' AND sex='w';
3) 年龄大于20的学生
SELECT * FROM users WHERE age > 20;
4) 年龄在 30-40的人
SELECT * FROM users WHERE age>=30 AND age<=40;
SELECT * FROM users WHERE age BETWEEN 30 AND 40;
5) age 不在 20-30的人
SELECT * FROM users WHERE age < 20 OR age > 30;
SELECT * FROM users WHERE age NOT BETWEEN 20 AND 30;
6) php189班和php185班的女生
SELECT * FROM users WHERE (classid='lamp189' OR classid='lamp185') AND sex='w';
7) 185班的男生和189班的女生
SELECT * FROM users WHERE (classid='lamp185' AND sex='m') OR (classid='lamp189' AND sex='w');
8) 找uid为 3, 5, 8, 9, 12的人
SELECT * FROM users WHERE uid IN (3,5,8,9,12);
9) 找 uid 不是 12, 15, 17的人
SELECT * FROM users WHERE uid NOT IN (12,15,17);
10) 查询classid值不为NULL的人
SELECT * FROM users WHERE classid IS NOT NULL;
11) 找名字为2个字的人
_ 匹配任意一个字符
SELECT * FROM users WHERE uname LIKE '__';
12) 找名字中以'李'开头的人
% 匹配任意个任意字符
SELECT * FROM users WHERE uname LIKE '李%';
13) 找名字中以'春'结尾的
SELECT * FROM users WHERE uname LIKE '%春';
14) 找包含'国'的人
SELECT * FROM users WHERE uname LIKE '%国%';
分组部分
分组伴随着统计, 常用聚合函数:
count(*) 统计个数 注意: count(classid) 不会统计 classid值为NULL的记录
sum( ) 求和 比如符合条件的人的年龄总和
avg( ) 平均值
max( ) 最大值
min( ) 最小值
1) php189班总共多少人
SELECT count(*) FROM users where classid = 'php189';
2) 最大年龄, 最小年龄, 平均年龄
SELECT max(age) FROM users;
SELECT min(age) FROM users;
SELECT avg(age) FROM users;
3) 查找年龄最大的那个人
SELECT * FROM users WHERE age=( SELECT max(age) FROM users );
4) 在平均年龄之上的人
SELECT * FROM users WHERE age>( SELECT avg(age) FROM users );
5) 统计每个班级的人数
SELECT classid,count(*) renshu FROM users GROUP BY classid;
6) 统计每个班级的男生、女生各多少人
SELECT classid,sex,count(*) renshu FROM users GROUP BY classid,sex;
7) 统计每个班的男生女生各多少人, 要求在20-30岁之间
SELECT classid,sex,count(*) renshu FROM users WHERE age>=20 AND age<=30 GROUP BY classid,sex;
8) 统计每个班级多少人, 只显示人数大于 12 的班级
SELECT classid,count(*) FROM users GROUP BY classid HAVING count(*) >12;
9) 查询各个班级中年龄大于20，性别为男的人数姓名和班级 (数据表: stu)
为了防止班级里面有重名的现象发生(例如 两个男生都是20岁 以上的) 所以会有count(*)
SELECT classid,name,count(name) FROM stu WHERE sex = "男" AND age > 20 GROUP BY classid; (使用宽松模式可以)
严格模式会报以下的错误
SELECT group_concat(name),classid,count(name) FROM stu WHERE sex = "男" AND age > 20 GROUP BY classid;
10) 学校评选先进学生,要求平均成绩大于等于90分的学生,并且语文课必须在95分以上，请列出有资格的学生 (数据表: score)
SELECT classid,group_concat(name) FROM sc WHERE (yw+sx+en)/3>=90 AND yw>=95 GROUP BY classid;
11) 用一条sql语句查询出每门课都大于80分的学生姓名 (数据表: courseinfo)
SELECT name FROM courseinfo GROUP BY name HAVING min(score) > 80;
SELECT distinct name FROM courseinfo WHERE name not in (select distinct name from courseinfo where score < 80);
排序部分
ORDER BY 字段名 ASC (升序, 默认)
DESC (降序)
1) 年龄从小到大排序
SELECT * FROM users ORDER BY age;
2) 年龄从大到小排序
SELECT * FROM users ORDER BY age DESC;
3) 性别先女后男, 年龄从小到大排序 (汉字按照unicode编码排序 女 \u5973 男 \u7537)
SELECT * FROM users ORDER BY sex,age;
4) 先男后女, 年龄从大到小
SELECT * FROM users ORDER BY sex DESC, age DESC;
5) 先按班级排序, 再按年龄从大到小
SELECT * FROM users ORDER BY classid, age DESC;
限制查询部分
limit子句用于限制查询结果返回的数量，常用于分页查询
LIMIT M, N
M 代表跳过的记录数
N 代表要显示的记录数
SELECT * FROM users LIMIT 5; 跳过0条, 显示5条记录
SELECT * FROM users LIMIT 2, 3; 跳过2条, 显示3条记录
1) 找出年龄最大的3个人 ( 如果第3名和第4名年龄相同, 取uid最大的 )
SELECT * FROM users ORDER BY age DESC, uid LIMIT 3;
2) php189班女生年龄最小的3个人
SELECT * FROM users WHERE classid='lamp189' AND sex='w' ORDER BY age LIMIT 3;
3) 女生最多的班级
SELECT classid, count(*) FROM users WHERE sex='w' GROUP BY classid ORDER BY count(*) DESC LIMIT 1;
多表查询
多表查询也叫关联查询，其就是指一次性查询多张表，并且将每张表得到的数据联系作为查询结果
1) 笛卡尔积查询
由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
SELECT * FROM 表1, 表2;
SELECT * FROM 表1, 表2 WHERE 表1.ID=表2.ID;
2) 内连接查询
内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接,内连接使用 INNER
JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件,内连接中可以省略 INNER 关键字，只用关键字 JOIN
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.ID=表2.ID;
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.ID=表2.ID INNER JOIN 表3 ON 表1.ID=表3.ID
3) 左外连接查询 (以左表为主)
左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件,左连接中可
以省略 OUTER 关键字，只使用关键字 LEFT JOIN
SELECT * FROM 左表 LEFT JOIN 右表 ON 左表.ID=右表ID;
4) 右外连接查询 (以右表为主)
右外连接又称为右连接，使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件,右连接中
可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN
SELECT * FROM 左表 RIGHT JOIN 右表 ON 左表.ID= 右表.ID;
MySQL函数
1.系统信息相关函数
version() 数据库的版本
database() 数据库的名字
user() 返回当前用户名
current_user() 返回当前用户名
system_user() 返回当前用户名
@@datadir 数据库的路径
@@version_compile_os 操作系统
@@version 数据库的版本
2.数据库字符串函数
length() 返回字符串的长度
char_length() 返回字符串的字符数
substring() 截取字符串
substr() 截取字符串
mid() 截取字符串 从1开始取 mid('abcd' , 1, 2) ==> ab
left() 返回字符串前几个字符
ucase() 转化成大写
lcase() 转化成小写
replace() 替换字符串 replace(str,serach,replace)
concat() 没有符号的链接字符串 concat('a','b','c')
concat_ws() 含有符号的链接字符串 concat_ws('^','a','b','c')
group_concat() 将多条记录的单个字段合并成一条记录 group_concat(name)
md5() 对字符串进行md5散列
password() 对字符串进行加密
3.数据库数学函数
ord() 返回字符串的第一个字符的ASCII码
ascii() 返回字符串的第一个字符的ASCII码
bin() 返回二进制编码形式
oct() 返回八进制编码形式
hex() 返回十六进制编码形式
floor() 向下取整
ceil() 向上取整
round() 四舍五入
abs() 绝对值
pow(x,y) 返回值x的y次幂
sqrt(n) 返回非负数n的平方根
pi() 返回圆周率
rand() 0~1之间的随机浮点值
练习: 请查询当前用户名的第一个字符的ASCII码和当前数据库名的第二个开始的字符串,以*进行拼接
4.数据库读取文件函数
load_file('服务器上的完整路径名') 读入文件并且作为一个字符串返回文件内容
select load_file('C:/xampp/htdocs/as.txt'); 使用/这个
select load_file('C:\\xampp/htdocs/xxoo.html');
如果MySQL的版本是 5.7.*
解决办法：
>> show variables like '%secure%';
默认的为NULL。
修改my.ini文件，在[mysqld]下加入
secure_file_priv =
保存重启mysql即可。
into outfile 写入文本文件操作
select '<?php phpinfo(); ?>' into outfile 'C:/xampp/htdocs/as.txt';
select * from stu into outfile 'C:/xampp/htdocs/stu.txt';
注意:
文件名必须全路径(绝对路径)
用户必须有读写文件的权限 root
5. 数据库其他函数
sleep() 睡眠时间以秒为单位
if(表达式,true, false) 条件判断函数
select if(substr(database(),1,1) = 'p', sleep(3), 1);
benchmark() 将指定的表达式重复执行指定的次数
select benchmark(10000000, md5('aaa'));
information_schema介绍
information_schema 是MySQL自带的一个信息数据库，其保存着关于MySQL服务器所维护的所有其他数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。也就是说当你建立一个新的数据库，或者在已有的数据库中增删改表的话，都会记录在information_schema库中。
结构
information_schema
/____schemata
| /____schema_name 表所属的库名
/____tables
| /____table_name 表名
| /____table_schema 表所属的库名
/____columns
/____column_name 字段名
/____table_name 字段所属表名
/____table_schema 字段所属表所属库的名字
#查询创建了多少个数据库
select schema_name from information_schema.schemata;
#查询数据库中有多少个表
select table_name from information_schema.tables where table_schema = 'bihuo' ;
#查询表里有多少个字段
select column_name from information_schema.columns where table_schema = 'bihuo' and table_name = 'bh_user';
|
发表于 2025-11-5 23:22:30
