一、插入数据
1.插入一行
insert into 表名(列名1,列名2,列名3)values(值1,值2,值3);
insert into orders (order_num,order_date,cust_id) values ('20010','2005-10-09 00:00:00','10001');
2.插入多行数据
insert into orders (
order_num,
order_date,
cust_id
)
values (
'20011',
'2005-10-09 00:00:00',
'10001'),
(
'20012',
'2005-10-10 00:00:00',
'10002');
二、更新和删除数据
1.更新表
update 表名 set 列名=‘’where 搜索条件
update orders set order_date='2015-10-10 00:00:00',cust_id=10003 where order_num=20012;
2.删除数据
delete from 表名 where 条件
delete from orders where order_num=20012;
3.truncate table
删除原来的表并重新建立一个表
truncate table students;
三 创建和操纵表
1.创建表
create table students
( student_id int not null auto_increment,
student_name char(50) not null default 1,
student_city char(50) not null,
student_email char(50) not null,
primary key (student_id)
) engine=innoDB;
2.更新表
给表添加一个列
alter table student add student_phone char(20);
删除刚添加的主建
alter table student drop column student_phone;
3.使用alter table 定义主建
alter table student add constraint fk_student_students foreign key (student_id) references students (student_id);
4.删除表
drop table students;
删除表因为设置了外键 ,不能删除表
解决办法:
SET foreign_key_checks = 0; // 先设置外键约束检查关闭
drop table students; // 删除表,如果要删除视图,也是如此
SET foreign_key_checks = 1; // 开启外键约束检查,以保持表结构完整性
show VARIABLES like "foreign%";//最后检查:外键约束是否开启
5.重命表名
rename table student to student_11;
参考链接:https://blog.youkuaiyun.com/u010429286/article/details/79042886