笔记:查询语言操作DQL
操作数据表tb_train_cpp2为例:
SELECT * FROM tb_train_cpp2;[查询全部字段]
SELECT userId,userName,salary FROM tb_train_cpp2;[查询指定字段]
SELECT userName AS '姓名',age AS '年龄',hobby AS '兴趣' FROM tb_train_cpp2;[为指定字段起别名进行查询]
SELECT * FROM tb_train_cpp2 WHERE age=28;[WHERE:比较运算符]
SELECT * FROM tb_train_cpp2 WHERE age<25;
SELECT * FROM tb_train_cpp2 WHERE age>30;
SELECT * FROM tb_train_cpp2 WHERE hobby is NULL;[WHERE:NULL/NOT NULL]
SELECT userId,userName,userCard FROM tb_train_cpp2 WHERE hobby IS NOT NULL AND age<20;
SELECT userId,userName,userCard FROM tb_train_cpp2 WHERE hobby IS NOT NULL AND sex='男';
SELECT * FROM tb_train_cpp2 WHERE nationName LIKE '__';[WHERE:LIKE模糊查询]
SELECT * FROM tb_train_cpp2 WHERE userCard LIKE '1%5';
SELECT * FROM tb_train_cpp2 WHERE userName LIKE '孙%';
注:_:表示匹配单个字符/%:匹配0,1,多个字符
SELECT * FROM tb_train_cpp2 WHERE salary BETWEEN 85 AND 95;
SELECT sex,GROUP_CONCAT(userName),COUNT(*) FROM tb_train_cpp2 WHERE age<30 GROUP BY sex;
SELECT * FROM tb_train_cpp2 ORDER BY salary DESC;[按指定字段顺序排列:ASC升序/DESC降序]
SELECT * FROM tb_train_cpp2 ORDER BY age ASC;
SELECT * FROM tb_train_cpp2 WHERE userName IN('曹操','甄姬');
SELECT * FROM tb_train_cpp2 WHERE userName LIKE '__' AND nationName IN('中国','英国','美国');
ALTER TABLE tb_train_cpp2 ADD country CHAR(5) AFTER sex;[添加字段country]
UPDATE tb_train_cpp2 SET country='蜀国' WHERE userName IN('关羽','张飞','黄月英','刘备','赵子龙');
UPDATE tb_train_cpp2 SET country='魏国' WHERE userName IN('曹操','甄姬');
UPDATE tb_train_cpp2 SET country='吴国' WHERE userName IN('孙尚香','太史慈','孙权');
SELECT country,GROUP_CONCAT(userName),COUNT(*) FROM tb_train_cpp2 GROUP BY country;
SELECT sex,GROUP_CONCAT(userName),GROUP_CONCAT(userCard),COUNT(*) FROM tb_train_cpp2 GROUP BY sex;
SELECT sex,GROUP_CONCAT(userName),AVG(age) FROM tb_train_cpp2 WHERE country IN('魏国','蜀国') GROUP BY sex;
SELECT country,GROUP_CONCAT(userName),
COUNT(*) AS '组中人数',
MAX(age) AS '最大年龄',
MIN(salary) AS '最小能力值'
FROM tb_train_cpp2 WHERE age<30 AND userName LIKE '__'
GROUP BY country;
SELECT age,GROUP_CONCAT(userName),COUNT(*) FROM tb_train_cpp2[HAVING字句只能接分组查询]
WHERE salary BETWEEN 85 AND 95 GROUP BY age
HAVING COUNT(*)>1;
SELECT * FROM tb_train_cpp2 WHERE country IN('吴国','魏国') AND userName LIKE '__'
ORDER BY age LIMIT 1;
创建数据表tb_train_cpp22[以后做外键操作]
CREATE TABLE IF NOT EXISTS tb_train_cpp22(
cid INT UNSIGNED KEY AUTO_INCREMENT,
cname CHAR(5) NOT NULL UNIQUE,
people_num INT UNSIGNED
)ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_cpp22(cname,people_num) VALUES
('魏国',1500),
('吴国',1250),
('蜀国',980);
