2-1 mysql基础语法

基本操作

# 单行注释1
-- 单行注释2

# 创建
CREATE TABLE class(
  id int NOT NULL AUTO_INCREMENT,
  class_name varchar(50) NOT NULL,
	class_teacher varchar(55) DEFAULT NULL COMMENT '老师',
  PRIMARY KEY (id)
);

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别 1-男 2-女\r\n',
  `class_id` int DEFAULT NULL COMMENT '班级编号',
  `grade` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 COMMENT='学生基本信息表';

# 查
SELECT * FROM student;
SELECT stu_name FROM student;

# 增
insert into student(stu_name, gender) VALUES("小李", 1) ;
insert into student(stu_name, gender) VALUES("小王", 2) ;
insert into student(stu_name, gender) VALUES("小黄", 2) ;
insert into class(class_name)VALUES("java");
insert into class set class_name = "C++", class_teacher = "罗翔";

# 改
UPDATE student set gender = 1 where grade = 90;

# 删
DELETE FROM student;
DELETE FROM student WHERE stu_name = '小王';
DELETE FROM class;

# 批量插入
INSERT into student(stu_name, gender)
SELECT stu_name, gender FROM student WHERE gender = 1;

# 聚合函数
-- 求总个数
SELECT COUNT(*) FROM student;
SELECT * FROM student;

-- 求平均值、最值、和
SELECT AVG(grade) as avg_grade FROM student;

SELECT MAX(grade) as max_grade FROM student;

SELECT MIN(grade) as min_grade FROM student;

SELECT sum(grade) as sum_grade FROM student;

# 保留小数后几位，四舍五入
SELECT ROUND(3.1415926, 4)

# 查看数据库版本
SELECT VERSION()

# 当前数据库所在服务器的时间
SELECT NOW()

# 日期格式化
SELECT "2021/2/12 13:45:32"
SELECT DATE_FORMAT("2021/2/12 13:45:32","%Y-%m-%d %H:%i:%s")

# 日期增加
SELECT DATE_ADD(NOW(),INTERVAL 1 day);	# 加一天
SELECT DATE_ADD(NOW(),INTERVAL -1 day);	# 减一天
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR);	# 加一小时

# 拼接字符
SELECT CONCAT(stu_name, gender) as tar FROM student;

# 关键字去重 distinct
SELECT * FROM student;
SELECT DISTINCT stu_name FROM student;
# 在distinct后边有多个字段时， 会对字段的组合去重
SELECT DISTINCT stu_name, id FROM student

关联、分组、排序、分页

# 1.多表联查
# 查询所有学生信息及其所在班级名称
SELECT
	a.id,
	a.stu_name,
	a.gender,
	b.cla_id,
	b.cla_name 
FROM
	stu_info AS a,
	cla_info AS b
WHERE
	a.cla_id = b.cla_id;
	
# 2.关联查询
# 左连接 LEFT JOIN
SELECT
	*
FROM
	stu_info AS a
	LEFT JOIN cla_info AS b ON a.cla_id = b.cla_id;
	
# 右连接 RIGHT JOIN
SELECT
	* 
FROM
	stu_info AS a
	RIGHT JOIN cla_info AS b ON a.cla_id = b.cla_id;
	
# 内连接 INNER JOIN   (效果等于多表联查)
SELECT
	* 
FROM
	stu_info AS a
	INNER JOIN cla_info AS b ON a.cla_id = b.cla_id;
	
# 主键与外键
# 主键具有唯一性， 可以是一个字段，也可以是多个字段的组合
# 外键可以有多个

# 分页 limit
SELECT * FROM stu_info LIMIT 1, 3;	# 从第二条开始，共查三条
SELECT * FROM stu_info LIMIT 3;			#从第一条开始查的简写
# pageNo页码 pageCount页数（每页查询的行数）
# 第一页 pageNo = 1, LIMIT (pageNo-1)*3, 3
SELECT * FROM stu_info LIMIT 0, 3;
# 第二页 pageNo = 2, LIMIT (pageNo-1)*3, 3
SELECT * FROM stu_info LIMIT 3, 3;
# 第三页 pageNo = 3, LIMIT (pageNo-1)*3, 3
SELECT * FROM stu_info LIMIT 6, 3;

# 排序
# 查询所有学生信息，要求按照成绩由高到低排序
SELECT * FROM stu_info ORDER BY grade DESC;
# 查询所有学生信息，要求按照成绩由低到高排序
SELECT * FROM stu_info ORDER BY grade ASC;

# 分组group by
# 1.分组往往结合聚合函数使用
# 2.对哪个字段进行分组， 查询出来的结果集，只展示当前分组的第一行
# 统计每个班的学生人数 
SELECT cla_id, COUNT(*) FROM stu_info GROUP BY cla_id;
# 每个班的第一个学生
SELECT * FROM stu_info GROUP BY cla_id;
# HAVING对GROUP BY分组之后的数据集进行筛选
# 学生所在的班级有哪些
SELECT cla_id FROM stu_info GROUP BY cla_id HAVING NOT ISNULL(cla_id);
# 统计每个班的女生人数
SELECT cla_id, COUNT(*) FROM
(SELECT * FROM stu_info WHERE gender=0) AS t
GROUP BY cla_id;


# 总结：WHERE/GROUP BY > ORDER BY > LIMIT
# 执行顺序：from---where--group by---having---select---order by---limit
# WHERE 必须放在 ORDER BY 前面
SELECT * FROM stu_info WHERE gender=0 ORDER BY grade DESC

# WHERE 必须放在 LIMIT 前面 
SELECT * FROM stu_info WHERE gender=0 LIMIT 2;

# GROUP BY必须放在 ORDER BY前面
# GROUP BY必须放在 LIMIT前面
SELECT gender, COUNT(*) as x FROM stu_info
GROUP BY gender
ORDER BY x DESC;

# ORDER BY 必须放在 LIMIT 前面
SELECT * FROM stu_info ORDER BY grade LIMIT 2;

# 子查询
# COLUMN IN (数据集) 判断字段的值是否在数据集中
SELECT * FROM stu_info WHERE cla_id 
IN(SELECT cla_id FROM cla_info)

表的设计操作

# 创建表 CREATE TABLE [table_name]();

# AUTO_INCREMENT 声明字段自动递增
# AUTO_INCREMENT=1 声明主键自增的起始值从1开始
# PRIMARY KEY 主键索引
# ENGINE=InnoDB 声明数据库引擎,其他引擎MyISAM
# CHARSET=utf8 声明数据表字符编码
# COMMENT 注释
# CHARACTER SET utf8 字符集
# COLLATE utf8_general_ci 字符排序规则
CREATE TABLE `stu_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键，值是唯一的不可重复',
  `stu_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别 1-男 0-女',
  `cla_id` int DEFAULT NULL COMMENT '班级',
  `grade` int DEFAULT '0' COMMENT '成绩',
  PRIMARY KEY (`id`,`stu_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb3 COMMENT='学生基础信息表';

# 删除表 DROP TABLE [table_name];
DROP table ce1;

# 修改表字段
# modify修改，修改表中某一字段的数据类型、长度、默认值、注释等
ALTER TABLE stu_info
MODIFY COLUMN stu_name text DEFAULT NULL COMMENT 'abc';

# 添加表字段 
ALTER TABLE stu_info_copy 
ADD COLUMN create_time datetime DEFAULT now() COMMENT '创建日期';

# 字段重命名 
# ALTER TABLE [t_name] 
# CHANGE [old_column] [new_column] [data_type] [default_value]
ALTER TABLE stu_info_copy 
CHANGE stu_name s_name varchar(50) DEFAULT '' COMMENT '用户名';

# 删除表字段
ALTER TABLE stu_info_copy 
DROP COLUMN stu_name;

分支循环

# IF语句
-- IF[判断条件] THEN [结果或结果集]
-- ELSEIF [判断条件] THEN [结果或结果集]
-- ELSE [结果或结果集]
-- END IF;

# 函数, 类似三元运算符
# IF([condition],[value if true],[value if false])

SELECT *, IF(gender=1,'男','女') as '性别' FROM stu_info WHERE id=3;

# CASE语句（类似switch）
-- CASE [字段]
-- WHEN [判断值] THEN [结果或结果集]
-- WHEN [判断值] THEN [结果或结果集]
-- ELSE [结果或结果集]
-- END;

SELECT *, CASE gender
	WHEN 1 THEN
		'男'
	WHEN 0 THEN
		'女'
	ELSE
	  '嘿嘿'
END AS '性别'
FROM stu_info;

# WHILE语句
--  WHILE [] DO
-- 	执行sql
-- END WHILE;

函数

1. 函数有且只有一个返回值
2. 函数返回值只能是一个值，不能是结果集

CREATE DEFINER=`root`@`localhost` FUNCTION `f_01`(`a` int,`b` int) RETURNS int
    DETERMINISTIC
BEGIN
	# 声明成员变量，关键字 DECLARE
	DECLARE res INT DEFAULT 0;
	# 赋值 关键字 SET
	SET res = a + b;
	
	# 会话变量，规定变量名前加一个 @符号
	# 会话变量不需要声明，直接赋值用就行
	set @tar = TRUE;

	WHILE @tar DO
		SET res = res + 1;
		IF res > 10 THEN
			SET @tar = FALSE;
		END IF;
	END WHILE;
	RETURN res;
END

存储过程

• 优点

1. 可以封装，隐藏了复杂的业务逻辑。
2. 可以预编译，提高数据库执行速度。存储过程只在创建时编译一次，之后每次执行存储过程都不需要再编译。

• 缺点

3. 不能跨平台，不同的数据库系统有不同的存储过程定义方法。
4. 难以调试。

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_01`(IN `a` int,IN `b` int)
BEGIN
	# 班级id
	DECLARE p_c_id int;
	# 最高成绩
	DECLARE p_max_grade int;
	# 查询宋丹丹老师的学生中，成绩最高的学生信息
	# 1 查询宋丹丹老师的任课班级
	SET p_c_id = (SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹');
	# 2 查找该班级最高的成绩
	SET p_max_grade = (
	SELECT MAX(grade) FROM stu_info WHERE cla_id = p_c_id);
	# 3 根据班级和成绩找到匹配的学生信息
	SELECT * FROM stu_info
	where cla_id = p_c_id AND grade = p_max_grade;
	SET a= a+1;
	SELECT a;
END

• 调用

CALL p_01(2, 1);

游标

1. 游标是一组有序的结果集。
2. mysql中，游标只能用于函数和过程。
3. mysql中，游标是单向、向前、逐个、只读的。

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_02`(IN `id` int)
BEGIN
	DECLARE p_id INT;
	DECLARE p_name varchar(55);
	
	# 声明变量，控制遍历游标的循环终止
	DECLARE curStatus INT DEFAULT 1;

	# 声明游标，该语句必须在所有sql执行语句（select/insert/update/delete）之前。
	DECLARE list CURSOR FOR SELECT id,stu_name FROM stu_info WHERE cla_id = id;
	
	# CONTINUE HANDLER 声明handler(处理器)，handler会自动地向下读取游标的一行记录
	# 当handler读不到数据时(not found即异常号02000)表明已经读完了游标的数据，此时设置curStatus = 0
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET curStatus = 0;
	
	# 开启游标
	OPEN list;
		# 向下读取一行数据，并将这行数据赋值给对应的变量
		FETCH list INTO p_id,p_name;
		#遍历游标
		WHILE curStatus = 1 DO
			# 执行sql语句
			INSERT INTO person_info SET name = p_name;
			FETCH list INTO p_id,p_name;
		END WHILE;
	# 关闭游标
	CLOSE list;
	SELECT * FROM person_info RIGHT JOIN stu_info ON person_info.`name` = stu_info.stu_name;
END