MYSQL常用SQL语句

于 2022-11-11 12:15:00 发布
阅读量200 收藏
点赞数
分类专栏： MYSQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/NiKaBoy/article/details/127705555
版权
本文详细介绍了MySQL数据库的创建、删除、表操作以及查询语句的使用，包括增删改查、分组、联接、排序、分页、函数应用等核心概念，并展示了如何使用存储过程和索引来提升效率。内容涵盖了基础操作到高级技巧，是学习和工作中不可或缺的参考资料。
摘要由CSDN通过智能技术生成

  1. 创建数据库

    create database mysql_test;
-- 使用库
use mysql_test;

  2. 删除数据库

    DROP DATABASE mysql_test;

  3. 创建表

    DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '学生名字',
  `snum` bigint(11) NOT NULL COMMENT '学号',
  `cid` bigint(11) NOT NULL COMMENT '班级id',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `gender` char(2) CHARACTER SET utf8mb4 NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  4. 删除表

    DROP TABLE student;

  5. 查询单表

    SELECT * FROM student;

  6. 增删改

    -- 增加一个学生
INSERT INTO `student` (`name`, `snum`, `cid`, `birthday`, `gender`) VALUES ('王路飞', '20229104321', '1', '2000-01-01 21:03:45', '1');

-- 修改 根据id 学生名字
UPDATE `student` SET `name`='小王' WHERE (`id`='1');

-- 删除 根据id  学生 
DELETE FROM `student` WHERE (`id`='1');

  7. 新增字段

     ALTER TABLE student ADD id_card  VARCHAR(18) DEFAULT  NULL COMMENT '身份证号';

  8. 修改字段

    
-- 修改字段和字段类型 ALTER TABLE [表] CHANGE [旧字段] [新字段] [类型] ;
ALTER TABLE student CHANGE natian nation VARCHAR (32)  DEFAULT '汉族';

  9. 分组

    -- 根据班级id分组
SELECT * FROM student stu  GROUP BY  stu.cid;
-- 查询每个班级学生数量
SELECT stu.name,stu.cid, COUNT(stu.id) as 学生数量 FROM student stu  GROUP BY  stu.cid;

-- 查询学生的平均成绩
SELECT
	stu.snum,
	stu. NAME,
	AVG(g.grade) AS avg_grade
FROM
	student stu
LEFT JOIN grade g ON g.s_id = stu.id
LEFT JOIN course c ON c.id = g.c_id
WHERE
	stu.id = '269999'
GROUP BY
	stu.id
	
-- 查询学生所有科目总成绩
SELECT
	stu.snum,
	stu. NAME,
	SUM(g.grade) AS avg_grade
FROM
	student stu
LEFT JOIN grade g ON g.s_id = stu.id
LEFT JOIN course c ON c.id = g.c_id
WHERE
	stu.id = '269999'
GROUP BY
	stu.id

在这里插入图片描述

  1. left join (左连接 左边满足条件的都要，右表要交集，右不存在的部分补null)

在这里插入图片描述

```mysql
SELECT
	stu.*,
	c.*
FROM
	student stu
LEFT JOIN clazz c ON c.id = stu.cid

```

在这里插入图片描述
在这里插入图片描述

  1. right join(右连接 与左连接相反 ）

在这里插入图片描述

```mysql
SELECT
	stu.*,
	c.*
FROM
	student stu
RIGHT  JOIN clazz c ON c.id = stu.cid
```

在这里插入图片描述

  1. inner join（内连接 左边和右表都存在的部分）

在这里插入图片描述

```mysql
SELECT
	stu.*, c.*
FROM
	student stu
INNER JOIN clazz c ON c.id = stu.cid
```

在这里插入图片描述

  1. UNION ALL 和 UNION （将两张表连起来 union all 不过滤重复数据 union 过滤重复数据）

    
--  UNION ALL 不去重

SELECT
	*
FROM
	student stu
WHERE
	stu.gender = 0
UNION ALL
	SELECT
		*
	FROM
		student s
	WHERE
		s.gender = 0
	OR s.gender = 1;
	
	--  UNION 去重
	SELECT
	*
FROM
	student stu
WHERE
	stu.gender = 0
UNION 
	SELECT
		*
	FROM
		student s
	WHERE
		s.gender = 0
	OR s.gender = 1;

  2. 排序

    -- DESC 降序 从大到小    ASC 升序 从小到大
select * from student stu ORDER BY stu.snum ASC

  3. 分页

    -- 从第一条数据开始查出10条数据 （起始是0）   0是起始位置 10是行数数量 
select * from student stu ORDER BY stu.snum ASC  LIMIT 0,10

-- 从第二条数据开始查出10条数据 
select * from student stu ORDER BY stu.snum ASC  LIMIT 1,10

  4. 查看当天数据

    select * from student where TO_DAYS(create_date) = TO_DAYS(NOW());

  5. 查看昨天的数据

    select * from student where  TO_DAYS(NOW()) - TO_DAYS(create_date) = 1

  6. 获取到多组中时间最新的数据

    --  必须加limit 10000 ,不然分组后面的条件都失效
-- 查询出每个班级中年龄最大(出生日期最小)的学生
SELECT
	*
FROM
	(
		SELECT
			stu.*
		FROM
			student stu
		LEFT JOIN clazz c ON stu.cid = c.id ORDER BY stu.birthday ASC LIMIT 100000000
	) s
GROUP BY
	s.cid

-- 查询出每个班级中年龄最小（出生日期最大）的学生

	SELECT
	*
FROM
	(
		SELECT
			stu.*
		FROM
			student stu
		LEFT JOIN clazz c ON stu.cid = c.id ORDER BY stu.birthday DESC LIMIT 100000000
	) s
GROUP BY
	s.cid
	
-- 也可以换个思路，先分组查出最大/最小的年龄，再根据这个字段in查询到学生的信息分组或者去重防止重复数据

SELECT
	*
FROM
	student s
WHERE
	s.birthday IN (
		SELECT
			min(student.birthday) AS maxTime
		FROM
			student
		GROUP BY
			student.cid
	)
 GROUP BY s.cid

  7. mysql的执行顺序

    • from子句识别查询表的数据；

    • where子句基于指定的条件对记录进行筛选；

    • group by 子句将数据划分成多个组别，如按性别男、女分组；

    • 有聚合函数时，要使用聚集函数进行数据计算；

    • Having子句筛选满足第二条件的数据；

    • 执行select语句进行字段筛选

    • 筛选重复数据；

    • 对数据进行排序；

    • 执行limit进行结果限定

      FROM > WHERE >GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

  8. mysql的语法顺序

    SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

  9. 存储过程（插入数据）

    -- mysql 创建存储过程插入测试数据 
create procedure man()
BEGIN
    declare i int default 10000;
    while(i<99999) do
				INSERT INTO `mysql_test`.`student` (`name`, `snum`, `cid`, `birthday`, `gender`) VALUES ('nikaboy', concat('202291',i), '2', '2000-11-01 21:03:45', '1');
        set i=i+1;
    end while;
END;
$
delimiter ;

  10. 先查出来再插入

    场景a表符合条件的数据需要导入到b表中。

    -- 格式 INSERT INTO tab1 (....) select [field1],[field2],[field2] FROM tab2 WHERE [condition]
INSERT INTO student (
	`name`,
	cid,
	snum,
	birthday,
	gender,
	id_card,
	nation
) SELECT
	tab_a.`name`,
	tab_a.cid,
	tab_a.snum,
	tab_a.birthday,
	tab_a.gender,
	tab_a.id_card,
	tab_a.nation
FROM
	student tab_a
WHERE
	tab_a.cid = 2

  11. 先查出来再更新

    场景a表符合条件的数据需要更新到b表符合条件的数据。

    -- 格式 UPDATE tab1 ,tab2 SET tab1.field1 = tab2.field1 WHERE [condition1] [condition2]
UPDATE clazz tab_a
INNER JOIN (
	SELECT
		*
	FROM
		clazz z
	WHERE
		z.id = 2
) tab_b ON tab_a.college = tab_b.college
SET tab_a.`name` = tab_b.`name`,
 tab_a.major = tab_b.major
WHERE
	tab_a.id = 4

  12. IF 使用 和 elseif

    -- IF (条件,为真的返回结果,为假的返回结果)
SELECT
	stu.`name`,
	stu.snum,
IF ( stu.gender = 1,'男','女') AS gender
FROM
	student stu
	
-- 	mysql的 elseif
SELECT
	stu.cid,
	stu.`name`,
	CASE stu.gender
WHEN 1 THEN
	'男性'
WHEN 0 THEN
	'女性'
ELSE
	'人妖'
END AS gender
FROM
	student stu

  13. 设置索引

    -- 查看表存在的索引
show index from student;

    • 主键索引

      -- 添加主键索引
ALTER TABLE clazz  ADD PRIMARY KEY (id);

-- 删除主键索引
ALTER TABLE clazz drop PRIMARY KEY ;

    • 唯一索引

      -- 增加唯一索引
ALTER TABLE student ADD UNIQUE INDEX unique_snum (snum);
-- 删除唯一索引
ALTER TABLE student DROP INDEX unique_snum ;
DROP INDEX unique_snum ON student;

    • 普通索引

      -- 增加普通索引
ALTER TABLE student ADD  INDEX common_index_name (`name`);
-- 删除索引
DROP INDEX common_index_name ON student;

    • 全文索引

      • 全文索引 ：全文索引目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX
        使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

      由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。

      -- 创建全文索引
ALTER TABLE student ADD FULLTEXT INDEX fulltext_index_name (`name`);
-- 删除全文索引
DROP INDEX fulltext_index_name ON student;

    • 联合索引

      MYISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎可以支持HASH和BTREE索引

      -- 联合索引 使用联合索引 有最左规则 注意
ALTER TABLE `student` ADD INDEX `unite_snum_name` (`snum`, `name`) USING BTREE

-- 创建全文索引
DROP INDEX unite_snum_name ON student;

  14. mysql的一些函数

    -- CONCAT('','',...) 拼接字符串
select * from student stu WHERE stu.`name` LIKE CONCAT('%','路','%')

-- 从左边开始截取8位字符 LEFT(field,length)
select stu.`name`,LEFT(stu.snum,8) as snum  FROM student stu WHERE id='269999' 

-- 从右边开始截取8位字符 RIGHT(field,length)
select stu.`name`,RIGHT(stu.snum,8) as snum  FROM student stu WHERE id='269999'

-- 截取字符串 substring(field,index,length) //注意index不是从0开始，从1开始
select stu.`name`,substring(stu.snum,1,8) as snum  FROM student stu WHERE id='269999'

  15. 其他
    …

NiKaBoy
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值