本文共 4631 字，大约阅读时间需要 15 分钟。

数据库操作指南

数据库管理

create database test1;

2. 删除数据库:

drop database test1;

3. 查看可用数据库:

show databases;

4. 切换到指定数据库:

use test1;

数据库表操作

创建表

-- 学生表
create table `Student` (
  `s_id` VARCHAR(20),
  `s_name` VARCHAR(20) NOT NULL DEFAULT '',
  `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
  `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
  primary key (`s_id`)
);
-- 课程表
create table `Course` (
  `c_id` VARCHAR(20),
  `c_name` VARCHAR(20) NOT NULL DEFAULT '',
  `t_id` VARCHAR(20) NOT NULL,
  primary key (`c_id`)
);
-- 教师表
create table `Teacher` (
  `t_id` VARCHAR(20),
  `t_name` VARCHAR(20) NOT NULL DEFAULT '',
  primary key (`t_id`)
);
-- 成绩表
create table `Score` (
  `s_id` VARCHAR(20),
  `c_id` VARCHAR(20),
  `s_score` INT(3),
  primary key (`s_id`, `c_id`)
);

插入数据

-- 学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

数据查询

基础查询

-- 查询指定字段
select 中文名, 职业, 城市 from [data$];

全部字段查询

select * from [data$];

数据范围查询

select * from [data$A1:E4];

去重查询

select distinct 中文 from [data$];
select distinct * from [data$];

高级查询

条件查询

select * from [data$] where 类型=”笔记本”;
select * from [data$] where 单价>6000;
select * from [data$] where 销售日期>'#2018/1/9#';
select * from [data$] where 北京+上海+南京>20;

字段操作

select *, 北京+上海+南京 as 销量 from [data$];

Null 操作

select * from [data$] where 北京 is not null or 上海 is not null and 成都 is null;

IN 操作

select * from [data$] where 品牌 in(“IBM”,”联想”) and 类型 in(“台式”,”笔记本”);

BETWEEN 操作

select * from [data$] where 销售日期 between #2020/1/1# and #2020/12/31#;

LIKE 操作

select * from [data$] where 姓名 like “李%”;
select * from [data$] where 姓名 not like “李%”;
select * from [data$] where 姓名 like “%梅”;
select * from [data$] where 姓名 like “%春%”;
select * from [data$] where 姓名 like “李_”;
select * from [data$] where 英文名 like “[a-cw-z]%”;
select * from [data$] where 出生年月 like “198[0-2]%”;
select * from [data$] where 姓名 like “[张李陈王]%”;
select * from [data$] where 身份证号 like “[%13579]”;

连字符处理

select * from [data$] where 月份&“1月份和尚头”;

嵌套查询

select * from [data$] where 姓名 like (select * from [基础讲解$J5:J6]);
select * from (select *, 北京+上海+成都 from [data2$]) where expr1000 between 20 and 30;

排序

select * from [data$] order by 总分 asc;
select * from [data$] order by 总分 desc;
select top 10 * from [data$] order by 总分 desc;

分组

select 品名, 型号 from [data$] group by 品名, 型号;
select distinct 品名, 型号 from [data$];

重命名

select t1.品牌 from [data$] as t1;

多表合并

select * from [1月份$] union all select * from [2月份$] union all select * from [3月份$];
select * from [1月份$] union select * from [2月份$] union select * from [3月份$];

多表链接

select * from [产品资料$] as t1,[销量表$] as t2 where t1.编号=t2.编号;
select t1.工号, 姓名, 城市, 工资 from [员工表$] as t1,[工资表$] as t2 where t1.工号=t2.工号;
select t3.工号, 姓名, 工资, 分红比例 from [员工表$] as t1,[工资表$] as t2,[分红表$] as t3 where t1.工号=t2.工号 and t2.工号=t3.工号;

###_left_right_选择函数

select 员工编号, left(姓名,1) as 姓氏 from [data$];
select 编号, right("000"&编号,4) as 修正编号 from [data$];

IIF函数

select *, iif(month(销售日期)>6,"下半年","上半年") as 2012年 from [电脑销售表$];

DATEDIFF和DATEADD

select 员工姓名, 入职日期, datediff("yyyy",入职日期,date()) as 工龄 from [data$];
select dateadd("m",1,now())-now() as 本月余下的天数;

SWITCH函数

select *, switch(成绩>*/
#####

转载地址：http://ecczk.baihongyu.com/