MySQL10

Error_Fe

已于 2024-05-06 17:09:42 修改

阅读量256

点赞数 3

文章标签： c++ adb sql

于 2024-04-29 16:41:57 首次发布

本文链接：https://blog.csdn.net/Error_Fe/article/details/138317956

版权

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database xscj;
Query OK, 1 row affected (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> use xscj;
Database changed
mysql> SOURCE C:/Users/Administrator/Downloads/sy111.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 22 rows affected (0.00 sec)
Records: 22  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create database yggl;
Query OK, 1 row affected (0.00 sec)

mysql> use yggl;
Database changed
mysql> SOURCE C:/Users/Administrator/Downloads/sy112.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 学历, 性别, COUNT(*) AS 人数
    -> FROM employees
    -> GROUP BY 学历, 性别
    -> ORDER BY 学历, 性别;
+------+------+------+
| 学历    | 性别    | 人数     |
+------+------+------+
| 大专    |    0 |    2 |
| 大专    |    1 |    2 |
| 本科     |    1 |    6 |
| 硕士   |    0 |    1 |
| 硕士   |    1 |    2 |
+------+------+------+
5 rows in set (0.01 sec)

mysql> SELECT LEFT(电话, 1) AS 电话首位, COUNT(*) AS 人数
    -> FROM employees
    -> GROUP BY LEFT(电话, 1)
    -> ORDER BY LEFT(电话, 1);
+----------+------+
| 电话首位      | 人数     |
+----------+------+
| 3        |    2 |
| 5        |    2 |
| 6        |    1 |
| 8        |    8 |
+----------+------+
4 rows in set (0.00 sec)

mysql> SELECT d.部门名称, AVG(s.收入) AS 平均收入
    -> FROM departments d
    -> JOIN employees e ON d.部门号 = e.部门号
    -> JOIN salary s ON e.编号 = s.编号
    -> GROUP BY d.部门名称;
+------------+-------------------+
| 部门名称           | 平均收入                 |
+------------+-------------------+
| 人力资源部          | 2100.800048828125 |
| 市场部          | 2004.296630859375 |
| 研发部          | 2526.276611328125 |
| 经理办公室         | 2749.989990234375 |
| 财务部           | 2147.574981689453 |
+------------+-------------------+
5 rows in set (0.02 sec)

mysql> use xscj;
Database changed
mysql> SELECT MONTH(`出生日期`) AS 出生月, COUNT(*) AS 人数
    -> FROM `xs`
    -> GROUP BY 出生月
    -> ORDER BY 出生月;
+--------+------+
| 出生月       | 人数     |
+--------+------+
|      1 |    2 |
|      2 |    2 |
|      3 |    1 |
|      5 |    1 |
|      6 |    1 |
|      8 |    2 |
|      9 |    1 |
|     10 |    2 |
+--------+------+
8 rows in set (0.00 sec)
mysql> select 学号,姓名,课程名,成绩
    -> from xs_kc
    -> join kc using(课程号)
    -> join xs using(学号)
    -> where 姓名 in (
    -> select 姓名
    -> from xs_kc
    -> join xs using(学号)
    -> where exists(select 1 from xs_kc as sub_xs_kc where sub_xs_kc.学号=xs.学号 and 成绩 between 60 and 69)
    -> group by 姓名
    -> having count(课程号) > 1
    -> ) order by xs.学号,kc.课程名;
+--------+--------+----------------+------+
| 学号      | 姓名       | 课程名              | 成绩    |
+--------+--------+----------------+------+
| 081203 | 严红      | 数据结构             |   88 |
| 081203 | 严红      | 计算机基础               |   85 |
| 081203 | 严红      | 计算机网络               |   68 |
| 081203 | 严红      | 软件工程               |   60 |
| 081303 | 孙祥欣       | 操作系统             |   81 |
| 081303 | 孙祥欣       | 计算机基础               |   63 |
| 081304 | 孙研       | 操作系统             |   76 |
| 081304 | 孙研       | 程序设计与语言               |   84 |
| 081304 | 孙研       | 计算机基础               |   65 |
+--------+--------+----------------+------+
9 rows in set (0.00 sec)

随便弄的

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.53 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lty                |
| mysql              |
| performance_schema |
| test               |
| xscj               |
| yggl               |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database ygg1;
Query OK, 1 row affected (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> use ygg1;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy122.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create database xscj;
ERROR 1007 (HY000): Can't create database 'xscj'; database exists
mysql> create database xcsj;
Query OK, 1 row affected (0.00 sec)

mysql> use xcsj;
Database changed
mysql> SOURCE D:/360安全浏览器下载/sy121.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 21 rows affected (0.00 sec)
Records: 21  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT xs.学号,姓名,专业名, AVG(xs_kc.成绩) AS 平均成绩 from xs join xs_kc on xs.学号=xs_kc.学号 WHERE xs.专业名 = '通信工程' GROUP BY xs.学号, xs.姓名, xs.专业名 ORDER BY 平均成绩 DESC;
+--------+--------+----------+----------+
| 学号      | 姓名       | 专业名       | 平均成绩       |
+--------+--------+----------+----------+
| 081204 | 马琳琳       | 通信工程       |  79.5000 |
| 081202 | 王林       | 通信工程       |  78.0000 |
| 081201 | 王敏       | 通信工程       |  76.0000 |
| 081203 | 严红      | 通信工程       |  75.2500 |
+--------+--------+----------+----------+
4 rows in set (0.01 sec)

mysql> use ygg1;
Database changed
mysql> select 编号,姓名,出生日期,year(curdate())-year(出生日期) as 年龄 from employees order by 出生日期 desc limit 1;
+--------+--------+------------+------+
| 编号       | 姓名       | 出生日期           | 年龄     |
+--------+--------+------------+------+
| 020010 | 王向容       | 1982-12-09 |   42 |
+--------+--------+------------+------+
1 row in set (0.01 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2024-05-06 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:57 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:57 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:58 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:57:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-05-06 16:58:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW v_tx_kc AS
    -> SELECT xs.学号, xs_kc.课程号, xs_kc.成绩
    -> FROM xs
    -> JOIN xs_kc ON xs.学号 = xs_kc.学号
    -> WHERE xs.专业名 = '通信工程';
ERROR 1146 (42S02): Table 'ygg1.xs' doesn't exist
mysql> use xcsj;
Database changed
mysql> CREATE VIEW v_tx_kc AS
    -> SELECT xs.学号, xs_kc.课程号, xs_kc.成绩
    -> FROM xs
    -> JOIN xs_kc ON xs.学号 = xs_kc.学号
    -> WHERE xs.专业名 = '通信工程';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO xs_kc (学号, 课程号, 成绩, 学分)
    -> VALUES ('081201', '102', 95, 5);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM xs_kc WHERE 学号 = '081201';
+--------+--------+------+------+
| 学号      | 课程号     | 成绩    | 学分    |
+--------+--------+------+------+
| 081201 | 101    |   76 |    5 |
| 081201 | 102    |   95 |    5 |
+--------+--------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM v_tx_kc;
+--------+--------+------+
| 学号      | 课程号     | 成绩    |
+--------+--------+------+
| 081201 | 101    |   76 |
| 081201 | 102    |   95 |
| 081202 | 102    |   78 |
| 081203 | 101    |   85 |
| 081203 | 201    |   88 |
| 081203 | 301    |   68 |
| 081203 | 302    |   60 |
| 081204 | 301    |   89 |
| 081204 | 302    |   70 |
+--------+--------+------+
9 rows in set (0.00 sec)