Briefing: Leer basis SQL-opdrachten gebruiken in het open source databasebeheersysteem MySQL.
MySQL wordt het meest gebruikt relationele databasebeheerder. Het gebruiksgemak en de open source-licentie hebben enorm bijgedragen aan deze populariteit.
Vorige week heb ik gedekt installeren en configureren van MySQL in Ubuntu. In dit artikel zal ik u meerdere presenteren MySQL-functies, meestal vasthouden aan de basics.
Opmerking: MySQL queries (opdrachten) zijn hoofdlettergevoelig; het is echter gebruikelijk om te gebruiken IN HOOFDLETTERS voor het eigenlijke commando trefwoorden en kleine letters voor de rest.
Verbinding maken met en de verbinding verbreken met de MySQL-server
Om te kunnen invoeren queries, u zult eerst verbinding moeten 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 te specificeren hostnaam (als de server zich op een andere machine bevindt, als dit niet het geval is, laat het dan weg), -u vermeldt de gebruiker en -p geeft aan dat u a wilt invoeren wachtwoord.
Hoewel dit niet wordt aanbevolen (om veiligheidsredenen), kunt u het wachtwoord rechtstreeks in het commando invoeren door het direct erna in te typen -p. Bijvoorbeeld, als het wachtwoord voor test_user is 1234 en u probeert verbinding te maken op de machine die u gebruikt, kunt u gebruiken:
mysql -u test_user -p1234Als u de vereiste parameters hebt ingevoerd, wordt u begroet door de MySQL shell prompt (mysql>):
Naar loskoppelen van de server en verlaten de mysql-prompt, typ:
QUITTypen ophouden (MySQL is niet hoofdlettergevoelig) of q zal ook werken. druk op Enter verlaten.
U kunt ook informatie over de versiemet een eenvoudige opdracht:
sudo mysqladmin -u root version -pOpmerking: Zorg ervoor dat u verbonden bent met de server voordat u een van de vragen invoert die ik zal behandelen.
Als je een wilt zien lijst met optiesGebruik:
mysql --helpQuery's gebruiken in MySQL
MySQL slaat gegevens op in tafels en gebruikt gebelde commando's queries (SQL = gestructureerde zoektaal). Voordat ik me ga bezighouden met het opslaan, openen en wijzigen van gegevens, zal ik dekking zoeken basisvragen dus je snapt het.
Aangezien MySQL tabellen gebruikt, is de uitgang of queries wordt ook weergegeven in tafels. Alle SQL-instructies moeten worden gevolgd door een puntkomma (;), hoewel er uitzonderingen zijn (met name: STOPPEN). Je kunt scheiden kolommen die al met Countr werken 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)Sinds puntkomma (;) markeert het einde van een instructie, u kunt 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 invoeren vragen met meerdere regels (als u geen a puntkomma aan het einde van de regel voordat u op drukt Enter). 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 (een woord omgeven door ' or ").
Als u wilt annuleren een vraag, typ in c en druk op Enter.
Er zijn prompts met verschillende betekenissen:
- mysql> = klaar voor nieuwe vraag
- -> = wachten op volgende regel met meerdere regels
- '> = wachten op de volgende regel, wachtend op het voltooien van een reeks die begon met een enkel aanhalingsteken (
') - “> = wachten op de volgende regel, wachtend op het voltooien 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 de volgende regel, wachtend op het voltooien van een opmerking die begon met
/*
U kunt het ook afdrukken huidige tijd (hh: mm: ss) naast de huidige datum met NU()Alsmede de gebruiker je bent verbonden als gebruiken GEBRUIKER():
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 berekenen wiskundige berekeningen ook:
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 kan dat lijst met beschikbare databases met:
mysql> SHOW DATABASES;U kunt ook de geselecteerde database met:
mysql> SELECT DATABASE();Dit wordt uitgevoerd NULL als 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 je vermelden dat je dat wilt gebruiken het:
mysql> USE example_db;Als dit lukt, krijgt u de melding:
Database changedAls dit niet lukt, krijg je een fout u vertellen 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_tableBijvoorbeeld:
mysql -u root -p example_tableTables in SQL gebruiken
1. Informatie over tabellen verkrijgen
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.
U ziet dat ik de tabelnaam heb genoemd (table_1) en de naam van de kolommen (naam, geboortedatum, geslacht, Land). Na de kolomnamen heb ik het data type ze slaan op. VARCHAR (n) zijn reeksen van maximaal n Tekens in lengte, DATUM spreekt voor zich (EEJJ-MM-DD formaat) en CHAR (1) betekent een enkel teken (specifiek, ik ben van plan om te gebruiken 'M' en 'F' heeft gewacht mannetje en vrouw). Andere veel voorkomende typen zijn onder meer INT (Gehele getallen), BOOL (Booleans) TIJD (uu: mm: ss), er zijn er veel Datatypen beschikbaar om te gebruiken in MySQL (numerieke, snaar, datum en tijd). U kunt ook meer complexe gegevenstypen gebruiken, zoals AUTO_INCREMENT.
De tabel verschijnt nu als u TOON TABELLEN.
Als je het wenst, kan je het wijzig de lay-out van een tafel met ALTER TABLE:
mysql> ALTER TABLE table_1 ADD email VARCHAR(50);
mysql> ALTER TABLE table_1 DROP birth_date;Deze voorbeelden een kolom toegevoegd (eerste voorbeeld) en heeft een kolom verwijderd (tweede voorbeeld). U kunt meer informatie over ALTER TABLE hier, omdat het ook meer geavanceerde toepassingen heeft.
3. Tafels beschrijven
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 gebruiken NULL. Zorg ervoor dat niet-numerieke waarden met citaten (') 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 wilt leeg een tabel (verwijder alle vermeldingen), gebruik:
DELETE FROM table_name;gebruik WAAR, je kan zijn verwijder specifieke rijen:
DELETE FROM table_name WHERE col_name = valueIk zal dieper ingaan in de volgende paragrafen.
7. Tabelvermeldingen bijwerken
De syntaxis voor bijwerken een invoer is:
UPDATE table_name SET col = 'value' WHERE conditionsBijvoorbeeld:
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 uitpakken van gegevens 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 weer te geven alle gegevens een tafel. Bijvoorbeeld:
mysql> SELECT * FROM table_1;Het wildcard (*) staat voor alles, table_1 is de tafel waar ik aan het uitpakken ben. Je kunt zien dat ik de WAAR een deel; het is optioneel om voorwaarden te hebben voor de geselecteerde gegevens.
b. Selecteer specifieke gegevens
Eerst ga ik over rijen selecteren.
Om bepaalde rijen te selecteren, moet u opgeven voorwaarden 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 < '1990-1-1';
mysql> 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, gewoon vergelijk snaren (hoofdlettergevoelig). Je kan ook vergelijken waarden zoals dadels en integers die al met Countr werken vergelijkingsoperatoren (>, <,> =, <=, =). <> wordt gebruikt voor het aangeven van 'is niet gelijk aan'. U kunt opgeven meerdere voorwaarden gebruik logische operatoren (EN, OR). EN heeft een hogere prioriteit dan OR. Het is het beste om haakjes te gebruiken bij complexere condities.
IS NIET NUL is een manier om alleen rijen weer te geven die geen waarde hebben voor de opgegeven kolom. Je kunt niet gebruiken bewerkers voor rekenkundige vergelijking die al met Countr werken NULL, omdat het een ontbrekende waarde vertegenwoordigt (het resultaat zal gewoon zijn NULL te). Je zou ... moeten gebruiken IS NIETS en IS NIET NUL.
Te gebruiken zowel NULL en worden behandeld als Juist, de rest als TRUE.
Nu zal ik het weergeven behandelen specifieke kolommen.
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 van de hand doen gegevens herhalen. 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 wordt alleen weergegeven DISTINCT resultaten.
Om nog meer te krijgen specifiek, u kunt het weergeven van bepaalde kolommen combineren met voorwaarden (WAAR):
mysql> SELECT name, sex FROM table_1 WHERE country = 'France' AND birth_date < '1991-1-1';c. Gegevens sorteren
Om gegevens te sorteren, 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 ingevoerde, geordende in oplopende bestellen op geboortedatum.
U kunt ook bestellen aflopend bestellen:
mysql> SELECT name FROM table_1 ORDER BY birth_date DESC;sorteer- kan worden toegepast op meerdere kolommen. 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 gebruik CURDATE (). Met dit en nog een jaar kun je een verschil berekenen (bijvoorbeeld om het leeftijd van een persoon) 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 voorbeeldtable_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 om te gebruiken voor het resultaat (YEAR) en twee datums (geboortedatum, CURDATE ()) waarvoor een verschil moet worden berekend. De AS (alias) sleutelwoord benoemt de resulterende kolom en maakt het gemakkelijk om mee te werken (in dit voorbeeld: sorteren op leeftijd).
Voorgestelde lees Graviton: A Minimalist Open Source Code Editor
Voor verwijzen naar specifieke delen van datums, Kunt u gebruik maken JAAR(), MAAND() en DAG VAN DE MAAND(), Met de gegevens as argument. Bijvoorbeeld:
mysql> SELECT name, birth_date, MONTH(birth_date) FROM table_1;Je kunt vergelijken resultaten (maanden, jaren, dagen), net als normale nummers. Echter, om vergelijken deze met dingen zoals de volgende maand, je kunt niet gewoon toevoegen CURDATE (), omdat dit ertoe kan leiden dat u controleert op maand 13 of een andere dergelijke onzin. De oplossing 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));Je zou ook de modulo-operator (MOD):
mysql> SELECT name, birth_date
FROM pet
WHERE MONTH(birth_date) = MOD(MONTH(CURDATE()), 12) + 1;gebruik ongeldige datums zal terugkeren NULL en produceren WAARSCHUWINGEN, gezien met:
mysql> SHOW WARNINGS;e. Patroonovereenkomst gebruiken
In MySQL, _ staat voor een enkel personage en % voor 0 + tekens en patronen zijn (standaard) hoofdlettergevoelig. In plaats van = en <>, om patronen te gebruiken die u zou moeten gebruiken LIKE en NIET ZOALS:
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 waarin de naam voorkomt 'a'(eerste voorbeeld), eindigt met'b'(tweede voorbeeld) of begint met'c'(derde voorbeeld). Het laatste voorbeeld selecteert rijen waar de naam precies drie tekens heeft (drie exemplaren van '_).
U kunt ook gebruik maken van uitgebreide reguliere expressiesmet REGEXP_LIKE () (Ook REGEXP en RLIKE operators). Reguliere expressies vallen buiten het bestek van deze handleiding, maar u kunt meer informatie bekijken hier.
f. Resultaten tellen
Gegevens tellen 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 tafel:
mysql> SELECT COUNT(*) FROM table_1;Je kunt ook de telling splitsen groepen. Ik zou dat bijvoorbeeld kunnen GROEP DOOR land en laat zien hoeveel items er in elk land zijn:
mysql> SELECT country, COUNT(*) FROM table_1 GROUP BY country;Je zou zelfs meer specifieke groepen kunnen noemen door in te voeren meer kolommen om te groeperen. Bijvoorbeeld:
mysql> SELECT country, sex, COUNT(*) FROM table_1 GROUP BY country, sex;Deze resultaten kunnen ook samen met worden gebruikt WAAR om de output te verkleinen.
Wees voorzichtig bij het tellen. Als u iets anders opgeeft om te printen naast COUNT () en noem die kolommen niet later GROEP DOOR, je krijgt misschien een fout or 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 je bijvoorbeeld eens voor dat er een andere tafel is (table_2) die de datum opslaat (gegevens) bij heksenmensen (naam) woonden vergaderingen bij (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 het te verklaren syntaxis. Voor naam, we moesten noemen table_1.name en table_2.name, omdat de kolom in beide tabellen aanwezig is (voor geboortedatum, gegevens en vergadering we hoefden de tabel niet op te geven, omdat ze uniek zijn voor een van hen). 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 samen en neemt de rij die iets gemeen heeft, een voorwaarde gespecificeerd door het sleutelwoord ON; 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 batch mode. In plaats van de interactieve schaal, je kunt de uitspraken in een filet en uitvoeren hen:
mysql -h host_name -u user_name -p < batch_fileU 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 -dwingen.
Je kan zelfs omleiden van de output 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 < batch_file > output_fileU kunt ook interactieve output die al met Countr werken -t or echo uitvoer die al met Countr werken -v parameters.
Als je in de interactieve MySQL-shell en een script wilt uitvoeren, gebruik een van deze twee:
mysql> source batch_file;
mysql> . batch_file;Afsluiten
In dit artikel heb ik verschillende manieren behandeld die u kunt gebruiken MySQL naar omgaan met tabulaire databases, zelfs in meer graven vergevorderd kenmerken.
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!