时间：2021-07-01 10:21:17 帮助过：10人阅读
mysql> select * from user1; ‘; +----+----------+-----+-----+------+---------+-----------+ | id | username | age | sex | addr | married | salary | +----+----------+-----+-----+------+---------+-----------+ | 1 | 李四 | 23 | 男 | 北京 | 1 | 50000.00 | | 2 | 周星星 | 27 | 女 | 上海 | 0 | 25000.00 | | 3 | 凌凌漆 | 31 | 女 | 北京 | 0 | 40000.00 | | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | | 7 | 汪峰 | 42 | 男 | 深圳 | 1 | 95000.00 | | 8 | 刘德华 | 58 | 男 | 广州 | 0 | 115000.00 | | 9 | 吴亦凡 | 28 | 男 | 北京 | 0 | 75000.00 | | 10 | 奶茶妹 | 18 | 女 | 北京 | 1 | 65000.00 | | 11 | 刘嘉玲 | 36 | 女 | 广州 | 0 | 15000.00 | +----+----------+-----+-----+------+---------+-----------+ -- 添加desc字段 VARCHAR(100) ALTER TABLE user1 ADD userDesc VARCHAR(100); mysql> UPDATE user1 SET userDesc=‘user description‘ WHERE id<=9; Query OK, 9 rows affected (0.01 sec) Rows matched: 9 Changed: 9 Warnings: 0 mysql> SELECT * FROM user1; +----+----------+-----+-----+------+---------+-----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+-----------+------------------+ | 1 | 李四 | 23 | 男 | 北京 | 1 | 50000.00 | user description | | 2 | 周星星 | 27 | 女 | 上海 | 0 | 25000.00 | user description | | 3 | 凌凌漆 | 31 | 女 | 北京 | 0 | 40000.00 | user description | | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | user description | | 7 | 汪峰 | 42 | 男 | 深圳 | 1 | 95000.00 | user description | | 8 | 刘德华 | 58 | 男 | 广州 | 0 | 115000.00 | user description | | 9 | 吴亦凡 | 28 | 男 | 北京 | 0 | 75000.00 | user description | | 10 | 奶茶妹 | 18 | 女 | 北京 | 1 | 65000.00 | NULL | | 11 | 刘嘉玲 | 36 | 女 | 广州 | 0 | 15000.00 | NULL | +----+----------+-----+-----+------+---------+-----------+------------------+ 11 rows in set (0.00 sec) -- 查询用户userDesc 为NULL的用户 SELECT id,username,age,userDesc FROM user1 WHERE userDesc=NULL; -- 检测NULL值 SELECT id,username,age,userDesc FROM user1 WHERE userDesc<=>NULL; -- IS [NOT] NULL检测NULL值 SELECT id,username,age,userDesc FROM user1 WHERE userDesc IS NULL; -- 测试范围BETWEEN AND -- 查询年龄在18~30之间的用户 SELECT id,username,age,sex FROM user1 WHERE age BETWEEN 18 AND 30; -- 查询薪水在10000~50000之间的用户 SELECT id,username,age,salary FROM user1 WHERE salary BETWEEN 10000 AND 50000; SELECT id,username,age,salary FROM user1 WHERE salary NOT BETWEEN 10000 AND 50000; -- 测试指定集合 IN -- 查询编号为1,3,5,7,9 SELECT id,username,age FROM user1 WHERE id IN(1,3,5,7,9,29,45,78); SELECT id,username,age FROM user1 WHERE username IN(‘king‘,‘queen‘,‘lily‘,‘rose‘); -- 测试逻辑运算符 -- 查询性别为男并且年龄>=20的用户 SELECT id,username,age,sex FROM user1 WHERE sex=‘男‘ AND age>=20; -- id>=5 && age<=30 SELECT id,username,age,sex FROM user1 WHERE id>=5 AND age<=30; SELECT id,username,age,sex FROM user1 WHERE id>=5 AND age<=30 AND sex=‘男‘; -- 要求sex=‘女‘ 并且 addr=‘北京‘ SELECT id,username,age,sex,addr FROM user1 WHERE sex=‘女‘ AND addr=‘北京‘; -- 查询薪水范围在60000~10000并且性别为男 addr=‘北京‘ SELECT id,username,age,sex,salary,addr FROM user1 WHERE salary BETWEEN 60000 AND 100000 AND sex=‘男‘ AND addr=‘北京‘; -- 查询id=1 或者 用户名为queen SELECT id,username,age FROM user1 WHERE id=1 OR username=‘queen‘; -- 测试模糊查询 SELECT id,username,age FROM user1 WHERE username=‘king‘; SELECT id,username,age FROM user1 WHERE username LIKE ‘king‘; -- 要求用户名中包含三 SELECT id,username,age,sex FROM user1 WHERE username LIKE ‘%三%‘; -- 用户名中包含n SELECT id,username,age FROM user1 WHERE username LIKE ‘%in%‘; -- 要求查询出姓张的用户 SELECT id,username,age FROM user1 WHERE username LIKE ‘张%‘; -- 查询以风结尾的用户 SELECT id,username,age FROM user1 WHERE username LIKE ‘%风‘; -- 用户名长度为三位的用户 SELECT id,username,age,sex FROM user1 WHERE username LIKE ‘___‘; SELECT id,username,age,sex FROM user1 WHERE username LIKE ‘张_‘; SELECT id,username,age,sex FROM user1 WHERE username LIKE ‘张_%‘; -- 测试分组 -- 按照性别分组sex SELECT id,username,age,sex FROM user1 GROUP BY sex; -- 按照addr分组 SELECT username,age,sex,addr FROM user1 GROUP BY addr; -- 按照性别分组，查询组中的用户名有哪些 SELECT GROUP_CONCAT(username),age,sex,addr FROM user1 GROUP BY sex; SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1 GROUP BY sex; -- 测试COUNT() SELECT COUNT(*) FROM user1; SELECT COUNT(id) FROM user1; -- 按照sex分组，得到用户名详情，并且分别组中的总人数 SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1 GROUP BY sex; -- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和 SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM user1 GROUP BY addr; -- 按照sex分组，统计组中总人数、用户名详情，得到薪水总和，薪水最大值、最小值、平均值 SELECT sex, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(salary) AS sum_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, AVG(salary) AS avg_salary FROM user1 GROUP BY sex; SELECT GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1 GROUP BY sex WITH ROLLUP; -- 按照字段的位置来分组 SELECT id,sex, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(salary) AS sum_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, AVG(salary) AS avg_salary FROM user1 GROUP BY 2; -- 查询age>=30的用户并且按照sex分组 SELECT sex,GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1 WHERE age>=30 GROUP BY sex; -- 按照addr分组，统计总人数 SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1 GROUP BY addr; -- 对于分组结果进行二次筛选，条件是组中总人数>=3 SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1 GROUP BY addr HAVING COUNT(*)>=3; SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1 GROUP BY addr HAVING totalUsers>=3; -- 按照addr分组， SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(salary) AS sum_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, AVG(salary) AS avg_salary FROM user1 GROUP BY addr; -- 要求平均薪水>=40000 SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(salary) AS sum_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, AVG(salary) AS avg_salary FROM user1 GROUP BY addr HAVING avg_salary>=40000; -- 测试排序 -- 按照id降序排列 SELECT id,username,age FROM user1 ORDER BY id DESC; -- 按照age升序 SELECT id,username,age FROM user1 ORDER BY age ; -- 按照多个字段排序 SELECT id,username,age FROM user1 ORDER BY age ASC,id ASC; -- 测试条件+排序 SELECT id,username,age FROM user1 WHERE age>=30; SELECT id,username,age FROM user1 WHERE age>=30 ORDER BY age DESC; -- 实现随机记录 SELECT id,username,age FROM user1 ORDER BY RAND(); -- 测试LIMIT语句 -- 显示结果集的前5条记录 SELECT id,username,age,sex FROM user1 LIMIT 5; SELECT id,username,age,sex FROM user1 LIMIT 0,5; -- 显示前3条记录 SELECT id,username,age,sex FROM user1 LIMIT 0,3; SELECT id,username,age,sex FROM user1 LIMIT 3,3; -- 更新前3条记录，将age+5 UPDATE user1 SET age=age+5 LIMIT 3; -- 按照id降序排列，更新前三条记录，将age-10 UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3; -- 删除前三条记录 DELETE FROM user1 LIMIT 3; DELETE FROM user1 ORDER BY id DESC LIMIT 3; -- 测试完整SELECT 语句的形式 SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM user1 WHERE id>=2 GROUP BY addr; SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM user1 WHERE id>=2 GROUP BY addr HAVING totalUsers>=2; SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM user1 WHERE id>=2 GROUP BY addr HAVING totalUsers>=2 ORDER BY totalUsers ASC; SELECT addr, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age FROM user1 WHERE id>=2 GROUP BY addr HAVING totalUsers>=2 ORDER BY totalUsers ASC LIMIT 0,2; mysql> SELECT id,username,age,sex FROM user1; +----+----------+-----+-----+ | id | username | age | sex | +----+----------+-----+-----+ | 1 | 李四 | 23 | 男 | | 2 | 周星星 | 27 | 女 | | 3 | 凌凌漆 | 31 | 女 | | 4 | 张三 | 38 | 男 | | 5 | 张三风 | 38 | 男 | | 6 | 张子怡 | 39 | 女 | | 7 | 汪峰 | 42 | 男 | | 8 | 刘德华 | 58 | 男 | | 9 | 吴亦凡 | 28 | 男 | | 10 | 奶茶妹 | 18 | 女 | | 11 | 刘嘉玲 | 36 | 女 | +----+----------+-----+-----+ 11 rows in set (0.00 sec) mysql> SELECT id,username,age,sex FROM user1 -> GROUP BY addr; +----+----------+-----+-----+ | id | username | age | sex | +----+----------+-----+-----+ | 2 | 周星星 | 27 | 女 | | 1 | 李四 | 23 | 男 | | 8 | 刘德华 | 58 | 男 | | 7 | 汪峰 | 42 | 男 | +----+----------+-----+-----+ 4 rows in set (0.03 sec) mysql> SELECT id,username,age,sex,addr FROM user1 -> GROUP BY addr; +----+----------+-----+-----+------+ | id | username | age | sex | addr | +----+----------+-----+-----+------+ | 2 | 周星星 | 27 | 女 | 上海 | | 1 | 李四 | 23 | 男 | 北京 | | 8 | 刘德华 | 58 | 男 | 广州 | | 7 | 汪峰 | 42 | 男 | 深圳 | +----+----------+-----+-----+------+ 4 rows in set (0.00 sec) mysql> SELECT id,age,sex,GROUP_CONCAT(addr,username) FROM user1 -> GROUP BY sex; +----+-----+-----+-------------------------------------------------------------+ | id | age | sex | GROUP_CONCAT(addr,username) | +----+-----+-----+-------------------------------------------------------------+ | 1 | 23 | 男 | 北京李四,北京吴亦凡,上海张三,上海张三风,广州刘德华,深圳汪峰 | | 10 | 18 | 女 | 北京奶茶妹,北京张子怡,北京凌凌漆,上海周星星,广州刘嘉玲 | +----+-----+-----+-------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) AS user_total FROM user1; +------------+ | user_total | +------------+ | 11 | +------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(userDesc) FROM user1; +-----------------+ | COUNT(userDesc) | +-----------------+ | 9 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT GROUP_CONCAT(username) FROM user1 -> GROUP BY sex; +-------------------------------------+ | GROUP_CONCAT(username) | +-------------------------------------+ | 李四,吴亦凡,张三,张三风,刘德华,汪峰 | | 奶茶妹,张子怡,凌凌漆,周星星,刘嘉玲 | +-------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT sex,GROUP_CONCAT(username),addr FROM user1 -> GROUP BY sex; +-----+-------------------------------------+------+ | sex | GROUP_CONCAT(username) | addr | +-----+-------------------------------------+------+ | 男 | 李四,吴亦凡,张三,张三风,刘德华,汪峰 | 北京 | | 女 | 奶茶妹,张子怡,凌凌漆,周星星,刘嘉玲 | 北京 | +-----+-------------------------------------+------+ 2 rows in set (0.00 sec) mysql> SELECT sex,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users FROM user1 -> GROUP BY sex; +-----+-------------------------------------+-------------+ | sex | users_detail | total_users | +-----+-------------------------------------+-------------+ | 男 | 李四,吴亦凡,张三,张三风,刘德华,汪峰 | 6 | | 女 | 奶茶妹,张子怡,凌凌漆,周星星,刘嘉玲 | 5 | +-----+-------------------------------------+-------------+ 2 rows in set (0.00 sec) mysql> SELECT sddr,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users,MAX(age) AS max_age, MIN(age) AS min_age,SUM(age) AS sum_age,AVG(age) AS -> avg_age FROM user1 GROUP BY addr; ERROR 1054 (42S22): Unknown column ‘sddr‘ in ‘field list‘ mysql> SELECT sddr,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users,MAX(age) AS max_age, MIN(age) AS min_age,SUM(age) AS sum_age,AVG(age) AS -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘‘ at line 1 mysql> SELECT addr,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users,MAX(age) AS max_age, MIN(age) AS min_age,SUM(age) AS sum_age,AVG(age) AS -> avg_age FROM user1 GROUP BY addr; +------+----------------------------------+-------------+---------+---------+---------+---------+ | addr | users_detail | total_users | max_age | min_age | sum_age | avg_age | +------+----------------------------------+-------------+---------+---------+---------+---------+ | 上海 | 周星星,张三,张三风 | 3 | 38 | 27 | 103 | 34.3333 | | 北京 | 李四,奶茶妹,吴亦凡,张子怡,凌凌漆 | 5 | 39 | 18 | 139 | 27.8000 | | 广州 | 刘德华,刘嘉玲 | 2 | 58 | 36 | 94 | 47.0000 | | 深圳 | 汪峰 | 1 | 42 | 42 | 42 | 42.0000 | +------+----------------------------------+-------------+---------+---------+---------+---------+ 4 rows in set (0.01 sec) mysql> SELECT addr,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users,MAX(age) AS max_age, MIN(age) AS min_age,SUM(age) AS sum_age,AVG(age) AS -> avg_age FROM user1 GROUP BY sex; +------+-------------------------------------+-------------+---------+---------+---------+---------+ | addr | users_detail | total_users | max_age | min_age | sum_age | avg_age | +------+-------------------------------------+-------------+---------+---------+---------+---------+ | 北京 | 李四,吴亦凡,张三,张三风,刘德华,汪峰 | 6 | 58 | 23 | 227 | 37.8333 | | 北京 | 奶茶妹,张子怡,凌凌漆,周星星,刘嘉玲 | 5 | 39 | 18 | 151 | 30.2000 | +------+-------------------------------------+-------------+---------+---------+---------+---------+ 2 rows in set (0.00 sec) mysql> SELECT sex,GROUP_CONCAT(username) AS users_detail,COUNT(*) AS total_users,MAX(age) AS max_age, MIN(age) AS min_age,SUM(age) AS sum_age,AVG(age) AS -> avg_age FROM user1 GROUP BY sex; +-----+-------------------------------------+-------------+---------+---------+---------+---------+ | sex | users_detail | total_users | max_age | min_age | sum_age | avg_age | +-----+-------------------------------------+-------------+---------+---------+---------+---------+ | 男 | 李四,吴亦凡,张三,张三风,刘德华,汪峰 | 6 | 58 | 23 | 227 | 37.8333 | | 女 | 奶茶妹,张子怡,凌凌漆,周星星,刘嘉玲 | 5 | 39 | 18 | 151 | 30.2000 | +-----+-------------------------------------+-------------+---------+---------+---------+---------+ 2 rows in set (0.00 sec) mysql> SELECT GROUP_CONCAT(username) AS userDetail FROM user1 WHERE age>=30 GROUP BY sex; +-------------------------+ | userDetail | +-------------------------+ | 张三,张三风,汪峰,刘德华 | | 凌凌漆,张子怡,刘嘉玲 | +-------------------------+ 2 rows in set (0.01 sec) mysql> SELECT sex,GROUP_CONCAT(username) AS userDetail FROM user1 WHERE age>=30 GROUP BY sex; +-----+-------------------------+ | sex | userDetail | +-----+-------------------------+ | 男 | 张三,张三风,汪峰,刘德华 | | 女 | 凌凌漆,张子怡,刘嘉玲 | +-----+-------------------------+ 2 rows in set (0.00 sec) mysql> SELECT sex,GROUP_CONCAT(username) AS userDetail,COUNT(*) AS totalUsers FROM user1 WHERE age>=30 GROUP BY sex; +-----+-------------------------+------------+ | sex | userDetail | totalUsers | +-----+-------------------------+------------+ | 男 | 张三,张三风,汪峰,刘德华 | 4 | | 女 | 凌凌漆,张子怡,刘嘉玲 | 3 | +-----+-------------------------+------------+ 2 rows in set (0.00 sec) mysql> SELECT adde,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary, -> SELECT adde,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary,; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SELECT adde,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(s‘ at line 2 mysql> SELECT addr,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary, -> MIN(salary) AS min_of_salary, AVG(salary) AS avg_of_salary FROM user1 GROUP BY addr; +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | addr | userDetail | total_users | sum_salary | max_of_salary | min_of_salary | avg_of_salary | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | 上海 | 周星星,张三,张三风 | 3 | 55000.00 | 25000.00 | 15000.00 | 18333.333333 | | 北京 | 李四,奶茶妹,吴亦凡,张子怡,凌凌漆 | 5 | 315000.00 | 85000.00 | 40000.00 | 63000.000000 | | 广州 | 刘德华,刘嘉玲 | 2 | 130000.00 | 115000.00 | 15000.00 | 65000.000000 | | 深圳 | 汪峰 | 1 | 95000.00 | 95000.00 | 95000.00 | 95000.000000 | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ 4 rows in set (0.01 sec) mysql> SELECT addr,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary, -> MIN(salary) AS min_of_salary, AVG(salary) AS avg_of_salary FROM user1 GROUP BY addr ORDER BY salary; +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | addr | userDetail | total_users | sum_salary | max_of_salary | min_of_salary | avg_of_salary | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | 上海 | 周星星,张三,张三风 | 3 | 55000.00 | 25000.00 | 15000.00 | 18333.333333 | | 北京 | 李四,奶茶妹,吴亦凡,张子怡,凌凌漆 | 5 | 315000.00 | 85000.00 | 40000.00 | 63000.000000 | | 深圳 | 汪峰 | 1 | 95000.00 | 95000.00 | 95000.00 | 95000.000000 | | 广州 | 刘德华,刘嘉玲 | 2 | 130000.00 | 115000.00 | 15000.00 | 65000.000000 | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ 4 rows in set (0.01 sec) mysql> SELECT addr,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary, -> MIN(salary) AS min_of_salary, AVG(salary) AS avg_of_salary FROM user1 GROUP BY addr ORDER BY sum_salary; +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | addr | userDetail | total_users | sum_salary | max_of_salary | min_of_salary | avg_of_salary | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | 上海 | 周星星,张三,张三风 | 3 | 55000.00 | 25000.00 | 15000.00 | 18333.333333 | | 深圳 | 汪峰 | 1 | 95000.00 | 95000.00 | 95000.00 | 95000.000000 | | 广州 | 刘德华,刘嘉玲 | 2 | 130000.00 | 115000.00 | 15000.00 | 65000.000000 | | 北京 | 李四,奶茶妹,吴亦凡,张子怡,凌凌漆 | 5 | 315000.00 | 85000.00 | 40000.00 | 63000.000000 | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ 4 rows in set (0.01 sec) mysql> SELECT addr,GROUP_CONCAT(username) AS userDetail, COUNT(*) AS total_users, SUM(salary) AS sum_salary, MAX(salary) AS max_of_salary, -> MIN(salary) AS min_of_salary, AVG(salary) AS avg_of_salary FROM user1 GROUP BY addr HAVING avg_of_salary>=40000; +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | addr | userDetail | total_users | sum_salary | max_of_salary | min_of_salary | avg_of_salary | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ | 北京 | 李四,奶茶妹,吴亦凡,张子怡,凌凌漆 | 5 | 315000.00 | 85000.00 | 40000.00 | 63000.000000 | | 广州 | 刘德华,刘嘉玲 | 2 | 130000.00 | 115000.00 | 15000.00 | 65000.000000 | | 深圳 | 汪峰 | 1 | 95000.00 | 95000.00 | 95000.00 | 95000.000000 | +------+----------------------------------+-------------+------------+---------------+---------------+---------------+ 3 rows in set (0.00 sec) mysql> sekect * from user1 -> limit 5; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘sekect * from user1 limit 5‘ at line 1 mysql> select * from user1 limit 5; +----+----------+-----+-----+------+---------+----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+----------+------------------+ | 1 | 李四 | 23 | 男 | 北京 | 1 | 50000.00 | user description | | 2 | 周星星 | 27 | 女 | 上海 | 0 | 25000.00 | user description | | 3 | 凌凌漆 | 31 | 女 | 北京 | 0 | 40000.00 | user description | | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | +----+----------+-----+-----+------+---------+----------+------------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM user1 LIMIT 0, 3; +----+----------+-----+-----+------+---------+----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+----------+------------------+ | 1 | 李四 | 23 | 男 | 北京 | 1 | 50000.00 | user description | | 2 | 周星星 | 27 | 女 | 上海 | 0 | 25000.00 | user description | | 3 | 凌凌漆 | 31 | 女 | 北京 | 0 | 40000.00 | user description | +----+----------+-----+-----+------+---------+----------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM user1 LIMIT 3, 3; +----+----------+-----+-----+------+---------+----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+----------+------------------+ | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | user description | +----+----------+-----+-----+------+---------+----------+------------------+ 3 rows in set (0.00 sec) mysql> UPDATE user1 SET age=age+5 WHERE id<=3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM user1 LIMIT 0, 3; +----+----------+-----+-----+------+---------+----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+----------+------------------+ | 1 | 李四 | 28 | 男 | 北京 | 1 | 50000.00 | user description | | 2 | 周星星 | 32 | 女 | 上海 | 0 | 25000.00 | user description | | 3 | 凌凌漆 | 36 | 女 | 北京 | 0 | 40000.00 | user description | +----+----------+-----+-----+------+---------+----------+------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM user1 ORDER BY id DESC UPDATE user1 SET age=age+10 WHERE id<=3; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘UPDATE user1 SET age=age+10 WHERE id<=3‘ at line 1 mysql> SELECT * FROM user1 ORDER BY id DESC; +----+----------+-----+-----+------+---------+-----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+-----------+------------------+ | 11 | 刘嘉玲 | 36 | 女 | 广州 | 0 | 15000.00 | NULL | | 10 | 奶茶妹 | 18 | 女 | 北京 | 1 | 65000.00 | NULL | | 9 | 吴亦凡 | 28 | 男 | 北京 | 0 | 75000.00 | user description | | 8 | 刘德华 | 58 | 男 | 广州 | 0 | 115000.00 | user description | | 7 | 汪峰 | 42 | 男 | 深圳 | 1 | 95000.00 | user description | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 3 | 凌凌漆 | 36 | 女 | 北京 | 0 | 40000.00 | user description | | 2 | 周星星 | 32 | 女 | 上海 | 0 | 25000.00 | user description | | 1 | 李四 | 28 | 男 | 北京 | 1 | 50000.00 | user description | +----+----------+-----+-----+------+---------+-----------+------------------+ 11 rows in set (0.00 sec) mysql> UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 0, 3; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘ 3‘ at line 1 mysql> UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3; Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM user1; +----+----------+-----+-----+------+---------+-----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+-----------+------------------+ | 1 | 李四 | 28 | 男 | 北京 | 1 | 50000.00 | user description | | 2 | 周星星 | 32 | 女 | 上海 | 0 | 25000.00 | user description | | 3 | 凌凌漆 | 36 | 女 | 北京 | 0 | 40000.00 | user description | | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | user description | | 7 | 汪峰 | 42 | 男 | 深圳 | 1 | 95000.00 | user description | | 8 | 刘德华 | 58 | 男 | 广州 | 0 | 115000.00 | user description | | 9 | 吴亦凡 | 18 | 男 | 北京 | 0 | 75000.00 | user description | | 10 | 奶茶妹 | 8 | 女 | 北京 | 1 | 65000.00 | NULL | | 11 | 刘嘉玲 | 26 | 女 | 广州 | 0 | 15000.00 | NULL | +----+----------+-----+-----+------+---------+-----------+------------------+ 11 rows in set (0.00 sec) mysql> DELETE FROM user1 LIMIT 3; Query OK, 3 rows affected (0.00 sec) mysql> SELECT * FROM user1; +----+----------+-----+-----+------+---------+-----------+------------------+ | id | username | age | sex | addr | married | salary | userDesc | +----+----------+-----+-----+------+---------+-----------+------------------+ | 4 | 张三 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 5 | 张三风 | 38 | 男 | 上海 | 0 | 15000.00 | user description | | 6 | 张子怡 | 39 | 女 | 北京 | 1 | 85000.00 | user description | | 7 | 汪峰 | 42 | 男 | 深圳 | 1 | 95000.00 | user description | | 8 | 刘德华 | 58 | 男 | 广州 | 0 | 115000.00 | user description | | 9 | 吴亦凡 | 18 | 男 | 北京 | 0 | 75000.00 | user description | | 10 | 奶茶妹 | 8 | 女 | 北京 | 1 | 65000.00 | NULL | | 11 | 刘嘉玲 | 26 | 女 | 广州 | 0 | 15000.00 | NULL | +----+----------+-----+-----+------+---------+-----------+------------------+ 8 rows in set (0.00 sec)
MySQL中常用语句2
标签：near 平均值 name 语句 off 函数 mat 详细 error