mysql数据库操作

1. 添加数据库字段
   alter table 表名 add column 字段名 字段类型 comment ‘注释信息’
   如：alter table dev_app_info add column download_amount int comment ‘app下载次数 （总共下载次数）’ default 0
2. 更新某个字段的值为原来的值加1
   update dev_app_info set download_amount = download_amount + 1 where id = 1
3. mysql： left join right join full join inner join
   左连接：列出主表所有的信息，只列出右表对应的信息
   右连接：列出主表所有的信息，只列出左表对应的信息
   全连接：列出两个表所有的信息
   内连接：只列出两个表对应的信息
4. 创建索引、视图、存储过程
   4.1 索引
   4.1.1 创建索引 create index index_name on student (sno)
   4.1.2 删除索引 alter table student drop index index_name
   联合索引遵循最左原则
   4.2 视图
   4.2.1 创建视图 create view view_name as select * from sys_user
   4.2.2 查询视图 select * from view_name
   4.2.5 删除视图 drop view view_naem
   备注：当原表数据变动的时候，视图的结果也会变动，保持和原表一致
   4.3 存储过程

delimiter$$
create procedure dd(in sid int)
begin
select *  from student_course
where id=sid;
end $$
delimiter ;

调用：call dd(6);
删除存储过程： drop procedure dd

5. exists用法
   5.1 select * from course as cou
   where exists(
   select * from student_course as sc where cou.id = sc.course_id
   )

6. mysql中as的用法：
   select stu.id, stu.name from student as stu where stu.name = ‘tom’
   select stu.id as idtest, stu.name as nametest from student as stu where stu.name = ‘tom’
   select stu.id idtest, stu.name nametest from student stu where stu.name = ‘tom’
   通过上面的三个sql语句，说明用不用as 都是可以的

7. You can’t specify target table ‘student_course’ for update in FROM clause这种问题的解决办法
   7.1 出现这种问题是因为：先查询出数据，然后根据查询的数据再进行更新删除等操作
   7.2 解决办法是： 增加中间表
   原来的sql: delete from student_course
   where id in
   (select sc.id as id from student_course as sc
   left join teacher as tea on sc.teacher_id = tea.id
   where tea.name = ‘su’
   )
   报：ou can’t specify target table ‘student_course’ for update in FROM clause
   修改后的sql:
   delete from student_course
   where id in
   (
   select id from
   (select sc.id as id from student_course as sc
   left join teacher as tea on sc.teacher_id = tea.id
   where tea.name = ‘su’) as test01
   )

8. mysql： case when then else end 用法

   case student_id
   when ‘条件1’ then ‘值1’
   when ‘条件2’ then ‘值2’
   when ‘条件3’ then ‘值3’
   when ‘条件4’ then ‘值4’
   else ‘值5’
   end
   例子：
   select id，
   case student_id
   when ‘10001’ then ‘tom’
   when ‘10002’ then ‘json’
   when ‘10003’ then ‘ak’
   when ‘1004’ then ‘km’
   else ‘mk’
   end
   from student_course
   例子：
   select course_id, avg(score),
   sum
   (case
   when score > 90 then 1
   else 0
   end )/count(*) as 及格百分数
   from student_course group by course_id order by avg(score)

9. != <> 都是不等于的意思，用法没有差别

10. LIMIT 0, 10
    LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

11. between and
    数字范围: 包含头尾，相当于：x>=y && x<=z
    date类型: 当于 x >= y && x<=z
    datetime类型: 相当于 x >= y && x<z

12. mysql COUNT
    COUNT(常量) 和 COUNT() 表示的是直接查询符合条件的数据库表的行数。对于count(1)和count()，MySQL的优化是完全一样的，根本不存在谁更快！但依旧建议使用count(*)，因为这是SQL92定义的标准统计行数的语法，这些优化的前提都是没有进行where和group的条件查询。
    COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数，COUNT(字段)需要进行字段的非NULL判断，所以效率会低一些。

13. mysql 的日期函数
    时间转字符串 select date_format(now(), ‘%Y-%m-%d’); 结果：2021-07-20
    字符串转时间 select str_to_date(‘2021-07-20’, ‘%Y-%m-%d %H’); 结果：2021-07-20 00:00:00
    时间转时间戳 select unix_timestamp(now()); 结果：1626765289
    时间戳转时间 select from_unixtime(1626765289); 结果 2021-07-20 15:14:49
    字符串转时间戳 select unix_timestamp(‘2021-07-20’); 结果 1626710400
    时间戳转字符串 select from_unixtime(1626710400,‘%Y-%d’); 结果 2021-20

14. mysql复制表
    insert into student_course select * from student_course_copy1
    特点是这连个表的结构一样，如果不一样则要对表中的字段进行操作

15. This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
    百度翻译为：这个版本的MySQL还不支持LIMIT&IN/ALL/ANY/SOME子查询
    我原始的sql:

select sc.* from student_course as sc 
where id in 
(select id  from  student_course order by score desc limit 3) 

解决办法是：添加一层查询

select sc.* from student_course as sc 
where id in 
(select test01.id  from 
(select id  from  student_course order by score desc limit 3)  as test01)

16. mysql 的开窗函数：mysql8.0以后的版本添加了开窗函数
    row_number()：不考虑值相同的情况，直接顺序添加序号
    dense_rank() ：当有相同值的情况下，相同的序号是一样的，紧挨的下一个序号不考虑这些，直接顺序添加序号
    rank() ：当有相同值的情况下，相同的序号是一样的，紧挨的下一个序号在这些所有序号的基础上进行添加序号

select id, student_id, course_id, teacher_id, score
,row_number() over(partition by  student_id order by score desc)
,dense_rank()  over(partition by  student_id order by score desc)
,rank()  over(partition by  student_id order by score desc)
from student_course

17. 更改表字段的类型：
如：alter table sys_log modify column language varchar(10) comment “语言”
18. sql语句的执行顺序： from > where > group by > 聚合函数 > having > select > order by > limit。 Mysql Where条件执行顺序是从左到右：遵循原则：排除越多数据的条件放在第一位
19. group by 在mysql和oracle中的区别：参考这篇文章 详解GROUP BY分组查询在Mysql与Oracle中的区别与使用
20. mysql select 出现非分组字段 不出现异常
MySQL数据库，select后面出现了没在group by里面的字段，为什么还可以执行成功？——不建议使用
21. mysql topn 问题
全网最全的mysql分组后取topN的解答
22. not in 使用大坑：NULL值处理：在MySQL中，NULL与NOT IN一起使用时需要特别小心。如果list中任何一个值是NULL，或者被比较的列的值为NULL，则NOT IN操作的结果总是未知的（UNKNOWN），这意味着该行不会被包含在查询结果中。例如，给定查询SELECT * FROM table WHERE column NOT IN (1, 2, NULL);，无论column的值是什么，该行都不会出现在结果集中。
23. 数据库练习： 复杂sql语句练习（mysql）
总结：写复杂 sql：

1. 先写复杂sql的一部分
2. 在把各部分组装起来
3. 有时候sql的写法会有多种