MySQL
默认端口号3306
CREATE DATABASE
ALTER DATABASE
DROP DATABASE
TINYINT 1 Byte 有符号值 ： -128-127
无符号值：0-255
SMALLINT 2Byte 有符号值：-32768 32767
无符号值：0-65535
MEDIUMINT 3Byte 有符号位 -2^23 2^23-1
无符号位 0- 2^24-1
INT 4Byte -2^31 2^31-1
0-2^32-1
BIGINT 8 0-2^64-1
FLOAT[(M,D)] M总位数，D小数点后的位数
单精度浮点，约7位小数
DOUBLE[(M,D)] 双精度
YEAR 2位或4位
TIME
DATE
DATETIME
TIMESTAMP 1970-2030
CHARM(M)定长
VARCHAR(M)变长
ENUM('VALUE1','VALUE2')枚举
SET('VALUE1','VALUE2')组合
CREATE TABLE T1 ( username VARCHAR(20), age TINYINT UNSIGNED, salary FLOAT(8,2) UNSIGNED);
mysql> SELECT DATABASE
-> ();
+-------------+
| DATABASE
() |
+-------------+
| test |
+-------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW COLUMNS FROM T1
-> ;
+----------+---------------------+------+-----+---------+-------+
| 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.02 sec)
mysql> INSERT T1 VALUES('TOM',22,564.41);
Query OK, 1 row affected (0.35 sec)
mysql> INSERT T1(username,salary) VALUES('SAFA',6566.41);
Query OK, 1 row affected (0.30 sec)
mysql> SELECT *FROM T1;
+----------+------+---------+
| username | age | salary |
+----------+------+---------+
| TOM | 22 | 564.41 |
| SAFA | NULL | 6566.41 |
+----------+------+---------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM T4;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SELECT *FROM T4;
+----+----------+
| id | username |
+----+----------+
| 3 | TDODM |
| 4 | TOM |
| 44 | TDOM |
+----+----------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE T4(
-> id SMALLINT UNSIGNED PRIMARY KEY,
-> username VARCHAR(30) NOT NULL);
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW COLUMNS FROM T4;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
自动升序 AUTO_INCREMENT 若数据类型为浮点数，小数点后必须为0
主键约束 PRIMARY KEY 每张数据表只有一个
唯一约束 UNIQUE KEY
默认约束 ENUM('a','b','c') DEFAULT 'd'
非空约束 NOT NULL
表级约束 多个列 只能在列定义后声明
列级约束 单个列 可以在列定义时声明
外键约束
父表 和 子表 必须用相同的存储引擎，禁止使用临时表
只能用InnoDB
外键列和参照列必须具有相似的数据类型，数字长度，符号位必须相同，而字符长度则可以不同
外键列和参照列必须创建索引，如果外键列不存在索引的话，MYSQL将自动创建索引
查看存储引擎
SHOW CREATE TABLE TABLENAME\
mysql> CREATE TABLE provinces(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.34 sec)
mysql> SHOW CREATE TABLE provinces;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id));
Query OK, 0 rows affected (0.17 sec)
查看索引
SHOW INDEXES FROM users\G;
cascade
set null
restrict
no action
添加单列
ALTER TABLE tbl_name ADD col_name column_definition [FIRST | AFTER col_name];
添加多列
ALTER TABLE tbl_name ADD （col_name column_definition，.....）;
删除列
ALTER TABLE tbl_name DROP col_name ;
删除多列
ALTER TABLE tbl_name DROP col_name1,DROP col_name2,... ;
添加/删除默认约束:
alter table tb_name alter [column] col_name {set default literal | drop default}; eg:alter table users2 alter age drop default;
添加外键约束：
alter table tb_name add [constraint [symbol]] foreign key [index_name] (index_col_name,...) reference_definition ;
eg:alter table users2 add foreign key (pid) references provinces(id);
添加唯一约束：
alter table tb_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,...);
eg:alter table users2 add unique (username);
改数据表添加主键例子：
alter table users2 add constraint pk_users2_id prinary key (id);
加主键约束：
alter table tb_name add [constraint [symbol]] primary key [index_type] (index_col_name,...)
修改列定义 modify [column]
列名称 change [column] [old name][new name]
修改表名 rename
rename table users2 to users3,users1 to users4;
