• 常用SQL语句


    创建表:

    create table course(
        id INT auto_increment,
        title TEXT  NOT NULL,
        period INT,
        description TEXT,
        primary key(id)
    ) ENGINE=INNODB CHARSET=utf8;

    向表中插入数据(三种方式):

    id字段为自增长,可以不给值:

    INSERT INTO course(title,period,description)
    VALUES('经济基础学', 320, '没啥用,爱学不学');

    没有给出具体字段,所以values里要逐个写,包括id:

    INSERT INTO course
    VALUE(2, '马克思主义哲学', 330, '这个考试能忽悠就行');

    字段顺序也是可以颠倒的:

    INSERT INTO course(period, description, title)
    VALUE(340, '这个考试难啊', '高等数学');

    如果没有给某一字段设置值,而且也没有指定默认值的话,那么数据库会将其值设置为null:

    INSERT INTO course(title,period)
    VALUE('必修课', 350);

    删除表中数据

    语法:

    DELETE FROM table_name 
    WHERE  条件表达式

    条件表达式如下表:

    操作符 描述 针对表course的举例
    = 等于 title=’高等数学’
    <> 不等于 period<>30
    > 大于 period>300
    < 小于 period<300
    >= 大于等于 period>=320
    <= 小于等于 period<=320
    between 在两个数之间 period between 200 and 400
    like 模糊匹配 title like ‘大学’
    in 是否在集合中 title in (‘经济基础学’, ‘马克思主义哲学’)
    is null 判断是否为空 description is null
    and 并,用于连接多个条件表达式 period>300 and description like ‘%必修课%’
    or 或,用于连接多个条件表达式 title=’高等数学’ or title=’经济基础学’

    多条件表达式的优先级:

    and运算的优先级高于or, 即先运算and再运算or。 也可以用括号来指定优先级,括号里的表达式先运算。

    删除id为1的课程:

    DELETE FROM course
    WHERE id=1;

    删除名字以’经济’开头且学时小于200的课程, 同时删除学时大于600的课程:

    DELETE FROM course
    WHERE title like '经济%' and period<200 or period>600;

    删除名字以‘经济’开头,且学时小于200或者大于600的课程:

    DELETE FROM course
    WHERE title like '经济%' and (period<200 or period>600);

    修改表中数据

    语法:

    UPDATE table_name
    SET  字段名1=新值1, 字段名2=新值2 ...
    WHERE 条件表达式

    修改所有学生的年龄为18:

    注意,这个操作在实际应用中是非常危险的,所以实际中我们必须对其指定条件;

    UPDATE student
    SET age=18;

    将所有parent为null的学生年龄加1, 并将parent设置为 ‘未知’:

    UPDATE student
    SET age=age+1
    WHERE parent IS NULL;

    查询数据

    语法:

    top用于指定返回的最大行数,distinct只用于在只返回一列时指明排除重复项

    SELECT [distinct|top] 字段名1, 字段名2.....
    FROM table_name
    [WHERE 条件表达式]
    [GROUP BY 分组列
    [HAVING 分组筛选表达式]
    ]
    [ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC]

    查询course表中所有记录:

    SELECT * FROM course;

    查询所有课时,并且排除重复的数字:

    SELECT distinct period FROM course;

    查询课时大于200的课程,最多返回2个课程:

    SELECT top 2 * FROM course
    WHERE period>200;

    注意:top语法在postgreSQL中存在;在mysql中是没有top语法的, MySQL中可以使用limit:

    SELECT * FROM course
    WHERE period>200
    LIMIT 1,2;

    GROUP BY用于对数据进行分组以便汇总计算,HAVING是GROUP BY的可选项,用于对汇总结果进行筛选;汇总计算是指统计个数,计算平均值等,

    统计所有课程的平均课时:

    SELECT AVG(period) FROM course;

    按课时period进行分组,统计每个课时的课程个数,并且只返回课程个数大于3的课时:

    SELECT period, COUNT(*) 
    FROM course
    GROUP BY period
    HAVING count(*) >3;

    常用的聚集函数:

    聚集函数 描述
    COUNT(*) 统计记录个数
    AVG(column) 计算某列的平均值
    MAX(column) 找出某列的最大值
    MIN(column) 找出某列的最小值
    VAR(column) 计算某列方差
    FIRST(column) 返回某列的第1个值
    LAST(column) 返回某列的最后1个值

    ORDER BY 用于指定返回的结果按照某个或几个字段值大小排序, ASC升序默认, DESC降序:

    SELECT * FROM course
    ORDER BY id  desc;

    多表连接的SQL语句

    先多创建几张表:

    create table student(
        id INT auto_increment,
        name TEXT NOT NULL,
        age INT,
        parent TEXT,
        primary key(id)
    ) ENGINE=INNODB CHARSET=utf8;
    
    create table enroll(
        student_id INT,
        course_id INT,
        primary key(student_id, course_id),
        CONSTRAINT FOREIGN KEY(student_id) REFERENCES student(id),
        CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id)
    );
    
    create table teacher(
        id INT auto_increment,
        name TEXT NOT NULL,
        gender BOOLEAN,
        address TEXT,
        course_id INT,
        primary key(id),
        CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id)
    );

    因为整个系统的数据是分布在不同的表中,所以很多时候为了得到完整的结果,开发者需要从两个或多个表中查询数据,这时需要在FROM子句中用JOIN关键字连接多个表,相关语法为:

    SELECT 列名1, 列名2...
    FROM table_name1 JOIN table_name2 ON 连接条件表达式
    WHERE ...

    JOIN本身有多种类型,见下表:

    关键字 含义
    INNER JOIN 获取两个表中满足查询关键字的连接记录
    LEFT JOIN 在INNER JOIN返回记录的基础上, 返回 所有左表未被连接记录到的记录
    RIGHT JOIN 在INNER JOIN返回记录的基础上, 返回 所有右表未被连接记录到的记录
    FULL JOIN 返回INNER JOIN, LEFT JOIN, RIGHT JOIN结果的合集

    虽然一个JOIN只能连接两个表,但可以同时使用多个JOIN以达到连接多个表的目的:

    查询所有教’高等数学’的男老师:

    SELECT teacher.*
    FROM teacher INNER JOIN course on teacher.course_id = course.id
    WHERE teacher.gender = True and course.title='高等数学'

    查询所有18岁的学生选择的课程

    SELECT distinct course.title
    FROM course INNER JOIN enroll ON course.id=enroll.course_id
        INNER JOIN student ON enroll.student_id=student.id
    WHERE student.age=18;

    其它问题

    前面提到在update数据时,如果没有指定where条件,那是很危险的事情,因为这它会将整个表所有记录的值都改掉,为了防止此事件发生,我们可以在启动mysql时使用-U参数

  • 相关阅读:
    第十章、数据库运行维护与优化
    第九章、安全管理
    第八章、数据库后台编程技术
    第七章、高级数据库查询
    第六章、数据库及数据库对象
    第五章、UML与数据库应用系统
    第四章、数据库应用系统功能设计与实施
    struts2标签debug
    SSH框架 more than one namespace has been specificed without a prefix
    Http Status 404
  • 原文地址:https://www.cnblogs.com/huangxm/p/6275743.html
Copyright © 2020-2023  润新知