Linux-MYSQL数据库【语法结构】

• 数据库表操作
• 数据的增删改查操作
• 数据查询操作

本期目标
1.知道数据库表的创建、删除操作
2.掌握数据的增删改查
3.掌握数据的各种查询方法
数据库表操作

• 创建数据库表
• 删除数据库表

创建数据库表

语法格式：

create table 表名 (

字段名1 类型 约束,

字段名2 类型 约束,

…… …. ….

)

例：create  table  students  (name varchar(10))

删除数据库表

语法格式：

方法一： drop table 表名

方法二： drop  table  if  exists  表名

例：

drop  table  students

drop  table  if  exists  students

数据的增删改查操作

• 数据简单查询
• 数据增加
• 数据修改
• 数据删除

简单查询

语法格式：

select  *  from  表名;

例： select  *  from  students;

添加一行数据

说明：主键自增长，可以用0或null代替。

方式一: insert  into  表名  values(…)

例1: insert  into  students  values(0,  ‘亚瑟’,  22,  177.56)

方式二: inert  into  表名  (字段1,  字段2,  …) value(值1,  值2,  …)

例: inert  into  students  (name)  value  (‘老夫子’)

添加多行数据

方式一: 写多条insert语句，多条语句之间用英文分号分隔

例:

insert  into  students(name)  value  (‘老夫子2’);

insert  into  students(name)  value  (‘老夫子3’);

insert  into  students  values(0,’亚瑟2‘,23,167.56)

方式二: 通过一条insert语句插入多条数据，数据间用逗号分隔

格式一: insert  into  表名  values  (…),  (…)  …

例: insert  into  students  values(0,‘亚瑟3’,23,167.56),(0,‘亚瑟4’,23,167.56)

格式二: insert  into  表名(字段名1,…)  value(值1,…),(值1,…)…

例: insert  into  students(name)  value (‘老夫子5’),(‘老夫子6’)

修改数据

语法格式:

update  表名  set  字段名1=值1,字段名2=值2…  where 条件

例:修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20

update  students  set  name=‘狄仁杰’,age=20  where  id=5

删除数据-delete

语法格式:

delete  from  表名  where  条件

例:删除id为6的学生数据

delete  from  students  where  id=6

注意:此方法为物理删除，工作中大部分使用逻辑删除。

逻辑删除是指通过设定一个字段来标识当前记录已经删除。

is_delete字段来标识，0代表删除，1表示未删除。

添加字段:alter  table  表名  add  字段名  数据类型  约束

删除数据-其他方式

Truncate删除数据:

truncate  table  表名

例:删除学生表的所有数据(保留表结构)

truncate  table  students

Drop删除表:

格式三：drop  table  表名

例:删除学生表(包括所有数据和表结构)

drop  table  students

1. 查询表中所有数据(select)
2. 数据添加(insert into)
3. 数据修改(update)
4. 数据删除(delete)

数据查询操作

• 条件查询
• 排序
• 聚合函数
• 分组、分页
• 连接查询
• 自关联
• 子查询

数据准备-创建数据库表

drop table if exists students;

create table students (

studentNo varchar(10) primary key,

name varchar(10),

sex varchar(1),

hometown varchar(20),

age tinyint(4),

class varchar(10),

card varchar(20)

);

数据准备-插入数据

insert into students values

(‘001’, ‘王昭君’, ‘女’, ‘北京’, ’20’, ‘1班’, ‘340322199001247654’),

(‘002’, ‘诸葛亮’, ‘男’, ‘上海’, ’18’, ‘2班’, ‘340322199002242354’),

(‘003’, ‘张飞’, ‘男’, ‘南京’, ’24’, ‘3班’, ‘340322199003247654’),

(‘004’, ‘白起’, ‘男’, ‘安徽’, ’22’, ‘4班’, ‘340322199005247654’),

(‘005’, ‘大乔’, ‘女’, ‘天津’, ’19’, ‘3班’, ‘340322199004247654’),

(‘006’, ‘孙尚香’, ‘女’, ‘河北’, ’18’, ‘1班’, ‘340322199006247654’),

(‘007’, ‘百里玄策’, ‘男’, ‘山西’, ’20’, ‘2班’, ‘340322199007247654’),

(‘008’, ‘小乔’, ‘女’, ‘河南’, ’15’, ‘3班’, null),

(‘009’, ‘百里守约’, ‘男’, ‘湖南’, ’21’, ‘1班’, ”),

(‘010’, ‘妲己’, ‘女’, ‘广东’, ’26’, ‘2班’, ‘340322199607247654’),

(‘011’, ‘李白’, ‘男’, ‘北京’, ’30’, ‘4班’, ‘340322199005267754’),

(‘012’, ‘孙膑’, ‘男’, ‘新疆’, ’26’, ‘3班’, ‘340322199000297655’);

查询基本语法

查询部分字段数据：

select  字段1,字段2,…  from  表名

例:select  name,sex,age  from  students

起别名:

select  别名.字段1,别名.字段2,… from  表名  as  别名

select  字段1  as 别名1,字段2  as  别名2,…  from  表名

例:select  s.name,s.sex,s.age  from  students  as  s;

例:select  name  as  姓名,sex  as  性别,age  as  年龄  from  students;

去重:

select  distinct  字段1,…  from  表名

例:select  distinct  sex  from  students;

条件查询-语法格式

条件查询是根据一定的条件去查询数据的结果。

语法格式:

select  字段1,字段2…  from  表名  where  条件;

例: select  *  from  students  where  id=1;

说明: where支持多种运算符进行条件处理

比较运算

逻辑运算

模糊查询

范围查询

空判断

条件查询-比较运算符

例1：查询小乔的年龄

select  age  from  students  where  name=‘小乔’

例2：查询20岁以下的学生

select  *  from  students  where  age<20

例3：查询家乡不在北京的学生

select  *  from  students  where  hometown!=‘北京’

条件查询-模糊查询

关键字: like

% ：匹配任意多个字符

_ : 匹配一个任意字符

例1：查询姓孙的学生

select  *  from  students  where  name  like  ‘孙%’

例2：查询姓孙且名字是一个字的学生

select  *  from  students  where  name  like  ‘孙_’

例3：查询姓名以‘乔’结尾的学生

select  *  from  students  where  name  like  ‘%乔’

例4：查询姓名中包含‘白’的学生

select  *  from  students  where  name  like  ‘%白%’

条件查询-范围查询

l in表示在一个非连续的范围内

例:查询家乡是北京或上海或广东的学生

select  *  from  students  where  hometown  in(‘北京’,’上海’,’广东’)

between  …  and  …表示在一个连续的范围内

例：查询年龄为18至20的学生

select  *  from  students  where  age  between  18  and  20

条件查询-空判断

注意:Mysql中空表示null，与 ‘’ (空)是不一样的。

判断为空: is null

例：查询没有填写身份证的学生

select  *  from students where card is null

判断非空: is not null

例：查询填写了身份证的学生

select  *  from students  where  card  is  not  null

排序

l

语法格式:

select  *  from  表名  order  by  字段名1  asc|desc,  字段名2  asc|desc,…

说明:

将行数据按照字段1进行排序，如果某些字段1的值相同时，则按照字段2排序，以此类推

默认按照列值从小到大排列

asc从小到大排列，即升序

desc从大到小排序，即降序

排序

例1：查询所有学生信息，按年龄从小到大排序

select  *  from  students  order  by  age

例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序

select  *  from  students  order  by  age  desc,studentNo

聚合函数

例1：查询学生总数

select  count(*)  from  students;

例2：查询女生的最大年龄

select  max(age)  from  students  where sex=’女‘;

例：查询1班的最小年龄

select  min(age)  from  students;

示例：查询北京学生的年龄总和

select  sum(age)  from  students  where  hometown=‘北京’;

例：查询女生的平均年龄

select  avg(age)  from  students  where  sex=‘女’

分组查询

按照字段分组，此字段相同的数据会被放到一个组中

分组的目的是对每一组的数据进行统计(使用聚合函数)

语法格式:

select  字段1,字段2,聚合函数…  from  表名  group  by  字段1,字段2…

例1：查询各种性别的人数

select  sex,count(*)  from  students  group  by sex

例2：查询每个班级中各种性别的人数

select  class,sex,count(*)  from  students  group  by  class,sex

分组后的数据筛选

语法格式:

select  字段1,字段2,聚合…  from  表名  group  by  字段1,字段2,字段3...having 条件

说明: 关键字having后面的条件运算符与where的相同

例1：查询男生总人数

方案一:select  count(*)  from  students  where sex=‘男’

方案二:select  sex,count(*)  from  students  group  by  sex  having  sex=‘男’

having与where对比

where 是对 from 后面指定的表进行数据筛选，属于对原始数据的筛选。

having 是对 group  by 的结果进行筛选。

having 后面的条件中可以用聚合函数，where后面不可以。

分页-获取部分数据

使用场景: 用来获取一部分的数据或者用来分页

语法格式:

select  *  from  表名  limit  start,count

说明:

从start开始，获取count条数据

start索引从0开始

例1：查询前3行学生信息

select  *  from  students  limit  0,3

分页实现

Limit典型应用场景是分页查询：

select  *  from  students  limit  (n-1)*m,  m

说明: 

n表示显示第几页的数据

m表示每页显示多少条数据

注意：

(n-1)*m, m 是公式，并不是语法格式,不能直接写在SQL语句中

连接查询-常用方式

连接查询-数据准备1

创建课程表并插入数据

drop table if exists courses;

create table courses (

courseNo int(10) unsigned primary key auto_increment,

name varchar(10)

);

insert into courses values (‘1’, ‘数据库’), (‘2’, ‘qtp’), (‘3’, ‘linux’),

(‘4’, ‘系统测试’), (‘5’, ‘单元测试’), (‘6’, ‘测试过程’);

连接查询-数据准备2

创建成绩表并插入数据

drop table if exists scores;

create table scores (

id int(10) unsigned primary key auto_increment,

courseNo int(10),

studentno varchar(10),

score tinyint(4)

);

insert into scores values (‘1’, ‘1’, ‘001’, ’90’), (‘2’, ‘1’, ‘002’, ’75’),

(‘3’, ‘2’, ‘002’, ’98’),(‘4’, ‘3’, ‘001’, ’86’),(‘5’, ‘3’, ‘003’, ’80’),

(‘6’, ‘4’, ‘004’, ’79’),(‘7’, ‘5’, ‘005’, ’96’),(‘8’, ‘6’, ‘006’, ’80’);

连接查询-内连接

语法格式:

select  *  from  表1

inner  join  表2  on  表1.列=表2.列

另一种写法:

select  *  from  表1,表2  where  表1.列=表2.列

连接查询-内连接

例1：查询学生信息及学生的成绩

方式一: Select  *  from  students  stu  inner  join  scores  sc  on  stu.studentNo  = sc.studentNo

方式二:select  *  from  students  stu,  scores  sc  where  stu.studentNo  =  sc.studentNo

例2：查询课程信息及课程的成绩

select  *  from  courses  cs  inner  join  scores  sc  on  cs.courseNo  =  sc.courseNo

例3：查询王昭君的成绩，要求显示姓名、课程号、成绩

select  stu.name,  sc.courseNo,  sc.score  from  students  stu

inner  join  scores  sc  on  stu.studentNo  =  sc.studentNo  where  stu.name  =  ‘王昭君’

连接查询-左连接

语法格式:

select  *  from 表1

left  join  表2  on  表1.列=表2.列

例1:查询所有学生的成绩，包括没有成绩的学生

select  *  from  students  stu  left  join  scores  sc  on  stu.studentNo  =  sc.studentNo

例2:查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select  *  from  students  stu  left  join  scores  sc  on  stu.studentNo  =  sc.studentNo  left  join

courses  cs  on  cs.courseNo  =  sc.courseNo

连接查询-右连接

语法格式:

select  *  from 表1

right  join  表2  on  表1.列=表2.列

例1:查询所有学生的成绩，包括没有成绩的学生

select  *  from  scores  sc  right join  students  stu  on  stu.studentNo  =  sc.studentNo

例2:查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select  *  from  scores  sc  right join  courses  cs  on  cs.courseNo  =  sc.courseNo  right join students stu on stu.studentNo = sc.studentNo

自关联-数据准备

创建表:

drop table if exists areas;

create table areas(aid int primary key, atitle varchar(20),pid int);

插入数据:

insert into areas values (‘130000’, ‘河北省’, NULL), (‘130100’, ‘石家庄市’, ‘130000’),

(‘130400’, ‘邯郸市’, ‘130000’), (‘130600’, ‘保定市’, ‘130000’),(‘130700’, ‘张家口市’, ‘130000’),

(‘130800’, ‘承德市’, ‘130000’),(‘410000’, ‘河南省’, NULL), (‘410100’, ‘郑州市’, ‘410000’),

(‘410300’, ‘洛阳市’, ‘410000’),(‘410500’, ‘安阳市’, ‘410000’),(‘410700’, ‘新乡市’, ‘410000’),

(‘410800’, ‘焦作市’, ‘410000’),(‘410101’, ‘中原区’, ‘410100’),(‘410102’, ‘二七区’, ‘410100’),

(‘410301’, ‘洛龙区’, ‘410300’);

自关联-查询实现

自关联：inner join 关联同一个表，不同的字段。

自关联要用别名

例1：查询河南省所有的市

Select  *  from  areas  as  a1  inner join  areas  as  a2  on  a1.aid=a2.pid  where  a1.atitle=’河南省‘;

例2：查询郑州市的所有的区

Select  *  from  areas  as  a1  inner join  areas  as  a2  on  a1.aid=a2.pid  where  a1.atitle=’郑州市’;

例3：查询河南省的所有的市区

Select  *  from  areas  as  a1  inner join  areas  as  a2  on  a1.aid=a2.pid  inner join  areas  as  a3  on  a2.aid=a3.pid  where  a1.atitle=’河南省‘

子查询-简介

• 嵌入在其他查询语句中的select语句称为子查询
• 其他的查询语句称之为主查询。
• 子查询辅助主查询，要么充当条件，要么充当数据源。
• 子查询是一条完整的、可单独执行的select查询语句。

子查询-充当条件

例1：查询王昭君的成绩，要求显示成绩（标量子查询）

select  *  from  scores  where  studentNo  =  (select  studentNo  from  students  where  name  =  ‘王昭君’)

例2：查询18岁的学生的成绩，要求显示成绩（列子查询）

select  *  from  scores  where  studentNo  in  (select  studentNo  from  students  where age=18)

例3：查询和王昭君同班、同龄的学生信息（行子查询）

select  *  from  students  where  (class,age)=(select class,age  from  students  where name=’王昭君’)

子查询-充当数据源

例1：查询数据库和系统测试的课程成绩
Select  *  from  scores s  inner join  (select  *  from  courses  where  name  in  (‘数据库’,’系统测试’))  c  on  s.courseNo  =  c.courseNo

1. 条件查询(where  条件)
2. 排序(order by  字段名)
3. 聚合函数(count、max、min、sum、avg)
4. 分组(group by)、分页(limit)
5. 连接查询(inner join、left join、right join)
6. 自关联(inner join)
7. 子查询(充当条件或数据源)