文章目录
- mysql数据库官方文档
- 三类完整性约束
- 关系代数
- SQL分类
- mysql数据类型
- 查询数据
- 连接查询
- 套嵌查询
- 集合查询
- 基于派生表的查询
- 插入数据
- 创建基本表
- 创建临时表
- 复制表
- 删除表
- 修改基本表 alter
- update
- delete
- 视图
- 索引
- 事务
- 用户管理及授权
- mysql函数
- mysql正则表达式
- 过程化sql
- 触发器
- 存储过程
- 存储函数
- 数据库的设计
- mysql8.0新特性CET
- 附录
mysql数据库官方文档
三类完整性约束
- 实体完整性,主码唯一标识属性
- 参照完整性,外码
- 用户定义完整性,数据的类型,大小等是否满足实际需求
关系代数
- 集合运算:并、差、交、笛卡儿积
- 专门关系运算:选择、投影、连接、除
SQL分类
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
mysql数据类型
- 数据类型
查询数据
实际应用中应尽量采用连接查询。
-
含有至少、除了的这些select语句不好理解
- 至少用来供应商s1所提供的全部零件的工程号JNO
select JNO from SPJ SPJA where not exists (select * from SPJ SPJB where SNO="S1" and not exists (select * from SPJ SPJC where SPJC.PNO = SPJB.PNO and SPJC.JNO = SPJB.JNO));
- 至少用来供应商s1所提供的全部零件的工程号JNO
-
消除值重复的行:distinct
select distinct pasword from tab_user;
-
between and,not between and
select Sname from Student where Sage between 20 and 23;
-
确定集合: in,not in
select Sno from Student where Sdept in ('CS','MA','IS');
-
字符匹配:like (等价于=),not like (等价于 != 或者 <> )
-
%:任意长度的字符串
select sno from Student where sname like '刘%';
-
_:任意单个字符串
select sno from Student where sname = '欧阳_';
-
-
is null,is not null
select sno from SC where grade is not null;
-
order by
- asc升序(默认)
- desc降序
select sno from SC where cno = '3' order by Sage desc;
-
聚集函数
- count:不会忽略空值
- sum
- avg
- max
- min
select count(distinct Sno) from SC; select avg(Grade) from SC where Cno = '1';
-
goup by
-- 求各课程号及相应的选课人数 select Cno, count(Sno) from SC group by Cno;
-
having:不能用where子句
-- 查询了选择了三门以上课程的学生学号 select Sno from SC group by Sno having count(*)>3;
连接查询
-
等值连接查询
select Student.*, SC.* from Student, SC where Student.Sno = SC.Sno;
-
自然连接查询
--查询每门课程的间接先选课 select Student.Sno, Sname,Sex, Sage, Sdept, Cno, Grade from Student, SC where Student.Sno = SC.Sno
-
自身连接
select First.Cno, Second.Cpno from Course First, Course Second where First.Cpno = Second.Cno;
-
join
在自然连接时把悬浮元组也保存在结果关系中,其他属性上填上空值。- 内连接(其实就是等值连接)
select * from tab1 a inner join tab2 b on a.aid = b.bid;
- 外连接 outer可以省略
-- 左外连接 select Student.Sno, Sname,Sex, Sage, Sdept, Cno, Grade from Student left outer join SC on (Student.Sno = SC.Sno);
- 内连接(其实就是等值连接)
套嵌查询
select Sname from Student where Sno in
(select Sno from Sc Where Cno = '2');
-
exists:existszhi谓词的子查询不返回任何数据,只产生逻辑值true和false。
-
选修了全部不课程的学生姓名
select Sname from Student where not exists (select * from Course where no texists (select * from SC where Sno = Student.Sno and Cno = Course.Cno));
-
至少选修了学生2015选修的的全部课程的学生号码
select Distinct Sno from SC scx where not exists (select * from SC SCY where SCY.Sno = '2015' and not exists (select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno))
-
集合查询
-
并 union
select Sno from SC where Cno='1' union select Sno from SC where Cno = '2'
-
交 intersect
select Sno from SC where Cno='1' intersect select Sno from SC where Cno = '2'
-
差 except
select Sno from SC where Cno='1' except select Sno from SC where Cno = '2'
基于派生表的查询
- 派生表语句中的as可以省略
select Sno, Cno from SC,(select Sno, Avg(Grade) from SC group by Sno) as Ag_sc(avg_sno,avg_grade)
插入数据
-- Student(Sno, Sname, Sage, Sdept)
insert into Student (Sno, Sname, Sage, Sdept) values ('2017', '周杰伦', 18, 'sc');
insert into Student values ('2017', '周杰伦', 18, 'sc');
insert into Student (Sname, Sno) values('周杰伦', '2017');
创建基本表
-
实体完整性约束:primary key
-
参照完整性约束:foreign key(colxx) references tabxx(colxx)
- 违约处理:no action 不执行(默认),cascade 级联处理
-
用户完整性约束:check(xx > 30)
create table SC( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno) on delete cascade//当Student中的元组被删除,级联删除SC表中相应的元组 on update cascade, foreign key(Cno) references Course(Cno) on delete no action on update cascade );
-
完整性命名句子 constraint
-
用变量的形式代表约束,方便后期操作
create table SC( Sno char(9), Cno char(4), Grade smallint, constraint cp1 primary key(Sno,Cno), constraint cf1 foreign key(Sno) references Student(Sno) on delete cascade//当Student中的元组被删除,级联删除SC表中相应的元组 on update cascade, constraint cf2 foreign key(Cno) references Course(Cno) on delete no action on update cascade, constraint cc check(Grade >= 0 and Grande <= 100 ) );
-
删除约束
alter table Student drop constraint cf1;
-
增加约束
alter table Student add constraint c4 check(Grade between 0 and 100);
-
创建临时表
-- 方式1
create temporary table tab(
...
);
-- 方式2
create temporary table tab as (select ...);
复制表
-- 1
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
-- 2
create table newtab as (select id, username, pasword from admin);
删除表
-
级联删除:cascade
drop table Student cascade;
-
默认restrict
修改基本表 alter
- 加入新的列
alter table Student add S_entrance date;
- 删除列
alter table Student drop Sname;
- 修改数据的类型
alter table Student modify int;
- 修改字段名称
alter table Student change Sname name char(100);
- 增加约束条件
alter table Course add unique(Cname);
alter table Course add primary key (Canme);
- 删除约束
alter table tableName drop foreign key keyName;
alter table tableName drop primary key;
- 修改表的名称
alter table tab1 rename to tab2;
update
update Student set Sage = 22 where Sno = '2017';
-- 更改所有
update Student set Sage = 22;
delete
delete from Student where Sno = '2017';
-- 删除所有
delet from Student;
视图
-
创建视图
create view IS_S(Sno, Sname, Grade) as select Student.Sno, Sname, Grade from Student.SC where Sdept='IS' and Student.Sno = SC.Cno = '1';
-
删除视图
-- 建立在视图上的视图也被删除 drop view IS_S cascade;
-
查询和更新和基本表一样
索引
-
创建索引
1.建表时建立索引
CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);create table pro( id int, name char(10), index (id, name) );
2.建表后创建
create [unique | fulltext | spatial] index 索引名 on 表名(字段名) [using 索引方法]create index iname no tname(属性名称)
-
删除索引
drop index [indexName] on tab;
-
使用alter添加和删除索引
-- unique意味着索引值必须唯一 alter table tab add unique index_name(col); -- 普通索引,允许值相同 alter table tab add index_name(col); alter table tab add fulltext index_name(col); -- 删除索引 alter table tab drop index c;
事务
主要用于除了操作量大,复杂度高的数据。事务用来管理insert,update,delete语句。
-
事务必须满足四个条件:
- 原子性:一个事务操作要么完成,要么就当没放生过,在中途如果遇到错误就回滚。
- 一致性:事务执行的开始和结束之后,数据库的完整性没有被破坏。
- 隔离性(独立性):数据库允许多个并发事务同时对其数据进行读写和修改,独立性防止多个事务并发执行时由于交叉执行导致的数据的不一致。事务隔离分为不同的级别,读未提交,读提交,可重复读和串行化。
- 持久性:事务处理结束就后,对数据的修改是永久的,即便系统故障也不会丢失。
-
事务的实例
begin transaction if (...)then{ ... rollback; } else{ ... commit; }
-
事务的隔离级别
-
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
-
存在问题:
- 脏读:一个事务,读取到另一个事务中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
-
隔离级别:
- read uncommitted:读未提交
- 产生的问题:脏读、不可重复读、幻读
- read committed:读已提交 (Oracle)
- 产生的问题:不可重复读、幻读
- repeatable read:可重复读 (MySQL默认)
- 产生的问题:幻读
- serializable:串行化
- 可以解决所有的问题
- 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- 数据库查询隔离级别:
- select @@tx_isolation;//旧版本
- select @@transaction_isolation//新版本
- 数据库设置隔离级别:
- set global transaction isolation level 级别字符串;
- read uncommitted:读未提交
-
演示:
set global transaction isolation level read uncommitted; start transaction; -- 转账操作 update account set balance = balance - 500 where id = 1; update account set balance = balance + 500 where id = 2;
-
用户管理及授权
-
管理用户
-
添加用户
通用create role username;
mysql
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
修改用户密码
set password for 'username'@'hostname' = 'newpassword'; set password for 'username' = 'newpassword';
-
忘记密码
1. cmd – > net stop mysql 停止mysql服务
* 需要管理员运行该cmd
2. 使用无验证方式启动mysql服务: mysqld --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. 使用新密码登录。
10. -
查询用户:
-- 1. 切换到mysql数据库 USE myql; -- 2. 查询user表 SELECT * FROM USER;
-
-
删除用户
drop user 'username'@'hostname';
-
用户授权
-
授予权限
-- grant select on table Student to user1; grant all privileges on table Student, Course to user1, user2; -- 授权给所有用户 grant select on table SC to public; -- 把修改学生学号的权限授给用户user4 grant update(Sno), select on table Student to user3; -- 允许user2把自己的权限授予其他用户 grant select on table SC to user2 with grant option;
-
撤销权限
revoke update(Sno) on table Student from user2;
-
mysql函数
mysql函数
- 返回当前日期时间:NOW()
- 字符串拼接:CONCAT(s1, s2,…,sn)
mysql正则表达式
待续。。。
过程化sql
-
用户变量声明和定义
-- 1 set @id = new.id; -- 2 set id = new.id; -- 3 declare count int default 0; select count(*) into count from tab_user
-
选择语句
if (condition) then ... end if -- 2 if (condition) then ... else ... end if -- 3 if (condition) then ... elseif (condition) then ... else ... end if
-
循环语句
-- 1 通过break,exit,leave等结束循环 loop ... end loop -- 2 while condition loop ... end loop --3 for count in [reverse] bound1...bound2 loop ... end loop
触发器
触发器是一种特殊的存储过程,与存储过程的区别是触发器会被自动调用,而存储过程只有在显式调用时才被执行。
- 触发器的优点
- 提供了检查数据完整性的替代方法
- 可以捕获数据库层中业务逻辑的错误
- 可以在对更改表之前和之后自动调用触发器
- 对于审核表中数据的更改十分有用
- 触发器缺点
- 无法替代所有验证,比如应用层的表单验证。
- 可能会增加数据库服务器的开销
- 创建触发器
-
OLD.colname:旧的数据
-
NEW.colname:新增的数据,insert用这个
-
update触发器
delimiter create trigger credit_get after update on takes for each row begin if (NEW.grade <> 'F' and NEW.grade is not null and (OLD.grade = 'F' or OLD.grade is null))then update student set tot_cred = tot_cred + (select credits from course where course.course_id = NEW.course_id) where student.ID = NEW.ID; end if; end;
-
insert触发器
delimiter create trigger timeslot_check1 before insert on section for each row begin if(NEW.time_slot_id not in (select time_slot_id from time_slot)) then delete from section where time_slot_id = NEW.time_slot_id; end if; end
-
delete 触发器
delimiter create trigger timeslot_check2 after delete on time_slot for each row begin if(OLD.time_slot_id not in (select time_slot_id from time_slot) and OLD.time_slot_id in (select time_slot_id from section)) then insert into time_slot values(OLD); end if; end
-
存储过程
-
存储过程一览
-
参数:IN,OUT,INOUT
-- IN USE `yiibaidb`; DROP procedure IF EXISTS `GetOfficeByCountry`; DELIMITER $$ USE `yiibaidb`$$ CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT * FROM offices WHERE country = countryName; END$$ DELIMITER ; -- 调用 CALL GetOfficeByCountry('France') -- OUT USE `yiibaidb`; DROP procedure IF EXISTS `CountOrderByStatus`; DELIMITER $$ CREATE PROCEDURE CountOrderByStatus( IN orderStatus VARCHAR(25), OUT total INT) BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus; END$$ DELIMITER ; -- 调用 CALL CountOrderByStatus('Shipped',@total); SELECT @total; -- INOUT DELIMITER $$ CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4)) BEGIN SET count = count + inc; END$$ DELIMITER ; -- 调用 SET @counter = 1; CALL set_counter(@counter,1); -- 2//原文出自【易百教程】,商业转载请联系作者获得授权,非商业请保留原文链接:https://www.yiibai/mysql/stored-procedures-parameters.html
-
case语句:类似与switch case,else语句可以省略,但是如果找不到匹配项会报错
DELIMITER $$ CREATE PROCEDURE GetCustomerShipping( in p_customerNumber int(11), out p_shiping varchar(50)) BEGIN DECLARE customerCountry varchar(50); SELECT country INTO customerCountry FROM customers WHERE customerNumber = p_customerNumber; CASE customerCountry WHEN 'USA' THEN SET p_shiping = '2-day Shipping'; WHEN 'Canada' THEN SET p_shiping = '3-day Shipping'; ELSE SET p_shiping = '5-day Shipping'; END CASE; END$$
-
循环:while,repeat,loop;leave(类似break)和iterate(类似continue)用于跳出循环
WHILE expression DO statements END WHILE -- 类似于do while REPEAT statements; UNTIL expression END REPEAT -- loop CREATE PROCEDURE test_mysql_loop() BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 1; SET str = ''; loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF; SET x = x + 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET str = CONCAT(str,x,','); END IF; END LOOP; SELECT str; END;
-
游标:游标是一块数据缓冲区,存储SQL语句的执行结果,如果要处理存储过程中的结果集,那最好使用游标。mysql游标为只读,不可滚动和敏感。存储过程,存储函数和触发器中都可以使用游标。
DELIMITER $$ CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000)) BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_email varchar(100) DEFAULT ""; -- declare cursor for employee email DEClARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN email_cursor; get_email: LOOP FETCH email_cursor INTO v_email; IF v_finished = 1 THEN LEAVE get_email; END IF; -- build email list SET email_list = CONCAT(v_email,";",email_list); END LOOP get_email; CLOSE email_cursor; END$$ DELIMITER ;//原文出自【易百教程】,商业转载请联系作者获得授权,非商业请保留原文链接:https://www.yiibai/mysql/cursor.html
-
列出存储过程
-
错误处理
DECLARE action HANDLER FOR condition_value statement;
-
aciton: continue(如果出现错误,执行statement,并继续执行),exit (执行stament并推出存储过程)
-
condition_value:NOT FOUND,1062(重复的键错误,如重复插入),1051(table not found)
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1; DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred';
存储函数
-
存储函数是有返回值的,存储过程没有
-
创建:所有参数都是IN所有不用声明类型
CREATE FUNCTION function_name(param1,param2,…) RETURNS datatype [NOT] DETERMINISTIC statements
-
示例
DELIMITER $$ CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE lvl varchar(10); IF p_creditLimit > 50000 THEN SET lvl = 'PLATINUM'; ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN SET lvl = 'GOLD'; ELSEIF p_creditLimit < 10000 THEN SET lvl = 'SILVER'; END IF; RETURN (lvl); END $$ DELIMITER ;
数据库的设计
- 需求分析
- 概念设计:ER模型
- 逻辑设计
- 物理设计
- 数据库实施
- 数据库运行与维护
mysql8.0新特性CET
- 什么是CET
附录
- 官方文档
- Mysql学习网站
- 52wiki社区
- 菜鸟教程
- 易百教程
更多推荐
mysql数据库相关
发布评论