Mysql基础命令(第一部分)

1.数据库相关

//返回可用数据库的一个列表。包含在这个列表中的可能是MySQL内部使用的数据库
show databases;
//选定某个数据库
use imooc;
//获得一个数据库内的表的列表
show tables;
//用来显示表列
show columns from users;
//用来显示表列,和上面语句类似
describe user;
//用于显示广泛的服务器状态信息
show status;
//用来显示授予用户（所有用户或特定用户）的安全权限
show grants;

2.检索数据

//检索单个列
select id from users;
//检索多个列
select id,username from users;
//检索所有列
select * from users;
//去重检索
select distinct id from users;
//不能部分使用DISTINCT DISTINCT关键字应用于所有列而
//不仅是前置它的列。如果给出SELECT DISTINCT id,
//name，除非指定的两个列都不同，否则所有行都将被
//检索出来。

//限制查询结果
select id from users limit 5;
//LIMIT 2, 1指示MySQL返回从行2开始的1行,简单理解为从第3行开始(包括第三行),限制1条
select id from users limit 2,1;
//等同于上面的语句
select id from users limit 1 offset 2;
//使用完全限定的表名以及列名
select users.id from imooc.users;

3.排序数据

//按照单列排列,默认时升序的
select created_time from users order by created_time;
//按照单列升序排列
select created_time from users order by created_time asc;
//按照多列排列,默认时升序的
select created_time ,id from users order by created_time,id;
//按照单列降序排列
select created_time from users order by created_time desc;
//按照多列排列,修改时间降序,id升序
select created_time ,id from users order by created_time desc,id;
//使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值
select id from users order by id limit 1;
//使用ORDER BY和LIMIT的组合，也能够找出一个列中第二高的值
select id from users order by id limit 1 offset 1;
//在给出ORDER BY子句时，应该保证它
//位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY
//之后。使用子句的次序不对将产生错误消息

4.过滤数据

//查询username=imooc的用户信息
select * from users where username="imooc";
//在同时使用ORDER BY和WHERE子句时，应
//该让ORDER BY位于WHERE之后，否则将会产生错误

//查询创建时间在一个范围内的值(a<=created_time<=b)
select * from users where created_time between "2019-08-14" and "2021-01-01";
//在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字
//分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值

//查询null值
select * from users where realname is null;

//多条件匹配查询,用and连接
select * from users where realname is null and username ='imooc';

//匹配任一条件查询,用or连接
select * from users where realname is null or username ='imooc';

//注意and和or的优先级问题,这条语句会识别为
//select * from users where realname is null和select * from users where  username ='imooc' //and sex =2
select * from users where realname is null or username ='imooc' and sex =2;

//加上()即可解决这个问题
select * from users where (realname is null or username ='imooc') and sex =2;

//in操作符 
//查询id是1和2的用户
select * from users where id in(1,2);

//not in操作符 
//查询id不是1或者2的用户
select * from users where id not in(1,2);

//MySQL支持使用NOT对IN、BETWEEN和
//EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件
//取反有很大的差别。

5.使用通配符过滤

//查询所有以i开头的用户在搜索串中，%表示任何字符出现任意次数
select * from users where username like  'i%';

//根据MySQL的配置方式，搜索可以是区分大小
//写的。如果区分大小写，'i%'与Imooc将不匹配

//查询名字包含im的用户信息,重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。%
//代表搜索模式中给定位置的0个、1个或多个字符,所以用户命为im也可以找到
select * from users where username like  '%im%';

//虽然似乎%通配符可以匹配任何东西，但有一个例
//外，即NULL。即使是username like LIKE '%'也不能匹配
//用值NULL作为用户名的用户


//另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符
//查询名字包含i_m的用户,_可以是任何一个字符,但不能为空
select * from users where username like  '%i_m%';

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。
•  在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

6.创建计算字段

// Concat()函数来拼接两个列
// 查询对应的id和用户名,并讲其拼接
select concat(id,'->',username) from users;

// 去掉username两边的空格,注意' username '变成'username',如果是'user name'无法变成
// 'username'
select concat(id,'->',Trim(username)) from users;

// 使用别名
select concat(id,'->',username) as myname from users;

// SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以
// 省略FROM子句以便简单地访问和处理表达式。例如，SELECT3*2;将返回6，SELECT Trim('abc');
// 将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据
// 需要使用SELECT进行试验


// 执行运算操作
select (age+1) as newage from users;

7.使用函数

//使用upper()方法将字符转成大写字符
select upper(username) from users;

//首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一
//个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为
//格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的
//日期格式可能也行，但这是首选的日期格式，因为它排除了多义性


//如果不适用Date函数,只能查询到'2019-08-14 00:00:00'的数据,使用了只要是这个时间内都可以查到,不管
//是00:00:00,还是23:59:59
select * from users where Date(created_time)='2019-08-14';

//查询'2019-08-14 00:00:00'到'2019-08-15 00:00:00'的值
select * from users where created_time between '2019-08-14' and '2019-08-15';

//查询'2019-08-14 00:00:00'到'2019-08-15 23:59:59'的值
select * from users where Date(created_time) between '2019-08-14' and '2019-08-15';

//查询'2019-08-18 00:00:00'的值
select * from users where Year(created_time)=2019 and Month(created_time)=08 and Day(created_time)=18

 

8.汇总数据

//查询用户的平均年龄
//AVG()函数忽略列值为NULL的行
select avg(age) from users;

//使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空
值（NULL）还是非空值
//使用COUNT(column)对特定列中具有值的行进行计数，忽略
NULL值
//如果指定列名，则指定列的值为空的行被COUNT()函数忽略，
//但如果COUNT()函数中用的是星号（*），则不忽略

//查询用户总数
select count(*)from users;
//查询username不为空的总数
select count(username)from users;

//查询出最大年龄的用户
select max(age) from users;

//虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大
//值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行
//MAX()函数忽略列值为NULL的行

//查询出所有用户年龄之和,SUM()函数忽略列值为NULL的行
select sum(age) from users;



//查询出年龄不重复的用户数量
select  count(distinct age) from users;

9.分组数据

//查询不同年龄的用户数量
select age,count(*) from user group by age;


//查询年龄分组中分组数量大于2的分组
select age,count(*) from user  group by age having  count(*)>2;

//这条语句是有问题的,因为where语句不能用聚集函数作为查询条件
select age,count(*) from user where count(*)>2 group by age ;

//查询不同年龄的用户数量并按照数量降序
select age,count(*) from user group by age order by count(*) desc;

//查询不同年龄的用户数量并按照数量降序,同时数量要大于1,注意group by语句在having语句前面,order by语句在最后
select age,count(*) from user group by age having count(*)>1 order by count(*) desc;

GROUP BY语句的规定:

•  GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组。 
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。