mysql学习笔记

来源：互联网发布：python 不支持中文编辑：程序博客网时间：2024/05/05 10:07

1 数据库基础 Day 1 (4课时)

数据数据库数据库管理系统图形用户界面（可视化工具）

人工管理阶段文件管理阶段数据库管理阶段

API地址 www.yq1012.com/mysql/

Oracle mySql SQLServer DB2

1. Oracle SQLServer DB2收费的 mySql免费的开源

2. Oracle mySql oracle公司 SQLServer微软 DB2 IBM

3. Oracle DB2 超大型数据库 SQLServer大型数据库 mySql中小型数据库

4. Oracle SQLServer DB2 安全系数高 mySql安全系数低

系统数据网络开发去IOE图书馆书架索引（目录）

mySql 安装

1. 安装数据库以及数据库管理系统端口号3306 用户名root和密码

2. 将mysql的安装路径C:\Program Files\MySQL\MySQL Server 5.5\bin 设置为环境变量PATH

3. 安装对应的图形化用户界面 Navicat

mySql 使用(mysql要已经启动我的电脑右击管理服务)

1．通过图形化用户界面 Navicat 新增一个链接ip地址本机localhost 127.0.0.1

端口号和安装保持一致3306

用户名密码

2．命令行 Dos

前提设置好环境变量Path mySql.exe所在的bin路径

mysql –uroot –proot -u 用户名 -p密码没有分号

MySQL数据库的备份与还原

1- MySQL数据库的备份，在数据库外：

>mysqldump -uroot -p123 test > D:\tt.sql

导出数据库中的表结构及表中的数据。不包含该数据库。

2- MySQL数据库的还原

还原此种方式备份的数据时，必须在该数据库服务器中创建一个数据库，并指定将该数据备份文件拷贝到该数据库中。

第一种，在数据库外。

>mysql -uroot –p123 test< d:\tt.sql

第二种，进入数据库,use hao后

mysql>source d:\tt.sql；

Set sql_safe_updates=0;

练习创建数据库：

创建数据库EmpSalary,并备份

2 SQL基础

l 2.1 SQL（结构化查询语句）(IBM提出ANSI ISO研发) Day 2 (4课时)

数据定义语言(DDL Data Defined Language)

create table | database |…

alter table | database | …

drop table | database |…

数据操作语言(DML Data Manipution Language) Insert Update Delete

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

数据控制语言(DCL Data Control Language) Grant Revoke Commit deny

Mysql的数据类型

整形 BIT tinyint int（默认为有正负符号的，除非声明为unsigned）

浮点型 float double decimal(7,3) 1234.567 7代表去掉小数点后总的位数3代表小数位

字符型 char(M) 长度固定N<M 仍然占用M个字节浪费存储空间存取速度快

varchar(M) 长度不固定 N<M占用N 个字节节省存储空间存取速度慢

text 文章等类型查询不区分大小写

ENUM 枚举类型单项 Clon ENUM(‘f’,’m’)

SET 枚举类型多项’a,b,c’ 可添加 ‘abc’要加单引号

布尔型 Boolean 0代表假非0代表真

日期型 date（’2017-03-03’） time（’10：10：10’）

Datetime（’2017-03-03 10：10：10’）

year（4位数’2017’ 2位数’17’）

Mysql的帮助提示

help decimal;

show tables;

show create table tt1;

desc table名称；description描述

mysqlshow –u root –p root –?

mysqlshow –u root –p root –-help

Mysql的版本查询

Select version();

mysqlshow –u root –p root –version

mysqlshow –u root –p root -V

mysqlshow –u root –p root –-count test tt

Mysql的字符集设置

Create database test default character set gbk;数据库字符集设置

Create table t1(

Id int character set gbk ,

Name char(10)) default charset= gbk; 表格字符集设置

alter table emp charset=gbk;修改整个表格的字符集

character set 数据读写的字符集要一致就不会出现

windows gbk gb2312 Linux utf-8

第一范式：一个属性拆分成多个独立的属性南京市栖霞区仙林街道

第二范式：一个表可拆分成多个表学号姓名课程学分成绩

第三范式：一个表有唯一的主键学号姓名宿舍编号宿舍区宿舍楼房间号

l 2.2 DDL (定义和管理数据库和各种数据库对象的数据) Day 3 (4课时)

DDL数据库的操作命令

create database 数据库名字；

drop database 数据库名字；

alter database 数据库名字；很少用

use database 使用某一个数据库

show databases;

Create database test default character set gbk;

DDL数据库表格的操作命令

1． create

create table 名字(

列名类型,

列名类型

);

CREATE TABLE `user` (

`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`ssl_cipher` blob NOT NULL,

`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

`authentication_string` text COLLATE utf8_bin,

PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Users and global privileges'；

列属性的约束

1. 自增 auto_increment

2. 主键（复合主键）primary key 默认为非空唯一primary key（stuid,carid）

3. 外键 foreign key

constraint 约束名 foreign key (外键列名)references被参考表名（被参考列）

constraint fk_t1 foreign key(fid) references t1(uid)

alter table tte add constraint ft foreign key(uid) references t3(id)

4. 索引 index

5. 注释 comment

6. Default

7. 是否为空 null not null

8. 唯一 unique

9. 检查check

age int check(age between 18 and 25),

注意：先有主键，后有外键。主外键属性保持一致,主外键名不能重复。

自增和默认不能同时使用

列级约束 null not null

表级约束 primary key froreign key unique check

create table tt4(

id int,

name char(10),

unique(id,name));

mysql>show tables; 查看数据库中所有的表

mysql>desc 表名; describle

mysql>show create table 表名;

2.drop

drop table if exits employees;

删除表：

mysql>drop table 表名;//删除表结构及表中的数据

3.alter

alter table 表名 add 列名类型;//增加一列

mysql>alter table student add address varchar(50);

alter table 表名 modify 列名修改后的类型;//修改一列

mysql>alter table student modify address varchar(60);

alter table 表名 drop 列名;//删除一列

mysql>alter table student drop address;

alter table 表名 change 原列名新列名列类型;//为列重命名

mysql>alter table student change address addr;

alter table 表名 rename to 新表名;//为表重命名

alter table 表名 rename 新表名;//为表重命名

mysql>alter table student rename to stu;

alter table emp charset=gbk;修改整个表格的字符集

注意：在实际使用中，一般表建立之后，不会修改表中结构。

ALTER TABLE `emp`

MODIFY COLUMN `job` char(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL AFTER `ename`,

MODIFY COLUMN `deptno` int(11) NULL AUTO_INCREMENT AFTER `comm`,

DROP PRIMARY KEY,

ADD PRIMARY KEY (`empno`, `deptno`);

4 Show desc

Show tables;

Show create table 名字;

Desc 表格名称;

练习创建表：

l 2.3 DML (用来操作表格的语句) Day 4 (4课时)

MySQL中（关键字），不区分大小写（数据区分大小写）。一般情况下，关键字全部大写。为了方便，学习时使用小写。数据库表中每一行记录是唯一的（解决方法是定义主键）。

1 insert
insert into 表名(列名1,列名2…列名n) values (值1,值2…值n);

注意：

值与列顺序相同，且值必须与对应的列的类型相同。

ex：insert into student(sno, name, age, gender, tel, score, birthday) values(1，’jack’，25，‘男’，‘110’, 80.5，’1990-1-1’);

如果没有全部列出所有列，其他没有对应的值的列，以null插入。
ex: insert into student(name, age,gender) values(‘jack’, 25,‘男’);其他列对应的值为null.

如果表中所有列都要插入数据，可不用在表名后列出所有的列。以后插入数据，必须将列在表后显示出来。

ex: insert into student values(值1，值2…值n);

2 Delete

Delete from 表格名 where condition；数据删除，可恢复，表结构不删除

Truncate 表格名; 数据删除，不可恢复，表结构不删除，属于DDL语言

Drop table 表格名；数据和表结构都删除，并且不可恢复

3 update

update 表名 set 列=值,列=值…; 将某列的值修改成指定值

ex: 将stu表中age列的值修改成100，gender列值修改成女

update stu set age=100, gender=’女’;

update 表名 set列=值,列=值… where condition(条件);

ex:将sal大于3000的工资增加500

update stu set sal=sal+500 where sal>3000;

ex:将年龄大于23的学生的工资增加300

update stu set sal=sal+300 where age > 23;

ex:将学号为1,11,21,31,41学生，年龄增长一岁，工资增长25%

update stu set age=age+1, sal=sal*1.25 where sno in (1, 11, 21, 31, 41);

1- 比较运算符
=、!=/<>、>、>=、<、<=

ex: 删除姓名为jack学生信息

delete from student where name=’jack’;

ex:删除性别不为女的所有学生信息

delete from student where gender != ‘女’;

ex:删除年龄大于25的所有学生信息

delete from student where age > 25;

2- and or

and连接两个条件，表示两个条件必须同时满足，结果才满足。

or 连接两个条件，表示其中任意一个条件成立，结果就成立。

ex:删除性别为男且年龄在25岁以上的所有学生信息

delete from student where gender=’男’ and age > 25;

ex:删除分数在80到90之间所有学生的信息

delete from student where score >= 80 and score <= 90;

ex:删除性别为男或者分数小于60分所有学生的信息

delete from student where gender=’男’ or score < 60;

ex:删除学号为1, 2, 3的学生信息

delete from student where sno = 1 or sno = 2 or sno = 3;

3- between min_val and max_val表示一段范围

ex:删除分数在80到90之间所有学生的信息

delete from student score between 80 and 90;

delete from student where score >= 80 and score <= 90;

4- in 表示离散的值

ex: 删除学号为1，2， 3的学生信息

delete from student where sno = 1 or sno = 2 or sno = 3;

delete from student where sno in (1, 2, 3);

5- null

ex:删除电话为null的学生信息

~~delete from student where tel = null;~~

delete from student where tel is null;

6- not 表示取反

ex:删除分数不在80到90之间所有学生的信息

delete from student where score not between 80 and 90;

ex:删除学号不为1，2， 3的所有学生信息

delete from student where sno not in (1, 2, 3);

ex:删除电话不为null的所有学生信息

delete from student where tel is not null;

练习DML语句：

DEPT

DEPTNO（部门编号）主键

DNAME（部门名称）

LOC（部门地址）

财务部

2楼102

技术部

2楼103

销售部

2楼104

SALGRADE

GRADE（工资等级1-6）enum

LOSAL（起始值）

HISAL（终止值）

2000

3000

3001

4000

4001

6000

6001

9000

9001

15000

15001

20000

EMP

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

00001

王明

技术员

00005

2017-03-03

5000

800

00002

李兰

会计

00010

2017-03-03

3000

500

00003

张皓

会计

00010

2017-03-03

3000

500

00004

秦三

会计

00010

2017-03-04

6000

900

00005

秦思

技术员

00012

2017-03-04

7000

900

00006

林思

销售总监

00009

2017-03-04

10000

900

1．将所有2017-03-02后入职的技术员薪资涨1000，奖金涨1000

update emp set sal=sal+1000,comm=comm+1000 where hiredate>'2017-03-02';

2．返回林思的工资等级

select grade from salgrade where( select sal from emp where ENAME like '林思') between LOSAL and HISAL;

3．将所有在部门（02，03）的员工薪资涨500

update emp set sal=sal+500 where deptno in ('02','03');

4．删除姓名为‘李兰’的员工

delete from emp where ename='李兰';

5．删除入职日期不为‘2017-03-03’的员工

delete from emp where hiredate !='2017-03-03';

6．删除奖金大于500并且薪资在5000到8000的员工

delete from emp where comm>500 and sal between 5000 and 8000;

7．删除部门编号为（02，03，04），并且薪资不高于5000的员工

delete from emp where deptno in( '02,03,04') and sal<5000;

l 2.4 DQL (数据查询语句)

l 2.4.1 DQL (数据查询语句)- 单表查询 Day 5 6（8课时）

查询将数据从表取出，放到一个临时的表中。查询有三种方式（选择，投影，连接）

Select 子句 from子句

where 子句

order by 排序

having 子句

group by 分组

union 连接

1． From 首先确定数据检索的源---表

2． Where 确定限定条件对应的行---行

3． Select 确定需要检索的数据投影到对应的列---列

l 无条件单表查询

1. 查询表中所有列

Select * from emp;

Select ename,eno from emp;

2. 查询表中指定列

Select eno,ename from emp;可以更改列的顺序

3. 算术运算符

Select 子句中支持加减乘除和函数

Eg.查看员工年薪

Select ename,empno,sal*12+comm from emp;

Eg.员工转正后，月薪上调20%，请查询出所有员工转正的月薪；转正日期为入职后的6个月

select ENAME '员工姓名' , SAL '实习工资' , hiredate '入职日期', hiredate+interval 6 month '转正日期', sal*1.2 '转正工资' from emp;

Eg.员工试用期6个月，转正后月薪上调20%，请查询所有员工工作第一年的年薪所得

（不考虑奖金部分，年薪的试用期6个月的月薪+转正后的6个月的月薪）

select ENAME '员工姓名' , SAL '实习工资' , hiredate '入职日期', hiredate+interval 6 month '转正日期', sal*6+sal*1.2*6 '年薪' from emp;

select ename,sal,DATE_ADD(hiredate,INTERVAL 6 MONTH) as newdate from emp;

4. null

任何值与null计算结果都为null

通过ifnull 将null 置为0

select ename,sal*12+ifnull(comm,0) from emp;

5. 别名

Select 列 ‘列名’ from emp;

Select 列 as‘列名’ from emp;

Eg.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，需要考虑奖金部分,要求显示列标题为员工姓名，工资收入，奖金收入，总收入

select ENAME '员工姓名' , sal*6+sal*1.2*6 '工资收入', ifnull(comm, 0) '奖金收入' , sal*6+sal*1.2*6+ifnull(comm, 0) '年薪' from emp;

6. 连接字符串

Oracle 使用||进行字符串连接

Sql server 使用+进行字符串连接

Mysql 使用cancat()函数进行字符串连接

字符串常量被称为原义字符串

select concat(ename,' 的岗位是 ',job) '员工岗位' from emp;

7. 消除重复行

select distinct job from emp;

l 条件单表查询

1． Where

n .关系运算符（< <= > >= <> != =）

可以对数值型字符型日期型直接进行运算

select empno,ename,deptno from emp where ename>'王';

n 特殊运算符（between and in like is null）

between 上限 and下限结果集操作包含上限和下限对应的行

like 模糊查询 %零个或者多个字符 _表示有且仅有一个字符

可以通过escape标示符实现对_ %本身这两个字符的查找

不区分大小写

select empno,ename,deptno from emp where empno between '00002' and '00003';

select * from emp where comm in(500,800);

select empno,ename,deptno from emp where empno in('00002','00003' );

select * from emp where ename like '王%';

select * from emp where hiredate like '_____03%';

select * from emp where ename like '%/_%' escape '/';

n 逻辑运算符（ and or not）

优先级 not and or

分页

Mysql limit

Limit 0,5 0代表起始行 5代表起始行开始连续的行的个数

limit (pagesize-1)*pizenum,pizenum

2． Order by

默认为升序 asc

降序序显式声明为desc

select * from emp order by deptno desc;

select ename,job from emp order by deptno asc;

多列排序

select * from emp order by deptno asc,sal desc;

select ename,job from emp order by 2 asc;

mysql中用命令行复制表结构

1.只复制表结构到新表

1 CREATE TABLE 新表 SELECT * FROM旧表 WHERE 1=2;

或

1 CREATE TABLE 新表 LIKE旧表 ;

注意上面两种方式，前一种方式是不会复制时的主键类型和自增方式是不会复制过去的，而后一种方式是把旧表的所有字段类型都复制到新表。

2.复制表结构及数据到新表

1 CREATE TABLE 新表 SELECT * FROM旧表

3.复制旧表的数据到新表(假设两个表结构一样)

1 INSERT INTO 新表 SELECT * FROM旧表

4.复制旧表的数据到新表(假设两个表结构不一样)

1 INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM旧表

练习单表查询：

emp表中所有20号部门员工工资增加10%。

查找01部门，薪水低于12000的员工编号，姓名，职位

查找没有上级的员工信息

查找奖金高于工资的员工信息

查找两个字的员工信息

查找10号部门或职位为经理的员工信息

查找2007年入职的员工信息

查询30号部门员工信息，按照薪水降序排列

查找所有员工信息，按照职位降序排列，职位相同，按照薪水降序排列

查找姓张的员工信息

查找员工编号不为1005,1009,1013的员工信息

查找入职在2003年1月1号到2010年1月1号入职的员工信息

l 单行函数

1 字符函数

大小写处理

LOWER(str) UPPER(str)

SELECT LOWER(ename) from emp;

SELECT UPPER(ENAME) from emp;

字符处理函数

CONCAT(str1,str2,...) SUBSTR(str,pos,len) LENGTH(str)

SELECT CONCAT(ename,' 的薪水是 ',sal)薪水 FROM emp;

SELECT SUBSTR(ename,1,1) 姓氏 FROM emp

SELECT SUBSTR(ename,2) 名字 FROM emp

SELECT SUBSTR(ename,-1,1) 名字 FROM emp

SELECT ename,LENGTH(ename) 名字存储字节长度 FROM emp

SELECT ename,CHAR_LENGTH(ename) 名字字符长度 FROM emp

字符处理函数

INSTR(str,substr) 填补LPAD(str,len,padstr) RPAD(str,len,padstr) TRIM([remstr FROM] str)

SELECT * from dept

SELECT loc,INSTR(LOC,'楼') FROM dept

SELECT loc,LPAD(loc,10,'#') FROM dept;

SELECT loc,RPAD(loc,2,'#') FROM dept;

SELECT loc,REPLACE(loc,'楼','栋') FROM dept

SELECT TRIM(' 你好 '),TRIM('s' from 'sssdsssddss') FROM DUAL;

e.g查询姓名包含大写或小写字母a的员工姓名

select ename,CHAR_LENGTH(ename) from emp where instr(upper(ename),'A')>0;

2 数值函数

SELECT FLOOR(RAND()*100) FROM DUAL

SELECT sal,CONCAT('$',ROUND(sal,2))from emp

mysql> SELECT ROUND(13.57),ROUND(13.57,1),ROUND(16.57,-1),TRuncate(83.57,-2);

+--------------+----------------+-----------------+--------------------+

+--------------+----------------+-----------------+--------------------+

| 14 | 13.6 | 20 | 0 |

+--------------+----------------+-----------------+--------------------+

1 row in set

3 日期函数

SELECT NOW(),CURDATE(),CURTIME(),UTC_DATE(), UTC_TIME(),UTC_TIMESTAMP()FROM DUAL

时间相加

SELECT DATE_ADD(CURDATE(), INTERVAL 31 DAY)

SELECT ADDDATE(CURDATE(), INTERVAL 31 DAY)

SELECT DATE('2003-12-31 01:02:03')

SELECT DATEDIFF('1997-11-30','1997-12-31')

SELECT SUBDATE(CURDATE(), INTERVAL 3 DAY)

SELECT INTERVAL 1 DAY + '1997-12-31'

日期格式调整

SELECT DATE_FORMAT(CURDATE(),'%Y年%M月%D日')

SELECT DAYOFMONTH(NOW()),DAYOFWEEK(NOW())

取出日期

SELECT DATE(NOW())

2.4.2 DQL (数据查询语句)- 多表查询 Day 7（4课时）

1．笛卡尔积

一个表的每一行和第二张表的每一行发生连接

Select * from emp,dept

Select * from emp,dept,salgrade//多余两个表

Select * from emp e,dept d//别名

2．等值连接

Select * from emp e,dept d where e.deptno=d.deptno

3．非等值连接

Select * from emp e,dept d where e.deptno between ‘01’ and ‘09’

1. 内连接和外连接

内连接将满足条件的多表连接结果显示，不满足条件的不显示

Select ename,job,e.deptno,d.DEPTNO from emp e INNER JOIN dept d where e.deptno=d.deptno

外连接将满足条件的多表连接结果显示，不满足条件的也显示

左外连接右外连接 mysql不支持全外连接

Select ename,job,e.deptno,d.DEPTNO from emp e LEFT OUTER JOIN dept d on e.deptno=d.deptno

Select ename,job,e.deptno,d.DEPTNO from emp e LEFT OUTER JOIN dept d USING(DEPTNO) where e.deptno>'01'

2. 自连接

SELECT * FROM emp d,emp f WHERE d.EMPNO=f.MGR

3. 分组函数

MIN(expr) MAX( ) SUM( ) AVG([DISTINCT] expr) COUNT(expr)

SELECT COUNT(DISTINCT(job)) from emp;

4. 分组查询group by

SELECT * FROM emp GROUP BY deptno;

SELECT DEPTNO,count(JOB) FROM emp GROUP BY DEPTNO;

分组函数不能放在where子句中可以在having子句中

SELECT DEPTNO,MAX(sal)

FROM emp

GROUP BY DEPTNO

HAVING MAX(sal)>2000

分组查询的6个关键词

SELECT FROM WHERE GROUP BY HAVING ORDER BY

5. 6个关键词的执行顺序

FROM WHERE GROUP BY HAVING SELECT ORDER BY

SELECT e.deptno,count(empno)

from emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY e.deptno

SELECT deptno,count(*) from emp GROUP BY deptno HAVING count(*)>2

2.4.3 DQL (数据查询语句)- 子查询 Day 8（4课时）

1.子查询用小括号括起来

SELECT ename FROM emp WHERE (SELECT SAL FROM emp WHERE ENAME='jack')<sal

2.WHERE子句中的子查询,子查询可以包括分组函数

子查询可以在 WHERE HAVING FROM update的set

分组函数可以在 select HAVING GROUP BY

SELECT ename FROM emp WHERE (SELECT SAL FROM emp WHERE ENAME='jack')<sal

SELECT ename,empno,sal FROM emp WHERE sal=(SELECT MIN(SAL) FROM emp )

关系运算和单行函数不能包含返回多行结果的子查询语句

SELECT ename,empno from emp where sal=(SELECT MIN(sal) FROM emp GROUP BY deptno)

3.多行字查询运算符

IN的使用

查询每个部门工资最低的员工姓名，员工编号，员工薪资

SELECT ename,empno,sal from emp where sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)

ANY 的使用只要满足结果中任意一行即可

查询大于所有部门最低工资的员工姓名，员工编号，员工薪资

SELECT MIN(sal) FROM emp GROUP BY deptno

SELECT ename,empno,sal from emp where sal >ANY (SELECT MIN(sal) FROM emp GROUP BY deptno)

ALL 的使用满足结果中所有行

SELECT MIN(sal) FROM emp GROUP BY deptno

SELECT ename,empno,sal from emp where sal >ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)

4.相关子查询

SELECT * from emp,dept WHERE emp.deptno=dept.deptno

SELECT dname,(SELECT count(empno)from emp WHERE emp.deptno=dept.deptno GROUP BY emp.deptno )

FROM dept

2.5 DCL (数据控制语句)- Day 9（4课时）

DCL: 数据控制语言，主要创建用户，管理用户权限

Create G rant revoke drop

Create user 用户名@ip地址 IDENTIFIED BY 密码刚刚创建的用户没有任何权限

G rant 权限 on数据库名.表名 to用户名@ip地址

Revoke 权限 on数据库名.表名 from用户名@ip地址

权限：all代表所有权限 *代表所有数据库或说有表

Drop user用户名@ip地址;

显示权限

show grants for admin@localhost;

CREATE USER 'admin'@'localhost' IDENTIFIED BY '123';

SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('123');

解决MySQL乱码问题

查看编码的命令：

>show variables like ‘char%’

character_set_client 表示客户端发来数据的编码

character_set_connection 将客户端编码转换成服务器端编码

character_set_results 将服务器端查询的结构转换成客户端编码

如何不会有乱码？

1- 客户端，client, connection,results,服务器端编码一致。（客户端编码和服务器端编码一致，设置其他三项编码保持一致）。

>set names 编码;(gbk, utf8)

set names gbk相当于以下三句

>set character_set_client=gbk;

> set character_set_connection=gbk;

>set character_set_results=gbk;

2- character_set_client:与client保持一致；character_set_connection:与数据库保持一致; character_set_results:与客户端保持一致（客户端编码与服务器端编码不一致时）

>set character_set_client=客户端的编码;

> set character_set_connection=服务器端编码;

>set character_set_results=客户端编码;

2.5 TPL(事务处理语言)- - Day 10（4课时）

-- 修改默认引擎为INNODB，以支持事务管理

ALTER TABLE dept ENGINE=INNODB;

select * FROM dept;

-- 去掉自动提交

SET autocommit=0;

-- 开始事务

BEGIN;

DELETE FROM dept where deptno='45';

DELETE FROM dept where deptno='08';

DELETE FROM dept where deptno='06';

-- 事务提交，任何一句不能执行，则整个三句都不能执行

COMMIT;

BEGIN

DELETE FROM dept where deptno='45';

DELETE FROM dept where deptno='08';

-- 事务提交，全部语句都能执行，则整个事务都能执行

COMMIT;

BEGIN

-- 以下2句都能执行，但整个事务回退

DELETE FROM dept where deptno='66';

DELETE FROM dept where deptno='67';

ROLLBACK;

SELECT * FROM dept;

-- 开始事务

BEGIN;

DELETE FROM dept where deptno='01';

SAVEPOINT p1;

DELETE FROM dept where deptno='02';

SELECT * FROM dept;

-- 撤销p1后的sql语句

ROLLBACK to p1;

SELECT * FROM dept;

DELETE FROM dept where deptno='30';

-- 事务提交，任何一句不能执行，则整个三句都不能执行

COMMIT;

3 视图索引存储过程与函数

3.1 视图 DAY11(2课时)

视图是建立在一个或者多个表上面通过子查询建立的投影

视图本身是不包含任何数据的，视图的数据全部来自基表

基表的数据更改则视图数据更改

视图的数据修改其实是在修改基表数据

视图可以进行多视图的连接查询

Create view view名（各列别名）AS查询语句

视图如果有别名，通过别名进行列的操作

Drop view view名

Show tables;

Show create view view名

Select * from information_schema.views

3.2 索引 DAY11(2课时)

主键索引外键索引全文索引普通索引

create index idx_名 ON emp(empno,ename);

alter table emp add index(empno,ename);

create table emp(empno int,ename char(10),index(empno,ename));

查看索引 show index from 表名

删除索引 drop index index名on 表名

全文检索搜索引擎为MyISAM 支持频率在50%以下的单词检索，不区分大小写

CREATE TABLE `tt1` (

`id` int(11) DEFAULT NULL,

`title` text,

`content` text,

FULLTEXT KEY `title` (`title`,`content`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk

select * from tt1 where match(title,content) against ('mysql');

3.3 存储过程与函数 DAY12 (4课时)

存储函数与存储过程的区别：

存储函数不能拥有输出参数；必须添加return;

存储函数可直接调用，且不需使用call语句，而存储过程的调用必须使用call语句；

存储函数中必须包含一条return语句，而这条特殊的SQL语句不允许包含于存储过程。

存储过程

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

-> BEGIN

-> SELECT COUNT(*) INTO param1 FROM t;

-> END

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+------+

| @a |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

函数

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20) charset utf8) RETURN S CHAR(50)

-> RETURN CONCAT('Hello, ',s,'!');

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world'); -- 字符串要加单引号

+----------------+

| hello('world') |

+----------------+

| Hello, world! |

+----------------+

1 row in set (0.00 sec)

示例：建立存储函数：根据给定的cust_id返回客户所在的州名（缩写），若库中无给定的cust_id，则返回“不存在该客户”。

->delimiter //

->create function fn_search(c_id int)

->returns varchar(50) -- 定义返回的数据类型，与函数部分中的数据类型需统一，如函数中的“不存在该客户”为6个字符，如果这里设置为char(5)，则无法输出该结果

->deterministic – 表示对于相同的输入值，返回值也相同

->begin

->declare state char(2); -- 声明一个变量state，作为输出的州变量

->select cust_state from customers where cust_id=c_id into state;

->if state is null then

->return(select ’不存在该客户’); --注意这里return不用加s

->else

->return(select state);

->end if;

->end;

->//

-- 执行存储函数

->select fn_search(10001);

显示

mysql> SHOW procedure status;

mysql> SHOW function status;

mysql> SHOW CREATE FUNCTION test.hello\G

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G

练习：

查找对应姓名雇员的奖金，如果奖金高于500，就显示‘表现优秀‘，否则继续努力’

delimiter //

CREATE PROCEDURE pr5(IN NAME CHAR(20) charset gbk )

BEGIN

SELECT SAL,IF(SAL>50,'表现不错','继续努力')评语,ENAME FROM emp WHERE ENAME=NAME;

END//

delimiter ;

CALL pr5('jack');

delimiter //

CREATE FUNCTION f7(NAME CHAR(20) charset gbk)

RETURNS char(50) charset gbk

BEGIN

DECLARE result char(50) charset gbk;

DECLARE s DOUBLE;

SELECT emp.SAL INTO s FROM emp WHERE ename=name;

IF s>500

THEN SET result=CONCAT(name,' 表现不错 ,奖金',s);

ELSE SET result=CONCAT(name,' 继续努力 ,奖金',s);

END IF;

RETURN result;

END//

delimiter ;

SELECT f7('jack');

3.4 触发器 DAY13(2课时)

触发器：MySQL响应insert、delete、update语句时自动执行的一条MySQL语句，创建触发器时需要给出的4条信息：唯一的触发器名、触发器相关的表、触发器应该响应的活动（insert 、delete、update）、触发器何时执行（处理前或处理后）。

1 insert触发器：当对表插入数据时起作用,只能用after

建立insert触发器

SHOW CREATE TRIGGER tr2;

DROP TRIGGER tr2;

CREATE TRIGGER tr2 AFTER INSERT ON emp FOR EACH ROW

BEGIN

INSERT INTO emp_log VALUES (null,NOW(),'insert');

END

执行insert触发器

INSERT INTO emp VALUES('0012','张三','销售','00002','2015-09-09',9000,700,'03');

2 delete触发器：当对表删除数据时起作用，只能用after 同时返回奖金高于500的人数

建立 delete触发器

CREATE TRIGGER tr3 AFTER DELETE ON emp FOR EACH ROW

BEGIN

INSERT INTO emp_log VALUES (null,NOW(),'delete');

call pro();

END

执行delete触发器

delete from emp where ename=’jack’;

3 update触发器：当对表修改数据时起作用，同时含有new和old两个虚拟表。结合New可访问更新行的记录；结合old可访问更新前行的记录，可用after，也可用before

练习：建立一个update触发器，如果更新后的comm大于2000，则2000作为comm

create trigger tr_u BEFORE update on emp for each row

begin

if new.comm>2000 then set new.comm=2000; end if;

insert into emp_log values(now(),'update');

end

UPDATE emp set comm=comm*2 WHERE comm>500;

SELECT * from emp;

4 删除触发器：drop trigger trg_name;

5 查看触发器：show triggers;

3.5 事件 DAY13(2课时)

1 事件简介

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

2 事件的优缺点

优点

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

缺点

定时触发，不可以调用。

3 创建事件

一条create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action ），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者benin...end语句块，这两种情况允许我们执行多条SQL。不支持DDL语句

一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次。

当一个使用 ON COMPLETION NOT PRESERVE 属性定义的事件最后一次执行后，事件直接就不存在了，不能修改。

.查看：show variables like ’event_scheduler’;

.开启：set global event_scheduler=1;

CREATE EVENT EVENT_NAME ON SCHEDULE schedule

event_body;

其中schedule的语法格式为

AT timestamp [+INTERVAL interval]…|every interval -- 指定事件执行的时间，可以为某时刻点即timestamp，或某时刻点开始的interval时间后，或者为每隔interval时间执行一次

[starts timestamp [+INTERVAL interval]] -- 设置事件开始执行的时间

[ends timestamp [+INTERVAL interval]] -- 设置事件终止执行的时间

-- 建立一个事件，用于每分钟向elog表中插入一条数据“now()、’event’”，该事件从5分钟后开始并于20分钟后结束

->delimiter //

-> CREATE EVENT `NewEvent`

ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 20 MINUTE

ON COMPLETION NOT PRESERVE

ENABLE

insert into elog values(now(),'event');;

->//

（2）修改事件，用于修改时间的状态:alter event event_name {enable|disable};

（3）删除事件:drop event event_name;

（4）查看事件:show events

5分钟后将emp_log 这张表复制到 emp_log_old 并且将emp_log_old备份到D盘

grant file on *.* to root@localhost;在命令行中进行

CREATE EVENT `NewEvent`

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE

ON COMPLETION PRESERVE

ENABLE

Insert into emp_log_old SELECT * FROM emp_log;

select * from emp_log_old into outfile 'd:\2.txt';;

补充：流程控制语句IF, CASE, LOOP, WHILE, ITERATE, LEAVE

1、查询每个学生学号、学生姓名、课程名称、成绩（成绩大于60时的显示及格，小于60时的显示不及格）

Select stu.SID, stu.SName, `subject`.SubName,

select_subject.Grade < 60 AS '不及格',

select_subject.Grade > 60 AS '及格'

from stu,select_subject,`subject`

WHERE stu.SID = select_subject.StuID and select_subject.SubID= `subject`.SubID;

Select stu.SID, stu.SName, `subject`.SubName,if( select_subject.Grade < 60,'不及格','及格')

AS '成绩'

from stu,select_subject,`subject`

WHERE stu.SID = select_subject.StuID and select_subject.SubID= `subject`.SubID;

CASE

Select stu.SID, stu.SName, `subject`.SubName,

case select_subject.Grade < 60

WHEN 1 THEN'不及格'

WHEN 0 THEN '及格'

else '成绩为空'END

AS '成绩'

from stu,select_subject,`subject`

WHERE stu.SID = select_subject.StuID and select_subject.SubID= `subject`.SubID;

ELT

Select stu.SID, stu.SName, `subject`.SubName,

ELT((select_subject.Grade < 60)+1,'及格','不及格') '成绩'

from stu,select_subject,`subject`

WHERE stu.SID = select_subject.StuID and select_subject.SubID= `subject`.SubID

LOOP, ITERATE

CREATE PROCEDURE doiterate(p1 INT)

BEGIN

label1: LOOP

SET p1 = p1 + 1;

IF p1 < 10 THEN ITERATE label1;

ELSE

END IF;

LEAVE label1;

END LOOP label1;

SET @x = p1;

END

REPEAT

mysql> CREATE PROCEDURE dorepeat(p1 INT)

-> BEGIN

-> SET @x = 0;

-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

-> END

-> //

mysql> CALL dorepeat(1000)//

mysql> SELECT @x//

WHILE

CREATE PROCEDURE dowhile()

BEGIN

DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO

...

SET v1 = v1 - 1;

END WHILE;

END

4 JDBC数据库连接

public class JDBCDemo1 {

public static void main(String[] args) {

// String driver = "oracle.jdbc.driver.OracleDriver";

String driver = "com.mysql.jdbc.Driver";

String database = "DEMOJDBC";

String url = "jdbc:mysql://localhost:3306/test";

String userName = "root";

String password = "123456";

Connection con = null;

Statement st = null;

java.sql.PreparedStatement ps=null;

ResultSet rs = null;

String sql = null;

String sql1 = null;

String sql2=null;

String sql3=null;

try {

Class.forName(driver);//加载驱动

//String url = "jdbc:mysql://localhost:3306/mysql?user=root&password=123&useUnicode=true&characterEncoding=UTF8";

//con = DriverManager.getConnection(url);//连接数据库(获取连接)

con = DriverManager.getConnection(url, userName, password);//连接数据库(获取连接)

System.out.println("Database connected successfully.");

st = con.createStatement();//创建状态对象

String name = "1=1' or ' 1=1";

//sql = "SELECT last_name userName FROM employees where last_name = '"+name+"'";//编写sql

sql = "SELECT last_name userName FROM employees where last_name =?";//编写sql

ps=con.prepareStatement(sql);

ps.setString(1, "ccc");

rs=ps.executeQuery();

//rs = st.executeQuery(sql);//在数据库中执行sql 查询

//sql1 = "update employees set last_name='czz' where last_name = '1'";//编写sql

//sql2 = "delete from employees where last_name = 'rrr'";//编写sql

//sql3 = "insert into employees(employee_id,last_name) VALUES (8888,'SAA'); ";//编写sql

//int isok=st.executeUpdate(sql3);//增删改 isok>0成功 <=0失败

/*if(isok>0){

System.out.println("修改成功");

}

else{

System.out.println("修改失败");

}*/

while (rs.next()) {

System.out.println("---------------------------------------------");

System.out.print("EMPNAME:" + rs.getString("userName") + "\t");//数据库返回信息处理

//get Type--数据库列的类型对应

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (st != null) {

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (con != null) {

try {

con.close();//关闭数据库

} catch (SQLException e) {

e.printStackTrace();

}

0 0