使用docker跑一个mySQL镜像,设置数据挂载目录和root账户密码：
如果navicat报错Authentication plugin ‘caching_sha2_password‘ cannot be loaded则在mySql容器中依次输入：
- (登录mysql)
mysql -h localhost -u root -p
2.修改身份验证类型(修改密码)
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
#刷新权限
FLUSH PRIVILEGES;
然后可以正常使用navicat连接。
新建数据库和表
- 在navicat中新建
hello-mysql数据库，新建一个查询 - 先建立一个表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
name VARCHAR(50) NOT NULL COMMENT '学生名',
gender VARCHAR(10) NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
class VARCHAR(50) NOT NULL COMMENT '班级名',
score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4
id为主键，且设置自动增长
name为名字，非空
gender为性别，非空
age为年龄，非空
class为班级名，非空
score为成绩，非空
3. 插入一些数据:
INSERT INTO student ( NAME, gender, age, class, score )
VALUES
( '张三', '男', 18, '一班', 90 ),
( '李四', '女', 19, '二班', 85 ),
( '王五', '男', 20, '三班', 70 ),
( '赵六', '女', 18, '一班', 95 ),
( '钱七', '男', 19, '二班', 80 ),
( '孙八', '女', 20, '三班', 75 ),
( '周九', '男', 18, '一班', 85 ),
( '吴十', '女', 19, '二班', 90 ),
( '郑十一', '男', 20, '三班', 60 ),
( '王十二', '女', 18, '一班', 95 ),
( '赵十三', '男', 19, '二班', 75 ),
( '钱十四', '女', 20, '三班', 80 ),
( '孙十五', '男', 18, '一班', 90 ),
( '周十六', '女', 19, '二班', 85 ),
( '吴十七', '男', 20, '三班', 70 ),
( '郑十八', '女', 18, '一班', 95 ),
( '王十九', '男', 19, '二班', 80 ),
( '赵二十', '女', 20, '三班', 75 );
基础练习
- 指定查询的列：
SELECT name,score FROM student;
- 通过
as为返回列指定别名
SELECT name as '名字',score as '分数' FROM student;
- 带条件查询
SELECT name AS
'名字',
class AS '班级'
FROM
student
WHERE
age >= 19;
- 多个条件使用and连接：
SELECT name as '名字',class as '班级' FROM student WHERE gender='男' and score>=90;
- 使用
like做模糊查询
//查询名字以“王”开头的学生
SELECT * FROM student WHERE name LIKE '王%';
- 通过in指定一个集合
//查询一班和二班的所有学生
SELECT * FROM student WHERE class in('一班','二班')
- 通过
not in来查询不在指定集合中的数据：
SELECT * FROM student WHERE class NOT IN ('一班','二班')
- 通过
between and来指定一个区间
SELECT * FROM student WHERE age BETWEEN 18 AND 20;
- 分页返回
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,5;
- 通过
order by来指定排序的列
//order by 指定根据 score 升序排列，如果 score 相同再根据 age 降序排列。
SELECT name,score,age FROM student ORDER BY score asc,age desc;
- 使用
group by分组查询
SELECT
class AS '班级',
AVG( score ) AS '平均成绩'
FROM
student
GROUP BY
class
ORDER BY
'平均成绩' DESC;
这里用到不少新语法：
根据班级来分组是 GROUP BY class。
求平均成绩使用 sql 内置的函数 AVG()。
之后根据平均成绩来降序排列。
count内置函数
SELECT
class,
count( * ) AS count
FROM
student
GROUP BY
class;
having过滤查询结果
SELECT
class,
AVG( score ) AS avg_score
FROM
student
GROUP BY
class
HAVING
avg_score > 90;
14.distinct去重
SELECT DISTINCT class FROM student;
- 过一遍所有内置函数
函数分为如下几类：
-
聚合函数(用于对数据的统计),比如AVG、COUNT、SUM、MIN、MAX
SELECT
class AS '班级',
avg( score ) AS '平均成绩',
count( * ) AS '人数',
sum( score ) AS "总成绩",
min( score ) AS '最低分',
max( score ) AS '最高分'
FROM
student
GROUP BY
class
-
字符串函数(用于对字符串的处理) 比如CONCAT、SUBSTR、LENGTH、UPPER、LOWER
SELECT
CONCAT( 'xx', NAME, 'yy' ),
SUBSTR( NAME, 2, 3 ) as '名字',
LENGTH( NAME ),
UPPER( 'aa' ),
LOWER( 'TT' )
FROM
student;
其中，substr 第二个参数表示开始的下标（mysql 下标从 1 开始），所以 substr('一二三',2,3) 的结果是 '二三'。
当然，也可以不写结束下标 substr('一二三',2)
-
数值函数(用于对数值的处理) 比如ROUND、CEIL、FLOOR、ABS、MOD
SELECT
ROUND( 1.234567, 2 ),
CEIL( 1.234567 ),
FLOOR( 1.234567 ),
ABS( - 1.234567 ),
MOD ( 5, 2 );
分别是 ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取模。
-
日期函数:(对日期、时间进行处理),比如DATE、TIME、YEAR、MONTH、DAY
SELECT YEAR
( '2023-06-01 22:06:03' ),
MONTH ( '2023-06-01 22:06:03' ),
DAY ( '2023-06-01 22:06:03' ),
DATE( '2023-06-01 22:06:03' ),
TIME( '2023-06-01 22:06:03' );
-
条件函数：根据条件是否成立返回不同的值,比如IF、CASE
SELECT NAME
,
IF
( score >= 60, '及格', '不及格' )
FROM
student;
//case案例
SELECT NAME
,
score,
CASE
WHEN score >= 90 THEN
'优秀'
WHEN score >= 60 THEN
'良好' ELSE '差'
END AS '档次'
FROM
student;
-
系统函数（用于获取系统信息）比如VERSION、DATABASE、USER
select VERSION(), DATABASE(), USER()
-
其他函数NULLIF、COALESCE、GREATEST、LEAST
NULLIF：如果相等返回 null，不相等返回第一个值。
COALESCE：返回第一个非 null 的值：
GREATEST、LEAST：返回几个值中最大最小的。
select NULLIF(1,1), NULLIF(1,2);
select COALESCE(null, 1), COALESCE(null, null, 2);
select GREATEST(1,2,3),LEAST(1,2,3,4);
- 类型转换函数(转换类型为另一种),比如
CAST、CONVERT、DATE_FORMAT、STR_TO_DATE