mysql 总结

mysql 总结

写在前面

  • 写作不易,如果感觉有用,请打赏作者一杯咖啡~
  • 转载请联系作者,或者注明作者,以及原文链接!

初始数据库

  • 数据库(Database,简称DB)
  • 概念
    • 长期存放在计算机内,有组织,可共享大量数据的集合,是一个数据“仓库”。
  • 作用
    • 保存,管理数据。

数据库管理系统 (Database Management System)

  • 数据管理软件,科学组织和存储数据、高效地获取和维护数据

mysql 简介

  • 概念
    • 是现流行的开源免费的关系型数据库
  • 特点
    • 免费、开源数据库
    • 小巧、功能齐全
    • 使用便捷
    • 可运行于Windows或Linux操作系统
    • 可适用于中小型甚至大型网站应用

结构化查询语句分类

  • DDL(定义和管理数据对象,如数据库,数据表等)
    • 常用命令
      • CREATE
        • create database wxt_db;
      • DROP
        • drop database if exists wxt_db;
      • ALTER
        • 重命名表
          • ALTER TABLE 旧表名 RENAME AS 新表名
          • alter table student rename as students;
        • 添加age字段
          • ALTER TABLE 表名 ADD 字段名 列类型 [ 属性 ]
          • alter table students add age int(10);
        • 修改age字段类型
          • ALTER TABLE 表名 MODIFY 字段名 列类型 [ 属性 ]
          • alter table students modify age int(3);
        • 修改sex字段名
          • ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型 [ 属性 ]
          • alter table students change sex gender char(2);
        • 删除age字段
          • ALTER TABLE 表名 DROP 字段名
          • alter table students drop age;
  • DML (用于操作数据库对象中所包含的数据)
    • 常用命令
      • INSERT
        • INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( ‘值1’, ‘值2’, ‘值3’, …)
        • insert into student values (null, ‘jack’, ‘男’);
        • insert into student (id,name) values (null, ‘rose’);
      • UPDATE
        • UPDATE 表名 SET column_name = value [ , column_name2= value2, …. ][ WHERE condition];
        • update account set cash=cash-500 where name=’A’;
      • DELETE
        • DELETE FROM 表名 [ WHERE condition ];
        • delete from student where id=1;
  • DQL (用于查询数据库数据)
    • SELECT
  • DCL(用来管理数据库的语言,包括管理权限及数据更改)
    • GRANT
    • COMMIT
    • ROLLBACK

创建表语句

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [ IF NOT EXISTS ] `表名` (
`字段名1` 列类型 [ 属性 ] [ 索引 ] [注释] ,
`字段名2` 列类型 [ 属性 ] [ 索引 ] [注释] ,
… …
`字段名n` 列类型 [ 属性 ] [ 索引 ] [注释]
) [ 表类型 ] [ 表字符集 ] [注释] ;

对照


1
2
3
4
5
6
7
8
9
create table student (
id int(10),
name varchar(20),
sex char(1),
age int(3),
major varchar(20),
birthday datetime,
address varchar(50)
);

属性

  • unsigned 修饰的列不能为负数

    1
    2
    3
    4
    5
    6
    drop table if exists student;
    create table student (
    id int(10),
    name varchar(20),
    age int(3) unsigned
    );
  • zerofill 如果int(3), 记录对应的值为7, 显示出来就是007

    1
    2
    3
    4
    5
    drop table if exists student;
    create table student (
    id int(3) zerofill,
    name varchar(20)
    );
  • auto_increment, 自动增长

    1
    2
    3
    4
    5
    6
    drop table if exists student;
    create table student (
    id int(5) auto_increment,#自动增长和主键一起用
    name varchar(20),
    primary key(id)# 把id设置为主键
    );
  • null not null

    1
    2
    3
    4
    5
    6
    drop table if exists student;
    create table student (
    id int(5) auto_increment,
    name varchar(20) not null,# 不能为null
    primary key(id)
    );
  • default

    1
    2
    3
    4
    5
    6
    7
    drop table if exists student;
    create table student (
    id int(5) auto_increment comment '这是id字段',
    name varchar(20),
    sex char(2) default '保密',#设置默认值
    primary key(id)
    ) comment '这是描述学生的表';

事务

  • 事务就是将一组SQL语句放在同一批次内去执行,如果一个SQL语句出错,则该批次内的所有SQL都
    将被取消执行

  • 事务的四大原则

    • 原子性(Atomicity)
      • 事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行。
    • 一致性(Consistency):
      • 事务的执行前后,数据完整性要保持一致。
    • 隔离性(Isolation):
      • 事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。
    • 持久性(Durability):
      • 对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障。
  • 事务的应用场景
    转账:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    create table account (
    id int(10) auto_increment primary key,
    name varchar(50),
    cash double
    );
    insert into account
    (name, cash)
    values
    ('A',2000),
    ('B',10000);
    ##############事务操作################
    #1 关闭自动提交
    set autocommit=0;
    #2 开启事务
    start transaction;
    #3 sql 由于各种原因有可能转账失败
    update account set cash=cash-500 where name='A';
    update account set cash=cash+500 where name='B';
    #4回滚或提交(失败使用回滚)
    commit;#rollback;
    #开启自动提交
    set autocommit=1;

DQL精讲

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DROP TABLE IF EXISTS `student`;
#案例中的表
create table student (
id int(10) auto_increment,
name varchar(20),
sex char(1),
age int(3),
chinese int(3),
math int(3),
english int(3),
grade varchar(10),
primary key (id)
) charset=utf8;
insert into student
(id,name,sex,age,chinese,math,english,grade)
values
(1,'赵子龙','男',30,100,90,60,'1班'),
(2,'吕布','男',35,90,90,90,'1班'),
(3,'诸葛孔明','男',30,60,60,60,'1班'),
(4,'张飞','男',20,10,59,0,'1班'),
(5,'貂蝉','女',18,100,70,99,'2班'),
(6,'大乔','女',20,100,90,60,'2班'),
(7,'小乔','女',20,100,90,60,'2班'),
(8,'张三三','女',20,10,100,100,'2班'),
(9,'曹操','男',50,90,50,90,'2班'),
(10,'李四','男',18,100,100,100,'3班');

1. 普通查询 select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#查询所有
select* from tb;
#指定字段
select field1, field2, ... from tb;
#为字段起别名
select id as 学号, name 名字, math+10 数学, chinese 中文,english 英文 from student ;
#查询学生的id name,各科成绩,总分和平均分
select id,name,english,math,chinese,english+math+chinese 总分,(english+math+chinese)/3 平均分 from student;
#查询所有的学生所有字段
select * from student;
#查询所有学生对应的数学和英语
select id, name, math, english from student;
#过滤表中的重复数据 显示数学成绩
select distinct math from student;
#所有学生的分数+10分显示
select name, english+math+chinese+10 总分 from student;

2. 条件查询

1
2
3
4
5
6
7
8
9
10
11
查询英语成绩大于90分的同学
select * from student where english>90;
查询总分大于270的同学
select * from student where english+chinese+math>270;
数学成绩在80~100之间的同学
select * from student where math>=80 and math<=100;
select * from student where math between 80 and 100;
查询成绩在数学57,58,59,60的同学
select * from student where math in (57,58,59,60);
查询数学成绩>90,英语<60的同学
select * from student where math>90 and english<60;

3. 模糊查询 使用like , %:0或多个字符 , _ :一个字符

1
2
3
4
5
6
查询名字中带有明的
select * from student where name like '%明%';
查找名字叫张x的学生
select * from student where name like '张_';
查找名字叫张xx的同学
select * from student where name like '张__';

4. 排序查询(order by 字段名 asc|desc)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
按数学成绩高低排序 (desc 表示降序)
select id, name, math from student order by math desc;
按总分成绩由低到高
select id, name, math+chinese+english 总分
from student order by 总分;
对姓张的学生按数学成绩排序输出(降序)
select id,name, math from student
where name like '张%'
order by math desc;
```
---
### 5. 分组查询 (group by):
```SQL
where 在分组之前进行过滤,不能使用聚合函数
having 在分组之后进行过滤,可以使用聚合函数,having中用到的列,必须在前面使用过才可以用
select id,name from students group by math;

6. 使用聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
select * from student;
#count :统计符合条件的记录个数
#1. 统计一个班级的共有多少学生
select count(*) from student;
#2. 统计总分及格的学生个数
select count(*) from student
where english+math+chinese>=180;
#3. 统计各个班级总分及格的学生个数
select grade 班级, count(*) 人数 from student
where english+math+chinese>=180
group by grade;
#4. 统计各个班级总分及格的学生个数, 只显示几个人数多于4个的
select grade 班级, count(*) 人数 from student
where english+math+chinese>=180
group by grade
having count(*)>=4;
##SUM : 返回满足条件的,列对应数据的总和
统计数学总成绩
SELECT SUM(math) FROM student;
#1. 统计各个班级的数学成绩总和
SELECT grade ,SUM(math) FROM student
GROUP BY grade;
#2. 计各个班级各科总成绩:
SELECT grade, SUM(chinese),SUM(math),SUM(english)
FROM student
GROUP BY grade;
#3. 统计各个班级总分
SELECT grade, SUM(chinese+math+english)
FROM student
GROUP BY grade;
##AVG : 返回一列的平均值
#1. 求各个班级数学平均分
select grade,avg(math) from student
group by grade;
#2. 求各个班级总分的平均分
select grade , avg(chinese+math+english) from student
group by grade;
#max/min : 函数返回满足where条件的列最大值/最小值
#1. 各个班级数学最高分
select max(math) from student
group by grade;
#2. 各个班级成绩的最高分
select grade,max(math+chinese+english) from student
group by grade;
#limit用来做分页显示
LIMIT [m,n] m: 开始位置, n 偏移量 第m条记录后的n条数据
#总成绩前3的学生
select id, name , english+chinese+math 总分 from student
order by 总分 DESC
limit 3;
#总成绩5~10的学生
select id, name , english+chinese+math 总分 from student
order by 总分
limit 4, 6;

索引

提高查询速度

  • 主键索引(PRIMARY KEY)
    • 同时保证实体完整性
  • 唯一索引(UNIQUE)
    • 避免同一个表中某数据列中的值重复,与主键索引区别:主键索引只能有一个,唯一索引可有多个
  • 常规索引(INDEX)
    • 快速定位特定数据
  • 全文索引(FULLTEXT)
    • 只能用于MYISAM类型的数据表,只能用于CHAR 、VARCHAR、TEXT据列类型,适合大型数据集

索引不是越多越好!!!不要对经常变动的数据加索引,小数据量的表建议不要加索引,一般应加在查找条件的字段

外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#数据库中,提供的语法, 帮我们约束表与表之间列的参考关系
########### 多表查询 #############
drop table if exists dept;#如果存在就删除
# 部门表
create table dept(
id int(5) auto_increment,
name varchar(20),
tel varchar(20),
primary key(id)
);
#员工表
drop table if exists emp;
create table emp (
id int(10) auto_increment,
name varchar(20),
salary double,
age int(3),
sex char(1),
dept_id int(5),#参考部门的id
foreign key(dept_id) references dept(id), # 定义外键
primary key(id)
);
#添加部门信息
truncate dept;
insert into dept
(id,name,tel) values
(1,'人事部','021-6818888'),
(2,'财务部','021-6818877'),
(3,'技术部','021-6818866'),
(4,'市场部','021-6818855');
#添加员工
insert into emp values
(null,'尚云飞',10000,29,'男',2),
(null,'吕纯阳',8000,30,'男',1),
(null,'七星',8000,20,'女',3),
(null,'令狐冲',10000,29,'男',4);
select* from dept;
select * from emp;
#问题
#不能添加一个员工到 不存在部门
insert into emp values (null,'张三',10000,18,'男',6);
#删除部门 先删除部门再删除员工
delete from emp where dept_id=1;
delete from dept where name='人事部';

表与表之间的关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
1) 笛卡尔积
select dept.*, emp.* from dept,emp;
2) **#查看员工及所属部门信息
select emp.*, dept.* from dept,emp
where dept.id=emp.dept_id;
3)**#内联 理解内连接作用及语法
select emp.*, dept.* from emp
inner join dept
on emp.dept_id=dept.id;
4)**#左外连接 理解外连接的作用及语法
SELECT * FROM emp
LEFT JOIN dept
ON emp.dept_id=dept.id;
SELECT * FROM dept
LEFT JOIN emp
ON emp.dept_id=dept.id;
5) #右外连接
#全连接
SELECT * FROM emp
LEFT JOIN dept
ON emp.dept_id=dept.id
UNION
SELECT * FROM emp
RIGHT JOIN dept
ON emp.dept_id=dept.id;

综合测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#1 查看员工信息及所属部门信息
select * from emp
inner join dept
on emp.dept_id = dept.id;
#2 查看叫熊二的员工的所属部门名及电话
#内链查询
select dept.name,tel from emp
inner join dept
on emp.dept_id = dept.id
where emp.name='熊二';
#子查询
select name, tel from dept
where id in
(select dept_id from emp where name='熊二');
#3 查看各部门人数
SELECT dept.name, COUNT(*) FROM dept
INNER JOIN emp ON dept.id=emp.dept_id
GROUP BY dept.id;
#4 查看各部门平均薪资
SELECT dept.name, avg(salary) FROM dept
INNER JOIN emp ON dept.id=emp.dept_id
group by dept.id;
#5 给技术部所有员工加薪1000
update emp set salary=salary+1000
where dept_id in
(select id from dept where name = '技术部');
#6 按薪资降序查看员工信息及部门名称
select emp.*, dept.name from dept
inner join emp on dept.id = emp.dept_id
order by salary desc;

数据库备份()

    1. 没有登录mysql

      • 备份

        1
        2
        3
        mysqldump -u root -p [选项] 数据库名 [表1,...] > 位置
        mysqldump -u root -p test_db persons > D:\per.sql
      • 恢复数据

        1
        2
        3
        mysql -u root -p 数据库名 < 位置
        mysql -u root -p person_db < D:\per.sql
    1. 登录mysql

      • 恢复到当前数据库
        • source sql位置

视图/触发器/函数/存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
1)视图
#创建视图 视图名dept_emp_view
#解释, 把select的结果作为视图,视图是一张虚表,数据来源于真是表
CREATE VIEW dept_emp_view
AS
SELECT emp.eid,ename,sex,income,salary,age,dept.did,dname,dtel FROM emp
INNER JOIN dept ON emp.did = dept.did;
#查询虚表
select ename,dname from dept_emp_view;
#删除视图
DROP VIEW IF EXISTS 视图名
#查看视图
DESC myview;
SHOW CREATE VIEW myview;
#修改视图
CREATE OR REPLACE VIEW myview(id,NAME,dname) AS SELECT 字段1,字段2,字段3 FROM ...;
2) 存储过程
a)基本的存储过程使用
#定界符(结束符) 是 ";"
delimiter $ #修改定界符为$
create procedure test_pro()
begin #存储过程定义开始 相当于java中方法的{
update employee set salary=salary+1000
where did=(select did from department where dname='研发部 ');
end #存储过程定义结束 相当于java中方法的}
$ #结束
delimiter ; #结束符为;
#调用存储过程
call test_pro();
#删除
DROP PROCEDURE test_pro;
#查看存储过程
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE test_pro;
#测试
select ename,salary,did from employee;
b)***扩展*** 带参数的存储过程使用
#1. 准备创建用户表 实现调用存储过程,通过传参 实现把记录再次重插入user表
CREATE TABLE USER (
u_id INT(10) AUTO_INCREMENT PRIMARY KEY,
uname VARCHAR(20)
);
INSERT INTO USER VALUES (1,'张三'),(2,'李四'),(3,'王五');
#2. 创建存储过程 in|out|inout in类型参数v_id 用来传递参数
DELIMITER $$ #
CREATE PROCEDURE my_pro(IN v_id INT)
BEGIN
DECLARE v_uname VARCHAR(50); #定义变量
SELECT uname INTO v_uname FROM USER WHERE u_id = v_id; #通过select...into...给变量赋值
INSERT INTO USER (u_id, uname) VALUES (NULL,v_uname); #执行DDL语句
END
$$ #存储过程创建结束
DELIMITER ; #恢复mysql分号定界符
#测试
SELECT * FROM USER;
#3. 调用
CALL my_pro(2);
3) 函数
系统函数
自定义函数
mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS 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)
4) 触发器
坚持原创技术分享,您的支持将鼓励我继续创作!