板凳-------Mysql cookbook学习 (九)
第4章:表管理
4.0 引言
MySQL :: 员工样例数据库 :: 3 安装
https://dev.mysql.com/doc/employee/en/employees-installation.html
Employees 数据库与几种不同的 存储引擎,默认情况下启用 InnoDB 引擎。编辑 文件并调整 comments 选择不同的存储引擎:employees.sql
SET default_storage_engine = InnoDB;
– set storage_engine = MyISAM;
– set storage_engine = Falcon;
– set storage_engine = PBXT;
– set storage_engine = Maria;
• MySQL 5.7 或更早:使用 storage_engine。
• MySQL 8.0+:使用 default_storage_engine。
D:\sql\Mysql_learning\employee\mysql\master>mysql -u root -p -t < employees.sql
Enter password: ****
+-----------------------------+
| INFO |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
+---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:09 |
+---------------------+
mysql> SOURCE D:/sql/Mysql_learning/employee/mysql/master/test_employees_md5.sql;
Database changed
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected, 2 warnings (0.00 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 0 rows affected (0.04 sec)Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
6 rows in set (0.00 sec)Query OK, 0 rows affected, 1 warning (0.01 sec)Query OK, 0 rows affected (0.04 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 300024 rows affected, 1 warning (6.19 sec)
Records: 300024 Duplicates: 0 Warnings: 1Query OK, 1 row affected (0.29 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 9 rows affected, 1 warning (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 1Query OK, 1 row affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 24 rows affected, 1 warning (0.01 sec)
Records: 24 Duplicates: 0 Warnings: 1Query OK, 1 row affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 331603 rows affected, 1 warning (6.28 sec)
Records: 331603 Duplicates: 0 Warnings: 1Query OK, 1 row affected (0.38 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 443308 rows affected, 1 warning (9.02 sec)
Records: 443308 Duplicates: 0 Warnings: 1Query OK, 1 row affected (0.43 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 2844047 rows affected, 1 warning (51.55 sec)
Records: 2844047 Duplicates: 0 Warnings: 1Query OK, 1 row affected (2.60 sec)Query OK, 0 rows affected (0.07 sec)+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
6 rows in set (0.00 sec)+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| departments | OK | ok |
| dept_emp | OK | ok |
| dept_manager | OK | ok |
| employees | OK | ok |
| salaries | OK | ok |
| titles | OK | ok |
+--------------+---------------+-----------+
6 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+------------------+
| computation_time |
+------------------+
| 00:01:18 |
+------------------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.04 sec)+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
2 rows in set (0.00 sec)
mysql>
SQL CREATE TABLE LIKE 用法及示例
在 MySQL 中,CREATE TABLE LIKE 是一种用于创建新表的语法,该新表的结构与现有表完全相同,但不包含源表中的数据1。以下是详细的用法和示例:
import mysql.connector
from pathlib import Path
import redef merge_sql_files(main_sql_path):"""递归合并所有被引用的SQL文件"""main_path = Path(main_sql_path)base_dir = main_path.parentmerged_sql = []with open(main_path, 'r', encoding='utf-8') as f:for line in f:# 处理source命令if line.strip().startswith('source'):# 提取被引用的文件名match = re.search(r'source\s+[\'"](.+?)[\'"]|source\s+(\S+)', line.strip())ref_file = match.group(1) or match.group(2)ref_path = base_dir / ref_fileif ref_path.exists():merged_sql.append(f"\n-- 开始导入: {ref_file}\n")merged_sql.append(merge_sql_files(str(ref_path)))merged_sql.append(f"\n-- 完成导入: {ref_file}\n")else:raise FileNotFoundError(f"无法找到引用的SQL文件: {ref_path}")else:# 移除DELIMITER等客户端命令if not line.strip().upper().startswith(('DELIMITER', 'USE ', 'CONNECT ')):merged_sql.append(line)return ''.join(merged_sql)def execute_mega_sql(db_config, full_sql):"""执行大型SQL脚本"""cnx = mysql.connector.connect(**db_config)cursor = cnx.cursor()try:# 设置更大的缓冲区cursor.execute("SET GLOBAL max_allowed_packet=256*1024*1024")cursor.execute("SET GLOBAL net_buffer_length=1000000")# 分割并执行SQL语句statements = [s.strip() for s in full_sql.split(';') if s.strip()]for stmt in statements:try:# 确保语句以分号结尾sql = stmt if stmt.endswith(';') else stmt + ';'for result in cursor.execute(sql, multi=True):if result.with_rows:result.fetchall() # 消费结果集print(f"执行成功: {result.statement[:100]}...")cnx.commit()except mysql.connector.Error as err:print(f"跳过错误: {err}\n语句: {stmt[:200]}...")cnx.rollback()except Exception as e:print(f"意外错误: {e}")cnx.rollback()finally:cursor.close()cnx.close()# 配置信息
db_config = {'host': 'localhost','user': 'root','password': 'root','database': 'cookbook'
}# 主SQL文件路径
main_sql_path = r"D:\sql\Mysql_learning\employee\mysql\dataset_small\employee.sql"try:print("开始合并SQL文件...")full_sql = merge_sql_files(main_sql_path)# 可选:保存合并后的文件供检查with open('merged_script.sql', 'w', encoding='utf-8') as f:f.write(full_sql)print("开始执行SQL脚本...")execute_mega_sql(db_config, full_sql)print("数据导入成功完成!")except Exception as e:
print(f"导入失败: {str(e)}")
def verify_import():tables = ['employee', 'department', 'dept_emp','dept_manager', 'title', 'salary']cnx = mysql.connector.connect(**db_config)cursor = cnx.cursor(dictionary=True)for table in tables:cursor.execute(f"SELECT COUNT(*) as count FROM {table}")result = cursor.fetchone()print(f"{table}表记录数: {result['count']}")cursor.close()cnx.close()verify_import()
employee表记录数: 1000
department表记录数: 9
dept_emp表记录数: 1103
dept_manager表记录数: 16
title表记录数: 1470
salary表记录数: 9488
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_cookbook |
+-----------------------+
| actors |
| adcount |
| al_winner |
| app_log |
| artist |
| book_authors |
| book_vendor |
| booksales |
| catalog_list |
| cd |
| city |
| color |
| cow_color |
| cow_order |
| current_dept_emp |
| customers |
| date_val |
| datetbl |
| datetime_val |
| department |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| dialogue |
| dialogue_analysis |
| die |
| doremi |
| drawing |
| driver_log |
| employee |
| expt |
| formula1 |
| goods_characteristics |
| goods_shops |
| groceries |
| groceries_order_items |
| hitcount |
| hitlog |
| hostip |
| hostname |
| housewares |
| housewares2 |
| housewares3 |
| housewares4 |
| httpdlog |
| httpdlog2 |
| hw_category |
| image |
| ingredient |
| insect |
| inv_item |
| invoice |
| item |
| limbs |
| limbs_backup |
| limbs_stats |
| mail |
| mail2 |
| mail_view |
| marathon |
| mark_log |
| metal |
| money |
| movies |
| movies_actors |
| movies_actors_link |
| mytable |
| name |
| news |
| newsstaff |
| numbers |
| obs |
| occasion |
| painting |
| passtbl |
| passwd |
| patients |
| perl_session |
| person |
| php_session |
| phrase |
| player_stats |
| player_stats2 |
| poi |
| poll_vote |
| profile |
| profile_contact |
| rainfall |
| rand_names |
| rank |
| ranks |
| reviews |
| roster |
| ruby_session |
| salary |
| sales_region |
| sales_tax_rate |
| sales_volume |
| sibling |
| some table |
| standings1 |
| standings2 |
| states |
| str_val |
| sundays |
| taxpayer |
| testscore |
| testscore_withmisses |
| testscore_withmisses2 |
| time_val |
| title |
| tmp |
| tomcat_role |
| tomcat_session |
| tomcat_user |
| top_names |
| trip_leg |
| trip_log |
| tsdemo |
| weatherdata |
| weekday |
+-----------------------+
121 rows in set (0.01 sec)
mysql> SELECT * FROM department LIMIT 5;
+---------+------------------+
| dept_no | dept_name |
+---------+------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
+---------+------------------+
5 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM employee;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.01 sec)
mysql> use employees
Database changed
mysql> show create table employees;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (`emp_no` int NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
第一张表:departments
记录的是9个部门的部门编号和部门名称第二张表:dept_emp
部门员工数据,员工id和部门id,其实时间和结束时间(注:9999的意思就是仍在职)统计一下这张表的数据量,331603条记录SELECT COUNT(*)
FROM dept_emp再统计一下员工id(emp_no)的去重数量,300024条记录SELECT COUNT(DISTINCT emp_no)
FROM dept_emp差异3W条,为什么?有重复出现的员工id,我们尝试把重复出现员工id筛选出来SELECT emp_no
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>1好了,现在有了那近3W个有重复值的员工id,但我们仍然不清楚重复的原因,现在需要跟着重复员工id把完整的信息筛选出来复制代码
SELECT *
FROM dept_emp
WHERE emp_no IN (SELECT emp_no
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>1
)
复制代码现在结果很明确,他们是调部门了!所以该表记录是数据真实含义是每个员工在每个部门所待的时间跨度。但同时,也存在一个有趣的现象,就是没有一个员工是调过两次部门的,证明如下复制代码
SELECT *
FROM dept_emp
WHERE emp_no IN (SELECT emp_no
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(emp_no)>2
)
复制代码这个现象我们看看能不能在接下的表中又所发现第三张表:dept_manger
同第二张表结构差不多,每个部门的每个经理的任职时期,总共就24个人,每个部门至少有过两个经理。mysql> select count(distinct emp_no) as manager_sum-> from dept_manager-> group by dept_no;
+-------------+
| manager_sum |
+-------------+
| 2 |
| 2 |
| 2 |
| 4 |
| 2 |
| 4 |
| 2 |
| 2 |
| 4 |
+-------------+
9 rows in set (0.01 sec)第四张表:employees
员工信息表,emp_no是唯一键值,统计结果与表二得出的数据一致。SELECT COUNT(*)
FROM employees;第五张表:salaries记录每个员工每段时期的薪资!第六张表:title记录每个员工每段时期的职位名称!但请注意,周期与第五张表是不同的,因为在同一职位上你也是会涨工资的嘛
department 部门表
dept_emp 部门员工任职期表(按部门&时期)
dept_manager 部门经理任职期表(按时期)
employees 员工详情表
salaries 员工薪资表(按时期)
title 员工职称表(按时期)
mysql> select *-> from dept_emp-> where emp_no = 110022;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 9999-01-01 |
+--------+---------+------------+------------+
1 row in set (0.00 sec)mysql> select *-> from dept_manager-> where emp_no = 110022;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
+--------+---------+------------+------------+
1 row in set (0.00 sec)mysql> select *-> from employees-> where emp_no = 110022;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------+--------+------------+
| 110022 | 1956-09-12 | Margareta | Markovitch | M | 1985-01-01 |
+--------+------------+------------+------------+--------+------------+
1 row in set (0.00 sec)mysql> select *-> from titles-> where emp_no = 110022;
+--------+--------------+------------+------------+
| emp_no | title | from_date | to_date |
+--------+--------------+------------+------------+
| 110022 | Manager | 1985-01-01 | 1991-10-01 |
| 110022 | Senior Staff | 1991-10-01 | 9999-01-01 |
+--------+--------------+------------+------------+
2 rows in set (0.01 sec)mysql> select *-> from salaries-> where emp_no = 110022;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 110022 | 71166 | 1985-01-01 | 1986-01-01 |
| 110022 | 71820 | 1986-01-01 | 1987-01-01 |
| 110022 | 72970 | 1987-01-01 | 1988-01-01 |
| 110022 | 76211 | 1988-01-01 | 1988-12-31 |
| 110022 | 78443 | 1988-12-31 | 1989-12-31 |
| 110022 | 81784 | 1989-12-31 | 1990-12-31 |
| 110022 | 82871 | 1990-12-31 | 1991-12-31 |
| 110022 | 86797 | 1991-12-31 | 1992-12-30 |
| 110022 | 89204 | 1992-12-30 | 1993-12-30 |
| 110022 | 92165 | 1993-12-30 | 1994-12-30 |
| 110022 | 94286 | 1994-12-30 | 1995-12-30 |
| 110022 | 96647 | 1995-12-30 | 1996-12-29 |
| 110022 | 97604 | 1996-12-29 | 1997-12-29 |
| 110022 | 98843 | 1997-12-29 | 1998-12-29 |
| 110022 | 100014 | 1998-12-29 | 1999-12-29 |
| 110022 | 100592 | 1999-12-29 | 2000-12-28 |
| 110022 | 104485 | 2000-12-28 | 2001-12-28 |
| 110022 | 108407 | 2001-12-28 | 9999-01-01 |
+--------+--------+------------+------------+
18 rows in set (0.01 sec)
分组统计&排序
统计历史上各个部门所拥有的员工数量,并降序排序
mysql> select dept_no, count(*) as emp_sum-> from dept_emp-> group by dept_no-> order by emp_sum desc;
+---------+---------+
| dept_no | emp_sum |
+---------+---------+
| d005 | 85707 |
| d004 | 73485 |
| d007 | 52245 |
| d009 | 23580 |
| d008 | 21126 |
| d001 | 20211 |
| d006 | 20117 |
| d003 | 17786 |
| d002 | 17346 |
+---------+---------+
9 rows in set (0.12 sec)1. 使用 SHOW TABLES 命令(视图会与表一起列出)
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| test |
| titles |
+----------------------+
9 rows in set (0.01 sec)2. 使用 SHOW FULL TABLES 命令(可区分视图和表)
mysql> SHOW FULL TABLES WHERE table_type = 'VIEW';
+----------------------+------------+
| Tables_in_employees | Table_type |
+----------------------+------------+
| current_dept_emp | VIEW |
| dept_emp_latest_date | VIEW |
| test | VIEW |
+----------------------+------------+
3 rows in set (0.00 sec)3. 使用 information_schema 数据库查询
mysql> SELECT * FROM information_schema.views-> WHERE table_schema = 'employees';
+---------------+--------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| def | employees | current_dept_emp | select `employees`.`l`.`emp_no` AS `emp_no`,`d`.`dept_no` AS `dept_no`,`employees`.`l`.`from_date` AS `from_date`,`employees`.`l`.`to_date` AS `to_date` from (`employees`.`dept_emp` `d` join `employees`.`dept_emp_latest_date` `l` on(((`d`.`emp_no` = `employees`.`l`.`emp_no`) and (`d`.`from_date` = `employees`.`l`.`from_date`) and (`employees`.`l`.`to_date` = `d`.`to_date`)))) | NONE | YES | root@localhost | DEFINER | gbk | gbk_chinese_ci |
| def | employees | dept_emp_latest_date | select `employees`.`dept_emp`.`emp_no` AS `emp_no`,max(`employees`.`dept_emp`.`from_date`) AS `from_date`,max(`employees`.`dept_emp`.`to_date`) AS `to_date` from `employees`.`dept_emp` group by `employees`.`dept_emp`.`emp_no` | NONE | NO | root@localhost | DEFINER | gbk | gbk_chinese_ci |
| def | employees | test | select `employees`.`dept_emp`.`dept_no` AS `dept_no`,count(0) AS `emp_sum` from `employees`.`dept_emp` group by `employees`.`dept_emp`.`dept_no` order by `emp_sum` desc | NONE | NO | root@localhost | DEFINER | gbk | gbk_chinese_ci |
+---------------+--------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
3 rows in set (0.01 sec)4. 查看视图的定义内容
mysql> SHOW CREATE VIEW test;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `dept_emp`.`dept_no` AS `dept_no`,count(0) AS `emp_sum` from `dept_emp` group by `dept_emp`.`dept_no` order by `emp_sum` desc | gbk | gbk_chinese_ci |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)5. 使用 DESCRIBE 查看视图结构
mysql> DESCRIBE test;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| dept_no | char(4) | NO | | NULL | |
| emp_sum | bigint | NO | | 0 | |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
联结
使用联结,将创建的视图test补上部门名称,使用WHERE联结两个表的dept_on变量,这是一种等值联结/自然联结,因为dept_on在两张表都是唯一并且一一对应的。
mysql> select test.dept_no, emp_sum, dept_name-> from test, departments-> where test.dept_no = departments.dept_no;
+---------+---------+--------------------+
| dept_no | emp_sum | dept_name |
+---------+---------+--------------------+
| d009 | 23580 | Customer Service |
| d005 | 85707 | Development |
| d002 | 17346 | Finance |
| d003 | 17786 | Human Resources |
| d001 | 20211 | Marketing |
| d004 | 73485 | Production |
| d006 | 20117 | Quality Management |
| d008 | 21126 | Research |
| d007 | 52245 | Sales |
+---------+---------+--------------------+
9 rows in set (0.13 sec)INNER JOIN
当需要联结的两个联结键都是“唯一的”(内部没有重复值的意思),便需要使用内部联结,因为这种情况下是用WHERE和INNER JOIN语句是一样的,为了方便使用WHERE吧,如果语句赋值还是使用INNER JOIN
mysql> SELECT *-> FROM dept_manager, dept_emp-> WHERE dept_manager.emp_no = dept_emp.emp_no;
+--------+---------+------------+------------+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 | 110022 | d001 | 1985-01-01 | 9999-01-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 | 110039 | d001 | 1986-04-12 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 | 110085 | d002 | 1985-01-01 | 9999-01-01 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 | 110114 | d002 | 1985-01-14 | 9999-01-01 |
| 110183 | d003 | 1985-01-01 | 1992-03-21 | 110183 | d003 | 1985-01-01 | 9999-01-01 |
| 110228 | d003 | 1992-03-21 | 9999-01-01 | 110228 | d003 | 1985-08-04 | 9999-01-01 |
| 110303 | d004 | 1985-01-01 | 1988-09-09 | 110303 | d004 | 1985-01-01 | 9999-01-01 |
| 110344 | d004 | 1988-09-09 | 1992-08-02 | 110344 | d004 | 1985-11-22 | 9999-01-01 |
| 110386 | d004 | 1992-08-02 | 1996-08-30 | 110386 | d004 | 1988-10-14 | 9999-01-01 |
| 110420 | d004 | 1996-08-30 | 9999-01-01 | 110420 | d004 | 1992-02-05 | 9999-01-01 |
| 110511 | d005 | 1985-01-01 | 1992-04-25 | 110511 | d005 | 1985-01-01 | 9999-01-01 |
| 110567 | d005 | 1992-04-25 | 9999-01-01 | 110567 | d005 | 1986-10-21 | 9999-01-01 |
| 110725 | d006 | 1985-01-01 | 1989-05-06 | 110725 | d006 | 1985-01-01 | 9999-01-01 |
| 110765 | d006 | 1989-05-06 | 1991-09-12 | 110765 | d006 | 1989-01-07 | 9999-01-01 |
| 110800 | d006 | 1991-09-12 | 1994-06-28 | 110800 | d006 | 1986-08-12 | 9999-01-01 |
| 110854 | d006 | 1994-06-28 | 9999-01-01 | 110854 | d006 | 1989-06-09 | 9999-01-01 |
| 111035 | d007 | 1985-01-01 | 1991-03-07 | 111035 | d007 | 1985-01-01 | 9999-01-01 |
| 111133 | d007 | 1991-03-07 | 9999-01-01 | 111133 | d007 | 1986-12-30 | 9999-01-01 |
| 111400 | d008 | 1985-01-01 | 1991-04-08 | 111400 | d008 | 1985-01-01 | 9999-01-01 |
| 111534 | d008 | 1991-04-08 | 9999-01-01 | 111534 | d008 | 1988-01-31 | 9999-01-01 |
| 111692 | d009 | 1985-01-01 | 1988-10-17 | 111692 | d009 | 1985-01-01 | 9999-01-01 |
| 111784 | d009 | 1988-10-17 | 1992-09-08 | 111784 | d009 | 1988-02-12 | 9999-01-01 |
| 111877 | d009 | 1992-09-08 | 1996-01-03 | 111877 | d009 | 1991-08-17 | 9999-01-01 |
| 111939 | d009 | 1996-01-03 | 9999-01-01 | 111939 | d009 | 1989-07-10 | 9999-01-01 |
+--------+---------+------------+------------+--------+---------+------------+------------+
24 rows in set (0.00 sec)mysql>
mysql> SELECT *-> FROM dept_manager INNER JOIN dept_emp-> ON dept_manager.emp_no = dept_emp.emp_no;
+--------+---------+------------+------------+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+--------+---------+------------+------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 | 110022 | d001 | 1985-01-01 | 9999-01-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 | 110039 | d001 | 1986-04-12 | 9999-01-01 |
| 110085 | d002 | 1985-01-01 | 1989-12-17 | 110085 | d002 | 1985-01-01 | 9999-01-01 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 | 110114 | d002 | 1985-01-14 | 9999-01-01 |
| 110183 | d003 | 1985-01-01 | 1992-03-21 | 110183 | d003 | 1985-01-01 | 9999-01-01 |
| 110228 | d003 | 1992-03-21 | 9999-01-01 | 110228 | d003 | 1985-08-04 | 9999-01-01 |
| 110303 | d004 | 1985-01-01 | 1988-09-09 | 110303 | d004 | 1985-01-01 | 9999-01-01 |
| 110344 | d004 | 1988-09-09 | 1992-08-02 | 110344 | d004 | 1985-11-22 | 9999-01-01 |
| 110386 | d004 | 1992-08-02 | 1996-08-30 | 110386 | d004 | 1988-10-14 | 9999-01-01 |
| 110420 | d004 | 1996-08-30 | 9999-01-01 | 110420 | d004 | 1992-02-05 | 9999-01-01 |
| 110511 | d005 | 1985-01-01 | 1992-04-25 | 110511 | d005 | 1985-01-01 | 9999-01-01 |
| 110567 | d005 | 1992-04-25 | 9999-01-01 | 110567 | d005 | 1986-10-21 | 9999-01-01 |
| 110725 | d006 | 1985-01-01 | 1989-05-06 | 110725 | d006 | 1985-01-01 | 9999-01-01 |
| 110765 | d006 | 1989-05-06 | 1991-09-12 | 110765 | d006 | 1989-01-07 | 9999-01-01 |
| 110800 | d006 | 1991-09-12 | 1994-06-28 | 110800 | d006 | 1986-08-12 | 9999-01-01 |
| 110854 | d006 | 1994-06-28 | 9999-01-01 | 110854 | d006 | 1989-06-09 | 9999-01-01 |
| 111035 | d007 | 1985-01-01 | 1991-03-07 | 111035 | d007 | 1985-01-01 | 9999-01-01 |
| 111133 | d007 | 1991-03-07 | 9999-01-01 | 111133 | d007 | 1986-12-30 | 9999-01-01 |
| 111400 | d008 | 1985-01-01 | 1991-04-08 | 111400 | d008 | 1985-01-01 | 9999-01-01 |
| 111534 | d008 | 1991-04-08 | 9999-01-01 | 111534 | d008 | 1988-01-31 | 9999-01-01 |
| 111692 | d009 | 1985-01-01 | 1988-10-17 | 111692 | d009 | 1985-01-01 | 9999-01-01 |
| 111784 | d009 | 1988-10-17 | 1992-09-08 | 111784 | d009 | 1988-02-12 | 9999-01-01 |
| 111877 | d009 | 1992-09-08 | 1996-01-03 | 111877 | d009 | 1991-08-17 | 9999-01-01 |
| 111939 | d009 | 1996-01-03 | 9999-01-01 | 111939 | d009 | 1989-07-10 | 9999-01-01 |
+--------+---------+------------+------------+--------+---------+------------+------------+
24 rows in set (0.00 sec)
OUTER JOIN
如果两个表进行联结的两个联结键其中一个是有重复值的,一个是没有的,而且“有重复的”要比“唯一的”范围“大”,然后我们用"唯一的"的内容去填补“有重复的”表!
mysql> SELECT dept_manager.*, dept_name-> FROM dept_manager LEFT JOIN departments-> ON dept_manager.dept_no = departments.dept_no;
+--------+---------+------------+------------+--------------------+
| emp_no | dept_no | from_date | to_date | dept_name |
+--------+---------+------------+------------+--------------------+
| 110022 | d001 | 1985-01-01 | 1991-10-01 | Marketing |
| 110039 | d001 | 1991-10-01 | 9999-01-01 | Marketing |
| 110085 | d002 | 1985-01-01 | 1989-12-17 | Finance |
| 110114 | d002 | 1989-12-17 | 9999-01-01 | Finance |
| 110183 | d003 | 1985-01-01 | 1992-03-21 | Human Resources |
| 110228 | d003 | 1992-03-21 | 9999-01-01 | Human Resources |
| 110303 | d004 | 1985-01-01 | 1988-09-09 | Production |
| 110344 | d004 | 1988-09-09 | 1992-08-02 | Production |
| 110386 | d004 | 1992-08-02 | 1996-08-30 | Production |
| 110420 | d004 | 1996-08-30 | 9999-01-01 | Production |
| 110511 | d005 | 1985-01-01 | 1992-04-25 | Development |
| 110567 | d005 | 1992-04-25 | 9999-01-01 | Development |
| 110725 | d006 | 1985-01-01 | 1989-05-06 | Quality Management |
| 110765 | d006 | 1989-05-06 | 1991-09-12 | Quality Management |
| 110800 | d006 | 1991-09-12 | 1994-06-28 | Quality Management |
| 110854 | d006 | 1994-06-28 | 9999-01-01 | Quality Management |
| 111035 | d007 | 1985-01-01 | 1991-03-07 | Sales |
| 111133 | d007 | 1991-03-07 | 9999-01-01 | Sales |
| 111400 | d008 | 1985-01-01 | 1991-04-08 | Research |
| 111534 | d008 | 1991-04-08 | 9999-01-01 | Research |
| 111692 | d009 | 1985-01-01 | 1988-10-17 | Customer Service |
| 111784 | d009 | 1988-10-17 | 1992-09-08 | Customer Service |
| 111877 | d009 | 1992-09-08 | 1996-01-03 | Customer Service |
| 111939 | d009 | 1996-01-03 | 9999-01-01 | Customer Service |
+--------+---------+------------+------------+--------------------+
24 rows in set (0.00 sec)
🔍 JOIN 使用场景精要总结
1. INNER JOIN(内连接)
适用场景:
✅ 两个表的联结键都是唯一值(如主键或唯一索引)
✅ 只需要两表完全匹配的行(交集部分)
✅ 性能通常更好(只返回匹配数据)特点:WHERE 和 INNER JOIN 在唯一键联结时效果等价但 INNER JOIN 语法更清晰,推荐作为标准写法示例:sql
-- 查询既是经理又是员工的人(emp_no 是唯一键)
SELECT *
FROM dept_manager INNER JOIN dept_emp
ON dept_manager.emp_no = dept_emp.emp_no;
2. OUTER JOIN(外连接)
适用场景:
✅ 一个表的联结键有重复值,另一个是唯一值
✅ 需要保留主表所有行(即使另一表无匹配)
✅ 用“唯一键表”补充“重复键表”的缺失信息类型:LEFT JOIN:保留左表全部数据 + 右表匹配项(右表无匹配则补 NULL)RIGHT JOIN:保留右表全部数据(较少用,通常用 LEFT JOIN 替代)示例:sql
-- 查询所有部门经理及其部门名称(dept_no 在 departments 中是唯一的)
SELECT dept_manager.*, dept_name
FROM dept_manager LEFT JOIN departments
ON dept_manager.dept_no = departments.dept_no;
📌 关键区别
特性 INNER JOIN LEFT JOIN
返回结果 仅两表匹配的行 左表全部行 + 右表匹配行
联结键要求 最好都是唯一键 左表可重复,右表建议唯一
性能 更快(数据量少) 稍慢(需处理 NULL)
语义清晰度 明确要求匹配 明确保留主表数据
💡 小迪的黄金法则
优先用 INNER JOIN:除非需要保留不匹配的行。外连接时注意主表:LEFT JOIN 的主表是要保留全部数据的表。警惕重复键:如果联结键有重复值,结果行数可能爆炸式增长!
联结探究
我们来研究下不符合上面使用场景的情况是怎样的。我们以dept_emp和salaries表中员工号为10010的数据为例,因为刚好emp_no有“重复的”,接下来我们就用emp_no作为联结键。
mysql> SELECT *-> FROM dept_emp-> WHERE emp_no = 10010;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)mysql>
mysql> SELECT *-> FROM salaries-> WHERE emp_no = 10010;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10010 | 72488 | 1996-11-24 | 1997-11-24 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 |
+--------+--------+------------+------------+
6 rows in set (0.00 sec)
使用内部联结,INNER JOIN和WHERE一起上,结构一致,使用“有重复的”的联结键,产生的结果是笛卡尔积:2*6=12
mysql> SELECT *-> FROM salaries INNER JOIN dept_emp-> ON salaries.emp_no = dept_emp.emp_no-> WHERE salaries.emp_no = 10010;
+--------+--------+------------+------------+--------+---------+------------+------------+
| emp_no | salary | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+--------+------------+------------+--------+---------+------------+------------+
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+--------+------------+------------+--------+---------+------------+------------+
12 rows in set (0.00 sec)mysql>
mysql> SELECT *-> FROM salaries, dept_emp-> WHERE salaries.emp_no = dept_emp.emp_no AND salaries.emp_no = 10010;
+--------+--------+------------+------------+--------+---------+------------+------------+
| emp_no | salary | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+--------+------------+------------+--------+---------+------------+------------+
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+--------+------------+------------+--------+---------+------------+------------+
12 rows in set (0.00 sec)
使用外部链接,LEFT和RIGHT都上,结果也和内部联结相似,只是排序些许不同(不影响)。
mysql> SELECT *-> FROM salaries LEFT JOIN dept_emp-> ON salaries.emp_no = dept_emp.emp_no-> WHERE salaries.emp_no = 10010;
+--------+--------+------------+------------+--------+---------+------------+------------+
| emp_no | salary | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+--------+------------+------------+--------+---------+------------+------------+
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+--------+------------+------------+--------+---------+------------+------------+
12 rows in set (0.00 sec)mysql>
mysql> SELECT *-> FROM salaries RIGHT JOIN dept_emp-> ON salaries.emp_no = dept_emp.emp_no-> WHERE salaries.emp_no = 10010;
+--------+--------+------------+------------+--------+---------+------------+------------+
| emp_no | salary | from_date | to_date | emp_no | dept_no | from_date | to_date |
+--------+--------+------------+------------+--------+---------+------------+------------+
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d004 | 1996-11-24 | 2000-06-26 |
| 10010 | 72488 | 1996-11-24 | 1997-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 74347 | 1997-11-24 | 1998-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 75405 | 1998-11-24 | 1999-11-24 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 78194 | 1999-11-24 | 2000-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 79580 | 2000-11-23 | 2001-11-23 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
| 10010 | 80324 | 2001-11-23 | 9999-01-01 | 10010 | d006 | 2000-06-26 | 9999-01-01 |
+--------+--------+------------+------------+--------+---------+------------+------------+
12 rows in set (0.00 sec)
联结多个表
mysql> SELECT dept_manager.*, departments.dept_name, employees.first_name-> FROM dept_manager, departments, employees-> WHERE dept_manager.dept_no = departments.dept_no-> AND dept_manager.emp_no = employees.emp_no;
+--------+---------+------------+------------+--------------------+-------------+
| emp_no | dept_no | from_date | to_date | dept_name | first_name |
+--------+---------+------------+------------+--------------------+-------------+
| 111692 | d009 | 1985-01-01 | 1988-10-17 | Customer Service | Tonny |
| 111784 | d009 | 1988-10-17 | 1992-09-08 | Customer Service | Marjo |
| 111877 | d009 | 1992-09-08 | 1996-01-03 | Customer Service | Xiaobin |
| 111939 | d009 | 1996-01-03 | 9999-01-01 | Customer Service | Yuchang |
| 110511 | d005 | 1985-01-01 | 1992-04-25 | Development | DeForest |
| 110567 | d005 | 1992-04-25 | 9999-01-01 | Development | Leon |
| 110085 | d002 | 1985-01-01 | 1989-12-17 | Finance | Ebru |
| 110114 | d002 | 1989-12-17 | 9999-01-01 | Finance | Isamu |
| 110183 | d003 | 1985-01-01 | 1992-03-21 | Human Resources | Shirish |
| 110228 | d003 | 1992-03-21 | 9999-01-01 | Human Resources | Karsten |
| 110022 | d001 | 1985-01-01 | 1991-10-01 | Marketing | Margareta |
| 110039 | d001 | 1991-10-01 | 9999-01-01 | Marketing | Vishwani |
| 110303 | d004 | 1985-01-01 | 1988-09-09 | Production | Krassimir |
| 110344 | d004 | 1988-09-09 | 1992-08-02 | Production | Rosine |
| 110386 | d004 | 1992-08-02 | 1996-08-30 | Production | Shem |
| 110420 | d004 | 1996-08-30 | 9999-01-01 | Production | Oscar |
| 110725 | d006 | 1985-01-01 | 1989-05-06 | Quality Management | Peternela |
| 110765 | d006 | 1989-05-06 | 1991-09-12 | Quality Management | Rutger |
| 110800 | d006 | 1991-09-12 | 1994-06-28 | Quality Management | Sanjoy |
| 110854 | d006 | 1994-06-28 | 9999-01-01 | Quality Management | Dung |
| 111400 | d008 | 1985-01-01 | 1991-04-08 | Research | Arie |
| 111534 | d008 | 1991-04-08 | 9999-01-01 | Research | Hilary |
| 111035 | d007 | 1985-01-01 | 1991-03-07 | Sales | Przemyslawa |
| 111133 | d007 | 1991-03-07 | 9999-01-01 | Sales | Hauke |
+--------+---------+------------+------------+--------------------+-------------+
24 rows in set (0.00 sec)
SQL基本概念 - stream886 - 博客园https://www.bilibili.com/video/BV1cR4y1t7FA?spm_id_from=333.788.player.switch&vd_source=0d7eeb7b353e4faa1fb0433fdf7f3396
SQL to SQLAlchemyhttps://blog.csdn.net/three_man/article/details/46697159
mysql第一天 架构
基本语法CREATE TABLE new_table_name LIKE existing_table_name;
new_table_name:要创建的新表的名称。
existing_table_name:作为模板的现有表的名称。
此语法会复制 existing_table_name 表的所有列定义、索引以及约束到 new_table_name 表中1。示例代码
以下是一个具体的例子,展示如何使用 CREATE TABLE LIKE 创建一个新表:-- 假设有一个名为 `employees` 的表
CREATE TABLE employees_backup LIKE employees;上述语句将创建一个名为 employees_backup 的新表,其结构与 employees 表完全一致,但不包含任何数据1。如果需要同时复制数据,则可以结合 INSERT INTO SELECT 使用:-- 复制数据到新表
INSERT INTO employees_backup SELECT * FROM employees;注意事项
CREATE TABLE LIKE 不会复制触发器或视图等其他数据库对象3。
在不同数据库系统中,LIKE 的支持情况可能有所不同。例如,PostgreSQL 支持类似的 CREATE TABLE AS 功能,但行为略有差异2。
结合 INSERT INTO SELECT 的完整示例
以下是一个完整的示例,展示如何结合 CREATE TABLE LIKE 和 INSERT INTO SELECT 创建并填充新表:-- 创建结构相同的空表
CREATE TABLE employees_backup LIKE employees;-- 将数据从原表插入到新表
INSERT INTO employees_backup SELECT * FROM employees WHERE department = 'Sales';
上述代码将创建一个名为 employees_backup 的新表,并仅复制属于 Sales 部门的数据
4.1 克隆表
mysql> insert into mail2 select * from mail where srcuser = 'barb';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from mail2;
+---------------------+---------+---------+---------+---------+-------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+-------+
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
+---------------------+---------+---------+---------+---------+-------+
3 rows in set (0.00 sec)
4.2 将查询结果保存到表中
一、CREATE TABLE … LIKE + INSERT INTO … SELECT
原理
结构复制:通过 LIKE 关键字复制源表的完整结构(包括字段、索引、约束等),但不复制数据。
数据筛选插入:使用 INSERT INTO … SELECT 将源表中符合条件的数据插入新表。
实例
场景:从 employees 表复制 2023 年入职的员工到新表 employees_2023。
sql
-- 1. 创建结构相同的新表
mysql> CREATE TABLE employees_2023 LIKE employee;
Query OK, 0 rows affected (0.04 sec)-- 2. 插入符合条件的数据
INSERT INTO employees_2023
SELECT *
FROM employee
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
关键点:
新表 employees_2023 包含与 employees 相同的字段、索引和约束。
SELECT * 表示复制所有字段,需确保源表与目标表结构完全一致。
二、CREATE TABLE … AS SELECT
原理
结构与数据一次性创建:根据 SELECT 子句的字段动态创建新表,并将查询结果直接插入。
新表结构:仅包含 SELECT 中显式指定的字段,不继承源表的索引和约束(除非手动声明)。
实例
场景:创建 high_salary_employees 表,仅包含高薪员工的姓名和薪资。
sql
CREATE TABLE high_salary_employees AS
SELECT emp_no, -- 显式指定字段first_name+last_name,salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee); -- 筛选高薪员工
关键点:
新表 high_salary_employees 仅包含 emp_id、full_name、salary 三个字段。
不继承源表的索引和约束,如需需手动添加(如 PRIMARY KEY)。
三、两种方法对比
特性 CREATE TABLE ... LIKE + INSERT CREATE TABLE ... AS SELECT
结构完整性 完全复制源表结构(含索引、约束) 仅复制 SELECT 中指定的字段
数据筛选灵活性 支持复杂 WHERE 条件和多表连接 支持 WHERE,但需提前规划字段列表
性能(大数据量) 分两步执行,可能稍慢 一次性完成,通常更快
适用场景 需要完整保留源表结构,后续可能修改数据 快速创建临时表或统计结果表
四、注意事项
字段匹配:
使用 INSERT INTO ... SELECT 时,SELECT 的字段顺序和类型必须与目标表严格一致。
示例(指定字段避免顺序问题):
sql
INSERT INTO employees_2023 (emp_id, full_name, hire_date)
SELECT id, name, hired_at FROM employees WHERE ...;约束与索引:
CREATE TABLE ... AS 不会复制源表的约束(如 NOT NULL)和索引,需手动添加:
sql
CREATE TABLE high_salary_employees AS
SELECT emp_id, salary FROM employees WHERE ...;ALTER TABLE high_salary_employees ADD PRIMARY KEY (emp_id); -- 手动添加主键临时表优化:
若需快速统计,可结合 TEMPORARY 关键字:
sql
CREATE TEMPORARY TABLE temp_stats AS
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
五、实战案例
需求:从 orders 表复制 2023 年 1 月的订单到新表,并添加额外统计字段。
sql
-- 方法一:LIKE + INSERT
CREATE TABLE jan2023_orders LIKE orders;INSERT INTO jan2023_orders
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';-- 方法二:AS SELECT(带计算字段)
CREATE TABLE jan2023_orders_stats AS
SELECT order_id,customer_id,amount,amount * 0.9 AS discounted_amount -- 计算字段
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
通过上述方法,你可以灵活复制表结构和数据,满足不同场景需求。
https://www.modb.pro/db/408221
MySQL 样例数据库 Employee 的制作过程
相关文章:

板凳-------Mysql cookbook学习 (九)
第4章:表管理 4.0 引言 MySQL :: 员工样例数据库 :: 3 安装 https://dev.mysql.com/doc/employee/en/employees-installation.html Employees 数据库与几种不同的 存储引擎,默认情况下启用 InnoDB 引擎。编…...
深入解析 Flask 命令行工具与 flask run命令的使用
Flask 是一个轻量级的 Python Web 应用框架,其内置的命令行工具(CLI)基于 Click 库,提供了方便的命令行接口,用于管理和运行 Flask 应用程序。本文将详细介绍 Flask 命令行工具的功能,以及如何使用 flask r…...
第6篇:中间件 SQL 重写与语义分析引擎实现原理
6.1 章节导读 SQL 是数据库中间件的“输入语言”。 在一个真正强大的中间件系统中,SQL 语句的执行通常不再是“原封不动”地传递给数据库,而是需要先经过: 语义分析:解析 SQL 的结构和含义。 SQL 重写:根据中间件逻辑…...

基于SpringBoot的“嗨玩旅游”网站设计与实现(源码+定制+开发)嗨玩旅游平台开发:景点展示与个性化推荐系统(SpringBoot)
博主介绍: ✌我是阿龙,一名专注于Java技术领域的程序员,全网拥有10W粉丝。作为CSDN特邀作者、博客专家、新星计划导师,我在计算机毕业设计开发方面积累了丰富的经验。同时,我也是掘金、华为云、阿里云、InfoQ等平台…...

python版若依框架开发:python版若依部署
python版若依框架开发 从0起步,扬帆起航。 python版若依部署文章目录 python版若依框架开发1.源码2.概述3.部署1.源码 https://gitee.com/insistence2022/RuoYi-Vue-FastAPI 请诸君移步上述链接,即可对python版若依项目进行初步了解。 2.概述 若依框架本身基于java,可以快…...
React进阶:状态管理选择题
React进阶:状态管理选择题 引言 随着React应用复杂度增加,选择合适的状态管理方案成为我们面临的关键决策。 状态管理本质上是解决"谁来存储数据"以及"如何更新和分发这些数据"的问题。在React生态中,随着应用规模扩大…...

h5的aliplayer-min.js 加密视频会走到debugger
h5的aliplayer-min.js 如果 https://g.alicdn.com/apsara-media-box/imp-web-player/2.19.0/aliplayer-min.js走加密视频的话会有debugger 更换aliplayer-min.js版本解决了 https://g.alicdn.com/apsara-media-box/imp-web-player/2.25.1/aliplayer-min.js 对应css:…...
第5篇《中间件负载均衡与连接池管理机制设计》
5.1 章节导读 在数据库中间件中,如何高效地管理数据库连接与请求调度至关重要。 本节围绕两个核心模块展开: 连接池管理:提升连接复用能力,避免频繁建立/断开连接。 负载均衡策略:合理调度 SQL 请求,提升…...

DashBoard安装使用
DashBoard安装使用 一、实验目的 1、掌握dashboard 的安装部署 2、熟悉图像化部署任务:产生pod---定义服务--验证访问 二、实验内容: 1、配置步骤 1.1、Helm安装 离线安装(适用于内网/离线环境) # 根据系统架构选择版本&am…...

极客大挑战 2019 EasySQL 1(万能账号密码,SQL注入,HackBar)
题目 做法 启动靶机,打开给出的网址 随便输点东西进去,测试一下 输入1、1’、1"判断SQL语句闭合方式 输入以上两个都是以下结果 但是,输入1’时,出现的是另外结果 输入1,1"时,SQL语句没有…...
C# CallerMemberName特性
当你在一个方法运用了CallerMemberName特性,编译器会自动将调用该方法的方法或属性的名称作为该参数的默认值,可应用于MVVM框架。 代码: using System.ComponentModel; using System.Runtime.CompilerServices;public class Person : INoti…...

采用 Docker GPU 部署的 Ubuntu 或者 windows 桌面环境
# 国内下载不了 docker pull gezp/ubuntu-desktop:24.04-cu12.6.2# 阿里云镜像 docker pull registry.cn-hongkong.aliyuncs.com/gezp/ubuntu-desktop:24.04-cu12.6.2# create container with nomachine docker run -d --restarton-failure --name myubuntu --shm-size1024m -e…...

关于面试找工作的总结(四)
不同情况下收到offer后的处理方法 1.不会去的,只是面试练手2.还有疑问,考虑中3.offer/职位不满足期望的4.已确认,但又收到更好的5.还想挽回之前的offer6.确认,准备入职7.还想拖一下的1.不会去的,只是面试练手 HR您好,非常荣幸收到贵司的offer,非常感谢一直以来您的帮助,…...

分布式拜占庭容错算法——实现工作量证明(PoW)算法详解
Java 实现工作量证明(PoW)算法详解 一、PoW 核心原理 #mermaid-svg-AAj0Pvst1PVcVy5v {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-AAj0Pvst1PVcVy5v .error-icon{fill:#552222;}#mermaid…...

深度解析Mysql中MVCC的工作机制
MVCC,多版本并发控制 定义:维护一个数据的多个版本,使读写操作没有冲突,依赖于:隐藏字段,undo log日志,readView MVCC会为每条版本记录保存三个隐藏字段 DB_TRX_ID: 记录最近插入或修改该记录的事务IDDB_R…...

MP4文件声音与视频分离
最近学习PR剪辑 要添加视频文件和音频文件 但是直接给MP4文件 得到的是一个整体 不管怎么切分 都是无法得到单独的整体 这就需要将视频文件和音频文件分离 我推荐使用ffmpeg工具进行分离 迅雷链接:https://pan.xunlei.com/s/VORu5x64jjL-gXFd_VTpYjRPA1?pwd8wec#…...

接口自动化测试之pytest 运行方式及前置后置封装
🍅 点击文末小卡片,免费获取软件测试全套资料,资料在手,涨薪更快 一、Pytest 优点认知 1.可以结合所有的自动化测试工具 2.跳过失败用例以及失败重跑 3.结合allure生产美观报告 4.和Jenkins持续集成 5.很多强大的插件 pytest-htm…...
服务器被攻击了怎么办
可以上一个高防IP或者AI云防护都是可以的。(有效防御CC、APl接口、http、tcp、WEB应用扫描/爬虫、SYN、WAF、DDOS、UDP、入侵、渗透、SQL注入、XSS跨站脚本攻击、远程恶意代码执行、session fixation、Webshell攻击、恶意请求,恶意扫描、暴力破解、CSRF等…...

06-排序
排序 1. 排序的概念及其应用 1.1 排序的概念 排序:所谓排序,就是使一串记录,按照其中的某个或某些关键字的大小,递增或递减的排列起来的操作。 稳定性:假定在待排序的记录序列中,存在多个具有相同的关键…...
python,shell,linux,bash概念的不同和对比联系
一、基本概念理解 1. Linux 是一个 操作系统内核,常与 GNU 工具集成组成完整的 Linux 操作系统。 提供对硬件的管理能力与系统调用接口。 用户通过 Shell 或 GUI 与 Linux 交互。 2. Shell 是用户与 Linux 内核之间的 命令行解释器(CLI)…...

FPGA管脚类型,及选择
fpga的IO Type选择,如下: 具体的定义:...

如何在 Ubuntu22.04 上安装并开始使用 RabbitMQ
单体架构学的差不多了,可以朝着微服务进军了,笔者打算实操一下 RabbitMQ(这个和 Redis 一样重要的组件) 笔者这里采用的是本地 wsl2 的 Ubuntu22.04 先按指定的博客进行前置操作 Ubuntu22.04 安装 RabbitMQ 解决 ARM Ubuntu 22.04 缺少 libs…...

R-CNN 模型算法流程梳理
目录 一、R-CNN整体流程 二、需要注意的地方 论文连接:[1311.2524] Rich feature hierarchies for accurate object detection and semantic segmentation 如果你之前了解过RNN,很容易混淆认为R-CNN也具有RNN的时序循环功能,这种理解是错误…...
细说C语言将格式化输出到FILE *stream流的函数fprintf、_fprintf_I、fwprintf、_fwprintf_I
目录 1、将格式化数据输出到FILE *stream流基本型 (1)语法 (2)参数 (3)示例 2、将格式化数据输出到FILE *stream流并启用并启用在格式字符串中使用参数的顺序的规范 (1)语法 …...

本地日记本,用于记录日常。
文章目录 想法程序说明展望 想法 本人想要复盘以前的事情,所以就想着写一个小程序,记录一下一天发生了什么事情。以后如果忘记了可以随时查看。写日记的想法来自我看的一本书,里面有一段话说的意思是,经验从来都不是随着年龄增长…...

[蓝桥杯]格子刷油漆
格子刷油漆 题目描述 X 国的一段古城墙的顶端可以看成 2N2N 个格子组成的矩形(如下图所示),现需要把这些格子刷上保护漆。 你可以从任意一个格子刷起,刷完一格,可以移动到和它相邻的格子(对角相邻也算数&…...
Monorepo架构: 项目管理工具介绍、需求分析与技术选型
概述 如何实现 monorepo,以及在项目中如何管理多个包,在进行具体项目开发前,有必要强调一个重要思维 — 全局观 即看待技术方案时,要从需求角度出发,综合考量该方案能否长远满足项目或团队需求 为什么要有全局观呢&a…...

ubuntu下libguestfs-tools
在ubuntu下,使用libguestfs-tools工具挂载其他磁盘和分区。 首先安装libguestfs-tools将vmx虚拟磁盘共享:sudo vmhgfs-fuse .host:/ /mnt/hgfs -o allow_other执行如下命令查看分区名称:virt-filesystems -a /mnt/hgfs/D/vmware/FGT_VM64-v7…...

Authentication failed(切换了新的远程仓库tld)
启用 Git Credential Manager git config --global credential.helper manager 强制弹出凭据输入窗口 git config --global credential.helper.modalprompt true 指定 TFS 服务器使用基础认证(Basic Auth) git config --global credential.https://…...

【Web应用】若依框架:基础篇14 源码阅读-后端代码分析-课程管理模块前后端代码分析
文章目录 一、课程管理模块前端代码截图二、前端代码及分析index.vuecourse.js 三、前端执行流程1. 组件初始化2. 查询操作3. 列表操作4. 对话框操作5. API 请求6. 执行流程总结关键点 四、课程管理模块后端代码截图五、后端代码块CourseControllerICourseServiceCourseMapperC…...