当前位置： 首页 > news >正文

数据库基础SQL知识面试题二

news 来源：原创 2024/5/2 10:34:32

　　　　　　　　　　　　　数据库基础SQL知识面试题二

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰 

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

 

一.选课系统SQL语法练习

course数据库中有以下四张表：  
•students表（学生表）：
　　　　sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到dept表的id字段。
  
  •dept表（系表）：
　　　　id整型自增主键，dept_name字符串64位。
  
  •course表（课程表）：
　　　　id整型自增字段主键，course_name字符串64位，teacher_id整型外键到teacher表的id字段。
  
  •teacher表（老师表）：
　　　　id整型自增字段主键，name字符串64位，dept_id整型外键到dept表的id字段。

  •score表（选课成绩表）：
        sid整型自增主键，course_id整型，score整型。


各表的预置数据如下所示：
• students表: 
　　(1,’Jason Yin’,0,1),(2,’Andy’,0,1),(3,’Bob’,0,1),(4,’Ruth’,1,2),(5,’Mike’,0,2),(6,’John’, 0,3),(7,’Cindy’,1,3),(8,’Susan’,1,3)

• dept表:
　　(1,’Education’),(2,’Computer Science’),(3,’Mathematics’)

• course表:
　　(1,‘math’,3),(2,’english’,2),(3,’chinese’,4),(4,’history’,1),(5,’biology’,5)

• teacher表:
　　(1,’Zhang san’,1),(2,’Li si’,1),(3,’Wang wu’,2),(4,’Liu liu’,3),(5,’Ding qi’,3)

• score表: 
　　(1,1,76),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3, 2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),(6,2,58),(6,4,88),(7,1,55),(7,2,65),(7,3,63),(7,4,68),(7,5,70),(8,4,88),(8,5,100)

1>.请根据上述的信息创建出相应的表和插入预置数据 

mysql> CREATE DATABASE course CHARACTER SET = utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
mysql> USE course;
Database changed
mysql> 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| course     |
+------------+
row in set (0.00 sec)

mysql> 
mysql> CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,demt_name VARCHAR(64));
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE students(
    ->  sid INT PRIMARY KEY AUTO_INCREMENT,
    ->  sname VARCHAR(64),
    ->  gender VARCHAR(12),
    ->  dept_id INT NOT NULL,
    ->  CONSTRAINT student_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> 
mysql> CREATE TABLE teacher( 
    ->  id INT PRIMARY KEY AUTO_INCREMENT,
    ->  name VARCHAR(64),
    ->  dept_id INT NOT NULL,
    ->  CONSTRAINT teacher_dept FOREIGN KEY(dept_id) REFERENCES dept(id) 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE course(
    ->  id INT PRIMARY KEY AUTO_INCREMENT,
    ->  course_name VARCHAR(64),
    ->  teacher_id INT,
    ->  CONSTRAINT course_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id) 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE score(sid INT,course_id INT,score INT,PRIMARY KEY(sid,course_id));                                
Query OK, 0 rows affected (0.01 sec)

mysql>
创建数据库与对应的表结构 
mysql> INSERT INTO dept VALUES(1,'Education'),(2,'Computer Science'),(3,'Mathematics');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO teacher VALUES(1,'Zhang san',1),(2,'Li si',1),(3,'Wang wu',2),(4,'Liu liu',3),(5,'Ding qi',3);         
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO students VALUES(1,'Jason Yin',0,1),(2,'Andy',0,1),(3,'Bob',0,1),(4,'Ruth',1,2),(5,'Mike',0,2),(6,'John',0,3),(7,'Cindy',1,3),(8,'Susan',1,3);  
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO course VALUES(1,'math',3),(2,'english',2),(3,'chinese',4),(4,'history',1),(5,'biology',5);  
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO score VALUES(1,1,76),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3,2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),(6,2,58),(6,4,88),(7,1,55),(7,2,65),(7,3,63),(7,4,68),(7,5,70),(8,4,88),(8,5,100);
Query OK, 27 rows affected (0.01 sec)
Records: 27  Duplicates: 0  Warnings: 0

mysql> 
mysql>
插入预置数据

2>.查看所有英语成绩超过数学成绩的学生的学号和姓名

mysql> SELECT aa.sid,aa.sname FROM (SELECT a1.sid,sname,score FROM students a1 INNER JOIN score b1 ON a1.sid=b1.sid WHERE course_id=2) aa INNER JOIN  (SELECT a2.sid,sname, score FROM students a2 INNER JOIN score b2 ON a2.sid=b2.sid WHERE course_id=1) bb ON aa.sid=bb.sid WHERE aa.score>bb.score;
+-----+-----------+
| sid | sname     |
+-----+-----------+
|   1 | Jason Yin |
|   3 | Bob       |
|   7 | Cindy     |
+-----+-----------+
3 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT aa.sid,aa.sname FROM (SELECT a1.sid,sname,score FROM students a1 INNER JOIN score b1 ON a1.sid=b1.sid WHERE course_id=2) aa INNER JOIN (SELECT a2.sid,sname, score FROM students a2 INNER JOIN score b2 ON a2.sid=b2.sid WHERE course_id=1) bb ON aa.sid=bb.sid WHERE aa.score>bb.score;

3>.查看平均成绩大于等于60的所有学生的姓名和平均成绩

mysql> SELECT a.sid,a.sname,AVG(b.score) FROM students a INNER JOIN  score b ON a.sid=b.sid GROUP BY  a.sid,a.sname HAVING AVG(b.score)>=60;
+-----+-----------+--------------+
| sid | sname     | AVG(b.score) |
+-----+-----------+--------------+
|   1 | Jason Yin |      76.0000 |
|   2 | Andy      |      78.0000 |
|   3 | Bob       |      79.8000 |
|   4 | Ruth      |      72.5000 |
|   5 | Mike      |      82.3333 |
|   6 | John      |      73.0000 |
|   7 | Cindy     |      64.2000 |
|   8 | Susan     |      94.0000 |
+-----+-----------+--------------+
8 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT a.sid,a.sname,AVG(b.score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname HAVING AVG(b.score)>=60;

4>.查询所有同学的学号，姓名，选课数和总成绩

mysql> SELECT a.sid,a.sname,COUNT(*),SUM(score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname;
+-----+-----------+----------+------------+
| sid | sname     | COUNT(*) | SUM(score) |
+-----+-----------+----------+------------+
|   1 | Jason Yin |        4 |        304 |
|   2 | Andy      |        4 |        312 |
|   3 | Bob       |        5 |        399 |
|   4 | Ruth      |        2 |        145 |
|   5 | Mike      |        3 |        247 |
|   6 | John      |        2 |        146 |
|   7 | Cindy     |        5 |        321 |
|   8 | Susan     |        2 |        188 |
+-----+-----------+----------+------------+
8 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT a.sid,a.sname,COUNT(*),SUM(score) FROM students a INNER JOIN score b ON a.sid=b.sid GROUP BY a.sid,a.sname;

5>.查询姓zhang的老师的个数

mysql> SELECT count(*) FROM teacher WHERE name LIKE 'zhang%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT count(*) FROM teacher WHERE name LIKE 'zhang%';

6>.查询没学过zhangsan老师课程的学生的学号和姓名

mysql> SELECT sid,sname FROM students WHERE Sid NOT IN (SELECT b.sid FROM course a INNER JOIN score b ON a.id=b.course_id INNER JOIN teacher c ON a.teacher_id=c.id WHERE c.name='Zhang san');
+-----+-----------+
| sid | sname     |
+-----+-----------+
|   1 | Jason Yin |
+-----+-----------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT sid,sname FROM students WHERE Sid NOT IN (SELECT b.sid FROM course a INNER JOIN score b ON a.id=b.course_id INNER JOIN teacher c ON a.teacher_id=c.id WHERE c.name='Zhang san'); 

7>.查询既学过英语也学过语文的学生的学号和姓名

mysql> SELECT a.sid,sname,count(*) FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id AND c.course_name in ('english', 'chinese') GROUP BY  Sid,sname HAVING COUNT(*)>=2;
+-----+-----------+----------+
| sid | sname     | count(*) |
+-----+-----------+----------+
|   1 | Jason Yin |        2 |
|   2 | Andy      |        2 |
|   3 | Bob       |        2 |
|   7 | Cindy     |        2 |
+-----+-----------+----------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT a.sid,sname,count(*) FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id AND c.course_name in ('english', 'chinese') GROUP BY Sid,sname HAVING COUNT(*)>=2; 

8>.查询有学生的单科成绩小于60的姓名和课程名称

mysql> 
mysql> SELECT a.sname,c.course_name FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id WHERE b.score<60;
+-----------+-------------+
| sname     | course_name |
+-----------+-------------+
| Jason Yin | biology     |
| Bob       | math        |
| John      | english     |
| Cindy     | math        |
+-----------+-------------+
4 rows in set (0.01 sec)

mysql> 
mysql>
mysql> SELECT a.sname,c.course_name FROM students a INNER JOIN score b ON a.sid=b.sid INNER JOIN course c ON b.course_id=c.id WHERE b.score<60;

9>.按平均成绩从高到低显示所有学生的姓名和语文，数学，英语 三科成绩 

mysql> SELECT a.sid,AVG(score) score_avg,SUM(CASE WHEN b.course_name='chinese' THEN a.score ELSE 0 END) a1,SUM(CASE WHEN b.course_name='math' THEN a.score ELSE 0 END) a2,SUM(CASE WHEN b.course_name='English' THEN a.score ELSE 0 END) a3  FROM score a INNER JOIN course b ON a.course_id=b.id  GROUP BY a.sid;
+-----+-----------+------+------+------+
| sid | score_avg | a1   | a2   | a3   |
+-----+-----------+------+------+------+
|   1 |   76.0000 |   82 |   76 |   90 |
|   2 |   78.0000 |   77 |    0 |   78 |
|   3 |   79.8000 |   75 |   48 |   95 |
|   4 |   72.5000 |   78 |    0 |    0 |
|   5 |   82.3333 |   90 |   75 |    0 |
|   6 |   73.0000 |    0 |    0 |   58 |
|   7 |   64.2000 |   63 |   55 |   65 |
|   8 |   94.0000 |    0 |    0 |    0 |
+-----+-----------+------+------+------+
8 rows in set (0.00 sec)

mysql>
mysql> SELECT a.sid,AVG(score) score_avg,SUM(CASE WHEN b.course_name='chinese' THEN a.score ELSE 0 END) a1,SUM(CASE WHEN b.course_name='math' THEN a.score ELSE 0 END) a2,SUM(CASE WHEN b.course_name='English' THEN a.score ELSE 0 END) a3 FROM score a INNER JOIN course b ON a.course_id=b.id GROUP BY a.sid; 

10>.查询各科成绩中的最高分和最低分

mysql> SELECT course_id,MIN(score),MAX(score) FROM score GROUP BY course_id;
+-----------+------------+------------+
| course_id | MIN(score) | MAX(score) |
+-----------+------------+------------+
|         1 |         48 |         76 |
|         2 |         58 |         95 |
|         3 |         63 |         90 |
|         5 |         56 |        100 |
|         4 |         67 |         92 |
+-----------+------------+------------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,MIN(score),MAX(score) FROM score GROUP BY course_id;

11>.计算各科平均成绩和及格率百分比

mysql> SELECT course_id,AVG(score),SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 FROM score GROUP BY course_id;
+-----------+------------+---------------------------------------------------------+
| course_id | AVG(score) | SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 |
+-----------+------------+---------------------------------------------------------+
|         1 |    63.5000 |                                                 50.0000 |
|         2 |    77.2000 |                                                 80.0000 |
|         3 |    77.5000 |                                                100.0000 |
|         5 |    76.6000 |                                                 80.0000 |
|         4 |    82.0000 |                                                100.0000 |
+-----------+------------+---------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,AVG(score),SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 FROM score GROUP BY course_id;

12>.查询不同老师所教不同课程平均分从高到低

mysql> SELECT c.name,b.course_name,AVG(score) FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN teacher c ON b.teacher_id=c.id GROUP BY c.name,b.course_name ORDER BY AVG(score) DESC;
+-----------+-------------+------------+
| name      | course_name | AVG(score) |
+-----------+-------------+------------+
| Zhang san | history     |    82.0000 |
| Liu liu   | chinese     |    77.5000 |
| Li si     | english     |    77.2000 |
| Ding qi   | biology     |    76.6000 |
| Wang wu   | math        |    63.5000 |
+-----------+-------------+------------+
5 rows in set (0.00 sec)

mysql>
mysql> SELECT c.name,b.course_name,AVG(score) FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN teacher c ON b.teacher_id=c.id GROUP BY c.name,b.course_name ORDER BY AVG(score) DESC;

13>.查询英语和数学课程成绩排名第5到第10位的学生姓名和成绩

mysql> SELECT c.sname,a.score FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN students c ON a.sid=c.sid WHERE b.course_name='English' ORDER BY a.score LIMIT 4,6;                                                           
+-------+-------+
| sname | score |
+-------+-------+
| Bob   |    95 |
+-------+-------+
1 row in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT c.sname,a.score FROM score a INNER JOIN course b ON a.course_id=b.id INNER JOIN students c ON a.sid=c.sid WHERE b.course_name='English' ORDER BY a.score LIMIT 4,6;

14>.统计按照各科成绩，分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数

mysql> SELECT b.course_name,SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END),SUM(CASE WHEN score<90 AND score>=80 THEN 1 ELSE 0 END),SUM(CASE WHEN score<80 AND score>=60 THEN 1 ELSE 0 END),SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) FROM score a INNER JOIN course b ON a.course_id=b.id GROUP BY b.course_name;
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
| course_name | SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END) | SUM(CASE WHEN score<90 AND score>=80 THEN 1 ELSE 0 END) | SUM(CASE WHEN score<80 AND score>=60 THEN 1 ELSE 0 END) | SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) |
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
| math        |                                          0 |                                                       0 |                                                       2 |                                         2 |
| english     |                                          2 |                                                       0 |                                                       2 |                                         1 |
| chinese     |                                          1 |                                                       1 |                                                       4 |                                         0 |
| biology     |                                          2 |                                                       0 |                                                       2 |                                         1 |
| history     |                                          1 |                                                       4 |                                                       2 |                                         0 |
+-------------+--------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT b.course_name,SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END),SUM(CASE WHEN score=80 THEN 1 ELSE 0 END),SUM(CASE WHEN score=60 THEN 1 ELSE 0 END),SUM(CASE WHEN score

15>.查看每门课程被选修的学生数

mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
|         1 |        4 |
|         2 |        5 |
|         3 |        6 |
|         5 |        5 |
|         4 |        7 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

16>.查看只学习了一门课程的学生的姓名和学号

mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
|         1 |        4 |
|         2 |        5 |
|         3 |        6 |
|         5 |        5 |
|         4 |        7 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

17>.查询名字相同的学生名单和个数

mysql> SELECT sname,count(*) FROM students GROUP BY sname HAVING COUNT(*)>=2; 
Empty set (0.00 sec)

mysql>
mysql> SELECT sname,count(*) FROM students GROUP BY sname HAVING COUNT(*)>=2;

18>.85年之后出生的学生人数

mysql> alter table students add brithday datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update students set brithday='1983-01-01' where sid<5;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> update students set brithday='1986-01-01' where sid>=5;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql>
添加字段并插入测试数据 
mysql>  SELECT * FROM students WHERE brithday>='1985-01-01';
+-----+-------+--------+---------+---------------------+
| sid | sname | gender | dept_id | brithday            |
+-----+-------+--------+---------+---------------------+
|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 |
|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 |
|   7 | Cindy | 1      |       3 | 1986-01-01 00:00:00 |
|   8 | Susan | 1      |       3 | 1986-01-01 00:00:00 |
+-----+-------+--------+---------+---------------------+
4 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT * FROM students WHERE brithday>='1985-01-01';

19>.查询每门课程的平均成绩，按升序排序，如果平均成绩相同按ID降序排序

mysql> SELECT course_id,AVG(score) FROM score GROUP BY course_id ORDER BY AVG(score),course_id DESC;
+-----------+------------+
| course_id | AVG(score) |
+-----------+------------+
|         1 |    63.5000 |
|         5 |    76.6000 |
|         2 |    77.2000 |
|         3 |    77.5000 |
|         4 |    82.0000 |
+-----------+------------+
5 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,AVG(score) FROM score GROUP BY course_id ORDER BY AVG(score),course_id DESC;

20>.查询有不及格学生的课程和不及格学生个数

mysql> SELECT  course_id,COUNT(*) FROM score WHERE score<60 GROUP BY course_id;
+-----------+----------+
| course_id | COUNT(*) |
+-----------+----------+
|         5 |        1 |
|         1 |        2 |
|         2 |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> 
mysql>
mysql> SELECT course_id,COUNT(*) FROM score WHERE score

21>.将所有学生姓名中前后的空格去掉

mysql> UPDATE students SET sname=LTRIM(RTRIM(sname));     
Query OK, 0 rows affected (0.01 sec)
Rows matched: 8  Changed: 0  Warnings: 0

mysql> 
mysql>
mysql> UPDATE students SET sname=LTRIM(RTRIM(sname));

22>.将所有学生的考试成绩展示为课程名:成绩样式

mysql> 
mysql> SELECT a.sid,CONCAT(b.course_name,':',a.score)FROM score a INNER  JOIN course b ON a.course_id=b.id;
+-----+-----------------------------------+
| sid | CONCAT(b.course_name,':',a.score) |
+-----+-----------------------------------+
|   1 | math:76                           |
|   1 | english:90                        |
|   1 | chinese:82                        |
|   1 | biology:56                        |
|   2 | english:78                        |
|   2 | chinese:77                        |
|   2 | history:92                        |
|   2 | biology:65                        |
|   3 | math:48                           |
|   3 | english:95                        |
|   3 | chinese:75                        |
|   3 | history:89                        |
|   3 | biology:92                        |
|   4 | chinese:78                        |
|   4 | history:67                        |
|   5 | math:75                           |
|   5 | chinese:90                        |
|   5 | history:82                        |
|   6 | english:58                        |
|   6 | history:88                        |
|   7 | math:55                           |
|   7 | english:65                        |
|   7 | chinese:63                        |
|   7 | history:68                        |
|   7 | biology:70                        |
|   8 | history:88                        |
|   8 | biology:100                       |
+-----+-----------------------------------+
27 rows in set (0.00 sec)

mysql>
mysql> SELECT a.sid,CONCAT(b.course_name,':',a.score)FROM score a INNER JOIN course b ON a.course_id=b.id;

23>.将所有老师的名字拆分成姓和名两个字段显示

mysql> SELECT name, SUBSTRING(name,1,LOCATE(' ',name)- 1),SUBSTRING(name,LOCATE(' ',name)+1,50) FROM teacher;
+-----------+---------------------------------------+---------------------------------------+
| name      | SUBSTRING(name,1,LOCATE(' ',name)- 1) | SUBSTRING(name,LOCATE(' ',name)+1,50) |
+-----------+---------------------------------------+---------------------------------------+
| Zhang san | Zhang                                 | san                                   |
| Li si     | Li                                    | si                                    |
| Wang wu   | Wang                                  | wu                                    |
| Liu liu   | Liu                                   | liu                                   |
| Ding qi   | Ding                                  | qi                                    |
+-----------+---------------------------------------+---------------------------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql>
mysql> SELECT name, SUBSTRING(name,1,LOCATE(' ',name)- 1),SUBSTRING(name,LOCATE(' ',name)+1,50) FROM teacher;

24>.把所有学生的生日格式转换成年月日的格式，并计算每个学生年龄

mysql> SELECT sname,brithday,DATE_FORMAT(brithday, '%Y%m%d'),YEAR(NOW())- YEAR(brithday),FLOOR(DATEDIFF(NOW(),brithday)/365) FROM students;
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
| sname     | brithday            | DATE_FORMAT(brithday, '%Y%m%d') | YEAR(NOW())- YEAR(brithday) | FLOOR(DATEDIFF(NOW(),brithday)/365) |
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
| Jason Yin | 1983-01-01 00:00:00 | 19830101                        |                          36 |                                  36 |
| Andy      | 1983-01-01 00:00:00 | 19830101                        |                          36 |                                  36 |
| Bob       | 1983-01-01 00:00:00 | 19830101                        |                          36 |                                  36 |
| Ruth      | 1983-01-01 00:00:00 | 19830101                        |                          36 |                                  36 |
| Mike      | 1986-01-01 00:00:00 | 19860101                        |                          33 |                                  33 |
| John      | 1986-01-01 00:00:00 | 19860101                        |                          33 |                                  33 |
| Cindy     | 1986-01-01 00:00:00 | 19860101                        |                          33 |                                  33 |
| Susan     | 1986-01-01 00:00:00 | 19860101                        |                          33 |                                  33 |
+-----------+---------------------+---------------------------------+-----------------------------+-------------------------------------+
8 rows in set (0.00 sec)

mysql> 
mysql> 
mysql>
mysql> SELECT sname,brithday,DATE_FORMAT(brithday, '%Y%m%d'),YEAR(NOW())- YEAR(brithday),FLOOR(DATEDIFF(NOW(),brithday)/365) FROM students;

 

转载于:https://www.cnblogs.com/yinzhengjie/p/10409881.html

相关文章：

  • 取代Python多进程！伯克利开源分布式框架Ray
  • mysql常用命令汇总
  • 前端知识点整理（待续）
  • Angular 响应式表单 基础例子
  • 2019年2月22日 807. Max Increase to Keep City Skyline
  • 微信小程序：实现悬浮返回和分享按钮
  • 巨杉中标渤海银行，股份制银行再下一城
  • python使用elasticsearch模块操作elasticsearch
  • “春节十二响”C语言代码开源了，你要提 PR 吗？
  • VS 2019要来了，是时候了解一下C# 8.0新功能
  • 北大AI公开课2019 | 雷鸣：人工智能革命与机遇
  • 基于Gitflow分支模型自动化Java项目工作流
  • centos7升级内核至最新
  • 什么软件可以剪辑音乐？
  • 初探 Vue 生命周期和钩子函数
  • Invalidate和postInvalidate的区别
  • Java Agent 学习笔记
  • Javascript Math对象和Date对象常用方法详解
  • Js基础知识（一） - 变量
  • KMP算法及优化
  • SegmentFault 技术周刊 Vol.27 - Git 学习宝典：程序员走江湖必备
  • vuex 学习笔记 01
  • Vultr 教程目录
  • 彻底搞懂浏览器Event-loop
  • 从零开始学习部署
  • 基于游标的分页接口实现
  • 限制Java线程池运行线程以及等待线程数量的策略
  • 项目实战-Api的解决方案
  • 小程序滚动组件，左边导航栏与右边内容联动效果实现
  • 用Node EJS写一个爬虫脚本每天定时给心爱的她发一封暖心邮件
  • 【干货分享】dos命令大全
  • ​linux启动进程的方式
  • ​水经微图Web1.5.0版即将上线
  • #我与Java虚拟机的故事#连载03：面试过的百度，滴滴，快手都问了这些问题
  • （1）Nginx简介和安装教程
  • （6）STL算法之转换
  • （C语言）输入一个序列，判断是否为奇偶交叉数
  • （附源码）php投票系统 毕业设计 121500
  • （附源码）spring boot基于小程序酒店疫情系统 毕业设计 091931
  • (使用vite搭建vue3项目（vite + vue3 + vue router + pinia + element plus）)
  • （四）鸿鹄云架构一服务注册中心
  • （五）关系数据库标准语言SQL
  • (原創) 人會胖會瘦，都是自我要求的結果 (日記)
  • (转)Android中使用ormlite实现持久化(一)--HelloOrmLite
  • (轉貼) UML中文FAQ (OO) (UML)
  • .NET CF命令行调试器MDbg入门(一)
  • .net framwork4.6操作MySQL报错Character set ‘utf8mb3‘ is not supported 解决方法
  • .net之微信企业号开发(一) 所使用的环境与工具以及准备工作
  • @CacheInvalidate(name = “xxx“, key = “#results.![a+b]“,multi = true)是什么意思
  • @Resource和@Autowired的区别
  • @test注解_Spring 自定义注解你了解过吗？
  • [ C++ ] STL---string类的使用指南
  • [ Linux ] git工具的基本使用（仓库的构建，提交）
  • [20171102]视图v$session中process字段含义
  • [bzoj2957]楼房重建