50道MySQL经典题竞技趣味爆表(50道mysql经典题)
50道MySQL经典题:竞技趣味爆表!
MySQL作为一款常见的关系型数据库管理系统,被广泛应用于各类企业和大型网站中。为了检验自己的MySQL技能,可以尝试解答以下经典的MySQL题目,看看自己是否具备MySQL大牛的水平。
1.查询表中所有记录
SELECT * FROM table_name;
2.查询表的某一列
SELECT column_name FROM table_name;
3.查询表的某些列
SELECT column_name1, column_name2 FROM table_name;
4.查询表中满足条件的数据
SELECT * FROM table_name WHERE condition;
5.查询表中满足多个条件的数据
SELECT * FROM table_name WHERE condition1 AND condition2;
6.查询表中不满足条件的数据
SELECT * FROM table_name WHERE NOT condition;
7.查询表中某列不重复的值
SELECT DISTINCT column_name FROM table_name;
8.查询表中最大值
SELECT MAX(column_name) FROM table_name;
9.查询表中最小值
SELECT MIN(column_name) FROM table_name;
10.查询表中值的总和
SELECT SUM(column_name) FROM table_name;
11.查询表中值的平均值
SELECT AVG(column_name) FROM table_name;
12.查询表中符合某条件的记录数量
SELECT COUNT(*) FROM table_name WHERE condition;
13.查询表中数据按某列排序
SELECT * FROM table_name ORDER BY column_name;
14.查询表中数据按多个列排序
SELECT * FROM table_name ORDER BY column1, column2;
15.查询表中数据按某列倒序排序
SELECT * FROM table_name ORDER BY column_name DESC;
16.查询表中数据按多个列倒序排序
SELECT * FROM table_name ORDER BY column1 DESC, column2 DESC;
17.查询表中前n个记录
SELECT * FROM table_name LIMIT n;
18.查询表中第n个记录开始的m个记录
SELECT * FROM table_name LIMIT n, m;
19.查询表中每个值出现的次数
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
20.查询表中满足条件的数据并按某列排序
SELECT * FROM table_name WHERE condition ORDER BY column_name;
21.更新表中某个字段的值
UPDATE table_name SET column_name=value WHERE condition;
22.更新表中某个字段的值并限制更新数量
UPDATE table_name SET column_name=value WHERE condition LIMIT n;
23.删除表中某些记录
DELETE FROM table_name WHERE condition;
24.删除表中某些记录并限制删除数量
DELETE FROM table_name WHERE condition LIMIT n;
25.删除表中所有记录
DELETE FROM table_name;
26.向表中插入一条记录
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
27.向表中插入多条记录
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …), (value1, value2, …), …;
28.查询表中前n个不重复的值
SELECT DISTINCT column_name FROM table_name LIMIT n;
29.查询表中总记录数
SELECT COUNT(*) FROM table_name;
30.查询表中满足条件的记录数量并按某列排序
SELECT column_name, COUNT(*) FROM table_name WHERE condition GROUP BY column_name ORDER BY column_name;
31.查询表中两个字段相加的值
SELECT column1 + column2 FROM table_name;
32.查询表中两个字段相乘的值
SELECT column1 * column2 FROM table_name;
33.查询表中两个字段相除的值
SELECT column1 / column2 FROM table_name;
34.查询表中两个字段相减的值
SELECT column1 – column2 FROM table_name;
35.查询表中字符串字段的长度
SELECT LENGTH(column_name) FROM table_name;
36.查询表中日期字段的年份
SELECT YEAR(column_name) FROM table_name;
37.查询表中日期字段的月份
SELECT MONTH(column_name) FROM table_name;
38.查询表中日期字段的日子
SELECT DAY(column_name) FROM table_name;
39.查询表中日期字段的小时
SELECT HOUR(column_name) FROM table_name;
40.查询表中日期字段的分钟
SELECT MINUTE(column_name) FROM table_name;
41.查询表中日期字段的秒数
SELECT SECOND(column_name) FROM table_name;
42.查询表中日期格式化后的值
SELECT DATE_FORMAT(column_name, format) FROM table_name;
43.查询表中指定时间区间的值
SELECT * FROM table_name WHERE column_name BETWEEN start_date AND end_date;
44.查询表中指定列是否为NULL
SELECT * FROM table_name WHERE column_name IS NULL;
45.查询表中指定列是否不为NULL
SELECT * FROM table_name WHERE column_name IS NOT NULL;
46.查询表中指定列是否包含某个值
SELECT * FROM table_name WHERE column_name LIKE ‘%value%’;
47.查询表中指定列是否以某个值开头
SELECT * FROM table_name WHERE column_name LIKE ‘value%’;
48.查询表中指定列是否以某个值结尾
SELECT * FROM table_name WHERE column_name LIKE ‘%value’;
49.查询表中指定列是否是某个值
SELECT * FROM table_name WHERE column_name = ‘value’;
50.查询表中指定列是否不是某个值
SELECT * FROM table_name WHERE column_name ‘value’;
通过解答以上经典的MySQL题目,可以深入了解MySQL查询语言的特点和应用,让自己的MySQL技能有所提升。加油!