MySQL基础学习

本文主要是介绍MySQL基础学习，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一.SQL

1.概念

1、SQL（Structured Query Language）:结构化查询语言

   DML  数据操纵语言	用来操作数据库中所包含的数据	INSERT UPDATE DELETE
   DDL  数据定义语言	用于创建和删除数据库对象等操作	CREATE DROP ALTER
   DQL  数据查询语言	用来对数据库中的数据进行查询	SELECT
   DCL  数据控制语言	用来控制数据库组件的存取许可、存取权限等  GRANT COMMIT ROLLBACK

2、包括算术运算符、赋值运算符、比较运算符、逻辑运算符

​ 算术运算符
​ 一种符号，它是用来进行列间或者变量之间的比较和数学运算

​ 赋值运算符

​ 逻辑运算符

​ 比较运算符

3、系统数据库

information_schema:
主要存储系统中的一些数据库对象信息，如用户表信息、字段信息、权限信息、字符集信息和分区信息等。

performance_schema:
主要存储数据库服务器性能参数

mysql:
主要存储系统的用户权限信息

test:
MySQL数据库管理系统自动创建的测试数据库，任何用户都可以使用

2.DDL

数据库定义语言

 show databases;   //查看数据库 

 create database MySchool; //创建数据库  

 use  myschool;  //选择数据库  

 drop database myschool; //删除数据库 myschool;

1、数据类型

2、创建表和删除表

1、创建表 
 create table 
 注释: mysql 中的注释 
 单行注释: -- 注释内容
 多行注释: /*....*/  

 CREATE TABLE [IF NOT EXISTS]  表名 (
     字段1 数据类型 [字段属性|约束][索引][注释],
      ……
     字段n 数据类型 [字段属性|约束][索引][注释]
 )[表类型][表字符集][注释];

CREATE TABLE `student`(
    `studentNo` INT(4) NOT NULL COMMENT '学号' PRIMARY KEY,
	`loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',  
	`studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
	`sex` CHAR(2) DEFAULT '男' NOT NULL  COMMENT '性别', 
	`gradeId` INT(4)  UNSIGNED COMMENT '年级编号', 
	`phone` VARCHAR(50)  COMMENT '联系电话',
	`address` VARCHAR(255)  DEFAULT '地址不详'COMMENT '地址', 	
    `bornDate` DATETIME  COMMENT '出生时间',
	`email` VARCHAR(50) COMMENT'邮件账号',
	 ` identityCard ` VARCHAR(18)  UNIQUE KEY COMMENT '身份证号'
) COMMENT='学生表';


2、删除表 
	drop table 表名
	
	DROP TABLE [IF  EXISTS] 表名;
	DROP TABLE IF EXISTS `student`;

3、表的约束

 编码		CHARSET='gbk'	set names 'gbk'告诉服务器gbk编码的数据

4、修改表

1、表
	#修改表名 
	ALTER TABLE 旧表名 RENAME [TO]  新表名;

	#添加字段  
	ALTER TABLE 表名 ADD 字段名  数据类型  [属性];

	#修改字段
	ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

	#删除字段
	ALTER TABLE 表名 DROP 字段名;

	#查看表定义
	DESCRIBE student;
	DESC student;
	
	DROP TABLE IF EXISTS `demo01`;
    #创建表
    CREATE TABLE  `demo01`(
       `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `name` VARCHAR(8) NOT NULL
    );
    #修改表名
    ALTER TABLE `demo01` RENAME `demo02`;
    #添加字段
    ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL;
    #修改字段
    ALTER TABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL;
    #删除字段
    ALTER TABLE ` demo02 ` DROP `password`;


2、键

	添加主键
	ALTER TABLE 表名 ADD CONSTRAINT [主键名] PRIMARY KEY 表名（主键字段）;
	ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);
 
	添加外键
	ALTER TABLE 表名 ADD CONSTRAINT [外键名] FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）;
	ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) 
REFERENCES `grade` (`gradeId`);

3.DML

1、存储引擎

	1、类型
		MyISAM、InnoDB 、Memory、CSV等9种

	2、MyISAM与InnoDB类型主要区别(图)
	  适用场合
	  	使用MyISAM:   不需事务，空间小，以查询访问为主
	  	使用InnoDB:   多删除、更新操作，安全性高，事务处理及并发控制

	3、相关命令
		#查看数据库存储引擎 
		SHOW ENGINES;
		#查看当前默认存储引擎
		SHOW VARIABLES LIKE 'storage_engine%';
		#创建表使用MYISAM存储引擎
		CREATE TABLE student(
		)ENGINE=MYISAM;

2、数据表的存储位置

MyISAM类型表文件
*.frm：表结构定义文件
*.MYD：数据文件
*.MYI：索引文件

InnoDB类型表文件
*.frm：表结构定义文件
ibdata1文件

3、添加数据

	1、添加一条数据
	INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

	INSERT INTO student (studentNo,loginPwd,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)
	VALUE (1003,'333333','ccc','男',1,'33333333','北京','1990-10-8','333333@163.com','33333333');

	INSERT INTO student(studentNo,loginPwd,studentName,sex,gradeId,bornDate)
	VALUE (1004,'444444','ddd','男',2,'20000404');
	
	2、添加多条数据
	INSERT INTO 新表（字段名列表）
	VALUES(值列表1),(值列表2),……,(值列表n);

	INSERT INTO SUBJECT(subjectName,classHour,gradeId)
	VALUES ('java',200,1),('html',100,2),('mysql',100,2);

	3、从一个表中取数据，然后创建一个新表，并把数据插入新表（只能执行一次）
	CREATE TABLE studentInfo (SELECT studentNo,loginPwd,studentName,sex FROM student);

	4、从一个表中取数据，然后把数据插入已经存在的表中(可以执行多次，数据冗余)
	INSERT INTO studentinfo (SELECT studentNo,loginPwd,studentName,sex FROM student);

4、修改数据

	UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n 
	[WHERE 条件1 AND 条件2];
	
	1、带条件更新 
	UPDATE student SET address="北京昌平" WHERE address="北京海淀";

	2、更新多个数据 多个条件
	UPDATE student SET address="广州", phone=10010, loginPwd=666 WHERE studentNo=1002 AND studentName='赵敏';

5、删除数据

1、DELETE FROM 表名 [WHERE条件];
	删除数据(全部删除)
	DELETE FROM studentinfo;
	删除数据带条件
	DELETE FROM studentinfo WHERE studentNo=1001;
	
2、TRUNCATE TABLE 表名;
	全部删除
	TRUNCATE TABLE studentinfo;

3、TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快
	delete 一行一行删除 ，每删除一行都会产生日志 ，效率比较低 ,truncate 一次性
    TRUNCATE  全部删除，几乎不产生日志信息,速度快
    delete 和 truncate 都会保留表的结构（字段、索引、约束保持不变），delete不会重置自增列，truncate会重置自增列

4.DQL

1、查询数据

查询产生一个虚拟表看到的是表形式显示的结果，但结果并不真正存储
	每次执行查询只是从数据表中提取数据，并按照表的形式显示出来
	
查询语法：

SELECT    <列名|表达式|函数|常量> 
FROM      <表名> 
[WHERE    <查询条件表达式>] 
[ORDER BY <排序的列名>[ASC或DESC]];

1、查询学生表中所有的数据 * 所有列
	SELECT * FROM student;

2、查询部分列  带条件
	SELECT studentNo,loginPwd,studentName FROM student WHERE studentNo=1001;

3、列起别名 使用 as，as可以省略
	SELECT studentNo AS '学号',loginPwd AS '密码',studentName AS '姓名' FROM student;

4、查询学号 密码 姓名 合并在一起 为一列 使用concat();
	SELECT CONCAT(studentNo,'的密码是',loginPwd,'姓名是',studentName) AS '信息' FROM student;

5、查询 email为 Null的学生   使用 is 不用使用=
	SELECT * FROM student WHERE email IS NULL;

6、查询 定义常量列  添加学校类
	SELECT studentNo,loginPwd,studentName ,'千锋' AS 'school' FROM student;

2、SQL执行顺序

1.手写:

2.机读:

​ 随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
​ 下面是经常出现的查询顺序：

3.总结:

3、常用函数

常用函数——聚合函数

常用函数——字符串函数

常用函数——时间日期函数

常用函数——数学函数

4、子查询

	1、子查询 （如果使用比较运算符 ，子查询的结果只能有一个值，一列）

		SELECT * FROM student WHERE borndate>(SELECT borndate FROM student WHERE studentname='aaa');

		查询参加最近一次Java考试成绩的学生的最高分和最低分

		SELECT MAX(studentResult),MIN(studentResult)  FROM result 
		WHERE examDate=(SELECT MAX(examdate) FROM result 
			WHERE subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'))
		AND subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java');

	2、IN子查询

		常用IN替换等于（＝）的子查询
	    IN后面的子查询可以返回多条记录

		查询java成绩为60的学员信息
		SELECT * FROM student WHERE studentNo 
		IN (SELECT studentNo FROM result WHERE studentResult=60 AND subjectNo=1);

		查询没有参加 2017-8-19号 java考试学生名单
		SELECT * FROM student WHERE studentNo 
		NOT IN(SELECT studentNo FROM result WHERE examDate='2017-8-19' AND subjectNo=1);

5、EXISTS子查询

	SELECT …… FROM 表名 WHERE EXISTS(子查询);

	子查询有返回结果： EXISTS子查询结果为TRUE，外层查询执行
	子查询无返回结果： EXISTS子查询结果为FALSE,外层查询不执行
	
	注意：

	在SELECT语句中，WHERE、GROUP BY、HAVING子句的执行次序如下：
	1、WHERE子句从数据源中去掉不符合其搜索条件的数据；
	2、GROUP BY子句搜集数据行到各个组中；
	3、HAVING子句去掉不符合其组搜索条件的各组数据行。
	4、Order BY    
	5、Limit

6、分组,排序,分页查询

GROUP BY子句

	SELECT …… FROM  <表名>  
	WHERE  ……
	GROUP BY ……
	
	SELECT …… FROM  <表名>
	WHERE ……
	GROUP BY ……
	HAVING……

	where 和having  区别

	WHERE子句
	用来筛选 FROM 子句中指定的操作所产生的行 
	GROUP BY子句
	用来分组 WHERE 子句的输出 
	HAVING子句
	用来从分组的结果中筛选行 

	SELECT subjectNo ,AVG(studentResult) AS '平均分' 
	FROM result 
	GROUP BY subjectNo
	HAVING AVG(studentResult)>=80;

ORDER BY子句

ORDER BY子句实现按一定顺序显示查询结果

把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序
SELECT `studentNo` AS 学生编号,(studentResult*0.9+5 ) AS 综合成绩
FROM `result`
WHERE (`studentResult`*0.9+5) >=60
ORDER BY studentResult DESC;

LIMIT子句

MySQL查询语句中使用LIMIT子句限制结果集
SELECT  <字段名列表>
FROM  <表名或视图>
[WHERE  <查询条件>]
[GROUP BY <分组的字段名>]
[ORDER BY  <排序的列名>[ASC 或 DESC]]
[LIMIT [位置偏移量,]行数];

7、JOIN

1、七种JOIN

​ 什么叫共有，什么叫独有？
​ 共有：满足 a.deptid = b.id 的叫共有
​ A独有: A 表中所有不满足 a.deptid = b.id 连接关系的数据
​ 同时参考 join 图

建表sql:

    CREATE TABLE `t_dept` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `deptName` VARCHAR(30) DEFAULT NULL,
        `address` VARCHAR(40) DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    CREATE TABLE `t_emp` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
         `name` VARCHAR(20) DEFAULT NULL,
         `age` INT(3) DEFAULT NULL,
         `deptId` INT(11) DEFAULT NULL,
         PRIMARY KEY (`id`),
         KEY `fk_dept_id` (`deptId`)
         #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



    INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
    INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
    INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
    INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
    INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
    INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');

    INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

七种join:

 
1 A、B两表共有
 select * from t_emp a inner join t_dept b on a.deptId = b.id;
 
2 A、B两表共有+A的独有
 select * from t_emp a left join t_dept b on a.deptId = b.id;
 
3 A、B两表共有+B的独有
 select * from t_emp a right join t_dept b on a.deptId = b.id;
 
4 A的独有 
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 
 
5 B的独有
 select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;  
 
6 AB全有
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
 这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。
7 A的独有+B的独有
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

2、内连接

	内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行 

	给表起别名
	SELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult` 
		FROM student AS s INNER JOIN result AS r 
		ON s.`studentNo`=r.`studentNo`; 
	
	传统内连接
	SELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult` 
		FROM result AS r ,student AS s 
		WHERE s.`studentNo`=r.`studentNo`;

3、外连接

	主表（左表）student中数据逐条匹配表result中的数据
	1.匹配，返回到结果集
	2.无匹配，NULL值返回到结果集

	左外连接 （以左表为主表）
	SELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult`
	FROM student AS s LEFT  JOIN result  AS r
	ON s.`studentNo`=r.`studentNo`

4、union连接

	union : 合并连接
	
    SELECT * from t_emp a LEFT JOIN t_dept b on a.deptId = b.id
    UNION
    SELECT * from t_emp a RIGHT JOIN t_dept b on a.deptId = b.id;

二.事务

1.概念：

​ 在业务逻辑上的一些列操作要么全部成功，要么全部失败。
​ 例如：在关系型数据库中，事务可以是一个sql语句或者一堆sql或者是整个程序。
​ mysql默认支持事务，自动管理事务。(可以mysql手动处理)

​ 事务能干什么？
​ 保证数据操作是准确的，一致性。

2.事务操作的相关方法

​ dos黑窗口如何操作事务？相关的方法

	开启事务  begin;或者start transaction;
    关闭事务(提交事务) commit;
    回滚事务    rollback;

​ jdbc如何操作事务？相关的方法java代码相关api

	开启事务  Connection.setAutoCommit(false);
	关闭事务(提交事务)  Connection.commit();
    回滚事务   Connection.rollback();

​ 案例说明 以转账为例：

public class TransformServiceImpl implements TransformService {
	TransformDao transformDao = new TransformDaoImpl();
	@Override
	public void transformAccount(int fromId, int toId, double money) {
	/** 分析过程：
	 *    1.先查询fromId和toId的金钱  查询(根据id查询Money)
	 *    2.fromId和toId的金钱进行计算加减    不操作dao层
	 *    3.更新fromId和toId的钱      更新(根据id查询Money)
	 */
		try {
            //开启事务
            ManagerTransactionUtils.startTransaction();
            double   fromMoney = transformDao.queryMoneyById(fromId);
            double   toMoney = transformDao.queryMoneyById(toId);
            //加减运算
            fromMoney = fromMoney-money;
            toMoney =toMoney+money;
            int rowCount1 =  transformDao.updateMoneyById(fromId, fromMoney);
            //模拟异常
            //int i=10/0;
            int rowCount2=  transformDao.updateMoneyById(toId, toMoney);
            if (rowCount1>0&&rowCount2>0) {
                System.out.println("转账成功");
            }else {
                System.out.println("转账失败");
            }
            //关闭事务
            ManagerTransactionUtils.closeTransaction();
        } catch (Exception e) {
            //如果异常直接事物回滚
            ManagerTransactionUtils.rollBackTransaction();
        }
    }
}

ManagerTransactionUtils 工具类:

public class ManagerTransactionUtils {
	//得到Connection,要确保同一个线程使用的Connection是一致的。
	//ThreadLocal跟线程绑定的，只存储与之绑定线程的数据，而且该数据是private static 
	//ThreadLocal是存储线程局部变量
	private static  ThreadLocal<Connection> threadLocal =new ThreadLocal<>();
	public  static Connection getConnection() {
		//1.先从threadlocal查找
		Connection connection = threadLocal.get();
		if (connection==null) {
			//从数据源中获取连接
			 connection =C3P0Utils.getConnection();
			 //用threadlocal存储使用的Connection
			 threadLocal.set(connection);
		}
		return connection;
	}
	//开启事务
	public static void  startTransaction() {
		getConnection().setAutoCommit(false);
	}
	//关闭事务
	public static void  closeTransaction() {
		getConnection().commit();
	}
	//回滚事务
	public static void  rollBackTransaction() {
		getConnection().rollback();
	}
}

3.事务特性(4个)：

	1.原子性（Atomicity）：一个事务就是最小单位，不能再分割了。事务中的操作要么全部成功，要不全部失败。
	2.一致性（Consistency）：事务从一个状态到另一个状态保持一致。  以转账为例。转账前和转账后总金额不变。
	3.隔离性（Isolation）：多个用户并发访问数据库时，每个操作是独立的，每个事务互不影响。
	4.持久性（Durability）：一个事务一旦被提交，对数据库的数据改变就是永久性的。如果再发生变化那就是再开启一个新的事务。

4.事务并发出现问题：

	脏读：表示一个事务读取到了一个未提交事务的数据。
	
	不可重复读：在同一个事务中，多次读取同一数据。多次读取的结果不一样。(关注点的是某一行)
		示例： A事务读数据，B事务对A正在读的数据进行了修改，导致A事务多次读取结果不一样。
		
	幻读(虚读)：在A事务操作的时候，涉及到的是整个表，B事务对这个表进行了插入或者删除,导致A事务受影响。			  (关注点是整个表)
		示例:某个公司有500人，A事务查询公司人数500，还没结束，新入职了一个人，
		     B事务把新入职的人加入到公司人数，A再读人数变了。
			  
	以上三种重点理解，dos命令演示效果
	先设置隔离级别： set  transaction isolation level 隔离级别之一

5.隔离级别： 从低到高

	1.读未提交  read uncommitted;  可能会出现脏读,不可重复读,幻读。
    2.读已提交  read committed;    避免了脏读,但是还可能会出现不可重复读,幻读。
    3.重复读    repeatable read    mysql默认级别 避免了脏读,不可重复读,但是可能会出现幻读。但是mysql某些版本已经解决了幻读。
    4.可串行化  serialiable:       避免了所有事务并发问题。 原因是消耗资源过大。    基本不用
    
	查看mysql的默认级别
  	select @@tx_isolation;
  	先设置隔离级别： set transaction isolation level 隔离级别之一   临时的。

演示:

	1.演示脏读：	  
		     事务1                  				事务2(read uncommitted)                                         事务3(read committed)
		1  begin;
		2  insert插入数据
		3                     			select * from ...(读到了事务1未提交的事务，产生了脏读)       		select * from ...
		4.  commit；
		5.                    			select * from ...  表示读到的是提交的数据。                    		select * from ...
		结论： 如果出现脏读，通过设置隔离级别解决脏读问题。  一般实际情况read uncommitted基本不用。
		
	2.不可重复读：
			事务1 (read committed)                          			事务2	
		1         begin；
		2         select * from user where id =1;
		3                                                      		update user set name="pp" where id =1;             
		4         select * from user where id =1;(不可重复读)
		5         commit;
		结论：  read committed 只是确保读取已经提交的数据。 在同一事务中多次读取结果不一致，不可重复读
		
		重复读：
		             事务1 (repeatable read )                          		事务2 (repeatable read )  	
		1          begin；
		2          select * from user where id =1;
		3                                                   			update user set name="ww" where id =1;             
		4          select * from user where id =1;
		5          commit;
		6	   select * from user where id =1;
		如果出现不可重复读，更改隔离级别为repeatable read 
		
	   3.幻读演示：
		             事务1 (repeatable read )                          		事务2 (repeatable read )  	
		1          begin；
		2          select * from user;
		3							    		insert into user (name,money) values(5,'yy',20);  
		4          select * from user;
		5          insert into user (name,money) values(5,'yy',20);   
		6	   select * from user;
		结论： 发现第二次查询数据和第一次查询的数据一样，但是数据库已经有数据库，再次插入同样的数据直接报错。
		  实际情况基本没有
		  
		 丢失更新(了解)：
		    第一类丢失更新：
			      在没有事务隔离的情况下，两个事务同时更新一条数据，但是第二条数据更新失败了，导致对数据修改的两条失败。
		   第二类丢失更新：不可重复读的特例。  
			      两个事务同时读取一行数据，然后其中一个事务进行了修改，而另一个也进行修改提交了。造成第一次修改无效。

三.变量

1.系统变量

​ 说明：变量由系统定义，不是用户定义，属于服务器层面
​ 注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

​ 使用步骤：
​ 1、查看所有系统变量
​ show global |【session】variables;
​ 2、查看满足条件的部分系统变量
​ show global |【session】 variables like ‘%char%’;
​ 3、查看指定的系统变量的值
​ select @@global |【session】系统变量名;
​ 4、为某个系统变量赋值
​ 方式一：
​ set global |【session】系统变量名=值;
​ 方式二：
​ set @@global |【session】系统变量名=值;

1、全局变量

​ 作用域：针对于所有会话（连接）有效，但不能跨重启

查看所有全局变量
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%';

查看指定的会话变量的值 (session可省略)
SELECT @@autocommit;
SELECT @@session.tx_isolation;

为某个会话变量赋值 (session可省略)
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

2.自定义变量

​ 说明：变量由用户自定义，而不是系统提供的

​ 使用步骤：
​ 1、声明
​ 2、赋值
​ 3、使用（查看、比较、运算等）

1、用户变量

​ 作用域：针对于当前会话（连接）有效，作用域同于会话变量

①声明并初始化：

​ 赋值操作符：=或:=

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

②赋值（更新变量的值）

方式一：一般用于赋简单的值
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;

③使用（查看变量的值）:

select @变量名;

2、局部变量

​ 作用域：仅仅在定义它的begin end块中有效
​ 应用在 begin end中的第一句话

①声明：

​ declare: 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

②赋值（更新变量的值）：

方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;

③使用（查看变量的值）：

SELECT 局部变量名;

3、案例

​ 声明两个变量，求和并打印:

#用户变量
    SET @m=1;
    SET @n=1;
    SET @sum=@m+@n;
    SELECT @sum;

#局部变量
    DECLARE m INT DEFAULT 1;
    DECLARE n INT DEFAULT 1;
    DECLARE SUM INT;
    SET SUM=m+n;
    SELECT SUM;

4、用户变量和局部变量的对比

四.存储过程和函数

​ 存储过程和函数：类似于java中的方法
​ 好处：
​ 1、提高代码的重用性
​ 2、简化操作

1.存储过程

​ 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
​ 1、提高代码的重用性
​ 2、简化操作
​ 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

1、创建语法

    CREATE PROCEDURE 存储过程名(参数列表)
    BEGIN
        存储过程体（一组合法的SQL语句）
    END

​ 注意：

	1、参数列表包含三部分
		参数模式  参数名  参数类型

		举例：
		in stuname varchar(20)

		参数模式：
		in：该参数可以作为输入，也就是该参数需要调用方传入值
		out：该参数可以作为输出，也就是该参数可以作为返回值
		inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

	2、如果存储过程体仅仅只有一句话，begin end可以省略
		存储过程体中的每条sql语句的结尾要求必须加分号。
		存储过程的结尾可以使用 delimiter 重新设置

		语法：
		delimiter 结束标记

		案例：
		delimiter $

2、调用语法

CALL 存储过程名(实参列表);

3、案例演示

1).空参列表
​ 案例：插入到admin表中五条记录

    SELECT * FROM admin;

    DELIMITER $
    CREATE PROCEDURE myp1()
    BEGIN
        INSERT INTO admin(username,`password`) 
        VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
    END $

    #调用
    CALL myp1()$

2).创建带in模式参数的存储过程

​ 案例1：创建存储过程实现 根据女神名，查询对应的男神信息

    CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
    BEGIN
        SELECT bo.*
        FROM boys bo
        RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
        WHERE b.name=beautyName;
    END $

    #调用
    CALL myp2('柳岩')$

​ 案例2 ：创建存储过程实现，用户是否登录成功

    CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
    BEGIN
        DECLARE result INT DEFAULT 0;#声明并初始化

        SELECT COUNT(*) INTO result#赋值
        FROM admin
        WHERE admin.username = username
        AND admin.password = PASSWORD;

        SELECT IF(result>0,'成功','失败');#使用
    END $

    #调用
    CALL myp3('张飞','8888')$

3).创建out 模式参数的存储过程
​ 案例1：根据输入的女神名，返回对应的男神名

    CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
    BEGIN
        SELECT bo.boyname INTO boyname
        FROM boys bo
        RIGHT JOIN
        beauty b ON b.boyfriend_id = bo.id
        WHERE b.name=beautyName ;
    END $

​ 案例2：根据输入的女神名，返回对应的男神名和魅力值

    CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
    BEGIN
        SELECT boys.boyname, boys.usercp INTO boyname, usercp
        FROM boys 
        RIGHT JOIN 
        beauty b ON b.boyfriend_id = boys.id 
        WHERE b.name=beautyName ;
    END $

    #调用
    CALL myp7('小昭',@name,@cp)$
    SELECT @name,@cp$

4).创建带inout模式参数的存储过程

​ 案例1：传入a和b两个值，最终a和b都翻倍并返回

    CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
    BEGIN
    	SET a=a*2;
    	SET b=b*2;
    END $

    #调用
    SET @m=10$
    SET @n=20$
    CALL myp8(@m,@n)$
    SELECT @m,@n$

4、删除存储过程

    语法：drop procedure 存储过程名
    DROP PROCEDURE p1;
    #不能同时删除多个
    DROP PROCEDURE p2,p3;#错误

5、查看存储过程的信息

	SHOW CREATE PROCEDURE  myp2;

6、习题

#一、创建存储过程实现传入用户名和密码，插入到admin表中
DELIMITER $
CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN
	INSERT INTO admin(admin.username,PASSWORD)
	VALUES(username,loginpwd);
END $


#二、创建存储过程实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
	SELECT b.name ,b.phone INTO NAME,phone
	FROM beauty b
	WHERE b.id = id;

END $


#三、创建存储存储过程或函数实现传入两个女神生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $


#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(),@str)$
SELECT @str $


#五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神  格式的字符串
如 传入 ：小昭
返回： 小昭 AND 张无忌

DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20), OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName;
END $

CALL test_pro5('柳岩',@str)$
SELECT @str $


#六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录

DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT, IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex,size;
END $

CALL test_pro6(3,5)$

2.函数

​ 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
​ 1、提高代码的重用性
​ 2、简化操作
​ 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

​ 区别：
​ 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
​ 函数：有且仅有1 个返回，适合做处理数据后返回一个结果

1、创建语法

    CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
    BEGIN
        函数体
    END
    
	注意：
	1.参数列表 包含两部分：
	参数名 参数类型

	2.函数体：肯定会有return语句，如果没有会报错
	如果return语句没有放在函数体的最后也不报错，但不建议

	3.函数体中仅有一句话，则可以省略begin end
	4.使用 delimiter语句设置结束标记

2、调用语法

	SELECT 函数名(参数列表)

3、案例演示

1)、无参有返回

    #案例：返回公司的员工个数
    DELIMITER $
    CREATE FUNCTION myf1() RETURNS INT
    BEGIN
        DECLARE c INT DEFAULT 0; #定义局部变量
        SELECT COUNT(*) INTO c #赋值
        FROM employees;
        RETURN c;
    END $
    SELECT myf1()$

2)、有参有返回

 	#案例1：根据员工名，返回它的工资
    CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        SET @sal=0;#定义用户变量 
        SELECT salary INTO @sal   #赋值
        FROM employees
        WHERE last_name = empName;
        RETURN @sal;
    END $
    SELECT myf2('k_ing') $

    #案例2：根据部门名，返回该部门的平均工资
    CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        DECLARE sal DOUBLE ;
        SELECT AVG(salary) INTO sal
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE d.department_name=deptName;
        RETURN sal;
    END $
    SELECT myf3('IT')$
    
    #案例3:创建函数，实现传入两个float，返回二者之和
    CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
    BEGIN
        DECLARE SUM FLOAT DEFAULT 0;
        SET SUM=num1+num2;
        RETURN SUM;
    END $
    SELECT test_fun1(1,2)$

4、查看函数

	SHOW CREATE FUNCTION myf3;

5、删除函数

	DROP FUNCTION myf3;

五.流程控制结构

​ 顺序、分支、循环

1.分支结构

1、if函数

​ 语法：

	if(条件,值1，值2)

​ 功能：实现双分支
​ 应用在begin end中或外面

2、case结构

​ 语法：

情况1：类似于switch
	case 变量或表达式
	when 值1 then 语句1;
	when 值2 then 语句2;
    ...
    else 语句n;
    end 

情况2：
    case 
    when 条件1 then 语句1;
    when 条件2 then 语句2;
    ...
    else 语句n;
    end 

应用在begin end 中或外面

​ 案例演示:

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

3、if结构

​ 语法：

    if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    ....
    else 语句n;
    end if;
    功能：类似于多重if

    只能应用在begin end 中

​ 案例演示:

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87)$


#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
END $

CALL test_if_pro(2100)$

2.循环结构

​ 分类：while、loop、repeat

​ 循环控制：

​ iterate类似于 continue，继续，结束本次循环，继续下一次
​ leave 类似于 break，跳出，结束当前所在的循环

1、while

​ 语法：

	【标签:】while 循环条件 do
        循环体;
    end while【标签】;

	联想：
		while(循环条件){
			循环体;
		}

​ 案例演示:

    #1.没有添加循环控制语句
    #案例：批量插入，根据次数插入到admin表中多条记录
    
    CREATE PROCEDURE pro_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i<=insertCount DO
            INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
            SET i=i+1;
        END WHILE;
    END $

    CALL pro_while1(100)$


    #2.添加leave语句
    #案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
    
    CREATE PROCEDURE test_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        a:WHILE i<=insertCount DO
            INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
            
            IF i>=20 THEN LEAVE a;
            END IF;
            
            SET i=i+1;
        END WHILE a;
    END $

    CALL test_while1(100)$


    #3.添加iterate语句
    #案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
    
    TRUNCATE TABLE admin$
    DROP PROCEDURE test_while1$
    CREATE PROCEDURE test_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        a:WHILE i<=insertCount DO
            SET i=i+1;
            IF MOD(i,2)!=0 THEN ITERATE a;
            END IF;

            INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        END WHILE a;
    END $

    CALL test_while1(100)$

2、loop

​ 语法：

    【标签:】loop
    	循环体;
    end loop 【标签】;

    可以用来模拟简单的死循环

3、repeat

​ 语法：

    【标签：】repeat
        循环体;
    until 结束循环的条件
    end repeat 【标签】;

4、经典案例

    /*一、已知表stringcontent
    	其中字段：
    	id 自增长
    	content varchar(20)

   	 	向该表插入指定个数的，随机的字符串
    */
    DROP TABLE IF EXISTS stringcontent;
    CREATE TABLE stringcontent(
        id INT PRIMARY KEY AUTO_INCREMENT,
        content VARCHAR(20)

    );
    
    DELIMITER $
    CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
        DECLARE startIndex INT;#代表初始索引
        DECLARE len INT;#代表截取的字符长度
        WHILE i<=insertcount DO
            SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）
            SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
            INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
            SET i=i+1;
        END WHILE;

    END $

    CALL test_randstr_insert(10)$

​

这篇关于MySQL基础学习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！