MYSQL中的DDL、DML、DCL、DQL的区别
SQL:结构化查询语句,是一种特殊的编程语言,用来存储以及查询更新和管理数据库系统
DDL:数据定义语言,用来定义数据库对象(库、表、列),负责数据结构定义
常用create—创建 \alter—修改 \drop—删除
DDL所站的高度,他不会对具体的数据进行操作。
DML:数据操作语言,用来定义数据库记录(数据),数据增删改的操作
常用select—查询 \insert—添加 \update—更新 \delete—删除
DCL:数据控制语言,用来定义访问权限和安全级别
1.访问权限grant授权或者revoke取消授权
2.revoke - 收回已经授予的权限
3.commit - 提交
4.savepoint - 保存点
5.rollback - 回滚
6.set transaction - 设置当前事务的特性,它对后面的事务没有影响.
DQL:数据查询语言,用来查询记录(数据)
select数据检索语句
增:
建库建表
create database mydata; #创建名为mydata的库
create table Information( #建表,表名为Information
Zhanghao varchar(20) not null , #表的第一列数据为Zhanghao,字符不超过20
Mima varchar(20) not null #表的第二列数据为Mima,字符不超过20
)comment = '账号数据'; #表名备注‘账号数据’
char和varchar:
name = “张三”
name char (10) 定长字符串 10个字符空间 存储的性能高 浪费空间 空间换时间
name varchar(10) 变长字符串 2个字符空间 存储的性能低 节约空间 时间换空间
data 年月日
表中写入数据
insert into 表名(字段名1,字段名2,...) values(值1,值2,...);
# 第二种方式:按照表中所有字段进行插入数据,一定要与字段在表中定义的顺序一致
insert into 表名 values(值1,值2,...);
#批量添加数据
insert into 表名(字段名1,字段名2,...) values(值1,值2,...),(值1,值2,...)...;
insert into 表名 values (值1,值2,...),(值1,值2,...)...;
创建新的列(字段):
alter table information add time varchar(20) not null ; #在表information中增加time这一字段(列)
添加主键
alter table liushui add primary key (time1); #在表liushui中将time1设为主键
删:
删除mysql表中某一行(记录)数据:
delete from information where Zhanghao = 'x'; #删除表information的Zhanghao这一列中值为x的那一行
删除字段:
alter table information drop asd; #删除表information中asd字段(列)
删除主键
alter table liushui drop primary key ; #删除表liushui中的主键
删除表
drop table 表名
改:
修改表名
alter table 表名 rename to 新的表名;
修改数据类型
alter table 表名 modify 字段名(列名) 新的数据类型
修改字段名和数据结构
alter table 表名 change 字段名 新字段名 新的数据类型
查:
读取数据库数据
show databases; #查看所有数据库
use (数据库名); #进入到某个数据库中
show tables; #查看进入到的数据库中所有表名
查看mysql一个表中所有数据:
select *from Information; #查看表information中所有数据
查询表结构
desc 表名称;
查询当前数据库中所有表名称
show tables;
查询多个字段
select 字段列表 from 表名;
select * from 表名; #查询所有数据
select distinct 字段名 from 表名;#去除重复数据
select 字段名 as 字段别名 from 表名;#将字段取别名显示 as可以省略,但要至少留一个空格
条件查询
select 字段列表 from 表名 where 条件列表;
与:不建议使用&&,用and,也可between and
日期类型也能用between and
等于不能使用==,使用 = 就行;不等于就是 !=,也可用<>
或:||、or、in()
查询为null的数据:null值不能使用=、!=。需要使用is、is not
模糊查询(重要)
符号like
select *from 表名 where name like '马%';#查询name中第一个字为‘马’的数据
select *from 表名 where name like '_花%';#查询name中第二个字为‘花’的数据
select *from 表名 where name like '%德%';#查询name中包含‘德’字德数据
排序查询
select 字段列表 from 表名 order by 排序字段名1 排序方式1,... #若不写排序方式,则为默认的升序排序
排序方式:ASC:升序(默认值)
DESC:降序
注:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序(例如:如果数学成绩一样,则按照英语成绩排序)
分组查询
聚合函数
函数名 功能
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
聚合函数语法:
select 聚合函数名(列名) from 表名;#列里内容不能有null 推荐写*
注:null值不参与所有聚合函数运算
分组查询
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤]
注:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无意义
select sex,ave(math) from stu group by sex;#查询男同学和女同学各自的数学平均分
select sex,ave(math),count(*) from stu group by sex;#查询男同学和女同学各自的数学平均分以及人数
select sex,ave(math),count(*) from stu where math>70 group by sex;#查询男同学和女同学各自的数学平均分以及人数、要求分数低于70分的不参与分组
select sex,ave(math),count(*) from stu where math>70 group by sex having count(*)>2;#查询男同学和女同学各自的数学平均分以及人数、要求分数低于70分的不参与分组、分组后人数大于2人
where和having区别:
执行时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
可判断的条件不一样:where不能对聚合函数进行判断,having可以
执行顺序:where>聚合函数>having
分页查询
select 字段列表 from 表名 limit 起始索引,查询条目数;
索引从0开始
计算公式:起始索引=(当前页码-1)*每页显示的条数
tips:
分页查询limit是mysql数据库的方言
oracle分页查询使用rownumber
sql server分页查询使用top
约束
概念:
约束是作用于表中列上的规则,用于限制加入表的数据
约束的存在保证了数据库中数据的正确性、有效性和完整性
分类:
名称 描述 关键字
非空约束 保证列中数据不能有null值 not null
唯一约束 保证列中所有数据各不相同 unique
主键约束 主键是一行数据的唯一标识,非空且唯一 primary key
检查约束 保证列中的值满足某一条件 check
默认约束 保存数据是,未指定值则采用默认值 default
外键约束 外键用来让两个表的数据之间建立链接,保证数据的一致性和完整性 foreign key
tips:mysql不支持检查约束
create table emp(
id int primary key auto_increment, -- 员工id,主键且自增长
ename varchar(50) not null unique, -- 员工姓名,非空且唯一
joindata data notnull, -- 入职日期,非空
salary double(7,2), -- 工资,非空
bonus double(7,2) default 0 -- 奖金,如果没有奖金默认为0
);
外键约束
让两个表的数据之间建立链接,保证数据的一致性和完整性
create table 表名(
列名 数据类型;
....
constraint 外键名称 foreign key(外键列名) references 主表名(主表列名)
);
-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名(主表列名称);
-- 删除约束
alter table 表名 drop foreign key 外键名称;
tips
- 在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
- 用外键要适当,不能过分追求。
- 不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。
多表查询
内连接
-- 隐式内连接
select 字段列表 from 表1,表2.... where 条件;
-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;
#内连接相当于查询表A,表B交集数据
外连接
-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 右外连接
select 字段列表 from 表2 right [outer] join 表2 on 条件;
左外连接:相当于查询A表所有数据和交集部分数据
右外连接:相当于查询B表所有数据和交集部分数据
子查询
-- 单行单列:作为条件值,使用=、!=、>、<、等进行条件判断
select 字段列表 from 表 where 字段名 = (子查询);
-- 多行单列:作为条件值,使用in等关键字进行条件判断
select 字段列表 from 表 where 字段名 in (子查询);
-- 多行多列:作为虚拟表
select 字段列表 from (子查询) where 条件;
事务
简介:
1.数据库的事务(transaction)是一种机制、一个操作序列,包含了一组数据库操作命令
2.事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
3.事务是一个不可分割的工作逻辑单元
-- 开起事务 例如:转账时,扣除金额后与增加目标用户金额中间发生报错
start transaction; #就会将数据回滚至扣除金额之前,保证数据不会因为报错
或者 begin; #而丢失
-- 提交事务
commit; #没有报错提交后数据发生持久化改变
-- 回滚事务
rollback; #在上述过程中遇到报错数据会回滚至开起事务之前
事务的四大特征
1.原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
2.一直性(Consistency):事务完成时,必须使所有的数据都保持一直状态
3.隔离性(Isolation):多个事务之间,操作的可见性
4.持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
遇到的问题
mysql 5.7插入中文数据:
在建表时最后加上character set = utf8
如:
create table user(
id int(20) primary key not null ,
name varchar(30) not null ,
pwd varchar(30) not null
)character set = utf8;
防止sql注入
select * from books where bookName like '%/' #{bookName} '/%';
/*防止sql注入,之前在输入框输入%或_会查询出所有数据*/