数据类型：
数据类型是指列、存储过程的参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。
——————————————————————————————————————————————————————————
在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> PROMPT set 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.12 sec) root@127.0.0.1 (none)>USE t2; Database changed root@127.0.0.1 t2>SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | t2 | +------------+ 1 row in set (0.00 sec) root@127.0.0.1 t2>
——————————————————————————————————————————————
root@127.0.0.1 t2>CREATE TABLE t2( -> username VARCHAR(20), -> age TINYINT UNSIGNED, -> 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.00 sec) root@127.0.0.1 t2>SHOW TABLES FROM MYSQL; +---------------------------+ | 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 FROM tbl_name; 3.查看当前数据库中的表:SHOW TABLES;
root@127.0.0.1 t2>SHOW COLUMNS FROM t2 -> ; +----------+---------------------+------+-----+---------+-------+ | 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 * FROM t2; +----------+------+---------+ | 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 TABLE tb1( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(30) NOT NULL -> ); Query OK, 0 rows affected (0.23 sec) root@127.0.0.1 t2>INSERT tb1(username) VALUES('Tom'); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>INSERT tb1(username) VALUES('Dean'); Query OK, 1 row affected (0.14 sec) root@127.0.0.1 t2>SELECT * FROM tb1; +----+----------+ | id | username | +----+----------+ | 1 | Tom | | 2 | Dean | +----+----------+ 2 rows in set (0.00 sec)
——————————————————————————————————————————————————————————————
初涉唯一的约束：
root@127.0.0.1 t2>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.10 sec) root@127.0.0.1 t2>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.00 sec)
————————————————————————————————————————————————
初涉默认约束：
root@127.0.0.1 t2>CREATE TABLE tb5( -> 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.21 sec) root@127.0.0.1 t2>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 | | | sex | enum('1','2','3') | YES | | 3 | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) root@127.0.0.1 t2>INSERT tb5(username) VALUES('Tom'); Query OK, 1 row affected (0.18 sec) root@127.0.0.1 t2>SELECT * FROM tb5; +----+----------+------+ | id | username | sex | +----+----------+------+ | 1 | Tom | 3 | +----+----------+------+ 1 row in set (0.00 sec)