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技能有所提升。加油!


数据运维技术 » 50道MySQL经典题竞技趣味爆表(50道mysql经典题)