文章目录
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函数
- 返回当前日期时间：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.com/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.com/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模型
- 逻辑设计
- 物理设计
- 数据库实施
- 数据库运行与维护
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
