MySQLベース2：データテーブル操作

このサイトを参照してくださいhttp://www.imooc.com/video/2004
すべてのデータベースを表示:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| t2                 |
| world              |
+--------------------+

データベースを開く:

mysql> USE world
Database changed

は現在開いているデータベースを表示します。

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| world      |
+------------+
1 row in set (0.00 sec)

は、データテーブルを作成します。

mysql> CREATE TABLE IF NOT EXISTS tb1(
    -> username VARCHAR(20),#  ，    20  、
    -> age TINYINT UNSIGNED,#     、
    -> salary FLOAT(8,2) UNSIGNED #   ，    ，    、
    -> );
Query OK, 0 rows affected (0.32 sec)

はデータテーブルを調べます。

mysql> SHOW TABLES
    -> ;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| tb1             |
+-----------------+
4 rows in set (0.01 sec)

は他のデータベースのデータテーブルを調べます。

mysql> SHOW TABLES FROM mysql
    -> ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

データテーブル構造を表示:

mysql> SHOW COLUMNS FROM tb1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

データテーブルにレコードを書き込みます。

mysql> INSERT INTO tb1 VALUES('tom',25,7863.25);#  INTO    
Query OK, 1 row affected (0.17 sec)

第二の書き込み方法は、すべてのフィールドに値が与えられていません。

mysql> INSERT tb1(username,salary) VALUES('John',4500.69);
Query OK, 1 row affected (0.11 sec)

検索用のselect文を記録します。*フィールドのフィルタリングを表します。

mysql> SELECT * FROM tb1;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| tom      |   25 | 7863.25 |
| John     | NULL | 4500.69 |
+----------+------+---------+
2 rows in set (0.02 sec)

NULLフィールドは空であり、デフォルトであることができる。NOT NULLは不可欠です。

mysql> CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL #  NULL    
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> SHOW COLUMNS FROM tb2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT tb2 VALUES('TOM',NULL);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM tb2;
+----------+------+
| username | age  |
+----------+------+
| TOM      | NULL |
+----------+------+
1 row in set (0.00 sec)

mysql> INSERT tb2 VALUES(NULL,26);
ERROR 1048 (23000): Column 'username' cannot be null

自動番号のフィールドは、エラーが発生したら、自動番号はメインキーと合わせて使用しなければなりません。

mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

は、キー付きの自動番号表を作成します。

<pre name="code" class="sql">mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW COLUMNS FROM tb3;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> INSERT tb3(username) VALUES('Tom');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT tb3(username) VALUES('John');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tb3(username) VALUES('Rose');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT tb3(username) VALUES('Dimitar');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM tb3;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | John     |
|  3 | Rose     |
|  4 | Dimitar  |
+----+----------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE tb4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.19 sec)

 
 
 主键可以没有AUTO_INCRMENT： 
  
  
 
mysql> SHOW COLUMNS FROM tb4;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT tb4 VALUES(4,'Tom');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tb4 VALUES(22,'John');
Query OK, 1 row affected (0.21 sec)

mysql> SELECT * FROM tb4;
+----+----------+
| id | username |
+----+----------+
|  4 | Tom      |
| 22 | John     |
+----+----------+
2 rows in set (0.00 sec)

mysql> INSERT tb4 VALUES(22,'Rose');
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'

もう一つの一意の制約、unique制約、メインキーとユニークなテーブルを作成します。
 mysql> CREATE TABLE tb5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.22 sec)


mysql>
mysql> SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> INSERT tb5(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.04 sec)


mysql> INSERT tb5(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
デフォルトの制約:
mysql> CREATE TABLE tb6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW COLUMNS FROM tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tb6;
Empty set (0.00 sec)