韩顺平SQL学习
参考链接：https://www.bilibili.com/video/BV1H64y1U7GJ/?spm_id_from=333.337.search-card.all.click&vd_source=851580dc05196665ad66dcdc4fb7ded6

MySQL安装和配置

简单原理

使用了一系列week5的指令在vagrant上安装了数据库

$ sudo apt install mariadb-{server,client}
Exercise: What does echo {a,b,c}-{1,2,3} print? Try and guess before running it.
Answer: a-1 a-2 a-3 b-1 b-2 b-3 c-1 c-2 c-3
$ sudo systemctl status mariadb
$ sudo journalctl -u mariadb
set run by default $ sudo systemctl enable mariadb

命令行-链接sql

If 客户端 —> 3306(端口)Mysql服务(保证运行状态)
此时链接sql指令：mysql -h 主机IP -P 端口 -u 用户名 -p密码

密码和p不加空格
若p后无密码，mysql要求输入密码
如果没有 -h 主机，默认本地
如果没有 -P, 默认3306
实际开发中，往往要修改端口3306(容易遭受黑客攻击)
链接数据库前，确保它处于运行状态(running…)

图形可视化MySQL管理软件

Navicat安装和使用

试用15天
SQLyog安装和使用
看起来比较好用，可以安装一个

数据库

三重结构

MySQL就是在主机安装一个数据库管理系统(DBMS-database manage system)
DBMS可以管理多个数据库
一个数据库可以创建多个表，保存数据

图示

MySQL数据库-普通表的本质仍然是文件

SQL语句分类

DDL: 数据定义语句[create 表，库…]
DML: 数据操作语句[增加insert;修改update；删除delete]
DQL: 数据查询语句[select]
DCL: 数据控制语句[管理数据库，用户权限: grant revoke]

通过java程序操作数据库

也可以通过java程序来操作SQL。

创建数据库

基本语法CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET sharset_name: 指定数据库采用字符集，若不指定，默认utf-8.

[DEFAULT]COLLATE collation_name: 指定数据库字符集的校对规则。

utf8_bin -> 区分大小写

utf8_general_ci -> 不区分大小写
练习：
db01 -> 单纯创建表
db02 -> 创建表 + 使用utf8字符集
db03 -> 创建表 + 使用utf8字符集 + 带区分大小写的校对规则

root@debian12:/home# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE hsp_db01;
MariaDB [(none)]> CREATE DATABASE hsp_db02 CHARACTER SET utf8;
MariaDB [(none)]> CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin;

查询数据库

显示数据库 SHOW DATABASES;
查看当前创建数据库定义信息 SHOW CREATE DATABASE db_name;
删除数据库DROP DATABSE db_name; 慎重删库
创建数据库表的时候，用反引号解决规避关键字的问题’`’这东西

备份&恢复数据库重要

备份数据库DOS那儿执行：mysqldump -u root -p -B db_name db_name > path\\bak.sql
恢复数据库：先进入mysql mysql里执行之后再 source path\\bak.sql
第二个方法：直接将bak.sql内容放到查询编辑器中，执行(但是一般还是进入sql里方法好)

Mysql数据类型(列类型 = 数据类型)

一些常用的数据类型（字段类型）

数值类型
其他类型
要存放非常非常大的数值： DECIMAL可以办到（而且它的精度也是最高的）
char固定长度；verchar可变长度。查询速度char > verchar。都是255

表 - 一定是在数据库里面(不单独存在)

创建

基本语法：

CREATE TABLE table_name(
     field1 datatype;
     field2 datatype;
     field3 datatype;
)character set 字符集 collate 校对规则 engine 存储引擎

MariaDB [(none)]> use hsp_db02;
Database changed
MariaDB [hsp_db02]> create table `user`(
    -> id INT,
    -> `name` VARCHAR(255),
    -> `password` VARCHAR(255),
    -> `birthday` DATE)
    -> CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
MariaDB [hsp_db02]> create table `user`( id INT, `name` VARCHAR(255), `password` VARCHA
R(255), `birthday` DATE) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
Query OK, 0 rows affected (0.051 sec)

反引号很重要(斜着的那个东西`)

创建表练习

MariaDB [hsp_db02]> create table `emp`( id INT, `name` VARCHAR(255), sex CHAR(1), `birthday` DATE, entry_date DATETIME, job VARCHAR(32), salary DOUBLE, `resume` TEXT);
Query OK, 0 rows affected (0.037 sec)

MariaDB [hsp_db02]> insert into `emp` values(100,'monster','F','2001-11-11','2010-11-10 11:11:11', 'mountain', '3000','xunshan');
Query OK, 1 row affected (0.013 sec)

insert into `emp`
    -> VALUES(3,'human','F','2021-2-10','2024-2-20 16:00:01','cute',1000,'cute is job');
Query OK, 1 row affected (0.014 sec)

MariaDB [hsp_db02]> select * from `emp`
    -> ;
+------+---------+------+------------+---------------------+----------+--------+-------------+
| id   | name    | sex  | birthday   | entry_date          | job      | salary | resume      |
+------+---------+------+------------+---------------------+----------+--------+-------------+
|  100 | monster | F    | 2001-11-11 | 2010-11-10 11:11:11 | mountain |   3000 | xunshan     |
|    3 | human   | F    | 2021-02-10 | 2024-02-20 16:00:01 | cute     |   1000 | cute is job |
+------+---------+------+------------+---------------------+----------+--------+-------------+
2 rows in set (0.001 sec)

修改

添加列

1
2
3

ALTER TABLE tablename
ADD (column datatype [DEDAULT expr]
     [, colunm datatype]...);

修改列

1
2
3

ALTER TABLE tablename
MODIFY (column datatype [DEDAULT expr]
     [, colunm datatype]...);

删除列
1
2
ALTER TABLE tablename
DROP (column);
查看表的结构 desc 表名; -可以查看表所有的列
修改表名 rename table table_name to new_table_name
修改表字符集 alter table table_name character set 字符集

练习题：

答案：

MariaDB [hsp_db02]> ALTER TABLE `emp`
    -> ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME;
Query OK, 0 rows affected (0.057 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [hsp_db02]> ALTER TABLE `emp`
    -> MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.081 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [hsp_db02]> ALTER TABLE emp
    -> drop sex;
Query OK, 0 rows affected (0.053 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [hsp_db02]> RENAME TABLE emp to employee;
Query OK, 0 rows affected (0.033 sec)
MariaDB [hsp_db02]> ALTER TABLE employee CHARACTER SET utf8;
Query OK, 0 rows affected (0.051 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hsp_db02]> ALTER TABLE employee CHANGE `name` user_name VARCHAR(64) NOT NULL DEFAULT '';
Query OK, 2 rows affected (0.090 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hsp_db02]> DESC employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| user_name  | varchar(64) | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| entry_date | datetime    | YES  |     | NULL    |       |
| job        | varchar(60) | NO   |     |         |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | text        | YES  |     | NULL    |       |
| image      | varchar(32) | NO   |     |         |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.002 sec)

CRUD(重要)

C - CREATE
R - READ
U - UPDATE
D - DELECT

INSERT 语句 - 插入数据

语法

1 2	INSERT INTO table_name column values(value...);

练习

使用INSERT语句向表employee中插入两个员工信息

MariaDB [hsp_db02]> insert into `employee`(id,user_name,birthday,entry_date,job,salary,resume,image)
    -> values(1,'Amy','2001-7-19','2024-10-10 12:00:01','programmar',10000,'GOOD girl','not yet');
Query OK, 1 row affected (0.013 sec)

MariaDB [hsp_db02]> insert into `employee`(id,user_name,birthday,entry_date,job,salary,resume,image)
    -> values(2,'Bell','2022-2-22','2024-2-2 12:22:22','dontknow','0','good girl2','no');
Query OK, 1 row affected (0.014 sec)

MariaDB [hsp_db02]> select * from employee;
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
| id   | user_name | birthday   | entry_date          | job        | salary | resume      | image   |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
|  100 | monster   | 2001-11-11 | 2010-11-10 11:11:11 | mountain   |   3000 | xunshan     |         |
|    3 | human     | 2021-02-10 | 2024-02-20 16:00:01 | cute       |   1000 | cute is job |         |
|    1 | Amy       | 2001-07-19 | 2024-10-10 12:00:01 | programmar |  10000 | GOOD girl   | not yet |
|    2 | Bell      | 2022-02-22 | 2024-02-02 12:22:22 | dontknow   |      0 | good girl2  | no      |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
4 rows in set (0.001 sec)

注意事项

插入数据与字段数据类型相对
数据长度应在规定范围内
values里的值和表头应该一一对应
字符&日期数据类型 -> 单引号引起来
列可以插入空值，前提是该字段允许为空:insert into table value(null)
添加多条记录： insert into table_name (列名) values (),(),()
给表中所有字段添加数据，可以不写前面的字段名称(表会默认匹配)
不给某个字段值的时候，有默认->添加默认；无默认->报错(希望指定某个表的默认值，创建表的时候写默认)

UPDATE语句 - 更新数据

基本语法：

1
2
3

UPDATE table_name
     SET col_name1 = expr1[, col_name2=expr2 ...]
     [WHERE where_definition]

练习

MariaDB [hsp_db02]> update employee SET salary = 5000;
Query OK, 4 rows affected (0.009 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [hsp_db02]> UPDATE employee SET salary = 3000 where user_name = 'monster';
Query OK, 1 row affected (0.013 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hsp_db02]> UPDATE employee SET salary = salary + 1000 where user_name = 'Amy';
MariaDB [hsp_db02]> SELECT * FROM employee
    -> ;
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
| id   | user_name | birthday   | entry_date          | job        | salary | resume      | image   |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
|  100 | monster   | 2001-11-11 | 2010-11-10 11:11:11 | mountain   |   3000 | xunshan     |         |
|    3 | human     | 2021-02-10 | 2024-02-20 16:00:01 | cute       |   5000 | cute is job |         |
|    1 | Amy       | 2001-07-19 | 2024-10-10 12:00:01 | programmar |   6000 | GOOD girl   | not yet |
|    2 | Bell      | 2022-02-22 | 2024-02-02 12:22:22 | dontknow   |   5000 | good girl2  | no      |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
4 rows in set (0.001 sec)

注意事项

UPDATE 更新原有表中的各列
SET 语句指定要修改哪些值，也可以修改多个列
WHERE语句指定更新哪行，没有的话则所有的行都被更新

SELETE语句 - 选择数据

基本语法：

1 2	SELECT DISTINCT * \| {column1， column2, column3...} FROM table_name

可以使用表达式对查询的列进行运算

记得整理select函数的一些用法

单表

查询加强

# 表查询 - 加强
SELECT * FROM empl
     WHERE hiredate > '1920-01-01';
# 模糊查询 LIKE关键字
# % - 0-多个任意字符
# _ - 单个任意字符
SELECT ename,sal FROM emp
     WHERE ename LIKE '__O%'; # 第三个字符大写O
# 本身是否为空 - 用 IS NULL
SELECT * FROM emp
     WHERE mgr IS NULL;
# order by - 排序
SELECT * FROM emp
     ORDER BY sal ASC; # ASC - 升序

SELECT * FROM emp
     ORDER BY deptno ASC， sal DESC；  # 同时满足这个两个条件的排序，且有先后顺序

分页查询 - 重要

# SELECT ... LIMIT start,rows start从0开始，从start+1行起，取出三行
# 每页有三行
SELECT * FROM emp
     ORDER BY empno
     LIMIT 0,3;
     SELECT * FROM emp
     ORDER BY empno
     LIMIT 3,3;
SELECT * FROM emp
     ORDER BY empno
     LIMIT 6,3;

分组增强

# group by
# 每种岗位的雇员总数，平均工资
SELECT COUNT(*), AVG(sal), job
     FROM emp GROUP BY job;

# 总数 + 非空
SELECT COUNT(*), COUNT(comm) # count 不会统计非空值
     FROM emp;
# 另一种,这里的‘1’实际代表非空值，其实写什么都可以，只要不是非空
SELECT COUNT(*), COUNT(IF(comm IS NULL,1,NULL)) # count 不会统计非空值
     FROM emp;

# 显示管理员 DISTINCT - 去重
SELECT COUNT(DISTINCT mgr)
     FROM emp；

# 最大差额 - max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal)
     FROM emp;

多子句查询

# 会有一个不能写错的顺序
# group by col1,col2... -> having condition -> order by col -> limit start,rows;
SELECT deptno, AVG(sal) FROM emp
     GROUP BY deptno
     HAVING avg_sal > 1000
     ORDER BY avg_sal DESC
     LIMIT 0, 2;

多表

对两个或两个以上的表进行查询。
过滤是重点

多表笛卡尔集

# 规则： 从第一张取出每一行，和第二张表的每一行进行组合，返回结果【所有列col1*col2】
# 重点： 过滤 WHERE - 分析过滤条件
# 如果有两个detpno，那么select detpno就会报错(不知道你写的是哪个detpno)
# 需要指定显示某个表的列： 表.列名
SELECT ename, sal, dname
     FROM emp
     WHERE emp.deptno = dept.deptno; #表有关联，才有意义

过滤条件不少于(表个数-1)

多表查询

# 姓名，工资，薪水级别(两张表)
# 先写简单查询 -> 加入过滤条件
SELECT ename, sal, grade 
     FROM emp, salgrade
     WHERE sal between losal and hisal;

自链接

将同一张表看成两张表: 找到在同一张表的关联方法

# 显示员工名字 + 上级名字
# 要给表取别名
SELECT worker.name AS 'ee', boss.ename AS 'er'
     FROM emp wroker, emp boss
     WHERE worker.mgr = boss.empno;

子查询

把一个select语句的查询结果拿来，一个子查询来用

多行

SELECT DISTINCT job
     FROM emp
     WHERE deptno = 10;

# 把上面的查询结果用来当
SELECT ename,job,sal,deptno
     FROM emp
     WHERE job IN(
          SELECT DISTINCT job
          FROM emp
          WHERE deptno = 10
     ) AND deptno ！= 10

临时列表

DELETE语句 - 删除数据

基本语法：

1 2	DELETE from table_name [WHERE where_definition]

练习

MariaDB [hsp_db02]> delete from employee
    -> where user_name = 'monster';
Query OK, 1 row affected (0.015 sec)

MariaDB [hsp_db02]> select * from employee;
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
| id   | user_name | birthday   | entry_date          | job        | salary | resume      | image   |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
|    3 | human     | 2021-02-10 | 2024-02-20 16:00:01 | cute       |   5000 | cute is job |         |
|    1 | Amy       | 2001-07-19 | 2024-10-10 12:00:01 | programmar |   6000 | GOOD girl   | not yet |
|    2 | Bell      | 2022-02-22 | 2024-02-02 12:22:22 | dontknow   |   5000 | good girl2  | no      |
+------+-----------+------------+---------------------+------------+--------+-------------+---------+
3 rows in set (0.002 sec)

MariaDB [hsp_db02]> DELETE from employee;
Query OK, 3 rows affected (0.006 sec)

MariaDB [hsp_db02]> select * from employee;
Empty set (0.002 sec)

注意事项

不加where，整个表就会被删空
delete语句不能删除某一列的值(delete删除的是行)
可以用updat设置为null或’’
delete只能删除表里的数据，但是删除不了表本身(DROP TABLE table_name是删表的)

函数 - 先跳过

字符串函数

日期函数

加密函数

流程控制函数

内连接

外连接

左外连接和右外连接。

左外连接

左侧的表完全显示。

1	SELECT ... from 表1 left join 表2 on 条件 #表1是左表，表2是右表

右外连接

右侧的表完全显示。

1	SELECT ... from 表1 right join 表2 on 条件 #表1是左表，表2是右表

约束

主键

unique & not null
一张表中最多只有一个主键，但可以复合主键 # PRIMARY KEY(id,name)这种
unique
表示列是不可以重复的
没指定not null,则unique可以有多个null
一张表可以有多个unique字段
not null
不让指定列为null
如果约束条件为 unique + not null, 其实和主键没啥区别(但是这样约束出来的不是主键)
外键
定义主表和从表之间的关系。外键约束定义在从表上，主表必须有主键约束或是unique约束。
FOREIGN KEY(本表字段名) REFERENCES 主表名(主键名或unique字段)
主外键关系一旦定义，数据不能随意删除了。
check
强制指定数据必须满足的条件。
索引

MySQL安装和配置

简单原理

使用了一系列week5的指令在vagrant上安装了数据库

命令行-链接sql

图形可视化MySQL管理软件

Navicat安装和使用

SQLyog安装和使用

数据库

三重结构

SQL语句分类

通过java程序操作数据库

创建数据库

查询数据库

备份&恢复数据库重要

Mysql数据类型(列类型 = 数据类型)

表 - 一定是在数据库里面(不单独存在)

创建

创建表练习

修改

CRUD(重要)

INSERT 语句 - 插入数据

语法

练习

注意事项

UPDATE语句 - 更新数据

基本语法：

练习

注意事项

SELETE语句 - 选择数据

基本语法：

单表

查询加强

分页查询 - 重要

分组增强

多子句查询

多表

多表笛卡尔集

多表查询

自链接

子查询

多行

临时列表

DELETE语句 - 删除数据

基本语法：

练习

注意事项

函数 - 先跳过

字符串函数

日期函数

加密函数

流程控制函数

内连接

外连接

左外连接

右外连接

约束

主键

unique

not null

外键

check

索引

事务