MySQL 教程 - 06 - 记录的增删改查

插入记录 INSERT

  -- 向表中的指定字段,插入一条记录. 建议使用.
    INSERT INTO 表名 (字段名, 字段名....)  VALUES  (字段值, 字段值.......);

    -- 向表中所有字段,插入一条记录.
    -- 该记录的字段值依次与表中对应位置的字段的数据类型匹配, 若全部符合, 才可以成功插入.
    INSERT INTO 表名 VALUES  (字段值, 字段值.......);

    -- 向表中所有字段, 插入多条记录.
    INSERT INTO 表名 VALUES  (字段值, 字段值.......), (字段值, 字段值.......), .......; 

    CREATE TABLE user(
        id SHALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        usernane VARCHAR(40) NOT NULL,
        password VARCHAR(32) NOT NULT,
        age TINYINT UNSIGNED NOT NULL DEFAULI 20,
        sex BOOLEAN
    );

    -- id 字段如果采用自动编号, 则可以使用两种特殊的值, 让其使用:
    -- 使用 NULL
    INSERT INTO user VALUES(NULL,'Tom','123',20,'1');

    -- 使用 DEFAULT
    INSERT INTO user VALUES(DEFAULT,'Bob','456',20,'1');

    1. 字段值可以使用表达式.
    2. char型的字段的值都需要使用引号 ''.
    3. age字段的默认值为20, 所以该字段可以使用 DEFAULT 为年龄赋值20.  
       age 字段是 int 型的数据, 所以值不加引号 ''.
    4. 性别字段是 boolean 型的, 使用 ‘0’ 或者 ‘1’.

使用子查询插入记录 INSERT SET/SELECT

    INSERT INTO 表名 SET 列名 = xx, 列名 = xx, .....;

    -- 将查询结果全部插入到指定数据表中.
    INSERT INTO 表名 SELECT ...      

    -- 将查询结果部分插入到指定数据表中.
    INSERT INTO 表名(列名, 列名.....) SELECT ...

单表更新记录 UPDATE

    -- 更改表中的所有记录的该字段的值
    UPDATE 表名 SET 字段 = 新的字段值;

    -- 更改表中的指定记录的该字段的值
    UPDATE 表名 SET 字段 = 新的字段值  WHERE 条件;

    -- IGNORE 关键字.  即使更新发生错误,也不终止,直到更新完毕.
    UPDATE IGNORE 表名 SET ...

    -- 表中所有记录的年龄值都增加5.  
    UPDATE user SET age = age + 5;

    -- 表中所有记录的年龄值都减去其id值, 并且将性别都改为0.
    UPDATE user SET age = age - id, sex = 0;

    -- 表中 id 为偶数的记录的年龄值增加10.   
    UPDATE user SET age = age + 10 WHERE id % 2 = 0;

    -- 多个表的 UPDATE 操作 (根据 B表 修改 A表 的数据)
    UPDATE Software A, T_Software B SET A.UninstallName = B.UninstallName WHERE A.Id =B.Id

单表删除记录 DELETE

    -- 删除表中所有记录.  但是不会删除表的约束,并且删除的数据可以回滚.
    DELETE FROM 表名;   

    -- 删除表中符合条件的记录
    DELETE FROM 表名  WHERE 条件;

    -- 清空表中的所有记录.  会同时删除表的约束,且删除的数据不能回滚.  这个命令实际上是删除旧表,创建新表而已.
    TRUNCATE TABLE 表名;

    -- 删除表中的第 6 条记录.  
    DELETE FROM user WHERE id = 6;

    -- 删除无佣金的员工
    delete from emp where comm is null;

drop table : 属于DDL , 不可回滚 , 不可带 where 条件, 表内容和结构全部删除 , 删除速度快.
truncate table : 属于DDL , 不可回滚 , 不可带where , 表内容全部删除 , 删除速度快.
delete from : 属于DML , 可回滚 , 可带 where 条件, 不删除表结构, 表内容要看 where 条件执行的情况. 删除速度慢, 需要逐行删除.

普通查询 SELECT

  -- 查询所有字段. 查询该表所有记录的所有字段的值. * 代表所有字段.
    SELETC * FROM 表名;         

    -- 查询指定字段.
    -- 查询字段的顺序可以和表中字段的顺序不一致, 查询的顺序会影响显示的顺序.
    -- 可以在字段名前加上其所属的表名.
    SELETC 字段名, ...  FROM 表名; 

    -- 只查看 id 列和 username 列        
    SELECT id,username FROM user;


    -- 设置查询字段的别名.
    -- 字段的别名也会影响结果集的名字, 比如 SELECT id username FROM user; 容易看错为查询id和username两个字段.
    -- 字段赋别名的时候，as虽然可以省略 但最好不要这样做.
    -- SELETC 字段名 AS 别名, ...  FROM 表名;    

    -- 设置查看id列的别名为编号    
    SELECT id AS ‘编号’ FROM user;

    -- 设置查询的表的别名.
    -- 注意: 表的别名不能使用字符串.
    SELETC 字段名, ...  FROM 表名 AS 别名;        

    -- 设置查询的表的别名为u         
    SELECT u.id FROM user AS u;

    -- 给查询结果添加常量列.
    -- 只是在查询结果里显示常量列, 原表中不会添加该常量列.
    SELECT 字段名, '常量列的字段值' AS '常量列的列名' FROM 表名;

    SELECT username, 'People' As '物种' FROM users;
    -- 结果视图
    username 物种
    Jeck     People
    Mary     People
    Bob      People

    -- 查询时合并指定列.
    -- 将两个字段的值相加后输出在一列.  输出的列名为: 字段1 + 字段2 , 并且只能合并相同数值类型的字段.
    SELECT (字段1 + 字段2) FORM 表名;        

    -- 查询时去重.
    -- 重复的字段值只会显示一条.
    -- distinct 关键字将被应用在所有的后面出现的列. 而不仅仅是它的后置列.
    SELECT DISTINCT 字段名 FROM 表名;        
    -- 或者
    SELECT DISTINCT (字段名) FROM 表名;

    -- 统计不同国家的数量  
    SELECT DISTINCT country FROM user;

条件查询 WHERE 语句

对记录进行查询时, 默认显示所有记录, 可以通过 WHERE 子句, 指定显示的记录.
在WHERE表达式中, 可以使用MYSQL支持的函数或者运算符.

(1) 逻辑条件： and 和, 并且 or 或者. and 的优先级高于 or. 建议使用 ( )

    -- 查询id为2, 且姓名为李四的学生.
    SELECT * FROM student WHERE id=2 AND username='李四';     -- 交集

    -- 查询id为2, 或者姓名为张三的学生.
    SELECT * FROM student WHERE id=2 OR NAME='张三';     -- 并集

(2) 比较条件： > < >= <= =(等于) <>(不等于) between and (两者之间, 包头包尾)

    -- 查询 servlet 成绩大于70分的学生.
    SELECT * FROM student WHERE servlet > 70;

    -- 查询jsp成绩大于等于75, 且小于等于90分的学生.
    SELECT * FROM student WHERE jsp >= 75 AND jsp <= 90;
    或者 SELECT * FROM student WHERE jsp BETWEEN 75 AND 90;

    -- 查询女生.
    SELECT * FROM student WHERE gender <> '男';
    SELECT * FROM student WHERE gender = '女';

(3) 判空条件 (null 空字符串)： is null / is not null / = ” / <> ”
null 表示没有值, 而空字符串是有值的.

    -- 查询地址为空的学生. (包括null和空字符串)
    SELECT * FROM student WHERE address is null OR address = ’’;

    -- 查询地址不为空的学生. (不包括null和空字符串)
    SELECT * FROM user WHERE address is not null AND address <> '';

(4) 模糊条件： like

_ 表示任意一个字符, % 表示任意多个字符.

    -- 查询姓 ’陈’ 的学生.
    SELECT * FROM user WHERE username LIKE '陈%';

    -- 查询姓 ’陈’ , 且姓名有三个字的学生.
    SELECT * FROM user WHERE username LIKE '陈__';

    -- 查询名字中包含有 ’艳’ 字的人.
    SELECT * FROM user WHERE username LIKE ‘%艳%’;

    -- 查询名字以 ’艳’ 字结尾的人.
    SELECT * FROM user WHERE username LIKE ‘%艳’;

若要匹配通配符, 则需要进行转义, 声明转义字符 ESCAPE

    -- 查询含有 % 字符的人.
    SELECT * FROM user WHERE username LIKE '%/%%' escape '/';

(5) IN 操作符 IN 可以指定多个条件.

    -- 查询 servlet 成绩是 100分 和 90分 的学生.
    SELECT * FROM student WHERE servlet in (100,90);

IN 操作符可以和 OR 语句相互转换. 但是 IN 的执行速度更快.

    -- 查询 servlet 成绩是 100分 和 90分 的学生.
    SELECT * FROM student WHERE servlet = 100  OR  servlet = 90;

(6) NOT 操作符 可以否定它后面跟着的所有条件.

    -- 查询 servlet 成绩不是 100分 和 90分 的学生.
    SELECT * FROM student WHERE servlet not in (100,90);

聚合查询

使用聚合函数的查询.
常用的聚合函数： sum() avg() max() min() count()

-- 查询所有学生的java的总成绩 ( sum() 求和函数 )
    SELECT SUM(java) AS 'java的总成绩' FROM student;

    -- 查询学生的java的平均分.
    SELECT AVG(java) AS 'java的平均分' FROM student;

    -- 查询当前java最高分
    SELECT MAX(java) AS '最高分' FROM student;

    -- 查询当前java最低分
    SELECT MIN(java) AS '最低分' FROM student;

    -- 统计当前有多少学生 ( count(字段) )
    SELECT COUNT(*) FROM student;            // 统计所有字段的值的个数, 取最大值
    或SELECT COUNT(id) FROM student;         // 统计id字段的值的个数
        注意：count() 函数统计的数量不包含值为 null 的数据. 使用 count 统计表的记录数，要使用不包含 null 值的字段, 比如主键.

分组查询 GROUP BY 子句

    -- 把指定字段中, 值相同的记录归为一组(值相同的记录只显示最先出现的一个), 并按照 ASC 升序(默认)或者 DESC 降序排列.
    -- ASC  顺序, 正序. 数值：递增，字母：自然顺序a-z
    -- DESC 倒序, 反序. 数值：递减，字母：自然反序z-a
    SELECT * FROM 表名 GROUP BY 列名 ASC | DESC ;

    -- SQL语法规定: 若使用 group by 子句, 那么 select 之后的字段(除了聚集函数之外)都必须出现在 group by 子句中, 
                   可以比 group by 中的字段出现的少, 但不能包含 group by 中没有的字段.

    SELECT * FROM USER;
    SELECT * FROM USER GROUP BY username ;
        -- 该句也可以写为: GROUP BY 列号;   列号,表示结果集的第n列, n 从 1 开始.
        -- 那么在该句中, 需要按照 username 分组, 在结果集中, username 列是第二列, 所以列号是 2.  即,可以写为: GROUP BY 2;
    SELECT USER.*,COUNT(*) FROM USER GROUP BY username;
        -- 在有分组的情况下, count() 函数是统计分组之后的每组记录的数量.

分组查询并筛选 HAVING 子句

    -- HAVING 子句用来对分组之后的记录进行条件筛选.
    -- HAVING 子句的条件字段必须出现在查询的结果集中, 或者使用聚合函数.

    -- 在 GROUP BY 子句中的条件查询, 不能再跟 WHERE 子句了, 必须使用 HAVING 子句.  
    -- 即 分组之前的条件查询使用 WHERE 子句, 分组之后的条件查询使用 HAVING 子句.  WHERE 用来过滤行数组, HAVING 用来过滤分组数据.

    -- 查询 username 出现次数大于 1 次的记录.
    SELECT *,COUNT(*) FROM user GROUP BY username HAVING COUNT(*) > 1;

对查询结果排序 ORDER BY 子句

    --  OREDR BY 列名 ASC(默认) | DESC;

    -- 对 id 降序排序
    SELECT * FROM user ORDER BY id DESC;

    -- 先按照 age 降序排序, 遇到相同的 age 时, 对 id 进行升序排序.
    SELECT * FROM user OREDR BY age DESC, id ASC;

限制查询数量 LIMIT 子句

    -- 默认情况下, 查询会返回所有满足条件的结果. 
    -- 使用 LIMIT 子句可以限制查询返回的结果数量.
    LIMIT (起始行, 查询几行);      -- 注意: 起始行从0开始.    

    -- MySql5 中建议使用
    SELECT * FROM xx LIMIT 查询几行 OFFSET  起始行.

    -- 只查询前2条记录.        
    SELECT * FROM user LIMIT 2;

    -- 查询从第3条记录开始的4条记录.        
    SELECT * FROM user LIMIT 2, 4;

    -- 查询第5,6条记录            
    SELECT * FROM user LIMIT 4,2;

    -- 查询按照id降序排列从第5条记录开始的4条记录.
    -- 先查询所有记录, 按照id降序排列
    SELECT * FROM user ORDER BY id DESC;
    -- 然后对上面返回的结果限制数量  
    SELECT * FROM user ORDER BY id DESC LIMIT 4, 4;    

    -- 找出价格最高的商品
    SELECT product_name FROM product ORDER BY product_price DESC LIMIT 1;                           

    -- 查询第n高的工资
    SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1

分页查询

    -- 分页查询当前页的数据的sql:
    SELECT * FROM user LIMIT (当前页数-1) * 每页显示条数, 每页显示条数;