MySQL之基础总结部分 Oracle实战优化:递归+分析函数+OLAP函数的应用 Linux中MySQL数据库的使用③-----编码和基本数据类型 理论+实验:MySQL备份与恢复(完整备份、增量备份) “数”聚永川 “智”引未来——永川区大数据智能化产业发展强劲 从一款防疫App感受新加坡大数据智能化气息 “数”聚永川“智”引未来——永川区大数据智能化产业发展强劲 从连接量变到数据质变 物联网将二次爆发 重磅发布!猎芯半导体首创全球最小支持5G物联网的多模多频射频PA芯片 从精准授信到助企惠民,江苏银行物联网金融派上大用场 Python Selenium UI自动化_WebDriver元素_8大定位方式+总结(持续更新完善) Python中的继承、抽象基类和接口 Datawhale学习笔记【阿里云天池 金融风控-贷款违约预测】task1 赛题理解 Pytorch - torchvision计算机视觉工具库 linux 重点笔记 Ubuntu18.04安装ROS Melodic(一路到站型) 小甲鱼笔记:数据结构——线性表(一)线性表的顺序存储结构,线性表顺序存储结构的增,删,插入元素操作 实战比特币脚本编程(1) JAVA WEB DAY 01_Tomcat & Servlet Java基础算法之堆排序(Heap Sort) synchronized批量重偏向与批量撤销 终于等到了!阿里P8历时九个月整理,Java面试宝典,核心知识点笔记在此 “数字心脏”动态解析消费密码,国家级消费市场大数据联合实验室在上海先行先试 全世界运行着大约230亿台物联网设备,安全问题如何解? 物联网产业园&thinkplus解决方案中心国学讲座如期而至 都是程序员,凭什么他能站在鄙视链的顶端? 猛男必看!去小红书做程序员是种什么体验 drozer提示[Errno 2] No such file or directory 【STM32】NB-iOT BC35-G模块 AT指令应用设计指导(附代码) 【北京迅为】i.MX6ULL终结者编译LED汇编程序 Linux系统读写网卡PHY寄存器工具 洛谷:P1226 【模板】快速幂||取余运算(分治,数学) 【2020顶会KDD】AutoST:面向时空预测的高效神经网络学习模型 C/C++实现并查集disjoint_set的模板(带路径压缩优化) 实现一个百万级推送服务,除了它,还有谁 “健康守护者”——STM32标准库和HAL库的比较 程序员被公司辞退12天后,前领导要求回公司讲清代码,结果懵了 RTTR实现C++反射(1)集成rttr库 lotus node 远程运行 CCF历年4,5题收录 N卡 RTX3070/RTX3080/RTX3090挖矿算力推测 最高算力86MH 理论+实验:MySQL索引、事物与存储引擎 PostgreSQL JOIN 多表查询 TP框架实现Excel批量导入数据库数据 MySQL中的列转行 这次是真拯救了我,MySQL索引优化,explain讲得非常清楚了 Oracle快速入门(PLSQL编程) MySQL字符串拼接、截取 MySQL事务管理及存储引擎 《浪姐》万茜点赞宁静、郁可唯黑贴坐实?盗号者和程序员是背锅侠
您的位置:首页 >开发 >

MySQL之基础总结部分

前段时间因为开学 所以到现在才更新 如有不足之处欢迎指出。

一、为什么要学习数据库

1.1、数据库的好处

​ 1.持久化数据到本地 ​ 2.可以实现结构化查询,方便管理

1.2、数据库的相关概念

​ 1、DB:数据库,保存一组有组织的数据的容器 ​ 2、DBMS:数据库管理系统,又称为数据库软件(产品),用于管理DB中的数据 ​ 3、SQL:结构化查询语言,用于和DBMS通信的语言

1.3、数据库存储数据的特点

​ 1、将数据放到表中,表再放到库中 ​ 2、一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。 ​ 3、表具有一些特性,这些特性定义了数据在表中如何存储,类似java中 “类”的设计。 ​ 4、表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java 中的”属性” ​ 5、表中的数据是按行存储的,每一行类似于java中的“对象”。

常见的数据库管理系统 mysql、oracle、db2、sqlserver

二、初始MySQL

2.1、MySQL服务的启动和停止

​ 方式一:计算机——右击管理——服务 ​ 方式二:通过管理员身份运行 ​ net start 服务名(启动服务) ​ net stop 服务名(停止服务)

2.2、MySQL服务的登录和退出

​ 方式一:通过mysql自带的客户端 ​ 只限于root用户

方式二:通过windows自带的客户端登录:mysql 【-h主机名 -P端口号 】-u用户名 -p密码1. mysql -uroot -p密码2. mysql -hip -uroot -p连接目标的密码3. mysql --host=ip --user=root --password=连接目标的密码退出:1.exit或ctrl+C2. quit

2.3、MySQL的常见命令

1.查看当前所有的数据库show databases;2.打开指定的库use 库名3.查看当前库的所有表show tables;4.查看其它库的所有表show tables from 库名;5.创建表create table 表名(列名 列类型,列名 列类型,。。。);6.查看表结构desc 表名;7.查看服务器的版本方式一:登录到mysql服务端select version();方式二:没有登录到mysql服务端mysql --version或mysql --V

2.4、MySQL的语法规范

​ 1.不区分大小写,但建议关键字大写,表名、列名小写 ​ 2.每条命令最好用分号结尾 ​ 3.每条命令根据需要,可以进行缩进 或换行 ​ 4.注释 ​ 单行注释:#注释文字 ​ 单行注释:-- 注释文字 ​ 多行注释:/* 注释文字 */ ​

2.5、SQL的语言分类

​ 1) DDL(Data Definition Language)数据定义语言

​ 用来定义数据库对象:数据库,表,列等。关键字:create, drop,alter 等

​ 2) DML(Data Manipulation Language)数据操作语言

​ 用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等

​ 3) DQL(Data Query Language)数据查询语言

​ 用来查询数据库中表的记录(数据)。关键字:select, where 等

​ 4) DCL(Data Control Language)数据控制语言(了解)

​ 用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等

​ TCL(Transaction Control Language):事务控制语言 ​ commit、rollback

2.6、SQL的常见命令

show databases; 查看所有的数据库use 库名; 打开指定 的库show tables ; 显示库中的所有表show tables from 库名;显示指定库中的所有表create table 表名(字段名 字段类型,字段名 字段类型); 创建表desc 表名; 查看指定表的结构select * from 表名;显示表中的所有数据

2.7、卸载

​ 1. 去mysql的安装目录找到my.ini文件

​ * 复制 datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”

​ 2. 卸载MySQL

​ 3. 删除C:/ProgramData目录下的MySQL文件夹。

三、DQL语言的学习

3.1、基础查询

一、语法: SELECT 查询列表 【FROM 表名】;

语法:

​ select

​ 字段列表

​ from

​ 表名列表

​ where

​ 条件列表

​ group by

​ 分组字段

​ having

​ 分组之后的条件

​ order by

​ 排序

​ limit

​ 分页限定

类似于Java中 :System.out.println(要打印的东西);特点:①通过select查询完的结果 ,是一个虚拟的表格,不是真实存在② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

二、特点 1、查询列表可以是字段、常量、表达式、函数,也可以是多个 2、查询结果是一个虚拟表

三、示例1、查询单个字段select 字段名 from 表名;2、查询多个字段select 字段名,字段名 from 表名;3、查询所有字段select * from 表名4、查询常量select 常量值;注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要5、查询函数select 函数名(实参列表);6、查询表达式select 100/1234;7、起别名①as②空格8、去重select distinct 字段名 from 表名;计算列* 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)* ifnull(表达式1,表达式2):null参与的运算,计算结果都为null* 表达式1:哪个字段需要判断是否为null* 如果该字段为null后的替换值。-- 计算math和english分数之和SELECT NAME,math,english,math+english AS final FROM student;-- 如果有null参与的运算,计算结果都为nullSELECT NAME,math,english,math+IFNULL(english,0) AS final FROM student;9、+作用:做加法运算select 数值+数值; 直接运算select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算select null+值;结果都为null10、【补充】concat函数功能:拼接字符select concat(字符1,字符2,字符3,...);11、【补充】ifnull函数功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值select ifnull(commission_pct,0) from employees;12、【补充】isnull函数功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0

3.2、条件查询

​ 条件查询:根据条件过滤原始表的数据,查询到想要的数据 一、语法: select 要查询的字段|表达式|常量值|函数 ​ from 表名 ​ where 条件 ;

分类:1、条件表达式示例:salary>100002. 运算符 条件运算符:* > 、< 、<= 、>= 、= 、<>* BETWEEN...AND* IN( 集合)在某个范围之内* LIKE:模糊查询* 占位符:* _:单个任意字符* %:多个任意字符* IS NULL* and或 &&* or或 || * not或 !3、逻辑表达式示例:salary>10000 && salary<20000逻辑运算符:and(&&):两个条件如果同时成立,结果为true,否则为falseor(||):两个条件只要有一个成立,结果为true,否则为falsenot(!):如果条件成立,则not后为false,否则为true三、模糊查询like:一般搭配通配符使用,可以判断字符型或数值型通配符:%任意多个字符,_任意单个字符示例:last_name like 'a%'between andinis null /is not null:用于判断null值is null PK <=>普通类型的数值null值可读性is null×√√<=>√√×-- 查询年龄大于20岁(练习)SELECT * FROM student WHERE age > 20;-- 查询年龄等于20岁SELECT * FROM student WHERE age = 20;-- 查询年龄不等于20岁SELECT * FROM student WHERE age != 20;SELECT * FROM student WHERE age <> 20;-- 查询年龄大于等于20 小于等于30SELECT * FROM student WHERE age >= 20 &&age <=30;不推荐SELECT * FROM student WHERE age >= 20 ANDage <=30;SELECT * FROM student WHERE age BETWEEN 20 AND 30;-- 查询年龄22岁,18岁,25岁的信息SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25SELECT * FROM student WHERE age IN (22,18,25);-- 查询英语成绩为nullSELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用 = (!=) 判断SELECT * FROM student WHERE english IS NULL;-- 查询英语成绩不为nullSELECT * FROM student WHERE englishIS NOT NULL;-- 查询姓马的有哪些? likeSELECT * FROM student WHERE NAME LIKE '马%';-- 查询姓名第二个字是化的人SELECT * FROM student WHERE NAME LIKE "_化%";-- 查询姓名是3个字的人SELECT * FROM student WHERE NAME LIKE '___';-- 查询姓名中包含德的人SELECT * FROM student WHERE NAME LIKE '%德%';

3.3、排序查询

语法:select查询列表from表where 条件order by 排序的字段|表达式|函数|别名 【asc|desc】* 注意:* 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。-- 按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名SELECT *FROM student ORDER BY math ,english ASC;二、特点1、asc :升序,如果不写默认升序desc:降序2、排序列表 支持 单个字段、多个字段、函数、表达式、别名3、order by的位置一般放在查询语句的最后(除limit语句之外)

3.4、常见函数

​ 一、单行函数 ​ 1、字符函数 ​ concat 拼接 ​ substr 截取子串 ​ upper 转换成大写 ​ lower 转换成小写 ​ trim 去前后指定的空格和字符 ​ ltrim 去左边空格 ​ rtrim 去右边空格 ​ replace 替换 ​ lpad 左填充 ​ rpad 右填充 ​ instr 返回子串第一次出现的索引 ​ length 获取字节个数 ​

2、数学函数round 四舍五入rand 随机数floor 向下取整ceil 向上取整mod 取余truncate 截断3、日期函数now 当前系统日期+时间curdate 当前系统日期curtime 当前系统时间str_to_date 将字符转换成日期date_format 将日期转换成字符4、流程控制函数if 处理双分支case语句 处理多分支情况1:处理等值判断情况2:处理条件判断5、其他函数version版本database当前库user当前连接用户

二、分组函数 sum 求和 max 最大值 min 最小值 avg 平均值 count 计数

特点:1、以上五个分组函数都忽略null值,除了count(*)2、sum和avg一般用于处理数值型max、min、count可以处理任何数据类型3、都可以搭配distinct使用,用于统计去重后的结果4、count的参数可以支持:字段、*、常量值,一般放1-- 使用聚合函数查看总人数SELECT COUNT(NAME) AS '总人数' FROM student; 建议使用 count(*)* 注意:聚合函数的计算,排除null值。解决方案:如下代码1. 选择不包含非空的列进行计算2. IFNULL函数SELECT COUNT(english) FROM student;SELECT COUNT(IFNULL(english,0)) FROM student;

3.5、分组查询

1、语法: ​ select 查询的字段,分组函数 ​ from 表 ​ group by 分组的字段 ​2、注意:

分组之后查询的字段:分组字段、聚合函数

where 和 having 的区别?

​ 1. where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来

​ 2. where 后不可以跟聚合函数,having可以进行聚合函数的判断。

特点:1、可以按单个字段分组2、和分组函数一同查询的字段最好是分组后的字段3、分组筛选 针对的表 位置关键字分组前筛选:原始表 group by的前面 where分组后筛选:分组后的结果集 group by的后面having4、可以按多个字段分组,字段之间用逗号隔开5、可以支持排序6、having后可以支持别名练习:-- 按照性别分组。分别查询男、女同学的平均分SELECT sex , AVG(math) FROM student GROUP BY sex;-- 按照性别分组。分别查询男、女同学的平均分,人数SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;--按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于60分的人,不参与分组SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 60 GROUP BY sex;--按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于60分的人,不参与分组,分组之后。人数要大于2个人SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 60 GROUP BY sex HAVING COUNT(id) > 2;SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 60 GROUP BY sex HAVING 人数 > 2;

3.6、多表连接查询

笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接如何解决:添加有效的连接条件* 有两个集合A,B .取这两个集合的所有组成情况。* 要完成多表查询,需要消除无用的数据

3.6.1、传统模式下的连接 :等值连接——非等值连接

1、等值连接语法:select 查询列表from 表1 别名,表2 别名where 表1.key = 表2.key【and 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序字段】特点:① 等值连接的结果 = 多个表的交集② n表连接,至少需要n-1个连接条件③ n表连接至少需要n-1个连接条件④ 等值连接的结果是多表的交集部分2、非等值连接语法:select 查询列表from 表1 别名,表2 别名where 非等值的连接条件【and 筛选条件】【group by 分组字段】【having 分组后的筛选】【order by 排序字段】

3.6.2、多表查询

1、内连接语法:select 查询列表from 表1 别名【inner】 join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表limit 子句;特点:① 表的顺序可以调换② 内连接的结果=多表的交集③ n表连接至少需要n-1个连接条件分类:等值连接非等值连接自连接2、外连接语法:select 查询列表from 表1 别名left|right|full【outer】 join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表limit 子句;特点:①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null②left join 左边的就是主表,right join 右边的就是主表 full join 两边都是主表③一般用于查询除了交集部分的剩余的不匹配的行练习:* 准备sql# 创建部门表CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');# 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dept_id INT,FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键));INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);1. 内连接查询:1. 隐式内连接:使用where条件消除无用数据* 例子:-- 查询所有员工信息和对应的部门信息SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;-- 查询员工表的名称,性别。部门表的名称SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;起别名:SELECT t1.name, -- 员工表的姓名t1.gender,-- 员工表的性别t2.name -- 部门表的名称FROMemp t1,dept t2WHERE t1.`dept_id` = t2.`id`;2. 显式内连接:* 语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件* 例如:* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;3. 内连接查询:1. 从哪些表中查询数据2. 条件是什么3. 查询哪些字段2. 外链接查询:1. 左外连接:* 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;* 查询的是左表所有数据以及其交集部分。* 例子:-- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;-- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;2. 右外连接:* 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;* 查询的是右表所有数据以及其交集部分。* 例子:SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;3、交叉连接语法:select 查询列表from 表1 别名cross join 表2 别名;特点:类似于笛卡尔乘积

3.6.3、自连接

案例:查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_nameFROM employees eJOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_nameFROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

3.7、子查询

含义:

嵌套在其他语句内部的select语句称为子查询或内查询,外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多外面如果为select语句,则此语句称为外查询或主查询

特点:

1、子查询都放在小括号内2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧3、子查询优先于主查询执行,主查询使用了子查询的执行结果4、子查询根据查询结果的行数不同分为以下两类:① 单行子查询结果集只有一行一般搭配单行操作符使用:> < = <> >= <= 非法使用子查询的情况:a、子查询的结果为一组值b、子查询的结果为空② 多行子查询结果集有多行一般搭配多行操作符使用:any、all、in、not inin: 属于子查询结果中的任意一个就行any和all往往可以用其他查询代替
* 子查询不同情况1. 子查询的结果是单行单列的:* 子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= =-- 查询员工工资小于平均工资的人SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);2. 子查询的结果是多行单列的:* 子查询可以作为条件,使用运算符in来判断-- 查询'财务部'和'市场部'所有的员工信息SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;-- 子查询SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');3. 子查询的结果是多行多列的:* 子查询可以作为一张虚拟表参与查询-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息-- 子查询SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2WHERE t1.id = t2.dept_id;-- 普通内连接SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >'2011-11-11'

3.8、分页查询

语法:

select 字段|表达式,...from 表【where 条件】【group by 分组字段】【having 条件】【order by 排序的字段】limit 【起始的条目索引,】条目数;

分页查询

1.语法:limit 开始的索引,每页查询的条数; 起始条目索引从0开始,limit子句放在查询语句的最后 2. 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数 -- 每页显示3条记录 SELECT * FROM student LIMIT 0,3; -- 第1页SELECT * FROM student LIMIT 3,3; -- 第2页SELECT * FROM student LIMIT 6,3; -- 第3页3.公式:select * from表 limit (page-1)*sizePerPage,sizePerPage假如:每页显示条目数sizePerPage要显示的页数 page

3.9、联合查询

引入: union:合并、联合,将多次查询结果合并成一个结果

语法:

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】select 字段|常量|表达式|函数 【from 表】 【where 条件】 union【all】.....select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点:

1、多条查询语句的查询的列数必须是一致的2、多条查询语句的查询的列的类型几乎相同3、union代表去重,union all代表不去重

四、DML语言的学习

4.1、插入

语法: insert into 表名(字段名,…) values(值1,…);

特点:

* 注意:1. 列名和值要一一对应。2. 如果表名后,不定义列名,则默认给所有列添加值insert into 表名 values(值1,值2,...值n);3. 除了数字类型,其他类型需要使用引号(单双都可以)引起来一、方式一语法:insert into 表名(字段名,...) values(值,...);特点:1、要求值的类型和字段的类型要一致或兼容2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致但必须保证值和字段一一对应3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值①字段和值都省略②字段写上,值使用null4、字段和值的个数必须一致5、字段名可以省略,默认所有列二、方式二语法:insert into 表名 set 字段=值,字段=值,...;两种方式 的区别:1.方式一支持一次插入多行,语法如下:insert into 表名【(字段名,..)】 values(值,..),(值,...),...;2.方式一支持子查询,语法如下:insert into 表名查询语句;

4.2、修改

修改单表语法:

update 表名 set 字段=新值,字段=新值【where 条件】

修改多表语法:

update 表1 别名1,表2 别名2set 字段=新值,字段=新值where 连接条件and 筛选条件* 注意: 如果不加任何条件,则会将表中所有记录全部修改。

4.3、删除

方式1:delete语句

一、删除单表的记录★ 语法:delete from 表名 【where 筛选条件】【limit 条目数】

二、级联删除[补充] 语法:

delete 别名1,别名2 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 【where 筛选条件】

方式2:truncate语句

truncate table 表名

两种方式的区别【面试题】

一般删除速度 :drop>truncate>delete应用范围:truncate只能对table 而delete可以是table和view1.truncate删除后,如果再插入,标识列从1开始delete删除后,如果再插入,标识列从断点开始2.delete可以添加筛选条件 truncate不可以添加筛选条件3.truncate效率较高4.truncate没有返回值delete可以返回受影响的行数5.truncate不可以回滚delete可以回滚

五、DCL语言的学习

DCL:管理用户,授权

DBA:数据库管理员DCL:管理用户,授权

5.1、管理用户

1. 添加用户:* 语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';2. 删除用户:* 语法:DROP USER '用户名'@'主机名';3. 修改用户密码:UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');* mysql中忘记了root用户的密码?1. cmd -- > net stop mysql 停止mysql服务* 需要管理员运行该cmd2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功4. use mysql;5. update user set password = password('你的新密码') where user = 'root';6. 关闭两个窗口7. 打开任务管理器,手动结束mysqld.exe 的进程8. 启动mysql服务9. 使用新密码登录。4. 查询用户:-- 1. 切换到mysql数据库USE myql;-- 2. 查询user表SELECT * FROM USER;* 通配符: % 表示可以在任意主机使用用户登录数据库

5.2 、权限管理

1. 查询权限: -- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';2. 授予权限: -- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限,在任意数据库任意表上 GRANT ALL ON *.* TO 'wanghui'@'localhost';3. 撤销权限: -- 撤销权限: revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; REVOKE UPDATE ON wh.`account` FROM 'lisi'@'%';

六、DDL语言的学习

6.1、库和表的管理

库的管理:

1. 操作数据库:CRUD1. C(Create):创建* 创建数据库:* create database 数据库名称;* 创建数据库,判断不存在,再创建:* create database if not exists 数据库名称;* 创建数据库,并指定字符集* create database 数据库名称 character set 字符集名;* 练习: 创建wh数据库,判断是否存在,并制定字符集为gbk* create database if not exists wh character set gbk;2. R(Retrieve):查询* 1.查询所有数据库的名称:* show databases;* 2.查询某个数据库的字符集:查询某个数据库的创建语句* show create database 数据库名称;3. U(Update):修改* 修改数据库的字符集* alter database 数据库名称 character set 字符集名称;alter databasewh character set utf8; //这里我们不能写utf-84. D(Delete):删除* 删除数据库* drop database 数据库名称;* 判断数据库存在,存在再删除* drop database if exists 数据库名称;5. 使用数据库* 查询当前正在使用的数据库名称* select database();* 使用数据库* use 数据库名称;

表的管理:

2. 操作表1. C(Create):创建重点1. 语法:create table 表名(列名1 数据类型1,列名2 数据类型2,....列名n 数据类型n);* 注意:最后一列,不需要加逗号(,)* 创建表create table student(id int,name varchar(32),age int ,score double(4,1),birthday date,insert_time timestamp);* 复制表:* create table 表名 like 被复制的表名;2. R(Retrieve):查询* 查询某个数据库中所有的表名称* show tables;* 查询表结构* desc 表名;3. U(Update):修改1. 修改表名alter table 表名 rename to 新的表名;2. 修改表的字符集alter table 表名 character set 字符集名称;3. 添加一列alter table 表名 add 列名 数据类型;4. 修改列名称 类型alter table 表名 change 列名 新列别 新数据类型;alter table 表名 modify 列名 新数据类型; 只改类型5. 删除列alter table 表名 drop 列名;4. D(Delete):删除* drop table 表名;* drop tableif exists 表名 ;

6.2、常见类型

一、数值型1、整型tinyint、smallint、mediumint、int/integer、bigint1 2348特点:①都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号②如果超出了范围,会报out or range异常,插入临界值③长度可以不指定,默认会有一个长度长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型2、浮点型定点数:decimal(M,D)浮点数:float(M,D) 4double(M,D)8特点:①M代表整数部位+小数部位的个数,D代表小数部位②如果超出范围,则报out or range异常,并且插入临界值③M和D都可以省略,但对于定点数,M默认为10,D默认为0④如果精度要求较高,则优先考虑使用定点数二、字符型char、varchar、binary、varbinary、enum、set、text、blobchar:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略三、日期型year年date日期time时间datetime 日期+时间8timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间

6.3、常见约束

NOT NULL:非空,该字段的值必填UNIQUE:唯一,该字段的值不可重复DEFAULT:默认,该字段的值不用手动插入有默认值CHECK:检查,mysql不支持PRIMARY KEY:主键,该字段的值不可重复并且非空unique+not nullFOREIGN KEY:外键,该字段的值引用了另外的表的字段
主键和唯一1、区别:①、一个表至多有一个主键,但可以有多个唯一②、主键不允许为空,唯一可以为空2、相同点都具有唯一性都支持组合键,但不推荐外键:1、用于限制两个表的关系,从表的字段值引用了主表的某字段值2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求3、主表的被引用列要求是一个key(一般就是主键)4、插入数据,先插入主表删除数据,先删除从表可以通过以下两种方式来删除主表的记录分类:1. 级联更新:ON UPDATE CASCADE 2. 级联删除:ON DELETE CASCADE #方式一:级联删除语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 OREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称)ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;#方式二:级联置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;二、创建表时添加约束create table 表名(字段名 字段类型 not null,#非空字段名 字段类型 primary key,#主键字段名 字段类型 unique,#唯一字段名 字段类型 default 值,#默认constraint 约束名 foreign key(字段名) references 主表(被引用列))注意:支持类型可以起约束名列级约束除了外键不可以表级约束除了非空和默认可以,但对主键无效列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求三、修改表时添加或删除约束1、非空添加非空alter table 表名 modify column 字段名 字段类型 not null;删除非空alter table 表名 modify column 字段名 字段类型 ;2、默认添加默认alter table 表名 modify column 字段名 字段类型 default 值;删除默认alter table 表名 modify column 字段名 字段类型 ;3、主键添加主键alter table 表名 add【 constraint 约束名】 primary key(字段名);删除主键alter table 表名 drop primary key;4、唯一添加唯一alter table 表名 add【 constraint 约束名】 unique(字段名);删除唯一alter table 表名 drop index 索引名;5、外键添加外键alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);删除外键alter table 表名 drop foreign key 约束名;四、自增长列1.概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长2、一个表至多有一个自增长列3、自增长列只能支持数值型4、自增长列必须为一个key一.在创建表时,添加主键约束,并且完成主键自增长create table stu(id int primary key auto_increment,-- 给id添加主键约束name varchar(20));二. 删除自动增长 alter table 表 modify column 字段名 字段类型 约束 ALTER TABLE stu MODIFY id INT;三. 添加自动增长 alter table 表 modify column 字段名 字段类型 约束 auto_incrementALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

七、TCL语言的学习

7.1、数据库事务

一、含义 事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行

二、特点(ACID) A 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行 C 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态 I 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的 D 持久性:一个事务一旦提交了,则永久的持久化到本地

相关步骤:

1. 开启事务: start transaction;2. 回滚:rollback;3. 提交:commit;* 修改事务的默认提交方式:* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交0 代表手动提交* 修改默认提交方式: set @@autocommit = 0;

事务的分类:

隐式事务,没有明显的开启和结束事务的标志

比如insert、update、delete语句本身就是一个事务

显式事务,具有明显的开启和结束事务的标志

①开启事务set autocommit=0;start transaction;#可以省略②编写一组逻辑sql语句注意:sql语句支持的是insert、update、delete设置回滚点:savepoint 回滚点名;③结束事务提交:commit;回滚:rollback;回滚到指定的地方:rollback to 回滚点名;

使用到的关键字

set autocommit=0;start transaction;commit;rollback;savepoint断点commit to 断点rollback to 断点

7.2、事务的隔离级别:

事务并发问题如何发生?

当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些?

脏读:一个事务读取到了另外一个事务未提交的数据不可重复读:同一个事务中,多次读取到的数据不一致幻读:一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题?

通过设置事务的隔离级别1、READ UNCOMMITTED 读未提交* 产生的问题:脏读、不可重复读、幻读2、READ COMMITTED 可以避免脏读 读已提交 (Oracle) * 产生的问题:不可重复读、幻读3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读 可重复读 (MySQL默认)* 产生的问题:幻读4、SERIALIZABLE 可以避免脏读、不可重复读和幻读 串行化 * 可以解决所有的问题

设置隔离级别:

set session|globaltransaction isolation level 隔离级别名;

查看隔离级别:

select @@tx_isolation;

八、视图

含义:理解成一张虚拟的表

视图和表的区别: 使用方式 占用物理空间

视图完全相同不占用,仅仅保存的是sql逻辑表完全相同占用

视图的好处:

好处:1、简化sql语句2、提高了sql的重用性3、保护基表的数据,提高了安全性

视图的创建 语法: CREATE VIEW 视图名 AS 查询语句;

视图的增删改查 1、查看视图的数据 ★

SELECT * FROM wh;SELECT * FROM wh WHERE last_name='Partners';2、插入视图的数据INSERT INTO wh(last_name,department_id) VALUES('孙悟空',90);3、修改视图的数据UPDATE wh SET last_name ='齐天大圣' WHERE last_name='孙悟空';4、删除视图的数据drop view 视图1,视图2,...;
###某些视图不能更新包含以下关键字的sql语句:分组函数、distinct、groupby、having、union或者union all常量视图Select中包含子查询joinfrom一个不能更新的视图where子句的子查询引用了from子句中的表视图逻辑的更新#方式一:CREATE OR REPLACE VIEW whASSELECT last_name FROM employeesWHERE employee_id>100;#方式二:ALTER VIEW whASSELECT employee_id FROM employees;SELECT * FROM wh;
视图的删除DROP VIEW test_v1,test_v2,test_v3;###视图结构的查看desc 视图名;show create view 视图名;

视图和表的对比 关键字 是否占用物理空间 使用 视图 view 占用较小,只保存sql逻辑 一般用于查询 表 table 保存实际的数据 增删改查

九、变量

9.1、系统变量

分类说明:变量由系统提供的,不用自定义语法:①查看系统变量show 【global|session 】 variables like ''; 如果没有显式声明global还是session,则默认是session②查看指定的系统变量的值select @@【global|session】.变量名; 如果没有显式声明global还是session,则默认是session③为系统变量赋值方式一:set 【global|session 】 变量名=值; 如果没有显式声明global还是session,则默认是session方式二:set @@global.变量名=值;set @@变量名=值;
1、全局变量服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效作用域:针对于所有会话(连接)有效,但不能跨重启查看所有全局变量SHOW GLOBAL VARIABLES;查看满足条件的部分系统变量SHOW GLOBAL VARIABLES LIKE '%char%';查看指定的系统变量的值SELECT @@global.autocommit;为某个系统变量赋值SET @@global.autocommit=0;SET GLOBAL autocommit=0;2、会话变量服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)作用域:针对于当前会话(连接)有效查看所有会话变量SHOW SESSION VARIABLES;查看满足条件的部分会话变量SHOW SESSION VARIABLES LIKE '%char%';查看指定的会话变量的值SELECT @@autocommit;SELECT @@session.tx_isolation;为某个会话变量赋值SET @@session.tx_isolation='read-uncommitted';SET SESSION tx_isolation='read-committed';

9.2、自定义变量

说明:1、用户变量作用域:针对于当前连接(会话)生效位置:begin end里面,也可以放在外面使用:①声明并赋值:set @变量名=值;或set @变量名:=值;或select @变量名:=值;②更新值方式一:set @变量名=值;或set @变量名:=值;或select @变量名:=值;方式二:select xx into @变量名 from 表;③使用select @变量名;2、局部变量作用域:仅仅在定义它的begin end中有效位置:只能放在begin end中,而且只能放在第一句使用:①声明declare 变量名 类型 【default 值】;②赋值或更新方式一:set 变量名=值;或set 变量名:=值;或select @变量名:=值;方式二:select xx into 变量名 from 表;③使用select 变量名;

二者的区别:

作用域定义位置语法

用户变量 当前会话 会话的任何地方 加@符号,不用指定类型 局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

###分支 一、if函数 语法:if(条件,值1,值2) 特点:可以用在任何位置

二、case语句

语法:

情况一:类似于switchcase 表达式when 值1 then 结果1或语句1(如果是语句,需要加分号) when 值2 then 结果2或语句2(如果是语句,需要加分号)...else 结果n或语句n(如果是语句,需要加分号)end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)情况二:类似于多重ifcase when 条件1 then 结果1或语句1(如果是语句,需要加分号) when 条件2 then 结果2或语句2(如果是语句,需要加分号)...else 结果n或语句n(如果是语句,需要加分号)end 【case】(如果是放在begin end中需要加上case,如果放在select后面不需要)

特点: 可以用在任何位置

三、if elseif语句

语法:

if 情况1 then 语句1;elseif 情况2 then 语句2;...else 语句n;end if;

特点: 只能用在begin end中!!!!!!!!!!!!!!!

三者比较: 应用场合 if函数 简单双分支 case结构 等值判断 的多分支 if结构 区间判断 的多分支

###循环

语法:

【标签:】WHILE 循环条件DO循环体END WHILE 【标签】;

特点:

只能放在BEGIN END里面

如果要搭配leave跳转语句,需要使用标签,否则可以不用标签

leave类似于java中的break语句,跳出所在循环!!!

十、存储过程和函数

10.1、存储过程

含义:一组经过预先编译的sql语句的集合 好处:

1、提高了sql语句的重用性,减少了开发程序员的压力2、提高了效率3、减少了传输次数

分类:

1、无返回无参2、仅仅带in类型,无返回有参3、仅仅带out类型,有返回无参4、既带in又带out,有返回有参5、带inout,有返回有参注意:in、out、inout都可以在一个存储过程中带多个

一 创建存储过程 语法:

create procedure 存储过程名(in|out|inout 参数名参数类型,...)begin存储过程体end注意:1.参数模式:in、out、inout,其中in可以省略2.存储过程体的每一条sql语句都需要用分号结尾

类似于方法:

修饰符 返回类型 方法名(参数类型 参数名,...){方法体;}

注意

1、需要设置新的结束标记delimiter 新的结束标记示例:delimiter $CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型,...)BEGINsql语句1;sql语句2;END $2、存储过程体中可以有多条sql语句,如果仅仅一条sql语句,则可以省略begin end3、参数前面的符号的意思in:该参数只能作为输入 (该参数不能做返回值)out:该参数只能作为输出(该参数只能做返回值)inout:既能做输入又能做输出二、调用call 存储过程名(实参列表)举例:调用in模式的参数:call sp1(‘值’);调用out模式的参数:set @name; call sp1(@name);select @name;调用inout模式的参数:set @name=值; call sp1(@name); select @name;三、查看show create procedure 存储过程名;四、删除drop procedure 存储过程名;

10.2、函数

###创建函数

学过的函数:LENGTH、SUBSTR、CONCAT等 语法:

一、创建CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型BEGIN函数体END注意:函数体中肯定需要有return语句二、调用select 函数名(实参列表);三、查看show create function 函数名;四、删除drop function 函数名;

###函数和存储过程的区别

关键字调用语法返回值应用场景函数FUNCTIONSELECT 函数()只能是一个 一般用于查询结果为一个值并返回时,当有返回值而且仅仅一个存储过程PROCEDURECALL 存储过程()可以有0个或多个一般用于更新

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。