mysql 列表数据类型_mysql之数据类型以及操作数据表

数据类型：

数据类型是指列、存储过程的参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

——————————————————————————————————————————————————————————

在mysql当中数据类型大概有以下几类：

————————————————————————————————————————————

1.整型：

————————————————————————————————————————————————————————————————

浮点型：

————————————————————————————————————————————————————

日期时间型：

上述的几种类型都有自己的存储的范围，每个存储范围都不同，TIMESTAMP指的是时间戳。

DATE：经常用起来存储时间(1000~9999.12.31)

DATETIME:支持的时间是1000.0.0.0到9999.12.31.11.59

DATWTEMP：1970~2037之间的一个时间。

TIME ：8385959~8385959之间的一个过程。

————————————————————————————————————————————————————————————————

字符型：

——————————————————————————————————————————————————————————————————

数据表：

mysql> PROMPT \u@\h \d>PROMPTset to '\u@\h \d>'root@127.0.0.1 (none)>SHOW DATABASES;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| t2 |

| test |

+--------------------+

5 rows in set (0.12sec)

root@127.0.0.1 (none)>USEt2;Databasechanged

root@127.0.0.1 t2>SELECT DATABASE();+------------+

| DATABASE() |

+------------+

| t2 |

+------------+

1 row in set (0.00sec)

root@127.0.0.1 t2>

——————————————————————————————————————————————

root@127.0.0.1 t2>CREATE TABLEt2(-> username VARCHAR(20),-> age TINYINTUNSIGNED,-> salary FLOAT(8,2) UNSIGNED->);

Query OK,0 rows affected (0.22 sec)

VARCHAR(20)指的是我们的名字的长度有20位。

TINYINT UNSIGNED指的是我们的年龄不需要包含负数。

FLOAT(8,2)指的是我们的整个工资一共有8位，其中小数点后面有2位。

——————————————————————————————————————————————

查看数据表：

MYSQL 查看数据表:

SHOW TABLES[FROM db_name]

root@127.0.0.1 t2>SHOW TABLES;+--------------+

| Tables_in_t2 |

+--------------+

| t2 |

+--------------+

1 row in set (0.00sec)

root@127.0.0.1 t2>SHOW TABLES FROMMYSQL;+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

24 rows in set (0.11 sec)

——————————————————————————————————————————

查看数据表的结构：

1.查看数据表列表： SHOW TABLE [FROM db_name];2.查看数据表的结构：SHOW COLUMNS FROMtbl_name;3.查看当前数据库中的表:SHOW TABLES;

root@127.0.0.1 t2>SHOW COLUMNS FROMt2->;+----------+---------------------+------+-----+---------+-------+

| 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.00 sec)

——————————————————————————————————————————————————————————

修改数据表：

往数据表里面插入数据：插入记录

insert [into] tbl_name[(col_name,....)] values(val,...)省略列名，则必须给所有字段赋值

root@127.0.0.1 t2>INSERT t2 VALUES('TOM',25,7857.5);

Query OK,1 row affected (0.10 sec)

root@127.0.0.1 t2>INSERT t2(username,age) VALUES('John',25);

Query OK,1 row affected (0.14 sec)

记录查找

selece expr,....from tbl_name

root@127.0.0.1 t2>SELECT * FROMt2;+----------+------+---------+

| username | age | salary |

+----------+------+---------+

| TOM | 25 | 7857.50 |

| John | 25 | NULL |

+----------+------+---------+

2 rows in set (0.00 sec)

——————————————————————————————————————————————————————

空值与非空值：

NOT NULL意味着我们在给它赋值的时候是不能够为空的。

————————————————————————————————————————————————

自动编号：

root@127.0.0.1 t2>CREATE TABLEtb1(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR(30) NOT NULL

->);

Query OK,0 rows affected (0.23sec)

root@127.0.0.1 t2>INSERT tb1(username) VALUES('Tom');

Query OK,1 row affected (0.14sec)

root@127.0.0.1 t2>INSERT tb1(username) VALUES('Dean');

Query OK,1 row affected (0.14sec)

root@127.0.0.1 t2>SELECT * FROMtb1;+----+----------+

| id | username |

+----+----------+

| 1 | Tom |

| 2 | Dean |

+----+----------+

2 rows in set (0.00 sec)

——————————————————————————————————————————————————————————————

初涉唯一的约束：

root@127.0.0.1 t2>CREATE TABLEtb4(-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR(20) NOT NULL UNIQUE KEY,-> age TINYINTUNSIGNED);

Query OK,0 rows affected (0.10sec)

root@127.0.0.1 t2>SHOW COLUMNS FROMtb4;+----------+----------------------+------+-----+---------+----------------+

| 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)

————————————————————————————————————————————————

初涉默认约束：

root@127.0.0.1 t2>CREATE TABLEtb5(-> 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.21sec)

root@127.0.0.1 t2>SHOW COLUMNS FROMtb5;+----------+----------------------+------+-----+---------+----------------+

| 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.00sec)

root@127.0.0.1 t2>INSERT tb5(username) VALUES('Tom');

Query OK,1 row affected (0.18sec)

root@127.0.0.1 t2>SELECT * FROMtb5;+----+----------+------+

| id | username | sex |

+----+----------+------+

| 1 | Tom | 3 |

+----+----------+------+

1 row in set (0.00 sec)