数据库笔记

入数据库前

查看服务器列表

net start

启动MySQL

net start MySQL (根据安装的MySQL服务名称来确定后面的)

暂停MySQL

net stop MySQL

登录MySQL

mysql -u root -p ****** (*为你设置的密码)

以上都是在cmd上运行

准备入库

首先查看你的MySQL是否有你的库

查看所有库

show databases;  (一下结尾都需要有分号来结束了)

如果没用你要用的你就得创建一个

创建数据库

CREATE DATABASE school; (创建了一个名字为school的库)

有了数据库后可以选择你自己的库了

选择数据库

use shcool; (进入school库)

出现 Database changed 就表示已经进入成功了

如果你不想要你的数据库那么可以删库

删除数据库

DROP DATABASE school; (删除school数据库)

进入库后

创建表

create table class(
	class_id int, 
    class_name varchar(128),
    class_teacher varchar(10)
);		
/*
class、class_id、class_name、class_teacher 都为数据名称，int、varchar皆为数据类型
*/

插入数据

insert into class values(1,'计算机一班','孔子');
insert into class(class_name,class_teacher) values('计算机一班','孔子');
insert into class values(1,'计算机一班','孔子'),(2,'计算机二班','孟子'),(3,'计算机三班','老子');

更新数据

update class set class_name='云计算一班', class_teacher='墨子' where class_id = 1;

删除数据

delete from class where class_id=1;	/*删除class_id=1的数据*/
delete from class;	/*删除class整个表的数据*/
delete from class where 1=1;	/*删除class整个表的数据*/

查询数据

describe class;		/*查询表内容信息*/
select * from class;	/*查所有信息*/
select class_name,class_teacher from class; /*显示只有班级名及老师*/
select * from class where class_id = 1; 	/*只显示id为1的班级*/

列的完整性约束

	完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件， 数据库系统将不再执行用户的操作。

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系的某表主键
NOT　NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值自动增加，这是MySQl语句的特色
DEFAULT	为该属性设置默认值

主键 PRIMARY KEY

create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64));	/*创建表class*/
create table class(id int, name varchar(128), teacher varchar(64), PRIMARY KEY(id);	/*同以上一样创建表class*/
create table class(id int, name varchar(128), teacher varchar(64), PRIMARY KEY(id,name));/*主键为：id,name两个*/
                   
 create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINT
id_pk PRIMARY KEY(id,name));  /*CONSTRAINT 为取名，方便以后修改*/                   

外键 FOREIGN KEY

create table students(
    id int AUTO_INCREMENT PRIMARY KEY, 
    name varchar(20),
    class_id int,
    sex enum('男','女','未知'),
    FOREIGN KEY (class_id) REFERENCES class(id)
);
/*FOREIGN KEY (表里应用的数据) REFERENCES 某个表的(某个数据)*/

非空 NOT NULL

create table test(id int NOT NULL, str varchar(20)); /*id不能为空*/

唯一 UNIQUE

create table person(
    id int UNIQUE, 
    name varchar(20)
);	
/*id不能重复*/

自增 AUTO_INCREMENT

create table test(id int AUTO_INCREMENT PRIMARY KEY, str varchar(20));/*自增必须用在主键、外键或者唯一键上*/
create table students(
    id int AUTO_INCREMENT PRIMARY KEY, 
    name varchar(20),
    class_id int,
    sex enum('男','女','未知'),
    FOREIGN KEY (class_id) REFERENCES class(id)
);

默认 DEFAULT

create table person(
    id int UNIQUE,
    name varchar(20), 
    sex enum('男','女','未知') DEFAULT '未知'
);
insert into person(id,name) values(1,'盘古'); /*后面的sex属性它会自己默认‘未知’*/

​ 当数据库已经创建好了表，但是我们还要对其进行修改……

create table person(id int UNIQUE,name varchar(20), sex enum('男','女','未知') DEFAULT '未知');
/*对以上↑表进行修改*/

新增

alter table 表名 add 行为 (表属性名);

alter table person add PRIMARY KEY (name);	/*名字设置为主键*/

删除

alter table person drop index id; /*删除id的唯一键，唯一键和外键都要drop index*/

查询特殊字段

一下查询用这表查询

 create table grade(
     id int UNIQUE AUTO_INCREMENT, 
     name varchar(128) NOT NULL, 
     math tinyint unsigned, 
     chinese tinyint unsigned, 
     english tinyint unsigned, 
     class_id tinyint unsigned
 );
 
 insert into grade values
 (1,'三爷 ', 80, 87, 91,1),
 (2,'张三 ', 72, 64, 89,2),
 (3,'唐三藏', 54, 69, 87,2),
 (4,'猫三脚', 74, 71, 90, 1),
 (5,'三脚猫', 55, 56, 83, 1),
 (6,'李四', 78, 79, 89, 1),
 (7,'李四', 78, 79, 89, 2),
 ;

去重查询 DISTINCT

select name,math,chinese,english from grade where name = '李四';	/*查出两个一模一样的李四*/
select DISTINCT name,math,chinese,english from grade where name = '李四';	/*只有一个李四了*/

限定查询 IN

select * from grade where name in ('张三','李四');	/*只显示名字叫张三和李四的信息*/
select * from grade where name not in('张三','李四');	/*除了张三和李四都显示*/

范围查询 BETWEEN AND

select id, name, math from grade where math>=60 and math <=80;
/*👆查询数学60~80的同学👇*/
select id, name, math from grade where math BETWEEN 60 and 80;

select id,name,math from grade where math NOT BETWEEN 60 and 80; /*小于60或者大于80*/

模糊查询 like

通配符

符号	功能描述
-	该通配符能匹配单个字符
%	该通配符可以匹配任意长度的字符，0个，1个或多个字符

select * from grade where name like '%三%';	/*查找名字含有‘三’的同学*/
select * from grade where name like '%三';	/*查找‘三’排在后面的同学*/
select * from grade where name like '三%';	/*查找‘三’排在前面的同学*/
select * from grade where name like '%三%' and name like '%猫%';	/*查找有“三”又有“猫”的同学*/
select * from grade where name like '_三_';	/*查找名字为三个字且中间一个字是“三”的同学*/

排序查询 ASC/DESC

关键词 ASC/DESC(升序/降序)

select id,name,math from grade ORDER BY math ASC;	/*👆照数学成绩来进行升序排序👇*/
select id,name,math from grade ORDER BY math;

select id,name,math from grade ORDER BY math DESC;	/*按照数学成绩来进行降序排序*/

统计分组

函数关键词	作用
COUNT	实现对表中记录进行统计,不管表字段中包含的是 NULL 值还是非 NULL 值
AVG	可以实现对指定字段的平均值进行计算,在具体统计时将忽略 NULL 值
SUM	可以实现计算指定字段值之和,在具体统计时将忽略 NULL
MAX	可以实现计算指定字段值中的最大值,在具体统计时将忽略 NULL 值
MIN	可以实现计算指定字段值中的最小值,在具体统计时将忽略 NULL 值

select count(math),avg(math),sum(math),max(math),min(math) from grade;	/*查看grade表的数学科目的累计、平局分、总分、最大分、最小分*/

select count(math),avg(math),sum(math),max(math),min(math) from grade group by class_id;/*根据不同的class_id分别统计数学科目的累计、平局分、总分、最大分、最小分*/

select class_id, GROUP_CONCAT(name)from grade group by class_id;	/*通过GROUP_CONCAT函数来分别写同一个class_id的名字*/

联合查询

用一下这些表来查询

create table class(
	id tinyint AUTO_INCREMENT PRIMARY KEY,
    name varchar(64) NOT NULL,
    teacher varchar(32)
);

create table student(
	id tinyint AUTO_INCREMENT PRIMARY KEY,
    name varchar(64) NOT NULL,
	class_id tinyint,
    grade tinyint,
    FOREIGN KEY (class_id) REFERENCES class(id)
);

insert into class(name,teacher) values('计算机一班','孔子'),('计算机二班','孟子'),('计算机三班','老子');

insert into student(class_id,name,grade) values(1,'三爷', 80),(2,'张三', 72),(2,'唐三藏', 93),(2,'猫三脚', 74),(1,'三脚猫', 55), (1,'李四', 78);

内连接查询

1. 自连接

   内连接查询中存在一种特殊的等值连接——所谓自连接，就是指表与其自身进行连接。

   select s2.id,s2.name,s2.grade from student s1 inner join student s2 on s1.name='李四' and s1.class_id = s2.class_id;	/*查找与李四同班的同学*/
   

2. 等值连接

内连接查询中的等值连接就是在关键字 ON 后的匹配条件中通过等于关系运算符（=）来实现等值条件。

select * from student inner join class on student.class_id = class.id;

3. 不等值连接

   内连接查询中的不等连接就是在关键字 ON 后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使 用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。

   select * from student inner join class on student.class_id != class.id;
   select * from student inner join class on student.class_id != class.id where class.id = 1;
   

外连接查询

1. 左外连接

   外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字 LEFT JOIN 左边的表为参考表。

   select * from student as a left join class as b on a.class_id = b.id;	/*左连接查询所有学生对应的班级信息*/
   
   select * from class as a left join student as b on a.id = b.class_id; 	/*左连接查询所有班级的学员信息*/
   

2. 右外连接

   右外连接在新关系中执行匹配条件时，以关键字 RIGHT JOIN 右边的表为参考表，如果右表的 某行在左表中没有匹配行，左表将返回空值。

   select *from student right join class on student.class_id = class.id;	/*右连接查询所有班级对应的学员信息*/ 
   
   select * from class right join student on class.id = student.class_id;	/*右连接查询所有学员对应的班级信息*/
   

合并查询

union 和 union all 的主要区别是 union all 是把结果集 直接合并在一起，而 union 是将 union all 后的结果再执行一次 distinct，去除重复的记录后的结果。

select teacher '名字' from class union all 
select name from student;	/*#查询班级表所有老师和学生表中所有学生姓名*/

select teacher '名字', id '班级id' from class union 
select name, class_id from student;		/*查询班级表所有(老师、班级 ID)和学生表中所有学生(姓名、班级 ID*/

子查询

1. 带比较符的子查询

   比较运算符包括=、!=、>、>=、<、<=和<>等

   select teacher from class where id = (select class_id from student where name='张三');		/*查询“张三”所在班级班主任的姓名*/
   

2. 带关键字 IN 的子查询

   select teacher from class where id in (select class_id from student where name like '三%');	/*查询姓“三”所在班级班主任的姓名*/
   
   select teacher from class where id not in (select class_id from student where name like '三%');	/*查询不姓“三”所在班级班主任的姓名*/
   

3. 带关键字 EXISTS 的子查询

   关键字 EXISTS 表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如 果至少返回一行，那么 EXISTS 的结果为 true，此时外层语句将进行查询；如果子查询没有返回任何行，那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。

   select *from class where id=2 and EXISTS (select*from student where class_id=2);	/*如果 2 班存在学生记录，就查询 2 班的班级信*/
   

4. 带关键字 ANY 的子查询

   关键字 ANY 表示满足其中任一条件。

   select*from student where grade >= ANY(select grade from student where id<=2);		/*查询成绩大于前两名任一的同学*/
   

5. 带关键字 ALL 的子查询

   关键字 ALL 表示满足所有条件。

   select*from student where grade >= ALL(select grade from student where id<=2);		/*查询成绩必须都大于前两名的同学*/
   

触发器

用一下表演示

create table class(
	id tinyint unsigned PRIMARY KEY AUTO_INCREMENT,
    name varchar(64) NOT NULL,
    teacher varchar(32),
    cout tinyint unsigned DEFAULT(0)
);

create table student(
 	id tinyint unsigned PRIMARY KEY AUTO_INCREMENT,
   	name varchar(32) NOT NULL,
    sex enum('男','女','未知') DEFAULT '未知',
    class_id tinyint unsigned NOT NULL, 
    FOREIGN KEY (class_id) REFERENCES class(id)
);

insert into class(name,teacher) values('计算机一班','孔子'),('计算机二班','孟子'),('计算机三班','老子');

执行语句中如果要引用更新记录中的字段，对于 INSERT 语句,只有 NEW 是合法的，表示当前已插入的记录；对于 DELETE 语句，只有 OLD 才合法，表示当前删除的记录； 而 UPDATE 语句可以和 NEW（更新后）以及 OLD（更新前）同时使用。

触发事件	合法参数
insert	new
delete	old
update	new(跟新前) / old(跟新后)

创建包含单个执行语句的触发器

CREATE trigger tri_insert_student 	/*tri_insert_student 为触发器的名字*/
after insert 	/*insert 为触发器的类型*/
on student 		/*student 为触发器检测的表*/
for each row  update class set cout=cout+1 where class.id = new.class_id;	/*'update class set cout=cout+1 where class.id = new.class_id' 为触发器执行的操作*/


insert into student values(1,'三爷', 1,2),(2,'张三', 2,2),(3,'唐三藏', 1,1),(4,'猫三脚', 2,1),(5,'三脚猫', 2,2), (6,'李四', 1,1);

创建包含多个执行语句的触发器

| new |
| delete | old |
| update | new(跟新前) / old(跟新后) |

创建包含单个执行语句的触发器

CREATE trigger tri_insert_student 	/*tri_insert_student 为触发器的名字*/
after insert 	/*insert 为触发器的类型*/
on student 		/*student 为触发器检测的表*/
for each row  update class set cout=cout+1 where class.id = new.class_id;	/*'update class set cout=cout+1 where class.id = new.class_id' 为触发器执行的操作*/


insert into student values(1,'三爷', 1,2),(2,'张三', 2,2),(3,'唐三藏', 1,1),(4,'猫三脚', 2,1),(5,'三脚猫', 2,2), (6,'李四', 1,1);

创建包含多个执行语句的触发器