MySQL笔记大总结20250108

Day2

1.where

(1)关系运算符

select * from info where id>1;
select * from info where id=1;
select * from info where id>=1;
select * from info where id!=1;

(2)逻辑运算符

select * from info where name="吴佩奇" and age=19;   
select * from info where name="alex" or age=49;
select * from info where (name="李杰" or email="[e-mail]") and age=49;  

(3)between

select * from info where id between 2 and 4;

(4)in

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);

(5)exists

select * from info where exists(select * from info where id=5);
select * from info where not exists(select * from info where id=5);

(6)二次查询

select * from (select * from info where id>2) as T where T.age>30;

(7)通配符

select * from info where email like "%y%";
select * from info where name like "_ony";

2.指定列

(1)选择所有数据

1.语法
select * from 表名;
2.示例
select * from course;

 

(2)选择指定列数据

1语法
select 列名1,列名2 from 表名;
2.示例
select cname,teacher_id from course;

(3)为选定列指定别名

1.语法
select 列名1 as 别名1,列名2 as 别名2 from 表名;
2.示例
select cname as u1,teacher_id as v1 from course;
说明：
as 在mysql中是为列名起别名的关键字。

(4)新增数据并指定列名

1.语法
select 列名1 as 别名1,列名2 as 别名2,"数值" as 新增列名 from 表名;
2.示例
select cname as u1,teacher_id as v1,"y" as w1 from course;

select cname as u1,teacher_id as v1,"y" as w1 from course;
代码解释：
SELECT：这是 SQL 中用于查询数据的关键字。
cname as u1：表示从 course 表中选择 cname 列，并将该列的名称在结果集中重命名为 u1。这样在最终的查询结果中，原本的 cname 列将显示为 u1 列。
teacher_id as v1：从 course 表中选择 teacher_id 列，并将其重命名为 v1 列。这在结果集中将以 v1 的名称显示该列的数据。
"y" as w1：这里是一个常量值 "y"，将其作为一个名为 w1 的列添加到结果集中。无论表 course 中有多少行数据，w1 列的值都将始终为 "y"。这是一种在结果集中添加常量列的方法，在某些情况下可以用来补充信息或进行数据标记。
FROM course：指定了要查询数据的表，即 course 表。

 (5)动态获取关联数据并指定新增列名

select 
cname,
teacher_id,
(select tname from teacher where teacher.tid=teacher_id) as teacher
from course;

3.按指定列排序

1.顺序
select * from teacher order by 列名 desc;
2.倒序
select * from teacher order by 列名 asc;
3.示例
select * from teacher order by tname desc;
select * from teacher order by tname asc;
4.按多列排序
select * from info order by age,id desc;
优先按照age从小到大排序;如果age相同则按照id从大到小排序。

4.获取部分数据

1.获取排在前面一定行数的数据
select * from teacher limit 10;
2.获取指定行号后面的一定行数的数据
select * from teacher limit 10 offset 5;
从第5行开始但不包括第5行

5.分组

(1)关于对分组的理解：实际上是获取某个分组的统计数据。

1.语法
select 分组列名,分组统计函数 from 表名 group by 分组列名;
2.示例
select gender,count(gender) from student group by gender;
select gender,count(gender) as count from student group by gender;--为统计列重新命名

(2)筛选分组后的数据

 关键字：having

1.示例
select age,count(id) from info where id>4 group by age having count(id)>=2;
2.代码解释：
SELECT age, COUNT(id)：
SELECT 关键字用于指定要从表中查询的列。在这里，我们要查询两个部分：
age：表示要选择 info 表中的 age 列。
COUNT(id)：COUNT() 是一个聚合函数，用于计算 id 列的数量。在这个查询中，它会统计满足条件的每组 age 中 id 的数量。
FROM info：
这部分指定了要从哪个表中查询数据，这里是 info 表。
WHERE id > 4：
WHERE 子句用于筛选满足条件的数据行。在这里，它筛选出 id 大于 4 的记录。
GROUP BY age：
GROUP BY 子句用于将数据按照 age 列的值进行分组。对于 info 表中具有相同 age 的记录，会被分到同一组中。
HAVING COUNT(id) >= 2：
HAVING 子句用于对分组后的结果进行筛选。这里使用 HAVING 而不是 WHERE，是因为 WHERE 不能用于筛选聚合结果，而 HAVING 可以。它筛选出那些 id 数量（通过 COUNT(id) 计算）大于或等于 2 的组。

6.表关系(左右关系)

1.左外连接并获取所有列的数据
(1)语法
select * from 左表名 left outer join 右表名 on 左表名.对应列=右表名.对应列;
(2)示例
select * from student left outer join class on student.class_id=class.cid;
2.左外连接并获取指定列的数据
select sid,sname,gender,caption from student left outer join class on student.class_id=class.cid;
2.右外连接参左外连接
3.内连接，需要时查找笔记
4.上下连接，很少用，需要时查找笔记

7.外键约束

(1)创建表时设置外键

1.创建表时设置外建
constraint 外键约束名称 foreign key (需要设置外键的列名) references 关联表名(需要关联的列名)2.示例
constraint fk_student_class foreign key (class_id) references class(cid)3.代码解释：
CONSTRAINT：这是 SQL 中的关键字，用于在表中创建约束。约束用于确保数据的完整性和一致性。
fk_student_class：这是给外键约束取的一个名字，这个名字可以根据需要自定义，通常遵循一定的命名规则，以便后续的管理和维护，比如 fk_表名_关联表名 这样的命名约定，在这里表示这是一个与 student 表和 class 表关联的外键约束，其名称为 fk_student_class。
FOREIGN KEY (class_id)：这部分指定了在当前表中哪个列将作为外键，这里是 class_id 列。外键列的值必须是另一个表中某列的值或者为 NULL（如果外键允许 NULL 值）。
REFERENCES class(cid)：指定了外键所引用的表和列，这里表示 class_id 列引用了 class 表中的 cid 列。这意味着 class_id 列中的值必须是 class 表中 cid 列的值，或者为 NULL（如果外键允许 NULL）。

(2)为已有表添加外键约束

1.语法
alter table guardian add constraint 外键约束名称 foreign key (需要设置外键的列名) references 关联表名(需要关联的列名)
2.示例
alter table guardian add constraint fk_guardian_class foreign key guardian(class_id) references class(cid);

(3)删除外键

1.语法
alter table 表名 drop foreign key 外键约束名称;
2.示例
alter table guardian drop foreign key fk_guardian_class;

8.SQL语句顺序

from
join on
where
group by
having
(select执行顺序和书写顺序不一致)
order by
limit

结合老师课件写的示例，测试可以成功运行：
select *
from info 
left outer join depart on info.depart_id=depart.did
where id>1
group by age
having count(id)>1
order by age
limit 1;

特别舒适的MySQL语句写法 

1.示例代码
SELECT t1.column1, t2.column2, COUNT(t3.column3) AS count_column3
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
LEFT JOIN table3 t3 ON t2.id = t3.table2_id
WHERE t1.column4 > 10
GROUP BY t1.column1, t2.column2
HAVING COUNT(t3.column3) > 2
ORDER BY t1.column1 ASC
LIMIT 10;
2.执行顺序解读
该 SQL 语句的执行顺序如下：
从 table1（FROM table1 t1）开始，这是左外连接中的左表。
执行 LEFT JOIN table2 t2 ON t1.id = t2.table1_id，将 table1 中的行与 table2 中匹配的行连接起来，如果 table2 中没有匹配的行，用 NULL 填充 table2 的列。
接着执行 LEFT JOIN table3 t3 ON t2.id = t3.table2_id，将上一步的结果与 table3 中的行进行连接，如果 table3 中没有匹配的行，用 NULL 填充 table3 的列。
使用 WHERE t1.column4 > 10 对连接后的结果集进行筛选。
根据 GROUP BY t1.column1, t2.column2 对筛选后的结果进行分组。
使用 HAVING COUNT(t3.column3) > 2 对分组结果进行筛选。
从分组筛选后的结果中，选择 t1.column1、t2.column2 和 COUNT(t3.column3) 作为结果列，并将 COUNT(t3.column3) 重命名为 count_column3（SELECT t1.column1, t2.column2, COUNT(t3.column3) AS count_column3）。
对结果集进行升序排序（ORDER BY t1.column1 ASC）。
最后，仅返回前 10 行（LIMIT 10）。

8.创建用户及授权

1.创建用户
create user happy_mydata@127.0.0.1 identified by "bai666";
2.查询用户
select user,authentication_string,host from mysql.user;
3.删除用户
drop user happy_mydata@127.0.0.1;
4.修改用户
rename user mydata_happy to mysql_happy@localhost;
5.修改用户密码(两种方法)
set password for 'mysql_happy'@'localhost'  = '111111';
alter user 'mysql_happy'@'localhost' identified by 'new_password';
set password for mysql_happy@localhost=password("6666");(新版本已作废，仅适用于5.7.6以前的版本)
6.授权(先执行6后执行7才会完成授权)
grant all privileges on *.* to mysql_happy@localhost;
7.授权生效
flush privileges;
8.查看授权
show grants for mysql_happy@localhost;
9.取消授权(先执行9后执行7才会完成取消授权)
revoke all privileges on *.* from mysql_happy@localhost;

111