mysql查询非官方语言使用_MySQL查询语言操作

笔记:查询语言操作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);