程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL學習筆記，mysql筆記

MySQL學習筆記，mysql筆記

編輯：MySQL綜合教程

MySQL學習筆記，mysql筆記

　　數據庫是一個可以存放數據庫對象的容器，數據庫對象包括：表、視圖、存儲過程、函數、觸發器、事件。其中，表是數據庫最基本的元素，是其他數據庫對象的前提條件。

　　表中的一列稱為一個字段，一行稱為一條記錄。

　　1.數據表的創建、查看數據表、查看數據表結構

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> USE test1;
Database changed

mysql> CREATE TABLE table1(
    -> id SMALLINT UNSIGNED,
    -> username VARCHAR(20),
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

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

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
+-----------------+
1 row in set (0.00 sec)

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

mysql> DESCRIBE table1;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | YES  |     | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
| age      | tinyint(4)           | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

　　2.數據表的刪除 DROP TABLE table_name;

mysql> USE test1;
Database changed

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| tb2             |
+-----------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE tb2;
Query OK, 0 rows affected (0.20 sec)

mysql> DESCRIBE tb2;
ERROR 1146 (42S02): Table 'test1.tb2' doesn't exist

　　3.插入記錄、查看記錄 INSERT table_name [(col_name, ...)] VALUES(...);

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

mysql> INSERT table1 VALUES(5,'Tom',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table1 (id,username) VALUES(5,'Tom');
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM table1;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    5 | Tom      |   22 |
|    5 | Tom      | NULL |
+------+----------+------+
2 rows in set (0.00 sec)

　　4.字段的空值與非空 NULL,NOT NULL

mysql> CREATE TABLE table2(
    -> username VARCHAR(20) NOT NULL,
    -> #NULL可加可不加，不加默認可以為空#
    -> age TINYINT
    -> );
Query OK, 0 rows affected (0.25 sec)

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

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

　　5.主鍵約束與自動編號 PRIMARY KEY,AUTO_INCREMENT

　　　　※ 一張數據表只能存在一個主鍵

　　　　※ 主鍵能保證記錄的唯一性

　　　　※ 主鍵自動為NOT NULL

　　　　※ AUTO_INCREMENT只能配合PRIMARY KEY使用，不能單獨使用。PRIMARY KEY可以單獨使用。

mysql> CREATE TABLE table3(
    -> id SMALLINT PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> DESCRIBE table3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

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

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

mysql> INSERT table3 VALUES(4,'David');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table3 (username) VALUES('Somebody');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM table3;
+----+------------+
| id | username   |
+----+------------+
|  1 | XingyaZhao |
|  2 | XuebiBaby  |
|  4 | David      |
|  5 | Somebody   |
+----+------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE table4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.30 sec)

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

mysql> INSERT table4 VALUES(6,'ChuanDao');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table4 VALUES(3,'Pigiu');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT table4 VALUES(3,'York');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> SELECT * FROM table4;
+----+----------+
| id | username |
+----+----------+
|  3 | Pigiu    |
|  6 | ChuanDao |
+----+----------+
2 rows in set (0.00 sec)

　　6.唯一約束 UNIQUE KEY

　　　　※ 唯一約束可以保證某個字段中每個記錄的唯一性

　　　　※ 唯一約束的字段可以為空值

　　　　※ 每張數據表可以存在多個唯一約束

mysql> CREATE TABLE table5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> SHOW COLUMNS FROM table5;
+----------+----------------------+------+-----+---------+----------------+
| 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  | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',22);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT table5 (username,age) VALUES('XuebiBaby',21);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table5 (username,age) VALUES('XingyaZhao',18);
ERROR 1062 (23000): Duplicate entry 'XingyaZhao' for key 'username'

mysql> SELECT * FROM table5;
+----+------------+-----+
| id | username   | age |
+----+------------+-----+
|  1 | XingyaZhao |  22 |
|  2 | XuebiBaby  |  21 |
+----+------------+-----+
3 rows in set (0.00 sec)

　　7.默認約束 DEFAULT

mysql> CREATE TABLE table6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED DEFAULT 20
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> DESCRIBE table6;
+----------+----------------------+------+-----+---------+----------------+
| 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  |     | 20      |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT table6 (username) VALUES('Xingya');
Query OK, 1 row affected (0.08 sec)

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

mysql> INSERT table6 (username,age) VALUES('Tom',21);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM table6;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Xingya   |   20 |
|  2 | Jude     |   20 |
|  3 | Tom      |   21 |
+----+----------+------+
3 rows in set (0.00 sec)