mysql之DML(SELECT DELETE INSERT UPDATE)

658uhr

DML：数据操作语言
    INSERT
    DELETE
    SELECT
    UPDATE

SELECT：
    SELECT SELECT-LIST FROM TBNAME|TBNAMES|SELECT ACTION QUALIFICATION

        SELECT-LIST 搜索清单
            DISTINCT 去重，相同的值只显示一次。
            AS ALIAS  字段别名
            * 所有内容
            FIELD 字段名
            聚合计算函数：
                AVG(FIELD) 平均值
                MIN(FIELD) 最小值
                MAX(FIELD) 最大值
                SUM(FIELD) 求和
                COUNT(FIELD) 个数

        
        FROM子句：要查询的关系，可以是单个表，多个表，或其他SELECT语句。

        QUALIFICATION 搜索码，搜索关键字。

        ACTION 指定对于搜索码的限定或操作：
            WHERE子句：指定一个布尔关系的表达式。
                算数操作：+、-、*、/、%（取余）
                比较操作：=、>、<、>=、<=、<>（不等于）、!=（不等于）、<=>（NULLsafe NULL的安全等于，即便是NULL也能进行安全的相等比较）
                逻辑操作：AND（&&）、OR（||）、NOT（！）、XOR（异或）
                特殊操作：
                    BETWEEN  介于2者之间
                    LIKE ‘PATTERN’  比较操作，符合PATTERN的。
                        % 任意长度任意字符
                        _ 任意单个字符
                    REGEXP|RLIKE ‘PATTERN’ 支持正则表达式的LIKE，注意使用这个就不能用索引
                    [NOT] IN (LIST) 是否符合列表中的
                    IS [NOT] NULL  跟NULL比较

            ORDER BY  FIELDNAME [ASC|DESC] 将查询后的结果根据某个字段进行排序，默认是升序（ASC）排列。

            LIMIT [OFFSET,] COUNT 只显示COUNT个结果，或从OFFSET（偏移量）个开始，显示
            CONUT个结果。

            GROUP BY QUALIFICATION
                根据搜索码进行分组，主要的目的是为了求聚合函数。
                HAVING  QUALIFICATION 过滤，用于将GROUP BY的结果再次进行过滤。只能和GROUP BY一同使用。

    查询执行顺序：
        start --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT

    单表查询：
        SELECT * FROM TBNAME 显示所有内容
        SELECT FIELD1,FIELD2,... FROM TBNAME  投影
        SELECT * FROM TBNAME WHERE QUALIFICATION 选择

    多表查询：
        表的连接关系：
            交叉连接：笛卡尔乘积
            自然连接：表中字段建立等值关系

            内连接：
                TBNAME1 [NEER] JOIN TBNAME2 ON QUALIFICATION
                两个表中至少存在一个匹配，返回所在行。

            外连接：
                左外连接：TBNAME1 LEFT JOIN TBNAME2 ON QUALIFICATION
                返回左侧表中指定的行，无论右侧表中是否有匹配。

                右外连接：TBNAME1 RIGHT JOIN
                TBNAME2 ON QUALIFICATION 返回右侧表中指定的行，无论左侧表中是否有匹配。

                全连接：TBNAME1 FULL JOIN TBNAME2 ON QUALIFICATION
                两个表中只要有匹配，就返回对应的行。


                自连接：自己和自己连接，通过AS ALIAS的方式来实现。

    子查询（嵌套查询）:
        比较操作：只能返回单行单值
        在IN()中使用
        在FROM中使用

    联合查询：
        UNION：
            将多个SELECT语句的结果集进行合并。

            注意：字段的数据类型必须一致。

            默认只显示不同值，若有重复值想全部显示使用UNION ALL。
            (SELECT语句1) UNION (SELECT语句2)
            或
            (SELECT语句1) UNION ALL (SELECT语句2)



实例：
    1.显示test库中courese表的所有内容：
        #mysql -uroot -p
        mysql>USE test;
        mysql>SELECT * FROM courses;

    2.使用投影方式显示学生表中的名字和年龄：
        mysql>SELECT * FROM studnets;
        mysql>SELECT Name,Age FROM students;

    3.使用选择方式显示学生表中年龄大于20的
        mysql>SELECT * FROM students WHERE Age>=20;

    4.综合使用投影和选择。显示students表中性别为男的名字和年龄。
        mysql>SELECT Name,Age FROM students WHERE Gender=‘M’;

    5.显示students表中的性别：
        mysql>SELECT DISINCT Gender FROM students;

    6.显示students中年龄+1大于20的名字和年龄：
        注意这样直接使用可能无法使用索引。
        mysql>SELECT Name,Age FROM students WHERE Age+1>20;

    7.显示students中年龄大于20的男性：
        mysql>SELECT Name,Age FROM students WHERE Age>20 AND Gender='M';

    8.显示students中年龄不大于20，且性别不为男性：
        mysql>SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');

    9.显示students中年龄在20-25之间的同学：
        mysql>SELECT  Name,Age,Gender FROM students WHERE Age>=20 AND AGE<=25;
        msyql>SELECT  Name,Age,Gender FROM students WHERE Age BETWEEN 20 AND 25;

    10.显示students中姓名以Y开头的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE 'Y%';

    11.显示students中姓名以Y开头，总长5个字符的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE 'Y____';

    12.显示students中姓名里包含ing的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE '%ing%';

    13.显示students中姓名以M或N或Y开头的：
        mysql>SELECT Name,Age FROM students WHERE Name RLIKE ‘^[MNY].*’;

    14.显示students中年龄为18 20 25 的：
        mysql>SELECT Name,Age FROM students WHERE Age IN (18,20,25);

    15.显示students中CID2为空的名字：
        mysql>SELECT Name FROM students WHERE CID2 IS NULL;

    15.显示用户的姓名并排序：
        mysql>SELECT Name FROM students ORDER BY Name;

    16.别名显示：
        mysql>SELECT Name AS StudName FROM students;

    17.仅显示前3个结果：
        mysql>SELECT Name FROM students LIMIT 3;

    18.从第2个结果以后开始显示，共显示3个：
        mysql>SELECT Name FROM students LIMIT 2,3;

    19.聚合函数：
        mysql>SELECET AVG(Age) FROM sutdents WHERE Gender='M' 男同学年龄的平均值
        mysql>SELECET COUNT(Age) FROM sutdents; 同学年龄的个数

    20.根据性别分组计算年龄的平均值：
        mysql>SELECT AVG(Age) FROM students GROUP BY Gender;

    21.根据CID求同学选修课的个数：
        mysql>SELECT COUNT(CID) AS Persons,CID FROM students GROUP BY CID;

    22.显示选修课个数大于2个的：
        mysql>SELECT COUNT(CID) AS Persons,CID FROM students GROUP BY CID HAVING Persons>=2;

    23.多表查询（交叉连接）：
        mysql>SELECT * FROM students,courses;

    24.多表查询（自然连接）：
        假设有students表和courses表，从2张表中查询学生名称和对应的课程名称：
        mysql>SELECT students.Name,courses.Cname FROM sutdents,course WHERE sutdents.CID=courses.CID;

    25.使用别名：
        mysql>SELECT s.Name,c.Cname FORM studnets AS s,courses AS c WHERE s.CID=c.CID;


    26.多表查询（外连接）显示同学选择的课程，有名字显示名字，没有显示为空。CID1为选择的第一门课。
        mysql>SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID

    27.接上题，显示课程对应的同学名字，有名字显示名字，没有显示为空。
        mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JOINcourses AS c ON s.CID1=c.CID;

    28.自连接，假设students表中有TID，表示该课程的导师编号，导师编号就是SID。显示同学名字和对应的导师名字
        mysql>SELECT s.Name AS stu,c.Name AS teacher FROM students AS s，students AS c WHERE  s.SID=c.SID;

    29.显示表中年龄大于平均年龄的同学
        mysql>SELECT Name FROM students WHERE Age> (SELECT AVG(Age) FROM students);

    30.显示学生中年龄和老师一样的
        mysql>SELECT Name FROM students WHERE Age IN (SELECT Age FROM Tutor);

    31.显示所有同学的名字和年龄并显示年龄大于等于20的
        mysql>SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >=20;

    32.将students表和tutor表中查询到的Name和Age进行合并
        mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutor) ;

    33.显示courses表中没有被students表中出现在CID2的课程的名称：
        mysql>SELECT Cname FROM courses WHERE CID NOT IN(SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL);

    34.显示在courses表中的没有代课的老师名字，CID课程编号 Cname课程名称 和TID教师编号，tutors表中为老师名字Tname和TID
        mysql>SELECT Tname FROM tutors WHRER TID NOT IN (SELECT DISTINCT TID FROM courses);

    35.显示students表中CID1有2个或2个以上同学学习的额课程的名称
        mysql>SELECT Cname FROM courses WHRER CID IN (SELECT  CID1 FROM students GROUP BY CID1 HAVING COUNT(CID1)>=2 );

    36.显示每个老师和其教授的课程，没有教授的课程保持为NULL
        mysql>SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID;

    37.显示每个课程及其相关的老师，没有老师教授的课程将其老师显示为空
        mysql>SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID;

    38.显示每位同学的CID1的课程及其老师名称
        mysql>SELECT Name,Cname,Tname FROM stutdents,courses,tutors WHERE students.CID=courses.CID AND courses.TID=tutors.TID

    39.显示tutors中最后一行的数据：
        mysql>SELECT * FROM tutors ORDER BY TID DESC LIMIT 1;

INSERT：     
        
    INSERT INTO TBNAME (COL1,COL2,...) VALUES (VAL1,VAL
        2...)[,(VAL3,VAL4),...] 可以插入多行

    INSERT INTO TBNAME SET COL1=VAL1,...; 插入一行

    INSERT INTO  TBNAME (COL1,...) SELECT STATMENT  将搜索到的内容插入，注意格式要完全相同。


    REPLACE INTO 和INSERT类似，提供替换功能。


    插入的数据类型：
        字符型：单引号
        数值型：
        日期时间型
        空值：NULL
        空串：‘’

    实例：
        1.向tutors表中插入新数据：
            mysql>INSERT INTO tutors (Tname,Gerder,Age) VALUES (tom,'M',40),(jerry,'F',33);

        2.向totors表中插入单行数据：
            mysql>INSERT INTO tutors SET Tname='mike',Gender='F',Age=22;

        3.将students表中年龄大于20的插入tutors表中：
            mysql>INSERT INTO tutors (Tname,Gernder,Age) SELECT Nname,Gender,Age FORM students WHERE Age>20;


DELETE：
    DELETE FROM TBNAME1,TBNAME2,... [WHERE]|[ORDER BY] |[LIMIT] 删除表

    TRUNCATE TBNAME 清空表并重置AUTO_INCREMENT计数器。

    实例：
        1.删除students表
            mysql>DELETE FROM students;

        2.删除students表中年龄不大于18的行：
            msyql>DELETE FROM students WHERE Age<=18;

        3.显示并清空students表并重置计数器

            mysql>SHOW TABLE STATUS LIKE 'students'\G;
            mysql>SHOW VARIABLES LIKE '%last_insert_id%
            mysql>TRUNCATE students;


UPDATE
    UPDATE TBNMAE SET COL=VALUE [WHERE]|[ORDER BY] |[LIMIT]

    实例：
        1,修改students表中tom的年龄和性别：
            mysql>UPDTAE students SET Age=30,Gernder='F' WHERE Name='tom';