Mysql精讲（包括起源、安装、使用）可在开发中作开发手册使用

易忘的sql DQL语句

1.查看表结构的两种方式。
执行下面

SHOW CREATE table emp;

以上是查看建表语句，以下是查看表所在的硬盘位置：

show variables like 'datadir';

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CH

EMPNO	int(4)	NO	PRI		
ENAME	varchar(10)	YES			
JOB	varchar(9)	YES			
MGR	int(4)	YES			
HIREDATE	date	YES			
SAL	double(7,2)	YES			
COMM	double(7,2)	YES			
DEPTNO	int(2)	YES			

2.严格区分大小写查询，在查询字段前面加上 binary

不加会出现结果（不区分大小写）----->但是在linux环境是区分大小写的。

3、<>或者 != 、< 、<= 等 适用于三种数据类型（字符型、日期型、数值型）

对于字符型比较特殊，包含第一个值，但是不包含第二个值。

正确写法:

5.


6.

7.使用 or 连接多个查询条件，多个条件中只要满足任意一个就行，想大于 java中 ||

可以使用小括号括起来改变执行顺序。

9.

10.



11.




12.

单行函数：

单行函数是可以嵌套的，嵌套的时候首先执行的是里面的函数，把里面函数的结果当做外面函数的参数。

ISNULL函数的使用：

case when then else end ----经常使用。 比如是 电表类型这个字段 0表示电表，1表示水表，

case ele_type when "0"  then  price * 0.763 when '1' then price * 5 else  price  end 

下面讲多行函数

1.多行函数也是组函数

2.

原因是： 算数运算中，有null的结果肯定是 null .


3.

count(*) 与count(字段)的应用
4.


5.

6.


不能嵌套原因： AVG(SAL) 结果是一条结果。

8.distinct去除重复数据

下面是复杂的查询：

1.分组查询


样例：

第一种情况：按照单个字段分组

第二种情况： 按照多个字段进行分组。

第三种情况：对分组后的数据进行过滤（HAVING的使用）

例子：

多表查询

背景：

笛卡尔乘积：

内连接的等值连接

1> 92标准的等值连接

例子：

2> 99标准的等值连接

inner可以省略

内连接的非等值连接

1> 92标准的非等值连接sql语句

2> 99标准

内连接的自连接

1 > 92标准


2> 99标准

总结：

下面讲外连接

下面三种情况是 99标准 92标准查询结果与之相反
1> 左外连 把左边不一一匹配的数据查询出来了


2> 右外连 把右边不一一匹配的数据查询出来了 。




3> 全外连接

从 N个表中查询相关的数据

92标准：

99标准：


多表查询原理：

子查询

来看看遗留问题：

第一个遗留问题---->大于平均工资 的员工信息：

第二个遗留问题：

SELECT *  FROM   
EMP   E
JOIN (SELECT DEPTNO,MAX(SAL) MAXSAL 
FROM EMP
GROUP BY
DEPTNO ) T
ON  E.SAL=T.MAXSAL AND E.DEPTNO=T.DEPTNO;

分组查询、多表查询、子查询的综合应用

需求如下：

数据库操作语句 DML

insert 操作：

3>

开发小技巧，在数据库移植中使用。

数据库插入时间对象的使用

还可以这样插入： insert into emp values ()()()多个对象。

update 操作：

delete 操作：

数据库定义语句 DDL

例如：

之前我们没有使用过的操作：


总结：


修改表的语句

添加字段：

补充讲解 ： union

mysql不支持全外连接，那么我们可以使用union实现全外连接。
例子：


小结：

limit的使用

约束

可以创建多个唯一约束：

以上有外键约束的时候更新和删除比较麻烦
那么下面开始讲级联删除和级联更新。

3.删除唯一约束

存储引擎

命令行登录：mysql -uroot -proot;
show engines;

事务

spring的事务可以参考这篇博文： spring学习AOP之添加事务

另一个查不到

提交后才把内存中的数据加载到数据库硬盘中。

回顾一下在 Java中并发问题：

线程同步必须满足3个条件：

mysql 通过隔离级别解决读并发的

SET GLOBAL TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED; -- 读未提交
SET GLOBAL TRANSACTION ISOLATION LEVEL  READ COMMITTED;-- 读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;-- 可重复读
SET GLOBAL TRANSACTION ISOLATION LEVEL  SERIALIZABLE;-- 串行化

三、MySQL事务隔离级别

查看 mysql 的事务隔离级别 ： 可重复读

SELECT @@global.tx_ISOLATION;

mysql -uroot -proot

use springdb;

select * from dept;

数据库中原计划是通过隔离级别解决读并发问题的，但是发现四种隔离级别都有问题。

那么解决锁的问题，锁表级锁。

数据库索引

show index from emp;

视图的使用：

语法：
Create view 视图的名称
as
有效的查询语句

创建视图：

查看视图结构：

删除视图：


当进行新增操作时候，
如果你查询视图绑定sql语句的范围了，那么你在视图中查不到新增的数据。在原表中可以查到这个数据。

视图的作用：
可以隐藏数据的来源

存储过程：

CREATE PROCEDURE PROCROBIN(OUT RESULT INT)
BEGIN
   SELECT  COUNT(*)  INTO  RESULT    FROM EMP  ;
END ;

CALL PROCROBIN(@robin);
SELECT @robin;

第二个例子：

 CREATE PROCEDURE PROC8(IN NUM1 INT,IN NUM2 INT,OUT  RES INT)
BEGIN
        SET  RES=NUM1+NUM2;

END;


CALL PROC8(20,30,@ROBIN);

SELECT @ROBIN;

可参考资料：

存储过程的缺点：：数据库移植性太差。

Mysql存储过程控制语句：
（1）变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储过程外再也不能找到这个声明的变量，但是我们可以通过out参数或者将其值指派给会话变量来保存其值。

 CREATE PROCEDURE PROC6()
BEGIN
DECLARE X1 VARCHAR(5)     DEFAULT "OUTER";
		BEGIN
								DECLARE X1 VARCHAR(5) DEFAULT "INNER";
									SELECT X1;-- 结果是“INNER”
		END;
  SELECT X1; -- 结果是“OUTER”

END;


CALL PROC6();

条件语句：
if then else

			 
		DROP  TABLE  IF EXISTS TEMP;

	CREATE TABLE TEMP(
				ID  INT(4)
			);

			CREATE PROCEDURE PROCTEST(IN PARAM  INT)
			BEGIN

								DECLARE VAR INT ;
							SET VAR =PARAM+1;
								 IF VAR=1 THEN 
								INSERT INTO TEMP VALUES(100);
							END IF;

							IF PARAM =0 THEN 
							UPDATE TEMP SET ID=ID+1;

							ELSE 
							UPDATE TEMP SET ID=ID+2;
							END IF;


			END;

			CALL PROCTEST(0);


SELECT * FROM TEMP;

 

3.case语句：

 DELETE FROM TEMP;
CREATE PROCEDURE PROCTESTROBIN(IN  PARAM INT)
BEGIN
   DECLARE VAR  INT;
 SET VAR=PARAM+1;
   CASE VAR WHEN 1  THEN
       INSERT INTO TEMP  VALUES (1);
      WHEN 2 THEN 
     INSERT INTO TEMP  VALUES (2);
    ELSE 
 INSERT INTO TEMP  VALUES (100);
 END CASE;

END ;



CALL PROCTESTROBIN(10);


SELECT * FROM TEMP;

4.循环语句
while … end while:

 DELETE FROM TEMP;
CREATE PROCEDURE PROCTESTROBIN2()
BEGIN
   DECLARE var int DEFAULT 0;
   while  var <10
  do 
INSERT into temp VALUES(var);
 set var=var+1;
 end while;

  
END ;



CALL PROCTESTROBIN2();


SELECT * FROM TEMP;

5. repeat … end repeat ;
它在执行操作后检查结果，而while则是执行前进行检查。

 DELETE FROM TEMP;
CREATE PROCEDURE PROCTESTROBIN21()
BEGIN
   DECLARE VAR INT DEFAULT 0;
 
 REPEAT
INSERT INTO TEMP VALUES(VAR);
 SET VAR=VAR+1;

  UNTIL VAR >=10
 END REPEAT;

  
END ;



CALL PROCTESTROBIN21();


SELECT * FROM TEMP;

6.loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

DELETE FROM TEMP;
CREATE PROCEDURE PROCTESTROBIN221()
BEGIN
       DECLARE VAR INT DEFAULT 0;
							 loop1:  loop
							INSERT INTO TEMP VALUES(VAR);
							 SET VAR=VAR+1;
								if  VAR >=10 THEN   
								leave loop1;
							end if;

							 END loop;
END ;
CALL PROCTESTROBIN221();


SELECT * FROM TEMP;

数据库设计三范式：

再加上 关系表。

解决办法：

总结：

下面是一对一操作：

最后是 PowerDesigner的使用：

DBA命令：

导入导出的数据库命令：

也可以用工具导出：

更多优化：

下载 本博客上传文件------> MySql优化资料.zip