MySQL基础

本单元目标一、为什么要学习数据库二、数据库的相关概念 DBMS、DB、SQL三、数据库存储数据的特点四、初始MySQL MySQL产品的介绍 MySQL产品的安装 ★ MySQL服务的启动和停止 ★ MySQL服务的登录和退出 ★ MySQL的常见命令和语法规范五、DQL语言的学习 ★ 基础查询 ★ 条件查询 ★ 排序查询 ★ 常见函数 ★ 分组函数 ★ 分组查询 ★ 连接查询 ★ 子查询 √ 分页查询 ★ union联合查询 √ 六、DML语言的学习 ★ 插入语句修改语句删除语句七、DDL语言的学习库和表的管理 √ 常见数据类型介绍 √ 常见约束 √ 八、TCL语言的学习事务和事务处理九、视图的讲解 √十、变量十一、存储过程和函数十二、流程控制结构数据库的好处1.持久化数据到本地2.可以实现结构化查询，方便管理数据库相关概念1、DB：数据库，保存一组有组织的数据的容器2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据3、SQL:结构化查询语言，用于和DBMS通信的语言数据库存储数据的特点1、将数据放到表中，表再放到库中2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”5、表中的数据是按行存储的，每一行类似于java中的“对象”。MySQL产品的介绍和安装MySQL服务的启动和停止方式一：计算机——右击管理——服务方式二：通过管理员身份运行net start 服务名（启动服务）net stop 服务名（停止服务）MySQL服务的登录和退出方式一：通过mysql自带的客户端只限于root用户方式二：通过windows自带的客户端登录：mysql 【-h主机名 -P端口号】-u用户名 -p密码退出：exit或ctrl+CMySQL的常见命令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 –VMySQL的语法规范1.不区分大小写,但建议关键字大写，表名、列名小写2.每条命令最好用分号结尾3.每条命令根据需要，可以进行缩进或换行4.注释单行注释：#注释文字单行注释：– 注释文字多行注释：/* 注释文字 /SQL的语言分类DQL（Data Query Language）：数据查询语言 select DML(Data Manipulate Language):数据操作语言 insert 、update、deleteDDL（Data Define Languge）：数据定义语言 create、drop、alterTCL（Transaction Control Language）：事务控制语言 commit、rollbackSQL的常见命令show databases；查看所有的数据库use 库名；打开指定的库show tables ; 显示库中的所有表show tables from 库名;显示指定库中的所有表create table 表名( 字段名字段类型, 字段名字段类型); 创建表desc 表名; 查看指定表的结构select * from 表名;显示表中的所有数据DQL语言的学习进阶1：基础查询语法：SELECT 要查询的东西【FROM 表名】;类似于Java中 :System.out.println(要打印的东西);特点：①通过select查询完的结果，是一个虚拟的表格，不是真实存在② 要查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数进阶2：条件查询条件查询：根据条件过滤原始表的数据，查询到想要的数据语法：select 要查询的字段|表达式|常量值|函数from 表where 条件 ;分类：一、条件表达式示例：salary>10000 条件运算符： > < >= <= = != <>二、逻辑表达式示例：salary>10000 && salary<20000逻辑运算符： and（&&）:两个条件如果同时成立，结果为true，否则为false or(||)：两个条件只要有一个成立，结果为true，否则为false not(!)：如果条件成立，则not后为false，否则为true三、模糊查询示例：last_name like ‘a%‘进阶3：排序查询语法：select 要查询的东西from 表where 条件order by 排序的字段|表达式|函数|别名【asc|desc】进阶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 建议使用 count()进阶5：分组查询语法：select 查询的字段，分组函数from 表group by 分组的字段特点：1、可以按单个字段分组2、和分组函数一同查询的字段最好是分组后的字段3、分组筛选针对的表位置关键字分组前筛选：原始表 group by的前面 where分组后筛选：分组后的结果集 group by的后面 having4、可以按多个字段分组，字段之间用逗号隔开5、可以支持排序6、having后可以支持别名进阶6：多表连接查询笛卡尔乘积：如果连接条件省略或无效则会出现解决办法：添加上连接条件一、传统模式下的连接：等值连接——非等值连接1.等值连接的结果 = 多个表的交集2.n表连接，至少需要n-1个连接条件3.多个表不分主次，没有顺序要求4.一般为表起别名，提高阅读性和性能二、sql99语法：通过join关键字实现连接含义：1999年推出的sql语法支持：等值连接、非等值连接（内连接）外连接交叉连接语法：select 字段，…from 表1【inner|left outer|right outer|cross】join 表2 on 连接条件【inner|left outer|right outer|cross】join 表3 on 连接条件【where 筛选条件】【group by 分组字段】【having 分组后的筛选条件】【order by 排序的字段或表达式】好处：语句上，连接条件和筛选条件实现了分离，简洁明了！三、自连接案例：查询员工名和直接上级的名称sql99SELECT e.last_name,m.last_nameFROM employees eJOIN employees m ON e.manager_id=m.employee_id;sql92SELECT e.last_name,m.last_nameFROM employees e,employees m WHERE e.manager_id=m.employee_id;进阶7：子查询含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询特点：1、子查询都放在小括号内2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧3、子查询优先于主查询执行，主查询使用了子查询的执行结果4、子查询根据查询结果的行数不同分为以下两类：① 单行子查询结果集只有一行一般搭配单行操作符使用：> < = <> >= <= 非法使用子查询的情况： a、子查询的结果为一组值 b、子查询的结果为空 ② 多行子查询结果集有多行一般搭配多行操作符使用：any、all、in、not in in：属于子查询结果中的任意一个就行 any和all往往可以用其他查询代替进阶8：分页查询应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句语法：select 字段|表达式,…from 表【where 条件】【group by 分组字段】【having 条件】【order by 排序的字段】limit 【起始的条目索引，】条目数;特点：1.起始条目索引从0开始2.limit子句放在查询语句的最后3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage假如:每页显示条目数sizePerPage要显示的页数 page进阶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语言插入语法：insert into 表名(字段名，…)values(值1，…);特点：1、字段类型和值类型一致或兼容，而且一一对应2、可以为空的字段，可以不用插入值，或用null填充3、不可以为空的字段，必须插入值4、字段个数和值的个数必须一致5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致修改修改单表语法：update 表名 set 字段=新值,字段=新值【where 条件】修改多表语法：update 表1 别名1,表2 别名2set 字段=新值，字段=新值where 连接条件and 筛选条件删除方式1：delete语句单表的删除： ★delete from 表名【where 筛选条件】多表的删除：delete 别名1，别名2from 表1 别名1，表2 别名2where 连接条件and 筛选条件;方式2：truncate语句truncate table 表名两种方式的区别【面试题】#1.truncate不能加where条件，而delete可以加where条件#2.truncate的效率高一丢丢#3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始#4.truncate删除不能回滚，delete删除可以回滚DDL语句库和表的管理库的管理：一、创建库create database 库名二、删除库drop database 库名表的管理：#1.创建表CREATE TABLE IF NOT EXISTS stuinfo( stuId INT, stuName VARCHAR(20), gender CHAR, bornDate DATETIME );DESC studentinfo;#2.修改表 alter语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名【字段类型】;#①修改字段名ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;#②修改表名ALTER TABLE stuinfo RENAME [TO] studentinfo;#③修改字段类型和列级约束ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;#④添加字段ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;#⑤删除字段ALTER TABLE studentinfo DROP COLUMN email;#3.删除表DROP TABLE [IF EXISTS] studentinfo;常见类型整型：小数：浮点型定点型字符型：日期型：Blob类型：常见约束NOT NULLDEFAULTUNIQUECHECKPRIMARY KEYFOREIGN KEY数据库事务含义通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态特点（ACID）原子性：要么都执行，要么都回滚一致性：保证数据的状态操作前和操作后保持一致隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改相关步骤：1、开启事务2、编写事务的一组逻辑操作单元（多条sql语句）3、提交事务或回滚事务事务的分类：隐式事务，没有明显的开启和结束事务的标志比如insert、update、delete语句本身就是一个事务显式事务，具有明显的开启和结束事务的标志 1、开启事务取消自动提交事务的功能 2、编写事务的一组逻辑操作单元（多条sql语句） insert update delete 3、提交事务或回滚事务使用到的关键字set autocommit=0;start transaction;commit;rollback;savepoint 断点commit to 断点rollback to 断点事务的隔离级别:事务并发问题如何发生？当多个事务同时操作同一个数据库的相同数据时事务的并发问题有哪些？脏读：一个事务读取到了另外一个事务未提交的数据不可重复读：同一个事务中，多次读取到的数据不一致幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据如何避免事务的并发问题？通过设置事务的隔离级别1、READ UNCOMMITTED2、READ COMMITTED 可以避免脏读3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读4、SERIALIZABLE可以避免脏读、不可重复读和幻读设置隔离级别：set session|global transaction isolation level 隔离级别名;查看隔离级别：select @@tx_isolation;视图含义：理解成一张虚拟的表视图和表的区别：使用方式占用物理空间视图完全相同不占用，仅仅保存的是sql逻辑表完全相同占用视图的好处：1、sql语句提高重用性，效率高2、和表实现了分离，提高了安全性视图的创建语法：CREATE VIEW 视图名AS查询语句;视图的增删改查1、查看视图的数据 ★SELECT * FROM my_v4;SELECT * FROM my_v1 WHERE last_name=‘Partners’;2、插入视图的数据INSERT INTO my_v4(last_name,department_id) VALUES(‘虚竹’,90);3、修改视图的数据UPDATE my_v4 SET last_name =‘梦姑’ WHERE last_name=‘虚竹’;4、删除视图的数据DELETE FROM my_v4;某些视图不能更新包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all常量视图Select中包含子查询joinfrom一个不能更新的视图where子句的子查询引用了from子句中的表视图逻辑的更新#方式一：CREATE OR REPLACE VIEW test_v7ASSELECT last_name FROM employeesWHERE employee_id>100;#方式二:ALTER VIEW test_v7ASSELECT employee_id FROM employees;SELECT * FROM test_v7;视图的删除DROP VIEW test_v1,test_v2,test_v3;视图结构的查看DESC test_v7;SHOW CREATE VIEW test_v7;存储过程含义：一组经过预先编译的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、需要设置新的结束标记delimiter 新的结束标记示例：delimiter $CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型,…)BEGIN sql语句1; sql语句2;END $2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end3、参数前面的符号的意思in:该参数只能作为输入（该参数不能做返回值）out：该参数只能作为输出（该参数只能做返回值）inout：既能做输入又能做输出调用存储过程call 存储过程名(实参列表)函数创建函数学过的函数：LENGTH、SUBSTR、CONCAT等语法：CREATE FUNCTION 函数名(参数名参数类型,…) RETURNS 返回类型BEGIN 函数体END调用函数SELECT 函数名（实参列表）函数和存储过程的区别关键字调用语法返回值应用场景函数 FUNCTION SELECT 函数() 只能是一个一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个存储过程 PROCEDURE CALL 存储过程() 可以有0个或多个一般用于更新流程控制结构系统变量一、全局变量作用域：针对于所有会话（连接）有效，但不能跨重启查看所有全局变量SHOW GLOBAL VARIABLES;查看满足条件的部分系统变量SHOW GLOBAL VARIABLES LIKE ‘%char%’;查看指定的系统变量的值SELECT @@global.autocommit;为某个系统变量赋值SET @@global.autocommit=0;SET GLOBAL autocommit=0;二、会话变量作用域：针对于当前会话（连接）有效查看所有会话变量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’;自定义变量一、用户变量声明并初始化：SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;赋值：方式一：一般用于赋简单的值SET 变量名=值;SET 变量名:=值;SELECT 变量名:=值;方式二：一般用于赋表中的字段值SELECT 字段名或表达式 INTO 变量FROM 表;使用：select @变量名;二、局部变量声明：declare 变量名类型【default 值】;赋值：方式一：一般用于赋简单的值SET 变量名=值;SET 变量名:=值;SELECT 变量名:=值;方式二：一般用于赋表中的字段值SELECT 字段名或表达式 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语句，跳出所在循环！！！