David LeBauer's questions

Cada tabela em meu banco de dados ( ver esquema ) tem uma chave primária chamada id.

Gostaria de encontrar todos os registros em todas as tabelas em que id está no intervalo [1.1000]. Como posso escrever uma consulta dessas?

Eu tentei escrever um loop for seguindo os documentos do PostgreSQL

FOR T IN (
  SELECT TABLE_NAME FROM information_schema. TABLES
  WHERE table_schema = 'public'
    )
LOOP
    SELECT id, T FROM T WHERE ID BETWEEN 1 AND 1000;
  RETURN NEXT T;
END LOOP ;

mas obtém "Erro de sintaxe em FOR".

Estou usando o PostgreSQL e tentando escrever restrições de valor.

É possível registrar dados ausentes como distintos de valores NULL em um campo numérico sem usar um valor numérico arbitrário?

Para muitos campos, quero garantir que o registro seja numérico ou registrado como ausente, mas não NULL.

Uma solução comum é usar -9999 para indicar um valor ausente. No entanto, isso pode levar a erros silenciosos (por exemplo, calcular uma média). Existe uma solução melhor do que criar um novo tipo que permita um valor de "Missing" ou um número?

Eu tenho um banco de dados MySQL e usei o MySQL Workbench para inserir texto nos campos de "comentário" de muitas das colunas e tabelas; o plano era ter a documentação armazenada no banco de dados.

No entanto, não sei como a) adicionar um comentário a uma coluna ou, mais importante, b) recuperar essas informações.

Examinei a documentação limitada do MySQL em Descrever , Mostrar colunas e Mostrar extensões .

Por exemplo, eu gostaria de retornar isso:

mysql> describe dogs;

+----------------+-------------------------------+
| Field          | Comment                       | 
+----------------+-------------------------------+
| id             | primary key                   | 
| Name           | Dog's name                    | 
| species_id     | Foreign key to look up species| 

Ao invés de

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment | 
| name           | varchar (255)| YES  | MUL | NULL    |                | 
| species_id     | int(11)      | YES  | MUL | NULL    |                |

Embora isso faria:

mysql> describe dogs;

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| Field          | Type         | Null | Key | Default | Extra          | Comment                       | 
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |                               |
| name           | varchar (255)| YES  | MUL | NULL    |                | Dog's name                    |  
| species_id     | int(11)      | YES  | MUL | NULL    |                | Foreign key to look up species|

Quais são minhas opções? O MySQL workbench exporta essa documentação?

Ao criar tabelas de várias junções para uso em análise, quando é preferível usar visualizações em vez de criar uma nova tabela?

Uma razão pela qual eu preferiria usar visualizações é que o esquema do banco de dados foi desenvolvido por nosso administrador de dentro do Ruby, e eu não estou familiarizado com o Ruby. Posso solicitar a criação de tabelas, mas requer uma etapa adicional e gostaria de mais flexibilidade ao desenvolver/testar novas junções.

Comecei a usar visualizações seguindo a resposta a uma pergunta relacionada no SO ( Quando usar R, quando usar SQL ). A resposta mais votada começa "faça as manipulações de dados em SQL até que os dados estejam em uma única tabela e, em seguida, faça o resto em R".

Comecei a usar visualizações, mas encontrei alguns problemas com visualizações:

1. as consultas são muito mais lentas
2. As visualizações não são despejadas da produção para o banco de dados de backup que uso para análise.

As visualizações são apropriadas para esse uso? Em caso afirmativo, devo esperar uma penalidade de desempenho? Existe uma maneira de acelerar as consultas nas visualizações?

Qual é a maneira mais fácil de atualizar muitas linhas em uma tabela? Eu tenho um arquivo csv que se parece com isso:

|primary_key |value|
|          1 |  xyz|
|          2 |  abc|
|          3 |  def|
...

As linhas com essas chaves primárias já existem na tabela de destino

Gostaria de atualizar a tabela de destino com esses valores. Existe sintaxe para que eu possa escrever algo como:

update mytable set value = ('xyz', 'abc', 'def') where primary key = (1,2,3);

Olhando através do MySQL Update Reference , este site ( MySQL - csv update ), SO ( update multiple rows , multiple db updates , update multiple rows ), suspeito que a resposta seja "não", mas gostaria de confirmar que isso é verdade.

Qual é a melhor maneira de restaurar uma única tabela de um arquivo mysqldump de banco de dados grande (~ 5 GB)?

atualização: encontrei soluções (postadas abaixo) usando ferramentas de linha de comando para analisar a tabela, mas existe uma maneira de fazer isso com mysqlimport?

Existe uma consulta simples que levaria > 2 segundos para que eu possa testar o registrador de consulta lenta?

Estou procurando algo como uma instrução recursiva ou iterativa genérica.

nota: esta pergunta foi atualizada para refletir que estamos usando MySQL atualmente, tendo feito isso, gostaria de ver como seria muito mais fácil se mudássemos para um banco de dados compatível com CTE.

Eu tenho uma tabela de auto-referência com uma chave primária ide uma chave estrangeira parent_id.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
| notes      | text         | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

Dado um name, como posso consultar o pai de nível superior?

Dado um name, como posso consultar todos os idassociados a um registro de name = 'foo'?

context: Não sou dba, mas estou planejando pedir a um dba para implementar esse tipo de estrutura hierárquica e gostaria de testar algumas consultas. A motivação para isso é descrita por Kattge et al 2011 .

Aqui está um exemplo das relações entre os ids na tabela:

-- -----------------------------------------------------
-- Create a new database called 'testdb'
-- -----------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `testdb` ;

-- -----------------------------------------------------
-- Table `testdb`.`observations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Add Example Data Set
-- -----------------------------------------------------


INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10), 
   (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);

Estou criando um banco de dados para registros que se estendem antes de 1000 AD, mas os campos MySQL Date e DateTime suportam apenas datas a partir de 1000.

Existe uma maneira que seria mais conveniente do que usar um tipo bigint para contar segundos antes/depois de 1/1/1970 usando um timestamp Unix ou mudar para um software de banco de dados que suporte intervalos de datas maiores?

Eu tenho um arquivo .my.cnf em meu diretório inicial em minha área de trabalho que inclui:

[dbid]
user = myusername
password = mypassword
database = dbname
host = server.location.com

Se bem me lembro, antes de atualizar do Ubuntu 10.04 para 11.04, consegui usar o comando

mysql dbid

Para se conectar diretamente ao banco de dados

Mas hoje recebo este erro:

ERROR 1049 (42000): Unknown database 'dbname'

Fiz algo errado?

Fundo

No processo de QA/QC, estou escrevendo muitas consultas com várias junções. Estou usando MySQL.

Gostaria de saber como (se) posso simplificar as instruções de junção, por exemplo, definindo campos padrão para junção.

Minhas tabelas são nomeadas com uma forma plural do objeto que elas contêm:

 names
 types
 actions
 surnames
 names_surnames

onde names_surnamesestá uma tabela de pesquisa muitos-muitos

as chaves primárias são sempre ide as chaves estrangeiras são sempre, por exemplo

 names.type_id
 names.action_id
 names_surnames.name_id
 names_surnames.surname_id

Minhas junções seriam algo como

select names.col1 
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;

Pergunta

Existe uma maneira de fazer com que o MySQL sempre assuma que as tabelas serão unidas on thistables.thattable_id = thattable.id?