Vytvoření databáze
mysql> CREATE DATABASE sampdb;
ERROR 1044 (42000): Access denied for user ‚tsolar’@’localhost‘ to database ‚sampdb‘
To byl ten krok v první části. Pod rootem musíte přiřadit uživateli tsolar dostatečné oprávnění.
[root@testsrv01 sampdb]# mysql -p -u root
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‚help;‘ or ‚\h‘ for help. Type ‚\c‘ to clear the current input statement.
mysql> grant all on sampdb.* to ‚tsolar’@’localhost‘;
Query OK, 0 rows affected (0.00 sec)
Teď již v po řádku
mysql> CREATE DATABASE sampdb;
Query OK, 1 row affected (0.00 sec)
mysql> select database();
+————+
| database() |
+————+
| NULL |
+————+
1 row in set (0.00 sec)
mysql> use sampdb;
Database changed
mysql> select database();
+————+
| database() |
+————+
| sampdb |
+————+
1 row in set (0.00 sec)
Další možnost, jak se do databáze připojit rovnou.
[root@testsrv01 sampdb]# mysql -p -u tsolar sampdb
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.15 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‚help;‘ or ‚\h‘ for help. Type ‚\c‘ to clear the current input statement.
mysql> select database();
+————+
| database() |
+————+
| sampdb |
+————+
1 row in set (0.00 sec)
Vytvoření tabulky
Buď použijete SQL příkaz CREATE TABLE nebo spustíte skript, ve kterém jsou veškeré definice napsány.
Já jsem použil druhý způsob, proto již mám definice tabulek, které se budou dale používat.
mysql> source create_president.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> source create_member.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> describe president;
+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| last_name | varchar(15) | NO | | NULL | |
| first_name | varchar(15) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| city | varchar(20) | NO | | NULL | |
| state | varchar(2) | NO | | NULL | |
| birth | date | NO | | NULL | |
| death | date | YES | | NULL | |
+————+————-+——+—–+———+——-+
7 rows in set (0.00 sec)
mysql> show columns from president like ‚%name‘;
+————+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+————-+——+—–+———+——-+
| last_name | varchar(15) | NO | | NULL | |
| first_name | varchar(15) | NO | | NULL | |
+————+————-+——+—–+———+——-+
2 rows in set (0.00 sec)
mysql> show tables;
+——————+
| Tables_in_sampdb |
+——————+
| member |
| president |
+——————+
2 rows in set (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| sampdb |
| test |
+——————–+
3 rows in set (0.00 sec)
Databáze information_schema a test jsou defaultní databáze, vytvořené při založení serveru.
Informace lze získat i z příkazové řádky
[root@testsrv01 sampdb]# mysqlshow -u root@sampdb
+——————–+
| Databases |
+——————–+
| information_schema |
| test |
+——————–+
Jak zjistit co, která chyba znamená?
mysql> source create_score.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
ERROR 1005 (HY000): Can’t create table ‚sampdb.score‘ (errno: 150)
Jeden ze způsobů je využít utilitu perror z příkazové řádky.
[root@testsrv01 sampdb]# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
Vložení řádků do tabulky
mysql> show tables;
+——————+
| Tables_in_sampdb |
+——————+
| absence |
| grade_event |
| member |
| president |
| score |
| student |
+——————+
6 rows in set (0.01 sec)
mysql> select * from president;
Empty set (0.00 sec)
mysql> source insert_president.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
…
mysql> select * from president;
+————+—————+——–+———————+——-+————+————+
| last_name | first_name | suffix | city | state | birth | death |
+————+—————+——–+———————+——-+————+————+
| Washington | George | NULL | Wakefield | VA | 1732-02-22 | 1799-12-14 |
| Adams | John | NULL | Braintree | MA | 1735-10-30 | 1826-07-04 |
| Jefferson | Thomas | NULL | Albemarle County | VA | 1743-04-13 | 1826-07-04 |
…
Další způsob je použít hromadný load z textového souboru.
mysql> load data local infile ‚member.txt‘ into table member;
Query OK, 102 rows affected (0.00 sec)
Records: 102 Deleted: 0 Skipped: 0 Warnings: 0
Nebo z příkazového řádku
[root@testsrv01 sampdb]#mysqlimport –local sampdb member.txt