# #号注释
-- 双横杠注释
-- 创建班级表
CREATE TABLE cla_info(
cla_id int NOT NULL auto_increment COMMENT '班级主键',
cla_name varchar(50) NOT NULL COMMENT '班级名称',
PRIMARY KEY(cla_id)
) COMMENT '班级基础信息表';
-- 增删改查CRUD
-- 查询(查)
-- *代表查询全部字段
SELECT * FROM stu_info;
SELECT name FROM stu_info;
-- 添加(增)
INSERT INTO stu_info(stu_name, sex)VALUES('张三', 1);
INSERT INTO stu_info(stu_name, sex)VALUES('李四', 0);
-- 更新(改)
UPDATE stu_info SET grade = 89 WHERE id = 1;
UPDATE stu_info SET grade = 100 WHERE stu_name = '张三';
-- 删除(删)
DELETE FROM stu_info;-- 删库跑路
DELETE FROM stu_info WHERE id = 4;
-- 添加
INSERT INTO cla_info(cla_name)VALUES('艺术班');
-- 插入的另一种写法
IN INTO cla_info SET cla_name = '舞蹈班',cla_teacher = '罗翔';
-- 批量插入
INSERT INTO stu_info (stu_name,sex)
SELECT u_name, u_sex FROM user_info;
-- 聚合函数
-- 求总行数
SELECT COUNT(*) FROM stu_info;
-- 求平均值
SELECT AVG(grade) AS avg_grade FROM stu_info;
-- 最大值
SELECT MAX(grade) FROM stu_info;
-- 最小值
SELECT MIN(grade) FROM stu_info;
-- 求和
SELECT SUM(grade) FROM stu_info;
-- 保留小数后几位,四舍五入
SELECT ROUND(3.14159, 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');
-- 日期增加
-- 增加1天
SELECT DATE_ADD(NOW(),INTERVAL 1 day);
-- 减少1天
SELECT DATE_ADD(NOW(),INTERVAL -1 day);
-- 增加1小时
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR);
-- 拼接字符串
SELECT CONCAT(stu_name,grade) as tar FROM stu_info;
-- 去除重复关键字distinct
-- 对单个字段去重
SELECT DISTINCT stu_name FROM stu_info;
-- 在distinct后面有多个字段,会对多个字段的组合进行去重
SELECT DISTINCT id, stu_name FROM stu_info;