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)

欢迎大家阅读《MySQL学习笔记 – 数据表的基本操作_mysql》,跪求各位点评，by 搞代码

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL学习笔记 – 数据表的基本操作_mysql

Hi，您需要填写昵称和邮箱！