49.针对库中的所有表生成对应的SQL语句.md · viva302/数据库SQL实战 - Gitee

forked from 柯基与佩奇/数据库SQL实战

Create your Gitee Account

Explore and code with more than 12 million developers，Free private repositories ！：）

This repository doesn't specify license. Please pay attention to the specific project description and its upstream code dependency when using it.

Clone or Download

49.针对库中的所有表生成对应的SQL语句.md 1.54 KB

# 针对库中的所有表生成select count(*)对应的SQL语句

## 题目描述

针对库中的所有表生成select count(*)对应的SQL语句

``` mysql
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY ( `emp_no` ));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `dept_no` ));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `dept_no` ));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `from_date` ));
```

## 答案

``` sqlite
select "select count(*) from " || name || ";" cnts from sqlite_master where type = "table"
```

## 题解

MySQL语法：

``` mysql
select concat('select count(*) from ',TABLE_NAME,';') cnts from (select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'employees') t
```

Copy Edit Raw Blame History

authored 2020-09-29 10:31 . 针对库中的所有表生成select count(*)对应的SQL语句

<!--
 * @Author: your name
 * @Date: 2020-09-21 17:24:24
 * @LastEditTime: 2020-09-29 10:30:50
 * @LastEditors: your name
 * @Description: In User Settings Edit
 * @FilePath: \database-sql-combat\49.针对库中的所有表生成对应的SQL语句.md
-->
# 针对库中的所有表生成select count(*)对应的SQL语句

## 题目描述

针对库中的所有表生成select count(*)对应的SQL语句

``` mysql
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY ( `emp_no` ));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `dept_no` ));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `dept_no` ));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY ( `emp_no` , `from_date` ));
```

## 答案

``` sqlite
select "select count(*) from " || name || ";" cnts from sqlite_master where type = "table"
```

## 题解

MySQL语法：

``` mysql
select concat('select count(*) from ',TABLE_NAME,';') cnts from (select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'employees') t
```

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

SQL

1

https://gitee.com/viva302/database-sql-combat.git

git@gitee.com:viva302/database-sql-combat.git

viva302

database-sql-combat

数据库SQL实战

master