mysql之表操作
一 创建表的完整语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#解释：
类型：使用限制字段必须以什么样的数据类型传值
约束条件：约束条件是在类型之外添加一种额外的限制
# 注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选，字段名和类型是必须的
3、最后一个字段后不加逗号
二 基本数据类型之整型：
1、作用：id号，各种号码，年龄，等级
2、分类：
tinyint（**）
int （*****）
bigint（***）
3、测试：默认整型都是有符号的
create table t1(x tinyint);
insert into t1 values(128),(-129);
create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);
create table t3(x int unsigned);
#4294967295
insert into t3 values(4294967296);
create table t4(x int(12) unsigned);
insert into t4 values(4294967296123);
4、强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制
所以在创建表示，如果字段采用的是整型类型，完全无需指定显示宽度，
默认的显示宽度，足够显示完整当初存放的数据
# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
约束条件：不够8位用0填充，如果超出8位则正常显示
insert into t5 values(4294967296123);
insert into t5 values(1);
补充：
# 查看sql_mode mysql> show variables like "%sql_mode%"; +----------------------------+---------------------+ | Variable_name | Value | +----------------------------+---------------------+ | binlogging_impossible_mode | IGNORE_ERROR | | block_encryption_mode | aes-128-ecb | | gtid_mode | OFF | | innodb_autoinc_lock_mode | 1 | | innodb_strict_mode | OFF | | pseudo_slave_mode | OFF | | slave_exec_mode | STRICT | | sql_mode | STRICT_TRANS_TABLES | +----------------------------+---------------------+ 8 rows in set (0.00 sec) #修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错 mysql> set global sql_mode="strict_trans_tables";
浮点型：
作用：存储身高、体重、薪资
分类：
float （*****）
double （**）
decimal （**）
测试：
#相同点
#1、对于三者来说，都能存放30位小数，
#不同点：
1、精度的排序从低到高：float,double,decimal
2、float与double类型能存放的整数位比decimal更多
create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));
insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);
mysql> select * from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t10;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t11;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)
字符类型：
1、作用：姓名，地址，描述类的信息
2、分类：
char 定长
varchar 变长
3、测试:字符的宽度限制单位是字符个数
create table t12(x char(4)); # 超出4个字符则报错，不够4个字符则用空格补全成4个字符
create table t13(y varchar(4));# 超出4个字符则报错，不够4个字符那么字符有几个就存几个
insert into t12 values('hello');
insert into t13 values('hello');
insert into t12 values('a'); #'a '
insert into t13 values('a'); #'a'
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
select char_length(x) from t12; #4
select char_length(y) from t13; #1
# 注意：
针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中
但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用
mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用
mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的
# 对比char与varchar
name char(5)
# 缺点：浪费空间
# 优点：存取速度都快
egon alex lxx wxx yx
name varchar(5)
# 缺点：存取速度都慢
# 优点：节省空间
(1bytes+egon)(1bytes+alex)(1bytes+lxx)
总结：现如今在储存设备成本低的情况下，我们一般考虑存储速度，所以我们使用char。
日期类型：
1、作用：时间相关
2、分类：
date：1999-01-27 #年月日
time: 11:11:11 #时分秒
datetime:1999-01-27 11:11:11 #年月日+时分秒
year:1999 #年
3、测试
create table student(
id int,
name char(16),
born_year year,
birth date,
class_time time,
reg_time datetime
);
insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');
============注意啦，注意啦，注意啦=========== 1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入 2. 插入年份时，尽量使用4位值 3. 插入两位年份时，<=69，以20开头，比如50, 结果2050 >=70，以19开头，比如71，结果1971 MariaDB [db1]> create table t12(y year); MariaDB [db1]> insert into t12 values -> (50), -> (71); MariaDB [db1]> select * from t12; +------+ | y | +------+ | 2050 | | 1971 | +------+ ============综合练习=========== MariaDB [db1]> create table student( -> id int, -> name varchar(20), -> born_year year, -> birth date, -> class_time time, -> reg_time datetime); MariaDB [db1]> insert into student values -> (1,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"), -> (2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"), -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13"); MariaDB [db1]> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth | class_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | alex | 1995 | 1995-11-11 | 11:11:11 | 2017-11-11 11:11:11 | | 2 | egon | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 | | 3 | wsb | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 | +------+------+-----------+------------+------------+---------------------+
枚举与集合类型：
作用与分类：
枚举enum，多选一个
集合set，多选多
测试
create table teacher(
id int,
name char(16),
sex enum('male','female','others'),
hobbies set('play','read','music','piao')
);
约束条件:
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
not null与default:
#not null + default(两者一般连用）：表示该字段不能为空，必须为其传值，不传值默认为使用默认值 create table t15( id int, name char(16) not null, #标识该字段不能为空，必须为其插值 sex enum('male','female','other') not null default "male" #当该字段没有传值时，使用默认值 ); alter table t15 modify name char(16) not null; insert into t15(id,name) values (1,'egon1'), (2,'egon2'), (3,'egon3');
unique:标识该字段的值是唯一的，一般用在id字段
#unique:限制字段的值唯一 #单列唯一 create table t16( id int unique, #标识该字段的值必须是唯一的 name char(16) ); # 联合唯一 create table server( id int unique, ip char(15), port int, unique(ip,port) #标识ip与port字段的值必须是唯一的 );
primary key：该字段的值不为空且是唯一的
从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
一个表中可以：
单列做主键
多列做主键（复合主键）
create table t17(
id int primary key,
name char(16),
age int,
sex char(6)
)engine=innodb;
#联合主键
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
# primary key auto_increment
create table t20(
id int primary key auto_increment,
name char(16)
)engine=innodb;
auto_increment:
约束字段为自动增长，被约束的字段必须同时被key约束
# auto_increment注意点：
1、通常与primary key连用，而且通常是给id字段加
2、auto_incremnt只能给被定义成key（unique key，primary key）的字段加
#不指定id，则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ row in set (0.00 sec)
