MySQL简单学习笔记三之DMl和DQL

发表于 更新于 分类于 ， 阅读次数： Waline： 本文字数： 4k 阅读时长 ≈ 4 分钟
DML 数据操作语言 Data Manipulation Language
  增删改; 添加数据 删除数据 修改数据 insert update delete
DQL 数据查询语言 Data Query Language
  查询 select

系列链接

MySQL简单笔记一

MySQL简单笔记二之DDL

MySQL简单学习笔记三之DMl和DQL

MySQL语法的Oracle练习表分享

DML
1. 添加数据 
1
2
3
4
5
6
7
-- insert into 表名(列123...) values(值123..)
INSERT INTO student VALUES(5,'eee',15);
INSERT INTO student(id,NAME,age) VALUES(6,'fff',60);
INSERT INTO student(id,NAME) VALUES(7,'ggg');
INSERT INTO student VALUES(8,'hhh',NULL);
INSERT INTO student VALUES(9,'iii',DEFAULT);
-- 插入 varchar 或 date 型的数据要用 单引号 引起来

  1. 修改数据

    1
    2
    3
    4
    5
    -- update 表 set 列1=值1....
    -- 无条件则改所有行的该列值
    UPDATE student SET age=35;
    UPDATE student SET age=35 WHERE id=1;
    UPDATE student SET NAME='zs',age=32 WHERE id=10;

  2. 删除数据

    1
    2
    3
    4
    5
    6
    -- delete from 
    -- 没有条件删除全表
    DELETE FROM student 
    DELETE FROM student WHERE id=8;
    -- truncate table 表 删除全表,先删后建表 DDL 不属于DML
    DELETE FROM student WHERE id=9 AND NAME=iii;
DQL
1. 查询 
1
2
3
4
-- select 列名123.. from 表名
-- *所有列
SELECT * FROM student;
SELECT id ,`name` ,age FROM student;

  1. 条件查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 条件查询 = != <> < <= >= > 
    -- between...and; in(set); is null; and; or; not; 
    SELECT * FROM student WHERE age =20 AND gender='女';
    SELECT * FROM student WHERE age IN(20,35,12);
    SELECT * FROM student WHERE age NOT IN(20,35,12);
    SELECT * FROM student WHERE NOT(age=20 OR age=35 OR age=12 );
    SELECT * FROM student WHERE NAME IS NULL ;
    SELECT * FROM student WHERE NAME IS NOT NULL ;
    SELECT * FROM student WHERE age>=10 AND age<=60;
    SELECT * FROM student WHERE age BETWEEN 10 AND 60;
    SELECT * FROM student WHERE age NOT BETWEEN 15 AND 60;#我试的时候not在age前后都可以

  2. 模糊查询

    1
    2
    3
    -- %代表任意( 1,0,多) _表示一个任意
    -- 和like一起使用
    SELECT * FROM student WHERE NAME LIKE 'a%';

  3. 去重

    1
    2
    SELECT DISTINCT NAME FROM student;
    SELECT DISTINCT age FROM student;

  4. 组合数据 如和 可能为空

    1
    SELECT *,id+age FROM student;

  5. 试用数据的函数 自学 理解为编程语言有返回值

    1
    2
    3
    4
    SELECT *,id+IFNULL(age,0)FROM student;
    SELECT CURDATE();#用select输出
    SELECT YEAR(CURDATE());
    SELECT ADDDATE(CURDATE(),10);

  6. 给列名起别名

    1
    2
    3
    SELECT id sid,NAME sname,age sage FROM student;
    SELECT id AS sid,NAME AS sname,age AS sage FROM student;
    SELECT *,id+age AS '和' FROM student;

  7. 排序 默认升序asc

    1
    2
    3
    SELECT * FROM student ORDER BY age;
    SELECT * FROM student ORDER BY age ASC;
    SELECT * FROM student ORDER BY age DESC;#降序

  8. 指定多个参考列

    1
    SELECT * FROM student ORDER BY age ASC,NAME ASC;

  9. 顺序

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    /*顺序
    select
    from
    where -- 筛选
    group by
    having
    order by
    limit	-- 排序
    */
    SELECT * FROM student WHERE age BETWEEN 10 AND 60 ORDER BY NAME;

  10. 聚合函数 数据统计

    1
    2
    3
    -- sum() max() min() avg() count()
    SELECT SUM(id) ,AVG(age),MAX(age),MIN(age) FROM student;
    SELECT COUNT(*) FROM student ;#写* 列(会统计非空)都可 一般用 * 主键

  11. 分组查询

    1
    2
    3
    -- group by
    SELECT gender,COUNT(*) FROM student GROUP BY gender;
    SELECT age,gender,COUNT(*) FROM student GROUP BY age,gender;

  12. 分组再筛选

    1
    2
    -- having 统计后再筛选
    SELECT  age,gender,COUNT(*) FROM student GROUP BY age,gender HAVING COUNT(*)>=2;

  13. 分页 limit 叶索引 页大小

    1
    2
    3
    SELECT * FROM student LIMIT 0,2;
    SELECT * FROM student LIMIT 1,2;
    SELECT * FROM student LIMIT 5;

  14. 连接

    1
    SELECT * FROM student AS stu,course AS cou WHERE stu.`course_id`=cou.`id`;

    1. 内连接

      1
      2
      3
      4
      5
      SELECT * FROM student stu INNER JOIN course cou ON stu.`course_id`=cou.`id`;#inner可以省
      SELECT * FROM student stu 
      INNER JOIN course cou 
      ON stu.`course_id`=cou.`id` #一般连接条件
      WHERE stu.`gender`='男';

    2. 左外连接 左边全显示

      1
      2
      3
      SELECT * FROM student stu 
      LEFT OUTER JOIN course cou 
      ON stu.`course_id`=cou.`id`;

    3. 右外连接

      1
      2
      3
      SELECT * FROM student stu 
      RIGHT OUTER JOIN course cou 
      ON stu.`course_id`=cou.`id`;

    4. 完全外连接

      1
      2
      3
      4
      5
      6
      7
      SELECT * FROM student stu 
      LEFT OUTER JOIN course cou 
      ON stu.`course_id`=cou.`id`
      UNION
      SELECT * FROM student stu 
      RIGHT OUTER JOIN course cou 
      ON stu.`course_id`=cou.`id`;

    5. 交叉连接

      1
      2
      SELECT * FROM student ,course ;
      SELECT * FROM student CROSS JOIN course;

    6. 自然连接 内 也可以 加 right left

      1
      SELECT * FROM student NATURAL JOIN course;

  15. 子查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    -- 功能最强大 
    -- 查询嵌套 
    /*select (子查询)
    from (子查询)
    where (子查询)
    */
    -- 比eee年龄高的学生
    SELECT * FROM student WHERE age>(SELECT age FROM student WHERE NAME='eee');
    -- 比男生最大年龄还大的女士 大于所有
    SELECT * FROM student WHERE gender!='男' AND age>ALL(SELECT age FROM student WHERE gender='男');
    --  大于任意 最低
    SELECT * FROM student WHERE gender!='男' AND age>ANY(SELECT age FROM student WHERE gender='男');
    -- 和aaa这个女士学同一门课的女士 
    SELECT * FROM student 
    WHERE (gender,course_id) IN (SELECT gender,course_id FROM student WHERE NAME ='aaa') AND NAME!='aaa';
    -- 查询学生和课程
    SELECT NAME,course FROM student s,(SELECT * FROM course) c WHERE s.`course_id`=c.id ORDER BY NAME; 
    -- 相关子查询
    SELECT NAME, (SELECT course FROM course c WHERE s.`course_id`=c.id) FROM student s ORDER BY NAME;

  16. 了解事务

    1
    2
    3
    4
    5
    #手动开事务,将若干个语句作为一个原子操作
    star transaction;
    语句
    commit;#提交
    rollback;#回滚