初始mysql

登录数据库
1
mysql -uroot -proot
查看当前数据库的字段
1
SELECT * FROM chg_girlfriends
查看当前有哪些数据库
1
show databases;
创建数据库
1
creat database 数据库名字
选中数据库操作
1
use 数据库的名称
查看数据库的表
1
SHOW TABLES;

创建一个gril_friend的表

create table student(
    name  char(4) not null ,
    sex   enum('男','女'),
    id    char(11) primary key,
    class tinyint not null
);

查看某个数据表的结构
1
DESCRIBE STUDENT;
查看表中数据
1
SELECt * FROM student;

如何往数据表中添加数据记录

1 2	`INSERT INTO student VALUES('CHG','M','2',1);`

数据类型的选择

时间类型看格式
整型和字符串类型看长度

更新字段名

1	`ALTER TABLE student CHANGE COLUMN classNum cn TINYINT;`

创建约束

主键约束

不重复且不为空

创建一个主键：

1
2
3

CREATE TABLE test(
	test1 char PRIMARY KEY
);

创建联合主键（加起来不重复，任何一个都不能为空）：

CREATE TABLE test(
	test1 char,
	test2 char,
	PRIMARY KEY(test1,test2)
);

创建自增主键：

CREATE TABLE test(
	test1 char,
	test2 char,
	test3 TINYINT AUTO_INCREMENT,
	PRIMARY KEY(test1,test2,test3)
);

给表添加主键：

1 2	`alter TABLE test DROP PRIMARY KEY; ALTER TABLE test ADD PRIMARY KEY(test1,test2);`

删除主键：

1	`alter TABLE test DROP PRIMARY KEY;`

通过修改字段的方式增加主键(modify)：

1	`ALTER TABLE test MODIFY test1 TINYINT PRIMARY KEY;`

唯一约束

该字段的值不可以重复

alter添加
1
ALTER TABLE test ADD UNIQUE(test1,test2);
非空约束
1
not null
默认约束
1
default 默认值
传了值就不会使用默认值

外键

CREATE TABLE class (
        name char(10) PRIMARY KEY,
        class_id TINYINT DEFAULT 4,
        student_name char(4),
        FOREIGN KEY (student_name) REFERENCES student(name)
    );

范式
- 第一范式（1NF）
  
  数据表中的所有的字段都是不可分割的原子值
  
  就是满足填入的数据不可以拆分
- 第二范式(2NF)
  
  必须是满足第一范式的前提下
  
  一个表的所有列最好都和主键相关，不想关的拆解成其他表
- 第三范式(3NF)
  
  满足第二范式
  
  非主键不存在相关性

查询

单表查询

数据准备

CREATE DATABASE mysql_test;
   
USE mysql_test;
   
CREATE TABLE
    Student(
        sno VARCHAR(20) PRIMARY KEY,
        sname VARCHAR(20) NOT NULL,
        ssex VARCHAR(10) NOT NULL,
        sbirthday DATETIME NOT NULL,
        class VARCHAR(20) NOT NULL
    );
   
DROP TABLE student;
   
-- 提前准备
   
CREATE TABLE
    Teacher(
        tno VARCHAR(20) PRIMARY KEY,
        tname VARCHAR(20) NOT NULL,
        tsex VARCHAR(10) NOT NULL,
        tbirthday DATETIME,
        prof VARCHAR(20) NOT NULL,
        depart VARCHAR(20) NOT NULL
    );
   
CREATE TABLE
    Course(
        cno VARCHAR(20) PRIMARY KEY,
        cname VARCHAR(20) NOT NULL,
        tno VARCHAR(20) NOT NULL,
        FOREIGN KEY(tno) REFERENCES teacher(tno)
    )
   
DROP TABLE score;
   
CREATE TABLE
    score(
        sno VARCHAR(20) NOT NULL,
        cno VARCHAR(20) NOT NULL,
        degree DECIMAL NOT NULL,
        FOREIGN KEY(sno) REFERENCES Student(sno),
        FOREIGN KEY(cno) REFERENCES Course(con),
        PRIMARY KEY(sno, cno)
    );
   
-- 添加学生表的数据
   
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
   
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
   
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
   
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
   
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
   
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
   
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
   
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
   
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
   
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
   
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
   
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
   
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
   
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
   
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
   
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
   
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
   
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
   
INSERT INTO teacher
VALUES (
        '804',
        '李诚',
        '男',
        '1958-12-02',
        '副教授',
        '计算机系'
    );
   
INSERT INTO teacher
VALUES (
        '856',
        '张旭',
        '男',
        '1969-03-12',
        '讲师',
        '电子工程系'
    );
   
INSERT INTO teacher
VALUES (
        '825',
        '王萍',
        '女',
        '1972-05-05',
        '助教',
        '计算机系'
    );
   
INSERT INTO teacher
VALUES (
        '831',
        '刘冰',
        '女',
        '1977-08-14',
        '助教',
        '电子工程系'
    );
   
show tables;
   
select * from teacher;
   
INSERT INTO course VALUES('3-105', '计算机导论', '825');
   
INSERT INTO course VALUES('3-245', '操作系统', '804');
   
INSERT INTO course VALUES('6-166', '数字电路', '856');
   
INSERT INTO course VALUES('9-888', '高等数学', '831');
   
-- 存入数据
   
INSERT INTO score VALUES('103', '3-105', '92');
   
INSERT INTO score VALUES('103', '3-245', '86');
   
INSERT INTO score VALUES('103', '6-166', '85');
   
INSERT INTO score VALUES('105', '3-105', '88');
   
INSERT INTO score VALUES('105', '3-245', '75');
   
INSERT INTO score VALUES('105', '6-166', '79');
   
INSERT INTO score VALUES('109', '3-105', '76');
   
INSERT INTO score VALUES('109', '3-245', '68');
   
INSERT INTO score VALUES('109', '6-166', '81');
   
SELECT * FROM score;

查询student 表的所有记录
1
select * from student;
查询指定的列
1
2
select sname,sno,ssex from student;

查询某一列的所有的值

-- 查询教师所有的单位

-- 重复

SELECT depart from teacher;

-- 不重复

SELECT DISTINCT depart from teacher;

使用where 作为条件查询


SELECT * FROM score WHERE DEGREE BETWEEN 60 and 80;

SELECT * FROM score WHERE DEGREE >60 and DEGREE <80;

关系查找

1 2	`-- 表示或者关系的查询 SELECT * FROM score WHERE degree in(86,88); -- 表示范围`

多条件查找

1
2
3

-- 使用两个条件查询

SELECT * FROM student WHERE class=95031 AND ssex='女';

升序降序查找

-- 升序降序查表


-- 升序查表
SELECT * FROM student ORDER BY class ASC;

-- 降序查找
SELECT * FROM student ORDER BY class DESC;

-- 默认升序


# 以班级和年龄从大到小排序查询student 表中全部的记录

SELECT * FROM student ORDER BY class DESC,sbirthday;

1 2	`SELECT * FROM score ORDER BY cno ASC,DEGREE DESC;`

查询’95031’班的人数

1
2
3

-- 统计人数

SELECT COUNT(*) FROM student WHERE class='95031';

子查询


-- 统计最高分

SELECT * FROM score WHERE degree=(SELECT MAX(degree) FROM score);

计算不同组的平均数

-- 计算平均成绩
-- 计算某一个特定的值的平均数
SELECT AVG(DEGREE) FROM score where cno ='3-105';


-- 计算组平均值
SELECT cno,AVG(DEGREE) FROM score GROUP BY cno;

分组指定组内成员的数量，使用like匹配

-- 计算score 表中至少有两名学生选修，并以3开头的ban'j平均数

SELECT
    cno,
    AVG(DEGREE),
    COUNT(*)
FROM score
GROUP BY cno
HAVING
    COUNT(cno) >= 2
    AND cno LIKE '3%';

带条件的查询

-- 查询分数大于70，小于90的sno列

SELECT sno,DEGREE FROM score WHERE degree>70 and degree<90;

SELECT sno FROM score WHERE degree BETWEEN 70 AND 90;

分组后条件查询

    
# 查询至少有两名男生的班号
    
SELECT class
FROM student
WHERE ssex='男' GROUP BY class HAVING COUNT(*) >1;

日期求最大最小

1
2
3

# 查询student 中最大的sbirthday和最小

SELECT MAX(sbirthday)-MIN(sbirthday) FROM student;

多表查询

第一个


SELECT
    student.sno,
    student.sname,
    score.cno,
    score.degree
from student
    JOIN score ON student.sno = score.sno;

# 查询所有学生的son,cname,degree

SELECT
    score.sno,
    course.cname,
    score.degree
FROM score
    JOIN course ON course.cno = score.cno;

隐式join和显示，建议使用显示的，隐式的不太清楚

# 查询所有的学生的sname,cname,degree

SELECT
    student.sname,
    course.cname,
    score.degree
FROM student
    JOIN score ON student.sno = score.sno # 这两个不一样，也不能是student
    JOIN course ON score.cno = course.cno;


SELECT sname, cname, degree
FROM student, score, course
WHERE
    student.sno = score.sno
    and score.cno = course.cno;

as起别名

SELECT
    student.sname as stu_name,
    course.cname,
    score.degree,
    score.cno as sco_con
FROM student
    JOIN score ON student.sno = score.sno
    JOIN course ON score.cno = course.cno;

子查询

使用in计算一类

# 查询95031班，每个班的平均分

SELECT AVG(degree)
from score
where sno in (
        SELECT sno
        from student
        WHERE class = '95031'
    ) 
GROUP BY cno; # 根据不同的学科分类



# 查询所有教师的tname 和 depart

SELECT tname, depart
FROM teacher
WHERE tno IN (
        SELECT tno
        FROM course
    );
    
    
    
 # 查询男教师并且他们所上的课

SELECT cno, cname
FROM course
WHERE tno in (
        SELECT tno
        FROM teacher
        WHERE tsex = '男'
    );

子查询做限定

SELECT *
from score
WHERE degree > (
        SELECT degree
        FROM score
        WHERE sno = 109 AND cno='3-105'
    );
    
# 查询3-105课程的成绩高于 109 同学 3-105 成绩的所有同学的记录
SELECT *
FROM score
WHERE cno = '3-105' and degree > (
        SELECT degree
        FROM score
        WHERE
            sno = 109
            AND cno = '3-105'
    );
    

# 查询成绩高于学号 109 课程号为 3-105 的成绩的所有记录

SELECT *
FROM score
WHERE cno = '3-105' and degree > (
        SELECT degree
        FROM score
        WHERE
            sno = 109
            and cno = '3-105'
    );

year 函数和 in 做限制条件

# 查询和学号 108 101 的同学同年出生的所有学生的sno sname 和 sbirthday

SELECT *
FROM student
WHERE YEAR(sbirthday) in(
        SELECT
            YEAR(sbirthday)
        FROM student
        WHERE
            sno in(108, 101)
    );

嵌套子查询

# 查询 张旭 老师任课学生的成绩

SELECT *
FROM score
WHERE cno in (
        SELECT cno
        FROM course
        WHERE tno = (
                SELECT tno
                FROM teacher
                WHERE tname = "张旭"
            )
    );

多表查询plus

# 查询选修某课程的同学人数多于5人的教师名字

SELECT tname
FROM teacher
WHERE tno = (
        SELECT tno
        FROM course
        WHERE cno = (
                SELECT cno
                FROM score
                GROUP BY cno
                HAVING
                    COUNT(*) > 5
            )
    );

any

任意一个

# 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245"的同学的Cn0、Sno和Deg re并按Deg ree从高到低次序排序。

SELECT *
FROM score
WHERE
    cno = "3-105"
    and degree > ANY(
        SELECT degree
        FROM score
        WHERE cno = '3-245'
    )
ORDER BY degree DESC; 
# ASC 是升序

all

# 查询选修编号为“3-105”且成绩高于选修编号为“3-245"课程的同学的Cn0、Sno和Degree.

SELECT cno, sno, degree
FROM score
WHERE
    cno = '3-105'
    AND degree > all(
        SELECT degree
        FROM score
        WHERE cno = '3-245'
    )
ORDER BY degree;

查询练习

in

1
2
3

# 查询95033 or  95031的学生

SELECT * from student WHERE class in("95033","95031");

where

# 查询成绩在85以上的con

SELECT sno, cno,degree FROM score WHERE degree > 85;

多表查询

# 查询计算机系老师所教的课程表

SELECT *
FROM course
    JOIN teacher ON course.tno = teacher.tno
WHERE
    teacher.depart like "计算机%";

select * FROM course WHERE tno in (SELECT tno FROM teacher WHERE depart LIKE "计算机_");

union和nion

# 查询 计算机系  与  电子工程系  不同职称的教师的tname  prof
# union求并集
SELECT tname, prof
FROM teacher
WHERE
    depart LIKE "电子%"
    AND prof NOT IN(
        SELECT DISTINCT prof
        FROM teacher
        WHERE
            depart like "计算机%"
    )
UNION
SELECT tname, prof
FROM teacher
WHERE
    depart LIKE "计算%"
    AND prof NOT IN(
        SELECT DISTINCT prof
        FROM teacher
        WHERE
            depart like "电子%"
    );

查询多张表的类似元素

# 查询所有学生和老师的的name sex birthday
SELECT
    sname as name,
    ssex as sex,
    sbirthday as birthday
from student
UNION
SELECT
    tname,
    tsex,
    tbirthday # 后面可以不起别名
FROM teacher;


# 查询所有女老师和女学生的name  asex  birthday

SELECT
    sname as name,
    ssex as sex,
    sbirthday as birthday
FROM student
WHERE student.ssex = "女"
UNION
SELECT tname, tsex, tbirthday
FROM teacher
WHERE tsex = "女";

复制表数据做查询语句

#查询成绩比平均成绩低的同学的学号、姓名、成绩

SELECT cno,AVG(degree) FROM score GROUP BY cno;

# 可以求出平均数

SELECT * FROM score;

# 可以找出所有的信息

SELECT *
FROM score a
WHERE degree < (
        SELECT AVG(degree)
        FROM score b
        WHERE a.cno = b.cno
    );

year()

# 查询student 的年龄 和姓名

# 注意年龄需要  现在的日期-出生日期

select sname,YEAR(NOW())-YEAR(sbirthday) from student;

sql连接查询

数据准备

CREATE table person( id INT, name VARCHAR(20),cardId int);

CREATE TABLE card( id INT, name VARCHAR(20) );

SHOW TABLES;

INSERT INTO card(id, name)
VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');

SELECT * FROM card;

INSERT INTO
    person(id, name, `cardId`)
VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);

SELECT * FROM person;

-- 这里没有创建外键
-- 而且person里面的有一个card并不存在于caard表中

内连接

查询的是多个表中有关系的数据

inner join
join

1	`SELECT * FROM card INNER JOIN person ON person.cardId=card.id;`

外连接

左连接

left join
left outer join

-- 左连接

-- 会把左边的表全部数据取出来，右边的表有就查询出来，没有的话就NULL

SELECT * FROM person LEFT JOIN card ON person.`cardId`=card.id;

右链接

right join
right outer join

-- 右连接

-- 会把右边的表 全部数据取出来，左边的表有就查询没有的话就为NULL

SELECT * FROM person RIGHT JOIN card ON person.`cardId`=card.id;

完全外连接

full join
full outer join

但是mysql不支持full 连接的方式，但是额可以使用union 左右连接发现

SELECT *
FROM person
    RIGHT JOIN card ON person.`cardId` = card.id
UNION
SELECT *
FROM person
    LEFT JOIN card ON person.`cardId` = card.id;

事务

保证一个业务的完整性

多条sql语句同时进行，不允许失败

事务是否开启
1
select @@autocommot;
1 则为开启（默认)
事务开启的作用

我们执行一条sql语句的时候会立马知道结果，而且不能回滚（不能撤销）
如何控制事务？
- 默认开启事务
- rollback;语句是回滚

索引和视图

索引

主键索引

就是使用主键排序放置的
查找思想就是
- 每四条数据为一页
  
  第一页和左后一页是5条数据，因为最大值和最小值
  
  通过单向链表连接
- 每一页数据中最大的数据被称为槽
  
  槽的遍历是二分查找法实现

B+树索引（普通索引，二级索引）

将槽变为数据页
多级目录

视图

创建视图
1

存储过程

就是一个变成过程

sql脚本可以编译为：存储过程，函数，触发器

#MYSQL

mysql_fast_learning

https://tsy244.github.io/2023/09/22/MYSQL/mysql-fast-learning/

Author

August Rosenberg

Posted on

September 22, 2023

Licensed under

django Previous

python学习计划 Next