sql的书写操作

创建数据库

create database <数据库名>

创建表

create table <表名>(
<列名1> <数据类型> <该列的约束>，
<列名2> <数据类型> <该列的约束>，
...
<该表的约束1>,<该表的约束2>,...
);

数据类型

-字符串：定长char，可变长度varchar

-数字：int/integer

-日期：date

约束：

-null

-not null

-primary key

create table student
(sid  int(4)  primary key auto_increment comment '学生id',
sname char(20) not null comment '学生名字',
ssex char(3) not null comment '学生性别',
sage integer not null comment '学生年龄');

增加表的列

alter table <表名> add <列名>

alter table tableName add columnName varchar(30)  

删除表的列

alter table <表名> drop <列名>

删除表

1）如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果如果想保留表而将所有数据删除，如果和事务有关，或者想触发trigger，还是用delete；

2）执行速度：drop>truncate>delete

3）drop、truncate是立即执行，不能回滚

4）drop、truncate不能加where条件

drop table student;

truncate table 表名;

delete from  student where  T_name = "张三";

插入数据

可以只插入所需要的列，但其他未被写入的列必须可以为空，如果设置成不为null那将报错

insert into <表名> (<列名1>，<列名2>，...）
values(<值1>,<值2>...)；

更新数据

UPDATE <列名> SET 列1=value1, 列2=value2, … WHERE conditions

为列设置别名

select name as s_name from student;

删除重复数据

//distinct 放在列名前
select distinct name from student;

mysql的执行顺序

select
    catid,
    count(orderid) as sales
from
    t
where 
    catid <> "c666"
group by 
    catid
having
    count(orderid) > 10
order by 
    count(orderid) desc
limit 3

执行顺序是:from-on-join-where-groupby-having-select-orderby-limit

如果是单表就根据这个执行顺序来套入，order by里的字段前面必须出现，group by出现或者select出现都可以

https://www.nowcoder.com/practice/e00bbac732cb4b6bbc62a52b930cb15e?tpId=199&tqId=1975672&ru=/exam/oj&qru=/ta/sql-quick-study/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199

运算符

+、-、*、/、=、<>、 >、 >= <、 <=

select 姓名,出生日期 from student where 出生日期<>'1990-01-01';

查出null和not null值

select 教师号,教师姓名 from teacher where 教师姓名 is null;

select 教师号,教师姓名 from teacher where 教师姓名 is not null;

逻辑运算符

not、and、between、or、in

select 学号,成绩 from course where not 成绩<60;

-- 60<=成绩<=90
select 学号,成绩 from course where 成绩 between 60 and 90;

select 姓名,性别 from student where 姓名 in ('猴子','马云');

select 姓名,性别 from student where 姓名 not in('猴子','马云');

%跟_

%表示任意字符串，_表示任意一个字符

select * from student where 姓名 like '%猴%';

select * from student where 姓名 like '王__';

汇总函数

count()，参数写列名则不包括空值，写*则包括空值

sum()，对某列求和

avg()，求某列的平均值

max()，求某列的最大值

min()，求某列的最小值

分组

使用group by分组时，会将相同字段的记录归并成一条记录，这时那些不是在group by后那些字段就可能会出现多个值

分组前：

分组后：

如果使用name分组的话，返回应该是虚拟表3，可以看到id跟number一个单元格里有多个值，这样查询出来的结果是错误的；但name每个单元格只有一个数据，因此select name是不会报错的

既然一个单元格有多个值，若使用聚合函数把多个值合并起来，再查询，这样是允许的

例如使用count(id),sum(number)等等

偶尔也会出现多个字段分组，例如group by name,number，我们可以把它看成一个整体字段，用整体来分组

这也解释了为什么order by出现的字段group by里必须要有

排序

使用order by，默认是asc即升序，desc是倒序

可以根据多个列排序，order by 成绩 asc，课程号 desc;

常见错误

-在groupby和having里不要使用select 里的别名，因为先后顺序问题

-在where中使用聚合函数，都还没根据条件筛选出来，怎么使用聚合函数

视图

可以将查询出来的结果存放在一张虚拟表上，这张虚拟表叫做视图，可以对这张表进行增删改查，但一般不会增删改，因为会影响原表，而原表修改对视图也有影响

create view viewName as select语句

视图列名跟select子句的列名是一一对应的

视图的作用

当经常需要使用到一个比较复杂的sql查询时，通过sql可以避免每次都写很多的语句，因此具有简单性

视图可以只显示某些数据，它可以保证某些敏感数据不被增删改查，因此具有安全性

子查询

子查询是一次性的视图，可以放在from、in、any(任意一个，有一个满足即可)、all(所有都需要满足)语句中

select 性别,人数 from( select 性别,count(*) as 人数 from student group by 性别 )as 按性别汇总;

select 学号,成绩 from course where 成绩>any(select 成绩 from course where 课程号='0002');

偶尔使用sql语句：子查询

频繁使用sql语句：视图

子查询的执行顺序

看是关联子查询还是非关联子查询

如果是关联子查询，即外部查询返回的每一行数据子查询都要执行一次，然后子查询再将结果返回给外部查询，外部查询根据结果做出决策

select * from dept d where exists(select * from emp e where e.deptno = d.deptno); 

这样执行顺序是先执行外层再执行内层

如果是非关联子查询，即子查询是独立于太不查询的查询，子查询执行完毕后将结果传给外部查询

select * from emp where sal = (select max(sal) from emp);

执行顺序是先执行内层查询再执行外层查询

关联子查询有什么用

在每个组里进行比较的时候，使用关联子查询

联合查询，也叫组合查询

-union，删除重复行

-union all，保留重复行

联合查询select必须拥有相同数量的列且具有相同类型的列

//相同名字不会被查出来
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
//相同名字会被查出来
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2

https://www.nowcoder.com/practice/979b1a5a16d44afaba5191b22152f64a?tpId=199&tqId=1975677&ru=/exam/oj&qru=/ta/sql-quick-study/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199

连接查询

连接：表和表之间通过列产生关系，联结就是根据表和表之间的关系将两个表合并在一起的操作

常用的联结：交叉连接、内连接、左连接、右连接、全连接

例如
交叉连接

交叉连接也称笛卡尔积，即将表1的每一行数据和表2的每一行数据合并在一起

行数的3*2

内连接

查找两张表里共同存在的数据，即交集

步骤：

-分别从两张表里查出符合条件的行，两张表都需要存在某些数据

这就解释了https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5?tpId=199&tqId=1975674&ru=/exam/oj&qru=/ta/sql-quick-study/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199为什么需要用distinct

select a.学号,a.姓名,b.课程号 from student as a inner join course as b on a.学号=b.学号;

左连接

左表全部查询，右表只查出两张表都存在的数据

步骤：

-左边表的数据全部取出，右边的表选择同时存在于两张表中的数据

-将两张表中取出的数据进行合并，即交叉联结

select a.学号,a.姓名,b.课程号 from student as a left join course as b on a.学号=b.学号;

查出A表里不在B表的数据

select a.学号,a.姓名,b.课程号 from student as a left join course as b on a.学号=b.学号 where b.学号 is null;

右连接

左连接跟右连接差不多，可以互换

全连接

连接图

self join

返回表与自己连接后符合条件的记录，一般用在表里有一个字段是用主键作为外键的情况

case表达式

case when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     ...
     else <表达式>
end

注意事项：

-else语句可以省略不写，默认为空值，建议不要省略

-end不能省略

-case表达式可以写在sql的任意子句中

-- 查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩>=60 then 1
else 0
end) as 及格人数,
sum(case when 成绩<60 then 1
else 0
end) as 不及格人数
from score
group by 课程号;

书写sql的思路

-翻译成大白话

-写出分析思路

-写出对应的sql子句

例如查询所有学生的学号、姓名、选课数、总成绩

-大白话

1）学号、姓名（学生表student）

2）选课数（每个学生的选课数目：成绩表score，按学号分组，对课程号计数count）

3）总成绩（每个学生的总成绩：成绩表score，按学号分组，对成绩求和sum）

-思路

1）select 查询结果：[学号，姓名，选课数，总成绩]

2）from 从哪张表查找数据：[学生表student，成绩表score；两个表如何连接？通过学号；用哪种连接？左连接）

3）where 查询条件：[没有]

4）group by 分组：[每个学生的选课数目：按学号分组，对课程号计数count；每个学生的总成绩：按学号分组，对成绩求和sum]

5）having 对分组结果指定条件：[没有]

6）order by 对查询结果排序：[没有]

7）limit 从查询结果中取出指定行：[没有];

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号;

ID自动编号的数据库当中删除了一条记录后，如何才能使编号仍然保持连续

Delid = 26
delete from tablename where id = Delid
update tablename set id = id-1 where id > Delid

有一个前提条件，id不能为标识列(唯一值)，如果是标识列先取消自动标识，然后执行update 语句，执行完毕再把id设置成自动标志

sql 里的计数方式

count(*)，count(1)，count(主键)，count(普通字段)

首先 count(字段)表示的就是该字段里值不为null的数据行有多少行

count(*)其实就是count(0)，无论是count(0)还是count(1)，0跟1都永远不为null，因此会把为null的行也统计进来

性能比较，count(*)=count(1)>count(主键)>count(普通字段)

count(主键)跟count(普通字段)，当拿到行数据之后，还要判断拿到的主键/普通字段是否为null值，因此性能相对会差一点

但为什么count(主键)>count(普通字段)，因为主键值比较容易拿到，如果存在二级索引，那么count(主键)不需要回表操作，但是count(普通字段)需要回表操作

而count(*)跟count(1)不需要判断值是否为null值，因此相对性能会快一点

如果存在二级索引，那么innodb遍历数据将会使用二级索引，因为二级索引占的内存小一点，性能会好一点

mysql里的判断

-case函数

SELECT
    t.NAME,
    (
        CASE t.sex
        WHEN 1 THEN
            '男'
        WHEN 2 THEN
            '女'
        ELSE
            '未知'
        END
    ) 性别
FROM
    t_customer t

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

-if函数

使用CASE函数可以实现非常复杂的逻辑判断，可是若只是实现“如果符合条件则返回A，否则返回B”这样简单的判断逻辑的话，使用CASE函数就过于繁琐。MYSQL提供了IF()函数用于简化这种逻辑判断，其语法格式如下：IF(expr1,expr2,expr3)

如果 expr1 为真&&expr1 <> 0 && expr1 <> NULL)，那么 IF() 返回 expr2，否则返回expr3。IF()返回一个数字或字符串，这取决于它被使用的语境

select t.name,if(t.weight<80,'正常','肥胖') 体重 from t_customer t

where1=1的意义

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(id) from t_book t where 1=1
<if test="title !=null and title !='' ">
 AND title = #{title} 
</if> 
<if test="author !=null and author !='' ">
 AND author = #{author}
</if> 
</select>

这里where1=1的意义是为了后面if标签为true时给这条sql语句添加一个where

那么where1=1对性能是否有影响，是否会让索引失效

通过explain语句可以可知使用where1=1同样是能走索引的，因此对性能并不会影响。不仅如此，mysql在执行sql的时候会优化sql，自动把1=1这个条件去掉

但是如果数据量大的话，那么mysql优化还是有影响的，建议使用标签，这个标签可以自动把第一个and或者or去掉

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_book t
<where>
<if test="title !=null and title !='' ">
 title = #{title} 
</if>
<if test="author !=null and author !='' "> 
 AND author = #{author}
</if>
</where> 
</select>

where跟having的区别

where针对的是一条记录，例如当一条数据id=1时

having针对的是多条记录，例如说求语文平均成绩大于80的班级

where 后面不能接聚合函数，如果需要用聚合函数可以使用having