@概述
- 增删改查是数据库的基本操作;
- 习惯上用CRUD来分别代表增查改删:即Create,Retrieve,Update,Delete;
- 数据库的增删改查操作是通过SQL语句来实现的;
- MySQL中的SQL语句对大小写是不敏感的;
- 各大关系型数据库所使用的SQL语句整体上是大同小异的;
- 删除数据不是常规操作,要特别谨慎,一般企业开发中原则上是不删除数据的;
- 如果的确有删除需求,通常的做法是进行逻辑删除(例如标记isDelete=True),而不是物理删除;
@新增数据
- 创建校园数据库并设置字符集:
create database campus charset = utf8;
- 切换到校园数据库:
use campus;
- 创建学生表:
create table student(
id integer primary key auto_increment,
name varchar(20),
age integer,
gender integer,
birthday datetime
);
- 插入一条学生数据:
insert into student(name,age,gender,birthday) values ("张三",18,1,20180328);
- 继续插入更多数据:
insert into student(name,age,gender,birthday) values ("韩三平",60,1,20180328);
insert into student(name,age,gender,birthday) values ("李师师",25,0,20160328);
insert into student(name,age,gender,birthday) values ("王老五",40,1,20140328);
insert into student(name,age,gender,birthday) values ("杨六郎",25,0,20120328);
insert into student(name,age,gender,birthday) values ("洪七公",58,1,20100328);
insert into student(name,age,gender,birthday) values ("朱重八",42,1,20110328);
insert into student(name,age,gender,birthday) values ("马英九",55,0,20130328);
insert into student(name,age,gender,birthday) values ("李世石",45,1,20150328);
insert into student(name,age,gender,birthday) values ("萧十一郎",23,1,20170328);
@查询数据
- 查看所有表:
show tables;
- 查询学生表student中的所有记录:
select * from student;
@修改数据
- 修改王老五的姓名为隔壁老王:
update student set name = "隔壁老王" where name = "王老五";
@删除数据
- 删除张三的记录:
delete from student where name = "张三";
- 删除学生表:
drop table student;
- 删除校园数据库:
drop database campus;
@修改表字段
# 修改表字段
alter table student add level integer default 0; -- 增加字段
alter table student drop level;-- 删除字段
alter table student modify gender integer default 0; -- 修改字段属性
alter table student change gender sex integer default 0;-- 修改字段名称及属性