插入记录 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) * 每页显示条数, 每页显示条数;