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)