Elora blog
sql基础
Elora Rinta!
  2024-04-04 14:35:35   2024-04-05 19:40:36    8.2k Words  37 Mins  

表

由行和列组成的二维表格

  • 行，又叫记录
  • 列，又叫字段

SQL语言（structured query language)

SQL语言主要分为:

  • DQL：数据查询语言，如select
  • DDL：数据定义语言，进行数据库、表的管理等，如create、drop
  • DML：数据操作语言，增删改查，如insert、uupdate、delete
  • TPL：事务处理语言，如begin transaction、commit、rollback

注释

  • 单行注释：– 注释内容
  • 多行注释：/* */

MySQL常用数据类型

  • 整形 int
  • 小整数tinyint
  • 小数decimal：如decimal(5,2)表示共存5位数，小数占2位，不能超过2位；整数占3位
  • 字符串varchar：如varchar(3)表示最多存3个 字符，一个中文或一个字母都占一个字符
  • 日期实际datetime

表、字段、记录

  • 表：数据库中存储数据的基本单位
    字段（列）–field 记录（行）–record

创建表 CREATE TABLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table 表名(
	字段名 数据类型;
	字段名 数据类型
	...
)
CREATE TABLE a(NAME VARCHAR(10)); -- 指定varchar长度为10
CREATE TABLE b(
	NAME VARCHAR(10),
	HEIGHT DECIMAL(5,2)   -- 数字五位，两位小数
);
CREATE TABLE c(
	id INT,
	NAME VARCHAR(20),
	age TINYINT UNSIGNED
);



INSERT插入记录

1
2
3
#值的顺序与字段顺序对应
insert into 表名 values (...);
INSERT INTO c VALUES(1,'张飞',30);

1
2
3
#部分字段设值，值的顺序与给出的字段顺序对应
#INSERT INTO 表名 (字段名,字段名) values (值,值)
INSERT INTO c (id,NAME) VALUES (2,'老房子'); 

1
2
3
4
#插入多条记录，insert语句用分号隔开
INSERT INTO c VALUES(6,'周瑜',24);
INSERT INTO c (NAME) VALUES ('诸葛亮');
INSERT INTO c (id,NAME) VALUES (7,'黄忠');

1
2
#一条insert语句插入多条记录
INSERT INTO c VALUES (10,'张三',10),(12,'王五',20),(11,'赵六',31);

1
2
#一条insert语句插入指定字段值
INSERT INTO c (id,NAME) VALUES (14,'hua'),(15,'yu'),(16,'rain');

SELECT 简单查询

1
SELECT * from 表名;#*表示全部的字段

1
2
3
4
5
#查询指定字段
SELECT id from c;#查询id字段
SELECT id FROM c;
SELECT id,age FROM c;
SELECT id,age,NAME FROM c;



UPDATE 修改数据

1
2
update 表名 set 字段1=值1,字段2=值2 where 条件；
UPDATE c SET age=50;

1
2
3
#设置条件修改
UPDATE c SET NAME='di',age=20 WHERE id =2;
UPDATE c SET NAME='zhangfei' WHERE NAME ='张飞';


1
2
#让id大于10的age都加一岁
UPDATE c SET age=age+1 WHERE id >10 ;

DELETE删除表的记录

1
2
3
delete from 表名 where 条件;
DELETE FROM c WHERE id=6;
DELETE FROM c WHERE NAME='诸葛亮';


TEUNCATE删除表的所有数据，保留表结构

1
truncate table 表名;

DELETE与TRUNCATE区别

  • 速度上，turncate>delete
  • 如果只想删除部分数据用delete，带上where子句
  • 如果想保留表而将所有数据删除，自增长字段恢复从1开始，用truncate,delete会从删除前的最大值开始增长

DROP TABLE删除表

1
2
3
4
5
6
drop table 表名;

DROP TABLE a;

-- 如果表a存在，就删除表a，如果不存在，就什么都不做
DROP TABLE if EXISTS a;

字段的约束

常用的约束

  • 主键（primary key）：值不能重复，auto_increment代表值自动增长；
  • 非空（not null）：此字段不参与空值；
  • 唯一（unique）：此字段的值不允许重复
  • 默认值（default）：当不填写此值时会使用默认值，如果填写时以填写为准

主键

创建带约束字段的表：

1
2
3
4
5
6
7
8
9
10
11
12
create  table 表名(
	字段名 数据类型 约束,
	字段名 数据类型 约束
	...
)
CREATE TABLE d(
	id INT UNSIGNED PRIMARY KEY auto_increment, 	
	name VARCHAR(10),
	age TINYINT UNSIGNED
);
INSERT INTO d (name,age) VALUES ('liu',12);
INSERT INTO d (name,age) VALUES ('wan',22);

1
2
3
INSERT INTO d (id,name,age) VALUES (6,'www',12);
INSERT INTO d (name,age) VALUES ('ll',14);
SELECT * FROM d;

1
2
3
4
-- 如果不指定字段，主键自增长的字段可以用占位符0或null
INSERT INTO d VALUES (0,'wl',55);
INSERT INTO d VALUES (null,'sssdw',123);
SELECT * FROM d;

非空

1
2
3
4
5
6
CREATE TABLE e(
	id INT UNSIGNED,
	name VARCHAR(10) not null,
	age INT UNSIGNED
);
INSERT INTO e VALUES (1,'lbw',38);

1
INSERT INTO e (id,age) VALUES (2,22);

唯一

此字段的值不允许重复

1
2
3
4
5
6
7
8
CREATE TABLE f(
	id INT UNSIGNED PRIMARY KEY auto_increment,
	name VARCHAR(10) UNIQUE,
	age INT UNSIGNED
);
INSERT INTO f VALUES(1,'ana',3);
INSERT INTO f VALUES(2,'lily',5);
SELECT * FROM f;

1
INSERT INTO f VALUES(3,'ana',5);

默认值

1
2
3
4
5
6
7
8
9
10
CREATE TABLE g(
	id INT UNSIGNED PRIMARY KEY auto_increment,
	name VARCHAR(10) UNIQUE,
	age INT UNSIGNED DEFAULT 30
);
INSERT INTO g VALUES(1,'zs',20);
INSERT INTO g (id,name) VALUES(2,'lily');
INSERT INTO g VALUES(3,'ana',0);
INSERT INTO g VALUES(NULL,'www',NULL);
INSERT INTO g VALUES(NULL,'w5',22);

1
2
3
4
5
INSERT INTO g VALUES(NULL,'w5',22);
INSERT INTO g VALUES(NULL,'w6',32);
SELECT * FROM g;
-- 因为约束数据没有录入，但是自增长的值会继续增长

条件查询

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
drop table if exists students;
/* 创建学生表students */ 
create table students (
	studentNo varchar(10) primary key, /*学号，主键，值不能重复*/ 
	name varchar(10), /*姓名*/
	sex varchar(1), /*性别*/
	hometown varchar(20), /*家乡*/
	age tinyint, /*年龄*/
	class varchar(10), /*班级*/ card varchar(20) /*身份证号码*/
);
/* 向学生表students插入数据 */
insert into students values
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'), 
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'), 
('004', '白起', '男', '安徽', '35', '4班', '340202198505177654'), 
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'), 
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'), 
('007', '百里玄策', '男', '山西', '39', '2班', '140102198107277654'), 
('008', '小乔', '女', '河南', '25', '3班', null),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'), 
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'), 
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655')

查询

1
2
SELECT * FROM students;

1
2
3
4
SELECT `NAME` FROM students;
SELECT name FROM students;
SELECT NAME FROM students;
-- mysql中大小写不敏感

别名

字段的别名：

1
2
3
SELECT card as 身份证,name as 姓名,sex as 性别 FROM students;
-- as可以省略
SELECT card  身份证,name 姓名,sex 性别 FROM students;


表的别名：

1
2
SELECT * FROM students as stu;
SELECT * FROM students  stu;

去除重复distinct

1
2
-- distinct可以过滤除select查询结果中重复的记录
SELECT DISTINCT sex from students;

1
SELECT sex,class from students;

1
SELECT DISTINCT sex,class from students;

where子句

1
SELECT * from students WHERE studentNO='001';

1
SELECT name,class from students WHERE age=30;

  • select控制查询返回什么样的列（字段)
  • where控制查询返回什么样的行（记录）

比较运算符

  • 等于：=
  • 大于：>
  • 大于等于：>=
  • 小于：<
  • 小于等于：<=
  • 不等于：!=或<>

逻辑运算符

  • 与and

  • 或or

  • 非not

    模糊查询

  • like，表示模糊查询

  • %表示任意多个字符

  • 下划线表示任意一个字符

1
2
-- 查询姓名以孙开头的学生信息
SELECT * FROM students WHERE name like '孙%';

1
2
-- 查询姓名以孙开头,且名字只有一个字的学生信息
SELECT * FROM students WHERE name like '孙_';

1
2
-- 查询名中带乔的学生信息
SELECT * FROM students WHERE name like '%乔';

1
2
-- 查询姓名中有白的学生信息
SELECT * FROM students WHERE name like '%白%';

1
2
3
4
5
6
-- 查询姓名为两个字的学生记录
SELECT * FROM students WHERE name like '__';
-- 查询姓白且年龄大于30的学生记录
SELECT * FROM students WHERE name like '白%' AND age > 30;
-- 查询学号以1结尾的学生记录
SELECT * FROM students WHERE studentNO like '%1';

范围查找

  • in：表示在一个非连续的范围内
1
SELECT * FROM students WHERE hometown IN ('北京','上海','广东');


  • between…and…：表示在一个连续的范围内
1
SELECT * FROM students WHERE age BETWEEN 25 AND 30;

1
2
3
4
-- 查询age年龄在20或25或30的女生记录
SELECT * FROM students WHERE age in (20,25,30) AND sex='女';
-- 查询age年龄25到30以外的学生记录
SELECT * FROM students WHERE age NOT BETWEEN 25 AND 30;

空判断

在 SQL 中，NULL 代表未知值或缺失值，它与任何其他值（包括它自己）的比较结果都是未知的。因此，在 SQL 中，你不能使用 = 运算符来判断一个值是否为 NULL，因为任何值与 NULL 进行比较的结果都是未知的，包括自己与自己进行比较。

  • 判断空：is null
1
SELECT * FROM students WHERE card is NULL;

  • 判断非空：is not null
1
SELECT * FROM students WHERE card is NOT NULL;

where在update和delete

1
UPDATE students SET class = '2班' WHERE age = 25 AND name='孙尚香';

1
DELETE FROM students WHERE class = '1班' and age >30;

1
UPDATE students SET class = '1班' WHERE name like '孙%';

1
DELETE FROM students WHERE (age BETWEEN 20 and 30) and sex = '男';

排序 order by

1
2
select * from 表名
order by 字段1 asc |desc,字段2 asc|desc,...
1
SELECT * from students ORDER BY age; -- 默认为升序asc

1
SELECT * from students ORDER BY age DESC;

1
2
3
-- 按年龄从大到小排序，年龄相同时按学号从小到大排序
-- 优先级前面的字段在前面
SELECT * from students ORDER BY age DESC,studentNO;

1
2
-- where出现在oder by前面
SELECT * from students WHERE sex = '男' ORDER BY class,studentNO desc;

聚合函数

count总记录数

count(*) 表示总记录数，括号中写 * 与字段名、结果是相同的。字段记录有null时不同

1
2
SELECT COUNT(*) FROM students
SELECT COUNT(name) FROM students

1
2
-- 统计班级数
SELECT COUNT(DISTINCT class) FROM students

1
2
-- 统计女同学的数量
SELECT COUNT(*) FROM students WHERE sex='女';

max最大值

max(字段)表示求此字段的最大值

1
SELECT MAX(age) FROM students;

1
SELECT MAX(age) FROM students WHERE sex='女';


聚合函数不能用在where子句中

1
select name from students where age = max(age); -- 错误

min最小值

min(字段)表示求此字段的最小值

1
2
SELECT MIN(age) FROM students WHERE sex='女';
select name from students where age = min(age); -- 错误

sum求和

1
SELECT sum(age) FROM students WHERE sex='女';

avg平均年龄

avg会忽略null

1
SELECT avg(age) FROM students WHERE sex='女';

mod()取余

1
2
3
MOD(dividend, divisor)
-- dividend 是被除数，即要计算余数的数字。
-- divisor 是除数，即要除以的数字。

数据分组

1
2
3
select 聚合函数 from 表名 where 条件 group by 字段;
select 聚合函数 from 表名 group by 字段;
SELECT COUNT(*) FROM students GROUP BY sex;

1
SELECT sex,COUNT(*) FROM students GROUP BY sex;

1
SELECT sex,COUNT(*) FROM students WHERE class='1班' GROUP BY sex;

1
SELECT class,COUNT(*) 总数,avg(age),MAX(age) ,min(age) FROM students GROUP BY class

1
SELECT class,COUNT(*) 总数,avg(age),MAX(age) ,min(age) FROM students WHERE NOT class='3班' GROUP BY class ORDER BY class DESC;

分组后进行筛选

1
2
3
4
5
6
7
8
select 字段1, 字段2, 聚合.. from 表名
group by 字段1，字段2，字段3...
having 字段1，...聚合...
-- 没有group by不能用having
--  where先筛选复合条件的记录，再聚合统计
SELECT COUNT(*) FROM students WHERE sex='男';
-- having先分组统计，在统计的结果中筛选
SELECT COUNT(*) from students GROUP BY sex HAVING sex='男';

1
2
-- 求班级人数大于2的班级
SELECT class FROM students GROUP BY class HAVING COUNT(*)>2;

1
2
-- 查询班级总人数大于2人的班级名称及班级对应的总人数
SELECT class,COUNT(*) FROM students GROUP BY class HAVING COUNT(*)>2;

1
2
-- 查询平均年龄大于30岁的班级名称和班级总人数
SELECT class,COUNT(*) FROM students GROUP BY class HAVING AVG(age)>30;

对比where和having

  • where是对from后面指定的表进行筛选，是对原始数据进行筛选；
  • having是对group by的结果进行筛选；
  • having后面的条件可以用聚合函数，where后面的条件不可以使用聚合函数
  • WHERE 用于筛选行，它指定了对哪些行进行聚合操作。WHERE 子句筛选出满足条件的行，并将其传递给 GROUP BY 子句进行分组。
  • GROUP BY 用于对行进行分组，并对每个组应用聚合函数（如 COUNT、SUM、AVG 等）。GROUP BY 子句指定了按照哪些列对行进行分组，聚合函数将在每个组上计算。

数据分页显示

limit 开始行,获取行数;

1
2
3
select * from 表名 start,count -- start默认第一行
SELECT * FROM students LIMIT 0,3;

1
2
-- 查询从第四条记录开始的三条记录
SELECT * FROM students LIMIT 3,3;

1
2
3
4
-- limit总是出现在select语句的最后
select * from 表名 where 条件 group by 字段 oder by 字段 limit start,count;
-- 获取年龄最大的同学的相关信息
SELECT * FROM students ORDER BY age DESC LIMIT 1;

1
2
-- 查询年龄最小的女同学信息
SELECT * FROM students WHERE sex ='女' ORDER BY age  LIMIT 1;

分页

已知每页显示m条数据，求：查询第n页的数据

1
2
3
4
5
6
7
select * from students limit(n-1)*m ,m
-- 每页显示4条记录，查询第3页的数据
select * from students limit 8,4;
-- 查询students表，每页显示5条记录，求总页数
1. 查询记录总条数 a；
2. 使用a除以每页的条数5，得到b；
3. 如果b为整数，b为总数

多表查询

通过连接运算实现

  • 内连接
  • 左连接
  • 右连接

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
/* 创建学生表students */
create table students (
  studentNo varchar(10) primary key, /*学号，主键，值不能重复*/
  name varchar(10), /*姓名*/
  sex varchar(1), /*性别*/
  hometown varchar(20), /*家乡*/
  age tinyint, /*年龄*/
  class varchar(10), /*班级*/
  card varchar(20) /*身份证号码*/
);

/* 向学生表students插入数据 */
insert into students values
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198505177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'),
('007', '百里玄策', '男', '山西', '39', '2班', '140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', null),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');

/* 如果课程表courses存在，删除课程表courses */
drop table if exists courses;

/* 创建课程表courses */
create table courses (
courseNo int unsigned primary key auto_increment, /*课程号 主键，值不能重复，且自增长*/
coursename varchar(10) /*课程名称*/
);

/* 向课程表courses插入数据 */
insert into courses (coursename) values
('数据库'),
('html'),
('linux'),
('系统测试'),
('单元测试'),
('测试过程'),
('python');

/* 如果成绩表scores存在，删除课程表scores */
drop table if exists scores;

/* 创建scores表 */
create table scores (
id int unsigned primary key auto_increment,/*主键，值不能重复，且自增长*/
courseNo int unsigned, /*课程号，与courses表中courseNo关联*/
studentNo varchar(10), /*学号，与students表中studentNo关联*/
score tinyint /*成绩*/
);

/* 向scores表插入数据 */
insert into scores (courseNo, studentNo,score) values
(1, '001', 90),
(1, '002', 75),
(2, '002', 98),
(3, '001', 86),
(3, '003', 80),
(4, '004', 79),
(5, '005', 96),
(6, '006', 80);

/* 如果区域表areas存在，删除areas */
drop table if exists areas;

/* 创建区域表areas */
create table areas(
id int primary key,
name varchar(20),
pid int);

/* 向区域表areas插入数据 */
insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('440000', '广东省', NULL),
('440300', '深圳市', '440000'),
('440100', '广州市', '440000'),
('440500', '汕头市', '440000'),
('440600', '佛山市', '440000'),
('440200', '韶关市', '440000');

内连接

查询的结果只显示两个表中满足连接条件的部分，即A∩B

1
2
3
4
5
6
7
8
9
10
select * from 表1
inner join 表2 on 表1.字段=表2.字段

DROP TABLE if EXISTS a;
CREATE TABLE a(id INT, name VARCHAR(10));
INSERT INTO a VALUES ('1','张三'),('2','李四'),('3','王五');
DROP TABLE if EXISTS b;
CREATE TABLE b(id INT, score int);
INSERT INTO b VALUES ('1','30'),('1','40'),('2','50'),('2','60');
SELECT * FROM a INNER JOIN b ON a.id=b.id;


id(1)为别名

1
SELECT * FROM students INNER JOIN scores ON students.studentNo=scores.studentNo;

1
2
3
4
5
6
7
8
SELECT name,courseNo,score FROM students,scores WHERE students.studentNo=scores.studentNo;
-- 使用表的别名
SELECT name,courseNo,score FROM students st,scores sc WHERE st.studentNo=sc.studentNo;
-- 别名
--
SELECT name 姓名,courseNo 课程编号,score 成绩 FROM students st INNER JOIN scores sc ON st.studentNo=sc.studentNo;
--
SELECT name 姓名,courseNo 课程编号,score 成绩 FROM students st,scores sc WHERE st.studentNo=sc.studentNo;


连接courses表和scores表

1
2
3
4
-- 
SELECT * FROM scores INNER JOIN courses on scores.courseNo=courses.courseNo;
--
SELECT * FROM scores s INNER JOIN courses c on s.courseNo=c.courseNo;

1
SELECT name,courseNo,score FROM students s1 INNER JOIN scores s2 on s1.studentNo=s2.studentNo WHERE s1.name='王昭君';

1
SELECT name,score FROM students s1 INNER JOIN scores s2 on s1.studentNo=s2.studentNo WHERE s1.name='王昭君' AND s2.score<90;

隐式内连接

1
2
-- 隐式内连接
select * from 表1,表2 where 表1.字段=表2.字段;

多表内连接

1
SELECT * FROM students s1 INNER JOIN scores sc on s1.studentNo=sc.studentNo INNER JOIN courses s2 on sc.courseNo=s2.courseNo;


查询学生的linux成绩：

1
SELECT  name,score,coursename FROM students s1 INNER JOIN scores sc on s1.studentNo=sc.studentNo INNER JOIN courses s2 on sc.courseNo=s2.courseNo where s2.coursename='linux';


查询成绩最高的男生的信息

1
SELECT  name,score,coursename FROM students s1 INNER JOIN scores sc on s1.studentNo=sc.studentNo INNER JOIN courses s2 on sc.courseNo=s2.courseNo where s1.sex='男' ORDER BY sc.score DESC LIMIT 1;

左连接

用于从左表中选择所有的行，并且对于右表中没有匹配的行，返回 NULL 值。

在 LEFT JOIN 中，左表是指位于 JOIN 关键字左侧的表，右表是指位于 JOIN 关键字右侧的表。LEFT JOIN 会返回左表的所有行，以及与左表中的行匹配的右表的行。如果右表中没有与左表中的行匹配的行，则会在结果中将右表的列设置为 NULL。

左连接适用于以下情况：

  1. 你想要保留左表的所有行，并将与左表中的行匹配的右表的行加入结果集。
  2. 左表是主表，右表是从表，你想要基于主表的内容来扩展结果集。
1
2
3
4
select * from 表1
left join 表2 on 表1.字段=表2.字段
--
SELECT * FROM students LEFT JOIN scores ON students.studentNo=scores.studentNo;

右连接

与左连接类似，不同之处在于它会返回右表的所有行，以及与右表中的行匹配的左表的行。如果左表中没有与右表中的行匹配的行，则会在结果中将左表的列设置为 NULL。

右连接适用于以下情况：

  1. 你想要保留右表的所有行，并将与右表中的行匹配的左表的行加入结果集。
  2. 右表是主表，左表是从表，你想要基于主表的内容来扩展结果集。
1
2
3
4
select * from 表1
right join 表2 on 表1.字段=表2.字段
--
SELECT * FROM scores right JOIN courses ON scores.courseNo=courses.courseNo;

自关联

  • 表中的相同字段所表示的含义不同
  • 自关联是同一张表做连接查询
  • 自关联下，一定要找到同一张表可关联的不同字段

1
2
-- 查询有多少个省
SELECT COUNT(*) FROM areas WHERE pid IS NULL;

1
2
-- 查询多少个市
SELECT COUNT(*) FROM areas WHERE pid IS NOT NULL;

1
2
3
-- 查询广东省所有的城市
SELECT * FROM areas a1 INNER JOIN areas a2 ON a1.id=a2.pid WHERE a2.pid='440000';
SELECT * FROM areas a1 INNER JOIN areas a2 ON a1.id=a2.pid WHERE a1.name='广东省';

子查询

一条select语句中，嵌入了另一个select语句，那么嵌入的select语句称之为子查询。

外层的第一条select语句为主查询

子查询和主查询的关系

  • 子查询嵌入到主查询中

  • 子查询是辅助主查询的，要么充当条件，要么充当数据源；

  • 子查询是可以独立存在的语句，是一条完整的select语句

    1
    2
    -- 查询大于平均年龄的学生记录
    SELECT * FROM students WHERE age>(SELECT AVG(age) FROM students);

    标量子查询：子查询返回的结果只有一个值（一行一列），在主查询的条件中一般用比较运算符

    1
    2
    -- 查询30岁学生的成绩
    SELECT score FROM scores WHERE studentNo IN(SELECT studentNo FROM students WHERE age =30)

    列子查询：子查询返回的结果是一列（一列多行），条件中一般用in

1
2
3
4
5
-- 查询所有女生的信息和成绩
-- 内连接：
select * from students inner join scores on students.studentNo=scores.studentNo where sex='女';
--   where为子查询的条件
SELECT * FROM (SELECT * FROM students WHERE sex ='女') femalestu INNER JOIN scores sc ON femalestu.studentNo=sc.studentNo

表级子查询：子查询的结果返回一张表（多行多列），一般和其他表联合查询

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
drop table if exists departments; 
/* 创建部门表 */ 
create table departments ( 
	deptid int(10) primary key, /* 部门编号， 与 employees 表 deptid 关联 */ 
	deptname varchar(20) not null /* 部门名称 */ 
); /* 向部门表插入数据 */ 
insert into departments values ('1001', ' 市场部 ' ); 
insert into departments values ('1002', ' 测试部 ' ); 
insert into departments values ('1003', ' 开发部 ' ) ; 
drop table if exists employees; /* 创建员工表 */ 
create table employees ( 
	empid int primary key, /* 职工编号 */ 
	empname varchar(20) not null, /* 姓名 */ -- 姓名 
	sex varchar(4) default null, /* 性别 */ -- 性别 
	deptid int default null, /* 部门编号， 与 departments 表 deptid 关联 */ 
	jobs varchar(20) default null, /* 岗位名称 */ 
	politicalstatus varchar(20) default null, /* 政治面貌 */ 
	leader int default null /* 上级领导的职工编号 */ 
); /* 向员工表插入数据 */ 
insert into employees values ('1', ' 王昭君 ' , ' 女 ' , ' 1003', ' 开发 ' , ' 群众 ' , '9'); 
insert into employees values ('2', ' 诸葛亮 ' , ' 男 ' , ' 1003', ' 开发 ' , ' 群众 ' , '9'); 
insert into employees values ('3', ' 张飞 ' , ' 男 ' , ' 1002', ' 测试 ' , ' 团员 ' , '4'); 
insert into employees values ('4', ' 白起 ' , ' 男 ' , ' 1002', ' 测试经理 ' , ' 党员 ' , null); 
insert into employees values ('5', ' 大乔 ' , ' 女 ' , ' 1002', ' 测试 ' , ' 党员 ' , '4'); 
insert into employees values ('6', ' 孙尚香 ' , ' 女 ' , ' 1001', ' 市场 ' , ' 党员 ' , ' 12'); 
insert into employees values ('7', ' 百里玄策 ' , ' 男 ' , ' 1001', ' 市场 ' , ' 团员 ' , ' 12'); 
insert into employees values ('8', ' 小乔 ' , ' 女 ' , ' 1002', ' 测试 ' , ' 群众 ' , '4'); 
insert into employees values ('9', ' 百里守约 ' , ' 男 ' , ' 1003', ' 开发经理 ' , ' 党员 ' , null); 
insert into employees values ('10', ' 妲己 ' , ' 女 ' , ' 1003', ' 开发 ' , ' 团员 ' , '9'); 
insert into employees values ('11', ' 李白 ' , ' 男 ' , ' 1002', ' 测试 ' , ' 团员 ' , '4'); 
insert into employees values ('12', ' 孙膑 ' , ' 男 ' , ' 1001', ' 市场经理 ' , ' 党员 ' , null); 
insert into employees values ('13', ' 周瑜 ' , ' 男 ' , ' 1001', ' 市场 ' , ' 群众 ' , ' 12'); 
drop table if exists salary; /* 创建工资表 */ 
create table salary ( 
	sid int primary key, 
	empid int not null, /* 职工编号 , 与 employees 表 empid 关联 */ 
	salary int not null /* 工资 */ 
); /* 向工资表插入数据 */ 
insert into salary values ('1', '7', '2100'); 
insert into salary values ('2', '6', '2000'); 
insert into salary values ('3', '12', '5000'); 
insert into salary values ('4', '9', '1999'); 
insert into salary values ('5', '10', '1900'); 
insert into salary values ('6', '1', '3000');

查询练习

1
2
-- 列出男职工的总数和女职工的总数
SELECT sex,count(*) FROM employees GROUP BY sex;

1
2
-- 列出非党员职工的总数
SELECT count(*) FROM employees WHERE NOT politicalstatus = ' 党员 '

1
2
3
-- 列出所有职工工号、姓名以及所在部门名称
SELECT empid,empname,deptname FROM employees emp INNER JOIN departments dp ON emp.deptid=dp.deptid
-- 想要部门相同的一起列出在后面加oder by deptname

1
2
3
4
-- 列出所有职工工号、姓名和对应工资
SELECT emp.empid,empname,salary FROM employees emp INNER JOIN salary s ON emp.empid=s.empid ORDER BY emp.empid;
-- left join，没有工资的员工也显示
SELECT emp.empid,empname,salary FROM employees emp LEFT JOIN salary s ON emp.empid=s.empid ORDER BY emp.empid;

1
2
3
4
-- 列出领导岗的姓名以及所在部门名称
SELECT empname,deptname FROM (SELECT * FROM employees WHERE leader is NULL) l INNER JOIN departments d ON l.deptid=d.deptid
-- 方法2
select empname,deptname from employees e inner join departments d on e.depid =d.depid where leader is null;

1
2
-- 列出职工总人数大于4的部门号和总人数
SELECT deptid,COUNT(*) FROM employees GROUP BY deptid HAVING count(*)>4 

1
2
-- 列出职工总人数大于4的部门号和部门名称
SELECT e.deptid,deptname FROM departments d INNER JOIN employees e ON d.deptid=e.deptid GROUP BY e.deptid HAVING COUNT(*)>4;

1
2
-- 列出开发部和测试部的职工号，姓名
SELECT e.empid,e.empname FROM employees e INNER JOIN departments d ON e.deptid=d.deptid WHERE deptname IN(' 开发部 ',' 测试部 ')

1
2
-- 列出市场部所有女职工的姓名和政治面貌
SELECT e.empname,e.politicalstatus FROM employees e INNER JOIN departments d ON e.deptid=d.deptid WHERE deptname=' 市场部 ' AND sex=' 女 '

1
2
-- 显示出所有职工姓名和工资，包括没有工资的职工姓名
SELECT e.empname,salary FROM employees e LEFT JOIN salary s ON e.empid=s.empid

1
2
-- 求不姓孙的所有职工的工资总和
SELECT SUM(salary) FROM salary s INNER JOIN employees e ON s.empid=e.empid WHERE empname NOT LIKE ' 孙% '

MySQL常用内置函数

字符串函数

  • 拼接字符串concat(str1,str2...)
1
2
-- 把12,34，'ab'拼接为一个字符串'1234ab'
CONCAT(12,34,'ab')

  • 计算字符串中字节个数length(str) 
1
2
SELECT LENGTH('abc');
SELECT LENGTH('我');

1
2
-- 查询名字为3个字的学生信息
SELECT * FROM students WHERE LENGTH(name)=9;

  • 获取字符串中的字符数char_length()
  • 截取字符串
    • left(str,len)返回字符串str左端的len个字符,中文和英文字母一样
    • right(str,len)返回字符串str右端的len个字符
    • substring(str,pos,len)返回字符串str的位置pos起len个字符，pos从1开始计数
1
SELECT left('我和你们',3)

1
SELECT right('我和你abc',4)

1
SELECT substring('我和你abc',2,2)

1
2
-- 查询所有学生信息，按年龄从大到小排序
SELECT * FROM students ORDER BY SUBSTRING(card,7,8)

  • 去除空格
    • ltrim(str)返回删除左侧空格的字符串
    • rtrim(str)返回删除右侧空格的字符串
    • trim(str)去除两侧空格
1
2
3
SELECT LTRIM('                 abcd                     ');
SELECT RTRIM('                 abcd                     ');
SELECT TRIM('                 abcd                     ');

数学函数

  • 求四舍五入值round(n,d),n表示原数，d表示小数位数，默认为0
1
SELECT ROUND(1.653)

1
SELECT ROUND(1.653,2)

1
2
-- 求学生的平均年龄并保留2位小数
SELECT ROUND(AVG(age),2) FROM students;

  • 随机数rand()产生从0到1.0的随机浮点数
1
SELECT RAND();

1
2
-- 随机排序
SELECT * FROM students ORDER BY RAND();

1
2
-- 随机抽取一名学生
SELECT name FROM students ORDER BY RAND() LIMIT 1;

日期时间函数

  • 当前日期current_date()
1
SELECT CURRENT_DATE

  • 当前时间current_time()
1
SELECT CURRENT_TIME

  • 当前时间（具体）now()
1
SELECT NOW()

1
2
3
4
-- 记录当前时间，比如下单时间
CREATE table a (id INT,indate DATETIME);
INSERT INTO a VALUE(1,NOW());
SELECT * FROM a;

存储过程

存储过程procedure，即存储程序，是一条或者多条SQL语句的集合。

1
2
3
4
5
-- 语法
create procedure 存储过程名称(参数名称)
begin
sql 语句
end
1
2
3
4
5
-- 创建存储过程
CREATE PROCEDURE stu()
BEGIN
	SELECT * FROM students;
END

1
2
-- 调用存储过程stu()，用CALL
CALL stu();

1
2
3
4
5
6
-- 删除存储过程
drop procedure 存储过程;
drop procedure if exists 存储过程;
-- 删除时不用括号
DROP PROCEDURE stu;
DROP PROCEDURE IF EXISTS stu;

视图

视图（View）是一种虚拟的表，它是由一个或多个表的行数据经过某种逻辑组合而形成的，它本身并不存储数据，只是对查询结果的一种抽象表示。通过视图，我们可以将复杂的查询、逻辑和数据组织进行封装，以简化数据访问和管理。视图的本质就是对查询的封装。

  1. 虚拟性：视图本身不存储数据，它只是对查询结果的一种逻辑表示。
  2. 透明性：使用视图的应用程序无需关心视图的底层实现，可以像访问普通表一样访问视图。
  3. 安全性：视图可以隐藏底层表的部分列或行，只向用户暴露需要的数据，从而提高数据安全性。
  4. 简化复杂性：通过视图，可以将复杂的查询逻辑封装起来，使用户可以通过简单的查询访问数据。
  5. 重用性：视图可以被多个查询或应用程序共享，提高了代码的重用性。
1
2
3
-- 创建视图
create view 视图名称 as select 语句;
CREATE VIEW boys as SELECT * FROM students WHERE sex='男';

1
2
3
-- 查看视图
SELECT * FROM boys WHERE age>30;

注意：视图的表是只读的。需要注意的是，视图中的数据是动态的，当基础表中的数据发生变化时，视图中的数据也会相应地更新。

1
2
-- 视图和表进行内连接操作
SELECT * FROM boys INNER JOIN scores ON boys.studentNo=scores.studentNO

1
2
3
4
-- 删除视图
drop view 视图名称;
drop view if exists 视图名称;
DROP VIEW boys;

事务

事务是多条更改数据操作的sql语句集合，是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位

  • 开启事务

begin：开启事务后执行修改UPDATE或者删除DELETE记录语句，变更会写到缓存中而不会立刻生效

  • 回滚事务

rollback：放弃修改

  • 提交事务

commit：将修改的事务写入实际的表中

1
2
3
4
BEGIN;
DELETE FROM students WHERE studentNo='001';
SELECT * FROM students;
DELETE FROM scores WHERE studentNo='001';

1
2
3
4
-- 回滚操作
ROLLBACK;
SELECT * FROM students;
SELECT * FROM scores;

如果开启了一个事务，之后没有rollback也没有commit，系统出现了错误，会默认rollback

1
2
3
4
5
6
7
-- 开启事务，删除学生信息和成绩，提交事务使两个表的删除同时生效
BEGIN;
DELETE FROM students WHERE studentNo='001';
DELETE FROM scores WHERE studentNo='001';
COMMIT;-- 一旦提交事务，两个删除操作同时生效
SELECT * FROM students;
SELECT * FROM scores;

索引

可以加快select查询的速度，但是会降低更新表的速度，因为在保存文件的同时会同时保存索引文件。可以在更改记录前先删除索引

创建索引

1
2
3
create index 索引名称 on 表名(字段名称(长度))
-- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时长度一致
-- 字段类型如果不是字符串，可以不填写长度部分
1
2
3
4
5
-- 为students表的age字段创建索引，名为age_index
CREATE INDEX age_index ON students (age);
-- 为students表的name字段创建索引，名为name_index
CREATE INDEX name_index ON students (name(10));
-- varchar(10)
1
2
3
4
5
6
7
-- where条件后面的字段，数据库系统会自动查找是否有索引
-- 自动调用age_index
SELECT * FROM students WHERE age=30;
-- 自动调用name_index
SELECT * FROM students WHERE name='李白'
-- 不会调用任何索引，因为没有创建索引，会全表一个个查找
SELECT * FROM students WHERE name='李白'

查看索引

1
2
show index from 表名
SHOW INDEX FROM students

系统会为主键自动创建索引

删除索引

1
2
drop index 索引名 on 表名;
DROP INDEX age_index ON students;

使用命令行

使用数据库

1
2
3
4
-- 登录
mysql -h[主机名] -u 用户名 -p
-- -u的意思是用户名
-- -h，当登录的mysql不在本机上，后跟主机ip地址
1
mysql -u root -p

1
2
-- 查看数据库
show databases;

1
2
-- 打开数据库
use mydb;
1
2
-- 查看数据库中的表
show tables;

1
2
-- 查询内容
select * from students;

可以使用chcp 65001将命令行编码方式改为utf-8

1
2
-- 查看表的结构
desc students;

1
2
3
-- 创建数据库
create database 数据库名 default charset[默认字符集];
create database test1 default charset utf8;

1
2
3
-- 删除数据库
drop database test1;

修改用户密码

1
grant all on 数据库名 to 用户名@'登录主机' indentified by '密码' with grant option;
  • grant all on:代表为用户赋权；
  • 数据库名：可以是*，代表所有数据库；
  • 表名：可以是*，代表所有表，
  • to 用户名：指定要创建用户的名称
  • @'登录主机' :@localhost代表只能在本机登录，@’%‘代表可以远程
sql基础
2024/04/04/sql基础/
Author
Elora
Published
2024-04-04 14:35
License
 Comments
Comment plugin failed to load
Loading comment plugin
  1. 1. 表
  2. 2. SQL语言（structured query language)
    1. 2.1. 注释
  3. 3. MySQL常用数据类型
    1. 3.1. 表、字段、记录
      1. 3.1.1. 创建表 CREATE TABLE
      2. 3.1.2. INSERT插入记录
      3. 3.1.3. SELECT 简单查询
      4. 3.1.4. UPDATE 修改数据
      5. 3.1.5. DELETE删除表的记录
      6. 3.1.6. TEUNCATE删除表的所有数据，保留表结构
        1. 3.1.6.1. DELETE与TRUNCATE区别
      7. 3.1.7. DROP TABLE删除表
  4. 4. 字段的约束
    1. 4.1. 常用的约束
    2. 4.2. 主键
    3. 4.3. 非空
    4. 4.4. 唯一
    5. 4.5. 默认值
  5. 5. 条件查询
    1. 5.1. 数据准备
    2. 5.2. 查询
      1. 5.2.1. 别名
      2. 5.2.2. 去除重复distinct
    3. 5.3. where子句
      1. 5.3.1. 比较运算符
      2. 5.3.2. 逻辑运算符
      3. 5.3.3. 范围查找
      4. 5.3.4. 空判断
    4. 5.4. where在update和delete
    5. 5.5. 排序 order by
    6. 5.6. 聚合函数
      1. 5.6.1. count总记录数
      2. 5.6.2. max最大值
      3. 5.6.3. min最小值
      4. 5.6.4. sum求和
      5. 5.6.5. avg平均年龄
      6. 5.6.6. mod()取余
    7. 5.7. 数据分组
      1. 5.7.1. 分组后进行筛选
        1. 5.7.1.1. 对比where和having
    8. 5.8. 数据分页显示
      1. 5.8.1. 分页
  6. 6. 多表查询
    1. 6.1. 数据准备
    2. 6.2. 内连接
      1. 6.2.1. 隐式内连接
      2. 6.2.2. 多表内连接
    3. 6.3. 左连接
    4. 6.4. 右连接
  7. 7. 自关联
  8. 8. 子查询
    1. 8.1. 子查询和主查询的关系
    2. 8.2. 数据准备
    3. 8.3. 查询练习
  9. 9. MySQL常用内置函数
    1. 9.1. 字符串函数
    2. 9.2. 数学函数
    3. 9.3. 日期时间函数
  10. 10. 存储过程
  11. 11. 视图
  12. 12. 事务
  13. 13. 索引
    1. 13.1. 创建索引
    2. 13.2. 查看索引
    3. 13.3. 删除索引
  14. 14. 使用命令行
    1. 14.1. 使用数据库
    2. 14.2. 修改用户密码
© 2020 - 2024    Elora
Powered by Hexo & Theme Keep
This site is deployed on
Total words 75.1k Unique Visitor Page View
  1. 1. 表
  2. 2. SQL语言（structured query language)
    1. 2.1. 注释
  3. 3. MySQL常用数据类型
    1. 3.1. 表、字段、记录
      1. 3.1.1. 创建表 CREATE TABLE
      2. 3.1.2. INSERT插入记录
      3. 3.1.3. SELECT 简单查询
      4. 3.1.4. UPDATE 修改数据
      5. 3.1.5. DELETE删除表的记录
      6. 3.1.6. TEUNCATE删除表的所有数据，保留表结构
        1. 3.1.6.1. DELETE与TRUNCATE区别
      7. 3.1.7. DROP TABLE删除表
  4. 4. 字段的约束
    1. 4.1. 常用的约束
    2. 4.2. 主键
    3. 4.3. 非空
    4. 4.4. 唯一
    5. 4.5. 默认值
  5. 5. 条件查询
    1. 5.1. 数据准备
    2. 5.2. 查询
      1. 5.2.1. 别名
      2. 5.2.2. 去除重复distinct
    3. 5.3. where子句
      1. 5.3.1. 比较运算符
      2. 5.3.2. 逻辑运算符
      3. 5.3.3. 范围查找
      4. 5.3.4. 空判断
    4. 5.4. where在update和delete
    5. 5.5. 排序 order by
    6. 5.6. 聚合函数
      1. 5.6.1. count总记录数
      2. 5.6.2. max最大值
      3. 5.6.3. min最小值
      4. 5.6.4. sum求和
      5. 5.6.5. avg平均年龄
      6. 5.6.6. mod()取余
    7. 5.7. 数据分组
      1. 5.7.1. 分组后进行筛选
        1. 5.7.1.1. 对比where和having
    8. 5.8. 数据分页显示
      1. 5.8.1. 分页
  6. 6. 多表查询
    1. 6.1. 数据准备
    2. 6.2. 内连接
      1. 6.2.1. 隐式内连接
      2. 6.2.2. 多表内连接
    3. 6.3. 左连接
    4. 6.4. 右连接
  7. 7. 自关联
  8. 8. 子查询
    1. 8.1. 子查询和主查询的关系
    2. 8.2. 数据准备
    3. 8.3. 查询练习
  9. 9. MySQL常用内置函数
    1. 9.1. 字符串函数
    2. 9.2. 数学函数
    3. 9.3. 日期时间函数
  10. 10. 存储过程
  11. 11. 视图
  12. 12. 事务
  13. 13. 索引
    1. 13.1. 创建索引
    2. 13.2. 查看索引
    3. 13.3. 删除索引
  14. 14. 使用命令行
    1. 14.1. 使用数据库
    2. 14.2. 修改用户密码