一、MySQL
DDL语言
(一)数据库的管理
1.创建数据库
创建一个名称为mydb1的数据库。
create database 库名;
创建一个使用utf-8字符集的库名数据库。
create database 库名 character set utf8;
2.查看数据库
显示所有数据库
show databases;
3.修改数据库
修改库名的字符集为utf8(不能修改数据库名)
alter database 库名 character set utf8;
4.删除数据库
删除数据库
drop database 库名;
(二)表的管理
1.创建表
create table t1 (id int, name varchar(20)) ;
注意,在mysql中对表操作前,必须先选择所使用的数据库。
一般语句:
create table 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
...
字段名 字段类型 【约束】
)
例如:创建一个员工表:
create table employee(empno int, ename varchar(20), sal int);
后面表的代码以此例为照应
2.查看表
查看所有的表:
show tables;
查看指定表的创建语句
show create table employee;
注意,mysql表名称区分大小写
显示指定表的结构:
desc employee;
3.修改表
一般的命令
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
举例
更改表名: rename table employee to worker;
增加一个字段:alter table employee add column height double;
(column关键字在Oracle中,添加则语法错误)
修改一个字段:alter table employee modify column height float;
修改字段名: alter table employee change column height height1 float;
删除一个字段:alter table employee drop column height1;
修改表的字符集:alter table employee character set gbk;
4.删除表
删除employee表
drop table employee;
(MySQL中不能使用purge,添加会出现语法错误)
5.复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
(三)常见的约束
- 常见的约束
NOT NULL:非空,该字段的值必填
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段
- 主键和唯一
1、区别:
①、一个表至多有一个主键,但可以有多个唯一
②、主键不允许为空,唯一可以为空
2、相同点
都具有唯一性
都支持组合键,但不推荐 - 外键:
1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
3、主表的被引用列要求是一个key(一般就是主键)
4、插入数据,先插入主表
删除数据,先删除从表
可以通过以下两种方式来删除主表的记录
#方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
注意:
- 支持类型 - 可以起约束名
列级约束 -除了外键 - 不可以
表级约束 -除了非空和默认 -可以,但对主键无效
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
- 修改表时添加或删除约束
1、非空
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
2. 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
或
alter table 表名 modify 字段名 字段类型 DEFAULT NULL;
3、主键
1. 注意:
1. 含义:非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
2. 在创建表时,添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, -- 1.列级主键约束语法
name VARCHAR(20),
PRIMARY KEY(id), -- 2.表级主键约束语法
PRIMARY KEY(id,name), -- 3.表级组合主键约束语法
CONSTRSINT 别名 PRIMARY KEY(id,name) -- 4.表级组合主键约束语法
);
* 如果需要对多个字段建立组合主键约束,则只能使用表级约束语法
3. 删除主键
-- 错误 ALTER TABLE stu MODIFY id INT ;
ALTER TABLE stu DROP PRIMARY KEY;
4. 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY; -- 1.列级语法添加单列主键约束
ALTER TABLE stu ADD PRIMARY KEY(id,name); -- 2.表级语法添加组合主键约束
4、唯一
1. 创建表时,添加唯一约束
CREATE TABLE stu(
id INT UNIQUE, -- 1.列级约束语法
UNIQUE(id), -- 2.表级约束语法
CONSTRSINT 别名 UNIQUE(id) -- 3.表级约束语法起别名
);
* 注意mysql中,唯一约束限定的列的值可以有多个null,创建组合约束
* 只能用表级约束法。这里只是为了阐述问题方便放在了同一个表中,
* 实际只选择其中一种即可,以下的也是如此。
2. 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
3. 在创建表后,添加唯一约束
ALTER TABLE stu MODIFY id INT UNIQUE; -- 1.添加单列约束
ALTER TABLE stu ADD UNIQUE(列名1,列名2); -- 2.添加组合约束
5、外键
1. 在创建表时,可以添加外键
* 语法:
CREATE TABLE 表名(
....
外键列
CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
);
2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
4. 级联操作
* 如果想定义当删除主表记录时,从表记录也会随之删除,则需要在建立外键约束后添加on delete cascade或添加on delete set null,
* 第一种是删除主表记录时,把参照该主表记录的从表记录全部级联删除;
* 第二种是指定当删除主表记录时,把参照该主表记录的从表记录的外键设为null;
* 如果想定义当更新主表记录时,从表记录也会随之更新,则需要定义级联更新on update cascade。
1. 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
2. 分类:
1. 级联更新:ON UPDATE CASCADE 或者 ON UPDATE SET NULL
2. 级联删除:ON DELETE CASCADE
四、自增长列
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
auto_increment_increment
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
删除自增长列
alter table 表 modify column 字段名 字段类型 约束
DML语言
(三)数据的管理
1.数据的插入
方式一
语法:
insert into 表名(字段名,...) values(值,...);
特点:
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
方式二
语法:
insert into 表名 set 字段=值,字段=值,...;
两种方式 的区别:
- 方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,..)】 values(值,..),(值,...),...;
- 方式一支持子查询,语法如下:
insert into 表名
查询语句;
创建一个员工表,新建employee表并向表中添加一些记录:
create table employee(
id int,
name varchar(20),
sex int,
birthday date,
salary double,
entry_date date,
resume text
);
insert into employee values(1,'张三',1,'1983-04-27',15000,'2012-06-24','一个大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-02-22',10000,'2015-07-24','一个中流');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-08-28',7000,'2018-08-24','一个小菜');
2.数据的修改
- 修改单表的记录 ★
语法:update 表名 set 字段=值,字段=值 【where 筛选条件】;
- 修改多表的记录【补充】
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
将所有员工薪水都增加500元。
update employee set salary=salary+500;
将王五的员工薪水修改为10000元,resume改为也是一个中流
update employee set salary=10000, resume='也是一个中流' where name='王五';
3.数据的删除
方式一:使用delete
删除单表的记录★
语法:delete from 表名 【where 筛选条件】【limit 条目数】
级联删除
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二:使用truncate
语法:truncate table 表名
- 两种方式的区别【面试题】
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
删除表中姓名为王五的记录。
delete from employee where name='王五';
【注意from不能省略】
删除表中所有记录。
delete from employee;
使用truncate删除表中记录。
truncate employee;
(无条件 效率高)
DQL语言
基础查询
- 语法
select 查询列表
from 表名;
-
特点
1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表 -
示例
1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名,字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5、查询函数
select 函数名(实参列表);
6、查询表达式
select 100/1234;
7、起别名
as 别名
空格 别名
8、去重
select distinct 字段名 from 表名;
9、+
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;结果都为null
10、concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,...);
11、ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
12、isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
条件查询
- 语法
select 查询列表
from 表名
where 筛选条件
- 筛选条件的分类
1、简单条件运算符
< = <> != >= <= <=>安全等于
2、逻辑运算符
and
or
not
3、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
between and
in
is null /is not null:用于判断null值
is null PK <=>
- 普通类型的数值 - null值 - 可读性
is null - × √ √
<=> -√ √ ×
排序查询
- 语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】
- 特点
1、asc :升序,如果不写默认升序
desc:降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后(除limit语句之外)
常见函数
-
概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表); -
单行函数
1、字符函数
concat:连接
substr:截取子串
upper:变大写
lower:变小写
replace:替换
length:获取字节长度
trim:去前后空格
lpad:左填充
rpad:右填充
instr:获取子串第一次出现的索引
2、数学函数
ceil:向上取整
round:四舍五入
mod:取模
floor:向下取整
truncate:截断
rand:获取随机数,返回0-1之间的小数
3、日期函数
now:返回当前日期+时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月 -
4、其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式 -
5、流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
- 分组函数
1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
2、特点
①语法
select max(字段) from 表名;
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count()和count(1)效率>count(字段)
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段
分组查询
- 语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
-
特点
-使用关键字 - 筛选的表 -位置
分组前筛选 -where -原始表 -group by的前面
分组后筛选 -having -分组后的结果 -group by 的后面
连接查询
- 含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,...;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
- 分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外(mysql不支持)
交叉连接
SQL92语法
1、等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
四、SQL99语法
1、内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接
非等值连接
自连接
2、外连接
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join 左边的就是主表,right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
3、交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
子查询
一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询
二、分类
1、按出现位置
select后面:
仅仅支持标量子查询
from后面:
表子查询
where或having后面:
标量子查询
列子查询
行子查询
exists后面:
标量子查询
列子查询
行子查询
表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
三、示例
where或having后面
1、标量子查询
案例:查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资,要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
2、列子查询
案例:查询所有是领导的员工姓名
①查询所有员工的 manager_id
select manager_id
from employees
②查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
select manager_id
from employees
);
分页查询
一、应用场景
当要查询的条目数太多,一页显示不全
二、语法
select 查询列表
from 表
limit 【offset,】size;
注意:
offset代表的是起始的条目索引,默认从0卡死
size代表的是显示的条目数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
联合查询
一、含义
union:合并、联合,将多次查询结果合并成一个结果
二、语法
查询语句1
union 【all】
查询语句2
union 【all】
...
三、意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致
四、特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,union all包含重复项
TCL语言
事务
- 概念
- 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
- 数据库引擎
- 概念:在mysql中的数据用各种不同的技术存储在文件(或内存)中
- 操作:
- show engines: 查看mysql支持的存储引擎
*MyISAM 与 InnoDB 区别?
- show engines: 查看mysql支持的存储引擎
- 事务的ACID特征
- 原子性(Atomicity):是不可分割的最小操作单位,要么同时成功,要么同时失败
- 一致性(Consistency):保证数据的状态操作前和操作后保持一致
- 隔离性(Isolation):多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰
- 持久性(Durability):当事务提交或回滚后,数据库会持久化的保存数据
- 事务操作步骤
- 开启事务
- 编写事务逻辑操作单元
- 提交事务或回滚事务
- 事务分类
- 隐式事务:没有明显的开启和结束事务的标志
- insert、update、delete语句本身就是一个事务
- 显式事务:具有明显的开启和结束事务的标志
- 取消自动提交事务:set autocommit=0;
- 开启事务:start transaction;
- 提交或回滚事务:
- commit;
- rollback;
- 隐式事务:没有明显的开启和结束事务的标志
- 事务的隔离级别
- 事务并发问题是如何产生的?
- 当多个事务同时操作同一个数据库的相同数据时
- 事务并发问题
- 脏读:一个事务读取到了另外一个事务未提交的数据
- 不可重复读:同一个事务中,多次读取到的数据不一致
- 幻读:一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据
- 处理事务并发问题,设置事务隔离级别
- READ UNCOMMITTED
- READ COMMITTED:可以避免脏读
- REPEATABLE READ:可以避免脏读、不可重复读和一部分幻读
- SERIALIZABLE:可以避免脏读、不可重复读和幻读
- 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- 设置隔离级别
- set session|global transaction isolation level 隔离级别名;
- 查看隔离级别
- select @@tx_isolation;
- 事务并发问题是如何产生的?