MySQL基础二：数据表操作

参考该网站：http://www.imooc.com/video/2004

显示所有数据库：

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

打开一个数据库：

mysql> USE worldDatabase 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 keymysql> 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)