Mysql学习笔记（sql语句与基础知识自我整理）

Mysql笔记自我整理

前言：此文章为我本人前期学习sql语句时总结，是在我本人建立的数据库和表上进行的练习，因此还是有存在用意不明了的地方，请注意上下联系。

#1.创建数据库

CREATE DATABASE cjx_db01
CREATE DATABASE cjx_db02

#2.删除数据库,注意了！！！！！！=====慎重用啊，如果你给数据直接删了又没备份，你要从入门到入狱啊

DROP DATABASE cjx_db02

#3.创建一个使用utf-8字符集的数据库cjx_db03,并带上校对规则；

CREATE DATABASE cjx_db03 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

#4.查询的一条sql

SELECT * FROM t1 WHERE NAME = 'Tom'
SELECT * FROM t1 WHERE NAME = 'tom'

#5.查看数据库

SHOW DATABASES

#6.查看之前所创建的数据库信息

SHOW CREATE DATABASE cjx_db03

#7.备份数据库 mysqldump -u root -P -B 数据库 某表 某表 >文件地址

#8.创建表，举例如下

CREATE TABLE `user`(
        `id` INT,
				`name` VARCHAR(255),
				`passsword` VARCHAR(32),
				`birthday` DATE)
			CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE INNODB;--设置编码，储存引擎等

#9.创建一个让我自己学习sql语句的表，在db01数据库下创建student，进行SQL语句练习

#小数可以用DECIMAL(,)可控制高精度;
CREATE TABLE `student` (
        `id` INT,
				`name` VARCHAR(255),
				`gender` CHAR(32),
				`achievement` DOUBLE
)

#10.改变表的名字为students

ALTER TABLE student RENAME TO students;

#11.插入数据insert into

ALTER TABLE students ADD `grade` CHAR(32);/*增加列*/
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (1,'陈洁祥','man',99.55,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (2,'张三','man',35.5,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (3,'里斯','man',72,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (4,'蔡徐坤','woman',44,'一'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (5,'王八','man',55,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (6,'林允','woman',87,'一'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (7,'罗婕','woman',98,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (8,'陈雪','woman',92,'一'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (9,'赵武','man',62.5,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (10,'老子','man',72,'一'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (11,'二蛋','man',66,'二'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (12,'温婷','woman',60,'一'); 
INSERT INTO `students` (`id`,`name`,`gender`,`achievement`,`grade`) VALUES (13,'老六','man',47.5,'二'); 
ALTER TABLE students ADD `Teacher_Num` INT;
UPDATE students SET Teacher_Num = 1001 WHERE id = 1;
UPDATE students SET Teacher_Num = 1004 WHERE id = 2;
UPDATE students SET Teacher_Num = 1001 WHERE id = 3;
UPDATE students SET Teacher_Num = 1002 WHERE id = 4;
UPDATE students SET Teacher_Num = 1004 WHERE id = 5;
UPDATE students SET Teacher_Num = 1003 WHERE id = 6;
UPDATE students SET Teacher_Num = 1001 WHERE id = 7;
UPDATE students SET Teacher_Num = 1003 WHERE id = 8;
UPDATE students SET Teacher_Num = 1001 WHERE id = 9;
UPDATE students SET Teacher_Num = 1002 WHERE id = 10;
UPDATE students SET Teacher_Num = 1004 WHERE id = 11;
UPDATE students SET Teacher_Num = 1003 WHERE id = 12;
UPDATE students SET Teacher_Num = 1002 WHERE id = 13;
CREATE TABLE Teacher(
             teacher_id INT,
						 teacher_name VARCHAR(255));
INSERT INTO Teacher(teacher_id,teacher_name) VALUES (1001,'Jays');
INSERT INTO Teacher(teacher_id,teacher_name) VALUES (1002,'Tom');
INSERT INTO Teacher(teacher_id,teacher_name) VALUES (1003,'Jerry');
INSERT INTO Teacher(teacher_id,teacher_name) VALUES (1004,'Smith');

#12.修改数据，update

UPDATE students set achievement = 99.5 where name = '陈洁祥'

#13.基础关键字between and ,in(集合)

SELECT * from students where achievement BETWEEN 60 AND 100;
SELECT * from students where grade = '二';
SELECT * from students where id in(1,3,5,7,9);

#14. _:单个任意字符；%:多个任意字符

SELECT * from students where name like '陈%';
SELECT * from students where name like '_婷%';

#15.order by,ASC,DESC

SELECT * from students order by `achievement`;/*默认从小到大*/
SELECT * from students order by `achievement` desc;/*从大到小*/

#16.常见函数

/*
count：计算个数
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值
MD5(str),加密函数
*/
#用法如下(和分组查询一起吧group by)
SELECT count(*) from students;/*统计有多少行数据*/
SELECT gender as 性别,avg(achievement) from students group by gender;
SELECT grade ,sum(achievement) from students GROUP BY grade;

#17.多表查询

SELECT users.`name`,users.address,students.`name`,students.grade from users,students; 
SELECT * from users;
SELECT * from students;/*上下两行sql语句一起运行能拿到两个结果*/

SELECT students.`name`,students.achievement,Teacher.teacher_name from students,Teacher
							  WHERE students.Teacher_Num = Teacher.teacher_id;
								/*用老师代号将两个表信息联系输出，即学生信息及其对应的老师*/

#18.HAVING

SELECT * from students where achievement BETWEEN 60 AND 100 HAVING gender = 'man';

#19.时间类date，datetime，timestamp;
ALTER TABLE users ADD birthday date;/*年月日*/
ALTER TABLE users add job_time datetime ;/*年月日，时分秒*/
ALTER TABLE users add log_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
       ON UPDATE CURRENT_TIMESTAMP;/*按照当前时间更新*/
update users set birthday = '2023-10-28',job_time = '2023-11-11 10:10:10'
       where id IN(1,2);

#20.SELECT语句具体情况

SELECT * from students /*带*号，表示选择查询全部列*/
SELECT DISTINCT * from students;/*若某行数据有重复，只显示一行*/
SELECT id,`name`,`gender`,`grade` from students where id IN(1,2,3,4);/*只显示指定列与行*/
#为了他人方便看所查询出的列名，我们用as，改变查询出的列名
SELECT `gender`,avg(achievement) from students GROUP BY `gender`;
SELECT `gender` as '性别',avg(achievement) as '成绩' from students GROUP BY `gender`;/*查看与上语句区别*/

#21.比较重要的字符串函数：

/*
CONCAT(str1,str2,...);拼接类型为字符串的列；
REPLACE(str,from_str,to_str)替代
*/ 
SELECT CONCAT(`name`,'的性别是',gender) from students where `name` = '蔡徐坤';
SELECT `name`,REPLACE(gender,'woman','man') from students where `name` = '蔡徐坤';
/*例如你要输出表所需要的一个字符串全部更改为某字符串，可以用此方法，且不更改原表内容*/

#22.流程控制语句

/*
IF(expr1,expr2,expr3),如果expr1为true,则expr2，否则，expr3
IFNULL(expr1,expr2),如果expr1为true，则expr2
SELECT CASE WHEN expr1 THEN expr2,WHEN expr3 THEN expr4,ELSE expr5;类似java多重分支
*/
SELECT `name` as 姓名,achievement as 成绩,IF(achievement BETWEEN 60 AND 100,'优秀','差劲') as 水平 
       from students ORDER BY achievement desc;
			 /*意思就是把60-100分的同学评级为优秀，其余为差劲*/

#23.分页查询limit,记住，类似方言，记住用法，比较抽象
/*
（当前的页码-1）*每页显示的条数
*/
SELECT * from students ORDER BY achievement desc LIMIT 0,3;–先把数据按照成绩高低从大到小排序，然后显示第一页前三条

#24.WHERE 和 HAVING区别(注意)
/*
HAVING和WHERE是SQL查询中用于过滤数据的两个子句，它们在功能上有一些区别：

1. WHERE子句：

   • WHERE子句用于在执行SQL查询之前对表中的数据进行过滤。
     它筛选出满足条件的行，并且只有满足WHERE子句中指定的条件的行才会进入查询结果集。
   • 它通常用于过滤行，根据指定的条件来检索符合条件的数据行。
   • [[在SELECT语句中，WHERE子句出现在FROM子句之后，GROUP BY子句之前。]]

2. HAVING子句：

   • HAVING子句用于在执行SQL查询后，对GROUP BY分组的结果进行过滤。它对分组后的结果应用过滤条件。
   • 它通常用于对分组后的数据进行过滤，根据聚合函数（例如SUM、COUNT、AVG等）的计算结果来筛选结果。
   • [[在SELECT语句中，HAVING子句出现在GROUP BY子句之后。]]

示例：
假设有一个表格"orders"包含以下字段：order_id, customer_id, order_date, total_amount。
现在我们想查询每个customer_id对应的订单总金额大于100的客户：

-- 使用 WHERE 子句过滤行
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE total_amount > 100
GROUP BY customer_id;

-- 使用 HAVING 子句过滤分组后的结果
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 100;

在第一个示例中，使用WHERE子句直接筛选出订单总金额大于100的行。而在第二个示例中，
使用HAVING子句先对数据进行分组（按customer_id），然后在分组后的结果中筛选出订单总金额大于100的客户。

SELECT column1,column2... from TABLE GROUP BY column
                               HAVING CONDITION
															 ORDER BY COLUMN
															 limit st
*/
SELECT gender, AVG(achievement) AS 平均成绩
               FROM students
               WHERE achievement >= 60 AND grade = '二'
               GROUP BY gender
               ORDER BY 平均成绩 DESC
               LIMIT 0,2;

#26.sql语句顺序（注意）
/*
在执行SQL查询时，通常按照以下顺序逐步解析和执行语句的不同子句：

1. SELECT: 指定要检索的列或表达式。
2. FROM: 指定查询的数据来源，即要查询的表或视图。
3. JOIN: 用于将多个表连接在一起，以便在查询中使用多个表的数据。
4. WHERE: 在执行聚合前对数据进行过滤。
5. GROUP BY: 按指定列进行分组。
6. HAVING: 在聚合后对分组的结果进行过滤。
7. ORDER BY: 指定结果集的排序顺序。
8. LIMIT/OFFSET: 限制结果集的行数和起始行位置（可选）。

绝大多数SQL查询都会涵盖这些子句，但并非所有的查询都需要使用所有子句。下面是一个示例查询，展示了SQL语句的一般顺序：

SELECT column1, column2, ...
FROM table1
JOIN table2 ON ...
WHERE condition1
GROUP BY column1
HAVING condition2
ORDER BY column3
LIMIT 10;

需要注意的是，每个查询都应根据具体的需求进行定制。在实际编写SQL查询时，可以根据情况调整子句的顺序或添加其他子句，以满足查询的要求。
*/

#27.自连接，单表当作多表用

SELECT * from students students1 , students students2; /*13*13=169行数据*/
SELECT students1.`name`,students2.gender 
       from students students1,students students2
			 /*WHERE 过滤语句*/

#28.子查询

/*例如查询表中成绩低于平均成绩的同学*/
SELECT * from students 
         WHERE achievement < (SELECT avg(achievement) FROM students)
				 ORDER BY achievement DESC;
/*查询名字叫蔡徐坤和陈洁祥的信息*/
SELECT * FROM students 
         WHERE `name` in(SELECT `name` from students WHERE `name`='蔡徐坤' OR `name`='陈洁祥');

#29.临时表

/*SELECT 字段 from (SELECT	字段 FROM TABLE....)temp,TABLE2
              WHERE........ 				 		
*/

#30.all 和 any;可换为函数 MAX 和 MIN

SELECT `name`,achievement FROM students 
                          WHERE achievement > (SELECT avg(achievement) from students
													                     WHERE grade = '二');
/*上面语句为查询成绩大于二年级平均成绩的同学信息*/
SELECT avg(achievement) from students WHERE grade='二';--由此见二年级平均成绩为67分，检查上面是否正确
/*any其实就是只要满足一个就可以了，类似逻辑语句中的任意和存在*/

#31.表的复制,把students复制到表student

CREATE TABLE student like students;
INSERT INTO student SELECT * from students;
SELECT * from student;

#32.合并查询（1）UNION ALL,不去重；（2）UNION,去重

#33.外连接（左、右）

#先建立表格方便联系外连接
CREATE TABLE stu(
       id INT,
			 `name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');

CREATE TABLE exam(
       id INT,
			 achievement INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);

/*此时要求是需要显示学生ID姓名和成绩，若某学生没成绩则显示为空*/
SELECT stu.id,stu.`name`,exam.achievement
       from stu left join exam
			 ON stu.id = exam.id;--(左外连接)
/*此时要求显示所有成绩，若有的成绩没有对应id，则学生显示为空*/
SELECT stu.id,stu.`name`,exam.achievement 
       from stu right JOIN exam
       ON stu.id = exam.id;--(右外连接)		

#34.约束–主键、外键（实际工作用的少了不方便开发管理这里不提了）、check（mysql8.0可用）、unique、not null等，后面两个定义类似，这里就不提了

/*
定义主键：SELECT TABLE t (
                id INT PRIMARY KEY,
								);
			  --若要定义复合主键
				SELECT TABLE t (
				       id INT,
							 `name` VARCHAR(32)
							 PRIMARY KEY(id,`name`));
主键：（1）一张表只能有一个主键
     （2）主键不能重复、不能为空
		 （3）主键可为复合主键(意味着在id和name相同的情况下，才会不能添加，其中一个不同还是可以添加的)
		 
CHECK定义:SELECT TABLE t (
                 id INT,
								 `name` VARCHAR(32),
								 gender VARCHAR(6) CHECK(gender IN('man','woman'))
								 sal DOUBLE CHECK(sal >= 10000 AND sal <= 12000));
								 --即给定变量一个范围
*/

#35.自增长 AUTO_INCREMENT,例如在定义表的时候 id INT PRIMARY KEY AUTO_INCREMENT

#36.索引

--（1）唯一索引 CREATE UNIQUE INDEX id_index ON TABLE1 （id）;
--（2）普通索引	CREATE INDEX id_index ON TABLE1 (id);
--还可以这么写：ALTER TABLE table1 ADD INDEX id_index (id);
--（3）主键索引其实就是和设置主键一样，主键定义了那么它就是一个主键索引；
--（4）查看索引：SHOW INDEX FROM table1;
--（5）删除索引：DROP INDEX 索引名 ON table1;
      删除主键索引：ALTER TABLE 表名 DROP PRIMARY KEY;

#37.事务–表要在innodb引擎下

-- 创建测试表t4	
CREATE TABLE t4 (
       id INT,
			 `name` VARCHAR(32)); 
-- 开启事务
START TRANSACTION;
-- 设置保存点a
SAVEPOINT a;
-- 执行dm1操作
INSERT INTO t4 VALUES(1,'jays');
-- 设置保存点b
SAVEPOINT b;
-- 执行dm2操作
INSERT INTO t4 VALUES(2,'tom');
SELECT * from t4;
-- 回退至保存点b;
ROLLBACK TO b;
SELECT * from t4;
-- 回退至保存点a;
ROLLBACK TO a;
SELECT * from t4;
-- ROLLBACK:回退到事务开启之前的状态
ROLLBACK;
-- COMMIT:提交开启事务后的所有操作造成的改动
COMMIT;

#38.隔离级别，隔离是和事务相关的，没事务就别提隔离级别
– 创建测试表t5

CREATE TABLE t5 (
       id INT,
			 `name` VARCHAR(32),
			 money INT);
-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 查看系统隔离级别
SELECT @@global.transaction_isolation;
-- 设置会话/系统隔离级别
SET SESSION / GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
-- 但是默认的 REPEATABLE READ一般情况下不必修改，因为能满足绝大多数项目要求

#39.表的存储类型
– 常见MyISAM、InnoDB、Memory;
– 其中InnoDB是事务安全型；
– MyISAM:批量插入速度 高，锁机制 表锁，内存 无限制；
– InnoDB:批量插入速度 低，锁机制 行锁，内存 64TB；占用空间、内存大；
– Memory:批量插入速度 高，锁机制 表锁，内存 有；