MySQL

一、数据库的基本概念：
1、数据库（DATABASE）的集中式控制的优点：
    （1）降低存储数据的冗余度
    （2）更高的数据一致性
    （3）存储的数据可以共享
    （4）可以建立数据库所遵循的标准
    （5）便于维护数据完整性
    （6）能够实现数据的安全性（数据库管理员--DBA）

2、数据库的数据模型：
    （1）层次模型（倒挂树---运用举例：IBM的信息管理系统）
    （2）网状模型
    （3）关系模型【把世界看做是实体（Entity）和联系(Relationship)组成的】
    （4）对象模型

3、关系数据库的基本概念
    （1）英语单词总结：
        列：column；            行：row;
        属性：Attribute;        主键：Primary Key;       外键：Foreign Key
        数据库管理系统（DBMS）：Datebase Management System;
        关系型数据管理系统（RDBMS）Relational Datebase Management System;

    （2）主键：在关系型数据库的表中，使用一个唯一的标识符来标识每一行。
    
    （3）外键：就是用来表达表与表之间的关联关系。

    （4）关系型数据库中的表的关联关系的基本关系类型：一对一关系、一对多关系、多对多关系。

    （5）关系型数据管理系统（RDBMS）：分为本地数据库管理系统（同一客户端）、数据库服务器管理系统（在不同的机器上）。
    在商业引用程序开发中，为安全及性能考虑，通常采用数据库服务器管理系统。

    （6）结构化查询语言SQL（Structured Query Language）:
        a、数据定义语言（DDL）:用于创建、修改、删除数据库内的数据结构
        b、数据查询语言（DQL）：用于从数据库中的一个或者多个表中查询指定的数据
        c、数据操作语言（DML）：用于修改数据库中的数据，包括：插入、更新、删除数据
        d、数据控制语言（DCL）：用于控制对数据库的访问

4、基本规则：
    （1）数据库名：小写，不能有中文
    （2）`变量名` 用``扩起来的，避免关键字，比如name
    （3）表名，变量名不区分大小写
    （4）主类不能直接删，因为从表有引用指向它，除非没有从表的引用指向它，所以说有关联删不了
        （5）SQLyog里边单行注释 -- 多行注释 /**/；SQLyog里边选中某一段运行可以单独运行那一段

二、数据定义语言的使用：
1、管理数据库：

    （1）数据库名在服务器中必须是唯一的，并且符合标识符规则：

        a、第一个字符必须是下划线 或 @ 或 数字符合# 或Unicode标准3.0所定义的字母
        b、后续字符可以是：Unicode标准3.0所定义的字母 或 下划线 或 @ 或 数字符合 或美元符合$ 或来自基本拉丁字    母或其他国家/地区脚步的十进制数字。
        c、标识符不能是RDBMS的保留字
        d、不允许嵌入空格或其他特殊字符。

    （2）创建数据库：CREATE DATABASE 数据库名；   例：CREATE DATABASE Datebase_demo;

    （3）连接到数据库：USE 数据库名；   例：USE Datebase_demo;

    （4）删除数据库： DROP DATABASE 数据库名；   例： DROP DATABASE Datebase_demo;

    （5）数据库中数据的类型：（具体的可看教材p22-24或图片）
        A、整数数据类型：
        B、浮点数据类型：
        C、字符串类型：
        D、二进制数据类型：
        E、日期与时间数据类型：
2、管理表：
    （1）创建表：CREATE TABLE 表名（
            列名 列的数据类型 列的约束    -- 有多列时，使用“，”隔开，最后一个不需要写“，”。且不能交换列名与列的数据类型的位置。
            ）；

        例：CREATE TABLE Student(
            id bigint PRIMARY key ,        -- id用integer比bigint好，对Java的兼容性好，出错率小，最后设置默认编码为utf8。
            name VARCHAR(12) not null,
            age int(150)

            )CHARSET=utf8;

    （2）删除表：DROP TABLE 表名；    例：DROP TABLE Student；注意：通过外键约束连接到一起的表不能被删除，在删除之    前，必须先删除约束。

    （3）复制表： 使用SELECT。    例：CREATE TABLE Student_copy SELECT * FROM Student;
注意：这里的复制不能将表的约束一起复制下来。对于只复制表的结构，不复制表的数据我们可以使用WHERE,在WHERE 后添加一个永远不等于true的条件：
    例：CREATE TABLE Student_copy SELECT * FROM Student WHERE id = -1;

    （4）修改表：
        A、更改数据结构，为数据添加一列：例：ALTER TABLE Student ADD Address VARCHAR(12);-- 为表Student添加了一列Address.若要一次添加多列，则使用“，”隔开，例：ALTER TABLE Student ADD Address VARCHAR(12)，Teacher VARCHAR(12);
        B、更改列的定义，并为列添加一个默认值约束：ALTER TABLE Student CHANGE 旧列名 新列名 列的类型
        例： ALTER TABLE Student CHANGE name StudentName VARCHAR(15) DEFAULT ‘张三‘；

        C、删除列：例：ALTER TABLE Student DROP COLUMN age;

        D、修改表的表名：ALTER TABLE Student RENAME Student12；  -- RENAME 只针对于表，不能使用它来修改数据库的名字与表中的列的列名。

    (5)管理索引：
        A、创建索引（创建索引的好处：当数据大的时候查询较快，提高效率）：例： CREATE INDEX nameIndex ON Student (name);   -- 索引值默认为数字类型，不需要指定类型。

        B、通过索引查询：例： SELECT id，name FROM Student WHERE name=‘张三2‘;

        C、删除索引：例：ALTER TABLE Student DROP INDEX nameIndex；



三、数据完整性概述：

    1、根据数据完整性实施的方法，将其分为4类：
        （1）实体完整性：保证一行数据是有效的。
            实现方法：主键约束，唯一约束，标识列属性。

        （2）域完整性：保证一列数据是有效的。
            实现方法：外键约束，检查约束（在MySQL中不支持），默认值约束，非空约束。
        
        （3）引用完整性：保证引用的编号是有效的。
            实现方法：外键约束
        （4）自定义完整性：保证自定义完整性。
            

    2、对数据完整性实现的方法的使用；

        （1）创建非空约束：
        NOT NULL 约束强制列不接受 NULL 值。NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无        法插入新记录或者更新记录。
        CREATE TABLE t_person(
            p_id INTEGER PRIMARY KEY AUTO_INCREMENT, /* 设置主键为自增长。*/

            p_name VARCHAR(20) NOT NULL,        -- 在这里就使用了非空约束。
            p_age INTEGER(150) NOT NULL,
            p_address VARCHAR(50) DEFAULT ‘成都市‘


        )CHARSET=utf8 ,ENGINE=INNODB;
    设置自增长的起始值 ALTER TABLE t_person AUTO_INCREMENT = 100；如果手动设置id 值，自增长不生效，下次生效在最大的那里加1

        （2）唯一约束：
        A.设置唯一约束：
        UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARYKEY 约束均为列或列集合提供了唯一性的保证。PRIMARYKEY 拥有自动定义的 UNIQUE 约束。注意：每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

        例1：CREATE TABLE Persons(
            Id_P int NOT NULL UNIQUE    
        )CHARSET=utf8 ,ENGINE=INNODB;

        例2：CREATE TABLE Persons(
            Id_P int NOT NULL，
            UNIQUE (Id_P)    
        )CHARSET=utf8 ,ENGINE=INNODB;

        例3：CREATE TABLE Persons(
            Id_P int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Address varchar(255),
            City varchar(255),
            CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)  -- 一步设置多个属性的约束条件为唯一约束。                                        -- 约束名为uc_PersonID。
        )；

        例4：在表已经创建的情况下为属性添加唯一约束：
            ALTER TABLE Persons ADD UNIQUE (Id_P)；
            ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) -- 一步设置多个属性                                    的约束条件为唯一约束，约束名为uc_PersonID
        B.撤销唯一约束：
        ALTER TABLE Persons DROP INDEX uc_PersonID;
        ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;  -- 在MySQL不支持这种撤销唯一约束的方法.
        （3）主键约束
        A.设置主键约束
            注意：默认它就包含了非空约束与唯一约束，一个表中只能有一个主键，但一个主键不等于将表中的一列作        为主键，可以将多列作为一个主键，这个主键称为复合主键。

        例1：CREATE TABLE Persons(
            Id_P int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Address varchar(255),
            City varchar(255),
            PRIMARY KEY (Id_P)   -- 设置主键约束
        )；

        例2：CREATE TABLE Persons(
            Id_P int NOT NULL PRIMARY KEY,  -- 设置主键约束
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Address varchar(255),
            City varchar(255)
        )；

        例3：CREATE TABLE Persons(
            Id_P int NOT NULL,
            LastName varchar(255) NOT NULL,
            FirstName varchar(255),
            Address varchar(255),
            City varchar(255),
            CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
        )；
    
        例4：在表已经创建的情况下添加主键约束：
            ALTER TABLE Persons MODIFY Id_P PRIMARY KEY;
            ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
            ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
        注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。
    B.撤销主键约束：
        ALTER TABLE Persons DROP PRIMARY KEY；


    （4）外键约束：一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
    A.设置外键约束：
    例1：CREATE TABLE Orders(
        O_Id INT NOT NULL PRIMARY KEY,
        OrderNo INT NOT NULL,
        Id_P INT NOT NULL,
        FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)   -- 设置外键约束
          );

    例2：CREATE TABLE Orders(
        O_Id INT NOT NULL PRIMARY KEY,
        OrderNo INT NOT NULL,
        Id_P INT FOREIGN KEY REFERENCES Persons(Id_P)  -- 在MySQL中不支持这种设置外键的方法。
        );

    例3：CREATE TABLE Orders(
        O_Id int NOT NULL,
        OrderNo int NOT NULL,
        Id_P int,
        PRIMARY KEY (O_Id),
        CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)   
    )

    例4：在表已经存在的情况下，设置外键约束：

        ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)；
        ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)；

    B、撤销外键约束：
        ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders；
        ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders； -- 在MySQL中不支持这种撤销外键的方法。
    

    （5）默认值：DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。
    A.指定默认值：
    例1：CREATE TABLE Persons(
        Id_P int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255) DEFAULT ‘Sandnes‘   -- 设定默认值约束
        )
    例2：在表已经存在的情况下，添加默认值：
        ALTER TABLE Persons ALTER City SET DEFAULT ‘Sandnes‘；

    B、撤销默认值：
        ALTER TABLE Persons ALTER City DROP DEFAULT；


四、使用DML语句更改数据：

    1、添加新数据
        语法：INSERT INTO 表名 （列名列表）VALUES（值列表）；注意：列名列表省略时，需将属性按顺序一一赋值
        例：INSERT INTO Student (StudentID,NAME) VALUES (1,‘张三‘),(2,‘张三1‘),(3,‘张三2‘),(4,‘张三3‘);

    2、插入多行记录：实为将数据从一个表复制到另一个表。
        语法：INSERT INTO 表名 （列名列表）SELECT SELECT语句；
        例：INSERT INTO Student (StudentID,NAME) SELECT StudentID + 2,NAME FROM Student1;
    解释：在上面例子中的SELECT 中的StudentID，NAME指的是Student1中的属性，将StudentID + 2的原因是因为在Student中将StudentID作为了主键，且Student表中已经有了2个数据，加2是为了避免主键重复。

    3、表数据的复制：
        语法：SELECT 列名 INTO 新表名 FROM 表名；
        例：SELECT * INTO Student2 FROM Student1;

    4、更改已有的数据：
        语法：UPDATE 表名 SET 新列值列表 WHERE 过滤条件；
        例1：UPDATE Student SET NAME=‘李四‘ WHERE StudentID=2;
        例2：UPDATE Student SET NAME=NULL;这样就会删除列名为NAME的整列数据。

    5、删除数据
        (1)语法：DELETE FROM 表名 WHERE 过滤条件；（注意：如果不指定WHERE子句，表中的所有行将被删除，但表中的数据仍存在一些记录，这些记录是与其他表中的数据有关联的。例如：主键，从表就关联到了，以前使用了的数据仍然不可以使用）
        例：DELETE FROM Student WHERE StudentID < 2;

        (2)语法：TRUNCATE TABLE 表名；
        注意：TRUNCATE TABLE 可以删除表中的所有行（原始无数据的状态），但表的结构、列、约束、索引等不会被改的。且他不能用于有外键约束引用的表，这时删除表的数据需要使用不带WHERE子句的DELETE语句。
        执行TRUNCATE TABLE 表名；语句后，表中自动增长的标识值被设置为默认值。


五、数据库的简单查询

        CREATE TABLE Persons(
        Id_P int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255) DEFAULT ‘Sandnes‘   -- 设定默认值约束
        )；后面的查询以此表为例

1、数据库的查询操作基本分类：
     （1）投影操作
    （2）选择操作
    （3）排序操作

2、投影操作：
    （1）查询语法：
        SELECT 列名列表 FROM 表名；（选择多个列时，列之间以“，”隔开；如果选择所有列，列名列表用“*”代替）
        例1：SELECT Id_P FROM Persons;
        例2：SELECT Id_P，LastName FROM Persons;
        例3：SELECT * FROM Persons;  -- 常用*的查询语句来做测试，不使用它来做查询。

    (2)使用表名前缀（作用：了解信息来自那个表，区分不同表的同列名）
        语法：SELECT 表名1.列名1，表名1.列名2，表名2.列名1 FROM 表名1，表名2；
        例：SELECT Persons.Id_P FROM Persons；

    （3）为列名取别名：
         语法：SELECT 列名 AS 新列名 FROM 表名；（查询列显示列别名）
        例：SELECT Id_P AS ‘编号’，LastName AS name FROM Persons;（选择多个列时，列之间以“，”隔开）

    （4）使用DISTINCT（distinct）关键字排除重复数据：
        语法：SELECT DISTINCT 列名 FROM 表名；
        例：SELECT DISTINCT FirstName FROM Persons；-- 查询Persons表格中有什么样的FirstName。
        当列为多列的时候，即SELECT DISTINCT 列1，列2 FROM 表名；-- 列1，列2同时相同的数据信息过滤后查询的结果显示出来。

    （5）计算列：
        A、连接字符串，使用CONCAT(concat)关键字：
        语法： SELECT CONCAT(列名1，列名2,列名3) AS ‘标题‘ FROM 表名；（在列名之间也可以加入其它的字符串）
        例1：SELECT CONCAT（FirstName，LastName）AS Name FROM Persons;
        例2：SELECT CONCAT（FirstName，‘-’，LastName）AS Name FROM Persons;
        B、做简单的四则运算：
    （6）返回限定行数的查询：
         语法：SELECT 列名 FROM 表名 LIMIT 开始序号，返回行数； （注意：开始序号是从0开始的即：若从第一行开始，则开始序号为0）
        例1：SELECT DISTINCT FirstName FROM Persons LIMIT 1,4；-- 从第二行开始查询，总共返回4行数据。
        例2：SELECT DISTINCT FirstName FROM Persons LIMIT 4； -- 从第一行开始查询，返回4行数据


3、选择操作（使用WHERE关键字）：
     SELECT 列名列表 FROM 表名 WHERE 条件；
    （1）单条件选择操作：
        A、语法：SELECT  列1，列2 FROM 表名 WHERE 列3=值；
        例：SELECT FirstName，LastName FROM Persons WHERE Id_P=1；
        B、比较运算符(=,!=,^=,<>,>,>=,<,<=)：
            对于^=,<>与!=类似，只是在值为NULL的时候有区别。

    （2）多条件选择操作：
        使用关键字AND（同时满足两个条件）,OR（至少满足两个条件中的一个）。        
         语法1：SELECT 列名 FROM 表名 WHERE 条件 AND 条件；
        语法2： SELECT 列名 FROM 表名 WHERE 条件 OR 条件;
        注：NOT关键字也可以用在查询条件中，例：SELECT 列名 FROM 表名 WHERE 条件1 AND NOT 条件2； --条件1满足，条件2不满足。

    (3)执行范围测试：（使用BETWEEN关键字）
        语法： SELECT 列名列表 FROM 表名 WHERE 条件 BETWEEN 上限 AND 下限；（包括上下限值。当然在这里也可以使用NOT关键字，排除一个范围。SELECT 列名列表 FROM 表名 WHERE 条件 NOT BETWEEN 上限 AND 下限；）

    （4）定义集合关系：

        SELECT * FROM 表名 WHERE 列名 IN（值集合） ；（找出指定范围的）
         SELECT * FROM 表名 WHERE 列名 NOT IN（值集合）；（找出不在指定范围的）
        例：


    （5）模糊查询：使用通配符‘%’(包含0个或更多字符的任意字符串)，‘_’（任意单个字符）。
     A、语法： SELECT 列名 FROM 表名 WHERE 列名 LIKE 模式；
    例1：SELECT FirstName，LastName FROM Persons WHERE FirstName LIKE‘%王%’；-- FirstName中包含‘王’的
    例2：SELECT FirstName，LastName FROM Persons WHERE FirstName LIKE‘_’；-- FirstName只有一个字的。

    B、转义符
    例：匹配包含50%： ‘%50p%%‘ ESCAPE ‘p‘（这里的p最好是不在查询字符串中出现的字符） 或者 ‘%50<Escape Char>%%‘ 或者  ‘%50[%%‘（注意：后俩者在MySQL在不使用）

    （6）处理空值数据：
     SELECT 列名 FROM 表名 WHERE 列名1 IS NOT NULL；（查询测试列名1不为空）
     SELECT 列名 FROM 表名 WHERE 列名1 IS  NULL；（查询测试列名1为空）


4、排序操作：

    语法： SELECT 列名列表1 FROM 表名 ORDER BY 列1，列2 ASC(升序)；
          SELECT 列名列表1 FROM 表名 ORDER BY 列1，列2 DESC(降序)；（注：当列名列表2为多个是，首先按照列1的顺序进行（降序、升序）排序，当列1的数据出现相同时，再按照列2的顺序进行（降序、升序）排序）

MySQL

标签：