删除表内容
delete from 表名 [where 条件];
如要删除整张表里内容
- delete from 表名;(效率较低,若数据多)
- truncate table 表名;(推荐使用)
- 会删除表,并重新创建一个新的表(效率较高,只执行两条)
添加表内容
insert into 表名 (表字段) values (字段值)
修改表内容
update stu set age = 117 where id=3;
update 表名 set 更改内容 where 更改条件
update stu set name = ‘穆星煜’ where id =2;
查询表内容
1.语法:
select
字段列表
from
表名
where
条件列表
groupby
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2.select distinct address from student;
distinct/DISTINCT : 去除重复结果集
如果有null参与的计算,计算结果都为null,用IFNULL(可能有NULL的字段,代替值)
select Name,math,english,math + IFNULL(english,0) from student;
起别名 AS
select Name,math,english,math + IFNULL(english,0) AS 总分 from student;
关键字IN 查询在in()内的
select * from student where age in (22,18,25);
Null不能用=(!=)判断
使用IS
select * from student where english is null
LIKE 关键字
select * from student where name like ‘%德%’;
select * from student where name like ‘___’;
select * from student where name like ‘_化%’;
select * from student where name like ‘马%’;
Order by 关键字
ASC:升序
DESC:降序
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件.
聚合函数
将一列数据作为一个在整体,做纵向的计算
- count:计算个数
- 一般选择非空的列:主键
- count(*)
- max:计算最大值
- min:计算最小值
- sum:计算和
- avg:计算平均值
聚合函数的计算排除null
1.选择不包含非空的列计算
2.ifNULL函数
分组查询
1.语法:group by 分组字段;
2.注意:分组之后查询的字段:分组字段\聚合函数
where和having的区别?
where在分组之前进行限定,如果不满足条件,则不参与分组
having在分组之后进行限定,如果不满足结果,则不会被查询
where后不可以跟聚合函数,having可以进行聚合函数的判断
分页查询
-
语法:limit开始的索引,每页查询的条数
-
公式: 开始的索引=(当前的页码-1)*查询的条数;
select * from student limit 0,3;
select * from student limit 3,3;
select * from student limit 6,3;
-
limit是mysql独有的
约束:
对表中数据进行限定,保证数据的正确性,有效性和完整性
分类:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
4. 外键约束:foreign key
非空约束:
-
创建表示添加约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL --name为非空 );
-
创建表后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
-
删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束
唯一约束可以有NULL值,但是只能有一条记录为NULL值
-
创建表示添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) unique --name为非空 );
-
创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-
删除phone_number的唯一约束
ALTER TABLE stu DROP INDEX phone_number VARCHAR(20);
主键约束
primary key
-
含义:非空且唯一
-
一张表只能有一个字段为主键
-
主键就是表中记录的唯一标识
-
创建表示添加主键约束
CREATE TABLE stu( id INT PRIMARY KEY, --给id添加主键约束 name varchar(20) );
-
创建表后,添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-
删除phone_number的主键约束
ALTER TABLE stu DROP PRIMARY KEY;
-
自动增长:
-
概念:如果某一列是数值类型的,使用auto_increment 可以来完成值得自动增长
-
创建表示添加自动增长
CREATE TABLE stu( id INT PRIMARY KEY auto_increment, --给id添加主键约束 name varchar(20) );
-
创建表后,添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
删除自动增长
ALTER TABLE stu MODIFY id INT;
-
外键约束
foreign key,让表与表产生关系,从而保证数据的正确性
-
在创建表时,可以添加外键
语法: create table 表名( .... 外键列, constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) )
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-
创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名称(主表列名称) ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
-
级联操作
级联更新与级联删除
-
添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
-
分类:
- 级联更新: ON UPDATE CASCADE
- 级联删除: ON DELETE CASCADE
-
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
–数据有冗余
–表的拆分
±—±-----±----±---------±-------------+
| id | NAME | age | dep_name | dep_location |
±—±-----±----±---------±-------------+
| 1 | 张三 | 20 | 研发部 | 广州 |
| 2 | 李四 | 21 | 研发部 | 广州 |
| 3 | 王五 | 20 | 研发部 | 广州 |
| 4 | 老王 | 20 | 销售部 | 深圳 |
| 5 | 大王 | 22 | 销售部 | 深圳 |
| 6 | 小王 | 18 | 销售部 | 深圳 |
±—±-----±----±---------±-------------+
– 解决方案:分成2张表
–创建部门表(id,dep_name,dep_location)
–一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
–创建员工表(id,name,age,dep_id)
–多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
–添加2个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
SELECT * FROM department;
–添加员工,dep_id表示员工所在的部门
INSERT INTO employee(NAME,age,dep_id) VALUES ('张三',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('李四',21,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('王五',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('老王',20,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('大王',22,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('小王',18,2);
数据库的设计
-
多表之间的关系
- 分类
-
1对1:
- 如:人和身份证
- 分析:一个人只有一个身份证,一个身份证只能对应一个人
-
1对多(多对1)
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只能对应一个部门
-
多对多
-
如:学生和课程
-
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
-
- 实现关系:
- 一对多(多对一)
- 如:部门和员工
- 实现方式:在多的一方建立外键,指向一的一方的主键
- 多对多
- 如:学生和课程
- 实现方式:多对多关系实现需要借助第三章中间表.中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的外键
- 一对一
- 如:人和身份证
- 实现方式:一对一关系,可以在任意一方添加唯一外键指向另一方的主键
- 一对多(多对一)
-
数据库设计的范式
- 概念:设计数据库时,需要遵循的一些规范
- 分类:
- 第一范式:每一列都是不可分割的原子数据源
- 第二范式:在第一范式的基础上,非码属性必须完全依赖于候选码
- 第三范式:在第二范式基础上,任何非主属性不依赖于其他非主属性
数据库的备份和还原
-
命令行:
- 备份:mysqldump -u用户名 -p密码 备份数据库名称> 保存的路径
- 还原:
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件: source 文件路径
-
图形化工具
多表查询
准备sql
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender CHAR(1),
salary DOUBLE,
join_date DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('蜘蛛精','女',4500,'2011-03-14',1);
多表查询分类
-
内连接查询
-
隐式内连接:使用where条件消除无用数据
SELECT t1.name, ---员工表的姓名 t1.gender, ---员工表的性别 t2.name ---部门表的名称 FROM emp t1, dept t2 WHERE t1.dept_id = t2.id;
-
显示内连接:
- 语法: select 字段列表 from 表名1 [ inner] join 表名2 on 条件
- 例如:
- SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
- SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
-
内连接查询:
- 从那些表中查询数据
- 条件是什么
- 查询哪些字段
-
-
外连接查询
- 左外连接:
- 语法: select 字段列表 from 表1 left [outer] join 表2 on 条件;
- 查询的是左表所有数据以及其交集部分
- 右外连接:
- 语法: select 字段列表 from 表1 right[outer] join 表2 on 条件;
- 查询的是右表所有数据以及其交集部分
- 左外连接:
-
子查询
-
概念:查询中嵌套查询,称嵌套查询为子查询
–查询工资最高的员工信息
-
查询最高工资是多少(9000)
select max(salary) from emp;
-
查询员工信息,并且工资等于9000
select * from emp where emp.salary = 9000;
-
一条sql就完成这个操作
select * from emp where emp.salary = (select max(salary) from emp);
-
-
子查询不同情况
-
子查询的结果是单行单列的:
-
子查询可以作为条件,使用运算符去判断.运算符: > >= = <= <
-
查询员工工资小于平均工资的人:
select * from emp where emp.salary < (select avg(salary) from emp)
-
-
子查询的结果是多行单列的:
-
子查询可以作为条件,使用运算符in来判断
-
查询’财务部和市场部’所有员工的信息
select id from dept where dept.name ='财务部' or dept.name = '市场部';
select * from emp where dept_id in (3,2);
select * from emp where dept_id in (select id from dept where dept.name = '财务部' or dept.name = '市场部');
-
-
子查询的结果是多行多列的:
-
子查询可以作为一张虚拟表参与查询
-
查询员工的入职日期是2011-11-11日之后的员工信息和部门
-
子查询
select * from dept t1,(select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id;
-
普通内查询
select * from emp t1,dept t2 where t1.join_date > '2011-11-11' and t1.dept_id = t2.id;
-
-
CREATE TABLE dept( id INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'学工部','上海'), (30,'销售部','广州'), (40,'财务部','深圳'); CREATE TABLE job( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) ); INSERT INTO job(id,jname,description) VALUES (1,'董事长','管理整个公司,接单'), (2,'经理','管理部门员工'), (3,'销售员','向客户推销产品'), (4,'文员','使用办公软件'); CREATE TABLE emp( id INT PRIMARY KEY, ename VARCHAR(50), job_id INT, mgr INT, joindate DATE, salary DECIMAL(7,2), bonus DECIMAL(7,2), dept_id INT, CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job(id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id) ); INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龙',4,1004,'2000-12-17','30000.00',NULL,20), (1014,'关于',4,1007,'2002-01-23','13000.00',NULL,10); CREATE TABLE salarygrade( grade INT PRIMARY KEY, losalary INT, hisalary INT ); INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);
-
-
查询所有员工信息.查询员工编号,员工姓名,工资,职务名称,职务描述
select t1.id, t1.ename, t1.salary, t2.jname, t2.description from emp t1,job t2 where t1.job_id = t2.id;
-
查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
select t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc from emp t1,job t2,dept t3 where t1.job_id = t2.id and t1.dept_id=t3.id;
-
查询员工姓名,工资,工资等级
select t1.ename,t1.salary,t2.grade from emp t1,salarygrade t2 where t1.salary between t2.losalary and t2.hisalary;
-
查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
select t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade from emp t1,job t2,dept t3,salarygrade t4 where t1.job_id = t2.id and t1.dept_id = t3.id and (t1.salary between t4.losalary and t4.hisalary);
-
查询出部门编号,部门名称,部门位置,部门人数
select t1.id,t1.dname,t1.loc,t2.total from dept t1, (select dept_id,count(id) total from emp group by dept_id) t2 where t1.id = t2.dept_id;
-
查询所有员工的姓名以及直接上级的姓名,没有领导的员工也需要查询
select t1.ename, t1.mgr, t2.id, t2.ename from emp t1 left join emp t2 on t1.mgr = t2.id;
事务
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
--添加数据
INSERT INTO account (NAME,balance) VALUES ('张三',1000),('李四',1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
--张三给李四转账500元
--0.开启事务
START TRANSACTION;
--1.张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
--2.李四账户 +500
出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
--发现执行没有问题,提交事务
COMMIT;
--发现出问题了,回滚数据
ROLLBACK;
-
事务的基本概念
- 概念:
- 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败.
- 开启事务:start transaction;
- 回滚:rollback;
- 提交:commit;
- Mysql数据库中事务默认自动提交
- 事务提交的两种方式:
- 自动提交:
- mysql就是自动提交的
- 一条DML(增删改)语句会自动提交一次事务
- 手动提交:
- Oracle数据库默认是手动提交事务
- 需要先开启事务,在提交
- 自动提交:
- 修改事务的默认提交方式
- 查看事务的默认提交方式: SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
- 修改默认提交方式: set @@autocommit = 0;
- 事务提交的两种方式:
- 概念:
-
事务的四大特征(重要面试)
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
- 持久性:当事务提交或回滚后,数据库会持久化的保存数据
- 隔离性:多个事务之间,相互独立
- 一致性:事务操作前后,数据总量不变
-
事务的隔离级别
-
概念:多个事务之间隔离的,相互独立的.但是如果多个事务操作同一批数据,则会引发一些问题,设备不同的隔离级别就可以解决这些问题
-
存在问题
- 脏读:一个事务,读取到另一个事务中没有提交的数据
- 不可重复读(虚度):在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
-
隔离级别
- read uncommitted:读未提交
- 产生的问题:脏读,不可重复读,幻读
- read committed:读已提交(Oracle)
- 产生的问题:不可重复读,幻读
- repetable read:可重复读(MySQL默认)
- 产生的问题:幻读
- serializable:串行化
- 可以解决所有的问题
- 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- 数据库查询隔离级别:
- select @@tx_isolation;
- 数据库设置隔离级别:
- set global transaction isolation level 级别字符串;
- read uncommitted:读未提交
-
DCL:
-
SQL分类
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
-
DCL:管理用户,收取那
-
管理用户
-
添加用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
删除用户:
DROP USER '用户名'@'主机名'
-
修改用户密码:
--修改用户密码 UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); * mysql中忘记了root密码怎么办? 1.cmd --> net stop mysql 停止mysql服务 * 需要管理员运行cmd 2.使用无验证方式启动mysql服务: mysql --skip-grant-tables 3.打开新的cmd窗口,直接输入mysql命令,就可以登录成功 4.use mysql; 5.update user set password = password('新密码') where user = 'root'; 6.关闭两个窗口 7.打开任务管理器,手动结束mysqld.exe的进程 8.启动mysql服务 9.使用新密码登录
-
查询用户:
--1.切换到mysql数据库 USE mysql; --2.查询user表 SELECT * FROM USER; * 通配符: % 表示可以在任意主机使用用户登录数据库
-
-
权限管理
-
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-
授予权限
--授予权限 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; --授予全部权限 GRANT ALL ON '*' TO '用户名'@'主机名';
-
权限撤销
--撤销权限 REVOKE 权限列表 ON 数据库名.表名 from '用户名'@'主机名';
-
-