入门-MySQL

内容纲要

MySQL 学习笔记

计划

  1. 如何使用终端操作

  2. 如何使用可视化工具操作

  3. 如何在编程语言中操作数据库

第一章:终端打开MySQL

MacOS下

  1. terminal下打开Mysql:

    cd /usr/local/mysql/bin
    ls
    ./mysql -u root -p 
  2. 添加环境变量,便于使用

    touch .bash_profile 创建用户环境变量文件
    vim .bash_profile
  3. 编辑内容,在最后面部分添加

    PATH=$PATH:/usr/local/mysql/bin

    添加完成后,按esc键,然后输入:wq (shift+z double)进行保存退出。

  4. 在主目录 生效环境变量

    source ~/.bash_profile
  5. 以后打开只需要在terminal

    mysql -u root -p

第二章:数据库基本操作

  1. 登陆数据库 (mysql -u -p)
    source ~/.bash_profile
    mysql -u root -p

  2. 查看数据库服务器上的所有数据库 或 数据库中的数据表 (show)
    show databases;
    show databases;

  3. 如何选中并操作某个数据库 (use)

    use sys;
    mysql> select * from 2017_students where 序号 < 10 limit 5;
    +--------+-------------+-----------+--------+
    | 序号   | 学号        | 姓名      | 性别   |
    +--------+-------------+-----------+--------+
    |      1 | 20155093126 | 夏滨洲    | 男     |
    |      2 | 20175083029 | 柳露露    | 女     |
    |      3 | 20175085042 | 李晓博    | 女     |
    |      4 | 20175101001 | 陈浩鹏    | 男     |
    |      5 | 20175101002 | 杨德森    | 男     |
    +--------+-------------+-----------+--------+
    5 rows in set (0.01 sec)

    登陆用户数据库服务器(root) -> 选择数据库(sys) -> 选择数据表(2017_students)

  4. 如何创建数据库 (create)
    CREATE DATABASE test;
    show databases;

  5. 如何创建数据表 (create)

    CREATE TABLE pet(
        name VARCHAR(20),
        owner VARCHAR(20),
        sex CHAR(1)
    );
  6. 如何删除数据表 (DROP)
    DROP TABLE users;

  7. 查看数据表结构 (desc 或 describe)

    mysql> describe pet;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(20) | YES  |     | NULL    |       |
    | owner | varchar(20) | YES  |     | NULL    |       |
    | sex   | char(1)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
  8. 如何退出数据库服务器 (exit)
    exit

  9. 增删改查(INSERT INTO)

    1. INSERT INTO pet VALUES("James","RJman","m");
      INSERT INTO pet VALUES("Tom","Lee","m");
      INSERT INTO pet VALUES("Jery","Joe","f");
      INSERT INTO pet VALUES("Mark","Sun","f");
    2. DELETE FROM pet WHERE owner = 'RJman';
    3. UPDATE pet SET name = "Jack" WHERE OWNER = "Joe";
    4. SELECT * FROM pet;

第四章:MySQL常用数据字段类型

常用数据类型_菜鸟教程

  1. 数值
    1. 整数型(字节)
      TINEINT(1)、SMALLINT(2)、MEDIUMINT(3)、INT/INTEGER(4)、BIGINT(8)
    2. 浮点数(字节)
      FLOAT(4)、DOUBLE(8)DECIMAL
  2. 日期/时间(字节)
    • DATE 3 YYYY-MM-DD 日期值
    • TIME 3 HH:MM:SS 时间值或持续时间
    • YEAR 1 YYYY 年份值
    • DATETIME YYYY-MM-DD HH:MM:SS 混合
    • TIMESTAMP 4 时间戳
  3. 字符串类型
    • CHAR 定长字符串
    • VARCHAR 变长字符串
    • TINYBLOB 短二进制字符串
    • MEDIUMBLOB 中长进制字符串
    • BLOB 长二进制字符串
    • LONGBLOB 极长二进制字符串
    • TINYTEXT 短文本
    • MEDUIMTEXT 中长文本
    • TEXT 长文本
    • LONGTEXT 极长文本

第五章:MySqL建表约束

  1. 主键约束(primary key)
    能够唯一确定一张表中的一条记录,字段值不重复且不为(NULL)
    联合主键:主键为多个字段,要求字段值不同时重复或为空(NULL)

    CREATE TABLE users(
        id int,
        name VARCHAR(20),
        PRIMARY KEY(id)
    );
    mysql> describe users;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | NO   | PRI | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

    建表后添加和删除主键约束 (ALTER)

    --- 添加主键 add
    ALTER TABLE pet add PRIMARY KEY(name);
    desc pet;
    --- 删除主键 drop
    ALTER TABLE pet DROP PRIMARY KEY;
    desc pet;
    --- 修改字段 modify
    ALTER TABLE pet MODIFY name VARCHAR(20) PRIMARY KEY;
  2. 自增约束(auto_increment)
    添加记录时,当设定的自增字段没有传入值时,可以自增

  3. 外键约束
    涉及到两个表(父表,子表 或 主表,副表)

    --- 主表room
    CREATE TABLE room(
        id int PRIMARY KEY,
        building int
    );
    --- 副表users
    CREATE TABLE users(
        id int PRIMARY KEY,
        name VARCHAR(20),
        dormitory int,
        FOREIGN KEY(dormitory) REFERENCES room(id)
    );
    1. 副表的字段内容参照主表,则副表无法使用主表不存在的值
    2. 主表中的记录被副表引用,则主表记录不能被删除
    3. 无法创建外键的原因:
      • 字段类型或大小是否严格一致
      • 试图设置外键的字段没有建立起索引,或者不是一个primary key(主键)
      • 检查表的引擎类型是否一致
      • 外能键的名字不重复
      • 可能设置为外键设置了一个默认值,如default=0
      • 语法错误等...
  4. 唯一约束(unique)
    修饰的字段值不能重复
    当多个字段添加唯一约束时,类似联合主键,字段值不能同时重复
    ALTER TABLE pet MODIFY name VARCHAR(20) UNIQUE;
    删除唯一约束
    ALTER TABLE pet drop index owner;

  5. 非空约束
    修饰的字段值不能为空(NULL)
    ALTER TABLE pet MODIFY name VARCHAR(20) not null;
    注意
    当没有创建主键约束时,若对已经添加唯一约束的字段添加非空约束
    则该字段自动升级为主键约束

  6. 默认约束
    当插入字段值时,如果没有传值,则会使用默认值
    CREATE TABLE users(age int default 0);

第六章:数据库的三大设计范式

  1. 第一范式 1NF

    • 数据表中所有字段都是不可分割的原子值域
    • 如地址字段,若其内容客观上还可以分割,不符合第一范式
  2. 第二范式 2NF

    • 前提:满足第一范式
    • 除主键外的每一列都必须完全依赖于主键
    • 如果不完全依赖,只可能发生在联合主键的情况
    • 如订单表

      create table myorder(
          product_id INT,
          customer_id INT,
          product_name varchar(20),
          customer_name varchar(20),
          PRIMARY KEY(product_id,customer_id) 
      );

    ---问题?
    ---除主键外其它列,主依赖于主键的部分字段
    ---解决方法->拆表

    create table myorder(
        order_id INT PRIMARY KEY,
        product_id INT,
        customer_id INT
    );
    CREATE TABLE product(
        id INT PRIMARY KEY,
        name VARCHAR(20)
    );
    CREATE TABLE customer(
        id INT PRIMARY KEY,
        name VARCHAR(20)
    );
  3. 第三范式 3NF

    • 前提:满足第二范式
    • 除开主键外的其它列不能有传递依赖关系(删除冗余)

第一范式1NF:字段不可分割
第二范式2NF:1NF+除主键外其他列完全依赖主键
第三范式3NF:2NF+除主键外其它列不能有传递依赖关系
BCNF

第七章:数据查询

数据查询一般格式:

SELECT [ALL/DISTINCT]<目标表达式>[,<目标表达式2>]...
FROM <表名或视图名>[,<表名或视图名>]... | (<新的SELECT语句>)[AS]<别名>
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]];

单表查询

选择表的若干列

  1. 查询指定列
  2. 查询全部列
  3. 查询经过计算的值(表达式)

    SELECT Sname,2019-Sage Birth    --Birth为定义的别名
    FROM Student;

选择表中的若干元组

  1. 消除取值重复的行(SELECT DISTINCT)
  2. 查询满足条件的元组

    • 比较大小(=,>,<,>=,<=,!=<>,!>不大于,!<不小于)
    • 确定范围([NOT] BWTEEN AND)
    • 确定集合([NOT] IN)
    • 字符匹配([NOT] LIKE'<匹配串> [ESCAPE'<换码字符>'],通配符% _ *)

      --查询名字中第二个字为不为 _ 的学生姓名及其学号
      SELECT Sname,Sno
      FROM Student
      WHERE Sname NOT LIKE '_\_%'; -- 默认的ESCAPE 换码字符为'\'

      数据库字符集为ASCII时,一个汉字需要两个_;当字符集为GBK时只需要一个

    • 涉及空值的查询(IS [NOT] NULL)
    • 多重条件查询(AND,OR,AND 优先级高于 OR)

ORDER BY子句

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)和降序(DESC),默认升序,对于空值,排序时显示的次序由系统实现来决定

SELECT Sno
FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC

聚集函数

  1. COUNT(*) 统计元组个数
  2. COUNT([DISTINCT|ALL]<列名>) 统计一列中值的个数
  3. SUM([DISTINCT|ALL]<列名>) 计算一列值的总和(必须为数值类型)
  4. AVG([DISTINCT|ALL]<列名>) 计算一列值的平均值(必须为数值类型)
  5. MAX([DISTINCT|ALL]<列名>) 求一列值中的最大值
  6. MIN([DISTINCT|ALL]<列名>) 求一列值中的最小值
  7. 实例

    --查询选修了课程的学生人数
    SELECT COUNT(DISTINCT Sno)
    FROM SC;
    --查询选修了1号课程的学生平均成绩
    SELECT AVG(Grade) as avg_score
    FROM SC
    WHERE Cno = '1';

聚集函数遇到空值都跳过处理(COUNT(*)计算元组个数除外)
聚集函数只能用于SELECT子句或者GROUP BY 中的 HAVING子句
WHERE子句不能用聚集函数作为条件表达式

GROUP BY子句

-- 求各位学生学号及相应的选课数量
SELECT Sno,COUNT(Cno)
FROM SC
GROUP BY Sno;
-- 查询选修了三门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(Cno) > 3;

WHERE子句 与 HAVING短语的区别在于作用对象不同
WHERE子句作用于基本表或视图,从中选择满足条件的元组
HAVING短语作用于,从中选择满足条件的

连接查询(多表查询)

等值与非等值连接查询

连接查询中的WHERE子句中,用来连接两个表的条件成为连接条件连接谓词,一般格式为:
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
或者
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
连接名词中的列名称为连接字段(连接字段类型必须是可比的,名字不必相同)

-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno = SC.Sno AND
        SC.Cno = '2' AND SC.Grade > 90;

等值连接中去除重复列即为自然连接

自身连接

为表取两个别名FIRSTSECOND

-- 查询每一门课的间接先行课
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cno = SECOND.Cpno

外连接

多表连接

嵌套查询

嵌套查询可以由多个简单查询构成复杂的查询

带有IN谓词的子查询

带有比较运算符的子查询

  1. 子查询结果为一个值时可以用 = 代替 IN
  2. 如果查询条件依赖于父查询,这类子查询称为相关子查询

    --找出每位学生超出他自己选修课平均成绩的课程号及课程成绩
    SELECT Sno,Cno,Grade
    FROM SC x
    WHERE Grade >= (
            SELSECT AVG(Grade)
            FROM SC y
            WHERE y.Sno = x.Sno
            )

带有ANY(SOME) 或 ALL 谓词的子查询(一般可以用聚集函数替代)

带有[NOT] EXISTS谓词的子查询

[NOT] EXISTS 表示存在量词,带有EXISTS谓词的子查询不返回任何数据,只产生逻辑值TRUEFALSE

-- 查询选修了全部课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS(
    SELECT *
    FROM Course
    WHERE NOT EXISTS(
        SELECT *
        FROM SC
        WHERE Sno = Student.Sno
            AND Cno = Course.Cno
        )
    )

集合查询

主要的基本操作:UNION(并),INTERSECT(交),EXCEPT(差)

SELECT SNo
FROM SC
WHERE Cno = '1'
INTERSECT
SELECT Sno
FROM SC
WHERER Cno = '2'

基于派生表的查询

子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中
这时子查询生成的临时派生表(derived table)成为主查询的查询对象

SELECT Sno
FROM Student,
    (SELECT Sno,Cno FROM SC GROUP BY Sno) 
    AS new(new_Sno,new_Cno)
WHERE Student.Sno = new.new_Sno;

通过FROM子句生成派生表时,AS关键字可以省略,但必须为派生关系制定一个别名

第八章:数据库查询练习

第一部分:

AND,OR,IN 关系运算
DISTINCT,LIKE 去重、相似字符
max,sum,count,year(now()) 基础函数部分
ASC、DESC 排序
子查询
第二部分:

  1. 创建源数据表
    数据准备源码

    • student学生表(Sno,Sname,Ssex,S)

      CREATE TABLE student(
          Sno VARCHAR(20) PRIMARY KEY,
          Sname VARCHAR(20) NOT NULL,
          Ssex VARCHAR(10) NOT NULL,
          Sbirth DATETIME,
          Sclass VARCHAR(20)
      );
    • teacher教师表(Tno,Tname,Tsex,Tbirth,T,Tdept)

      CREATE TABLE teacher(
          Tno VARCHAR(20) PRIMARY KEY,
          Tname VARCHAR(10) NOT NULL,
          Tsex VARCHAR(10) NOT NULL,
          Prof VARCHAR(20) NOT NULL,
          Tdept VARCHAR(20) NOT NULL
      );
    • course课程表(Cno,Cname,Tno)

      CREATE TABLE course(
          Cno VARCHAR(20) PRIMARY KEY,
          Cname VARCHAR(20) NOT NULL,
          Tno VARCHAR(20) NOT NULL,
          FOREIGN KEY(Tno) REFERENCES teacher(Tno)
      );
    • score成绩表(Sno,Cno,Grade)

      CREATE TABLE score(
          id int PRIMARY KEY,
          Sno VARCHAR(20),
          Cno VARCHAR(20) NOT NULL,
          Grade DECIMAL,
          FOREIGN KEY(Sno) REFERENCES student(Sno),
          FOREIGN KEY(Cno) REFERENCES course(Cno)
      );
  2. 基础查询

    • 查询出生在1990-2000年之间的学生 (BETWEEN、AND)

      SELECT * from student 
      WHERE Date(Sbirth) BETWEEN '1990-01-01' AND '2000-01-01';
      
      +-----+-------+------+---------------------+--------+
      | Sno | Sname | Ssex | Sbirth              | Sclass |
      +-----+-------+------+---------------------+--------+
      | 101 | RJman | m    | 1999-06-18 00:00:00 | rg     |
      | 102 | Joe   | m    | 1999-02-02 00:00:00 | xg     |
      | 103 | Lee   | m    | 1998-05-06 00:00:00 | xg     |
      | 104 | Sun   | m    | 1997-07-18 00:00:00 | xg     |
      | 666 | ZTZ   | f    | 1991-09-15 00:00:00 | bzmh   |
      +-----+-------+------+---------------------+--------+
      5 rows in set (0.00 sec)
      
    • 查询班级在xg,rg的学生数量 (IN);

      SELECT count(*) FROM student WHERE Sclass in('xg','rg');
    • 查询Head系和Tail系的教师 (OR)

      SELECT * FROM teacher WHERE Tdept = 'Head' OR Tdept = 'Tail';
    • 查询所有班级即不重复的班级值 (DISTINCT),去重复值

      SELECT DISTINCT Sclass FROM student;
      
      +--------+
      | Sclass |
      +--------+
      | rg     |
      | xg     |
      | bzmh   |
      +--------+
      3 rows in set (0.00 sec)
    • 查询学生姓名并设定当前年份为其出生年份 (YEAR(NOW()))

      SELECT Sname,YEAR(NOW()) as BirthYear FROM student;
    • 查询学生获得的总分数和 (sum)

      SELECT sum(Grade) as 总分数 FROM course;
    • 查询student数据表中男生的数量 (count)

      SELECT count(*) as 男生数量 FROM student WHERE Ssex = 'm';  
    • 查询Sname包含_字符的记录 (LIKE,*,%,)

      SELECT * from student WHERE Sname like '%\_%';
    • 以Sno学号升/降序的方式查询student数据表 (ASC,DESC)

      SELECT * FROM student ORDER BY Sno ASC;
      SELECT * FROM student ORDER BY Sno DESC;
    • 以Sage年龄升序、Sno学号降序来查询student数据表 (优先级)
      ASC作为默认值一般可不写,但在使用两个字段以上排序时必须要写

      SELECT * FROM student ORDER BY Sname ASC,Sno DESC;
    • 查询出生年份最大的学生学号和姓名 (子查询或者排序)

      1. 子查询
      SELECT Sno,Sname FROM student
      WHERE Sbirth = Date((SELECT max(Date(Sbirth)) FROM student));
      2. 排序
      SELECT Sno,Sname FROM student
      ORDER BY Sbirth DESC
      LIMIT 0,1;
      
      +-----+-------+
      | Sno | Sname |
      +-----+-------+
      | 101 | RJman |
      +-----+-------+
  3. 进阶查询

    • 查询score表中所有学生的平均成绩 (GROUP BY)
    SELECT Sno,avg(Grade) as 平均成绩 FROM score GROUP BY Sno;
    
    +------+--------------+
    | Sno  | 平均成绩     |
    +------+--------------+
    | 101  |      92.3333 |
    | 102  |      78.0000 |
    | 103  |      78.6667 |
    | 104  |      69.0000 |
    | 206  |      92.6667 |
    | 266  |      75.6667 |
    | 666  |      76.6667 |
    +------+--------------+
    7 rows in set (0.00 sec)
    • 查询至少有两名学生考试并且课程号是以3结尾的课程平均分数
    SELECT Cno,avg(Grade),count(*) FROM score
    GROUP BY Cno HAVING count(Cno) >= 2
    AND Cno LIKE '%3';
    
    +------+------------+----------+
    | Cno  | avg(Grade) | count(*) |
    +------+------------+----------+
    | 0-03 |    74.5714 |        7 |
    +------+------------+----------+
    1 row in set (0.00 sec)
    • 多表查询
    ---多表查询,查询student 和 score表的Sname,Cno,Grade
    SELECT Sname,Cno,Grade FROM student,score
    WHERE student.Sno = score.Sno;
    ---多表查询,查询student 和 score表的Sno,Cname,Grade
    SELECT Sno,Cname,Grade FROM course,score
    WHERE course.Cno = score.Cno;
    ---多表查询,查询所有学生的Sname,Cname,Grade
    SELECT Sname,Cname,Grade FROM student,score,course
    WHERE student.Sno = score.Sno
    AND course.Cno = score.Cno;
    
    +-------+---------+-------+
    | Sname | Cname   | Grade |
    +-------+---------+-------+
    | RJman | Russian |    99 |
    | Joe   | Russian |    88 |
    | Lee   | Russian |    81 |
    | Sun   | Russian |    84 |
    | WNM   | Russian |    96 |
    | ZQD   | Russian |    86 |
    | ZTZ   | Russian |    78 |
    | RJman | English |    88 |
    | Joe   | English |    69 |
    | Lee   | English |    79 |
    | Sun   | English |    64 |
    | WNM   | English |    99 |
    | ZQD   | English |    67 |
    | ZTZ   | English |    89 |
    | RJman | Chinese |    90 |
    | Joe   | Chinese |    77 |
    | Lee   | Chinese |    76 |
    | Sun   | Chinese |    59 |
    | WNM   | Chinese |    83 |
    | ZQD   | Chinese |    74 |
    | ZTZ   | Chinese |    63 |
    +-------+---------+-------+
    21 rows in set (0.00 sec)
    • 查询'xg'班学生每门课的平均分

      SELECT * FROM student WHERE Sclass = 'xg';
      SELECT Sno FROM student WHERE Sclass = 'xg';
      ---子查询结合分组
      SELECT Cno,avg(Grade) FROM score
      WHERE Sno IN(SELECT Sno FROM student WHERE SClass = 'xg')
      GROUP BY Cno;

第九章:数据库安全

数据库安全性的两大标准 TESEC 和 CC

TESEC中的数据库达到C2级别,才能说数据库是安全的

授权

--授予user用户更新Sno字段和查询student表的权限
GRANT SELECT,UPDATE(Sno)
ON TABLE student
TO user
WITH GRANT OPTION;  --授予该用户转授权限
--收回user及被转授权限用户更新student表Sno字段的权限
REVOKE UPDATE(Sno)
ON TABLE student
FROM user CASCADE;  --CASCADE级联,同时收回被转授用户的权限

One Comments

发表评论