#not null default例子
create table t7 (id int not null default 8);
desc t7;
insert into t7 values();
SELECT * from t7;
insert into t7 values(NULL);
#auto_increment例子
自增键,每张表只能一个字段为自增
create table t8(id int unique auto_increment,name char(10));
desc t8;
insert into t8 (name) values("newrain");
SELECT * from t8;
insert into t8 (name) values("ehome");
SELECT * from t8;
#default例子:
create table student4 (id int not null,
name varchar(50) not null,
sex enum('m','FROM') default 'm' not null,
age int unsigned default 18 not null,
hobby set('music','disc','dance','book') default 'book,dance');
desc student4;
insert into student4 values (1,'jack','m',20,'book');
SELECT * from student4;
insert into student4 (id,name) values (12,"newrain");#insert into student4 values (3,NULL,'m',22,'book');不能为空
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES (值1,值2,值3…值n);
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法:
INSERT INTO 表1(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
#例子:
create table student7(id int,name varchar(20),sex enum('m','FROM'),age int(2));
desc student7;
insert into student7 values(1,'jack','m',20);#顺序插入数据
insert into student7(name,age) values('bob',21);#指定字段插入数据
insert into student7 values(6,'jex','m',21),(7,'bob1','FROM',22);#插入多条记录#插入查询结果:
create table student_his(id int,name varchar(20),sex enum('m','FROM'),age int(2));
insert into student_his SELECT * from student7 WHERE name ='bob';#插入查询结果
insert into student_his SELECT * from student7 WHERE age >17;#插入符合条件的
更新数据update
语法:
UPDATE 表名 SET 字段1=值1,字段2=值2 WHERE CONDITION;
#示例:
update student7 setid=8;#修改全部
SELECT * from student7;
update student7 setid=9 WHERE name+"bob1";#指定条件
SELECT * from student7;
update student7 setid=10,name="newrain" WHERE name="bob1";
SELECT * from student7;
删除数据delete
语法:
DELETE FROM 表名
WHERE CONITION;
#示例:
DELETE FROM mysql.user WHERE authentication_string=’’;#delete from 表名 WHERE 条件;
delete from student7 WHERE name="jack";#delete from 表名; //删除表的全部数据
delete from student7;
SELECT * from student7;#从磁盘抹除数据,大量数据时使用 有外键需要先删除外键再删除表
truncate 表名;
alter 尽量不要在高峰区使用否则服务器会变慢