Mysql数据库

基础查询

select 查询列表 from 表名;

USE myemployees; //最好写上

1.查询表中的单个字段

SELECT last_name FROM employees;

2.查询表中的多个字段

SELECT last_name,salary,email FROM employees;

3.查询表中的所有字段

方式一：

SELECT

employee_id,

first_name,

last_name,

phone_number,

last_name,

job_id,

phone_number,

job_id,

salary,

commission_pct,

manager_id,

department_id,

hiredate

FROM

employees ;

方式二：

SELECT * FROM employees;

4.查询常量值

SELECT 100;

SELECT 'john';

5.查询表达式

SELECT 100%98;

6.查询函数

SELECT VERSION();

7.起别名

方式一：使用as

SELECT 100%98 AS 结果;

SELECT last_name AS 姓,first_name AS 名 FROM employees;

方式二：使用空格

SELECT last_name 姓,first_name 名 FROM employees;

案例：查询salary，显示结果为 out put //如果要改的别名中含有关键字就用双引号包裹.

SELECT salary AS "out put" FROM employees;

8.去重关键词 DISTINCT

案例：查询员工表中涉及到的所有的部门编号

SELECT DISTINCT department_id FROM employees;

9.+号的作用

mysql中的+号：

仅仅只有一个功能：运算符

①若加号两边都为数值型，则两边相加

②若有字符型则转换为数值型相加，无法识别字符则视为0相加

③若有null相加则为null

10、连接关键字 CONCAT（）:将括号中的内容连接。可以连接字符串 "xxxx".和null连接为null

SELECT CONCAT('a','b','c') AS 结果;

SELECT

CONCAT(last_name,first_name) AS 姓名

FROM

employees;

11、 ifnull（表 , a）关键字：若表中一行内容位null则返回 a 。

select ifnull(last_name , a) from employees;

_______________________________________________________________________________进阶2：条件查询

语法：

select

查询列表

from

表名

where

筛选条件;

分类：

一、按条件表达式筛选

简单条件运算符：> < = != <> >= <=

二、按逻辑表达式筛选

逻辑运算符：

作用：用于连接条件表达式

&&和and：两个条件都为true，结果为true，反之为false

||或or：只要有一个条件为true，结果为true，反之为false

!或not：如果连接的条件本身为false，结果为true，反之为false

三、模糊查询

between and

in（）

is null is not null

<=>

1.like

特点：

①一般和通配符搭配使用

通配符：

% 任意多个字符,包含0个字符。%向外字符可以为任意字符

_ 任意单个字符。一个_代表此位置可以代表任何字符

案例1：查询员工名中包含字符a的员工信息

select

from

employees

where

last_name like '%a%';

案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资

select

last_name,

salary

FROM

employees

WHERE

last_name LIKE '__n_l%';

案例3：查询员工名中第二个字符为_的员工名

SELECT

last_name

FROM

employees

WHERE

last_name LIKE '_ $_%' ESCAPE '$ ';

2、.between and

①使用between and 可以提高语句的简洁度

②包含临界值

③两个临界值不要调换顺序

案例1：查询员工编号在100到120之间的员工信息

SELECT

FROM

employees

WHERE

employee_id BETWEEN 120 AND 100;//前后顺序不能颠倒，AND之前为大于之后为小于.

3、in

含义：判断某字段的值是否属于in列表中的某一项

特点：

①使用in提高语句简洁度

②in列表的值类型必须一致或兼容

③in列表中不支持通配符

案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

SELECT

last_name,

job_id

FROM

employees

WHERE

job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

4、is null

=或<>不能用于判断null值

is null或is not null 可以判断null值

5、安全等于 <=>

排序查询

语法：

select 查询列表

from 表名

【where 筛选条件】

order by 排序的字段或表达式 + asc 或 desc 默认为升序;

1、asc代表的是升序，可以省略

desc代表的是降序

2、order by子句可以支持单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面，除了limit子句

1、按单个字段排序

SELECT * FROM employees ORDER BY salary DESC;

2、添加筛选条件再排序

案例：查询部门编号>=90的员工信息，并按员工编号降序

SELECT *

FROM employees

WHERE department_id>=90

ORDER BY employee_id DESC;

3、按表达式排序

案例：查询员工信息按年薪降序

SELECT ,salary12*(1+IFNULL(commission_pct,0))

FROM employees

ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;

4、按别名排序

案例：查询员工信息按年薪升序

SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪

FROM employees

ORDER BY 年薪 ASC;

5、按函数排序

案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name

FROM employees

ORDER BY LENGTH(last_name) DESC;

6、按多个字段排序

案例：查询员工信息，要求先按工资降序，再按employee_id升序

SELECT *

FROM employees

ORDER BY salary DESC,employee_id ASC;

常见函数（方法）

1、基础函数

1、字符函数 LENGTH ： LENGTH('john')。

2、拼接函数 CONCAT : CONCAT(last_name,'_',first_name)。

3、大小写转换函数 upper、lower ： UPPER('john'); LOWER('joHn')。

4、截取函数 substr、substring :

SUBSTR('李莫愁爱上了陆展元',7) out_put; //从7号位置开始截取

SUBSTR('李莫愁爱上了陆展元',1,n) out_put;//从1号索引开始截取N个字段（索引1计入）

5、查找字符函数instr：返回子串第一次出现的索引，如果找不到返回0， INSTR('杨不殷','殷');

6、删除两边函数 trim（） :

LENGTH(TRIM(' 张翠山 ')) AS out_put;

TRIM('a' FROM 'aaaaaa张aa翠aaaa') AS out_put;结果为张aa翠。不删除内容体间。

7、填充函数 lpad左填充、rpad右填充 : LPAD( 被填充体 , 填充后字符长度 , '填充内容 ')

8、替换函数 replace: REPLACE('被替换体','被替换的内容', '替换内容' )

2、数学函数

1、四舍五入函数 round : ROUND(-1.55);

2、向上取整函数 ceil : SELECT CEIL(-1.02);

3、向下取整函数 floor : SELECT FLOOR(-9.99);

4、截断函数 truncate : TRUNCATE(1.69999,1); 保留小数点后1位

5、取余函数 : MOD(10,-3);

6、随机函数 : rand() 从0到1之间取随机数[0.0, 1.0) 浮点型

3、日期函数

1、返回当前系统旟+时间 now

SELECT NOW();

2、返回当前系统日期，不包含时间 curdate

SELECT CURDATE();

3、返回当前时间，不包含日期 curtime

SELECT CURTIME();

4、可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;

SELECT YEAR('1998-1-1') 年;

SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月;

5、字符转date日期：str_to_date(字符日期,转化格式) 如 str_to_date('9-13-1999','%m-%d-%Y')

date日期转字符 :date_format(日期 , 转化格式 ) 如 DATE_format("2018/6/6","%Y年%m月%d日")

[图片上传失败...(image-9c695e-1570969347993)]

4、流程控制函数

1、if 函数

SELECT IF(10<5,'大','小');

2、case 函数

格式一：

case 需要判断的字段货表达式

when X then 显示体

when Z then 显示体

else 显示体

end (表示结束)

格式二 :

case

when 需要判断体 then 显示体

else 显示体

end (表示结束)

5、分组函数

分类：

sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

特点：

1、sum、avg一般用于处理数值型

max、min、count可以处理任何类型

2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍，一般使用count(*)用作统计行数

5、count函数的详细介绍

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：

MYISAM存储引擎下，COUNT(*)的效率高

INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

6、和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees;

以最少函数位基准展现表

分组查询

group by + 分组类型(可以是函数、表达式等，一般使用单个类型)

后筛选 having，（前筛选为where）

完成显示后在查询，然后再显示表，在排序关键词 order by 之前

连接查询

内连接

等值连接查询

92版写法

SELECT last_name,department_name

FROM employees,departments

WHERE employees.department_id=departments.department_id;

99版写法

SELECT last_name,department_name

FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id

非等值连接查询

查询所有员工的工资和其工资的级别

92版写法

SELECT salary,grade_level

FROM employees,job_grades

WHERE salary BETWEEN lowest_sal AND highest_sal

99版写法

SELECT salary,grade_level

FROM employees e

INNER JOIN job_grades J ON e.salary BETWEEN lowest_sal AND highest_sal

自连接查询

92版写法

SELECT e.last_name,m.last_name

FROM employees e,employees m

WHERE e.employee_id = m.manager_id

99版写法

SELECT e.last_name,m.last_name

FROM employees e

INNER JOIN employees m ON e.employee_id = m.manager_id

外连接

左连接

SELECT d.*,e.employee_id

FROM departments d

LEFT OUTER JOIN employees e

ON d.department_id = e.department_id

WHERE e.employee_id IS NULL;

右连接

SELECT d.*,e.employee_id

FROM employees e

RIGHT OUTER JOIN departments d

ON d.department_id = e.department_id

WHERE e.employee_id IS NULL;

全连接

SELECT b.,bo.

FROM beauty b

FULL OUTER JOIN boys bo

ON b.boyfriend_id = bo.id;

交叉连接:查询N个表链接的大表

SELECT e., j.

FROM employees e

CROSS JOIN jobs j

子查询

用于where后面

标量子查询

列子量查询

用于select后面

用于显示

用于from后面

引用的表是子查询展现的内容，需要给表加名称

用于exists后面

exists(子查询语句) 返回此查询是否存在,存在返回true（1），不存在返回false（0）

not exists(子查询语句) 与上相反

分页查询

用于查询语句最末尾

select 。。。。order by xxx limit 索引位置(默认从0开始，可省略) , 读取条数

每页起始索引公式 (页数 - 1) * 每页总条数

联合查询

联合查询 union

联合合并：将多条查询语句的结果合并成一个结果

语法：

select 查询语句1

union

select 查询语句2

union

SELECT * FROM employees WHERE email LIKE '%a%'

UNION

SELECT * FROM employees WHERE department_id>90;

将表合并和同列，合并列数需要一样，默认去重（UNION ALL 为保留重复）

表格中数据的增删改

增（插入）

语法：

insert into 表名(列名,...)

values(值1,...),(值1,...); 支持多条插入，支持子查询插入

语法：

insert into 表名

set 列名=值,列名=值,... 不可多条插入，不支持子查询

补充:在自增列中默认增加在列的最末尾,自增列插入是有顺序的。

单条增加语句只能给一个表增加内容，不支持多表

改

update 表名

set 列=新值,列=新值,...

where 筛选条件;

【补充】修改多表的记录

语法：

sql92语法：

update 表1 别名,表2 别名

set 列=值,...

where 连接条件

and 筛选条件;

sql99语法：

update 表1 别名

inner、lef、right join 表2 别名

on 连接条件

set 列=值,...

where 筛选条件;

删

delete from 表名 where 筛选条件

2、多表的删除【补充】

sql92语法：

delete 表1的别名,表2的别名

from 表1 别名,表2 别名

where 连接条件

and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名

from 表1 别名

inner|left|right join 表2 别名 on 连接条件

where 筛选条件;

清空删除：truncate table

语法：清空表中内容，如果在自增表格中增（插入），自增列从1开始

truncate table 表名;

清空删除delete：

语法: 清空表中内容，如果在自增表格中增（插入）,自增列从原表最后一条开始计算如最后一条为N，则从N+1开始

delete from 表名；

————————————————————————————————————————————

库和表的管理

一、库的管理

创建、修改、删除

二、表的管理

创建、修改、删除

一、库的管理

1、库的创建

语法：

create database if not exists(判断是否存在) 库名;

2、库的修改

RENAME DATABASE books TO 新库名;（修改库动作一般不使用）

ALTER DATABASE books CHARACTER SET gbk;(更改库的字符集)

3、库的删除

DROP DATABASE IF EXISTS(判断是否存在) 库名;

二、表的管理

1.表的创建

语法：

create table 表名(

列名列的类型【(长度) 约束】,

...

列名列的类型【(长度) 约束】

)

2.表的修改

语法

①修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

②修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

④删除列

ALTER TABLE book_author DROP COLUMN annual;

⑤修改表名

ALTER TABLE author RENAME TO book_author;

DESC book;

3.表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;

通用的写法：

DROP DATABASE IF EXISTS 旧库名;

CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;

CREATE TABLE 表名();

4.表的复制

INSERT INTO author VALUES

(1,'村上春树','日本'),

(2,'莫言','中国'),

(3,'冯唐','中国'),

(4,'金庸','中国');

1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

2.复制表的结构+数据

CREATE TABLE copy2

SELECT * FROM author;

只复制部分数据

CREATE TABLE copy3

SELECT id,au_name

FROM author

WHERE nation='中国';

仅仅复制某些字段结构

CREATE TABLE copy4

SELECT id,au_name

FROM author

WHERE 0;

————————————————————————————————————————————

常见的数据类型

数值型

整型

小数：

定点数

浮点数

字符型

较短的文本：char、varchar

较长的文本：text、blob（较长的二进制数据）

日期型

分类：

tinyint、smallint、mediumint、int/integer、bigint

1 2 3 4 8

特点：

① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值

③ 如果不设置长度，会有默认的长度

长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

1.如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;

CREATE TABLE tab_int(

t1 INT(7) ,有符号（默认）

t2 INT(7) unsigned 无符号

);

二、小数

分类：

1.浮点型

float(M,D)

double(M,D)

2.定点型

dec(M，D)

decimal(M,D)

特点：

①

M：整数部位+小数部位

D：小数部位

如果超过范围，则插入临界值

②

M和D都可以省略

如果是decimal，则M默认为10，D默认为0

如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

原则：

所选择的类型越简单越好，能保存数值的型越小越好、

三、字符型

较短的文本：

char

varchar

其他：

binary和varbinary用于保存较短的二进制

enum用于保存枚举

set用于保存集合

较长的文本：

text

blob(较大的二进制)

特点：

写法 M的意思特点空间的耗费效率

char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符比较耗费高

varchar varchar(M) 最大的字符数，不可以省略可变长度的字符比较节省低

ENUM类型

CREATE TABLE tab_char(

c1 ENUM('a','b','c')

);

INSERT INTO tab_char VALUES('a');

INSERT INTO tab_char VALUES('b');

INSERT INTO tab_char VALUES('c');

INSERT INTO tab_char VALUES('m');

INSERT INTO tab_char VALUES('A');

四、日期型

分类：

date保存日期

time 只保存时间

year只保存年

datetime保存日期+时间

timestamp保存日期+时间

特点：

字节范围时区等的影响

datetime 8 1000——9999 不受

timestamp 4 1970-2038 受 (比较好用)

常见约束

分类：六大约束

NOT NULL：非空，用于保证该字段的值不能为空

比如姓名、学号等

DEFAULT:默认，用于保证该字段有默认值

比如性别

PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空

比如学号、员工编号等

UNIQUE:唯一键，用于保证该字段的值具有唯一性，可以为空

比如座位号

CHECK:检查约束【mysql中不支持】

比如年龄、性别

foreign key references:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，关联对象必须是一个键（包括外键在内）

添加约束的时机：

1.创建表时

2.修改表时

约束的添加分类：

列级约束：

六大约束语法上都支持，但外键约束没有效果

表级约束：

除了非空、默认，其他的都支持

主键和唯一的大对比：primary key 与 unique

保证唯一性是否允许为空一个表中可以有多少个是否允许组合

主键 √ × 至多有1个 √，但不推荐

唯一 √ √ 可以有多个 √，但不推荐

外键：

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

3、主表的关联列必须是一个key（一般是主键或唯一）

4、插入数据时，先插入主表，再插入从表

删除数据时，先删除从表，再删除主表

CREATE TABLE 表名(

字段名字段类型列级约束 ,

字段名字段类型 ,

表级约束

)

CREATE DATABASE students; 查看表约束类型

一、创建表时添加约束

1.添加列级约束

语法：

直接在字段名和类型后面追加约束类型即可。

DROP TABLE stuinfo;

CREATE TABLE stuinfo(

id INT PRIMARY KEY, #主键

stuName VARCHAR(20) NOT NULL UNIQUE, #非空

gender CHAR(1) CHECK(gender='男' OR gender ='女'), #检查

seat INT UNIQUE, #唯一

age INT DEFAULT 18, #默认约束

majorId INT REFERENCES major(id) #外键

);

查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

2.添加表级约束不支持非空、默认 (not null 、default)

语法：在各个字段的最下面

【constraint 约束名】可省略约束类型(字段名)

DROP TABLE IF EXISTS stuinfo;

CREATE TABLE stuinfo(

id INT,

stuname VARCHAR(20),

gender CHAR(1),

seat INT,

age INT,

majorid INT,

CONSTRAINT pk PRIMARY KEY(id), #主键

CONSTRAINT uq UNIQUE(seat), #唯一键

CONSTRAINT ck CHECK(gender ='男' OR gender = '女'), #检查

CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键

);

二、修改表时添加约束（无法添加检查约束check）

1、添加列级约束

alter table 表名 modify column 字段名字段类型新约束;

2、添加表级约束

alter table 表名 add 【constraint 约束名】(可省略) 约束类型(字段名) 【外键的引用】;

1.添加主键

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

2.添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat)

3.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

4.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

5添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

三、修改表时删除约束

1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

————————————————————————————————————————————

标识列：auto_increment

又称为自增长列

含义：可以不用手动的插入值，系统提供默认的序列值,

特点：

1、只能与键搭配

2、一个表只能有一个自增列

3、标识列的类型只能是数值型

4、标识列可以通过 SET auto_increment_increment=3设置步长;可以通过手动插入值,设置起始值

5、被标识列不能与默认约束（defult）共存

一、创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;

CREATE TABLE tab_identity(

id INT ,

NAME FLOAT UNIQUE AUTO_INCREMENT,

seat INT

);

INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');null即从索引最后开始添加

INSERT INTO tab_identity(NAME) VALUES('lucy');效果同上;说明主键若为标识列则能被省略可以省略

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3; 设置每次自增的值

————————————————————————————————————————————

TCL

Transaction Control Language 事务控制语言

事务：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的特性：

ACID

原子性：一个事务不可再分割，要么都执行要么都不执行

一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态

隔离性：一个事务的执行不受其他事务的干扰

持久性：一个事务一旦提交，则会永久的改变数据库的数据.

事务的创建

步骤1：开启事务

设置隔离级别

set session|global transaction isolation level 设置隔离级别;(默认为第二级别,只受幻读影响,可省略)

set autocommit=0; 关闭自动提交

start transaction; 开启事务(,mysql中必须写,其他数据库中可选)

步骤2：编写事务中的sql语句(select insert update delete)

语句1;

语句2;

...

步骤3：结束事务

commit;提交事务

rollback;回滚事务，返回到上一次的操作相当于撤销本次操作了；对于trancate操作无法回滚

savepoint + 节点名：设置保存点,可以设置在步骤2当中搭配rollback to 节点吗，返回到该节点

SHOW VARIABLES LIKE 'autocommit';查看是否关闭自动提交

事务的隔离级别：

脏读不可重复读幻读

read uncommitted： √ √ √

read committed： × √ √

repeatable read： × × √

serializable × × ×

mysql中默认第三个隔离级别 repeatable read

oracle中默认第二个隔离级别 read committed

查看隔离级别

select @@tx_isolation;

设置隔离级别

set session|global transaction isolation level 隔离级别;

视图

含义：虚拟表，和普通表一样使用

一、创建视图

语法：

create view 视图名

查询语句;

1.查询姓名中包含a字符的员工名、部门名和工种信息

①创建

CREATE VIEW myv1

SELECT last_name,department_name,job_title

FROM employees e

JOIN departments d ON e.department_id = d.department_id

JOIN jobs j ON j.job_id = e.job_id;

②使用

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

二、视图的修改

方式一：

create or replace view 视图名

查询语句;

create or replace view myv3

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;

方式二：

语法：

alter view 视图名

查询语句;

ALTER VIEW myv3

SELECT * FROM employees;

三、删除视图

语法：drop view 视图名,视图名,...;

DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

五、视图的更新

对于视图的delete、updaet、inset操作会影响到原表（通常无法更新）

变量

系统变量：

全局变量

会话变量

自定义变量：

用户变量

局部变量

一、系统变量

说明：变量由系统定义，不是用户定义，属于服务器层面

注意：全局变量需要添加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》全局变量

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

2》会话变量

作用域：针对于当前会话（连接）有效

二、自定义变量

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

使用步骤：

1、声明

2、赋值

3、使用（查看、比较、运算等）

1》用户变量

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

赋值操作符：=或:=

①声明并初始化

SET @变量名=值;

SET @变量名:=值;

SELECT @变量名:=值;

②赋值（更新变量的值）

方式一：

SET @变量名=值;

SET @变量名:=值;

SELECT @变量名:=值;

方式二：

SELECT 字段 INTO @变量名

FROM 表;

③使用（查看变量的值）

SELECT @变量名;

2》局部变量

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

①声明

DECLARE 变量名类型;

DECLARE 变量名类型 DEFAULT 值;

②赋值（更新变量的值）

方式一：

SET 局部变量名=值;

SET 局部变量名:=值;

SELECT 局部变量名:=值;

方式二：

SELECT 字段 INTO 具备变量名

FROM 表;

③使用（查看变量的值）

SELECT 局部变量名;

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

作用域定义位置语法

用户变量当前会话会话的任何地方加@符号，不用指定类型

局部变量定义它的BEGIN END中 BEGIN END的第一句话一般不用加@,需要指定类型

————————————————————————————————————————————

存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

1、提高代码的重用性

2、简化操作

3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

一、创建语法

CREATE PROCEDURE 存储过程名(参数模式参数名参数类型)

BEGIN

存储过程体（一组合法的SQL语句）

END

调用

CALL myp3('张飞','8888')$

二、in、out、inout

in:

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('柳岩')$

out:

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$

inout:

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$

三、删除存储过程

drop procedure 存储过程名

四、查看存储过程的信息

SHOW CREATE PROCEDURE myp2;

函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

1、提高代码的重用性

2、简化操作

3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

函数：有且仅有1 个返回，适合做处理数据后返回一个结果

一、创建语法

CREATE FUNCTION 函数名(参数名参数类型) RETURNS 返回类型

BEGIN

函数体;

return ?;

END

注意：

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

return 值;

3.函数体中仅有一句话，则可以省略begin end

4.使用 delimiter语句设置结束标记

二、调用语法

SELECT 函数名(参数列表)

------------------------------案例演示----------------------------

1.无参有返回

案例：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT

BEGIN

DECLARE c INT DEFAULT 0;#定义局部变量

SELECT COUNT(*) INTO c#赋值

FROM employees;

RETURN c;

END $

调用

SELECT myf1()

三、查看函数

SHOW CREATE FUNCTION myf3;

四、删除函数

DROP FUNCTION myf3;

流程控制语句

一、分支结构

1.if函数

语法：if(条件,值1，值2)

功能：实现双分支

应用在begin end中或外面

2.case结构

语法一：(只能应用在begin end 中)

情况1：

case 变量或表达式

when 值1 then 语句1;

when 值2 then 语句2;

else 语句n;

end case;

情况2：

case

when 条件1 then 语句1;

when 条件2 then 语句2;

else 语句n;

end case;

语法二：(能引用于全部部位的case语法)

情况一:

case 语句

when 值 then 结果

else 结果

end

情况二:

case

when 条件 then 结果

else 结果

end

3.if结构

语法：

if 条件1 then 语句1;

elseif 条件2 then 语句2;

....

else 语句n;

end if;

功能：类似于多重if

只能应用在begin end 中

if案例：创建函数，实现传入成绩，如果成绩>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';

ELSET ch='D';

END IF;

RETURN ch;

END $

SELECT test_if(87)$

case案例：创建函数，实现传入成绩，如果成绩>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)$

二、循环结构

分类：

while、loop、repeat

循环控制：

iterate + 标签：类似于 continue，继续，结束本次循环，继续下一次

leave + 标签：类似于 break，跳出，结束当前所在的循环

1.while

语法：

标签: while 循环条件 do

循环体;

end while 标签;

2.loop

语法：

标签: loop

循环体;

end loop 标签;

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

3.repeat

语法：

标签: repeat

循环体;

until 结束循环的条件

end repeat 标签;

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 $

2.添加leave语句

案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

TRUNCATE TABLE admin$

DROP PROCEDURE test_while1$

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 $

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 $

案例

已知表stringcontent

其中字段：

id 自增长

content varchar(20)

向该表插入指定个数的，随机的字符串

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 startIndex=FLOOR(RAND()*26+1); #代表初始索引，随机范围1-26

SET len=FLOOR(RAND()*(IF(27 - startIndex >= 20,20,27-startIndex))+1); #代表截取长度，随机范围1-（20-startIndex+1）

INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));

SET i=i+1;

END WHILE;

END $

————————————————————————————————————————————

JDBC

概念：Java DataBase Connectivity Java 数据库连接， Java语言操作数据库

JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar，复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下。

2.右键-->Add As Library

2. 注册驱动

3. 获取数据库连接对象 Connection

4. 定义sql

5. 获取执行sql语句的对象 Statement

6. 执行sql，接受返回结果

7. 处理结果

8. 释放资源

代码实现：

导入驱动jar包

2.注册驱动

Class.forName("com.mysql.jdbc.Driver");

3.获取数据库连接对象

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");

4.定义sql语句

String sql = "update account set balance = 500 where id = 1";

5.获取执行sql的对象 Statement

Statement stmt = conn.createStatement();

6.执行sql

情况一: 修改表内容

int count = stmt.executeUpdate(sql);

情况二: 提取表内容

ResultSet rs = stmt.executeQuery(sql);

while (rs.next()){

String lname = rs.getString(3);

int id = rs.getInt(1);

System.out.println(lname + "===" + id);

}

rs.next()：指针向下移，之后返回当前位置是否为空。非空返回true,空返回false。

rs.getXxx(int i 或者String str)：当参数为int型则返回指针所在行 i 列的数据；

当参数为String型则返回指针所在行列名为str的数据。

7.处理结果

System.out.println(count);

8.释放资源（完整写法需要 if （？！=null）和try catch异常）

stmt.close();

conn.close();

注意: 当有其他参数影响第四步定义的SQL语句时需要使用 PreparedStatement 类 PreparedStatement 类为 Statement的子类。

其中方法 setXxx(?索引,值)：用于设置?的值.

4.定义sql语句

String sql = "update account set balance = ? where id = ?";

5.获取执行sql的对象 Statement

Statement stmt = conn.PreparedStatement(sql) ;

stmt.setInt(1,int) ; (设置问号的值)

stmt.setInt(2,int);

6.执行sql

情况一: 修改表内容

int count = stmt.executeUpdate();

情况二: 提取表内容

ResultSet rs = stmt.executeQuery();

JdbcUtil工具类写法

①造一个Properties文件用于储存基本数据

②完成方法

private static String driver;

private static String url;

private static String user;

private static String password;

连接方法

public static Connection conn() throws SQLException {

InputStream ras = null;

try {

Properties pro = new Properties();

Class<JdbcUtil> ju = JdbcUtil.class;

ClassLoader cl = ju.getClassLoader();

ras = cl.getResourceAsStream("JDBC.Properties");

pro.load(ras);

driver = pro.getProperty("driver");

url = pro.getProperty("url");

user = pro.getProperty("user");

password = pro.getProperty("password");

Class.forName(driver);

} catch (IOException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} finally {

if(ras!=null) {

try {

ras.close();

} catch (IOException e) {

e.printStackTrace();

}

return DriverManager.getConnection(url,user,password);

}

关闭数据方法

public static void closeIO(Statement stmt,Connection conn){

if (stmt !=null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn !=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

关闭数据方法

public static void closeIO(ResultSet rs, Statement stmt, Connection conn){

if (stmt !=null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn !=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(rs !=null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

其中 JdbcUtil.conn() 方法代替了以下内容

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");

JdbcUtilIO()方法代替了关闭数据的写法。

——————————————————————————————————

JDBC事务管理

为了防止多条jdbc语句在java运行中出现异常时使用。

connection.setAutoCommit(false); 开启事务

connection.commit();提交事务

connection.rollback();回滚事务

try {

conn = JdbcUtil.conn();

conn.setAutoCommit(false); //开启事务

String sql = "update employees set salary = salary + ? where employee_id ='190' ";

String sql2 = "update employees set salary = salary - ? where employee_id ='200' ";

pstmt = conn.prepareStatement(sql);

pstmt2 = conn.prepareStatement(sql2);

pstmt.setInt(1,500);

pstmt2.setInt(1,500);

pstmt.executeUpdate();

pstmt2.executeUpdate();

conn.commit(); //提交事务

} catch (SQLException e) {

if (conn != null){

try {

conn.rollback(); //回滚事务

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

} finally {

JdbcUtil.closeIO(pstmt,conn);

JdbcUtil.closeIO(pstmt2,null);

}

————————————————————————————————————————————

数据库连接池

概念：

其实就是一个容器(集合)，存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。

C3P0数据库连接池技术

步骤：

1. 导入jar包 (两个) c3p0-0.9.5.2.jar 与 mchange-commons-java-0.2.12.jar ，

不要忘记导入数据库驱动jar包

2. 定义配置文件：

名称： c3p0.properties 或者 c3p0-config.xml

路径：直接将文件放在src目录下即可。

3. 创建核心对象数据库连接池对象 new ComboPooledDataSource

4. 获取连接： getConnection();

代码：

1.创建数据库连接池对象

DataSource ds = new ComboPooledDataSource();

2. 获取连接对象

Connection conn = ds.getConnection();

注: ComboPooledDataSource有两个构造器

默认构造器返回默认配置的DataSource对象

构造器二需要传入String型参数（为制定配置的名字），返回指定配置的DataSource对象。

Druid数据库连接池实现技术，由阿里巴巴提供的

步骤：

1. 导入jar包 druid-1.0.9.jar 与 properties 文件

2. 定义配置文件：

是properties形式的

可以叫任意名称，可以放在任意目录下

3. 加载配置文件。Properties

4. 获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory

5. 获取连接：getConnection

代码：

Properties pro = new Properties();

InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");

pro.load(is);

4.获取连接池对象

DataSource ds = DruidDataSourceFactory.createDataSource(pro);

5.获取连接

Connection conn = ds.getConnection();

注意：以上线程池方法生成的Connectionl链接的Close()方法不再是纯粹的清除缓存，而是将链接归还线程池

定义一个类DRUIDUtils

需要方法

1. 获取连接方法：通过数据库连接池获取连接 getConn()

2. 释放资源 close()

3. 获取连接池的方法 getDataSource()

private static DataSource ds;

static {

try {

Properties pt = new Properties();

pt.load(DRUIDUtils.class.getClassLoader().getResourceAsStream("druid.properties"));

ds = DruidDataSourceFactory.createDataSource(pt);

} catch (Exception e) {

e.printStackTrace();

}

获取连接方法

public static Connection getConn() throws SQLException {

return ds.getConnection();

}

获取连接池的方法

public static DataSource getDataSource(){

return ds;

}

释放资源

public static void close(Statement stmt, Connection conn){

if (stmt !=null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn !=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

public static void close(ResultSet rs, Statement stmt, Connection conn){

if (stmt !=null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(conn !=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

if(rs !=null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

Spring JDBC

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发

步骤：

1. 导入5个 jar 包 JdbcTemplate > lib

2. 创建JdbcTemplate对象。依赖于数据源DataSource

JdbcTemplate template = new JdbcTemplate(ds);

3. 调用JdbcTemplate的方法来完成CRUD的操作

①update():执行DML语句。增、删、改语句

②queryForMap():

查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合

注意：这个方法查询的结果集长度只能是1

③queryForList():查询结果将结果集封装为list集合

注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中

④query(): 查询结果，将结果封装为JavaBean对象形式放入list集合中

query的参数：RowMapper

一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装

new BeanPropertyRowMapper<类型>(类型.class)

⑤queryForObject(sql , 返回类型.class)：查询结果，将结果封装为对象（一般用于聚合函数的查询）

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

Mysql数据库

方式一：

方式二：

4.查询常量值

5.查询表达式

6.查询函数

8.去重关键词 DISTINCT

案例：查询员工表中涉及到的所有的部门编号

9.+号的作用

1.like

案例1：查询员工名中包含字符a的员工信息

案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资

案例3：查询员工名中第二个字符为_的员工名

案例1：查询员工编号在100到120之间的员工信息

案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

1、按单个字段排序

2、添加筛选条件再排序

案例：查询部门编号>=90的员工信息，并按员工编号降序

3、按表达式排序

案例：查询员工信息 按年薪降序

4、按别名排序

案例：查询员工信息 按年薪升序

5、按函数排序

案例：查询员工名，并且按名字的长度降序

6、按多个字段排序

案例：查询员工信息，要求先按工资降序，再按employee_id升序

推荐阅读更多精彩内容

案例：查询员工信息按年薪降序

案例：查询员工信息按年薪升序