数据库从入门到精通

cmd：

mysql -u rooot -p

net start mysql

net stop mysql



停止MySQL服务 net stop mysql80 启动MySQL服务 net start mysql80 登录 mysql -u root -p mysql -u root -p123456 mysql -h localhost -u root -p mysql --host=localhost --user=root --password=123456
查看版本
 mysql -V;
mysql --version;
mysql> select version();
查看帮助 mysql --help; 退出 mysql> quit; mysql> exit;

创建数据库 create database [if not exists] <database name>; 查看现有数据库 show databases; 指定当前默认数据库 use <database name>; 查看当前数据库 select database();

查看当前数据库所包含的表

show tables；
查看指定数据库所包含的表
    show tables from <database name>;
n查看指定表的结构
    desc <table name>;
n查看现有表的创建语句
    show create table <table name>;
n查看表中的数据
    select * from <table name>;
       创建普通用户
create user '用户名'@'主机地址' identified by '密码';
create user 'frank'@'localhost' identified by '123456';
查看用户权限 show grants; 查询当前登录用户权限 show grants for '用户名'@'主机'; 查询指定用户权限 例： show grants for 'frank'@'localhost';

  给用户授权
grant 权限列表 on 库.表 to 用户名@主机地址;
grant SELECT on db01.* to 'frank'@'localhost';
回收用户权限 REVOKE 权限列表 ON 库.表 FROM 用户名@主机名; REVOKE SELECT ON db01.* FROM 'frank'@'localhost'; 备注：不要忘记 flush privileges

       给角色授权
grant 权限列表 on 库.表 to 角色名;
grant SELECT on db01.* to dev;
回收角色权限 REVOKE 权限列表 ON 库.表 FROM 角色名; REVOKE SELECT ON db01.* FROM dev;

给用户分配角色 grant 角色名 to 用户名@主机地址; grant dev to 'frank'@'localhost'; 回收用户角色 REVOKE 角色名 FROM 用户名@主机地址; REVOKE dev FROM 'frank'@'localhost';

修改密码 ALTER USER '用户名'@'主机地址' IDENTIFIED BY '新密 码'; alter user 'frank'@'localhost' identified by '123'; 删除用户 DROP USER <用户名1> [ , <用户名2> ]… drop user 'frank'@'localhost';

2.数据类型

数字 int double(7,2)

字符 char(10) varchar(10)(可变)

日期 date（yyyy-MM-dd）

datetime （yyyy-MM-dd HH：mm：ss）

timestamp（yyyyMMddHHmmss）

CHAR(n)定长字符串n字符个数，长度范围：0-255 VARCHAR(n)可变字符串n字符个数，长度范围：0-65535 TINYTEXT微型文本串0~28–1字节 TEXT文本串0~216–1字节 LONGBLOB超大文本最大4G

3.创建约束

两种：列级约束、表级约束

create table student(

stuid int primary key auto_increment,

sname varchar(40) not null unique,

birthday date,

sex char(1) check(sex in('男'，'女'))，

cid int refenerces classes(classid)

);

添加非空约束 ALTER TABLE 表名称 MODIFY COLUMN 列名列类型 NOT NULL; 添加唯一约束 ALTER TABLE 表名称 ADD UNIQUE(列名); ALTER TABLE 表名称 MODIFY COLUMN 列名列类型 UNIQUE; ALTER TABLE 表名称 ADD CONSTRAINT 约束名 UNIQUE(列名); 添加主键约束 ALTER TABLE 表名称 ADD PRIMARY KEY(列名); ALTER TABLE 表名称 MODIFY COLUMN 列名列类型 PRIMARY KEY; ALTER TABLE 表名称 ADD CONSTRAINT 约束名 PRIMARY KEY(列名);

添加检查约束 ALTER TABLE 表名称 ADD CHECK(表达式); ALTER TABLE 表名称 ADD CONSTRAINT 约束名 CHECK(表达式); 添加默认值 ALTER TABLE 表名称 MODIFY COLUMN 列名列类型 DEFAULT 默认值; 添加外键约束 ALTER TABLE 表名称 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 主表(列名);

删除not null约束： alter table 表名 modify 列名类型; 删除unique约束： alter table 表名 drop index 唯一约束名; 删除primary key约束： alter table 表名 drop primary key; 删除check约束： alter table 表名 drop check 检查约束名; 删除foreign key约束： alter table 表名 drop foreign key 外键约束名

4.对表的操作

增加新列 ALTER TABLE 表名 ADD 列名列类型; 修改列类型 ALTER TABLE 表名 MODIFY 列名列类型; 修改列名 ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名; 删除列 ALTER TABLE 表名 DROP COLUMN 列名; 更改表名 ALTER TABLE 旧表名 RENAME TO 新表名; 删除表 DROP TABLE 表名;

添加一个字段 Alter table student add classId int(2);
修改字段的长度或类型 Alter table student modify name varchar(30);

修改字段的名字 Alter table student rename column name to sname;

删除一个字段 Alter table student drop column sal;

修改表的名字 Rename table student to stu; Alter table student rename to stu;

删除表 Drop table stu;
插入数据insert

(1)所有字段都插入： INSERT INTO 表名 VALUES(value1，value2，value3...); (2)插入部分字段： INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1，value2，value3,...); 注意：1> 插入空值可以用null，默认值用default。 2> 插入日期类型数据时，按照日期对应格式的字符形式插入 (2)一次性插入多行记录： INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1，value2，value3,...), (value1，value2，value3,...), (value1，value2，value3,...),…;

修改数据update

UPDATE 表名 SET column1=value1,column2=value2,... [WHERE 条件];
删除数据delete

DELETE FROM 表名 [WHERE 条件];

TRUNCATE TABLE 表名 ;

基础查询

*代表所有列

SQL运算符

查询语句

消除重复行

select distinct job from emp;

加在要查询的列之前

别名

select sal*12 年薪 from emp 空格加别名即可改

为别名

select(sal + ifnull(comm,0))*12 年薪 from emp

如果comm提成为0则为0

排序

order by

无标注默认升序

使用order by子句进行排序

升序 asc 降序des

select * from emp order by sal desc;

多列排序

按照第一个类进行排序。如果第一个列内容相同，则按照第二个列排序

每一个列都可以单独指定排序方式

select * from emp order by sal desc，ename;

按照别名排序

select ename 员工姓名，(sal + if null(comm,0))*12 年薪 from order by 年薪 desc

--复制一张表，目标表不存在

create table emp1 as select * from emp;

select *from emp1;

delete form emp1;

--复制一张表，目标表存在

insert into emp1 select * from emp where sal>2000;

查询数据的列要好原表的列匹配

--union

insert into emp1 select * from emp where sal >= 3000 union

select * from emp where job = 'MANAGER';

模糊查询

使用通配符进行模糊查询

— 下划线匹配一个字符

%   匹配任意个字符

-- 查询名字以S开头的员工信息

select * from emp where ename Like'S%';

-- 查询名字第三位为M的员工信息

select * from emp where ename Like'_ _M%';

-- 查询名字中有M的员工信息

select * from emp where ename Like'%M%';

正则匹配

--正则匹配 regexp 或 rlike

-- 查询名字为四个字母的员工

select * from emp where ename regexp '[A-Z]{4}$';

-- 查询名字以M开头的员工信息

select * from emp where ename rlike '^M';

--查询这四个人的信息

select * from emp where ename in ('SMITH','SCOTT','FORD','KING');

between

区间 between A and B

select * from emp where sal between 1100 and 3000;

常见函数

字符函数

lower(str)：将字符串转化为小写的格式。

upper(str)：将字符串转化为大写的格式。

length(str)/char_length(str)：返回字符串的长度。

substr(str,pos,len)：截取字符串的子串。pos 起始位置，从1开始，可以为负数；len 截取长度

substr('hello',-3,3) from dual; 截取后三位

substring(str,pos,len) ，mid(str,pos,len)：同substr

replace(str,oldstr,newstr)：替换字符串

select replace('hexxo worxd', 'x', 'l'); -- hello world

insert(str,pos,len,newstr) ：替换指定位置的字符串

select insert('这是SQL Server数据库',3,10,'MySQL');

concat(str1,str2,…,strn) ：连接字符串

select concat('Hello',' MySQL!'); -- Hello MySQL!

space(n) ：返回n个空格组成的字符串

instr(str,substr)：取子串在字符串第一次出现的位置

select instr('hello world', 'llo'); -- 3

format(x,d)：获取指定小数位的数字，四舍五入

select format(12332.12345,3); -- 12,332.123

trim(str)：获取str去掉左右两边空格的副本

select concat('',trim(' abc '), ''); -- abc

数学函数

--mod(n) 求模

select 11 mod 4;

select mod(11,4);

select 11%4;

--floor(n)

向下取整

--ceil(n)

向上取整

select ceil (-2.567);等于负二

--rand() 返回0-1之间的随机数

--获取20-50之间的随机整数

select floor(30*rand() + 20);

-- 随机乱序

select * from emp order by rand();

pow(x,y)、power(x,y) x的y次幂

round(x[,d]) 保留d为小数，四舍五入取整

d 小数位数，可以省略

select round(3.1415926)

truncate(x,[,d])保留d位小数，舍掉其余部分

select truncate(3.1415926,4);

日期函数

now()、sysdate()、 current_timestamp() ：返回当前系统时间

curdate()、current_date()：返回当前日期（年月日）

curtime()、current_time()：返回当前时间（时分秒）

adddate(date,days|interval expr unit) ：时间运算

select adddate(now(),11); select adddate(now(),interval 2 month );

date_add(date,interval expr unit) ：时间运算

datediff(date1,date2) ：两个日期的差值，只计算日期部分

date(date)/ time(date) ：提取日期/时间部分

week(date) ：返回date为一年中的第几周

day(date)、dayofmonth(date) ：date为一个月中的第几天

dayofweek(date)：返回date为星期几

last_day(date)：返回date所在月份的最后一天

转换函数

在某些情况下，MySQL允许值的数据类型和实际不一样，这时MySQL会隐含的转换数据类型。

如：create table test1(tid int, tname varchar(10) );

insert into test1 values('10', 9); -----会将'10'-->10, 9—>'9'

n为了提高程序的可靠性，我们应该使用转换函数。

date_format(date,format)：将时间转换为指定格式的字符串

例: select date_format(now(),'%Y-%m-%d %H:%i:%s');

str_to_date(str,format)：将字符串转换成datetime类型

例:str_to_date('2020-11-27 13:34:43', '%Y-%m-%d %T')

注意：str要和format的格式保持一致，否则，结果为null

cast(expr as type)：数据类型转换，expr可以为任意数据类型

type的值: binary,char,date,datetime,decimal,json

select cast(123.42 as char);

select cast('2021-05-28 12:13:16' as datetime);

n例1：时间显示出时分秒。

sql>select ename, date_format(hiredate, '%Y-%m-%d %T') from emp;

n例2：显示1981年入职的所有员工

select * from emp where date_format(hiredate,'%Y')=1981;

n例3：显示所有12月份入职的员工

select * from emp where date_format(hiredate,'%m')=12;

条件判断函数

IF(expr,v1,v2)

如果expr为TRUE，则返回v1，否则，返回v2

IFNULL(v1,v2)

如果v1不为NULL，则返回v1，否则返回v2

系统函数

VERSION()：查看MySQL版本号

CONNECTION_ID()：查看当前用户的连接数

USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER() ：查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

CHARSET(str)：查看字符串str使用的字符集

加密函数

MD5(str): MD5加密，返回32位十六进制的数字字符串

SHA(str):为字符串计算出一个数字消息所对应的，长度为40的字符串

(又称消息摘要)

练习实例

-- 找出部门10中所有经理、部门20中所有办事员，-- 既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料select * from emp where (deptno=10 and job='MANAGER')                
or (deptno=20 and job='CLERK')                     
or (job!='MANAGER' and job<>'CLERK' and sal>=2000);
select * from emp where (deptno=10 and job='MANAGER')                     
or (deptno=20 and job='CLERK')                     
or (job not in ('MANAGER','CLERK') and sal>=2000);

高级查询

聚合函数

--max(column) min(column)

select max(sal) 最高薪资,min(sal) 最低薪资 from emp;

COUNT(*) 表中行的数量

COUNT(column) 列不为null的行数

COUNT(distinct column) 指定列中相异值的数量

sum(column) 求和

avg(column) 求平均

round(avg(sal),2)代表求平均薪水，保留两位小数

除了COUNT()以外，聚合函数都会忽略null值

select sum(comm) from emp;

select min(comm) from emp;

分组查询

group by

select cid,round(avg(score),1) 平均成绩 from stuscore group by cid;

按cid分组排序平均成绩3

分组后继续筛选：

having

--having 在分组之后进行条件筛选

select cid,round(avg(score),1) 平均成绩 from stuscore

group by cid having 平均成绩 >70;

select cid,round(avg(score),1) 平均成绩 from stuscore

where score > = 60 group by cid having 平均成绩 >70;

结算：先 where再group by 再 having

select cid,round(avg(score),1){第3步} 平均成绩 from stuscore{1}

where score > = 60 {2}group by cid {4} having 平均成绩 >70{5};

where是筛选原表的数据 group by 分组 having 在分组基础之上进行筛选，

如果没有分组就不会有having

多表联查

内联结(INNER JOIN) 外联结 ——左外联结(LEFT JOIN) ——右外联结(RIGHT JOIN) ——全外联结(LEFT JOIN UNION RIGHT JOIN) 交叉联结(CROSS JOIN)，笛卡尔集

SELECT S.SName,C.CourseID,C.Score From Score C INNER JOIN Students S ON C.StudentID = S.SCode SELECT S.SName, C.CourseID, C.Score FROM Students S,Score C WHERE S.SCode = C.StudentID

多表联查时，如果没有给表起别名，使用表名.列名

如果给表起来别名，必须使用别名.列名，否则会出错

-- 两表联查-- 方法1：select s.sname,s.stuid,sc.* from student s    inner join stuscore sc on s.stuid=sc.stuid;
-- 方法2：select s.sname,sc.* from student s,stuscore sc    where s.stuid=sc.stuid;
-- 三表联查 student、course、stuscore
-- 方法1：select s.sname,c.cname,sc.score from student s    inner join stuscore sc on s.stuid=sc.stuid    inner join course c on sc.cid=c.cid;
-- 方法2：select s.sname,c.cname,sc.score from student s,course c,stuscore sc    where sc.stuid=s.stuid and sc.cid=c.cid;
select * from student;select * from stuscore;
-- 左外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuid;
-- 右外连接select s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;
-- 全外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuidunionselect s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;

子查询

指嵌入在其他sql语句中的其他select语句，也叫嵌套查询

查询薪资比SCOTT低的员工信息

select*from emp where sal<

(select sal from emp where ename = 'SCOTT');

/--采用IN子查询参加考试的学员名单--/ SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks) GO

子查询的结果为一行一列，当作一个数值

子查询的结果为一行多列，用小括号括起来，当作一个整体

子查询的结果为一列多行，使用关键字in

子查询的结果为多行多列，当作一张新表

合并查询

union

合并去重

union all

合并不去重

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union, union all。 union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。例：select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER'; union all 该操作符与union相似，但是它不会取消重复行，而且不会排序。例：select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';

分页查询

start 从0开始

分页查询(limit) 语法：select * from emp limit start_row,row_count; start_row: 起始行，从0开始 row_count: 记录数例： select * from emp limit 6; -- emp表前6行 select * from emp limit 6,10; -- emp表7-16行

-- 两表联查-- 方法1：select s.sname,s.stuid,sc.* from student s    inner join stuscore sc on s.stuid=sc.stuid;-- 方法2：select s.sname,sc.* from student s,stuscore sc    where s.stuid=sc.stuid;-- 三表联查 student、course、stuscore-- 方法1：select s.sname,c.cname,sc.score from student s    inner join stuscore sc on s.stuid=sc.stuid    inner join course c on sc.cid=c.cid;
-- 方法2：select s.sname,c.cname,sc.score from student s,course c,stuscore sc    where sc.stuid=s.stuid and sc.cid=c.cid;
select * from student;select * from stuscore;-- 左外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuid;-- 右外连接select s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;-- 全外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuidunionselect s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;

SQL语句导入、导出数据库

导出数据库语法：select 列名 from 表名 into outfile 文件路径; 例：select * from emp into outfile 'D:/Program Files/mysql- 8.0.25-winx64/uploads/a.sql'; 导入数据库语法：load data infile 文件路径 into table 表名; 例： load data infile 'D:/Program Files/mysql-8.0.25- winx64/uploads/emp.sql' into table emp1; 注意：导入导出路径必须为系统变量secure_file_priv指定的路径，查询该路径使用：show variables like '%secure_file_priv%'。

分析SQL语句执行性能

查询表的详细信息语法：explain 表名; 查看SQL语句的执行计划语法：explain select 语句; id: 选择标识符 select_type: 表示查询的类型。 table: 输出结果集的表 partitions: 匹配的分区 type: 表示表的连接类型 possible_keys: 表示查询时，可能使用的索引 key: 表示实际使用的索引 key_len: 索引字段的长度 ref: 列与索引的比较 rows: 扫描出的行数(估算的行数) filtered: 按表条件过滤的行百分比 extra:执行情况的描述和说明

实例

-- 求平均薪水的等级最低的部门名称
select dname from dept where deptno=
    (select e.deptno from salgrade sg,
    (select deptno,round(avg(sal),2) avgsal from emp
        group by deptno) e
    where e.avgsal between sg.losal and sg.hisal
    order by sg.grade limit 1);
select * from team1;
create table team1 as select min(tid) teamid from team group by tname;
delete from team where tid not in (select teamid from team1);

数据库对象

TABLE	表	表是存储数据的逻辑单元，以行和列的形式存在
DICTIONARY	数据字典	就是系统表，存放数据库相关信息的表
INDEX	索引	用于提高查询性能，相当于书的目录
VIEW	视图	以不同的侧面反映表的数据，是一种逻辑上的表
CONSTRAINT	约束	执行数据校验的规则，用于保证数据完整性的规则
PROCEDURE	存储过程	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
FUNCTION	函数	用于完成一次特定的计算，具有一个返回值
TRIGGER	触发器	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

索引

关键字index，目的是加快查询速度

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度，合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。但是并不是索引越多越好，只是针对大数据表和经常查询的字段进行设置，否则会影响其性能。

1、类似书的目录结构

2、 MySQL 的“索引”对象，用来提高SQL查询语句的速度

3、索引直接指向包含所查询值的行的位置，减少磁盘I/O

4、索引和表是相互独立的物理结构

5、 MySQL 自动使用并维护索引，插入、删除、更新表后，自动更新索引

6、对表进行insert,update,delete处理时，由于要将表的存放位置记录到索引项中而会降低一些速度。

7、MySQL中的索引存储类型有两种：BTREE、HASH，MySQL中的索引大多数为BTREE类型。

创建索引不需要特定的系统权限，建立索引的语法如下：

CREATE [ UNIQUE | FULLTEXT | … ] INDEX 索引名

ON 表名(列名1[，列名2，...]);

其中：

UNIQUE代表创建惟一索引。4

FULLTEXT 代表创建全文索引。

列名是创建索引的关键字列，可以是一列或多列。

索引有很多种，我们主要介绍以下几种：

普通索引

1）普通索引：这是最基本的索引类型，它没有唯一性之类的限制

创建索引：

create index 索引名 on 表名(列名);

修改表时添加索引：

alter table 表名 add index 索引名(列名);

创建表时添加索引：

create table 表名 (…,index 索引名);

唯一索引

2）唯一索引：和普通索引基本相同，有一个区别，索引的列的所有值必须唯一

创建索引：

create unique index 索引名 on 表名(列名);

修改表时添加索引：

alter table 表名 add unique index 索引名(列名);

创建表时添加索引：

create table 表名 (…,unique index 索引名);

主键索引

3）主键索引：是一种特殊的唯一索引，一般在创建表时指定

语法：

create table 表名(…,primary key (列名));

注：

在MySQL中，当建立主键时，主键索引同时也已经建立起来了，不必重复设置。
一张表只能有一个主键，即也只能有一个主键索引。

也可以通过修改表的方式加入主键索引:

语法：alter table 表名 add primary key(列);

全文索引

4）全文索引：全文索引只限于char、varchar和text类型的列上创建

创建索引：

create fulltext index 索引名 on 表名(列名);

修改表时添加索引：

alter table 表名 add fulltext index 索引名(列名);

创建表时添加索引：

create table 表名 (…,fulltext [index] 索引名);

组合索引

5）组合索引：也叫多列索引，就是含有多个列字段的索引

创建索引：

create index 索引名 on 表名(列名1,列名2,…);

修改表时添加索引：

alter table 表名 add index 索引名(列名1,列名2,…);

创建表时添加索引：

create table 表名 (…,index 索引名);

注：多列索引只有在where条件中含有索引中的首列字段时才有效。

使用索引

查看索引：

show index | keys from 表名;

删除和禁用索引：

删除索引：drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

删除主键索引：alter table 表名 drop primary key;

禁用索引：alter table 表名 disable keys;

打开索引： alter table 表名 enable keys;

缺点分析

索引缺点分析

索引有一些先天不足：

1：建立索引，系统要占用硬盘和内存空间来保存索引。

2：更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

在如下字段建立索引应该是不恰当的：

1、很少或从不引用的字段；

2、逻辑型的字段，如男或女(是或否)等。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标

视图

视图是一个虚拟表，其内容由查询定义。

同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自于定义视图的查询所引用的表，并且在引用视图时动态生成。

视图和表的区别

表需要占用磁盘空间，视图不需要；

视图不能添加索引

使用视图可以简化复杂查询

视图有利于提高安全性，比如不同用户查看不同的视图

创建视图

create [or replace] view 视图名 as select 语句

注：创建视图需要有create view的权限，并且对于查询涉及的列有select权限。

修改视图

alter view 视图名 as select 语句

删除视图

drop view [if exists] 视图名

查询视图

select * from 视图名

显示所有视图

show tables

显示视图结构

desc 视图名

显示视图详细信息

show create view 视图名

重命名视图

rename table 视图名 to 新视图名

索引和视图代码

select deptno,round(avg(sal),2) avgsal from emp
    group by deptno having avgsal>2000;
select e.*,d.dname from emp e
    inner join dept d on e.deptno=d.deptno;
select e.*,d.dname from emp e,dept d
    where e.deptno=d.deptno;
delete from emp1;
insert into emp1 select * from emp;
select * from emp1;


-- 表索引
-- 查看表索引
show keys from emp1;
-- 1. 给job添加普通索引
create index job_index on emp1(job);
-- 2. 给ename添加唯一索引
-- 某个列添加了唯一约束，系统会自动创建唯一索引
-- 如果该列没有唯一约束，可以单独添加唯一索引
create unique index ename_index on emp1(ename);
ALTER TABLE emp1 ADD UNIQUE(ename);
-- drop index job_index on emp1;
-- 3. 主键索引
-- 给列添加主键约束(primary key)时，系统会自动创建主键索引，不必重复设置
-- 4. 全文索引(fulltext)，只适用于char、varchar、text类型的列
create fulltext index job_fulltext on emp1(job);
-- 5. 组合索引，一次性给多个列添加索引

-- 添加索引时，如何选择具体哪一个索引
-- 1. 该列是主键，自动会添加主键索引
-- 2. 如果该列值必须唯一，考虑添加唯一索引
-- 3. 如果该列是字符类型，考虑添加全文索引
-- 4. 添加普通索引或组合索引

select count(*) from manydata;
show keys from manydata;
select * from manydata;
create index empno_index on manydata(empno);
select * from manydata where empno=99999; -- 125ms

-- view(视图)
-- 创建视图，各个部门的平均薪资
create or replace view view_avgsal as
    select deptno,round(avg(sal),2) avgsal from emp group by deptno;
-- 可以像普通表一样查询视图
select * from view_avgsal;
-- 修改视图，修改视图对应的sql语句
alter view view_avgsal as select * from emp;
show tables ;
-- 修改视图名
rename table view_avgsal to avgsal;
select * from avgsal;

事务

什么是事务

事务是用于保证数据的一致性，它由一组相关的DML语句组成，该组的DML语句要么全部执行成功，要么都不执行。在 MySQL 中只有使用了innodb数据库引擎的数据库或表才支持事务。

如：网上转账就是典型的要用事务来处理，用以保证数据的一致性。

事务特性

事务有4个特性,通常称为ACID特性：

原子性(atomicity)：一个事务中包含的所有SQL语句是一个不可分割的单元,要么都做，要么都不做。

一致性(consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据也是一致的。

隔离性(isolation)：多个并发的事务可以独立运行，而不能相互干扰。一个事务修改的数据在未提交前,其它事务看不到它所做的更改。

持久性(durability)：事务提交后，数据库的变化就会永久的保留下来。

数据库管理系统采用重执行日志来保证原子性、一致性和持久性。

数据库管理系统采用数据库锁机制来实现隔离性

隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatableRead）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

InnoDB默认是可重复读级别的：

脏读：脏读就是指当一个事务对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读：是指在一个事务内，多次读同一数据。在一个事务多次读取某数据时，同时另外一个事务修改该数据。那么第一个事务两次读数据之间，由于第二个事务的修改，两次读到的数据可能是不一样的，因此称为是不可重复读。

幻读：第一个事务对一个表中的数据进行修改，这种修改涉及到表中的全部行。同时第二个事务向表中插入一行新数据。那么，就会发生操作第一个事务的用户发现表中还有没被修改的数据行，就好象产生了幻觉一样，幻读是数据行记录变多了或者少了。

脏读是指读取了未修改完的记录，不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致，而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题

事务操作

提交事务：commit

使用commit语句可以提交事务，MySQL默认事务是自动提交的

set autocommit = 1(true); 默认自动提交事务

set autocommit = 0(false); 手动提交事务

保存点：

在事务的任何地方都可以设置保存点，可以将修改保存到保存点处。这样可以防止保存点后的误操作。

如：savepoint sp1;

回退事务：

当执行rollback时，通过指定保存点可以回退到指定的点。

（若执行了commit，则无法回退）

如：rollback to sp1; --回退到保存点sp1

rollback 全部回滚

设置事务隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>

其中的<isolation-level>可以是：

– READ UNCOMMITTED

– READ COMMITTED

– REPEATABLE READ

– SERIALIZABLE

例：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别的作用范围

– 全局级(global)：对所有的会话有效

– 会话级(session)：只对当前的会话有效

例：SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

开启事务

start transaction;

查看当前会话隔离级别

select @@**session.transaction_isolation;

查看全局隔离级别

select @@global.transaction_isolation;

查看当前用户的隔离级别

select @@transaction_isolation;

事务和锁

当执行事务操作时，MySQL会在被作用的表上加锁，防止其它用户改表的结构。

锁是防止访问同一资源的事务之间进行相互破坏的机制。

锁用于保护将要被或正在被修改的数据。当某个用户正在修改某个表，则拒绝其他用户修改该表，直到提交或回滚了事务之后，其他用户才可以更新数据。

锁的分类：

MySQL分为三类：全局锁、表级锁和行级锁。

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 flush tables with read lock;

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁。

表锁，表锁的语法： lock tables 表名 read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放
元数据锁（meta data lock，MDL）MDL 不需要显式添加，系统默认会添加。当对一个表做DML操作的时候，加 MDL 读锁；对表做DDL操作的时候，加 MDL 写锁，读锁之间不互斥，读写和写写之间都互斥。

行级锁

MySQL 行锁是在引擎层由各个引擎自己实现的， MyISAM 引擎不支持行锁，InnoDB 支持行锁。在innodb事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放，这个就是两阶段锁协议。

死锁

当两个事务相互等待对方释放资源时，就会形成死锁。

MySQL有两种死锁处理方式：

等待，直到超时（innodb_lock_wait_timeout=50s）。
发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

数据库编程

变量

局部变量

用户变量

全局变量

会话变量

会话变量和全局变量统称为系统变量

局部变量必须先声明再使用

用户变量无需声明

-- 变量
-- 1. 局部变量
-- 定义在存储过程、函数、触发器的begin/end语句块之间
-- declare v_name varchar(15) default '';
-- 2. 用户变量
-- 不需要声明，直接使用
-- ① set @num=1 或 set @num:=1
-- ② select @num:=18 或 select @num:=列 from 表名
-- 注意：select可以直接查询变量的值
-- set可以使用=或:=，但是select必须使用:=赋值
set @num=8;
set @num:=10;
select @num:=100;
select @num:=sal from emp where ename='SCOTT';
select @num;
-- 3. 全局变量
-- 4. 会话变量
show variables like '%autocommit%';
set @@autocommit=0;
set global autocommit = 1;
set @@autocommit = 1;

全局变量和会话变量的区别

对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）

游标

游标使用户可逐行访问SQL返回的结果集

使用游标的主要原因就是它可以把集合操作转换成单个记录处理方式

将表中数据导入到设置的变量中

-- 游标
-- 1. 用游标提取emp表中7788雇员的名称和职务
create procedure findempbyempno()
begin
    -- 编码部分
    -- 定义变量
    declare v_name varchar(15);
    declare v_job varchar(10);
    -- 声明游标
    declare cur_emp cursor for select ename,job
        from emp where empno=7788;
    -- 打开游标
    open cur_emp;
    -- 提取数据
    fetch cur_emp into v_name,v_job;
    select concat('员工姓名：',v_name,',工作：',v_job);
    -- 关闭游标
    close cur_emp;
end;

存储过程

存储过程的创建

create procedure <存储过程名>[(parameter list)]

begin 和end里面只能用局部变量,之外只能用用户变量

语句

赋值语句

SET

SET 变量名 = 表达式;

SET 变量名 := 表达式;

SET 变量名1 = 表达式1,变量名2 = 表达式2,…;
SELECT

SELECT 表达式 INTO 变量 FROM 表名; -- 不产生结果集

SELECT 会话变量 := 表达式; -- 产生结果集

SELECT 会话变量 :=列名 FROM 表名; -- 产生结果集

BEGIN-END语句块

BEGIN

-- 要执行的功能代码

END;

注：将功能封装到存储过程、函数、触发器等存储程序内部。

条件控制语句

IF语句：

IF 条件 THEN 语句块;

ELSE IF 条件 THEN 语句块;

……

ELSE 语句块;

END IF;
CASE语句：

CASE 表达式

WHEN 值1 THEN 语句块;

WHEN 值2 THEN 语句块;

……

ELSE 语句块;

END CASE;

循环语句

WHILE-DO循环：

WHILE 条件表达式 DO -- 继续循环的条件

循环体;

END WHILE;

REPEAT-UNTIL循环：

REPEAT

循环体;

UNTIL 条件表达式 END REPEAT; -- 结束循环的条件

LOOP循环：

循环标签:LOOP

循环体;

IF 条件表达式 THEN LEAVE 循环标签; -- 跳出循环的条件

END IF;

END LOOP;

LEAVE和ITERATE

LEAVE类似于Java中的break，

ITERATE类似于Java中的continue。

使用游标

声明游标

DECLARE cursor_name CURSOR FOR SELECT 语句

打开游标

OPEN cursor_name

提取数据

FETCH cursor_name INTO var1[,var2,…]

关闭游标

CLOSE cursor_name

例1：用游标提取emp表中7788雇员的名称和职务。

create procedure findempinfo()

begin* * declare v_name varchar(15); declare v_job varchar(10); -- 声明游标 * declare cur_emp1 cursor for select ename,job from emp where empno=7788; * open cur_emp1; -- *打开游标 * fetch cur_emp1 into v_name,v_job; -- 提取数据 select v_name,v_job; close cur_emp1; -- 关闭**游标

end;

call findempinfo(); -- 调用存储过程

存储过程类似方法

函数

存储过程和函数区别

1.函数必须有声明返回值类型,存储过程没有

2.函数必须返回一个和声明类型一致的值，存储过程无需返回任何值

3.调用方式不同，函数使用select，存储过程使用call进行调用

4.存储过程的参数有in、out、inout三种模式，函数一般只有in

触发器

触发器是一种特殊的存储过程，当特定对象上的特定时间出现时，将自

动触发执行的代码块，触发器比过程有更精细和更复杂的数据控制能力

应用

-- 触发器 trigger
-- 同一张表，同样的触发时间和触发事件最好只写一个触发器
-- 在student表上建立触发器，在向表student插入记录时触发，
-- 将时间和学生信息存入logs表中。
create trigger tri_student after inserton student for each rowbegin    
-- 自动给logs表添加一条数据    
-- new 表示即将要添加的行    
insert into logs(msg, createtime) values(new.sname,now());end;
-- 触发触发器
insert into student values(6,'二狗子','女');insert into student values(7,'张三','男'),(8,'李四','男'),(9,'王麻子','男');
select * from logs;select * from student;
-- 限制更改的薪资不能小于原来的薪资
create trigger tri_update_sal before updateon emp1 for each rowbegin    
-- new、old    
-- new和old对应唯一的行    
-- 即将要修改的薪资 new.sal    
-- 原来的薪资  用new.empno查询的薪资    
-- declare v_sal decimal(7,2);    
-- select sal into v_sal from emp1 where empno=new.empno;    
-- 判断即将要要求的薪资是否小于原来的薪资    
if new.sal<old.sal then        
-- 发起系统异常        
signal sqlstate '45008' set message_text = '黑心老板！不允许降薪';    end if;end;drop trigger tri_update_sal;
-- 限制每个部门下的员工数量不能超过5位
create trigger tri_deptcount before inserton emp1 for each rowbegin    declare v_count int default 0;    -- 查询即将要添加数据的部门的人数    
select count(*) into v_count from emp1 where deptno=new.deptno;    
-- 如果该部门人数大于等于5，发起系统异常    
if v_count>=5 then        signal sqlstate '45002' set message_text = '该部门已满员，出门右拐！';    end if;end;
create trigger tri_deptcount2 before inserton emp1 for each rowbegin    signal sqlstate '45003' set message_text = '我是新的触发器！';end;

管理触发器

JDBC操作数据库

Java DataBase Connectivity

BeanListHandler是将ResultSet中左右的数据转化成List

List中存放的是对象，里面装的是查询的结果

QueryRunner能进行增删改

qr.update

javabean是数据模型，将数据库中数据导入java中

java数据库连接技术

 public static void main1(String[] args) throws ClassNotFoundException, SQLException {
        // jdbc连接mysql数据库，对表做CRUD(增删改查)
        // 全查emp表
        // 1. 加载驱动类
        // 8.0以下 com.mysql.jdbc.Driver
        // 8.0及以上 com.mysql.cj.jdbc.Driver
         Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取mysql数据库的连接   ！！！orcl为数据库名，看情况修改为要查表所在的数据库
        String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        // 3. 准备sql语句
        String sql = "select * from emp where sal>2000";
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4. 执行sql语句
        // 查询 executeQuery()  返回ResultSet 结果集
        // 增删改 executeUpdate() 返回int
        ResultSet rs = ps.executeQuery();
        // 5. 遍历rs，取出数据
        while(rs.next()){
            // 每循环一次，取出一行数据
            System.out.println("**************************************");
            System.out.println("员工编号：" + rs.getInt("empno"));
            System.out.println("员工姓名：" + rs.getString("ename"));
            System.out.println("员工工作：" + rs.getString("job"));
            System.out.println("上级编号：" + rs.getInt("mgr"));
            System.out.println("入职日期：" + rs.getDate("hiredate"));
            System.out.println("员工薪资：" + rs.getDouble("sal"));
            System.out.println("员工提成：" + rs.getDouble("comm"));
            System.out.println("部门编号：" + rs.getInt("deptno"));
        }



    }
}

加载驱动类不是必需的

数据模型java bean/pojo

占位符

public static void main2(String[] args) throws SQLException {
    // 修改emp1
    String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    Connection conn = DriverManager.getConnection(url, "root", "123456");
    double sal = 3200;
    int empno = 7369;
    String job = "MANAGER";
    // String sql = "update emp1 set sal="+sal+",job='"+job+"' where empno=" + empno;
    // ? 占位符
    String sql = "update emp1 set sal=?,job=? where empno=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    // 在sql语句执行之前，设置占位符的值
    ps.setDouble(1, 3500);
    ps.setString(2, "MANAGER");
    ps.setInt(3, 7369);
    int updateRows = ps.executeUpdate();
    System.out.println("修改了" + updateRows + "行数据");
}

批处理

public static void main45(String[] args) throws SQLException {
    // 批处理
    // jdbc默认为自动提交
    // 一次性给emp1添加10条数据
    String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    Connection conn = DriverManager.getConnection(url, "root", "123456");
    // 设置提交方式为手动提交
    conn.setAutoCommit(false);
    String sql = "insert into emp1 values(?,?,?,?,?,?,?,?)";
    int[] empnos = {9527,9528,9529,9530,9531,9532,9533,9534,9535,9536};
    String[] names = {"华安","华文","华武","刘德华","周杰伦","吴京","李晨","胡军","朱亚文","易烊千玺"};
    String[] jobs = {"CLERK","MANAGER","SALESMAN","ANALYST"};
    int[] deptnos = {10,20,30,40};
    PreparedStatement ps = conn.prepareStatement(sql);
    // 循环添加10条数据
    for (int i = 0; i < 10; i++) {
        ps.setObject(1, empnos[i]);
        ps.setObject(2, names[i]);
        ps.setObject(3, jobs[(int)Math.floor(Math.random()*jobs.length)]);
        ps.setObject(4, 7788);
        ps.setObject(5, new Date());
        ps.setObject(6, 1000);
        ps.setObject(7, 0);
        ps.setObject(8, deptnos[(int)Math.floor(Math.random()*deptnos.length)]);

        // 添加批处理
        ps.addBatch();
        System.out.println(i);
    }
    // 一次性执行10条语句
    ps.executeBatch();
    // 手动提交
    conn.commit();
}

DBHelper

public class DBHelper {
    // 增删改查
    // emp表的所有查询
    public List<Employee> findEmp(String sql,Object... args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Employee> emps = new ArrayList<>();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
               ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            while(rs.next()){
                Employee emp = new Employee();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getDouble("sal"));
                emp.setComm(rs.getDouble("comm"));
                emp.setDeptno(rs.getInt("deptno"));

                // 将emp对象放进集合
                emps.add(emp);
            }
            return emps;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    // 所有表的增删改
    // update emp1 set sal=?,job=? where empno=?
    public int updateTable(String sql,Object... args){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql);
            // 在执行之前设置占位符的值
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,null);
        }
        return 0;
    }

    public static void main(String[] args) {
        DBHelper helper = new DBHelper();
//        String sql = "select * from emp";
//        System.out.println(helper.findEmp(sql));
        String sql = "update emp1 set sal=?,job=? where empno=?";
        helper.updateTable(sql,4100,"CLERK",7369);
    }



}

DBinfo

public interface DBInfo {

    // 数据库连接信息
    public String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    public String JDBC_URL = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    public String JDBC_USERNAME = "root";
    public String JDBC_PASSWORD = "123456";
}

DBUtil

public class DBUtil {

    // 获取数据库连接
    public static Connection getConn(){
        try {
            Class.forName(DBInfo.JDBC_DRIVER);
            return DriverManager.getConnection(DBInfo.JDBC_URL,DBInfo.JDBC_USERNAME,DBInfo.JDBC_PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // 关流
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if(null!=rs){
                rs.close();
            }
            if(null!=ps){
                ps.close();
            }
            if(null!=conn && !conn.isClosed()){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

javabean

public class Employee {

    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}