目录
一、列属性
1、列类型VS列属性
列类型主要用于约束单独的一个字段在整行中的取值限制。(例如 一个学生(一行数据),学号的取值为字符串、名字的取值为字符串、年龄为整型...)
列属性主要用于约束单独的一个字段在整列中的取值限制。(例如 所有学生的学号(一列数据),每个学号都不重复...)
2、null
当我们没有为某个字段提供数据时,mysql会使用null值填充
3、not null
主要用于约束当前的字段不允许为null值(不允许MySQL使用null进行填充)
当为某个字段设置了not null约束后,要么在插入数据时必须提供数据,要么为not null字段设置一个默认值
CREATE TABLE test_null(
name VARCHAR(20) NOT NULL ,
sex VARCHAR(10) NOT NULL ,
hobby VARCHAR(100)
);
4、default
自定义默认值。
CREATE TABLE test_null(
name VARCHAR(20) NOT NULL ,
sex VARCHAR(10) NOT NULL DEFAULT 'man',
hobby VARCHAR(100)
);
default关键字
在插入语句时可以使用defult关键字来告诉MySQL使用默认值代替输入数据
INSERT INTO test_null VALUES ('wangwu',DEFAULT ,'swimming');
5、unique
用于约束当前字段在整列中是唯一的。(不允许字段的值重复,但运行多个字段为null,因为null值不等于null)
CREATE TABLE test_unique(
id VARCHAR(16) UNIQUE ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(10) NOT NULL DEFAULT 'man',
hobby VARCHAR(100)
);
6、primary key
primary key 主键。不允许重复,且不允许为null
CREATE TABLE test_primary(
id VARCHAR(16) PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(10) NOT NULL DEFAULT 'man',
hobby VARCHAR(100)
);
7、auto_increment
在数据库应用中,我们经常需要用到自动递增的唯一编号来标识记录。在MySQL中,可通过数据列的auto_increment属性来自动生成
CREATE TABLE test_primary(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(10) NOT NULL DEFAULT 'man',
age TINYINT UNSIGNED
)CHARSET utf8;
INSERT INTO test_primary VALUES (DEFAULT ,'zhangsan','man',30);
INSERT INTO test_primary VALUES (DEFAULT ,'lisi','man',20);
提示:
对于auto_increment属性,必须应用在整型字段并且是primary key或者unique。
8、模拟主键
当一个表中如果没有设置primary key,那么MySQL会在表中寻找第一个具有unique与not null越苏的字段作为主键字段
DROP TABLE IF EXISTS test_primary;
CREATE table test_primary(
f1 INT UNSIGNED UNIQUE NOT NULL ,
f2 INT UNSIGNED UNIQUE ,
f3 INT UNSIGNED NOT NULL
)CHARSET utf8;
提示:
一个表中只能有一个主键
一个表中能有多个唯一键
一个表中只能有一个自增长
9、复合主键
MySQL中可以使用多个字段,共同组成主键
语法:
primary key(字段1,字段2,...)
CREATE TABLE test(
f1 INT UNSIGNED,
f2 CHAR(10),
PRIMARY KEY (f1,f2)
)CHARSET utf8;
对于复合主键的值,只有当复合主键的字段的值完全相同的时候才算重复
10.备注
语法:
comment
-- test_comment
CREATE TABLE test_comment (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
s_num CHAR(10) UNIQUE COMMENT '学号',
s_name CHAR(16) COMMENT '姓名',
s_sex CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
s_age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄'
)CHARSET utf8;
创建完之后可以通过show create table test_comment查看到备注;
二、修改表结构
alter table 表名 add | drop | modify | change
1、增加与删除字段
①、添加
alter table 表名 add [column] 字段名 列类型 列属性 【first | after 字段名】
DROP TABLE IF EXISTS test;
CREATE TABLE test(
s_name CHAR(32)
)CHARSET utf8;
ALTER TABLE test add s_age TINYINT UNSIGNED;
ALTER TABLE test add s_sex CHAR(2) AFTER name;
ALTER TABLE test add s_num CHAR(10) FIRST;
②、删除
alter table 表名 字段名
ALTER TABLE test DROP s_num;
2、修改字段
alter table 表名 change 原字段名 新字段名 列类型 【列属性】 【first | after字段名】
可以修改字段名,列属性,列类型和列所在的位置。如果原字段具有属性(例如not null,unique等),在修改时未填写列属性系统默认您将删除改属性。若填写则覆盖或添加属性
ALTER TABLE test CHANGE s_sex sex CHAR(10) DEFAULT '男';
3、修改表名
alter table 表名 rename to 新名
4、修改表选项
【表选 项】
engine 存储引擎
charset 存储字符集
collate 校验集
......
语法:
alter table 表名 表选项 新值;
提示:
虽然MySQL提供了修改存储字符集的命令,但是如果一个表中已有数据,那么最好不要执行修改存储字符集的命令。
5、修改列属性
列属性包含not null、default、auto_increment、primary key、unique.
当一个表创建之后,对于列属性的操作,我们可以使用alter table 表名 modify来操作,在操作时,如果书写了列属性就是添加列属性,如果没有书写就是删除原有属性。
①、增加列属性
DROP TABLE IF EXISTS test;
CREATE TABLE test (
f1 int UNSIGNED,
f2 char(10),
f3 int UNSIGNED
)CHARSET utf8;
ALTER TABLE test MODIFY f1 INT NOT NULL DEFAULT 0;
ALTER TABLE test MODIFY f2 CHAR(10) UNIQUE ;
ALTER TABLE test MODIFY f3 INT UNIQUE AUTO_INCREMENT;
②、添加主键
alter table 表名 add primary key (字段名【,字段2...】)
ALTER TABLE test add PRIMARY KEY (f1);
③、添加唯一键
alter table 表面 add unique 字段名
ALTER TABLE test ADD UNIQUE(f2);
④、删除属性
对于not null、defaule、autuo_increment不书写就是删除
DROP TABLE IF EXISTS test;
CREATE TABLE test (
f1 int UNSIGNED NOT NULL DEFAULT 0,
f2 char(10) UNIQUE ,
f3 int UNSIGNED UNIQUE AUTO_INCREMENT
)CHARSET utf8;
-- 删除了f1的not null 和 default
ALTER TABLE test MODIFY f1 INT;
⑤ 、删除primary key 与unique
如果auto_increment与primary key 或unique联用,那么要删除primary key活unique要先删除 auto_increment
删除primary key语法:
alter table 表名 drop primary key();
删除unique语法:
alter table 表名 drop key 唯一键的索引名 (唯一键的索引名默认为字段名)
DROP TABLE IF EXISTS test;
CREATE TABLE test (
f1 int UNSIGNED PRIMARY KEY ,
f2 char(10) UNIQUE ,
f3 int UNSIGNED UNIQUE AUTO_INCREMENT
)CHARSET utf8;
ALTER TABLE test DROP PRIMARY KEY ;
ALTER TABLE test DROP KEY f2;
三、高级操作
1、复制表结构
语法:
create table 表B like 表A
使用表A的结构重修创建一个表B,只复制结构不复制数据
2、备份SQL执行结果
语法:
create table 表名 select 语句;
说明:
将一条select语句得到的结果保存到一个新创建的表中。
3、限制更新
语法:
update 表名 set 字段 = 值 【where子句】【limit子句】
说明:
用于将where子句匹配到记录,仅更改limit子句限制的条数。
4、限制删除
语法:
delete form 表名 【where子句】【limit子句】
说明:
用于将where子句匹配到记录,仅更改limit子句限制的条数
5、批量插入
语法:
insert into 表A 【字段列表】 select 字段列表 from 表B;
说明:
将select语句得到的数据,插入到表A中。
6、蠕虫复制
蠕虫复制是批量插入的一个特殊情况,在使用批量插入语句时,如果插入的表与数据查询的表是同一个表那么这种情况,术语就叫蠕虫复制。
7、主键冲突
①、冲突更新
语法:
insert into 表名 【字段列表】 values(值列表) on duplicate key updaye 字段 = 值,字段=值...;
当主键冲突的时执行更新操作
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id INT UNIQUE AUTO_INCREMENT,
name CHAR(10),
num INT UNSIGNED
);
-- 进货
INSERT INTO goods VALUES (1,'华为',50) on DUPLICATE KEY UPDATE num=num+50;
②、冲突替换
语法:
replace into 表名 【字段列表】 values(值列表)
说明:
当主键发生冲突时,将所有新数据替换就数据
replace into goods VALUES (2,'vivo',50);
8、清空表
语法一:
delete from 表名;
说明:
只会将表中的数据删除,并不会重建索引
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id INT UNIQUE AUTO_INCREMENT,
name CHAR(10),
num INT UNSIGNED
);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
SELECT * FROM goods;
DELETE FROM goods;
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
SELECT * FROM goods;
语法二:
truncate 表名;
说明:
不但会删除表中的数据,还会重建索引;
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id INT UNIQUE AUTO_INCREMENT,
name CHAR(10),
num INT UNSIGNED
);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
SELECT * FROM goods;
TRUNCATE goods;
INSERT INTO goods VALUES (DEFAULT ,'华为',50);
SELECT * FROM goods;