Mysql数据库常用sql语句

1.数据库 test

连接:  mysql -uroot -p密码
退出: quit/exit/ctrl + D
查看-当前: select database();
	所有: show databases;
创建: create database test charset=utf8;
使用: use test;
删除: drop database test;

2.数据表 student

查看所有表: show tables;
查看表的结构: desc student;
创建表: create table student (id int unsigned primary key auto_increment not null,
							 name varchar(20) default "",
							 gender enum("male","female"),
							 is_delete bit default 0);
删除表: drop table student;
修改-添加字段: alter table student add age tinyint unsigned default 0;
修改字段的类型和约束: alter table student modify age int unsigned default 18;
修改字段名字: alter table student change name newname decimal(5,2) unsigned;
删除字段: alter table student drop age;

3.表中数据的增删改查

增-多行插入: insert into student values(0,"xiaoming","male"),(0,"xiaohua","female");
   部分插入: insert into student(name,height) values("xiaoming",180);
删-物理删除: delete from student where id=3;
   逻辑删除: alter table student add is_delete bit default 0;
	   	    update student set id_delete=1 where id=3;
改: update student set age=20 where id=3;
查-查询所有数据: select * from student;
   查询指定字段: select name,gender from student;
   范围查询: select * from student where id>3;
   as指定字段和表的别名: select name as n, gender as g from student as c;

4.查询

指定字段消除重复行: select distinct gender from student;
条件查询: >  <  =  !=  <>  >=  <=
		select * from student where age>18;
		and, or, not 
		select * from student where age=18 or age=20;
模糊查询: select * from student where name like "_%";
范围查询: in, not in, between a and b, not between a and b  
		 select * from student where age not in (18,20);
		 select * from student where age not between 18 and 25;
空判断: select * from student where age is not null;

5.排序

select * from student order by age desc;
聚合函数-数量: count(*)
		最大值: max(age)
		最小值: min(age)
		求和: sum(age)
		平均值: avg(age)
	    保留小数: round(avg(age),2)

6.分组

按字段分组: select gender from student group by gender;
计算分组中的数量: select gender,count(*) from student group by gender;
查询分组中的数据: select gender,group_concat(name),avg(age) from student  group by gender;
条件查询: select gender from student group by gender having avg(age)>18;
汇总: select gender,group_concat(name) from student group by gender with rollup;

7.分页

第n页, 每页显示d个。[从第(n-1)*d条数据起显示d条数据]: select * from student order by age limit (n-1)*d,d;

8.连接查询

内连接: select c.name,s.* from student as s inner join class as c on s.cls_id=c.id;
左连接: select c.name,s.* from student as s left join class as c on s.cls_id=c.id;
右连接: select c.name,s.* from student as s right join class as c on s.cls_id=c.id;
子查询:select name from students where age>(select avg(age) from students);
	  select * from students where cls_id in(select id from class);

9.联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROM table1 
UNION 
SELECT column_name(s) FROM table2;

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

10.按时间查询

查询今天的数据:
SELECT * FROM 表名 WHERE to_days(时间字段) = to_days(now());
SELECT * FROM test_date where DATE_SUB(CURDATE(), INTERVAL 0 DAY) <= create_date;
查询昨天的数据:
SELECT * FROM 表名 WHERE to_days(now())-to_days(时间字段) <= 1;
SELECT * FROM test_date where DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= create_date;
查询最近七天的数据:
SELECT * FROM 表名 WHERE date_sub(curdate(), interval 6 day) <= date(时间字段);
查询最近一个月的数据:
SELECT * FROM test_date where DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= create_date;
SELECT * FROM test_date where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= create_date;
查询本月:
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
select * from t_order where date_format(date,'%Y-%m')=date_format(now(),'%Y-%m');
查询上月:
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
查询指定月份：
select * from t_order where month(date)='6';
查询当前这周的数据:
SELECT * FROM enterprise WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据:
SELECT * FROM enterprise WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d')) = YEARWEEK(now())-1;
按周，天，月份查询：
-时间格式
SELECT DATE_FORMAT(create_time,'%Y%u') weeks FROM student GROUP BY weeks;  
SELECT DATE_FORMAT(create_time,'%Y%m%d') days FROM student GROUP BY days;   
SELECT DATE_FORMAT(create_time,'%Y%m') months FROM student GROUP BY months;
-时间戳格式
SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks FROM student GROUP BY weeks;  
SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days FROM student GROUP BY days;   
SELECT FROM_UNIXTIME(create_time,'%Y%m') months FROM student GROUP BY months;