MySQL gebruiken met Basic SQL-opdrachten

Briefing: Leer basis SQL-opdrachten gebruiken in het open source databasebeheersysteem MySQL.

MySQL is de meest gebruikte relationele databasebeheerder . Het gebruiksgemak en de open source-licentie hebben enorm bijgedragen aan deze populariteit.

Vorige week had ik betrekking op het installeren en configureren van MySQL in Ubuntu. In dit artikel zal ik u meerdere MySQL-functies presenteren, waarbij u zich meestal aan de basis blijft houden .

Opmerking: MySQL- query's (opdrachten) zijn hoofdletterongevoelig ; het is echter gebruikelijk om ALLE CAPS te gebruiken voor de daadwerkelijke opdrachtzoekwoorden en kleine letters voor de rest .

Basic Sql-opdrachten

Verbinding maken met en loskoppelen van de MySQL-server

Om query's in te kunnen voeren, moet u eerst verbinding maken met de server met behulp van MySQL en de MySQL-prompt gebruiken. Het commando om dit te doen is:

mysql -h host_name -u user -p 

-h wordt gebruikt om een hostnaam op te geven (als de server zich op een andere computer bevindt, als dit niet het geval is), dan vermeldt -u de gebruiker en geeft -p aan dat u een wachtwoord wilt invoeren .

Hoewel dit niet wordt aanbevolen (om veiligheidsredenen), kunt u het wachtwoord rechtstreeks in het commando invoeren door het in te typen direct achter -p . Als het wachtwoord voor test_gebruiker bijvoorbeeld 1234 is en u probeert verbinding te maken op de computer die u gebruikt, kunt u het volgende gebruiken:

 mysql -u test_user -p1234 

Als u met succes de vereiste parameters hebt ingevoerd, wordt u begroet door de MySQL shell prompt ( mysql> ):

Om de verbinding met de server te verbreken en de mysql-prompt te verlaten, typt u:

 QUIT 

Typ quit (MySQL is niet hoofdlettergevoelig) of \ q werkt ook. Druk op Enter om af te sluiten.

U kunt ook informatie over de versie weergeven met een eenvoudige opdracht:

 sudo mysqladmin -u root version -p 

Opmerking: zorg ervoor dat u verbonden bent met de server voordat u een van de vragen invoert die ik zal behandelen.

Als u een lijst met opties wilt zien, gebruikt u:

 mysql --help 

Query's gebruiken in MySQL

MySQL slaat gegevens op in tabellen en gebruikt opdrachten met de naam queries ( SQL = structured query language). Voordat ik me ga bezighouden met het opslaan, openen en wijzigen van gegevens, ga ik in op basisvragen, zodat u het onder de knie krijgt.

Omdat MySQL tabellen gebruikt, wordt de uitvoer van query 's ook weergegeven in tabellen . Alle SQL-instructies moeten worden gevolgd door een puntkomma ( ; ), maar er zijn uitzonderingen (met name: QUIT). U kunt kolommen scheiden met komma ( , ). Hier zijn enkele basisvoorbeelden:

 mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; mysql> SELECT VERSION(), CURRENT_DATE; 

Als voorbeeld zou de derde query iets vergelijkbaars als dit afdrukken:

 +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 8.0.15 | 2019-04-13 | +-----------+--------------+ 1 row in set (0, 41 sec) 

Omdat puntkomma ( ; ) het einde van een instructie markeert, kunt u ook meerdere instructies op één regel schrijven.

Bijvoorbeeld in plaats van:

 mysql> SELECT VERSION(); mysql> SELECT CURRENT_DATE; 

Je zou ook kunnen schrijven:

 mysql> SELECT VERSION(); SELECT CURRENT_DATE; 

U kunt ook query's met meerdere regels invoeren (als u aan het einde van de regel geen puntkomma invoegt voordat u op Enter drukt). In dat geval zal MySQL eenvoudig een andere prompt invoeren om je opdracht voort te zetten. Bijvoorbeeld:

 mysql> SELECT -> VERSION() ->, -> CURRENT_DATE; 

Zoekopdrachten met meerdere regels zullen ook plaatsvinden als u geen tekenreeks op een regel hebt beëindigd (een woord omringd door ' of ' ).

Als u een query wilt annuleren, typt u \ c en drukt u op Enter .

Er zijn aanwijzingen met verschillende betekenissen:

  • mysql> = klaar voor nieuwe zoekopdracht
  • -> = wachten op volgende regel met meerdere regels
  • '> = wachten op volgende regel, wacht op het voltooien van een reeks die begon met een enkele quote ( ' )
  • "> = Wachten op volgende regel, wachten op voltooiing van een reeks die begon met een dubbel aanhalingsteken ( " )
  • `> = wachten op volgende regel, in afwachting van het invullen van een identifier die begon met een backtick ( ` )
  • / *> = wachten op volgende regel, in afwachting van het voltooien van een opmerking die begon met /*

U kunt ook de huidige tijd ( uu: mm: ss ) naast de huidige datum afdrukken met NOW (), evenals de gebruiker waarmee u verbonden bent met USER () :

 mysql> SELECT NOW(); mysql> SELECT USER(); 

Dit zal iets gelijkaardigs als dit uitvoeren:

 +---------------------+ | NOW() | +---------------------+ | 2019-04-13 23:53:48 | +---------------------+ 1 row in set (0, 00 sec) +----------------+ | USER() | +----------------+ | [email protected] | +----------------+ 1 row in set (0, 00 sec) 

Met MySQL kun je ook wiskundige berekeningen maken :

 mysql> SELECT COS(PI()/3), (10-2+4)/3; 

uitsturen:

 +--------------------+------------+ | COS(PI()/3) | (10-2+4)/3 | +--------------------+------------+ | 0.5000000000000001 | 4.0000 | +--------------------+------------+ 

Databases in MySQL gebruiken

1. Informatie verkrijgen over databases

Allereerst kunt u beschikbare databases weergeven met:

 mysql> SHOW DATABASES; 

U kunt de geselecteerde database ook bekijken met:

 mysql> SELECT DATABASE(); 

Hiermee wordt NULL uitgevoerd als er geen database is geselecteerd. Hier is een voorbeelduitvoer voor de twee genoemde verklaringen:

 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ +------------+ | DATABASE() | +------------+ | NULL | +------------+ 

2. Databases maken

Dit wordt gedaan door simpelweg een opdracht in te voeren:

 mysql> CREATE DATABASE example_db; 

Opmerking: In Ubuntu 18.04 (of een ander Unix-gebaseerd systeem) zijn database- en tabelnamen hoofdlettergevoelig .

3. Databases selecteren

Om een ​​database te selecteren, moet u vermelden dat u deze wilt gebruiken :

 mysql> USE example_db; 

Als dit lukt, krijgt u de melding:

 Database changed 

Als dit niet lukt, krijgt u een foutmelding dat MySQL de opgegeven database niet kan vinden.

U kunt ook een database selecteren bij het verbinden met de server door de naam van een bestaande database te vermelden aan het einde van de opdracht connect:

 mysql -h host_name -u user_name -p example_table 

Bijvoorbeeld:

 mysql -u root -p example_table 

Tables in SQL gebruiken

1. Informatie verkrijgen over tabellen

Gebruik de volgende tabel om de tabellen in de huidige database weer te geven:

 mysql> SHOW TABLES; 

Opmerking: zorg ervoor dat u een database hebt geselecteerd.

Als de database leeg is (bijv. Een nieuw gecreëerde), zal de uitvoer zijn:

 Empty set (0, 00 sec) 

Na het maken van tabellen zal de uitvoer langs de lijn van:

 +----------------------+ | Tables_in_example_db | +----------------------+ | table_1 | | table_2 | +----------------------+ 1 row in set (0, 00 sec) 

2. Tabellen maken

Om tabellen te maken, moet u de lay-out opgeven: de kolommen en het type gegevens dat ze moeten opslaan.

In mijn voorbeeld zal ik informatie over een groep mensen opslaan: naam, geboortedatum, geslacht, land. Hier is hoe ik die tabel kan maken:

 mysql> CREATE TABLE table_1 (name VARCHAR(30), birth_date DATE, sex CHAR(1), country VARCHAR(40)); 

Opmerking: u kunt de opdracht ook op meerdere regels schrijven .

Je ziet dat ik de tabelnaam ( tabel_1 ) en de naam van de kolommen ( naam, geboortedatum, geslacht, land ) noemde. Na de kolomnamen heb ik het gegevenstype opgegeven dat ze opslaan. VARCHAR (n) zijn reeksen van maximaal n tekens, DATE spreekt voor zich ( CCYY-MM-DD- indeling) en CHAR (1) betekent één teken (in het bijzonder ben ik van plan 'm' en 'f te gebruiken ' voor mannelijk en vrouwelijk ). Andere veel voorkomende typen zijn INT (gehele getallen), BOOL (booleans), TIME (uu: mm: ss). Er zijn veel gegevenstypes beschikbaar om te gebruiken in MySQL (numeriek, string, datum en tijd). U kunt ook complexere gegevenstypen gebruiken, zoals AUTO_INCREMENT .

De tabel verschijnt nu als u TABELLEN TOONT .

Als je wilt, kun je de opmaak van een tabel aanpassen met ALTER TABLE :

 mysql> ALTER TABLE table_1 ADD email VARCHAR(50); mysql> ALTER TABLE table_1 DROP birth_date; 

Deze voorbeelden voegden een kolom toe (eerste voorbeeld) en verwijderden een kolom (tweede voorbeeld). U kunt hier meer informatie over ALTER TABLE bekijken, omdat het ook meer geavanceerde toepassingen heeft.

3. Beschrijven van tabellen

Je kunt de structuur van een tafel op elk moment bekijken met:

 mysql> DESCRIBLE table_name; 

Bijvoorbeeld:

 mysql> DESCRIBE table_1; 

gaat produceren:

 +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | name | varchar(30) | YES | | NULL | | | birth_date | date | YES | | NULL | | | sex | char(1) | YES | | NULL | | | country | varchar(40) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0, 00 sec) 

4. Tafels verwijderen

De verklaring voor het verwijderen van tabellen is:

 DROP TABLE table_name; 

5. Gegevens invoegen in tabellen

Om gegevens in te voegen, moet u de waarden opgeven die in elke kolom moeten worden geïntroduceerd (in dezelfde volgorde als in de tabeldefinitie). Voor lege of onbekende waarden moet u NULL gebruiken . Let op niet-numerieke waarden met aanhalingstekens ( ' ) of dubbele aanhalingstekens ( " ). De waarden moeten worden gescheiden door komma's ( , ).

Hier zijn voorbeelden voor Smith en Emily:

 mysql> INSERT INTO table_1 VALUES ('Smith', '1980-04-24', 'm', 'Argentina'); mysql> INSERT INTO table_1 VALUES ('Emily', '1994-07-19', 'f', NULL); 

6. Tafels leegmaken

Als u een tabel wilt leegmaken (alle vermeldingen verwijderen), gebruikt u:

 DELETE FROM table_name; 

Met WHERE kunt u specifieke rijen verwijderen :

 DELETE FROM table_name WHERE col_name = value 

Ik zal dieper ingaan in de volgende paragrafen.

7. Tabelvermeldingen bijwerken

De syntaxis voor het bijwerken van een item is:

 UPDATE table_name SET col = 'value' WHERE conditions 

Bijvoorbeeld:

 UPDATE table_1 SET country = 'France' WHERE name = 'Emily' 

Als u geen voorwaarden opgeeft, worden alle gegevens gewijzigd.

8. Gegevens uit tabellen ophalen

De MySQL-opdracht die wordt gebruikt voor het extraheren van gegevens uit tabellen is SELECT . De structuur van een dergelijke verklaring is:

 SELECT what FROM where WHERE conditions; 

Ik zal enkele veelgebruikte applicaties bespreken, zodat je kunt begrijpen hoe je exact uit de database kunt halen wat je wilt.

een. Alle gegevens selecteren

Allereerst is het eenvoudigst om alle gegevens uit een tabel weer te geven . Bijvoorbeeld:

 mysql> SELECT * FROM table_1; 

Het jokerteken ( * ) staat voor alles, table_1 is de tabel waarvan ik gebruik. Je kunt zien dat ik het WHERE- onderdeel heb weggelaten; het is optioneel om voorwaarden te hebben voor de geselecteerde gegevens.

b. Selecteer specifieke gegevens

Eerst ga ik over het selecteren van rijen .

Om bepaalde rijen te selecteren, moet u voorwaarden opgeven die de gegevens beperken:

 mysql> SELECT * FROM table_1 WHERE name = 'Smith'; mysql> SELECT * FROM table_1 WHERE sex = 'm'; mysql> SELECT * FROM table_1 WHERE birth_date SELECT * FROM table_1 WHERE sex = 'f' AND birth_date > '1991-1-1'; mysql> SELECT * FROM table_1 WHERE sex = 'm' OR country = 'France'; mysql> SELECT * FROM table_1 WHERE country IS NOT NULL; 

In de eerste twee voorbeelden vergelijk ik eenvoudig strings ( niet hoofdlettergevoelig ). U kunt ook waarden zoals datums en gehele getallen vergelijken met vergelijkingsoperatoren ( >, =, <=, = ). wordt gebruikt voor het aanduiden van ' is niet gelijk '. U kunt meerdere voorwaarden opgeven met logische operatoren ( EN, OF ). AND heeft een hogere prioriteit dan OR . Het is het beste om haakjes te gebruiken bij complexere condities.

IS NOT NULL is een manier om alleen rijen weer te geven die geen waarde hebben voor de opgegeven kolom. U kunt rekenkundige vergelijkingsoperatoren niet gebruiken met NULL, omdat dit een ontbrekende waarde vertegenwoordigt (het resultaat zal ook NULL zijn). Gebruik IS NULL en IS NIET NULL .

Zowel NULL als worden behandeld als FALSE, de rest als TRUE .

Nu zal ik het weergeven van specifieke kolommen behandelen .

Hiervoor moet u de kolommen opgeven die u wilt weergeven, gescheiden door komma's. Bijvoorbeeld:

 mysql> SELECT name, birth_date FROM table_1; 

Je kunt ook herhalende gegevens verwijderen . Als ik bijvoorbeeld alle geboortedata wil hebben (zonder meerdere keren dezelfde waarde te hebben gekregen als er meerdere mensen zijn geboren op die datum), gebruik ik:

 mysql> SELECT DISTINCT birth_date FROM table_1; 

Dit geeft alleen DISTINCT- resultaten weer.

Om nog specifieker te worden, kun je combineren met het weergeven van bepaalde kolommen met voorwaarden ( WHERE ):

 mysql> SELECT name, sex FROM table_1 WHERE country = 'France' AND birth_date < '1991-1-1'; 

c. Gegevens sorteren

Voor het sorteren van gegevens gebruikt u ORDER_BY :

 mysql> SELECT name FROM table_1 ORDER BY birth_date; 

Je kunt zien dat ik dit heb gecombineerd met het selecteren van specifieke gegevens. De bovenstaande opdracht toont de namen van alle vermeldingen, gerangschikt in oplopende volgorde op geboortedatum.

Je kunt ook in aflopende volgorde bestellen:

 mysql> SELECT name FROM table_1 ORDER BY birth_date DESC; 

Sorteren kan op meerdere kolommen worden toegepast. Als u bijvoorbeeld wilt sorteren in aflopende volgorde op geboortedatum en mensen die op dezelfde datum in stijgende volgorde met naam zijn geboren, zou ik het volgende gebruiken:

 mysql> SELECT name FROM table_1 ORDER BY birth_date DESC, name; 

d. Het manipuleren van datums

U kunt de huidige datum ophalen met CURDATE () . Met dit en nog een jaar kunt u een verschil berekenen (bijvoorbeeld om de leeftijd van een persoon te krijgen) met TIMESTAMPDIFF () :

 mysql> SELECT name, birth_date, CURDATE(), -> TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age -> FROM table_1 ORDER BY age; 

Hier is de uitvoer voor een voorbeeld table_1 :

 +--------+------------+------------+------+ | name | birth_date | CURDATE() | age | +--------+------------+------------+------+ | Emily | 1994-07-19 | 2019-04-13 | 24 | | Danny | 1992-08-04 | 2019-04-13 | 26 | | Joanna | 1992-08-04 | 2019-04-13 | 26 | | Joe | 1985-03-11 | 2019-04-13 | 34 | | Smith | 1980-04-24 | 2019-04-13 | 38 | +--------+------------+------------+------+ 

TIMESTAMPDIFF () neemt als argumenten de eenheid die moet worden gebruikt voor het resultaat (YEAR) en twee datums (geboortedatum, CURDATE ()) waarvoor een verschil moet worden berekend. Het sleutelwoord AS ( alias) noemt de resulterende kolom en maakt het gemakkelijk om mee te werken (in dit voorbeeld: sorteren op leeftijd).

Voor het verwijzen naar specifieke delen van datums, kunt u YEAR (), MONTH () en DAYOFMONTH () gebruiken, met de datum als argument . Bijvoorbeeld:

 mysql> SELECT name, birth_date, MONTH(birth_date) FROM table_1; 

Je kunt resultaten vergelijken (maanden, jaren, dagen) net als normale nummers. Om deze echter te vergelijken met dingen zoals de volgende maand, kun je niet gewoon toevoegen aan CURDATE (), omdat dit kan zorgen dat je controleert op maand 13 of andere dergelijke onzin . De tussenoplossing daarvoor is INTERVAL en DATE_ADD () :

 mysql> SELECT name, birth_date FROM table_1 WHERE MONTH(birth_date) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); 

U kunt ook de modulo-operator ( MOD ) gebruiken:

 mysql> SELECT name, birth_date FROM pet WHERE MONTH(birth_date) = MOD(MONTH(CURDATE()), 12) + 1; 

Als ongeldige datums worden gebruikt, wordt NULL geretourneerd en worden WAARSCHUWINGEN weergegeven, te zien met:

 mysql> SHOW WARNINGS; 

e. Patroonovereenkomst gebruiken

In MySQL staat _ voor elk willekeurig teken en % voor 0+ tekens en patronen (standaard) hoofdletterongevoelig . In plaats van = en , om patronen te gebruiken die u LIKE zou moeten gebruiken en NIET ALS :

 mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%a%'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE '%b'; mysql> SELECT birth_date FROM table_1 WHERE name LIKE 'c%'; mysql> SELECT * FROM table_1 WHERE name LIKE '___'; 

Deze voorbeelden selecteren de geboortedatums van de vermeldingen waarbij de naam ' a ' (eerste voorbeeld) bevat, eindigt met ' b ' (tweede voorbeeld) of begint met ' c ' (derde voorbeeld). Het laatste voorbeeld selecteert rijen waar de naam precies drie tekens heeft (drie instanties van ' _ ').

U kunt ook uitgebreide reguliere expressies gebruiken met REGEXP_LIKE () (ook REGEXP- en RLIKE- operators). Reguliere expressies vallen buiten het bestek van deze handleiding, maar u kunt hier meer informatie bekijken.

f. Resultaten tellen

Het tellen van gegevens is belangrijk en heeft veel toepassingen in de echte wereld. MySQL gebruikt COUNT () voor dergelijke taken. Het eenvoudigste voorbeeld is het tellen van de invoer van een tabel :

 mysql> SELECT COUNT(*) FROM table_1; 

U kunt de telling ook splitsen tussen groepen . Ik kan bijvoorbeeld GROUP BY- land gebruiken en weergeven hoeveel vermeldingen er in elk land zijn:

 mysql> SELECT country, COUNT(*) FROM table_1 GROUP BY country; 

Je zou zelfs meer specifieke groepen kunnen noemen door meer kolommen in te voeren om te groeperen . Bijvoorbeeld:

 mysql> SELECT country, sex, COUNT(*) FROM table_1 GROUP BY country, sex; 

Deze resultaten kunnen ook samen met WHERE worden gebruikt om de uitvoer te verkleinen.

Wees voorzichtig bij het tellen. Als u naast COUNT () nog iets anders opgeeft om af te drukken en deze kolommen niet vermeldt na GROUP BY, krijgt u mogelijk een foutmelding of onverwachte resultaten (zie ONLY_FULL_GROUP_BY ).

g. Gebruik van meerdere tabellen

Dit is iets dat je misschien zou willen doen in een iets complexere context.

Stel u bijvoorbeeld eens voor dat er een andere tabel ( tabel_2 ) is waarin de datum ( datum ) staat waarop de mensen ( naam ) vergaderingen hebben bijgewoond ( vergadering ).

U kunt weergeven hoe oud de mensen in deze tabellen waren toen ze vergaderingen bezochten:

 mysql> SELECT table_1.name, TIMESTAMPDIFF(YEAR, birth_date, date) AS age, meeting FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; 

Dit levert iets als:

 +-------+------+-----------------+ | name | age | meeting | +-------+------+-----------------+ | Emily | 21 | Dog Lovers Club | | Emily | 22 | Dog Lovers Club | | Emily | 23 | Hackathon | | Smith | 36 | TED Talk | | Smith | 38 | Footbal Match | +-------+------+-----------------+ 

Ik zal proberen de syntaxis uit te leggen . Voor naam moesten we table_1.name en table_2.name vermelden, omdat de kolom in beide tabellen aanwezig is (voor geboortedatum, datum en vergadering hoefden we de tabel niet op te geven, omdat ze uniek zijn voor een van de tabellen). Dit gebruikt de uitspraken die ik al behandeld heb.

Het interessante deel is dit:

  FROM table_1 INNER JOIN table_2 ON table_1.name = table_2.name; 

INNER JOIN zet de tabellen bij elkaar en neemt de rij die iets gemeen heeft, een voorwaarde gespecificeerd door het trefwoord AAN ; in dit geval, waar de namen overeenkomen.

Opmerking: u kunt dezelfde tabel ook zelf samenstellen, misschien om twee SELECT-resultaten te vergelijken.

De Batch-modus gebruiken in MySQL

Een andere leuke functie is de batchmodus . In plaats van de interactieve shell, kunt u de instructies in een bestand plaatsen en uitvoeren :

 mysql -h host_name -u user_name -p < batch_file 

U wordt vervolgens gevraagd om een ​​wachtwoord in te voeren (indien vereist voor de gebruiker). Als u wilt blijven werken in plaats van te stoppen bij fouten, gebruikt u -force .

U kunt de uitvoer zelfs omleiden naar een ander programma of naar een bestand:

 mysql -h host_name -u user_name -p < batch_file | less mysql -h host_name -u user_name -p output_file 

U kunt ook interactieve uitvoer krijgen met -t of echo uitvoer met -v parameters.

Als u zich in de interactieve MySQL-shell bevindt en een script wilt uitvoeren, gebruikt u een van deze twee:

 mysql> source batch_file; mysql> \. batch_file; 

Afsluiten

In dit artikel heb ik verschillende manieren behandeld waarop u MySQL kunt gebruiken voor het bewerken van databasegegevensbanken, en zelfs voor meer geavanceerde functies.

Ik zou blij zijn als deze handleiding worstelende gebruikers en beginners helpt. Zelfs als je geen beginner bent, hoop ik dat je iets leest waarvan je niets wist. Als het u heeft geholpen, laat het ons dan weten in de comments!

Aanbevolen

UbuntuBSD brengt Ubuntu en FreeBSD samen
2019
Hoe Fedora Live USB te creëren in Ubuntu
2019
Droevig nieuws! Scientific Linux is Discontinued
2019