Mysql的右外连接、全连接和视图、索引

笔记自学部分整理

– 创建表的语句

CREATE TABLE student_info1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code CHAR(2) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL DEFAULT 'zhangsan',
    age INT(12) NOT NULL,
    weight DECIMAL(10, 2),
    birthday DATE NOT NULL,
    in_school DATETIME NOT NULL,
    description TEXT
)

CREATE TABLE student_account1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    account VARCHAR(20),
    password VARCHAR(20),
    CONSTRAINT FK_SI_SA_01 FOREIGN KEY (student_id) REFERENCES student_info1(id)
)

– 删除表

DROP TABLE student_info1;
DROP TABLE IF EXISTS student_account1；
CREATE TABLE student_account1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    account VARCHAR(20),
    password VARCHAR(20),
    CONSTRAINT FK_SI_SA_01 FOREIGN KEY (student_id) REFERENCES student_info1(id)
)

– 修改表

ALTER TABLEstudent_info1 RENAME student_info2;

– 创建表
DROP TABLE IF EXISTS company;
CREATE TABLE company(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
number VARCHAR(255) NOT NULL,
age INT,
groupNum VARCHAR(11)，
salary VARCHAR(255) NOT NULL
)AUTO_INCREMENT = 1;

– 插入数据 让查询有数据

insert into  company(id,name,number,age,groupNum,salary) values(id,'刘三','01',20,1,40);

insert into company(id,name,number,age,groupNum,salary) values(id,'李三','02',20,1,35);

insert into company(id,name,number,age,groupNum,salary) values(id,'徐四','06',20,2，50);

insert into company(id,name,number,age,groupNum,salary) values(id,'张三','07',20,2,60);

insert into company(id,name,number,age,groupNum,salary) values(id,'王二','14',20,3,25);

– 统计数据查询

SELECT AVG(salary) AS '平均薪水',MAX(salary) AS '最高薪水',MIN(salary) AS '最低薪水',
SUM(salary) AS '薪水总和',COUNT(id) AS '总记录数'
FROM company
where groupNum=3;

SELECT groupNum,GROUP_CONCAT(name) AS '组员',AVG(salary) AS '平均薪水'
FROM company
where groupNum IS NOT NULL 
GROUP BY groupNum;

自学内容：右外连接、全连接

– 先创建表

CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
code VARCHAR(255),
idcard VARCHAR(255),
classs_id INT
)

CREATE TABLE classs (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
code VARCHAR(255)
)

CREATE TABLE course (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
code VARCHAR(255),
diff int
)

CREATE TABLE student_course(
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT
)

CREATE TABLE account(
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
username VARCHAR(255),
password VARCHAR(255)
)

– 插入数据
INSERT INTO student (id,name,code,idcard,classs_id)
VALUES (‘1’,’张三’,’1’,’111’,’96’)

INSERT INTO student (id,name,code,idcard,classs_id)
VALUES (‘2’,’李四’,’1’,’222’,’96’)

INSERT INTO student (id,name,code,idcard,classs_id)
VALUES (‘3’,’王五’,’1’,’333’,’93’)

INSERT INTO classs (id,name,code)
VALUES (‘1’,’JAVA96班’,’96’)

INSERT INTO classs (id,name,code)
VALUES (‘2’,’JAVA93班’,’93’)

INSERT INTO classs (id,name,code)
VALUES (‘3’,’JAVA98班’,’98’)

– 右外连接

SELECT 
cl.name as '班级名称',
s.`code` as '学号',
s.`name` as '姓名', 
a.username as '账号',
a.password as '账号密码',
c.`name` as '课程名称'
FROM student_course sc
RIGHT JOIN student s ON sc.student_id = s.id
RIGHT JOIN course c on sc.course_id = c.id
RIGHT JOIN clazz cl on s.clazz_id = cl.id
RIGHT JOIN account a on a.student_id = s.id

SELECT 
s.`code` as '学号',
s.`name` as '姓名', 
FROM student s
RIGHT JOIN student_course sc ON sc.student_id = s.id

– 全连接

SELECT 
cl.name as '班级名称',
s.`code` as '学号',
s.`name` as '姓名', 
a.username as '账号',
a.password as '账号密码',
c.`name` as '课程名称'
FROM student_course sc
FULL JOIN student s ON sc.student_id = s.id
FULL JOIN course c on sc.course_id = c.id
FULL JOIN clazz cl on s.clazz_id = cl.id
FULL JOIN account a on a.student_id = s.id

自学内容：数据库的视图和索引

数据库的视图

如果视图中引用的表，其结构发生变化（如字段名称变化，字段被删除等情况）时，要注意视图是否需要

– CREATE VIEW v_xxx AS SQL语句
CREATE VIEW view_s AS
SELECT code,name
FROM student
WHERE id=2

数据库的索引

索引就类似的书的目录，能够在一定程度上快速的进行数据的定位。
能够加快数据检索的速度。
一旦建立了索引，在数据进行插入或更新时，就会去更新索引结构，带来一定效率损耗。而且因为加了索引会多占用一定的磁盘空间。
我们一般是在需要建立查询条件的字段上创建索引。
而且索引也可以定义在多个字段上面的。

-- CREATE INDEX index_name ON table_name(field1,field2,...)

CREATE INDEX index_S ON student(code)