MySQL基础复习

  • SQL语句分类

1) Data Definition Language (DDL 数据定义语言) 如:建,建

2) Data Manipulation Language(DML 数据操纵语言),如:对表中记录操作增删改

3) Data Query Language(DQL 数据查询语言),如:对表中的查询操作

4) Data Control Language(DCL 数据控制语言),如:对用户权限的设置 

  •  1.1 DDL操作数据库 

创建数据库 

-- 直接创建数据库db1 
create database db1;
-- 判断是否存在,如果不存在则创建数据库db2
create database if not exists db2;
-- 创建数据库并指定字符集为gbk
create database db3 default character set gbk;

查看数据库 

-- 查看所有的数据库 
show databases; 
 
-- 查看某个数据库的定义信息 
show create database db3; 
show create database db1;

修改数据库

-- 将 db3 数据库的字符集改成 utf8 
alter database db3 character set utf8;

删除数据库 

-- 删除 db2 数据库 
drop database db2;

使用数据库 

-- 查看正在使用的数据库 
select database(); 
 
-- 改变要使用的数据库 
use db4; 
  • 1.2 DDL操作表结构 

前提先使用某个数据库 

amount DECIMAL(19,4); 这是保留4位小数的意思

create table student (  
id int,  -- 整数  
name varchar(20),  -- 字符串 
amount DECIMAL(19,4),
birthday date -- 生日,最后没有逗号 );

查看表 

desc 查看表结构

MySQL基础复习

 快速创建一个表结构相同的表 (这不熟)

语法  CREATE TABLE 新表名 LIKE 旧表名;

-- 创建一个s1的表与student结构相同 
create table s1 like student; 
 desc s1; 

MySQL基础复习

 修改表结构 

添加表列add

为学生表添加一个新的字段 remark,类型为 varchar(20) 

alter table student add remark varchar(20);

修改列类型MODIFY 

将 student 表中的 remark 字段的改成 varchar(100) 

alter table student modify remark varchar(100); 

修改列名 CHANGE 

将 student 表中的 remark 字段名改成 intro,类型 varchar(30) 

alter table student change remark intro varchar(30); 

删除列 DROP 

删除 student 表中的字段 intro 

alter table student drop intro;

MySQL基础复习

 修改字符集character set 

将 student2 表的编码修改成 gbk 

alter table student2 character set gbk; 
  • 2.DML操作表中的数据

用于对表中的记录进行增删改操作

MySQL基础复习

 复制表的数据

 什么是蠕虫复制: 将一张已经存在的表中的数据复制到另一张表中

? 将表名 2 中的所有的列复制到表名 1 中

INSERT INTO 表名 1 SELECT * FROM 表名 2;

? 只复制部分列

INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM student; 

更新表记录 (用update不能用alert,alert是操作表的,表中数据修改只能用update)

-- 不带条件修改数据,将所有的性别改成女 
update student set sex = ; 
 
-- 带条件修改数据,将id号为2的学生性别改成男 
update student set sex= where id=2; 
 
-- 一次修改多个列,把id为3的学生,年龄改成26岁,address改成北京 
update student set age=26, address=北京 where id=3; 

删除表记录

-- 带条件删除数据,删除id为1的记录 
delete from student where id=1; 
 
-- 不带条件删除数据,删除表中的所有数据 
delete from student; 
  • 3.DQL查询表中的数据

查询不会对数据库中的数据进行修改.只是一种显示数据的方式 

SELECT 列名 FROM 表名 [WHERE 条件表达式] 

1) SELECT 命令可以读取一行或者多行记录。

2) 你可以使用星号(*)来代替其他字段,SELECT 语句会返回表的所有字段数据

3) 你可以使用 WHERE 语句来包含任何条件。

使用别名AS的好处: 显示的时候使用新的名字,并不修改表的结构。 

-- 列使用别名 
select name as 姓名,age as 年龄 from student; 
-- 表和列同时使用别名 
select st.name as 姓名,age as 年龄 from student as st;-- st.name换成name也可以

清除重复值 (distinct 不熟)

-- 查询学生来至于哪些地方 
select address from student; 
 
-- 去掉重复的记录 
select distinct address from student;

查询结果参与运算 

select * from student; 
 
-- 给所有的数学加 5 分 
select math+5 from student; 
 
-- 查询 math + english 的和
 
select *,(math+english) as 总成绩 from student; -- as 可以省略 

???这样是结果显示增加了一列叫总成绩的吗 是的

MySQL基础复习

条件查询    (WHERE 条件)

为什么要条件查询 如果没有查询条件,则每次查询所有的行。实际应用中,一般要指定查询的条件。对记录进行过滤

MySQL基础复习

重要!!!

 MySQL基础复习

-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足) 
select * from student3 where age>35 and sex=; 
 
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足) 
select * from student3 where age>35 or sex=; 
 
-- 查询 id 是 1 或 3 或 5 的学生 
select * from student3 where id=1 or id=3 or id=5;

in 关键字 

SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);

in 里面的每个数据都会作为一次条件,只要满足条件的就会显示 

-- 查询id是1或3或5的学生 
select * from student3 where id in(1,3,5); 
 
-- 查询id不是1或3或5的学生 
select * from student3 where id not in(1,3,5);

范围查询

BETWEEN 值 1 AND 值 2

表示从值 1 到值 2 范围,包头又包尾

比如:age BETWEEN 80 AND 100 相当于: age>=80 && age<=100 

--查询 english 成绩大于等于 75,且小于等于 90 的学生 
select * from student3 where english between 75 and 90;

MySQL基础复习

-- 查询姓马的学生 
select * from student3 where name like 马%; 
 
select * from student3 where name like ; -- 这个不行 这个只有名字叫马才能查出来
 
-- 查询姓名中包含‘德‘字的学生 
select * from student3 where name like %德%; -- 这个不管开头结尾 包含就行 就叫德都行
 
-- 查询姓马,且姓名有两个字的学生 
select * from student3 where name like 马_;

MySQL约束与设计

继续DQL查询语句 

1.排序 

通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];

ASC: 升序,默认值  DESC: 降序

-- 查询所有数据,使用年龄降序排序 
select * from student order by age desc; 

 组合排序

? 什么是组合排序?

同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推

-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序 
select * from student order by age desc, math asc; 

2.聚合函数

之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询, 它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。

MySQL基础复习

 MySQL基础复习

 语法: SELECT 聚合函数(列名) FROM 表名; (select后面使用)

-- 查询学生总数 
select count(id) as 总人数 from student; 
select count(*) as 总人数 from student; 

IFNULL(列名,默认值)  如果列名不为空,返回这列的值。如果为 NULL,则返回默认值

-- 查询id字段,如果为null,则使用0代替 
select ifnull(id,0) from student; 
--我们可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏 
select count(ifnull(id,0)) from student; 

3.分组 (查询结果分组展示

分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组

SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件]

GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。 分组的目的就是为了统计,一般分组会跟聚合函数一起使用

-- 按性别进行分组,求男生和女生数学的平均分 
select sex, avg(math) from student3 group by sex; 

MySQL基础复习

 注意:当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的 

--查询年龄大于 25 岁的人,按性别分组,统计每组的人数 
select sex, count(*) from student3 where age > 25 group by sex ;

只显示符合having条件的分组

-- 对分组查询的结果再进行过滤 
--查询年龄大于 25 岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;

MySQL基础复习

 group by后面不能出现where,要使用having 

4.limit语句 (不熟悉)超级有用啊啊啊啊

MySQL基础复习

 有用!!!!!!!!!!!!!!!这把offset设为变量 i++ 不就可以实现自动序列遍历嘛!!!!!!!!!!!!!!!!!!!

-- 查询学生表中数据,从第3条开始显示,显示6条。 
select * from student3 limit 2,6;

LIMIT的使用场景: 分页

比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的 条数。 假设我们每页显示 5 条记录的方式来分页

-- 如果第一个参数是0可以省略写:  
select * from student3 limit 5; 
 
-- 最后如果不够5条,有多少显示多少 
select * from student3 limit 10,5;

数据库备份和还原 

-- 备份day21数据库中的数据到d:\day21.sql文件中 
mysqldump -uroot -proot day21 > d:/day21.sql

导出结果:数据库中的所有表和数据都会导出成 SQL 语句  

MySQL基础复习

 数据库表的约束 

MySQL基础复习

 通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行

? 疑问:如果一个字段设置了非空与唯一约束,该字段与主键的区别?

1) 主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。

2) 自增长只能用在主键上 

MySQL基础复习

 那我比较喜欢truncate!!!但不会用啊。。

默认值 

设置了非空的话必须要有默认值!!!!这里出错好难发现。。。

-- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州 
create table st9 (   
id int,   name varchar(20),   
address varchar(20)  default 广州 
);
-- 添加一条记录,使用默认地址 insert into st9 values (1, 李四, default);
select * from st9; insert into st9 (id,name) values (2, 李白); -- 添加一条记录,不使用默认地址 insert into st9 values (3, 李四光, 深圳);

外键约束 

什么是外键:在从表中与主表主键对应的那一列,

如:员工表中的 dep_id

主表: 一方,用来约束别人的表 

从表: 多方,被别人约束的表 

MySQL基础复习

是给从表增加外键!!!emplotee是从表我晕。

? 新建表时增加外键:  [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

? 已有表增加外键:  ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名); 

-- 2) 创建从表 employee 并添加外键约束emp_depid_fk 
-- 多方,从表 
create table employee(  
id int primary key auto_increment,  
name varchar(20),  
age int,  
dep_id int,  
-- 外键对应主表的主键  
-- 创建外键约束  
constraint emp_depid_fk foreign key (dep_id) references department(id) )
-- 删除employee表的emp_depid_fk外键 
alter table employee drop foreign key emp_depid_fk; 
 
-- 在employee表情存在的情况下添加外键 
alter table employee add constraint emp_depid_fk  foreign key (dep_id) references department(id);

MySQL基础复习 

 -- 创建外键约束  
constraint emp_depid_fk foreign key (dep_id) references  department(id) on update cascade on delete cascade ) 

什么是复合主键

  主键是唯一的索引,通常会用ID做为主键,但是并不是每个表都存在ID字段,例如学生表(姓名,生日,性别,班级),这里面每一个值都可能重复,无法使用单一字段作为主键,这时我们可以将多个字段设置为复合主键,由复合主键标识唯一性。只要不是复合主键每个值都重复,就不算重复。(主键依然只有这一个)

--创建收藏表tab_favorite
-- rid 旅游线路id,外键 
--date 收藏时间 
--uid用户id,外键 
--rid和uid不能重复,设置复合主键,同一个用户不能收藏同一个线路两次 
create table tab_favorite (   
rid int,   
date datetime,  
 uid int,   
-- 创建复合主键   
primary key(rid,uid),  
 foreign key (rid) references tab_route(rid),   
foreign key(uid) references tab_user(uid) 
) 

数据库设计

数据规范化

什么是范式:
好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需 要满足一些规则来优化数据的设计和存储,这些规则就称为范式。

三大范式:

简而言之,第一范式(1NF)每一列不可再拆分,称为原子性

     第二范式(2NF)就是在第一范式的基础上所有列完全依赖于主键列

     第三范式就是所有列不依赖于其它非主键列,也就是在满足 2NF 的基础上,任何非主列不得传递依赖于主键

------------------------------------------------------------------------------------------------------------------------------------

MySQL多表查询与事务的操作 

表连接查询 

内连接

用左边表的记录去匹配右边表的记录,如果符合条件的则显示。如:从表.外键=主表.主键 

隐式内连接
? 隐式内连接:看不到 JOIN 关键字,条件使用 WHERE 指定:SELECT 字段名 FROM 左表, 右表 WHERE 条件 

select * from emp,dept where emp.dept_id = dept.id; 

显式内连接
? 显示内连接:使用 INNER JOIN ... ON 语句, 可以省略 INNER SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

select * from emp e inner join dept d on e.dept_id = d.id;
--确定查询字段,查询唐僧的信息,显示员工 id,姓名,性别,工资和所在的部门名称 
select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`=唐僧;

总结内连接查询步骤:

  1) 确定查询哪些表

  2) 确定表连接的条件

  3) 确定查询的条件

  4) 确定查询的字段

左外连接
? 左外连接:使用 LEFT OUTER JOIN ... ON,OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录,如果符合条件的则显示;

否则,显示 NULL

可以理解为:内连接的基础上保证左表的数据全部显示(左表是部门,右表员工)

-- 使用内连接查询 
select * from dept d inner join emp e on d.`id` = e.`dept_id`; 
 
-- 使用左外连接查询 
select * from dept d left join emp e on d.`id` = e.`dept_id`;

对比结果:(内连接是红线部分,只显示了id与dept_id相等的。左外连接是全部部分,保证了dept这个左表全部显示了。)

MySQL基础复习

右外连接
? 右外连接:使用 RIGHT OUTER JOIN ... ON,OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

用右边表的记录去匹配左边表的记录,如果符合条件的则显示;

否则,显示 NULL

可以理解为:在内连接的基础上保证右表的数据全部显示

-- 使用内连接查询  
select * from dept inner join emp on dept.`id` = emp.`dept_id`; 
 
-- 使用右外连接查询 
select * from dept right join emp on dept.`id` = emp.`dept_id`;

MySQL基础复习

子查询(两张表!有点难了)

-- 需求:查询开发部中有哪些员工 
select * from emp; 
 
-- 通过两条语句查询 
select id from dept where name=开发部 ; 
select * from emp where dept_id = 1; 
-- 使用子查询 
select * from emp where dept_id = (select id from dept where name=‘开发);

? 子查询的概念:

1) 一个查询的结果做为另一个查询的条件

2) 有查询的嵌套,内部的查询称为子查询

3) 子查询要使用括号 

子查询的结果是一个值的时候

? 子查询结果只要是单行单列,肯定在 WHERE 后面作为条件,父查询使用:比较运算符,如:> 、<、<>、 = 等

SELECT 查询字段 FROM 表 WHERE 字段 =(子查询);

-- 根据最高工资到员工表查询到对应的员工信息 
select * from emp where salary = (select max(salary) from emp);
--  到员工表查询薪水小于平均的员工信息 
select * from emp where salary < (select avg(salary) from emp); 

子查询结果是多行单列的时候

子查询结果是单例多行,结果集类似于一个数组,父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

--查询工资大于5000的员工,来自于哪些部门的名字
-- 先查询大于5000的员工所在的部门id
-- 再查询在这些部门id中部门的名字

select name from dept where id in (select dept_id from emp where salary > 5000);
--查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
-- 再查询在这些部门id中有哪些员工
select * from emp where dept_id in (select id from dept where name in(开发部,财务部));

子查询的结果是多行多列

子查询结果只要是多列,肯定在 FROM 后面作为表 

SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;

子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段

-- 查询出2011年以后入职的员工信息,包括部门名称 
-- 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date >=2011-1-1; -- 查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id select * from dept d, (select * from emp where join_date >=2011-1-1) e where d.`id`= e.dept_id ;

表与表也可以用表连接(这个是显式内连接)

-- 也可以使用表连接: 
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >=2011-1-1;

事务 

事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的 SQL 语句都要回滚,整个业务执行失败。(所以事务是使sql语句为整体执行结果的) 

MYSQL 中可以有两种方式进行事务的操作:

1) 手动提交事务

2) 自动提交事务 

MySQL基础复习

 自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务每条语句都会自动开启一个事务,语句执行完毕 自动提交事务,MySQL 默认开始自动提交事务 

事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接) 

回滚点
5.5.1 什么是回滚点
在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点(就像是玩游戏的cheakpoint检查点)

事务的四大特性ACID 

MySQL基础复习

 事务的隔离级别

事务在操作时的理想状态: 所有的事务之间保持隔离,互不影响。

但因为并发操作,多个用户同时访问同一个数据。可能引发并发访问的问题

MySQL基础复习

  MySQL数据库有四种隔离级别(重要的!!考到了!!!原来是这里。。。)

上面的级别最低,下面的级别最高。“是”表示会出现这种问题,“否”表示不会出现这种问题。 

MySQL基础复习

MySQL事务隔离级别相关的命令

? 查询全局事务隔离级别 查询隔离级别

select @@tx_isolation;
? 设置事务隔离级别,需要退出 MySQL 再重新登录才能看到隔离级别的变化

设置隔离级别

set global transaction isolation level 级别字符串(就那4个 read uncommitted,read committed,repeatable read,serializable);

脏读非常危险的,比如张三向李四购买商品,张三开启事务,向李四账号转入 500 块,然后打电话给李四说钱 已经转了。李四一查询钱到账了,发货给张三。张三收到货后回滚事务,李四的再查看钱没了。

解决脏读的问题:将全局的隔离级别进行提升 

两次查询输出的结果不同,到底哪次是对的?不知道以哪次为准。 很多人认为这种情况就对了,无须困惑, 当然是后面的为准。我们可以考虑这样一种情况,比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户,结果在一个事务中针对不同的输出目的地进行的两次查询不一致,导致文件和屏幕中的结果不一致,银行工作人员就不知道以哪个为准了。

解决不可重复读的问题: 将全局的隔离级别进行提升为:repeatable read

同一个事务中为了保证多次查询数据一致,必须使用 repeatable read 隔离级别 

在 MySQL 中无法看到幻读的效果。 但我们可以将事务隔离级别设置到最高,以挡住幻读的发生

使用serializable隔离级别,一个事务没有执行完,其他事务的SQL执行不了,可以挡住幻


关于使用什么关键字的总结:

? DDL 库和表: create / alter / drop

? DML 增删改表中数据:insert /update/delete

? DQL 查询数据:select /show

?DCL :grant /revoke 

关于DCL 数据库控制:

1.创建用户

--创建 user1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123
create user user1@localhost identified by 123; 
--创建 user2 用户可以在任何电脑上登录 mysql 服务器,密码为 123 create user user2@% identified by 123;

2.给用户授权

--给 user1 用户分配对 test 这个数据库操作的权限:创建表,修改表,插入记录,更新记录,查询 
grant create,alter,insert,update,select on test.* to user1@localhost; 
-- 注:用户名和主机名要与上面创建的相同,要加单引号。 

-- 给 user2 用户分配所有权限,对所有数据库的所有表  
grant all on *.* to user2@%; 

3.撤销授权

--撤销 user1 用户对 test 数据库所有表的操作的权限 
revoke all on test.* from user1@localhost;

4.查看权限

语法: SHOW GRANTS FOR ‘用户名‘@‘主机名‘; 

5.删除用户

--删除 user2 
drop user user2@%; 

6.修改管理员密码 

语法: mysqladmin -uroot -p password 新密码
注意:需要在未登陆 MySQL 的情况下操作,新密码不需要加上引号。

7.修改普通用户密码
语法: set password for ‘用户名‘@‘主机名‘ = password(‘新密码‘);
注意:需要在登陆 MySQL 的情况下操作,新密码要加单引号。

 

MySQL基础复习

上一篇:学习 SQL Server (6) :内置函数,触发器


下一篇:python 函数--内置函数