头歌——MySQL 基本操作

本文详细介绍了MySQL数据库的基础操作,包括数据定义、表结构修改、SQL查询(单表、连接、子查询、复杂查询),以及使用聚合函数等内容,适合初学者和进阶者学习。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

数据库1-MySQL数据定义与操作实战

MySQL数据库 - 初识MySQL

MySQL数据库 - 数据库和表的基本操作(一)

MySQL数据库 - 数据库和表的基本操作(二)

MySQL数据库 - 单表查询(一)

MySQL数据库 - 单表查询(二)

MySQL数据库 - 单表查询(三)

MySQL数据库 - 连接查询

MySQL数据库 - 子查询

MySQL数据库 - 复杂查询(一)

MySQL数据库 - 复杂查询(二)

MySQL数据库 - 使用聚合函数查询


数据库1-MySQL数据定义与操作实战

MySQL数据库 - 初识MySQL

第1关:创建数据库

mysql -uroot -p123123 -h127.0.0.1
create database MyDb;

第2关创建表

mysql -uroot -p123123 -h127.0.0.1
create database TestDb;
use TestDb;
create table t_emp (
    id int,
    name varchar(32),
    deptId int,
    salary float
);

第3关:使用主键约束

mysql -uroot -p123123 -h127.0.0.1
create database MyDb;
use MyDb;
create table t_user1(
    userId INT PRIMARY KEY,
    name VARCHAR(32),
    password VARCHAR(11),
    phone VARCHAR(11),
    email VARCHAR(32)
);
create table t_user2(
    name VARCHAR(32),
    phone VARCHAR(11),
    email VARCHAR(32),
    PRIMARY KEY(name,phone)
);

第4关:外键约束

mysql -uroot -p123123 -h127.0.0.1
create database MyDb;
use MyDb;

CREATE TABLE t_class
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) 
);
CREATE TABLE t_student
(
    id INT  PRIMARY KEY,
    name VARCHAR(22) ,
    classId int,
    CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id)
);

第5关:添加常用约束

mysql -uroot -p123123 -h127.0.0.1
CREATE DATABASE MyDb;
USE MyDb;
CREATE TABLE t_user
(
    id INT  PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32) NOT NULL UNIQUE,
    sex VARCHAR(4) DEFAULT '男'
)DEFAULT CHARSET=utf8;
MySQL数据库 - 数据库和表的基本操作(一)

第1关:查看表结构与修改表名

USE Company;

########## Begin ##########

########## modify the table name ##########
ALTER TABLE tb_emp 
RENAME jd_emp;


########## show tables in this database ##########
show tables;


########## describe the table ##########
describe jd_emp;


########## End ##########

第2关:修改字段名与字段数据类型

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## change the column name ##########
ALTER TABLE tb_emp c
hange Id prod_id int(11);


########## change the data type of column ##########
ALTER TABLE tb_emp 
MODIFY Name varchar(30);


########## End ##########

DESCRIBE tb_emp;

第3关:添加与删除字段

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## add the column ##########
ALTER TABLE tb_emp 
ADD Country varchar(20) AFTER Name; 
########## delete the column ##########
ALTER TABLE tb_emp DROP Salary;


########## End ##########

DESCRIBE tb_emp;

第4关:修改字段的排列位置

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## modify the column to top ##########
ALTER TABLE tb_emp 
MODIFY Name varchar(25) FIRST;


########## modify the column to the rear of another column ##########
ALTER TABLE tb_emp 
MODIFY DeptId int(11) AFTER Salary;


########## End ##########

DESCRIBE tb_emp;

第5关:删除表的外键约束

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## delete the foreign key ##########
ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;


########## End ##########
SHOW CREATE TABLE tb_emp \G;
MySQL数据库 - 数据库和表的基本操作(二)

第1关:插入数据

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## bundle insert the value #########
INSERT INTO tb_emp(Id,Name,DeptId,Salary) 
VALUES (1,"Nancy",301,2300.00),
(2,"Tod",303,5600.00),(3,"Carly",301,3200.00);

########## End ##########
SELECT * FROM tb_emp;
########## End ##########

第2关:更新数据

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## update the value ##########
UPDATE tb_emp
SET Name="Tracy",DeptId=302,Salary=4300.00
WHERE id=3;

########## End ##########

SELECT * FROM tb_emp;

########## End ##########

DESCRIBE tb_emp;

第3关:删除数据

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## delete the value ##########
DELETE FROM tb_emp
WHERE Salary>3000;

########## End ##########

SELECT * FROM tb_emp;

########## End ##########

DESCRIBE tb_emp;
MySQL数据库 - 单表查询(一)

第1关:基本查询语句

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary ##########
select Name,Salary from tb_emp;

########## retrieving all the table ##########
select * from tb_emp;

########## End ##########

第2关:带 IN 关键字的查询

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary with IN statement ##########
SELECT Name,Salary FROM tb_emp 
WHERE Id NOT IN (1);


########## End ##########

第3关:带 BETWEEN AND 的范围查询

USE Company;

#请在此处添加实现代码
########## Begin ##########

########## retrieving the Name and Salary with BETWEEN AND statement ##########
SELECT Name,Salary FROM tb_emp 
WHERE Salary BETWEEN 3000 AND 5000;


########## End ##########

MySQL数据库 - 单表查询(二)

第1关:带 LIKE 的字符匹配查询

USE Company;

######### Begin #########
SELECT Name,Salary FROM tb_emp 
WHERE Name LIKE "C%";

######### End #########


第2关:查询空值与去除重复结果

USE Company;

######### Begin #########
SELECT * FROM tb_emp WHERE DeptId IS NULL;

######### End #########

######### Begin #########
SELECT DISTINCT Name FROM tb_emp;

######### End #########


第3关:带 AND 与 OR 的多条件查询

USE Company;

######### Begin #########
SELECT * FROM tb_emp 
WHERE DeptId=301 AND Salary > 3000;

######### End #########

######### Begin #########
SELECT * FROM tb_emp 
WHERE DeptId=301 OR DeptId=303;

######### End #########
MySQL数据库 - 单表查询(三)

第1关:对查询结果进行排序

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
select * from tb_score 
where class_id = 1 
order by score desc;

########## End ##########

第2关:分组查询

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 对班级名称进行分组查询 ##########
SELECT * FROM tb_class 
GROUP BY class_id;

########## End ##########

第3关:使用 LIMIT 限制查询结果的数量

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询班级中第2名到第5名的学生信息 ##########
SELECT * FROM tb_score 
order by score desc 
LIMIT 1,4;

########## End ##########
MySQL数据库 - 连接查询

第1关:内连接查询

USE School;

########## 查询数据表中学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className from tb_student join tb_class on tb_class.id = tb_student.class_id; 



########## End ##########


第2关:外连接查询

USE School;

########## 使用左外连接查询所有学生姓名和对应的班级 ##########

#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class right join tb_student on 
tb_class.id=tb_student.class_id;



########## End ##########

########## 使用右外连接查询所有学生姓名和对应的班级 ##########
select tb_student.name as studentName,tb_class.name as className
from tb_class left join tb_student 
on tb_class.id=tb_student.class_id;
#请在此处添加实现代码
########## Begin ##########




########## End ##########

第3关:复合条件连接查询

USE School;

########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select s1.name as studentName,score,
s2.name as className from tb_student as s1,
tb_class as s2 where s1.class_id=s2.id and
s1.score>90 order by score desc;
########## End ##########
MySQL数据库 - 子查询

第1关:带比较运算符的子查询

USE Company;

#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp where age>(select avg(age) from tb_emp);


########## End ##########

第2关:关键字子查询

USE Company;
#请在此处添加实现代码
########## Begin ##########

#1.使用 ALL 关键字进行查询
SELECT position,salary FROM tb_salary WHERE salary > 
ANY(SELECT max(salary) FROM tb_salary where position="java");
#2.使用 ANY 关键字进行查询
SELECT position,salary FROM tb_salary WHERE salary > 
ANY(SELECT min(salary) from tb_salary where position="java");
#3.使用 IN 关键字进行查询
select position,salary from tb_salary where position in("java");
########## End ##########
MySQL数据库 - 复杂查询(一)

第1关:交换工资

#请在此添加实现代码
########## Begin ##########
UPDATE tb_Salary
SET
sex = CASE sex WHEN "m"  THEN "f"
ELSE "m"
END;

########## End ##########

第2关:换座位

#请在此添加实现代码
########## Begin ##########
SELECT if(Id%2=0,Id-1,if(Id=5,Id,Id+1)) AS id,name
FROM tb_Seat ORDER BY Id;
########## End ##########

第3关:分数排名

#请在此添加实现代码
########## Begin ##########
select Score,(select count(distinct score) from score where score >=s.score) as Rank
from score as s order by Score desc;
select Score,(select count(*) from score as s2 where s2.score >s1.score)+1 as Rank
from score as s1 order by Rank;

########## End ##########

第4关:体育馆的人流量

#请在此添加实现代码
########## Begin ##########
select distinct a.* from gymnasium a,gymnasium b,gymnasium c
where a.visitors_flow>=100 and b.visitors_flow>=100
and c.visitors_flow>=100
and(
    (a.id = b.id-1 and b.id = c.id - 1)or
    (a.id = b.id-1 and a.id = c.id + 1)or
    (a.id = b.id+1 and b.id = c.id + 1)
)
order by a.id;

########## End ##########

第5关:统计总成绩

#请在此添加实现代码
########## Begin ##########
select t1.classname,t1.chinese,t2.maths
from(select c.classname classname,sum(s.chinese)
chinese from tb_class c,tb_score s where c.stuname=
s.name and s.chinese>=60 group by c.classname)t1,
(select c.classname classname,sum(s.maths)maths from tb_class c,tb_score s
where c.stuname=s.name and s.maths>=60 group by c.classname)t2
where t1.classname=t2.classname;

########## End ##########
MySQL数据库 - 复杂查询(二)

第1关:查询学生平均分


#请在此添加实现代码
########## Begin ##########
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2)as avg_score from student b 
inner join score a on b.s_id = a.s_id
GROUP BY b.s_id,b.s_name HAVING avg_score <60
union 
select a.s_id,a.s_name,0 as avg_score from student a 
where a.s_id not in (select distinct s_id from score);

########## End ##########

第2关:查询修课相同学生信息


#请在此添加实现代码
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########

第3关:查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
select a.*,count(b.s_score)+1 rank from score a left join score b 
on a.c_id = b.c_id and a.s_score <b.s_score
group by a.c_id,a.s_id
order by a.c_id,count(b.s_score);

########## End ##########

第4关:查询张老师课程成绩最高的学生信息

#请在此添加实现代码
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a 
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
where b.c_id = 
    (
    select c_id from course c,teacher d 
    where c.t_id=d.t_id and d.t_name="张三"
    )
    and b.s_score in (select MAX(s_score)from score where c_id="02");

########## End ##########

第5关:查询两门课程不及格同学信息

#请在此添加实现代码
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))
avg_score from student a 
inner join score b on a.s_id = b.s_id
where a.s_id in(
    select s_id from score where s_score<60 
    GROUP BY s_id 
    having count(*)>=2
)
GROUP BY a.s_id,a.s_name;

########## End ##########
MySQL数据库 - 使用聚合函数查询

第1关:COUNT( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询该表中一共有多少条数据 ##########
select count(*) from tb_class;

########## 查询此表中367班有多少位学生 ##########
select classid,count(*) from tb_class 
where classid=367;

########## End ##########

第2关:SUM( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询所有学生总分数 ##########
select sum(score) from tb_class;

########## 查询学生语文科目的总分数 ##########
select course,sum(score) from tb_class 
where course="语文";

########## End ##########

第3关:AVG( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询学生语文科目的平均分数 ##########
select course,avg(score)from tb_class 
where course="语文";

########## 查询学生英语科目的平均分数 ##########
select course,avg(score) from tb_class 
where course="英语";

########## End ##########

第4关:MAX( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最高分数 ##########
select course,max(score) from tb_class 
where course="语文";

########## 查询英语课程中的最高分数 ##########
select course,max(score) from tb_class 
where course="英语";

########## End ##########

第5关:MIN( )函数

USE School;

#请在此处添加实现代码
########## Begin ##########

########## 查询语文课程中的最低分数 ##########
select course,min(score) from tb_class 
where course="语文";

########## 查询英语课程中的最低分数 ##########
select course,min(score) from tb_class 
where course="英语";

########## End ##########

查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用,也是最重要的操作。用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。在这一章中将讲解的内容包括。 1、查询语句的基本语法 2、在单表上查询数据 3、使用聚合函数查询数据 4、多表上联合查询 5、子查询 6、合并查询结果 7、为表和字段取别名 8、使用正则表达式查询 什么是查询? 怎么查的? 数据的准备如下: [sql] view plain copy create table STUDENT( STU_ID int primary KEY, STU_NAME char(10) not null, STU_AGE smallint unsigned not null, STU_SEX char(2) not null ); insert into STUDENT values(2001,'小王',13,'男'); insert into STUDENT values(2002,'明明',12,'男'); insert into STUDENT values(2003,'红红',14,'女'); insert into STUDENT values(2004,'小花',13,'女'); insert into STUDENT values(2005,'天儿',15,'男'); insert into STUDENT values(2006,'阿猎',13,'女'); insert into STUDENT values(2007,'阿猫',16,'男'); insert into STUDENT values(2008,'阿狗',17,'男'); insert into STUDENT values(2009,'黑子',14,'男'); insert into STUDENT values(2010,'小玉',13,'女'); insert into STUDENT values(2011,'',13,'女'); insert into STUDENT values(2012,'冰冰',14,'女'); insert into STUDENT values(2013,'美丽',13,'女'); insert into STUDENT values(2014,'神乐',12,'男'); insert into STUDENT values(2015,'天五',13,'男'); insert into STUDENT values(2016,'小三',11,'男'); insert into STUDENT values(2017,'阿张',13,'男'); insert into STUDENT values(2018,'阿杰',13,'男'); insert into STUDENT values(2019,'阿宝',13,'女'); insert into STUDENT values(2020,'大王',14,'男'); 然后这是学生成绩表,其中定义了外键约束 [sql] view plain copy create table GRADE( STU_ID INT NOT NULL, STU_SCORE INT, foreign key(STU_ID) references STUDENT(STU_ID) ); insert into GRADE values(2001,90); insert into GRADE values(2002,89); insert into GRADE values(2003,67); insert into GRADE values(2004,78); insert into GRADE values(2005,89); insert into GRADE values(2006,78); insert into GRADE values(2007,99); insert into GRADE values(2008,87); insert into GRADE values(2009,70); insert into GRADE values(2010,71); insert into GRADE values(2011,56); insert into GRADE values(2012,85); insert into GRADE values(2013,65); insert into GRADE values(2014,66); insert into GRADE values(2015,77); insert into GRADE values(2016,79); insert into GRADE values(2017,82); insert into GRADE values(2018,88); insert into GRADE values(2019,NULL); insert into GRADE values(2020,NULL); 一、查询语句的基本语法 查询数据是指从数据库中的数据表或视图中获取所需要的数据,在mysql中,可以使用SELECT语句来查询数据。根据查询条件的不同,数据库系统会找到不同的数据。 SELECT语句的基本语法格式如下: [sql] view plain copy SELECT 属性列表 FROM 表名或视图列表 [WHERE 条件表达式1] [GROUP BY 属性名1 [HAVING 条件表达式2]] [ORDER BY 属性名2 [ASC|DESC]] 属性列表:表示需要查询的字段名。 表名或视图列表:表示即将进行数据查询的数据表或者视图,表或视图可以有多个。 条件表达式1:设置查询的条件。 属性名1:表示按该字段中的数据进行分组。 条件表达式2:表示满足该表达式的数据才能输出。 属性2:表示按该字段中的数据进行排序,排序方式由ASC或DESC参数指定。 ASC:表示按升序的顺序进行排序。即表示值按照从小到大的顺序排列。这是默认参数。 DESC:表示按降序的顺序进行排序。即表示值按照从大到小的顺序排列。 如果有WHERE子句,就按照“条件表达式1”指定的条件进行查询;如果没有WHERE子句,就查询所有记录。 如果有GROUP BY子句,就按照“属性名1”指定的字段进行分组;如果GROUP BY子句后面带着HAVING关键字,那么只有满足“条件表达式2”中指定的条件的记录才能够输出。GROUP BY子句通常和COUNT()、SUM()等聚合函数一起使用。 如果有ORDER BY子句,就按照“属性名2”指定的字段进行排序。排序方式由ASC或DESC参数指定。默认的排序方式为ASC。 二、在单表上查询数据 2.1、查询所有字段 [sql] view plain copy select * from STUDENT; 2.2、按条件查询 (1) 比较运算符 > , < ,= , != (),>= , 13; in(v1,v2..vn) ,符合v1,v2,,,vn才能被查出 IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中,则满足查询条件,该纪录将被查询出来。如果不在集合中,则不满足查询条件。其语法规则如下:[ NOT ] IN ( 元素1, 元素2, …, 元素n ) [sql] view plain copy select * from STUDENT where STU_AGE in(11,12); between v1 and v2 在v1至v2之间(包含v1,v2) BETWEEN AND关键字可以判读某个字段的值是否在指定的范围内。如果字段的值在指定范围内,则满足查询条件,该纪录将被查询出来。如果不在指定范围内,则不满足查询条件。其语法规则如下: [ NOT ] BETWEEN 取值1 AND 取值2 [sql] view plain copy select * from STUDENT where STU_AGE between 13 and 15; (2)逻辑运算符 not ( ! ) 逻辑非 [sql] view plain copy select * from STUDENT where STU_AGE NOT IN(13,14,16); or ( || ) 逻辑或 OR关键字也可以用来联合多个条件进行查询,但是与AND关键字不同。使用OR关键字时,只要满足这几个查询条件的其中一个,这样的记录将会被查询出来。如果不满足这些查询条件中的任何一个,这样的记录将被排除掉。OR关键字的语法规则如下: 条件表达式1 OR 条件表达式2 [ …OR 条件表达式n ] 其中,OR可以用来连接两个条件表达式。而且,可以同时使用多个OR关键字,这样可以连接更多的条件表达式。 [sql] view plain copy select * from STUDENT where STU_ID2015; and ( && ) 逻辑与 AND关键字可以用来联合多个条件进行查询。使用AND关键字时,只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个,这样的记录将被排除掉。AND关键字的语法规则如下: 条件表达式1 AND 条件表达式2 [ … AND 条件表达式n ] 其中,AND可以连接两个条件表达式。而且,可以同时使用多个AND关键字,这样可以连接更多的条件表达式。 (3)模糊查询 like 像 LIKE关键字可以匹配字符串是否相等。如果字段的值与指定的字符串相匹配,则满足查询条件,该纪录将被查询出来。如果与指定的字符串不匹配,则不满足查询条件。其语法规则如下:[ NOT ] LIKE '字符串' “NOT”可选参数,加上 NOT表示与指定的字符串不匹配时满足条件;“字符串”表示指定用来匹配的字符串,该字符串必须加单引号或双引号。 通配符: % 任意字符 [sql] view plain copy select * from STUDENT where STU_NAME LIKE '%王'; 表示匹配任何以王结尾的 [sql] view plain copy select * from STUDENT where STU_NAME LIKE '阿%'; 表示匹配任何以阿开的 _ 单个字符 比如说插入 [sql] view plain copy insert into STUDENT values(2021,'天下无镜',14,'男'); 然后 [sql] view plain copy select * from STUDENT where STU_NAME LIKE '_下_'; 查询的结果为空 但是如果下后面加两个_符号 [sql] view plain copy select * from STUDENT where STU_NAME LIKE '_下__'; 查询结果不为空 “字符串”参数的值可以是一个完整的字符串,也可以是包含百分号(%)或者下划线(_)的通配字符。二者有很大区别 “%”可以代表任意长度的字符串,长度可以为0; “_”只能表示单个字符。 如果要匹配姓张且名字只有两个字的人的记录,“张”字后面必须要有两个“_”符号。因为一个汉字是两个字符,而一个“_”符号只能代表一个字符。 (4)空值查询 IS NULL关键字可以用来判断字段的值是否为空值(NULL)。如果字段的值是空值,则满足查询条件,该记录将被查询出来。如果字段的值不是空值,则不满足查询条件。其语法规则如下: IS [ NOT ] NULL 其中,“NOT”是可选参数,加上NOT表示字段不是空值时满足条件。 IS NULL是一个整体,不能将IS换成”=”. 三、使用聚合函数查询数据 3.1、group by 分组 如下: [sql] view plain copy select * from STUDENT group by STU_SEX; 不加条件,那么就只取每个分组的第一条。 如果想看分组的内容,可以加groub_concat [sql] view plain copy select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX; 3.2、一般情况下group需与统计函数(聚合函数)一起使用才有意义 先准备一些数据: [sql] view plain copy create table EMPLOYEES( EMP_NAME CHAR(10) NOT NULL, EMP_SALARY INT unsigned NOT NULL, EMP_DEP CHAR(10) NOT NULL ); insert into EMPLOYEES values('小王',5000,'销售部'); insert into EMPLOYEES values('阿小王',6000,'销售部'); insert into EMPLOYEES values('工是不',7000,'销售部'); insert into EMPLOYEES values('人人乐',3000,'资源部'); insert into EMPLOYEES values('满大',4000,'资源部'); insert into EMPLOYEES values('天生一家',5500,'资源部'); insert into EMPLOYEES values('小花',14500,'资源部'); insert into EMPLOYEES values('大玉',15000,'研发部'); insert into EMPLOYEES values('条条',12000,'研发部'); insert into EMPLOYEES values('笨笨',13000,'研发部'); insert into EMPLOYEES values('我是天才',15000,'研发部'); insert into EMPLOYEES values('无语了',6000,'审计部'); insert into EMPLOYEES values('什么人',5000,'审计部'); insert into EMPLOYEES values('不知道',4000,'审计部'); mysql中的五种统计函数: (1)max:求最大值 求每个部门的最高工资: [sql] view plain copy select EMP_NAME,EMP_DEP,max(EMP_SALARY) from EMPLOYEES group by EMP_DEP; (2)min:求最小值 求每个部门的最仰工资: [sql] view plain copy select EMP_NAME,EMP_DEP,min(EMP_SALARY) from EMPLOYEES group by EMP_DEP; (3)sum:求总数和 求每个部门的工资总和: [sql] view plain copy select EMP_DEP,sum(EMP_SALARY) from EMPLOYEES group by EMP_DEP (4)avg:求平均值 求每个部门的工资平均值 [sql] view plain copy select EMP_DEP,avg(EMP_SALARY) from EMPLOYEES group by EMP_DEP; (5)count:求总行数 求每个部门工资大于一定金额的人数 [sql] view plain copy select EMP_DEP,count(*) from EMPLOYEES where EMP_SALARY>=500 group by EMP_DEP; 3.3、带条件的groub by 字段 having,利用HAVING语句过滤分组数据 having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。 having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常,你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值,就如你在SELECT语句中做的那样。 [sql] view plain copy select EMP_DEP,avg(EMP_SALARY),group_concat(EMP_NAME)from EMPLOYEES group by EMP_DEP HAVING avg(EMP_SALARY) >=6000; 查找平均工资大于6000的部门,并把部门里的人全部列出来 四、多表上联合查询 多表上联合查询分为内连接查询和外连接查询 (1)隐式内连接查询 [sql] view plain copy select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT,GRADE WHERE STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90; 查找大于90分的学生信息: (2)显式内连接查询 [sql] view plain copy select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT inner join GRADE on STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90; 用法:select .... from 表1 inner join 表2 on 条件表达式 (3)外连接查询 left join.左连接查询。 用法 :select .... from 表1 left join 表2 on 条件表达式 意思是表1查出来的数据不能为null,但是其对应表2的数据可以为null [sql] view plain copy select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT left join GRADE on STUDENT.STU_ID=GRADE.STU_ID; right join就是相反的了,用法相同 用left join的时候,left join操作符左侧表里的信息都会被查询出来,右侧表里没有的记录会填空(NULL).right join亦然;inner join的时候则只有条件合适的才会显示出来 full join() 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据 值。 仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或 视图的所有行,只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外 部联接中两个表的所有行都将返回。 五、子查询 以一个查询select的结果作为另一个查询的条件 语法:select * from 表1 wher 条件1(select ..from 表2 where 条件2) 1、与In结合 [sql] view plain copy select * from STUDENT where STU_ID IN(select STU_ID from GRADE where STU_SCORE>85); 查找大于85分的学生信息 2、与EXISTS结合 EXISTS和NOT EXISTS操作符只测试某个子查询是否返回了数据行。如果是,EXISTS将是true,NOT EXISTS将是false。 [sql] view plain copy select * from STUDENT where EXISTS (select STU_ID from GRADE where STU_SCORE>=100); 如果有学生成绩大于100,才查询所有的学生信息 3、ALL、ANY和SOME子查询 any和all的操作符常见用法是结合一个相对比较操作符对一个数据列子查询的结果进行测试。它们测试比较值是否与子查询所返回的全部或一部分值匹配。比方说,如果比较值小于或等于子查询所返回的每一个值,<=all将是true,只要比较值小于或等于子查询所返回的任何一个值,<=any将是true。some是any的一个同义词。 [sql] view plain copy select STU_ID from GRADE where STU_SCORE = any (select STU_ID from GRADE where STU_SCORE 80 order by STU_SCORE; 默认是按升序的, 也可以这么写 [sql] view plain copy select * from GRADE where STU_SCORE >80 order by STU_SCORE ASC; 结果如下: 如果想换成降序的: [sql] view plain copy select * from GRADE where STU_SCORE >80 order by STU_SCORE desc; 7.2、limit limit [offset,] N offset 偏移量,可选,不写则相当于limit 0,N N 取出条目 取分数最高的前5条 [sql] view plain copy select * from GRADE order by STU_SCORE desc limit 5; 取分数最低的前5条 [sql] view plain copy select * from GRADE order by STU_SCORE asc limit 5; 取分数排名在10-15之间的5条 [sql] view plain copy select * from GRADE order by STU_SCORE desc limit 10,5 八、为表和字段取别名 使用AS来命名列 [sql] view plain copy select STU_ID as '学号',STU_SCORE as '分数' from GRADE; 当表的名称特别长时,在查询中直接使用表名很不方便。这时可以为表取一个别名。用这个别名来代替表的名称。 MySQL中为表取别名的基本形式如下: 表名 表的别名 [sql] view plain copy select S.STU_ID,S.STU_NAME,S.STU_AGE,S.STU_SEX,G.STU_SCORE from STUDENT S,GRADE G WHERE S.STU_ID=G.STU_ID AND G.STU_SCORE >=90; 九、使用正则表达式查询 正则表达式是用某种模式去匹配一类字符串的一个方式。例如,使用正则表达式可以查询出包含A、B、C其中任一字母的字符串。正则表达式的查询能力比通配字符的查询能力更强大,而且更加的灵活。正则表达式可以应用于非常复杂查询。 MySQL中,使用REGEXP关键字来匹配查询正则表达式。其基本形式如下: 属性名 REGEXP '匹配方式' 在使用前先插入一些数据: [sql] view plain copy insert into STUDENT values(2022,'12wef',13,'男'); insert into STUDENT values(2023,'faf_23',13,'男'); insert into STUDENT values(2024,'fafa',13,'女'); insert into STUDENT values(2025,'ooop',14,'男'); insert into STUDENT values(2026,'23oop',14,'男'); insert into STUDENT values(2027,'woop89',14,'男'); insert into STUDENT values(2028,'abcdd',11,'男'); (1)使用字符“^”可以匹配以特定字符或字符串开的记录。 查询所有以阿的 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '^阿'; 以数字开 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '^[0-9]'; (2)使用字符“$”可以匹配以特定字符或字符串结尾的记录 以数字结尾 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '[0-9]$'; (3)用正则表达式来查询时,可以用“.”来替代字符串中的任意一个字符。 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '^w....[0-9]$'; 以w开,以数字结束,中间有4个 (4)使用方括号([])可以将需要查询字符组成一个字符集。只要记录中包含方括号中的任意字符,该记录将会被查询出来。 例如,通过“[abc]”可以查询包含a、b、c这三个字母中任何一个的记录。 使用方括号可以指定集合的区间。 “[a-z]”表示从a-z的所有字母; “[0-9]”表示从0-9的所有数字; “[a-z0-9]”表示包含所有的小写字母和数字。 “[a-zA-Z]”表示匹配所有字母。 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '[0-9a-z]'; 查询所有包含有数字和小写字母的 使用“[^字符集合]”可以匹配指定字符以外的字符 (5){}表示出现的次数 正则表达式中,“字符串{M}”表示字符串连续出现M次;“字符串{M,N}”表示字符串联连续出现至少M次,最多N次。例如,“ab{2}”表示字符串“ab”连续出现两次。“ab{2,4}”表示字符串“ab”连续出现至少两次,最多四次。 o出现2次 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP 'o{2}'; (6)+表示到少出现一次 fa至少出现一次 [sql] view plain copy select * from STUDENT where STU_NAME REGEXP '(fa)+'; 注意: 正则表达式可以匹配字符串。当表中的记录包含这个字符串时,就可以将该记录查询出来。如果指定多个字符串时,需要用符号“|”隔开。只要匹配这些字符串中的任意一个即可。每个字符串与”|”之间不能有空格。因为,查询过程中,数据库系统会将空格也当作一个字符。这样就查询不出想要的结果。 正则表达式中,“*”和“+”都可以匹配多个该符号之前的字符。但是,“+”至少表示一个字符,而“*”可以表示零个字符。
### SQL 完整性约束定义方法 #### 实体完整性 实体完整性主要通过主键来实现,确保表中的每一行都有唯一的标识符。可以通过 `PRIMARY KEY` 关键字定义主键[^2]。 ```sql CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), department_id INT ); ``` 如果需要在已有的表上添加主键约束,则可以使用 `ALTER TABLE` 语句: ```sql ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (emp_id); ``` --- #### 参照完整性 参照完整性用于维护两个表之间的关系,通常通过外键约束实现。外键确保子表中的某个字段值必须存在于父表的相关字段中。 ```sql CREATE TABLE department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50), department_id INT, FOREIGN KEY (department_id) REFERENCES department(dept_id) ON DELETE CASCADE -- 当删除部门时自动删除关联员工记录 ); ``` 对于已有表的情况,也可以通过 `ALTER TABLE` 添加外键约束: ```sql ALTER TABLE employee ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department(dept_id); ``` --- #### 用户自定义完整性 用户可以根据业务需求定义额外的约束条件,常见的有非空约束 (`NOT NULL`) 和唯一性约束 (`UNIQUE`)。 - **非空约束**:指定某列不允许为空值。 ```sql CREATE TABLE student ( stu_id INT NOT NULL, stu_name VARCHAR(50) NOT NULL, age INT ); ``` - **唯一性约束**:确保某一列或多列组合的值在整个表中是唯一的。 ```sql CREATE TABLE user_account ( user_id INT UNIQUE, username VARCHAR(50) UNIQUE, password_hash VARCHAR(100) ); ``` 还可以为多个字段设置联合唯一性约束: ```sql CREATE TABLE product ( category_id INT, product_code VARCHAR(20), price DECIMAL(10, 2), UNIQUE (category_id, product_code) ); ``` --- #### 检查约束 检查约束允许开发者定义特定的逻辑规则以验证数据的有效性。例如,限定年龄范围或价格不得低于零[^3]。 ```sql CREATE TABLE person ( id INT PRIMARY KEY, name VARCHAR(50), age INT CHECK (age >= 18 AND age <= 100), salary DECIMAL(10, 2) CHECK (salary > 0) ); ``` 同样支持修改现有表并添加检查约束: ```sql ALTER TABLE person ADD CONSTRAINT chk_salary_positive CHECK (salary > 0); ``` --- #### 默认值约束 默认值约束可以在插入新记录时提供缺省值,从而减少手动输入的工作量。 ```sql CREATE TABLE order_details ( order_id INT PRIMARY KEY, customer_id INT, status VARCHAR(20) DEFAULT 'Pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` --- #### 删除约束 当不再需要某些约束时,可以将其移除。以下是删除不同类型的约束示例: - 删除主键: ```sql ALTER TABLE employee DROP CONSTRAINT pk_employee; ``` - 删除外键: ```sql ALTER TABLE employee DROP CONSTRAINT fk_department; ``` - 删除检查约束: ```sql ALTER TABLE person DROP CONSTRAINT chk_salary_positive; ``` --- #### 处理完整性约束违反异常 当操作违反了完整性约束时,可能会抛出类似于 `java.sql.SQLIntegrityConstraintViolationException` 的异常[^1]。为了防止此类问题发生,在设计数据库结构时应充分考虑实际场景的需求,并合理配置约束条件。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

是dream

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值