MySQL知识点总结

于 2020-02-05 20:55:26 发布
阅读量480 收藏 1
点赞数
分类专栏： 数据库 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45557389/article/details/104188167
版权

一、MySQL安装步骤

1、下载

下载:官网 https://www.mysql.com/

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
解压出来就行了

目录：

Bin：可执行的文件

Docs:文档

Etc

Include：用于放置一些头文件

Lib：jar包

Run

Share：存放字符集、语言等信息

2、安装

打开系统管理员，命令进入到解压目录：
在这里插入图片描述
在这里插入图片描述

进入解压目录：

在这里插入图片描述

把mysql服务增加到window服务里： 命令：mysqld.exe install

在这里插入图片描述

查看确认服务
在这里插入图片描述
在这里插入图片描述

初始化数据： 命令：mysql --initialize

在这里插入图片描述
在这里插入图片描述
查找账号密码：

在这里插入图片描述

启动服务：

在这里插入图片描述

命令启动：net start mysql

命令关闭：net stop mysql
在这里插入图片描述
在这里插入图片描述

删除服务：mysqld remove

登录：
在这里插入图片描述
在这里插入图片描述

登录成功：

在这里插入图片描述

修改密码：

在这里插入图片描述

退出：exit

修改密码：命令alter user ‘root’@‘localhost’ identified by ‘123456’

在这里插入图片描述

3、万能数据库客户端：navicat

官网: https://www.navicat.com.cn

在这里插入图片描述

解压：

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

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

连接数据库：
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

破解navicat：

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

二、SQL语句分类

DQL(数据查询语言)：凡是select语句都是DQL

DML(数据操作语言)：insert delete update对表中的数据进行增删改

DDL(数据定义语言)：create drop alter对表结构进行增删改

DCL(数据控制语言)：grant授权revoke撤销权限等

TCL(事务控制语言)：commit提交事务rollback回滚事务

三、DDL(数据定义语言)

– 注释

#sql:一门语言,专门操作数据库的语言

1、创建库

#语法：CREATE DATABASE 库名

CREATE DATABASE test;
2、删除库

#语法：DROP DATABASE 库名

DROP DATABASE test;
3、创建表

#语法：CREATE TABLE 表名(字段 数据类型,…)

#VARCHAR相当于String，CHAR

#多个字段之间用",“隔开，最后一个不要”,"

CREATE TABLE student (

   s_name VARCHAR (255),

   s_sex VARCHAR (10),

   s_birthday date,

   s_age INT

)
4、增加字段

#语法：ALTER TABLE 表名 ADD COLUMN 字段名 数据类型

ALTER TABLE student add COLUMN s_score int
5、删除字段

#语法：ALTER TABLE 表名 DROP COLUMN 字段名

ALTER TABLE student DROP COLUMN s_score
6、主键：唯一标识
CREATE TABLE stu (

   id int PRIMARY KEY,

   s_name VARCHAR (255),

   s_sex VARCHAR (10),

   s_birthday date,

   s_age INT

)

四、DML(数据操作语言)

1、添加数据

#语法：INSERT INTO 表名 (字段名,…) VALUES (值,…);

#值要与字段列一一对应,值是varchar可用’ ',也可以用""

INSERT INTO stu (s_name,s_sex,s_birthday,s_age) VALUES ('张三','男', '1990-09-10', 21); 

INSERT INTO stu (s_name,s_sex,s_birthday,s_age) VALUES ('李四','男', '1990-09-10', 21); 

INSERT INTO stu VALUES (1,'wang','男','1990-09-10',21);

#员工表

CREATE TABLE emp (

   #设置主键自增  PRIMARY key：主键  auto_increment：自动增长

   id int PRIMARY key auto_increment,

   e_name VARCHAR(255),

   e_age int,

   e_salay VARCHAR(255)

)
2、添加数据(sql优化的一种)
INSERT INTO emp (e_name,e_age,salay) VALUES ('张三',23,4500), ('李四',22,5500), ('王五',20,5000), ('赵六',19,6000), ('张三1',23,4500), ('李四1',22,5500), ('王五1', 20,5000)
3、MODIFY修改数据类型

#语法：ALTER TABLE 表名 MODIFY 字段名 数据类型

ALTER TABLE emp MODIFY e_salay int
4、CHANGE修改列名

#语法：ALTER TABLE 表名 CHANGE 旧名 新名 数据类型

ALTER TABLE emp CHANGE e_salay salay int
5、添加字段
ALTER TABLE emp ADD COLUMN common int
6、删除数据

(1)DELETE：假删，删除后表结构还存在

#语法：DELETE from 表名 WHERE 条件

#注意：没有条件全部删除

DELETE from emp

DELETE from emp WHERE salay <= 5000

(2)TRUNCATE：把表结构删除，重新创建一张一模一样的表

#语法：TRUNCATE 表名

TRUNCATE emp
7、更新数据update

#语法：UPDATE 表名 set 字段1=值1,字段2=值2… where 条件;

#注意：没有条件整张表数据全部更新

UPDATE emp set common = 2000

UPDATE emp set common = 3000 WHERE e_age >= 20

UPDATE emp set common = 5000 WHERE e_age >= 22 AND e_age <= 23

五、DQL(数据查询语言)

#语法：SELECT 字段名 from 表名 WHERE 条件

#*代表表中所有的字段

#sql优化：尽可能使用搜索的字段名，不要用*

SELECT * from emp

SELECT * from emp WHERE salay >= 5000
1、条件查询

关系运算符>, <, >=, <=, !=, <>, null, is null, is not null

SELECT * from emp WHERE salay >= 5000 AND common >= 800

#查询工资在4500-5000的

SELECT * from emp WHERE salay >= 4500 AND salay <= 5000

#查询工资不是5000的

SELECT * from emp WHERE salay != 5000

SELECT * from emp WHERE salay <> 5000

#ISNULL(expr)用法

#如果expr为null，那么isnull()的返回值为1，否则返回值为0

#查询没有提成的

SELECT * from emp WHERE common is null 

#查询有提成的

SELECT * from emp WHERE common is not NULL

SELECT * from emp WHERE not common is NULL

#计算员工工资

#null与任何值相加都为null

SELECT * ,salay+common from emp

SELECT salay+common from emp

#IFNULL(expr1,expr2)用法

#假如expr1不为NULL，则IFNULL()的返回值为expr1; 否则其返回值为expr2

SELECT salay+IFNULL(common,500) from emp
2、起别名as

#as (可以省略)：起小名，一般别名在子查询中方便

SELECT * ,salay+IFNULL(common,500) as 总工资 from emp

#查询姓名和总工资

SELECT e_name 姓名,salay+IFNULL(common,0) 工资 from emp as a
3、BETWEEN AND

#在两个参数范围之间进行搜索，介于两者之间都包含

SELECT * from emp WHERE salay BETWEEN 4500 AND 5000
4、IN

#相当于一个容器集合

SELECT * from emp WHERE id in (2,4)
5、模糊查询like

#一般与通配符一起使用

#通配符：_单个字符 %任意个数的字符

SELECT * from emp WHERE e_name LIKE "张_"

SELECT * from emp WHERE e_name LIKE "张%"

SELECT * from emp WHERE e_name LIKE "%三%"
6、去重DISTINCT

#重复字段去重

SELECT DISTINCT e_name from emp
7、排序 ORDER BY

#desc降序 asc升序(默认，可省略)

SELECT * from emp ORDER BY salay asc

SELECT * from emp ORDER BY salay desc

#按总工资降序

SELECT * ,salay+IFNULL(common,0) total from emp ORDER BY total
8、聚合函数 sum avg count max min

#统计总提成

SELECT sum(common) from emp

#求提成的平均值

#avg只能统计不为null的个数

SELECT avg(common) from emp

#count函数中的参数为数字，代表统计的是记录

#统计记录的数量

SELECT count(1) from emp

SELECT count(*) from emp

#求最大值

SELECT max(common) from emp

#求最小值

SELECT min(common) from emp
9、分组GROUP BY

#一般与聚合函数一块使用

#添加字段

ALTER TABLE emp ADD COLUMN depart VARCHAR(10)

#按部门总成本排序

SELECT depart,sum(salay) a,sum(common) b,sum(salay+IFNULL(common,0)) c from emp GROUP BY depart ORDER BY c desc
10、WHERE 与 HAVING

#where放在表名后过滤大的条件

#having一般与分组合用过滤分组后的条件

SELECT depart,sum(salay) a,sum(common) b,sum(salay+IFNULL(common,0)) c from emp GROUP BY depart HAVING sum(salay+IFNULL(common,0)) >= 21000
11、分页limit

#limit一个参数代表查询的数量

SELECT * from emp LIMIT 5

#limit两个参数：第一个参数代表开始搜索的位置,位置从0开始;第二个参数代表查询的数量

SELECT * from emp LIMIT 3,4

六、多表联查

ALTER TABLE emp CHANGE depart e_d_id int

CREATE TABLE depart(

   id int PRIMARY key auto_increment,

   dname VARCHAR(10)

)
1、子查询

#以一个查询结果为另一个查询的条件

SELECT * from emp WHERE salay in(SELECT salay from emp WHERE salay > 5000)
2、内连接：只有匹配成功的数据才会搜索出来

#显示内连接 INNER JOIN…ON 条件

SELECT * from emp a INNER JOIN depart b ON a.e_d_id = b.id

#隐式内连接

SELECT e_d_id 序号,b.dname 部门,count(*) 人数 from emp a,depart b WHERE a.e_d_id = b.id GROUP BY a.e_d_id ORDER BY 序号

SELECT a.e_name,a.salay,b.dname from emp a,depart b WHERE a.e_d_id = b.id
3、笛卡尔积：多表联查没条件，多张表中记录数相乘
4、外连接：不但返回符合连接和查询条件的数据还返回一些不符合条件的一些数据

#左(外)连接：以左表为基础表

SELECT * from emp a LEFT JOIN depart b ON a.e_d_id = b.id

SELECT * from emp a LEFT OUTER JOIN depart b ON a.e_d_id = b.id

#右(外)连接：以右表为基础表

SELECT * from emp a RIGHT JOIN depart b ON a.e_d_id = b.id

SELECT * from emp a RIGHT OUTER JOIN depart b ON a.e_d_id = b.id
5、交叉连接：一般出现笛卡尔积现象
SELECT * from emp a CROSS JOIN depart b ON a.e_d_id = b.id
6、去重/不去重连接

#UNION：把多张表查询的结果(去重后)连接到一起

#union all:把多张表查询的结果(没有去重)连接到一起

七、外键

#关联表，可重复，可为null值，外键一定是另一张表的主键

#作用：保证数据的完整性和一致性

#关键字：FOREIGN KEY

#创建外键：CONSTRAINT约束 REFERENCES关联

1、创建表时创建外键
CREATE TABLE oders (

   o_id int NOT NULL,

   orderNo int NOT NULL,

   e_id int,

   PRIMARY KEY (o_id),

   FOREIGN KEY (e_id) REFERENCES emp (id)

)
2、创建表后添加外键

#语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名字 FOREIGN KEY (外键字段) REFERENCES 父表 (主键字段)

ALTER TABLE oders ADD CONSTRAINT oders_ibfk_1 FOREIGN KEY (e_id) REFERENCES emp (id)
3、删除外键

#语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名

ALTER TABLE oders DROP FOREIGN KEY oders_ibfk_1

#删除数据时，先删除外键表中的数据再删除关联表中的数据

DELETE from oders WHERE o_id = 1

DELETE from emp where id = 1

八、事务

1、事务

主要用于处理操作量大，复杂度高的数据，一个程序执行的最小单元

2、事务的特征(ACID)

原子性：事务的不可分割，组成事务的各个逻辑单元不可分割

一致性：事务执行的前后，数据完整性保持一致

隔离性：事务执行不应该受到其他事务的干扰

持久性：事务一旦结束，数据就持久化到数据库中

3、隔离级别

脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致

不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致

虚读/幻读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致

4、传播行为

PROPAGATION_REQUIRED(传播需求)：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置

PROPAGATION_NESTED(传播嵌套)：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作

PROPAGATION_SUPPORTS(传播支持)：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行

PROPAGATION_MANDATORY(强制性的传播)：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常

PROPAGATION_REQUIRES_NEW(传播需要新的)：支持当前事务，创建新事务，无论当前存不存在事务，都创建新事务

PROPAGATION_NOT_SUPPORTED(传播不支持)：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起

PROPAGATION_NEVER(不传播)：以非事务方式执行，如果当前存在事务，则抛出异常

5、创建事务 TRANSACTION

(1)BEGIN; #开始事务

INSERT INTO stu(s_name,s_sex,s_age) VALUES ('张三','男',21);

COMMIT;   #提交事务

(2)BEGIN; #开始事务

INSERT INTO stu(s_name,s_sex,s_age) VALUES ('admin','男',21);

ROLLBACK;    #回滚事务

九、JDBC

jdbc：(Java DataBase Connectivity,java数据库连接)

是一种用于执行SQL语句的Java API

1、下载驱动jar包：

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

2、jdbc连接步骤
  1. 加载驱动
  2. 创建链接
  3. 创建sql通道
  4. 执行sql
  5. 关闭资源
3、添加jar文件

第一种：

在这里插入图片描述

在这里插入图片描述

第二种：

在这里插入图片描述

在这里插入图片描述

4、示例代码

jdbc连接数据库

//路径:localhost接数或127.0.0.1
String url = "jdbc:mysql://localhost:3306/test?serverTimezone = UTC", username = "root", password = "123456";
String sql = "insert into emp (e_name, salay, common, age, sex) values('肉丝', 3000, 200, 32, '女')";
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection(url, username, password);
//创建sql通道
Statement stat = conn.createStatement();
//执行sql 返回值代表受影响的行数
int rows = stat.executeUpdate(sql);
if(rows > 0) {
	System.out.println("添加成功!");
}else {
	System.out.println("添加失败!");
}
//关资源
if(null != stat) {
	stat.close();
}
if(null != conn) {
	conn.close();
}

jdbc查询结果集

String sql1 = "select * from emp";
ResultSet rs = stat.executeQuery(sql1);
//判断是否有下一个
while(rs.next()) {
    //getString参数为int代表列的序号,参数为String代表列的列名
    String name= rs.getString(2);
    int salay = rs.getInt(3);
    int comm = rs.getInt("common");
    System.out.println(name + "-" + salay + "-" + comm);
}

预编译通道与普通通道区别

  1. 继承Statement
  2. 多次执行同一条sql效率高
  3. 防止sql注入

sql注入

String uname = "admin";
String upass = "***";
String url = "jdbc:mysql://localhost:3306/test?serverTimezone = UTC";
String username = "root";
String password = "123456";
String sql1 = "select * from user where username = '" + uname + "' and upass = '" + upass + "' or 1 = 1";
String sql2 = "select * from user where username = ? and upass = ?";
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//创建连接
Connection conn = DriverManager.getConnection(url, username, password);
//创建sql通道
Statement stat = conn.createStatement();
PreparedStatement psta = conn.prepareStatement(sql2);
//预编译通道执行前献给?赋值;?的数据类型是啥就set啥,
//方法的第一个参数是?的位置序号,第二个参数的赋的值
psta.setString(1, "admin");
psta.setString(2, "123456 or 1 = 1");
//预编译通道可以用?当作占位符
ResultSet rs = psta.executeQuery();
//普通通道执行
ResultSet rs = stat.executeQuery(sql1);
while(rs.next()) {
    String loginName = rs.getString(2);
    String loginPass = rs.getString(3);
    System.out.println(loginName + "--" + loginPass);
}

jdbc手动提交事务

设置自动提交(开启事务),默认是true

setAutoCommit(false);

手动提交事务

commit();

批处理

添加批处理的sql:addBatch()

执行批量sql:executeBatch()

5、开发流程

在这里插入图片描述

「miraitowa」
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值