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个: 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);

本文地址：https://blog.csdn.net/weixin_44857400/article/details/107166789