MySQL操作数据-增删改查

MySQL数据操作-增删改查

1、操作表及字段

书写规范（注意点）：

• 所有符号全部使用英文
• 表的名称、字段尽量使用 `` （反引号）包含起来
• 注释使用 – 或者 /* */
• 个人建议 sql关键字使用小写，方便阅读

1.1、创建表（重点）

语法：

create table [if not exists] `表名`(
    `字段名` 列类型 [属性] [索引] [注释] , 
    	......	
    `字段名` 列类型 [属性] [索引] [注释] 
)[表类型][表的字符集设置][表的注释]

实列：

-- 注意：每段最后需要逗号","，逗号需要是英文字符
-- 创建一个名为t_student的表
create table if not exists `t_student`(
		`id` int(4) not null auto_increment comment '学号',
		`name` varchar(30) not null default '匿名' comment '姓名',
		`pwd` varchar(20) not null default '123' comment '密码',
		`sex` varchar(2)  not null default '男' comment '性别',
		primary key (`id`)
)engine=innodb default charset=utf8

1.2、数据表类型

	MYSIAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持，数据表锁定	支持
外键约束	不支持	支持
全文索引	支持	不支持，数据库5.6版本及以后支持
表空间大小	较小	较大，约为MYSIAM的2倍

• MYISAM 节约空间 ，速度较快

• INNODB 安全性高，事务处理，多表多用户操作

  show create table `表名` -- 显示该表创建语法
  show create database `数据库名`  -- 显示该数据库创建语法
  desc `表名` -- 显示该表结构
  

1.3、修改表

-- 修改表名   alter table 旧表名 rename as 新表名
alter table `t_student` rename as `t_stu`

1.4、表的字段操作

-- 添加表的字段   alter table 表名 add 字段名 列属性
alter table `t_stu` add `age` int(11)
-- 修改表的字段(重命名，修改约束)
alter table `t_stu` modify age varchar(11)  -- 修改约束
alter table `t_stu` change age  age1 int(1) -- 重命名并改约束  age旧字段名  age1 新字段名
-- 删除表的字段
alter table `t_stu` drop age    -- t_stu表名 age 一个字段名

1.5、删除表

drop table [if exists] teacher   --表存在就删除

所有的创建和删除操作尽量加上判断，以免报错

2、MySQL数据管理

2.1、外键（了解）

-- 添加外键
-- 语法： alter table 主表名 add constraint 约束名 foreign key(`主键中作为外键的列`) references `从表名`(`从表字段`)
alter table `t_test` 
add  constraint `FK_sid` foreign key(`sid`) references `t_stu`(`id`)

上述的操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰）

2.2、DML（数据库操作语言）(重点，全部记住)

2.2.1、添加

insert 命令

-- 插入语句（添加）        注：值要与字段一一对应
-- 语法：insert into 表名([字段1,字段2])  values('值1','值2')[,('值1','值2')]
insert into `student`(`id`,`name`,`age`)  values(1,'张三',19)

-- 如果主键自增我们可以设置主键值为null (如果不写表的字段，它会一一匹配)  
insert into `student` values(null,'张三',19)

-- 一次插入多行值
insert into `student`(`id`,`name`,`age`)  values(1,'张四',19),(2,'李军',19)

2.2.2、修改

update命令

-- 修改语句
-- 语法：update `表名` set `字段名`='值'[,`字段名`='值'] where [条件];  注意：要附带条件
-- 上方值可以是具体值，也可以是个函数
update `student` set `name`='dzh' where id=1;
-- 不指定条件，所有字段都会改变   注意：严禁这样操作，等于删库
update `student` set `name`='dzh2'
-- 修改多个属性,逗号隔开
update `student` set `name`='dzh',`age`=20 where id=1;

-- current_time 是一个函数,值可以为函数
update `student` set `name`=current_time where id=1;

where子句运算符，可用于增删改查

where操作符	含义
=	等于
<>或者!=	不等于
>和<	大于和小于
>=和<=	不小于和不大于
between…and…	在某个范围间， 列如 where id between 2 and 5
and	我或你 相当于&& 列如5>1 and 1>2 ==> false
or	我或你 相当于|| 列如5>1 or 1>2 ==> true

-- 在某个范围内修改数据
update `student` set `name`='zh_id2——5' where `id` between 2 and 5;
-- 通过多条件定位数据(假设所有age都为19)
update `student` set `name`='zh_id3' where `age`=19 and `id`=3;

2.2.3、删除

delete命令

-- 删除语句
-- 语法：delete from `表名` [where 条件]
-- 清空表 不建议使用,可参考下文truncate命令
delete from `student`
-- 删除指定数据
delete from `student` where `id`=1

truncate 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空表
-- 语法：truncate `表名`
truncate `student`

delete 和truncate 区别

	delete命令	truncate命令
删除数据及表结构	能	能
自增列计数器归零	不能	能
事务影响	影响	不影响

了解：delete删除问题，重启数据库，现象

• InnoDB 自增列会从1开始，（存在内存中，断电即失去）
• MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）

2.2.4、查询

注：查询属于DQL（数据库查询语言），具体可参考我的篇文章：DQL MySQL查询数据

select 命令

-- 查询student表所有数据
-- 语法：select 字段名 from 表名
select * from `student`