从这些表中查询数据

2019-10-05 作者:数据库   |   浏览(171)

 

本篇介绍如何使用SSIS和作业完成自动更新目标数据任务。 

博主在工作中,常常需要使用sql语句来进行查询,总结发现,灵活使用这几个要点,就可以应付大部分简单情况。

use master

Preface

 

一.连接:根据两个或多个表中的列之间的关系,从这些表中查询数据。

 

 

** 温馨提示:如需转载本文,请注明内容出处。**

  • JOIN或INNER JOIN: 返回左表和右表中相互匹配的行
  • LEFT JOIN: 返回左表和右表中相互匹配的行,及左表中不相匹配的行
  • RIGHT JOIN: 返回左表和右表中相互匹配的行,及右表中不相匹配的行
  • FULL JOIN或FULL OUTER JOIN: 返回左表和右表中相互匹配的行,及不相匹配的行
  • 图片 1 
  • 应用举例
  • 在一些题目中没有明确的暗示情况下,使用哪一种连接方式都可以;
  • 也有一些情况,只能使用特定的连接方式。

alter database mfv408_Tift1 add log file

    How to rescue a dropped or truncated table online?Dropping or truncating is ddl operation which cannot be flashed back by the populare flashback tools like MyFlash,binlog2mysql,mysqldump_backup,etc.Therefore,the conventional method is restoring the database to a newly initialized instance on another server with backup(physical or logical).Whatif the backup set is rather huge for example the mysqldump backup is more than 200G?It will cost a long time to rescue the dropped table back.Is there an effective way to accomplish the issue?Let's see the tests below.

本文链接:https://www.cnblogs.com/grom/p/9018978.html 

1.join/inner join

(

 

 

必须使用inner join的情况

name=mfv408_Tift1_log,filename='C:_DISKdata1mfv408_Tift1_log2.ldf',

Framework

  笔者需要定期从服务器更新N家客户的远程服务器数据,上一篇的存储过程是其中一个更新方法,后来随着数据量逐渐增大,受网络环境的影响,批量远程插入消耗的时间已经无法承受,后来在导出数据时发现了保存SSIS选项,然后展开了测试开发,现将成果分享出来供大家参考,类似的资料不少,所以就简单写写操作步骤。

1  --查询所有未讲课的教师的Tname和Depart. 
2   select TNAME,DEPART 
3   from TEACHER 
4   where TNO not in(select TEACHER.TNO from COURSE inner join 
5   TEACHER on COURSE.TNO=TEACHER.TNO)
6   --或者使用 Except
7     select TNAME,DEPART from TEACHER
8     except
9     select TNAME,DEPART from COURSE join TEACHER on COURSE.TNO=TEACHER.TNO

    SIZE = 1024MB,

 

 

上面的sql语句中的“select TEACHER.TNO from COURSE inner join TEACHER on COURSE.TNO=TEACHER.TNO”中,只能使用inner join,使用left join、right join、full join都是错误的。

    MAXSIZE = UNLIMITED,

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

首先,尽量可能使用部署到环境的Windows账号,比如部署到服务器,就远程到服务器后使用Windows账号登录,可以避免很多权限上的麻烦。

 2.left join和right join 

    FILEGROWTH = 10%

 

另外,注意客户端的版本,SSMS一定不能低于SQL服务版本,不然会出异常。

使用了left join 的情况,调换一下表的位置,就可以用right join替换;反之亦然。

)

Precedure

 

1 --查询所有学生的Sname、Cname和Degree列。
2 select SNAME,CNAME,DEGREE 
3 from STUDENT left join SCORE on STUDENT.SNO=SCORE.SNO 
4 left join COURSE on SCORE.CNO=COURSE.CNO

go

 

微软官网文档:

也可以写成

 

**Test1:Rescue a table after dropping it based on a new mysqldump backup.**

 

1 select SNAME,CNAME,DEGREE 
2  from score  right join STUDENT on STUDENT.SNO=SCORE.SNO 
3 right join COURSE on SCORE.CNO=COURSE.CNO

 

 

导出导入数据,SSIS包部署在源库还是目标库均可,下面以导出数据为例。

 3.使用full join的情况较少,暂时还没有用到。

DBCC SHRINKFILE('mfv408_Tift1_log',10)

Generate the test data with sysbench.

 

 

 

 1 [root@zlm2 07:30:58 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=10000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 10000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 10000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 10000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 10000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 10000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 10000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 10000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 10000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 10000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 10000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 (zlm@192.168.1.101 3306)[sysbench]>show tables;
37  -------------------- 
38 | Tables_in_sysbench |
39  -------------------- 
40 | sbtest1            |
41 | sbtest10           |
42 | sbtest2            |
43 | sbtest3            |
44 | sbtest4            |
45 | sbtest5            |
46 | sbtest6            |
47 | sbtest7            |
48 | sbtest8            |
49 | sbtest9            |
50  -------------------- 
51 10 rows in set (0.00 sec)

右键导出数据的库 任务 导出数据

二、嵌套

USE master;

 

 

在sql语言中,一个select-from-where语句成为一个查询块。

GO

**Backup the database sysbench with mysqldump.**

图片 2

将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询成为嵌套查询。

ALTER DATABASE mfv408_Tift1 

 1 [root@zlm2 07:32:01 ~]
 2 #mysqldump --single-transaction --master-data=2 -A > db3306_`date  %Y%m%d`.sql
 3 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 4 
 5 [root@zlm2 07:32:09 ~]
 6 #ls -l
 7 total 34744
 8 drwxr-xr-x   2 root root     4096 Jul 23 10:10 20180723
 9 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
10 -rw-r--r--   1 root root 20390934 Jul 26 07:32 db3306_20180726.sql
11 -rw-r--r--   1 root root  7333548 Jul 24 02:48 db.sql
12 -rwxr-xr-x   1 root root       54 Jun 13 04:16 mysqld.sh
13 -rwxr-xr-x   1 root root  7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
14 drwxr-xr-x  13 root root     4096 Jul  4 03:21 sysbench-1.0
15 
16 [root@zlm2 07:32:11 ~]
17 #scp db3306_20180726.sql zlm3:/data/backup
18 root@zlm3's password: 
19 db3306_20180726.sql                                                                                                100%   19MB  19.5MB/s   00:00    
20 
21 [root@zlm2 07:33:35 ~]

 

 SQL语言允许多层嵌套查询,但要注意的是,子查询的select语句中不能使用order 不要子句,order by子句只能对最终查询结果排序。

REMOVE FILE mfv408_Tift1_log;

 

选择导出数据库(数据源库)

当掌握了sql语句的执行顺序后,就会对这个限制的原因有更深刻的理解。

GO

**Drop one table in database "sysbench".**

图片 3

举例

 

 

 

子查询在where子句中

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest10; 
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5  -------------------- 
 6 | Tables_in_sysbench |
 7  -------------------- 
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 | sbtest7            |
15 | sbtest8            |
16 | sbtest9            |
17  -------------------- 
18 9 rows in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>

 选择目标库

1 --查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
2 select * from SCORE where DEGREE>(select DEGREE from SCORE where SNO='109' and CNO='3-105')

 

图片 4

 

Create a rescue environment in an initialized instance on zlm3.

 

三、子查询

 1 (zlm@192.168.1.102 3306)[(none)]>show databases;
 2  -------------------- 
 3 | Database           |
 4  -------------------- 
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9  -------------------- 
10 4 rows in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[(none)]>create database sysbench; //Create a same name database.
13 Query OK, 1 row affected (0.00 sec)
14 
15 (zlm@192.168.1.102 3306)[(none)]>show databases;
16  -------------------- 
17 | Database           |
18  -------------------- 
19 | information_schema |
20 | mysql              |
21 | performance_schema |
22 | sys                |
23 | sysbench           |
24  -------------------- 
25 5 rows in set (0.00 sec)
26 
27 (zlm@192.168.1.102 3306)[(none)]>create user rescue@'192.168.1.%' identified by 'rescue'; //Create a rescue user called "rescue".
28 Query OK, 0 rows affected (0.00 sec)
29 
30 (zlm@192.168.1.102 3306)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges to user ""rescue.
31 ERROR 1142 (42000): GRANT command denied to user 'zlm'@'zlm3' for table 'sbtest10' //It seems current user does not has the privilege to grant.
32 (zlm@192.168.1.102 3306)[(none)]>exit
33 Bye
34 
35 [root@zlm3 07:49:50 ~]
36 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock //Login with root user.
37 mysql: [Warning] Using a password on the command line interface can be insecure.
38 Welcome to the MySQL monitor.  Commands end with ; or g.
39 Your MySQL connection id is 6
40 Server version: 5.7.21-log MySQL Community Server (GPL)
41 
42 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
43 
44 Oracle is a registered trademark of Oracle Corporation and/or its
45 affiliates. Other names may be trademarks of their respective
46 owners.
47 
48 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
49 
50 (root@localhost mysql3306.sock)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges again.It works.
51 Query OK, 0 rows affected (0.00 sec)

编辑要传输的表,有不同需求(如需要删除目标表内原数据实现全覆盖等)时,可在编辑映射里设置。

上层的查询块称为外层查询或者父查询,下层查询块称为内层查询或者子查询。

 

图片 5

子查询的位置可以很灵活,掌握这一点,感觉sql学习就有了很大的进展。

**Check the backup set and import it.**

 

1.子查询可以放到where子句或having短语的条件中,这就是我们上面所介绍的嵌套查询

 1 [root@zlm3 07:59:28 /data/backup]
 2 #ls -l|grep db3306
 3 -rw-r--r-- 1 root  root   20390934 Jul 26 07:33 db3306_20180726.sql
 4 
 5 [root@zlm3 07:59:42 /data/backup]
 6 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql 
 7 mysql: [Warning] Using a password on the command line interface can be insecure.
 8 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 9 ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
10 ERROR 1044 (42000) at line 36: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
11 ERROR 1044 (42000) at line 38: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
12 ERROR 1046 (3D000) at line 44: No database selected
13 
14 //A bundle of "No database seelcted" message has been omitted.
15 
16 ERROR 1046 (3D000) at line 915: No database selected
17 ERROR 1044 (42000) at line 935: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
18 ERROR 1142 (42000) at line 943: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest1'
19 ERROR 1142 (42000) at line 946: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest1'
20 ERROR 1044 (42000) at line 960: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
21 ERROR 1142 (42000) at line 961: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
22 ERROR 1142 (42000) at line 962: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
23 ERROR 1142 (42000) at line 963: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
24 ERROR 1142 (42000) at line 964: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
25 ERROR 1044 (42000) at line 988: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
26 ERROR 1142 (42000) at line 999: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest2'
27 ERROR 1142 (42000) at line 1002: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest2'
28 ERROR 1044 (42000) at line 1016: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
29 ERROR 1142 (42000) at line 1017: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
30 ERROR 1142 (42000) at line 1018: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
31 ERROR 1142 (42000) at line 1019: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
32 ERROR 1142 (42000) at line 1020: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
33 ERROR 1142 (42000) at line 1027: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest3'
34 ERROR 1142 (42000) at line 1030: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest3'
35 ERROR 1044 (42000) at line 1044: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
36 ERROR 1142 (42000) at line 1045: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
37 ERROR 1142 (42000) at line 1046: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
38 ERROR 1142 (42000) at line 1047: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
39 ERROR 1142 (42000) at line 1048: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
40 ERROR 1142 (42000) at line 1055: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest4'
41 ERROR 1142 (42000) at line 1058: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest4'
42 ERROR 1044 (42000) at line 1072: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
43 ERROR 1142 (42000) at line 1073: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
44 ERROR 1142 (42000) at line 1074: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
45 ERROR 1142 (42000) at line 1075: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
46 ERROR 1142 (42000) at line 1076: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
47 ERROR 1142 (42000) at line 1083: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest5'
48 ERROR 1142 (42000) at line 1086: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest5'
49 ERROR 1044 (42000) at line 1100: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
50 ERROR 1142 (42000) at line 1101: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
51 ERROR 1142 (42000) at line 1102: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
52 ERROR 1142 (42000) at line 1103: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
53 ERROR 1142 (42000) at line 1104: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
54 ERROR 1142 (42000) at line 1111: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest6'
55 ERROR 1142 (42000) at line 1114: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest6'
56 ERROR 1044 (42000) at line 1128: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
57 ERROR 1142 (42000) at line 1129: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
58 ERROR 1142 (42000) at line 1130: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
59 ERROR 1142 (42000) at line 1131: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
60 ERROR 1142 (42000) at line 1132: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
61 ERROR 1142 (42000) at line 1139: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest7'
62 ERROR 1142 (42000) at line 1142: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest7'
63 ERROR 1044 (42000) at line 1156: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
64 ERROR 1142 (42000) at line 1157: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
65 ERROR 1142 (42000) at line 1158: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
66 ERROR 1142 (42000) at line 1159: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
67 ERROR 1142 (42000) at line 1160: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
68 ERROR 1142 (42000) at line 1167: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest8'
69 ERROR 1142 (42000) at line 1170: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest8'
70 ERROR 1044 (42000) at line 1184: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
71 ERROR 1142 (42000) at line 1185: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
72 ERROR 1142 (42000) at line 1186: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
73 ERROR 1142 (42000) at line 1187: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
74 ERROR 1142 (42000) at line 1188: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
75 ERROR 1142 (42000) at line 1195: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest9'
76 ERROR 1142 (42000) at line 1198: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest9'
77 ERROR 1044 (42000) at line 1212: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
78 ERROR 1142 (42000) at line 1213: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
79 ERROR 1142 (42000) at line 1214: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
80 ERROR 1142 (42000) at line 1215: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
81 ERROR 1142 (42000) at line 1216: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
82 ERROR 1044 (42000) at line 1223: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
83 ERROR 1044 (42000) at line 1225: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
84 ERROR 1227 (42000) at line 1226: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
85 
86 //The other tables in backup set will be skipped except for table "sbtest10".

 批量设置图片 6或单表设置图片 7

2.子查询可以出现在from子句中,这时子查询生成的临时派生表,称为主查询的查询对象

 

 

1 --查询所有选修了1号课程的学生姓名  (当然也可以使用嵌套查询)
2 select Sname from Student,(select * from SC where cno='1') SC1 where Student.sno=SC1.sno

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:从这些表中查询数据

关键词: 小鱼儿玄机30码