mysql学习笔记

mysql学习笔记

1.简单语法

DDL语言（数据定义语言）

①创建数据库：create database myschool;
②查看所有数据库：show databases;
③创建表：

use myschool;#(先要找到在哪个数据库下创建表)
create table subject
(subjectNO int primary key,
subjectName varchar(50),
classHour int,
gradeID int);

(int默认长度是11，即int是int(11)的缩写；varchar和char的区别在于varchar的字段是可变的，char的字段不可变，eg:varchar(50)，存储的字段是name,则取出来的时候只取这4个字段，而char(50)，取出来的就是50分字符，后面46个空格。
④查看当前数据库中的所有表：show tables;
⑤查看创建表结构：show create table subject;
show create table subject\G;(稍微美观一些）
⑥删除数据库：drop database myschool;
⑦添加列属性：

 create table student
    (
studentNo int(4) not null,
    loginPwd varchar(20),
     studentName varchar(20) comment'学生姓名'
);

⑧查看引擎：show engines \G
⑨修改表语句：

⑩查看表字段： describe student;
简写：desc student;

DML语言（数据操作语言）

②为某个表添加某表中的数据：

insert into grade (gradeName)
select gradeName from grade;

更新查出来的数据设置给某列

mysql> update subject set classhour =
    -> (select classHour from
    -> (select classhour from subject where subjectno =3)as a）#临时表
    -> where subjectno =1;

DQL查询语句

①
②distinct语法：

多个字段去重：多个字段值一致才会去重，示例如下：

③使用表达式：

④排序
多字段排序，先按照第一个字段排序，如果遇到相同数据，再按照第二个字段排序；
eg:

 select * from
    -> subject s inner join grade g
    -> on s.gradeid = g.id
    -> order by s.classHour desc,g.id;

⑤分页Limit

limit 语句是在最后，一般select的语句写完之后，再做分页；

mysql> select classhour from subject limit 3,5;
+-----------+
| classhour |
+-----------+
|       122 |
|       234 |
|        88 |
|         0 |
|      1111 |
+-----------+
5 rows in set (0.00 sec)

mysql> select classhour from subject limit 3,5
    -> order by classhour desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by classhour desc' at line 2
mysql> select classhour from subject
    -> order by classhour desc
    -> limit 3,5;
+-----------+
| classhour |
+-----------+
|       212 |
|       212 |
|       122 |
|        88 |
|         0 |
+-----------+
5 rows in set (0.00 sec)

附加java code：
pageIndex：当前页；
pageSize：显示条数；
pageIndex * pageSize - pageSize:总记录条数
total：总记录数

⑥count

mysql> select count(*) from subject;
+----------+
| count(*) |
+----------+
|        8 |
+----------+

⑦子查询

子查询返回多行：in
子查询返回单行：=
子查询返回true/false：exits
若子查询条件成立，则显示父查询结果；
否则不显示。
eg:

        mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeId |
+-----------+-------------+-----------+---------+
|         2 | 数学        |       212 |       3 |
|         3 | 英语        |       234 |       4 |
|         4 | 数学        |       212 |       3 |
|         5 | 科学        |       122 |       3 |
|         6 | 英语        |       234 |       6 |
|         7 | 英语        |        88 |       6 |
|         9 | NULL        |         0 |       7 |
|       111 | 英语        |      1111 |      11 |
+-----------+-------------+-----------+---------+
8 rows in set (0.00 sec)

mysql> select subjectNo,subjectName
    -> from subject
    -> where exists
    -> (select gradeId from subject where subjectNo <10);
+-----------+-------------+
| subjectNo | subjectName |
+-----------+-------------+
|         2 | 数学        |
|         3 | 英语        |
|         4 | 数学        |
|         5 | 科学        |
|         6 | 英语        |
|         7 | 英语        |
|         9 | NULL        |
|       111 | 英语        |
+-----------+-------------+
8 rows in set (0.00 sec)

mysql> select subjectNo,subjectName
    -> from subject
    -> where exists
    -> (select gradeId from subject where subjectNo >200);
Empty set (0.00 sec)

相反的，exists 的反面是 not exists;
not exists：如果子查询成立，则显示空；如果子查询不成立，则显示夫查询结果；
eg:

mysql> select subjectNo,subjectName
    -> from subject
    -> where not exists
    -> (select gradeId from subject where subjectNo >200);
+-----------+-------------+
| subjectNo | subjectName |
+-----------+-------------+
|         2 | 数学        |
|         3 | 英语        |
|         4 | 数学        |
|         5 | 科学        |
|         6 | 英语        |
|         7 | 英语        |
|         9 | NULL        |
|       111 | 英语        |
+-----------+-------------+
8 rows in set (0.05 sec)

mysql> select subjectNo,subjectName
    -> from subject
    -> where not exists
    -> (select gradeId from subject where subjectNo <111);
Empty set (0.00 sec)

any：任意一个
all：全部
eg:

mysql> select * from grade;
+----+------+-----------+---------------+
| id | name | className | studentResult |
+----+------+-----------+---------------+
|  1 | 张明 | 语文      |            88 |
|  2 | 李文 | 语文      |            99 |
|  3 | 张宏 | 数学      |            78 |
|  4 | 微信 | 英语      |            67 |
|  5 | 魏中 | 英语      |            89 |
+----+------+-----------+---------------+
5 rows in set (0.00 sec)

mysql> select * from grade
    -> where studentResult > any
    ->
    -> (select studentResult from grade);
+----+------+-----------+---------------+
| id | name | className | studentResult |
+----+------+-----------+---------------+
|  1 | 张明 | 语文      |            88 |
|  2 | 李文 | 语文      |            99 |
|  3 | 张宏 | 数学      |            78 |
|  5 | 魏中 | 英语      |            89 |
+----+------+-----------+---------------+
4 rows in set (0.00 sec)

mysql> select * from grade
    -> where id > all
    -> (select studentResult from grade);
Empty set (0.00 sec)

⑧聚合函数

eg:

mysql> select max(studentResult) as '最高分',min(studentResult) as '最低分',avg(studentResult) as '平均分'
    -> from grade;                                                                                  '平均分'
+--------+--------+---------+
| 最高分 | 最低分 | 平均分  |
+--------+--------+---------+
|     99 |     67 | 84.2000 |
+--------+--------+---------+
1 row in set (0.00 sec)

⑨group by 分组
自动添加一列计算总和：with rollup;如下示例

mysql> select className,count(classname) from grade
    -> group by classname;
+-----------+------------------+
| className | count(classname) |
+-----------+------------------+
| 语文      |                2 |
| 数学      |                1 |
| 英语      |                2 |
+-----------+------------------+
3 rows in set (0.00 sec)

mysql> select className,count(classname) from grade
    -> group by classname with rollup;
+-----------+------------------+
| className | count(classname) |
+-----------+------------------+
| 数学      |                1 |
| 英语      |                2 |
| 语文      |                2 |
| NULL      |                5 |
+-----------+------------------+
4 rows in set (0.05 sec)

分组后筛选：
group by ……having……

⑩合并union 、union all
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

11、distinct
distinct 只能放在第一个字段，且和后面的字段一起去重
eg，distinct name,job，则是指name和job2个字段联合起来去重

12、尽量减少表连接的次数
通过笛卡尔积现象得出，连接表的次数越多，效率越低，尽量少做连接。

13、连接
1、内连接
a、内连接之等值条件，on的后面是 =

select name
from a
join 
select salary,name
from b
on a.name = b.name

b、内连接之非等值条件，on的后面不是等值

select name，salary
from a
join 
select salary_high,salary_low
from b
on salary between salary_high and salary_low

c、内连接的自连接
一个表看成2个表，取2个表明

2、外连接
left join ,左外连接，左表为主，全部获取
right jion，右外连接，右表为主，全部获取。
可以互换

3、union
unoin的效果比表连接效率高，union在减少匹配次数的情况下，还可以完成表的连接。而join，每增加一次join，就要进行一次笛卡尔积，匹配成倍的增加。
eg，a连接b,连接c
a有10条记录，b有10条记录，c有10条记录。
join，总共匹配101010 = 1000次
union，总共匹配1010+1010 = 200次

注意事项，union在进行结果集合并的时候，要保持结果集的列数相同。要求结果集的列的数据类型要一致

4、limit
作用，当数据量大的时候，可以分页显示，就像百度网页的结果分页。
limit (起始位置，长度），起始位置从0开始算。
比如，取出排名3-5名的学员信息，则limit(2,3)。
注意，limit在order by之后执行。

规律，limit(pageNo-1)*pageSize,pageSize

5、快速创建表
将一个查询结果，当作1个表新建。

create table test as select * from a

6、将表的查询结果插入另一张表中

insert into test1 select * fron a

7、删除
delete， 删除，只是把内容清除了，但是空间不做释放；且支持回滚，rollback一下，刚才删除的数据就会回来，不是真正意义上的删除。优点，可回滚；缺点，效率低。

truncate, ,物理删除，删除的快，空间内容全部一次性删除。不支持回滚，就是。内容没了，表结构还在。truncate只是在删除表里的数据，但是表结构都还在。

drop drop table test
这个是直接把表删除了，表结构和表中的数据都没了。

事务

事务的特点：
①原子性（A）：执行事在这里插入代码片务要么都成功要么都不成功
②一致性（C）：执行后，保持数据的一致
③隔离性（I）：事务之间相互独立，互不影响
④持久性（D）：数据永久储存在数据库中


查看默认事务提交方式：
①select @@autocommit;
@@：内置

当set autocommit = 0;时，rollback才能回滚，因为结果并没有上报到数据库。so,如果想要实时回滚数据，首先要关闭自动提交模式；

①事务隔离级别：
a.读取未提交（read-uncommitted）：事务间可以查看未提交（committ）的数据；
b.读取提交（read-committed）：事务间只能读取已经提交的数据；
c.重复读取（repeatable-read）：和正常操作一致
d.串行化（serializable）–>加锁、竞争锁
一个事务未完成，另一个事务不能操作，会显示tines out
②查看事务默认隔离级别：
(8版本以上用transaction_isolation，以下版本用tx_isolation)

select @@transaction_isolation;
select @@session.transaction_isolation;
select @@global.transaction_isolation;

例子：
在该cmd中先设置关闭自动提交设置，然后在表中增加某条数据

在另外一个cmd中查询该表数据：首先设置事务隔离级别为read-uncommitted，然后查询表数据；

用串行化的结果如下：
首先关闭自动提交，向表中增加数据：

打开另一个cmd（事务），设置默认隔离级别未串行化（serialazable），查询该表中的数据，结果如下：times out

把增加数据的事务提交之后，再做查询时即可。

2.简单知识点

①timestamp：
00-69：2000-2069年
70-99：1970-1999年
②数据类型




③concat(字段1,字段2，字段3……)：字段连接；

④
示例：两者效果一样

select * from subject where subjectName != 'null';

 select * from subject where subjectName is not null;

④
LIKE用法：like通常与通配符%一起使用，%表示通配pattern中出现的内容，而不加通配符%的LIKE语法，表示精确匹配，其实际效果等同于 = 等于运算符
eg：select * from student where name like '%梅';

select * from student where name like '李梅';

'_'代表一个字；
'__'代表两个字；
如下：

连接查询

mysql不支持full 链接，直接用等于‘=’即可；

注意事项

①mysql命令行中执行多行命令时，如果前边输入的命令发生错误，是很令人头疼的事情，可以通过输入\c来取消前边的输入，重新进入“mysql->”提示符。
②mysql注释
单行注释用#；多行注释用/* */；

错误记录

但是如果前边输入的东西很多，直接取消又很可惜的，因为得重新敲打指令，这种情况下，可以通过输入\p，然后在命令行客户端右键，选择“标记”，就可以选择要复制的内容，选择好后，按Enter，即可将选择的内容复制到剪切板，接着输入\c，再粘贴，修改，执行命令。
即输入\p→右键鼠标选择“标记”→选择要复制的内容→按Enter键→输入\c→右键鼠标选择“粘贴”，然后修改即可。

LEETCODE错误备注：

①

在使用mysql执行update的时候，如果不是用主键当where语句，会报如下错误，使用主键用于where语句中正常。

异常内容：Error Code: 1175. You are using safe update mode and you tried to
update a table without a WHERE that uses a KEY column To disable safe
mode, toggle the option in Preferences -> SQL Queries and reconnect.
Error Code: 1175. You are using safe update 2
这是因为MySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令
SET SQL_SAFE_UPDATES = 0;修改下数据库模式

如果想要提高数据库安全等级，可以在恢复回原有的设置，执行命令：SET SQL_SAFE_UPDATES = 1;

执行成功后，以delete命令为例，非主键情况下又报错了，说明安全等级修改成功

Error Code: 1175. You are using safe update

DATEDIFF 计算时间差

定义和用法
DATEDIFF() 函数返回两个日期之间的天数。

语法
DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。

注释：只有值的日期部分参与计算。

---

2020.6.21
快3个月，没有学习了。。

这段时间过得太懒了，以后不能再这样了。

---

2020.6.21

1、ifnull函数
concat函数中，如果连接的字段中其中有为null的，即结果值为null，为了避免出现这种情况，可以用ifnull()函数将null转化为非null值。
eg：
concat(first_name," ",last_name,",",ifnull(old,0))

2021.4.12’
继续开始攻坚技术难题-Fighting
1、CASE结构
CASE 变量|表达式|字段
WHEN 要判断的值/条件 THEN 返回的值1
WHEN 要判断的值/条件 THEN 返回的值2
…
ELSE 要返回的值n
END CASE

特点，①可以作为表达式，嵌套在其他语句中使用；也可以作为独立的语句使用，但只能放在BEGIN END中

2、if结构-实现多重分支
语法，
if 条件1then 语句1；
elseif 条件2 then 语句2；
…
else 语句n;
end if;
应用在begin end 中