查询
SELECT * FROM user;
SELECT user. * FROM user;
查询管理员编号和名称
SELECT id username*FROM user;
表来自哪个数据库下
SELECT id,username FROM user;
字段来自哪张表
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;
给表名起别名
SELECT id,username FROM cms_admin AS a;
SELECT a.id,a.username FROM cms.cms_admin AS a;
给字段起别名
SELECT id AS ‘编号’,username AS ‘用户名’ FROM cms_admin;
SELECT a.id AS id1,a.username AS u FROM cmd_admin AS a;
WHERE 条件
查询编号为1的用户
SELECT id,username FROM cms_admin WHERE id=1;
查询编号不为1的用户
SELECT id,username FROM cms_admin WHERE id!=1;
添加age字段
ALTER TABLE cms_admin ADD age TINYINT UNSIGNED DEFAULT 18;
INSERT cms_admin(username,password,age)
VALUES(‘test’,’tes’,NULL);
查询表中记录age值为NULL
SELECT *FROM cms_admin WHERE age<=>NULL;
IS NULL 查询表中记录age值为NULL
SELECT *FROM cms_admin WHERE age IS NULL;
SELECT *FROM cms_admin WHERE age IS NOT NULL;#除了NULL以外的
查询编号3到10之间的用户
SELECT *FROM cms_admin WHERE id BETWEEN 3 AND 10;
查询编号为1,3,5的;( 忽略大小写)
SELECT * FROM cms_admin WHERE id IN(1,3,5);
模糊查询
%: 代表0个一个或者多个任意字符
_:代表1个任意字符
查询姓张的用户
SELECT *FROM cms_admin WHERE username LIKE ‘%张’;
查询用户名中包含in的用户
SELECT *FROM cms_admin WHERE username LIKE ‘%in%’;
查询用户名为3位的用户
SELECT *FROM cms_admin WHERE username LIKE ‘_‘;
用户名_i%
SELECT *FROM csm_admin WHERE username LIKE ‘_I%’;
查询用户名为king并且密码为king的用户名
SELECT *FROM csm_admin WHERE username=’king’ AND password=’king’;
查询编号大于3的变量年龄不为NULL的用户
SELECT *FROM smc_admin WHERE id>3 AND age IS NOT NULL;
查询编号大于3的变量年龄不为NULL 的用户并且proid为3
SELECT *FROM cms_admin WHERE id>3 AND age IS NOT NULL AND proid=3;
查询用户名为以张开始或者用户名所在的身份为2,4的记录
SELECT *FROM cms_admin WHERE username LIKE ‘张%’ OR proid IN(2,4);
按照用户所属的身份分组proid
SELECT *FROM cms_admin GROUP BY proid;
向用户表中添加性别字段
ALTER TABLE csm_admin ADD sex ENUM(‘男’,’女’,’保密’);
UPDATE cms_admin SET sex=’男’ WHERE id IN(1,2,3,4,5);
UPDATE cms_admin SET sex=’男’ WHERE id IN(6,7,8,9,10);
UPDATE cms_admin SET sex=’男’ WHERE id IN(11,12);
按照用户性别分组
SELECT *FROM cms_admin GROUP BY sex;
按照字段位置分组
SELECT *FROM cms_admin GROUP BY 8;#8指的是name的位置
按照多个字段分组
SELECT *FROM cms_admin GROUP BY sex,age;
查询编号大于3的用户按sex分组
SELECT *FROM cms_admin WHERE id>3 GROUP BY sex;
查询详情 ( GROUP_CONCAT(name) )
查询id,sex,用户名详细按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_admin GROUP BY sex;
查询prodi,性别详情,注册时间详情,用户名详情,按照proid分组
SELECT proid,GROUP_CONCAT(sex),GROUP_CONCAT(username),GROUP_CONCAT(time) FROM cms_admin GROUP BY proid;
查询编号,sex,用户名详情以及组中总人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username) AS user1 ,COUNT(*) FROM totalUsers csm_admin GROUP BY sex;
统计表中所有记录
SELECT COUNT(*) AS totalusers FROM cms_admin;
COUNT(字段) 不统计NULL值;
SELECT COUNT(age) AS toltalUsers FROM csm_admin;
查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,以及年龄总和
SELECT id,sex,GROUP_CONCAT(usrname),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;
WITH ROLLUP 统计MAX,MIN,COUNT,等
SELECT id,sex,GROUP_CONCAT(usrname),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;
查询性别sex,用户名详情,组中总人数,最大年龄,年龄总和
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM coms_admin
GROUP BY sex;
查询组中人数大于2 的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM coms_admin
GROUP BY sex
HAVING COUNT(*)>2;
查询组中人数大于2并且最大年龄大于60的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM coms_admin
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;
按照ID降序排列DESC 默认的是ASC
SELECT *FROM cms_admin ORDER BY DESC;
按照年龄升序排列
SELECT *FROM csm_admin ORDER BY age ASC;
SELECT *FROM sma_admin ORDER BY 1 DESC;#1是指name的位置
按照年龄顺序,id降序排列
SELECT *FROM scm_admin ORDER BY age ASC,id DESC;
实现随机记录
SELECT *FROM cms_admin ORDER BY RAND();
查询表中前3条记录
SELECT *FROM csm_admin LIMIT 3;
查询表中的前一条几率
SELECT *FROM cms_admin LIMIT 0,1; #0是起始位置,1第几条