参考学习
https://www.lintcode.com/course/list/?tagId=864&type=1&chargeStatus=-1
以下是我的学习笔记。
增删改查
-- 职工表
create table employee
(
id int auto_increment
primary key,
employee_id int null comment '职工号
',
employee_name varchar(50) null comment '职工姓名',
gender varchar(20) null comment '职工性别',
phone varchar(20) null comment '职工电话',
card_Id varchar(50) null comment '身份证',
birthday date null comment '生日',
enter_time date null comment '职工入职时间',
dep_name varchar(20) null comment '部门名称',
dep_id int null comment '部门编号'
);
-- 部门表
create table department(
id int auto_increment primary key ,
dep_id int null comment '部门编号',
dep_name varchar(20) null comment '部门名称'
);
select
-- 单列
select `employee_name` from employee;
-- 多列
select `employee_name` , `enter_time`from employee;
-- distinct用法--不重复查询指定列内容
select distinct dep_id from employee;
select dep_id from employee;
-- select where筛选查询
select * from employee where employee_id != 102;
查询时间时需要将时间使用单引号’包裹,反引号``用于包裹字段名,防止与mysql关键字冲突
-- select between
select employee_name,dep_name,enter_time from employee where enter_time between '2017-08-04' and '2023-08-01';
select * from employee where employee_id between 100 and 103;
insert
-- insert插入
-- 插入整条数据
insert into department values (1,7,'交付');
-- id本应该是自增,这种方法可以直接指定id值
insert into department values (3,1,'苍盐海');
-- 向指定字段插入数据,这种方法可以不指定主键id,使其在插入成功时自增
insert into employee (employee_id,employee_name,enter_time,dep_id,dep_name) values (207,'东方青苍','2022-08-06',1,'苍盐海');
update
-- update更新
update employee
set gender = '男',
phone = '12589647781',
card_Id = '369851100062548823',
birthday = '1500-01-08'
where employee_name = '东方青苍';
注意!update语句中的where子句一定要加上!!!否则整张表的字段都会被更新。
delete
-- delete删除
delete from employee where employee_name = '张三';
-- employee_name不是唯一标识符,如果有重名的会全部删除,注意一下。可以使用id、employee_id这种具有唯一性的进行删除操作
delete from employee where employee_name = '王五';