数据库是按照一定格式存储数据的一些文件的组合。
数据库管理系统 DataBaseManagement 简称DBMS。
SQL是结构化查询语言 定义了操作所有关系型数据库的规则 DBMS--执行--SQL--操作--DB
常用命令 退出MySQL exit 查看有哪些数据库 show databases; 注意以分号结尾,英文状态
使用时某个数据库 use 数据库名; 创建数据库 create database 你想要的名;
数据库最基本的单元是表:table 任何一张表都有行(row)和列(column)
DDL 数据定义语言 (凡是带有create(新建),drop(删除),alter(修改)的都是DDL)
DML数据操作语言(凡是对表中的数据进行增删改的都是DML)
DQL 数据查询语言(凡是带select关键字的都是查询语言)
DCL数据控制语言 授权(grant) 撤销权限(revoke)
TCL事务控制语言 (包括事务提交commit,事务回滚rollback)
1手动 2 cmd--services.msc 3 使用管理员打开cmd net start / stop mysql 启停命令
还可登录他人 mysql -hip(目标ip地址)-uroot -p连接目标的密码
mysql --host=ip --user=root --password=连接目标的密码
三种注释 单行注释:--(一个空格)注释内容 或 #(不用加空格)注释内容 #是MySQL特有
Retrieve 查询 show databases;查询所有数据库的名称
show create database 数据库名称; 查询某个数据库的字符集:查询某个数据库的创建语句
查看以my开头的(my后面可有多个字符)数据库 show databases like 'my%';
查看以my开头的(my后面只有一个字符)数据库 show databases like 'my_';
Create创建 create database 你想要的名; 为了保险可以 create database if not exists 你想要的名;
还可指定创建 例如创建字符集为gbk的数据库 create database if not exists db4 character set gbk;
Drop 删除 drop database 数据库名称; drop database if exists 数据库名;
alter修改 alter database 名 charset gbk;
DDL操作表:CRUD(Create创建 Retrieve 查询 Update修改 Delete删除 )
Retrieve 查询 show tables;查询当前数据库所有表的名称
show table status like 'student'\G 查看表状态 \G表示列表信息,后面不用加分号
desc 表名称; 看表的字段信息;desc student id;只看某项
show (full)columns from 数据表名; 看表的字段信息;加full之后可看详细信息
show create table student\G查看表的结构,可查看创建时的sql语句
show tables like 'my%'; 查看以my开头的(my后面可有多个字符)表
show table status like 'employee'\G
(3) 查看Department数据表所有字段的信息(两种方式)。
(5) 查看Department数据表详细信息(包括权限、注释等)。
show full columns from department;
show create table department\G
数值 如age int score double(参数1:总长度,参数2:小数点后保留的位数)
字符串 name char(10)存“张三”会用10个字符空间,因为他不会计算 (存储的性能高 浪费空间)
name varchar(10)存“张三”会用2个字符空间,他会计算张三占几个字符 (存储性能低 节约空间)
Drop 删除 drop table 表名; drop table if exists 表名;
alter修改 修改表名 alter table 表名 rename to 新表名;.1.2
添加一列 alter table 表名 add 列名 数据类型;.6
修改数据类型 alter table 表名 modify 列名 新数据类型;.5
修改列名和数据类型 alter table 表名 change 列名 新列名 新数据类型;.4
1、 将数据表department的表名改成depart,并进行查看。
alter table department rename to depart;
2、 将数据表depart的表名改成department,并进行查看
alter table depart rename to department;。
alter table department charset = gbk; show create table department \G
4、 将数据表employee的“id”字段的字段名改为“emid”,并查看修改结果。
alter table employee change id emid int ;desc employee;
5、 将数据表employee的“name”字段的字段类型改为char(10),并查看修改结果。
alter table employee modify name char(10); desc employee name;
6、 在数据表employee中新增“sex”字段,字段类型为char(2),放在“name”字段之后,并查看修改结果。
alter table employee add sex char(2) after name ; desc employee;
7、 在数据表employee中修改“did”字段位置,放在第一个字段位置,并查看修改结果。
alter table employee modify did int first; desc employee;
8、 在数据表employee中新增phone char(11),address varchar(60)两个字段。
alter table employee add(phone char(11),address varchar(60));
9、 在数据表employee中删除“sex”字段,并查看结果
alter table employee drop sex; desc employee;
drop tabledepartment; show tables;
DDL 数据定义语言 (凡是带有create(新建),drop(删除),alter(修改)的都是DDL)
DDL操作表:CRUD(Create创建 Retrieve 查询 Update修改 Delete删除 )
DML(对表的数据进行增删改)(增insert删delete改update查select)
insert into employee values(1001,1,'张三',18);
insert into employee (did,emid,name) values(1002,2,'李四');
insert into employee values(1003,4,'李丽',19),(1002,5,'张强',20);
select * from employee; (不要用,这样人们不知道查哪些)
查部分 select emid,name from employee;
select * from employee where did=1001;
update employee set age=20 where name='张三';
delete from employee where emid =5;
1、 创建mydb数据库,在mydb数据库中创建一张电子杂志订阅表(subscribe)。
2、电子杂志订阅表中要包含4个字段,分别为编号(id int)、
用户是否确认订阅 (status int,使用数字表示,1表示已确认,0表示未确认)、
->status int comment'使用数字表示,1表示已确认,0表示未确认',
insertinto subscribe values(1,'tom123@163.com',1,'TRBXPO');
insert into subscribevalues(2,'lucy123@163.com',1,'LOICPE');
insert into subscribe values(3,'lily123@163.com',0,'JIXDAMI');
insert into subscribevalues(4,'jimmy123@163.com',0,'QKOLPH');
insert into subscribevalues(5,'joy123@163.com',1,'JSMWNL');
select * from subscribe where status=1;
select email,status from subscribe;
update subscribe set status=1 where id=4;
8、将编号等于5的订阅记录邮箱改为“joy666@163.com”,邮箱确认验证码改为“ABMROP”。
update subscribe set email='joy666@163.com',code='ABMROP'where id=5;
delete from subscribe where status=0;
DQL 数据查询语言(凡是带select关键字的都是查询语言)
select stu_id as 学号,stu_name as 姓名 from student;
select * from student where stu_credits>=60;
select * from course where term in(1,4,5);
用带or的 SELECT * FROM course WHERE term=1 or term = 4 or term = 5;
select * from score WHERE score BETWEEN 60 and 90;(包含60 和90)
带and 的查询 SELECT * from score WHERE score>=60 AND score <=90 ;
SELECT * from student WHERE remark IS NULL;
SELECT DISTINCT department FROM student;
SELECT * FROM student WHERE stu_name LIKE '李%';
7 带AND 关键字的多条件查询(同组是一般用or,不同组and)
SELECT * from student WHERE sex='女' AND stu_credits=60;
SELECT * FROM course WHERE credit=2 or credit = 4;
SELECT course_id,course_name,credit FROM course;
3、 查询student表中总学分在60分以下的学生的学号、姓名、所在学院信息。
SELECT stu_id,stu_name,department FROM student WHERE stu_credits<60;
4、 查询课程表中所有学分为2、4、6分的课程信息(in方法实现)。
SELECT * FROM course WHERE credit IN (2,4,6);
5、 查询总学分在50-60分之间的学生记录(两种方法实现)。
SELECT * FROM student WHERE stu_credits BETWEEN 50 AND 60 ;
SELECT * FROM student WHERE stu_credits>=50 AND stu_credits<=60;
SELECT DISTINCT course_id FROM score;
SELECT * FROM student WHERE stu_name LIKE '刘%';
SELECT * FROM student WHERE sex ='女';
SELECT * FROM course WHERE class_hour=60 or class_hour=72;
SELECT * FROM course WHERE class_hour in(60,72);
SELECT * FROM student WHERE birthday LIKE '2002%';
SELECT COUNT(stu_id) as 学生人数 from student;
SELECT * FROM student ORDER BY stu_id DESC;
3 分组查询-分组统计 技巧:select跟的 就是分组(group by 后面的)的 + 聚合函数
SELECT sex as 性别,count(*) as 人数 from student GROUP BY sex;
执行机制不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤
可判断的条件不一样:where 不能对聚合函数进行判断,having 可以
原因 执行顺序不一样:where > 聚合函数 >having
注意 分组之后 查询字段(即select后面跟的)为聚合函数和分组字段,查询其他字段无任何意义
3 分组查询-多分组统计 技巧:select跟的 就是分组(group by 后面的)的 + 聚合函数
SELECT department as 学院, sex as 性别,COUNT(*) as 人数 FROM student GROUP BY department,sex;
SELECT department as 学院, sex as 性别,COUNT(*) as 人数 FROM student GROUP BY department,sex with ROLLUP;
SELECT * FROM student LIMIT 2,2;(第一个2是从第三个开始,第二个2是查询2条)
SELECT department as 学院,count(*) as 人数 from student GROUP BY department;
SELECT term as 学期,sum(credit) as 学分 from course GROUP BYterm;
SELECT course_id as 课程,max(score) as 最高总学分 from score GROUP BY course_id;
SELECT department as 学院,count(*) as 人数 from student GROUP BYdepartment HAVING department='计算机学院';
SELECT * FROM score ORDER BY course_id desc,stu_id asc;
SELECT course_id as 课程编号 ,AVG(score) as 平均分 from score GROUP BY course_id HAVING AVG(score)>'75';
SELECT department as 学院, sex as 性别,COUNT(*) as 人数 FROM student GROUP BY department,sex;
SELECT stu_id as 学生编号 ,avg(score) as 平均分 FROM score GROUP BY stu_id with ROLLUP;
SELECT course_name FROM course LIMIT 3;
SELECT course_name FROM course LIMIT 5,2;
内连接查询(内连接查询根据匹配条件返回第一个表与第二个表所有匹配成功的记录)
SELECT student.stu_id,stu_name,course_id,score from student INNER JOIN score on student.stu_id=score.stu_id;
SELECT DISTINCT course.course_id,course_name from course,score WHERE course.course_id=score.course_id;
1 SELECT * FROM score WHERE stu_id=(SELECT stu_id FROM student WHERE stu_name='王红');
2 SELECT * FROM student WHERE stu_id in(SELECT stu_id FROM score WHERE course_id='101') ;
3 SELECT stu_id,stu_name FROM student WHERE EXISTS(SELECT * FROM score WHERE score<'60' AND student.stu_id = score.stu_id); 注意:为什么是select*而不是select stu_id ,因为exists只返回true或false
4 SELECT * FROM score WHERE course_id = ANY(SELECT course_id FROM course WHERE term=1);
SELECT * FROM score WHERE score>all(SELECT score FROM score WHERE course_id=108);
1、 查询所有选修课程的课程编号、课程名称。(两种方法实现)
SELECT DISTINCT course.course_id,course_name from course INNER JOIN score on course.course_id=score.course_id;
SELECT DISTINCT course.course_id,course_name from course,score WHERE course.course_id=score.course_id;
2、 查询所有成绩在85分以上的学生的学号、姓名、课程编号及成绩。
SELECT student.stu_id,stu_name,course_id,score FROM student INNER JOIN score on score.stu_id = student.stu_id AND score>85;
SELECT DISTINCT score.course_id,course_name from course LEFT JOIN score on course.course_id=score.course_id;
SELECT stu_id FROM score WHERE course_id =(SELECT course_id FROM course WHERE course_name='数据结构');
5、 查询学号为“190101”的学生所选修课程的课程信息。
SELECT * FROM course WHERE course_id in(SELECT course_id FROM score WHERE stu_id ='190101');
SELECT * FROM course WHERE course_id =ANY(SELECTcourse_id FROM score WHERE stu_id ='190101');
SELECT * FROM student WHERE EXISTS(SELECT * FROM score WHERE score<'60' AND student.stu_id = score.stu_id);
SELECT * FROM student WHERE stu_id in(SELECT stu_idFROM score WHERE score<60);
SELECT * FROM student WHERE stu_id =ANY(SELECT stu_idFROM score WHERE score<60);
从一个或多个表导出来的表,它是一种虚拟存在的表,表的结构和数据都依赖于基本表。可以像操作基本表一样操作试图
CREATE view stu_view2(学号,姓名) as SELECT stu_id,stu_name from student;
SELECT * FROM stu_view2;(括号中是起别名)
修改视图是指修改数据库中存在的视图的定义。例如,当即本表中某些字段发生变化时,视图必须修改才能正常使用
CREATE or REPLACE view stu_view1 as SELECT stu_id,stu_name,sex from student;
ALTER VIEW stu_view1 as SELECT stu_id,stu_name,sex FROM student WHERE sex='女';
通过视图来查询、添加、修改或删除基本表(也就是原表,因为视图当中其实没有元素)中的数据
1、 创建视图“course_view”,查看所有课程的course_id,course_name,并起别名为课程编号、课程名称。
CREATE view course_view(课程编号,课程名称) as SELECT course_id,course_name from course;
2、 创建视图“cj_view”,查看所有学生选修课程的学号、课程名称、成绩。
CREATE VIEW cj_view(编号,课程名称,成绩) as SELECT DISTINCT course.course_id,course_name,score from
course,score WHERE course.course_id=score.course_id;
3、 修改视图“course_view”为查看所有课程的course_id,course_name,term。
CREATE or REPLACE view course_view as SELECT course_id,course_name,term from course;
5、 向视图“course_view”中插入一条记录(‘202’,‘人工智能’,4)。
INSERT INTO course_view VALUES ('202','人工智能',4);
6、 修改视图“course_view”中课程编号为‘202’的课程的开课学期为3。
UPDATE course_view set term='3' WHERE course_id='202';
7、 删除视图“course_view”中课程名称为‘人工智能’的记录。
DELETE FROM course_view WHERE course_name='人工智能';
CASCADED级联检查(不仅关心自身视图的条件,还有满足基于的student_view01 条件)
主键约束可以唯一标识表中的记录,每个数据表最多只有一个主键约束,定义为主键的字段不能有重复且值不能为NULL值
alter TABLE course1 add UNIQUE(course_name);
alter TABLE course1 MODIFY credit int DEFAULT4 ;
3、 为“student1”表的性别字段设置默认值约束为“男”。
alter TABLE student1MODIFY sex char(2) DEFAULT'男';
alter TABLE student1MODIFY sex char(2);
5、 为“score1”表的学号、课程编号字段创建主键约束。
ALTER TABLE score1 add PRIMARY key (stu_id,course_id);
外键约束
1、 建立score表与student表之间的外键约束,要求拒绝主表删除外键关联字段,并且主表中更新记录时,同时自动更新从表中对应的记录
ALTER TABLE score ADD CONSTRAINT FK_ID FOREIGN KEY(stu_id) REFERENCES student(stu_id) on DELETE RESTRICT on UPDATE CASCADE;
2、 建立score表与course表之间的外键约束,要求拒绝主表删除外键关联字段,并且主表中更新记录时,同时自动更新从表中对应的记录。
ALTER TABLE score ADD CONSTRAINT FK_course_ID FOREIGN KEY(course_id) REFERENCES course(course_id) on DELETE RESTRICT on UPDATE CASCADE;
3、 在student表、score表和course表中各插入一条记录,其中stu_id=100001,course_id=101。
INSERT INTO student(stu_id,stu_name) VALUES ('100001',0);
INSERT INTO score(stu_id,course_id) VALUES ('100001',101);
INSERT INTO course(course_id,course_name) VALUES(101,0);
4、 将course表中course_id改为102,观察score表中是否同步修改。
UPDATE course set course_id='102' WHERE course_name='0';
DELETE FROM student where stu_name='0';
6、 插入“软件1班”、“0,软件2班”、“6,软件3班”3条记录,观察字段自增长情况。
INSERT INTO class(class_name) VALUES('软件一班');
INSERT INTO class VALUES(0,'软件2班');
INSERT INTO class VALUES(6,'软件3班');
1、 创建存储过程proc1,查看学生表中所有计算机学院学生的信息并执行
select* from student WHERE department='计算机学院';
2、创建带输入参数的存储过程proc2,查询指定课程号(作为输入参数)的学生成绩信息并执行。
create procedure proc2(in courseid char(3))
select score from score where course_id=courseid;
3、创建并执行带输入参数的存储过程p_xs,查询指定学号(作为输入参数)的学生姓名、课程编号、成绩。
create procedure p_xs(in stuid VARCHAR(30))
SELECT stu_name,course_id,score from student INNER JOIN score on student.stu_id=score.stu_id where student.stu_id=stuid;
show procedure status like 'proc2'\G
alter PROCEDURE p_xs COMMENT'注释内容';
function fun1(str varchar(30)) returns int
show function status like'fun1'\G
实现方式 在多的一方(从表)建立外键 指向一的(主表)一方的主键
实现方式 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
实现方式 在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)
1、有若干工厂,每个工厂生产多种产品,且每一种产品可以在多个工厂生产,每个工厂按照固定的计划数量生产产品;每个工厂聘用多名职工,且每名职工只能在一个工厂工作,工厂聘用职工有聘用期和工资。工厂的属性有工厂编号、厂名、地址,产品属性有产品编号、产品名、规格,职工的属性有职工号、姓名。
由于“工厂-职工”的联系“聘用”是1:N,它对应的关系模式与职工实体相同,所以,这个关系模式与职工关系模式可以合并
数据库的事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功过,要么同时失败
当选中begin一起运行时,它会当作事务,当其中有错误导致其无法运行时,数据不会提交,即只有在当前表中可以查询数据的变化,但在其他的表中不会查询到数据的变化。rollback是事务回滚(恢复到更改前的数据),当选中begin时才可以回滚。commit是 提交事务,即将数据提交,这样在别的表中也可以访问。
这个可以回滚到事务保存点的状态,而不至于直接回滚到最原始的状态
原子性(Atomicity) 事务是不可分割的最小单位,要么同时成功,要么同时失败
一致性(Consistency)事务完成时,必须使所有的数据保持一致状态
持久性(Durability) 事务一旦提交或回滚,它对数据库中的数据的改变是永久的
数据安全
CREATE USER user1@localhost IDENTIFIED by'123';
CREATE USER user2@localhost IDENTIFIED by'123';
set PASSWORD for user1@localhost='111';
GRANT SELECT on studb.student to user1@localhost;
-- (2) 授予列权限(只能对stu_name这一列进行修改)
GRANT UPDATE(stu_name) on studb.student to user1@localhost;
-- (3)授予数据库权限(第一条对数据库只有查询的权限,第二条全部权限都有)
GRANT SELECT on studb.* to user1@localhost;
GRANT all on studb.* to user1@localhost;
-- (4) 赋予用户权限(对所有数据库的数据表都有创建 修改 删除的权限)
GRANT CREATE,ALTER,DROP ON *.* to user1@localhost;
GRANT CREATE user ON *.* to user1@localhost;
首先在root用户下授予user1 用户在sell表上的selec权限
GRANT SELECT on studb.student to user1@localhost with grant option;
接着在user1用户中创建用户2(因为在第(4.1)步中已经赋予了创建用户的权限),然后将权限直接转给用户2就可以
REVOKE SELECT on studb.student from user1@localhost;
REVOKE ALL PRIVILEGES,GRANT OPTION from user1@localhost;
show VARIABLES LIKE '%secure%';
SELECT * FROM course into OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/myfile.txt';
LOAD data INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/myfile.txt' into TABLE course;