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查询中用于过滤数据的两个子句,它们在功能上有一些区别:
-
WHERE
子句:WHERE
子句用于在执行SQL查询之前对表中的数据进行过滤。
它筛选出满足条件的行,并且只有满足WHERE
子句中指定的条件的行才会进入查询结果集。- 它通常用于过滤行,根据指定的条件来检索符合条件的数据行。
- [[在
SELECT
语句中,WHERE
子句出现在FROM
子句之后,GROUP BY
子句之前。]]
-
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查询时,通常按照以下顺序逐步解析和执行语句的不同子句:
SELECT
: 指定要检索的列或表达式。FROM
: 指定查询的数据来源,即要查询的表或视图。JOIN
: 用于将多个表连接在一起,以便在查询中使用多个表的数据。WHERE
: 在执行聚合前对数据进行过滤。GROUP BY
: 按指定列进行分组。HAVING
: 在聚合后对分组的结果进行过滤。ORDER BY
: 指定结果集的排序顺序。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:批量插入速度 高,锁机制 表锁,内存 有;