首页 > > 网络编程 > Mysql >

MySQL学习笔记 -- 数据表的基本操作

2018-06-17 23:10:43来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

　　

　　数据库是一个可以存放数据库对象的容器，数据库对象包括：表、视图、存储过程、函数、触发器、事件。其中，表是数据库最基本的元素，是其他数据库对象的前提条件。

　　表中的一列称为一个字段，一行称为一条记录。

 

　　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)

 

　　8.外键约束 FOREIGN KEY

　　　　※ 父表和子表必须使用相同的存储引擎 InnoDB

　　　　※ 外键列和参照列必须具有相似的数据类型。其中数字的长度和是否有符号位必须相同；而字符的长度可以不同。

　　　　※ 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW INDEXES FROM provinces\G
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM users\G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：mysql主从配置

下一篇：SQL基础概念-指令

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签