Mysql数据库的DDl，DQL命令，数据库约束，事务...
Mysql数据库的DDl，DQL命令，数据库约束,Mysql事务总结
·
MySQL基础
Day1MySQL命令(DDl语句)
打开dos窗口,
输入: mysql -uroot -p
提示输入密码:*****DDL语句
查询当前服务器中所有数据库:show databases;
新建数据库:create database 新建数据库名称;
create databses if not exists 新数据库名称;
查看创建的数据库默认字符集: show create database 数据库名称;
修改数据库默认字符集: alter database 数据库名称 default(省略) character set gbk
删除数据库:drop database if exists 数据库名称;
drop database 数据库名称;
切换数据库: use 数据库名称;
查询当前数据库中所有的表:show tables;
创建表: create table 表名(
-> 字段名称1 字段类型1
-> 字段名称2 字段类型2
-> ....
-> );
mysql常见字段类型:
int:整数类型,默认11位;
varchar(指定长度):Java中的String,字符串类型,使用''/""包起来,也可以不带引号
name varchae(20),name字段最大支持20个字符
date:日期,只有日期,没有准确时间
datetime :日期+当前时间
timestap:时间戳(被访问时的具体时间)
double(5,3):小数类型,五位数据,小数点后可以跟三位
double:小数类型
查询表的结构:desc 表名;
给表中添加一列:alter table 表明 add 字段 字段类型;
修改表的字段: alter table 表明 change 以前的字段 修改后的字段 以前的字段类型;
修改表的字段类型:alter table 表明 modify 需要修改的字段 修改的字段类型;
修改表的名称:alter table 表明 rename to 新表名;
复制表:create table 新表名 like 要复制的表名;
删除表: drop table if exists 表名;
drop table表名;Day2MySQL注释
-- 普通注释
# 引用注释
查看MySQL服务端所有的字符编码集 show variables like '%character%';MySQL数据库操作语句
给表中插入数据:
方式1)
INSERT INTO `表名 VALUES(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学号 (主键,并且自增长),
字段名称1,
字段名称2,
字段名称3,.....);
方式2)
INSERT INTO 表名 (字段名称1,字段名称2,字段名称3,.....)VALUES(字段1,字段2,....),
(字段1,字段2,....),
(字段1,字段2,....);
插入数据时保证插入值与字段类型一致,否则会出现问题修改
update语句:修改
UPDATE 表名 SET 字段 = 数据 WHERE xx = xx ;
例: UPDATE 表名 SET NAME = '貂蝉' WHERE id = 1;
将id为1的人姓名改为'貂蝉'
UPDATE 表名 SET NAME = '大桥',address = '北京' WHERE NAME = '貂蝉';
将姓名为'貂蝉'的人改名为'大桥',地址改为'北京'
UPDATE 表名 SET address = '北京';
将所有人员地址改为北京删除
带条件来删除 delete from 表名 where ...
DELETE FROM 表名 WHERE address = '北京';
将地址为'北京'的删除
DELETE FROM 表名;
删除表中所有数据,字段还在
DROP TABLE 表名;
删除表格(删除所有,数据字段全删)
批量删除:带条件来删除 delete from 表名 where ... ltmit 要删除的数量
DELETE FROM stu WHERE id = 9 LIMIT 5;
-- 将id为9的重复用户删除5行,DELETE FROM, TRUNCATE TABLE区别
delete from 表名:
TRUNCATE TABLE 表名;
delete from 表名:只是删除表中的全部数据,那么当前表的结构还存在,针对自增长主键id,下次
从之前id值继续自增;
TRUNCATE TABLE 表名; 会将当前表的全部数据以及表都删除掉,
并且自动创建一张一模一样的表,直接影响自增长主键,从0继续开始自增! 查询数据
DQL语句: 数据库查询语句
基本查询
带where条件查询
模糊查询
聚合查询
分组查询
筛选查询
分页查询
-- 查询所有数据
SELECT
s.id'学号',
s.name'姓名',
s.age'年龄',
s.address'地址',
s.gender'性别',
s.day'日期'
FROM
表名 s; -- 起一个别名
SELECT * FROM 表名;基本查询
查询学生的(id,NAME,age,address)
SELECT
id,
NAME,
age,
address
FROM
stu;
去除重复字段,查询时前面加上DISTINCT
SELECT
DISTINCT address
FROM
stu;
查询学生年龄,姓名,math+english总分
SELECT
NAME,
age,
(math+english)
FROM
stu;
当有学生一门成绩为null时,总分也会变成null
使用mysql自带的函数ifnull(字段,期望值)
SELECT
NAME,
age,
(math+IFNULL(english,0)) -- 如果english为null,就把它看作0来计算.
FROM
stu;
查询字段时可以给字段起别名,as(as可以省略)
SELECT
NAME AS'姓名',
age AS'年龄',
(math+IFNULL(english,0)) AS '总分'
FROM
stu;
-- 字段进行求和,求差时类型必须一致,否则出现警告.
查询姓名为张三的学生信息
SELECT
*
FROM
stu
WHERE
NAME = '张三';
查询年龄小于20的学生信息
SELECT
*
FROM
stu
WHERE
age <= 20 ;
-- age < 20 OR age = 20; -- 两种写法都可以
age >= 20 && age <= 30; --查询大于等于20,小于等于30的学生信息
and :表示并列条件(连接多个条件:推荐使用and)
age >=20 AND age <=30 ;
-- 介于某个值和另一个值之间: between and ...
age BETWEEN 20 AND 30 ; -- 查询 20-30之间(包含20,30)
age != 20 ; -- 查询年龄不等于20的学生
age <> 20 ; -- 判断某个字段不等于<>
age = 18
OR -- ||
age = 20
OR -- ||
age = 30 ;
查询年龄是18或者20或者30的学生信息,使用OR/||都可以
查询的时候带where条件 某个字段名称 in(值1,值2,值3...)
age IN(18,20,30,55) ;
english IS NULL ; -- 查询某个字段为null的学生信息
english IS NOT NULL ; -- 查询字段不为null的信息DQL语句的模糊查询
select 字段列表 from 表名 字段名称 like(关键字) '%xx%'
%:表示可以匹配的任意的多个字符 一般都是 '%字符值%':包含指定的字符的信息 模糊查询出来
_:表示代替一个字符: 模糊查询 名字为三个字符的人 '___'SELECT
*
FROM
student
WHERE
NAME
LIKE
'%三%' ; -- 查询姓名第一个是三的学生
'%_化%' ; -- 查询第二个字为化的学生
'___' ; -- 查询姓名为三个字的学生
DQL语句聚合函数查询
-- count(字段名称):查询总记录数
-- avg(字段名称):查询当前字段的平均值
-- max(字段名称):查询当前字段的最大值
-- min(字段名称):查询当前字段的最小值
-- sum(字段名称):当前字段的求和值
count(字段):字段名称必须非null
count(字段):字段名称都使用的非业务字段,一般情况:都是表中id字段(主键自增长的字段)
SELECT
COUNT(NAME) -- name不为null
FROM
stu ; -- 查询表中name不为null的数量
SELECT
COUNT(IFNULL(english,0)) -- ifnull(字段名称,期望值)
FROM
stu; -- 如果学生english为null将null看作0来计数
-- 查询当前学生的数学平均分
-- 聚合函数查询出来的结果:都是单行单列
SELECT
AVG(math) '数学平均分'
FROM
stu ; -- 求平均值
SELECT
SUM(IFNULL(english,0)) '英语总成绩'
FROM
stu ; -- 求总和,如果表中有null,那么将null视为0参与运算
SELECT
MAX(math)
FROM
stu ; -- 求最大值DQL之 排序查询
select 字段列表 from 表名 order by 字段名称 排序方式
排序方式:asc :升序(默认值) desc :降序
SELECT
*
FROM
stu
ORDER BY
math ; -- 省略了ACS 因为默认升序
ORDER BY IFNULL(english,0) DESC ; -- 降序
SELECT
*
FROM
stu
ORDER BY
math ASC,
IFNULL(english,0) DESC;
-- 查询学生所有数据,math进行升序,english倒叙,如果math与english相同则按照english的降序进行排序分组查询:关键字:group by
分组查询1)可以查询分组的字段
2)查询聚合函数
select 字段列表 ,聚合函数 from 表名 group by 分组的字段;
注意:group by 后面不能跟聚合函数,否则语法通过不了
-- 分组查询:group by 带条件查询,必须先使用条件,然后在参与分组
-- where 语句必须放置在group by之前;
SELECT
sex, --查询分组字段
AVG(math) -- 查询聚合函数
FROM
stu
GROUP BY
sex; -- 查询男女math的平均数
SELECT
sex,
AVG(math),
COUNT(id)
FROM
stu
GROUP BY
sex; -- 查询男女的math平均数及总id数筛选:having
筛选后面可以使用聚合函数,而且如果sql语句有where条件,有group by ,有having
where必须置于group by之前,
group by 必须置于having 之前!SELECT
sex,
AVG(math),
COUNT(id)
FROM
stu
WHERE
math>70
GROUP BY
sex
HAVING
COUNT(id)>2; -- 按性别分组,查询男女平均的总人数,小于70不分组,筛选出总人数大于2的信息
SELECT
sex 性别,
AVG(math) 数学平均分,
COUNT(id) 总人数
FROM
stu
WHERE
math > 70 -- 求大于70的人
GROUP BY
sex -- 分组
HAVING
总人数 > 2 ; -- 同上一致分页查询
limit 起始行数,每页显示的条数;
select 字段列表 from 表名 limt 起始行数,每页显示的条数;
起始行数是从0开始算: 每页显示2
后台:分页的逻辑: 起始行数=(必须知道当前页码-1)*每页显示的条数SELECT
*
FROM
stu
LIMIT 0,2; -- 分页查询,查询2条,从0开始算
SELECT
*
FROM
stu
LIMIT 2,2; -- 第二页 起始行数=(2-1)*每页条数2
LIMIT 4,2; -- 第三页 起始行数=(3-1)*每页条数2Day3数据库约束
约束用户操作表的行为,针对一些字段设置用户限制默认约束: default
在创建表时,给定某个字段名称添加default后添加'默认值'.
例:gender VARCHAR(3) DEFAULT '男'
INSERT INTO emp(id,NAME) VALUES(1,'张飞');
-- 字段名称后不添加默认值表中的值为null,添加后为'男'非空约束
not null (限制用户直接插入null值),null是非法数据
添加非空约束
1)直接在字段名称后添加 NOT NULL
例:NAME VARCHAR(5) NOT NULL,
2)创建后添加 INSERT INTO 表名 MODIFY 字段名称 字段类型 NO NULL
例: ALTER TABLE 表名 MODIFY NAME VARCHAR(5) NO NULL;
删除非空约束
ALTER TABLE 表名 MODIFY 字段名称 字段类型;
例:ALTER TABLE 表名 MODIFY NAME VARCHAR(5) ;唯一约束
unique (限定某个字段的不能重复,必须唯一的)
添加唯一约束
字段名称后添加
例:id INT UNIQUE,
2)表内添加ALTER TABLE 表名 MODIFY 字段 类型 UNIQUE ;
ALTER TABLE 表名 MODIFY 字段 类型 UNIQUE ;
取消唯一约束
atler table 表名 drop index 唯一约束所作用的字段名称
ALTER TABLE 表名 DROP INDEX id ;主键约束
主键约束:primary key
特点:非空+唯一
定义主键约束:
1)创建表时添加: id INT PRIMARY KEY, -- 添加后id不能为空,也不可重复.
2)后期添加: ALTER TABLE 表名 MODIFY 字段 类型 PRIMARY KEY ;
ALTER TABLE emp MODIFY id INT PRIMARY KEY ;
-- 如果已经有字段重复,添加时会报错 -- Multiple primary key defined
删除主键约束
-- alter table 表名 drop PRIMARY KEY ;
ALTER TABLE emp DROP PRIMARY KEY ; -- 将唯一约束删除掉自增长约束
自增长约束:auto_increment和主键一块使用,
一般用于非业务字段,(默认从0开始,插入数据后自增长主键值会依次自增1)
id INT PRIMARY KEY AUTO_INCREMENT,
-- LAST_INSERT_ID() mysql的函数
SELECT LAST_INSERT_ID() ;-- 查询当前表中最后一次插入的自增主键的id值外键约束
foreign key
删除外键名称
ALTER TABLE employee DROP FOREIGN KEY fk_dept_emp ;
添加外键
ALTER TABLE employee
ADD
CONSTRAINT
fk_dept_emp
FOREIGN KEY
(dept_id)
REFERENCES
dept(id) ;
-- 添加外键约束,为了保证多表查询不越界
CREATE TABLE dept( -- 创建一个部门
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
INSERT INTO dept(NAME)VALUES('开发部'),('测试部'),('品质部'); --部门编号
SELECT * FROM dept;
CREATE TABLE emp( --创建员工表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(5),
age INT,
gendet VARCHAR(2),
dept_id INT,
CONSTRAINT -- 声名一个外键约束
fk_dept_empt -- 外键名称
FOREIGN KEY (dept_id) -- 作用在指定字段上
REFERENCES -- 关联
dept(id) -- 主表主键id
);
INSERT INTO emp (NAME,age,gendet,dept_id)VALUES('张三',23,'男',1),
('刘备',26,'女',2),
('张飞',28,'男',3),
('关羽',31,'女',3),
('孙策',34,'男',2),
('曹操',56,'女',1);
-- 有外键的表,删除修改数据时较为麻烦
-- CASCADE:级联操作
-- on update cascade 级联修改
-- 当修改主表字段,那么跟从表相关的信息一会被更改
-- on delete cascade 级联删除
-- 添加外键的同时--添加级联修改和级联删除
ALTER TABLE employee
ADD
CONSTRAINT
fk_dept_emp
FOREIGN KEY
(dept_id)
REFERENCES
dept(id)
ON UPDATE CASCADE -- 添加级联修改
ON DELETE CASCADE ; -- 添加级联删除数据库备份
进入dos,输入:mysqldump -uroot -p密码 数据库 > 路径
数据库还原
1)通过dos登录mysql
2)删除原有的数据库
3)`创建空数据库
4) source 指定之前备份的路径的sql文件
例:source D:\day29\code\my.sql
方式2)图形界面化:直观简单
sqlYog /navicat
备份:鼠标选择指定数据库,右键--->备份--->以sql文件转存数据库---指定路径
还原:
1)删除当前数据库
2)创建新的数据库
3)选择新建的数据库名---右键--->导入--->以sql转储文件数据库数据库范式
数据库范式:就是设计关系型数据库使用的一种规范要求!
常用的范式:三大范式(总共有五种)
1NF:第一范式
在设计关系性数据库时,最低要求必须遵循1NF,表中的每一项时不可拆分的(独立的原子项)
2NF:第二范式
在1NF的基础上,非主键字段(属性字段)必须完全依赖于主键字段
3NF:第三范式
在第二范式的基础上,非主键字段(主键和非主键)之间不能产生依赖!
A字段-->B字段-->C字段
A字段-->c字段(主键依赖)多表查询
多表查询:多个表查询相关字段
多表查询需要满足一定的条件
内连接
笛卡尔乘积(A表的记录*B表记录) = 结果集数据(没有给顶条件设置导致笛卡尔乘积)
内连接之显示内连接:通过where 设置条件
内连接:
1)查询哪个表 查询dept 和emp
2)查询这个表的哪个字段 部门表的部门名称 和员工的所有数据
3)这些表有没有关系条件 员工表的deptid=部门表id
显示内连接:
SELECT
e.*, -- 查询emp表中所有字段
d.id, -- 查询 dept 字段的id
d.name -- 查询 dept 字段的name
FROM
emp e,
dept d
WHERE -- 物理连接 通过条件语句进行连接
e.dept_id = d.id;
隐式内连接:
关键字;inner(省略) join on:作为多表的查询条件(连接条件)
SELECT
e.*,
d.name
FROM
emp e
INNER JOIN --inner join 逻辑连接
dept d
ON
e.dept_id = d.id;
隐式内连接和显示内连接:内连接查询
优先使用显示内连接:where语句是(mysql)独有的,然后使用隐式内连接子查询
多行查询使用in on
-- select语句嵌套select语句
需求:查询最高工资的员工信息
-- 1)查询最高工资是多少
-- select max(salary) from employee ;-- 18000
-- 2)是18000的工资员工信息
-- select * from employee where salary = 18000 ;
SELECT -- 嵌套后
*
FROM
employee
WHERE
salary = (SELECT MAX(salary) FROM employee ) ;
子查询3）
将某个查询的结果集当做一张虚表 ,然后使用这个虚表和另外的其他表建立连接查询
1)查询大于2011-9-10的员工
SELECT
*
FROM
employee
WHERE
join_date > '2011-09-10' ;
2)查询的结果集当做虚表,给它起别名和部门表建立连接查询
SELECT
t1.*,
t2.name '部门'
FROM
(SELECT
*
FROM
employee
WHERE
join_date > '2011-09-10') t1
LEFT OUTER JOIN
dept t2
ON
t1.dept_id = t2.id ;
-- 使用传统内连接:显示内连接 多去使用where条件查询(sql优化的一种)
SELECT
t1.name '员工名',
t1.gender '性别',
t1.salary '工资',
t1.join_date '入职时间',
t2.name '部门'
FROM
employee t1,
dept t2
WHERE
t1.join_date > '2011-09-10'
AND
t1.dept_id = t2.id ; 事务
事务:将整个事务看成一个整体,要么同时成功,要么同时失败
start transaction ; 开启事务
如果执行过程中不存在问题,提交事务:commit;
如果执行过程中有问题,回滚事务:rollback;(回滚到事务开启前的状态)
select @@autocommit
@@在MySQL中是一个全局变量:查看的事务提交状态码:默认为1(自动提交)
可以通过 set @@autocommit = 0 ; 取消自动提交什么时候时使用Transaction管理
当一个业务需要频繁操作sql(两个及以上的增删改)
这个时候使用事务管理 防止多个sql执行过程中存在异常,导致和预期不同
事务的特性
原子性:每一个事务时独立的,不可拆分,事务的执行过程中多个sql要么同时成功,要么同时失败
一致性:事务执行前和执行后总量保持不变
隔离性:事务与事务之间是独立的(业务与业务之间不影响)
持久性:事务针对sql的执行是永久性的(事务一旦执行成功,对数据库修改时持久的,就算关机也会保存下来)事务的隔离等级
隔离等级不同,引发的安全问题不同
脏读:一个事务读取到另一个没有提交的事务
读未提交read uncommitted:不能防止脏读(安全性最差)
读已提交read committed :有效防止脏读,但不可不可重复读防止不了(两次读取内容不一致)
可重复读repeatable read (mysql级别)
串行化 serializable
select @@tx_isolation;-- 查看事务的隔离级别
set global transaction isolation level 级别名称;设置级别
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)