MySQL知识复习
一、基本语句和语法
数据库的创建和表的创建、查看
-
创建数据库 create database db;
-
使用指定数据库 use db;
-
查看数据库或者表结构 desc db;
-
查看一个数据库的定义信息 show create database db;
-
查看当前正在使用的数据库 select database();
-
修改数据库 alter database db character set 字符集;
-
复制一张数据表 create table t1 like t2;
-
查看创建表的SQL语句 show create table t1;
-
修改表名 rename table old to new;
-
修改表的字符集 alter table t1 character set 字符集
-
添加字段 alter table t1 add 字段名 字段类型(长度)
-
修改表中字段类型或者长度
alter table t1 modify 字段名称 字段类型
例:alter table t1 modify no varchar(10); -
修改列名称 关键字 change
alter table t1 change oldname newname 类型(长度)
如: alter table no change no number varhcar(20); -
删除列 alter table t1 drop 列名
DML 对表中数据的增删改
1.数据插入
- insert into t1 (name1,name2) values()
方式一: 插入全部字段
insert into student (no,info) values(’’,’’);
方式二: 插入全部字段 不写字段名
insert into student values(’’,’’);
方式三: 插入指定字段
…
2.修改操作
语法格式1: update 表明 set 列名=值
语法格式2: update 表名 set 列名=值 where no=4;
删除表格
方式1:drop table t1;
方式2:drop table if exists t2;
一次修改多个列
update 表名 set 列名1=值,列名2=值2 where id=6;
删除操作
语法格式1: delete form 表名;
语法格式2: delete from 表名 where 条件;
- 删除所有数据的方式 两种
delete from 表名; 方式是表中数据逐条删除,效率低
truncate table 表名; 推荐使用,删除整张表,然后在创建一个一模一样的新表,但对设置了自增的主键 会重置。
查询操作
-
简单查询
- select 列名 from 表名;
查询所有数据,然后将 列名改为中文 起别名
select id as ‘编号’,name as ‘姓名’ from emp; --as 可以省略 - 将所有人的年龄+10 不会对表中的数据进行修改,只是显示的一种方式
如: select age+10 from emp;
- select 列名 from 表名;
-
条件查询
- 语法格式: select 列名 from 表名 where 条件表达式
-
模糊查询
- like ‘%精%’ :包含 精 的任意字符串
%a 通配符 以a结尾的字符串 - _ 通配符 匹配一个字符串
例1: 查询第二个字为’a’ 的 like ‘_a%’
例2:查询 没有部门的 员工信息 is null
select * from emp where dept is null;
例3: 查询有部门的员工信息 is not null
select * from emp where dept is not null;
- like ‘%精%’ :包含 精 的任意字符串
-
条件查询的方式:先找出每一条数据,满足条件的就返回,不满足的就过滤
排序查询
-
order by语句
- select 字段名 from 表名 【where 字段名=值】 order by 字段名称 【ASC/DESC】ASC升序 DESC 降序
-
单列排序:按照某一个字段进行排序
- 例:select * from emp order by age;组合排序 在一个排序基础上再使用 其他的列名 排序
-
聚合函数:
- count (字段) 统计记录数
- sum(字段)求和
- max(字段)最大值
- min(字段)最小值
- avg(字段)求平均值
- 语法格式 select 聚合函数(字段名) from 表名 [where 条件]
- 例:select count() from emp;等价于 select count() from emp;
- count 函数 在统计的时候会忽略 该字段为空的数据
-
分组查询
- group by 子句
- 语法格式 select 分组字段/聚合函数 from 表名 group by 分组字段;
- 分组的目的 就是为了执行统计操作 一般分组会和聚合函数一起使用另外查询的时候要查询分组字段
- 例:查询平均薪资大于6000 的部门
select dept_name,avg(salary) from emp
where dept_name is not null
group by dept_name
having avg(salary)>6000;
- 例:查询平均薪资大于6000 的部门
- group by 子句
-
where 与having的区别
where:
1.在分组前进行过滤
2.where后面不能跟 聚合函数
having:
1.在分组后进行 条件过滤
2.having后可以写 聚合函数 -
limit 通过limit 去指定要查询的数据的条数 、行数
- 语法格式: select 字段 from 表名 limit offset,length;
- 参数说明: offset :起始行数 默认从O开始 length:返回多少行
二、约束
1.概念:对数据进行一定的限制,保证数据的完整性、有效性、正确性
常见约束:
- 主键约束 primary key :不可重复 唯一 非空 语法: 字段名 字段类型 primary key
- 方式一:create table emp2(eid int primary key,ename varchar(20),sex char(1));
- 方式二:create table emp2(eid int,ename varchar(20),primary key(eid));
- 方式三:创建表之后 再添加主键 : 1.建表 2.alter table emp2 add primary key(字段名);
- 删除主键: alter table emp2 drop primary key;
- 主键自增: auto_increment (字段类型必须是整数类型)
- 例:eid int primary key auto_increment ,
- 修改 自增的起始值:create table emp2 ( ) auto_increment=100;
- delete和 truncate对自增长的影响
- truncate删除后 自增从1开始
- 唯一约束 unique :该列数值不能重复
- 非空约束 :某一列不允许为空值 语法:字段名 字段类型 not null
三、事务
事物操作:
1.手动提交事务:
1.开启事务: start transaction ; 或者 begin
2.提交事务 commit
3.回滚事务 rollback;
2.自动提交事务:
- mysql默认提交方式为 自动提交事务
- 每执行一条DML语句 都是单独的一个事务
四、多表操作
1.多表关系设计
- 一对多(常见)
- 例如:班级和学生,部门和员工,客户和订单,分类和商品
- 原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
- 一对一
- 在实际的开发中应用不多.因为一对一可以创建成一张表。
- 外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
- 多对多
- 例如:老师和学生,学生和课程,用户和角色
- 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
2.子查询
(一)概念:一条select 查询语句的结果, 作为另一条 select 语句的一部分
(二)特点:
- 子查询必须放在小括号中
- 子查询一般作为父查询的查询条件使用
(三)常见分类:
- where型 子查询: 将子查询的结果, 作为父查询的比较条件
- from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用
- exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果
五、索引
1.概念
可以大幅提高查询效率,使得MySQL查询更加高效。
2.常见索引
- 主键索引:主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一条记录
- 唯一索引:唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一.
- 普通索引:最常见的索引,作用就是 加快对数据的访问速度
3.索引的优缺点
- 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
索引的优点
- 大大的提高查询速度
- 可以显著的减少查询中分组和排序的时间。
索引的缺点 - 创建索引和维护索引需要时间,而且数据量越大时间越长
- 当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度