数据库语句学习

-- DELETE FROM  mynate WHERE user_sex='女'; 删除数据


-- TRUNCATE TABLE user_scores;  清空数据


#查询和提取数据的过程#  
-- ORDER BY 排序  WHERE 条件  GROUP BY进行分组    HAVING 条件 统计结果条件


#SELECT  user_qq,user_name,user_sex,user_birthday,user_mobile FROM users; 查询
#SELECT *FROM users;
#SELECT user_qq FROM users;


# 别名
-- SELECT user_qq AS '玩家qq' ,user_name AS '玩家昵称'
-- FROM users;
-- SELECT user_qq '玩家qq' ,user_name '玩家昵称'
-- FROM users;


--   DISTINCT 去重复
-- SELECT DISTINCT user_qq FROM user_scores;


--  LIMIT  自定结果集显示范围
--  LIMIT  2,5 表示从第二行 查询 5条
--  LIMIT 2 表示从第0条开始查 2条数据
--  SELECT * FROM user_scores LIMIT 2,5;


--  WHERE 查询条件
--  比较符号 等于 (= )  不等于 (<> )   大于(>)   大于等于(>=)  小于(<)  小于等于(<=)
-- SELECT user_qq FROM user_scores WHERE gno = 1;
-- SELECT *FROM user_scores WHERE score>70;




--   AND 并且  OR 或者 NOT 非 
-- SELECT *FROM user_scores WHERE gno=3 AND score<>30;
-- SELECT * FROM user_scores WHERE gno=3 OR gno=1;




-- BETWEEN 60 AND 70 条件查询 60~70 范围的查询 60 和 70 都包含在内
-- SELECT *FROM user_scores WHERE score BETWEEN 60 AND 70;-- 
-- BETWEEN 70 AND 60 其实就是表示 >=70 AND <=60 的判断
-- SELECT *FROM user_scores WHERE score BETWEEN 70 AND 60;


-- SELECT * FROM user_scores WHERE score NOT BETWEEN 60 AND 70;


-- 查询时间 
-- SELECT *FROM users WHERE  user_birthday BETWEEN date('1991-10-10') AND date('2001-10-10');


-- 模糊查询
-- SELECT *FROM users WHERE user_name  LIKE '孙%';
-- SELECT *FROM users WHERE user_name  NOT LIKE '孙%';


-- 查询空值
-- SELECT *FROM users WHERE user_birthday is NULL;


-- 查询不为空
-- SELECT *FROM users WHERE user_birthday is NOT NULL;




#单列排序 ASC 升序 DESC 降序


-- SELECT *FROM users  WHERE user_birthday is NOT NULL ORDER BY user_birthday ASC ; 
-- SELECT *FROM users ORDER BY user_birthday ASC;


-- SELECT * FROM user_scores ORDER BY gno ASC,score DESC;


# 聚合函数 sum()(数字类型) 对指定列中的所有非空值 求和    avg()(数字类型) 对指定列中的所有非空值 求平均  min() (数字 字符串 datetime) 求最小   max() (数字 字符串 datetime) 求最大 count() (任意类型) 求数量


-- SELECT count(users.user_birthday) FROM users;
-- SELECT count(*) FROM users; 
 
-- SELECT sum(user_scores.score) AS '总和' FROM user_scores;


-- SELECT avg(user_scores.score) AS '平均值' FROM user_scores ;


-- SELECT min(user_scores.score) AS '最小' FROM user_scores;


-- SELECT max(user_scores.score) AS '最高' from user_scores;


-- SELECT sum(user_scores.score) AS '平均', max(user_scores.score) AS '最高',min(user_scores.score) AS '最小'  FROM user_scores WHERE user_qq = '1426047433'
# GROUP BY 分组
#SELECT user_qq ,sum(user_scores.score) AS '平均', max(user_scores.score) AS '最高',min(user_scores.score) AS '最小'  FROM user_scores GROUP BY user_qq;
-- SELECT avg(user_scores.score) AS '平均分数',user_scores.user_qq AS 'QQ号' FROM user_scores GROUP BY user_qq;


-- SELECT user_qq AS '玩家QQ',sum(user_scores.score) AS '总分',avg(score) AS '平均分'  FROM user_scores WHERE score>(SELECT avg(user_scores.score) FROM user_scores) GROUP BY user_qq;
-- 
-- SELECT user_qq AS '玩家QQ',sum(user_scores.score) AS '总分',avg(score) AS '平均分'  FROM user_scores GROUP BY user_qq HAVING avg(score)>60;


-- SELECT user_scores.user_qq AS '玩家QQ', avg(user_scores.score) AS '平均分' ,sum(user_scores.score) AS '总分'  FROM user_scores  GROUP BY user_qq ORDER BY avg(user_scores.score) DESC;


#多表查询 内连接 WHERE 多个表 内联接 表与表 存在相对应的 字段 值 
 -- SELECT DISTINCT  user_name AS '玩家昵称',score AS '玩家分数' FROM users,user_scores WHERE users.user_qq=user_scores.user_qq;#0.00046 sec / 0.000013 sec




# INNER JOIN  ON 运行速度更快
-- SELECT   user_name AS '玩家昵称',score AS '玩家分数' FROM users INNER JOIN user_scores ON users.user_qq = user_scores.user_qq; #0.00042 sec / 0.000015 sec






-- WHERE user_qq=users.user_qq AND user_qq=user_scores.user_qq AND user_name = users.user_name AND score = user_scores.score;
# select 语句执行顺序  from  子句指定数据源 where 子句基于指定的条件对记录进行帅选 group by 子句将数据划分为多个组 使用聚合函数进行计算  having子句帅选分组 使用 ORDER BY  进行排序




-- SELECT user_name AS '昵称',sum(score) AS '总分',avg(score) AS '平均分'  FROM users INNER JOIN user_scores ON users.user_qq = user_scores.user_qq GROUP BY users.user_qq,users.user_name;


-- SELECT  user_name AS '昵称',sum(score) AS '总分',avg(score) AS '平均分' FROM  users INNER JOIN user_scores ON users.user_qq = user_scores.user_qq GROUP BY users.user_qq,users.user_name HAVING avg(score)>=30  ORDER BY avg(score) DESC ;




# 外链接   做链接的两个表地位不平等 其中一张是基础表   基础表中的每条数据必须出现 即使另一张表中没有数据与之匹配也要用空NULL补齐
#左连接时左表为基础表 右连接时右表是基础表


# 以 users 为基础表
-- SELECT *FROM users LEFT OUTER JOIN user_scores ON users.user_qq = user_scores.user_qq AND user_scores.gno = 1;


# 以 user_scores 为基础表
-- SELECT * FROM users RIGHT OUTER JOIN user_scores ON users.user_qq = user_scores.user_qq AND user_scores.gno = 1;




# IN 子查询
-- SELECT score FROM 
-- SELECT gno FROM user_scores WHERE gno = 2;
-- SELECT user_qq FROM users WHERE  user_qq NOT IN (SELECT user_qq FROM user_scores WHERE gno = 2);




#存在  EXISTS 
-- SELECT * FROM user_scores  WHERE EXISTS (SELECT * FROM users WHERE user_name = '八戒' );




# 联合查询  UNION  
-- SELECT user_name,user_sex FROM users UNION SELECT gno,score FROM user_scores;
-- SELECT user_qq,gno,score FROM user_scores WHERE user_qq = '1426047433' 
-- UNION ALL
-- SELECT '总分',' ',sum(score) FROM user_scores  WHERE user_qq = '1426047433' 
-- UNION ALL
--  SELECT '平均分' , '  ',avg(score) FROM user_scores WHERE user_qq = '1426047433' ;


# 日期和时间函数  curdate() 当前时间 
-- SELECT curdate() ,current_date(),curdate()+0,current_time(),curtime(),current_timestamp(),curtime()+0;


-- SELECT now() AS '当前时间',current_timestamp() AS '当前时间2';


# 当前月份
-- SELECT month(curdate());
# 当前月份名称
-- SELECT monthname(curdate());


# 当前天
-- SELECT day(curdate());


-- SELECT dayname(curdate());


-- SELECT dayofmonth(curdate());
-- SELECT dayofweek(curdate());
-- SELECT dayofyear(curdate());


-- SELECT week(curdate());
-- SELECT weekday(curdate());
-- SELECT weekofyear(curdate());
-- SELECT year(curdate());
# 季度
-- SELECT quarter(curdate());




# 数学函数


-- SELECT abs(-9); -- 绝对值
-- SELECT sign(9); -- 符号函数
--  SELECT rand(); --  随机数
-- SELECT rand(10); -- 产生的数字是固定的


-- SELECT ceil(3.5); 取不小于3.5 的最小整数
-- SELECT floor(3.5); 取不大于3.5 的最大的整数
-- SELECT round(3.4)-- 四舍五入;
-- SELECT round(3.44,1);1 表示几位小数
-- SELECT truncate(3.4567754,3); 3 表示保留几位小数 不进行四舍五入
-- SELECT mod(8,5); 求余数




-- 字符串 合并字符串 concat concat_ws
-- SELECT concat('ssss','ddddd','rrrrrr');
-- SELECT concat_ws('#','absc','frrrrrr','ffffff');


#  insert 第一个参数 源字符串  第二个参数 表示从第几个开始 ，第三个 表示 截取多长   第四个参数 表示 替换的字符
-- SELECT insert('ssfffffff',2,4,'g');


-- SELECT replace('abcdabercg','ab','f');


#截取字符串
-- SELECT left('abcde',3);
 -- SELECT right('abcdert',30);
 
 
 #填充字符串
 
 # 清除空格
-- SELECT rtrim('     ooooooo       ') AS 'COLL';
-- SELECT trim('   PPPPPP    ');


-- SELECT trim('a' FROM 'asdasdff');


# 获取子字符串
-- SELECT substring('yduasudyuf',2,4);


-- SELECT version();
-- SELECT connection_id();


# 查看当前所用数据库
-- SELECT database()-- ;


-- SELECT user();