Aprenda MySQL / Mariadb para iniciantes - Parte 1

Neste artigo, mostraremos como criar um banco de dados (também conhecido como esquema), tabelas (com tipos de dados) e explicar como executar Linguagem de manipulação de dados (DML) operações com dados em um Mysql / Mariadb servidor.

Supõe -se que você tenha anteriormente 1) instalou os pacotes necessários em seu sistema Linux e 2) executado mysql_secure_installation Para melhorar a segurança do servidor de banco de dados. Caso contrário, siga os guias abaixo para instalar o servidor MySQL/Mariadb.

1. Instale o último banco de dados MySQL em sistemas Linux
2. Instale o último banco de dados MariaDB em sistemas Linux

Por brevidade, vamos nos referir a Mariadb exclusivamente ao longo deste artigo, mas os conceitos e comandos descritos aqui se aplicam a Mysql também.

Parte 1: Aprenda mysql / mariadb para iniciantes Parte 2: Aprenda a usar várias funções do MySQL e MariaDB

Criação de bancos de dados, tabelas e usuários autorizados

Como você sabe, um banco de dados pode ser definido em termos simples como uma coleção organizada de informações. Particularmente, Mariadb é um sistema de gerenciamento de banco de dados relacional (Rdbms) e usa a linguagem de consulta de estrutura para executar operações em bancos de dados. Além disso, lembre.

Para armazenar informações persistentes em um banco de dados, usaremos mesas que as linhas de dados de lojas. Freqüentemente, duas ou mais tabelas estarão relacionadas uma à outra de alguma forma. Isso faz parte da organização que caracteriza o uso de bancos de dados relacionais.

Criando um novo banco de dados

Para criar um novo banco de dados nomeado BooksDB, Digite o prompt de mariadb com o seguinte comando (você será solicitado a inserir a senha do usuário do ROOT MARIADB):

[[Email protegido] ~]# Mysql -u Root -p Digite a senha: Bem -vindo ao monitor mariadb. Os comandos terminam com; ou \ g. Seu ID de conexão MariaDB é 2 servidor versão: 10.1.14-Mariadb Mariadb Server Copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB e outros. Digite 'ajuda;' ou '\ h' para obter ajuda. Digite '\ c' para limpar a instrução de entrada atual. Mariadb [(nenhum)]> Criar banco de dados BookStoredB; Consulta ok, 1 linha afetada (0.00 seg) mariadb [(nenhum)]> 

Depois que o banco de dados for criado, precisamos criar pelo menos duas tabelas. Mas primeiro vamos explorar o conceito de tipos de dados.

Apresentando tipos de dados mariadb

Como explicamos anteriormente, mesas são objetos de banco de dados onde manteremos informações persistentes. Cada tabela consiste em dois ou mais campos (também conhecidos como colunas) de um determinado tipo de dados (o tipo de informação) que esse campo pode armazenar.

Os tipos de dados mais comuns no mariadb são os seguintes (você pode consultar a lista completa na documentação oficial do Mariadb):

Numérico:

1. BOLEANO considera 0 como falso e quaisquer outros valores como verdadeiros.
2. Tinyint, Se usado com assinado, cobre o intervalo de -128 a 127, enquanto o intervalo não assinado é de 0 a 255.
3. Smallint, Se usado com assinado, cobre o intervalo de -32768 a 32767. O intervalo não assinado é de 0 a 65535.
4. Int, Se usado com não assinado, abrange o intervalo de 0 a 4294967295 e -2147483648 a 2147483647, caso contrário.

Observação: Em tinyint, smallint e int, o padrão assinado é assumido.

Duplo (m, d), onde M é o número total de dígitos e D é o número de dígitos após o ponto decimal, representa um número de ponto flutuante de precisão dupla. Se não assinado for especificado, os valores negativos não serão permitidos.

Corda:

1. Varchar (M) representa uma sequência de comprimento variável onde M é o comprimento máximo permitido da coluna em bytes (65.535 em teoria). Na maioria dos casos, o número de bytes é idêntico ao número de caracteres, exceto por alguns caracteres que podem levar até 3 bytes. Por exemplo, a carta espanhola ñ representa um personagem, mas ocupa 2 bytes.
2. Texto (m) representa uma coluna com um comprimento máximo de 65.535 caracteres. No entanto, como acontece com Varchar (M), O comprimento máximo real é reduzido se os caracteres de vários bytes forem armazenados. Se M é especificado, a coluna é criada como o menor tipo que pode armazenar esse número de caracteres.
3. MediumText (M) e LongText (M) são semelhantes a Texto (m), Somente que os comprimentos máximos permitidos são 16.777.215 e 4.294.967.295 caracteres, respectivamente.

Data e hora:

1. DATA representa a data em AAAA-MM-DD formatar.
2. TEMPO representa o tempo em HH: MM: SS.SSS formato (hora, minutos, segundos e milissegundos).
3. DATA HORA é a combinação de DATA e TEMPO em AAA AYYY-MM-DD HH: MM: SS formatar.
4. Timestamp é usado para definir o momento em que uma linha foi adicionada ou atualizada.

Depois de revisar esses tipos de dados, você estará em uma posição melhor para determinar qual tipo de dados você precisa atribuir a uma determinada coluna em uma tabela.

Por exemplo, o nome de uma pessoa pode facilmente se encaixar em um Varchar (50), enquanto uma postagem no blog precisará de um TEXTO Tipo (escolha M conforme suas necessidades específicas).

Criando mesas com chaves primárias e estrangeiras

Antes de mergulharmos na criação de tabelas, existem dois conceitos fundamentais sobre bancos de dados relacionais que precisamos revisar: Primário e estrangeiro chaves.

A chave primária contém um valor que identifica exclusivamente cada linha ou gravação na tabela. Por outro lado, um Chave estrangeira é usado para criar um link entre os dados em duas tabelas e para controlar os dados que podem ser armazenados na tabela onde a chave estrangeira está localizada. As chaves primárias e estrangeiras são geralmente ints.

Para ilustrar, vamos usar o BookStoredB e crie duas tabelas nomeadas Authorstbl e Bookstbl do seguinte modo. O NÃO NULO restrição indica que o campo associado requer um valor diferente NULO.

Também, INCREMENTO AUTOMÁTICO é usado para aumentar em um valor de Int colunas de chave primária quando um novo registro é inserido na tabela.

Mariadb [(nenhum)]> Use BookStoredB; Mariadb [(nenhum)]> Criar tabela AuthorStbl (Autorid int não nulo Auto_incrent, Authorname Varchar (100), Chave Primária (AutorId)); Mariadb [(nenhum)]> Criar tabela Bookstbl (bookId int não nulo Auto_increment, BookName Varchar (100) Não NULL, AuthorId int não nulo, Decimal de BookPrice (6,2) NÃO NULL, LOUTLASTUPDATED Timestamp, Boolean Boolean, chave primária (Chave de BookId), Key Foreign (autorid) Referências Authorstbl (AutorI); 

Crie tabelas MySQL com chave primária e estrangeira

Mariadb [(nenhum)]> Use BookStoredB; O banco de dados mudou MariaDB [BookStoredB]> Criar tabela AuthorStbl ( -> Autorid int não null Auto_increment, -> Authorname Varchar (100), -> Chave primária (AutorID) ->); Consulta ok, 0 linhas afetadas (0.05 Sec) Mariadb [BookStoredB]> Crie tabela bookstbl ( -> bookId em não nulo auto_increment, -> bookname varchar (100) não nulo, -> autorid não null, -> bookprice decimal (6,2) não nulo,> timestamp de bookislastupded,> boolean bookisavilable boolean , -> chave primária (bookId), -> Chave estrangeira (autorId) referências Authorstbl (autorId) ->); Consulta ok, 0 linhas afetadas (0.05 Sec) Mariadb [BookStoredB]> 

Agora podemos seguir em frente e começar a inserir registros em Authorstbl e Bookstbl.

Selecionando, inserindo, atualizando e excluindo linhas

Vamos primeiro povoar o Authorstbl mesa. Por que? Porque precisamos ter valores para AutorI antes de inserir registros no Bookstbl.

Execute a seguinte consulta do seu prompt de mariadb:

Mariadb [BookSoredB]> Inserir em Authorstbl (Authorname) valores ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho'); 

Depois disso, selecionaremos todos os registros de Authorstbl. Lembre -se de que precisaremos do AutorI para cada registro para criar o INSERIR consulta para Bookstbl.

Se você deseja recuperar um registro de cada vez, pode usar um ONDE Cláusula para indicar uma condição que uma linha deve encontrar para ser devolvida. Por exemplo,

Mariadb [BookStoredB]> Selecione * de AuthorStbl Where Authorname = "Agatha Christie"; 

Como alternativa, você pode selecionar todos os registros simultaneamente:

Mariadb [BookStoredB]> Selecione * do autorstbl; 

Selecione e consulte o registro em mysql

Mariadb [BookStoredB]> Selecione * de AuthorStbl Where Authorname = "Agatha Christie"; +----------+-----------------+ | AutorI | Authorname | +----------+-----------------+| 1 | Agatha Christie | +----------+-----------------+1 linha no conjunto (0.00 seg) mariadb [BookStoredB]> Selecione * do autorstbl; +----------+-----------------+ | AutorI | Authorname | +----------+-----------------+| 1 | Agatha Christie | | 2 | Stephen King | | 3 | Paulo Coelho | +----------+-----------------+3 linhas em set (0.00 seg) mariadb [BookStoredB]> 

Selecione e consulte uma string no banco de dados MySQL

Agora vamos criar o INSERIR consulta para Bookstbl, usando o correspondente AutorI Para combinar com o autor de cada livro. Um valor de 1 em Bookisavilable indica que o livro está em estoque, 0 de outra forma:

Mariadb [BookStoredB]> Inserir no Bookstbl (nome do bookname, AuthorId, BookPrice, Bookisavilable) ('e depois não havia nenhum', 1, 14.95, 1), ('The Man in the Brown Suit', 1, 23.99, 1), ('The Stand', 2, 35.99, 1), ('Pet Sematary', 2, 17.95, 0), ('The Green Mile', 2, 29.99, 1), ('The Alchemist', 3, 25, 1), ('pelo rio Piedra que eu me sentei e chorei', 3, 18.95, 0); 

Insira a consulta na tabela MySQL

Mariadb [BookStoredB]> Inserir em Bookstbl (Nome do BookName, AuthorId, BookPrice, Bookisavilable) -> valores ('e depois não havia nenhum', 1, 14.95, 1), -> ('The Man in the Brown Suit', 1, 23.99, 1), -> ('The Stand', 2, 35.99, 1), -> ('Pet Sematary', 2, 17.95, 0), -> ('The Green Mile', 2, 29.99, 1), -> ('The Alchemist', 3, 25, 1), -> ('pelo rio Piedra que eu me sentei e chorei', 3, 18.95, 0); Consulta ok, 7 linhas afetadas (0.03 Sec) Registros: 7 Duplicatas: 0 AVISOS: 0 

Neste ponto, faremos um Selecione Para ver os registros em Bookstbl. Então vamos ATUALIZAR o preço de "O Alquimista" por Paulo Coelho e Selecione Esse registro específico novamente.

Observe como o BookLastUpdated O campo agora mostra um valor diferente. Como explicamos anteriormente, um Timestamp O campo mostra o valor quando o registro foi inserido ou o último modificado.

Mariadb [BookStoredB]> Selecione * no bookstbl; Mariadb [BookStoredB]> Atualizar Bookstbl Set BookPrice = 22.75 onde bookId = 6; Mariadb [BookStoredB]> Selecione * no bookstbl onde bookId = 6; 

Insira a tabela de consulta e atualização no banco de dados MySQL

Mariadb [BookStoredB]> Selecione * no bookstbl; +--------+-----------------------------------------+----------+-----------+---------------------+-----------------+ | BookId | Nome do livro | AutorI | BookPrice | BookLastUpdated | Bookisavilable | +--------+---------------------------------------- -+----------+-----------+-------------------+--- --------------+ | 1 | E depois não havia nenhum | 1 | 14.95 | 2016-10-01 23:31:41 | 1 | | 2 | O homem de terno marrom | 1 | 23.99 | 2016-10-01 23:31:41 | 1 | | 3 | O suporte | 2 | 35.99 | 2016-10-01 23:31:41 | 1 | | 4 | PET SEMATARY | 2 | 17.95 | 2016-10-01 23:31:41 | 0 | | 5 | A milha verde | 2 | 29.99 | 2016-10-01 23:31:41 | 1 | | 6 | O alquimista | 3 | 25.00 | 2016-10-01 23:31:41 | 1 | | 7 | Pelo rio Piedra, sentei -me e chorei | 3 | 18.95 | 2016-10-01 23:31:41 | 0 | +--------+---------------------------------------- -+----------+-----------+-------------------+--- --------------+ 7 linhas no conjunto (0.00 seg) mariadb [BookStoredB]> Atualizar Bookstbl Set BookPrice = 22.75 onde bookId = 6; Consulta ok, 1 linha afetada (0.04 Sec) linhas correspondentes: 1 Alteração: 1 AVISOS: 0 MARIADB [BookStoredB]> Selecione * no bookstbl onde bookId = 6; +--------+---------------+----------+-----------+---------------------+-----------------+ | BookId | Nome do livro | AutorI | BookPrice | BookLastUpdated | Bookisavilable | +--------+---------------+----------+-----------+- ----------------------+ -----------------+ | 6 | O alquimista | 3 | 22.75 | 2016-10-01 23:35:00 | 1 | +--------+---------------+----------+-----------+- ----------------------+ -----------------+ 1 linha no conjunto (0.00 seg) mariadb [BookStoredB]> 

Insira e atualize o registro da tabela de banco de dados

Embora não o façamos aqui, você também pode excluir um registro se não for usado mais. Por exemplo, suponha que queremos excluir “O Alquimista" de Bookstbl.

Para fazer isso, usaremos o EXCLUIR Declaração da seguinte forma:

Mariadb [BookStoredB]> Exclua do bookstbl onde bookId = 6; 

Como no caso de ATUALIZAR, É uma boa ideia fazer um Selecione primeiro para ver os registros que podem ser potencialmente impactados pelo EXCLUIR.

Além disso, não se esqueça de adicionar o ONDE cláusula e uma condição (BookId = 6) Para selecionar o registro específico a ser removido. Caso contrário, você corre o risco de excluir todas as linhas da tabela!

Se você deseja concatenar dois (ou mais) campos, você pode usar o Concat declaração. Por exemplo, digamos que queremos retornar um conjunto de resultados que consiste em um campo com o nome do livro e o autor na forma de “O alquimista (Paulo Coelho)”E outra coluna com o preço.

Isso exigirá um JUNTAR entre Authorstbl e Bookstbl no campo comum compartilhado por ambas as tabelas (AutorI):

Mariadb [BookStoredB]> Selecione Concat (Bookstbl.Bookname, '(', Authorstbl.Authorname, ')') como descrição, Bookstbl.BookPrice de Authorstbl Junt -se Bookstbl no Authorstbl.AutorID = Bookstbl.AutorID; 

Como podemos ver, Concat nos permite ingressar em várias expressões de string separadas por vírgulas. Você também percebeu que escolhemos o pseudônimo Descrição para representar o conjunto de resultados da concatenação.

A saída da consulta acima é mostrada na imagem abaixo:

Consulta vários campos na tabela MySQL

Mariadb [BookStoredB]> Selecione Concat (Bookstbl.Bookname, '(', Authorstbl.Authorname, ')') como descrição, Bookstbl.BookPrice de Authorstbl Junt -se Bookstbl no Authorstbl.AutorID = Bookstbl.AutorID; +--------------------------------------------------------+-----------+ | Descrição | BookPrice | +--------------------------------------------------- -------+ -----------+ | E depois não havia nenhum (Agatha Christie) | 14.95 | | O homem de terno marrom (Agatha Christie) | 23.99 | | The Stand (Stephen King) | 35.99 | | PET SEMATARY (Stephen King) | 17.95 | | A milha verde (Stephen King) | 29.99 | | O Alquimista (Paulo Coelho) | 25.00 | | Pelo rio Piedra, sentei -me e chorei (Paulo Coelho) | 18.95 | +--------------------------------------------------- -------+ -----------+ 7 linhas no conjunto (0.00 seg) 

Consulta vários campos na tabela de banco de dados

Crie usuário para acessar o banco de dados BookSoredB

Usando raiz para executar tudo DML Operações em um banco de dados é uma má ideia. Para evitar isso, podemos criar um novo Mariadb Conta de usuário (vamos citar isso BookstoreUser) e atribuir todas as permissões necessárias para BookStoredB:

Mariadb [BookStoredB]> Criar usuário [email protegido] identificado por 'yourpasswordhere'; Mariadb [BookStoredB]> Conceda todos os privilégios no BookSoredB.* para [email protegido]; Mariadb [BookStoredB]> Privilégios descarregados; 

Crie novo usuário de banco de dados com privilégios

Mariadb [BookStoredB]> Criar usuário [email protegido] identificado por 'Tecmint'; Consulta ok, 0 linhas afetadas (0.00 seg) mariadb [BookStoredB]> Conceda todos os privilégios no BookSoredB.* para [email protegido]; Consulta ok, 0 linhas afetadas (0.00 seg) mariadb [BookStoredB]> Privilégios descarregados; Consulta ok, 0 linhas afetadas (0.00 seg) 

Ter um usuário dedicado e separado para cada banco de dados impedirá os danos a todo o banco de dados, caso uma única conta seja comprometida.

Dicas extras do MySQL

Para esclarecer o Mariadb Prompt, digite o seguinte comando e pressione Digitar:

Mariadb [BookStoredB]> \! claro 

Para inspecionar a configuração de uma determinada tabela, faça:

Mariadb [BookStoredB]> Selecione colunas em [Nome da tabela aqui]; 

Por exemplo,

Mariadb [BookStoredB]> Mostrar colunas em Bookstbl; 

Liste colunas na tabela de banco de dados

Mariadb [BookStoredB]> Mostrar colunas em Bookstbl; +-----------------+--------------+------+-----+-------------------+-----------------------------+ | Campo | Tipo | Nulo | Chave | Padrão | Extra | +-----------------+------------+------+-----+--- ------------------+ ---------------------------+ | BookId | int (11) | Não | PRI | Nulo | Auto_increment | | Nome do livro | Varchar (100) | Não | | Nulo | | | AutorI | int (11) | Não | Mul | Nulo | | | BookPrice | Decimal (6,2) | Não | | Nulo | | | BookLastUpdated | Timestamp | Não | | Current_timestamp | Na atualização current_timestamp | | Bookisavilable | Tinyint (1) | Sim | | Nulo | | +-----------------+------------+------+-----+--- ------------------+ ---------------------------+ 6 linhas em conjunto (0.02 Sec) 

Liste colunas na tabela de banco de dados

Uma rápida inspeção revela que o Bookisavilable campo admite NULO valores. Como não queremos permitir isso, nós vamos ALTERAR A tabela da seguinte forma:

Mariadb [BookStoredB]> Alterar a tabela Bookstbl Modificar bookisavilable boolean não nulo; 

(Sinta -se à vontade para mostrar as colunas novamente - o destacado SIM Na imagem acima, agora deve ser um NÃO).

Por fim, para visualizar todos os bancos de dados do seu servidor, faça:

Mariadb [BookStoredB]> Mostrar bancos de dados; OU Mariadb [BookStoredB]> Mostrar esquemas; 

Liste todos os bancos de dados MySQL

[[Email protegido] ~]# Mysql -u BookstoreUser -P Digite a senha: Bem -vindo ao monitor mariadb. Os comandos terminam com; ou \ g. Seu ID de conexão MariaDB é 3 versão do servidor: 10.1.14-Mariadb Mariadb Server Copyright (c) 2000, 2016, Oracle, Mariadb Corporation AB e outros. Digite 'ajuda;' ou '\ h' para obter ajuda. Digite '\ c' para limpar a instrução de entrada atual. Mariadb [BookStoredB]> Mostrar bancos de dados; +--------------------+ | Banco de dados | + ----------------------+ | BookStoredB | | Information_schema | + ----------------------+ 2 linhas no conjunto (0.00 seg) mariadb [BookStoredB]> Mostrar esquemas; +--------------------+ | Banco de dados | + ----------------------+ | BookStoredB | | Information_schema | + ----------------------+ 2 linhas no conjunto (0.00 seg) 

A imagem a seguir mostra o resultado do comando acima depois de acessar o prompt de mariadb como o BookstoreUser (Observe como esta conta não pode "ver" quaisquer bancos de dados que não sejam BookStoredB e Information_schema (Disponível para todos os usuários):

Liste todos os bancos de dados MySQL

Resumo

Neste artigo, explicamos como correr DML Operações e como criar um banco de dados, tabelas e usuários dedicados em um banco de dados MariaDB. Além disso, compartilhamos algumas dicas que podem facilitar sua vida como um administrador de sistema / banco de dados.

1. MySQL Database Administration Part - 1
2. MySQL Database Administration Part - 2
3. Tunning e otimização de desempenho do MySQL - Parte 3

Se você tiver alguma dúvida sobre este artigo, não hesite em nos informar! Sinta -se à vontade para usar o formulário de comentário abaixo para nos alcançar.