mysql基础知识(一）

一、MySQL的优点

1. 成本低、开源免费、自主性强
2. 性能高、移植性好、服务稳定
3. 体积小、便于安装、易于维护

二、创建表

 #2.1 创建学生信息表
 CREATE TABLE person (
 number INT ( 4 ) PRIMARY KEY auto_increment,
 name VARCHAR ( 50 ) NOT NULL,
 sex CHAR ( 2 ),
 phone char(11)
)
 #2.2 创建学生成绩表
 CREATE TABLE results (
r_id INT ( 4 ) PRIMARY KEY auto_increment,
numberNo INT ( 4 ) NOT NULL,
subjectNo INT ( 4 ) NOT NULL,
examDate dateTime NOT NULL,
studentResult FLOAT ( 4, 1 ) NOT NULL 
)
   #PRIMARY KEY 主键
   #auto_increment  自动增长

#查询表
select * from tb_person

#修改表名
alter table person rename to tb_person;

#删除字段
alter table person drop phone;

#添加字段
alter table person add phone char(11);

#修改字段名
alter table person change number number bigint(20);

#删除表
drop table person

#查看表语句建
show create table person;

#添加主键
alter table person add primary key(number);

#添加组合主键
alter table result add constraint pk primary key result(studentNo,subjectNo,examDate);

#添加外键
alter table 表名 add constraint 外键名 foreign key (外键字段) references 关联表名(关联字段) 

#加入自动增加
alter table person modify number int auto_increment;

三、插入语句

#方式一：
insert into 表名(字段名,...) VALUES(值,...);
insert into results(numberNo,subjectNo,examDate,studentResult)values(1,001,'2021-06-26',99.5)
#方式二：
insert into person(number,name,sex,phone)values(1,'张三','男',15973946488),(2,'婷婷','女',19967204153)
#方式三：
INSERT INTO 表名 SET 字段名=值,字段名=值,...;

四、更新语句

1、单表更新
update 表名 set 列 = 值,... where 查询条件;

update person set phone = 15973946485 where number=1;

2、多表更新 
#方式一：
update 
  表1 别名,
  表2 别名 
set
  列 = 值,
  ...
where 连接条件 and 筛选条件 ;

#示例
update person p,results r  
   set
   p.`NAME`='婷婷' ,
   r.studentResult=99
   where p.number=r.numberNo
and r.subjectNo=1;

#方式二：
update 
  表1 别名 
inner | left | right join 表2 别名 on 连接条件
  SET 列 = 值,
  ...
WHERE 筛选条件 ;

#示例
 UPDATE person p
   LEFT JOIN results r ON p.number = r.numberNo 
   SET p.`NAME` = '张三' 
 WHERE
r.subjectNo = 1;

五、删除语句

1、单表删除 
DELETE FROM 表名 WHERE 筛选条件;
delete from person where number=1;

2、多表删除（级联删除）
DELETE 
  表1的别名,
  表2的别名 
FROM
  表1 别名,
  表2 别名 
WHERE 连接条件 AND 筛选条件 ;
   #示例
   DELETE p,r 
   FROM
  person p,
  results r 
   WHERE
  p.number = r.numberNo 
AND r.r_id =3

六、表的复制

#1、复制表的结构
CREATE TABLE 表名 LIKE 旧表;
CREATE TABLE person1 LIKE person;

#2、复制表的某些字段
CREATE TABLE 表名 
SELECT 字段1,字段2,... FROM 旧表 WHERE 0;

#示例
CREATE TABLE person2
 SELECT number,phone FROM person WHERE 0;
 
#3、复制表的结构+数据
CREATE TABLE 表名 
SELECT 查询列表 FROM 旧表 WHERE 筛选条件;

#示例
CREATE TABLE person3
SELECT * FROM person WHERE sex = '女';
#4、复制表的某些字段+数据
CREATE TABLE 表名 
SELECT 字段1,字段2,... FROM 旧表 WHERE 筛选条件;

七、表的查询

7.1基础查询

一、特点

1. 查询列表可以是字段、常量、函数、表达式
2. 查询结果是一个虚拟表

二、示例

1.查询所有
SELECT * FROM person;

2.查询单个字段
SELECT phone FROM person;

3.查询函数(求平均数)
SELECT AVG(studentResult) FORM results;

4.查询去重
SELECT DISTINCT 字段名 FROM 表名;

7.2条件查询

SELECT 查询列表 FROM 表名 WHERE 筛选条件;
#示例
SELECT SEX FROM PERSON WHERE PHONE='15973946488';

7.3排序查询

SELECT 
  查询列表 
FROM
  表 
【WHERE 筛选条件】
ORDER BY 排序列表 【asc | desc】 ;

7.4分组查询

SELECT 
  查询字段
FROM
  表 
【where 筛选条件】 
 GROUP BY 分组的字段一、字段二
【having 分组后的筛选】
【order BY 排序的字段一、字段二】 ;
   
#示例一、(group by分组前 用where)
SELECT r.studentResult, r.numberNo  FROM results r 
WHERE
r.numberNo=1
GROUP BY
r.numberNo,
r.studentResult 
ORDER BY r.studentResult, r.numberNo
   
#示例二、(group by分组后 用having)
SELECT r.studentResult, r.numberNo  FROM results r 
GROUP BY
r.numberNo,
r.studentResult 
HAVING
r.numberNo=1
ORDER BY r.studentResult, r.numberNo
  

7.5连接查询

一、内连接

1.1等值连接
#查寻学生姓名以及对应分数
SELECT
	p.`NAME` AS 姓名,
	r.studentResult AS 分数 
FROM
	person p,
	results r 
WHERE
	p.number = r.r_id
	
1.1.1 inner内连接
SELECT
	p.`NAME` AS 姓名,
	r.studentResult AS 分数 
FROM
	person p
inner join results r
on
	p.number = r.r_id
		
1.2自连接	
#查询学生对应老师的编号
#创建表
CREATE TABLE person8 (
	number INT ( 4 ) PRIMARY KEY auto_increment,
	NAME VARCHAR ( 50 ) NOT NULL,
	teacher INT ( 4 ),
	sex CHAR ( 2 ),
	phone char(11)
)
#插入数据	
insert into person8(number,name,teacher,sex,phone)value(1,'张三',2,'男',15973946488)
insert into person8(number,name,teacher,sex,phone)value(2,'老师',0,'男',15973946488)
insert into person8(number,name,teacher,sex,phone)value(3,'李四',2,'男',15973946488)	

SELECT
	t.`NAME`,
	t.teacher 
FROM
	person8 p,
	person8 t 
WHERE
	p.number = t.teacher

二、外连接

1.1左外连接：left 【outer】（左边的是主表）
SELECT
	p.`NAME` AS 姓名,
	r.studentResult AS 分数 
FROM
	person p
left join results r
on
	p.number = r.r_id
	
1.2右外连接：right 【outer】（右边的是主表）
#查询分数为0的学生
SELECT
p.`NAME` AS 姓名,
r.studentResult AS 分数 
FROM
person p
right join results r
on
p.number = r.numberNo
where r.studentResult=0;

二、全连接

全外连接：full 【outer】（两边都是主表，但是MySQL不支持全外连接、Oracle支持）因此采用 union all 
SELECT p.`NAME` AS 姓名,r.studentResult AS 分数 FROM person p LEFT JOIN results r ON p.number=r.r_id 
UNION ALL
SELECT p.`NAME` AS 姓名,r.studentResult AS 分数 FROM person p RIGHT JOIN results r ON p.number=r.numberNo

7.6子查询

特点

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
4. 标量子查询，一般搭配着单行操作符使用：>、>=、<、<=、!=、<>、=、<=>
5. 列子查询，一般搭配着多行操作符使用：in、not in、any、some、all、exits

1.1 查询分数最少的学生信息
SELECT r.numberNo,r.r_id,r.studentResult 
  FROM results r 
WHERE 
  r.studentResult=(SELECT min(studentResult) FROM results)

1.2 查询分数在90分以上的学生
  SELECT r.numberNo,r.r_id,r.studentResult 
  FROM results r 
  WHERE r.studentResult IN (
SELECT
d.studentResult 
FROM  results d 
WHERE d.studentResult > 90 
)	   

7.7分页查询

SELECT 
  查询列表 
FROM
  表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【WHERE 分组前的筛选】
【GROUP BY 分组字段】 
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT 【开始的行数】size(每页显示多少条) ;
#limit语句放在查询语句的最后
#示例
SELECT r.numberNo,r.studentResult FROM results r 
WHERE r.studentResult> 90 
GROUP BY r.numberNo,r.studentResult 
HAVING r.numberNo=1 ORDER BY r.numberNo,r.studentResult LIMIT 0,5;