1. 打开数据库:
USE 数据库名称;
mysql> USE test;
Database changed
2. 显示已打开的数据库:
mysql> SELECT DATABASE();
±-----------+
| DATABASE() |
±-----------+
| test |
±-----------+
1 row in set (0.00 sec)
3. 创建数据表:
CREATE TABLE [IF NOT EXISTS]table_name(
column_name data_type,
…
)
mysql> CREATE TABLE tb1(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(5,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.01 sec)
4. 查看数据表列表:
SHOW TABLES [FROM db_name]
[LINK ‘pattern’ | WHERE expr]
mysql> SHOW TABLES;
±---------------+
| Tables_in_test |
±---------------+
| tb1 |
±---------------+
1 rows in set (0.00 sec)
5. 查看数据表结构:
SHOW COLUMNS FROM tbl_name
mysql> SHOW COLUMNS FROM tb1;
±---------±--------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±--------------------±-----±----±--------±------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(5,2) unsigned | YES | | NULL | |
±---------±--------------------±-----±----±--------±------+
3 rows in set (0.02 sec)
6. 向数据表写入记录:
INSERT [INTO] tbl_name [(col_name,…)]VALUES(val,…)
mysql> insert tb1 VALUES(‘Tom’, 23, 65.15);
Query OK, 1 row affected (0.01 sec)
mysql> insert tb1(username, salary) values(‘John’, 4500.45);
Query OK, 1 row affected (0.01 sec)
7. 查看整个数据表:
SELECT expr,… FROM tb1_name
mysql> select * from tb1;
±---------±-----±-------+
| username | age | salary |
±---------±-----±-------+
| Tom | 23 | 65.15 |
±---------±-----±-------+
1 row in set (0.00 sec)
8. 空值与非空:
NULL 字段值可以为空
NOT NULL 字段值禁止为空
mysql> CREATE TABLE tb2(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.02 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.01 sec)
mysql> INSERT tb2 VALUES(NULL, 23);
ERROR 1048 (23000): Column ‘username’ cannot be null
9. 自动编号AUTO_INCREMENT:
必须与主键组合使用,默认情况下,起始值为1,每次增量为1
10. 主键PRIMARY KEY:
① 主键约束
② 每张数据表只能存在一个主键
③ 主键保证记录的唯一性
④ 主键自动为NOT NULL
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT tb3(username, age) VALUES(‘Tom’, 12);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT tb3(username, age) VALUES(‘John’, 10);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb3(username, age) VALUES(‘Rose’, 8);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT tb3(username, age) VALUES(‘Dimiter’, 15);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from tb3;
±—±---------±-----+
| id | username | age |
±—±---------±-----+
| 1 | Tom | 12 |
| 2 | John | 10 |
| 3 | Rose | 8 |
| 4 | Dimiter | 15 |
±—±---------±-----+
4 rows in set (0.00 sec)
mysql> INSERT tb3 VALUES(8, ‘Tim’, 13);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from tb3;
±—±---------±-----+
| id | username | age |
±—±---------±-----+
| 1 | Tom | 12 |
| 2 | John | 10 |
| 3 | Rose | 8 |
| 4 | Dimiter | 15 |
| 8 | Tim | 13 |
±—±---------±-----+
5 rows in set (0.00 sec)
11. 唯一约束UNIQUE KEY:
① 唯一约束可以保证记录的唯一性
② 唯一约束的字段可以为空值(NULL)
③ 每张数据表可以存在多个唯一约束
创建一个既有主键约束又有唯一约束的数据表:
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW COLUMNS FROM tb4;
±---------±---------------------±-----±----±--------±---------------+
| 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.01 sec)
mysql> INSERT tb4(username, age) VALUES(‘Tom’, 22);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT tb4(username, age) VALUES(‘Tom’, 13);
ERROR 1062 (23000): Duplicate entry ‘Tom’ for key ‘username’
mysql> select * from tb4;
±—±---------±-----+
| id | username | age |
±—±---------±-----+
| 1 | Tom | 22 |
±—±---------±-----+
1 row in set (0.00 sec)
12. 默认值DEFAULT:
当插入记录时,如果没有明确的字段赋值,则自动赋予默认值
mysql> CREATE TABLE tb6(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL,
-> sex ENUM(‘1’,‘2’,‘3’)DEFAULT ‘3’
-> );
Query OK, 0 rows affected (0.01 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 | | NULL | |
| sex | enum(‘1’,‘2’,‘3’) | YES | | 3 | |
±---------±---------------------±-----±----±--------±---------------+
3 rows in set (0.01 sec)
mysql> INSERT tb6(username) VALUES(‘Tom’);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tb6;
±—±---------±-----+
| id | username | sex |
±—±---------±-----+
| 1 | Tom | 3 |
±—±---------±-----+
1 row in set (0.00 sec)
MySQL之数据表基本操作
最新推荐文章于 2022-11-14 15:06:46 发布