Mysql数据库的Sql语句笔记

login Mysql Client under Ubuntu 18.04

sudo mysql -uroot -p--login
exit/quit/contrl+d--退出
select version();--查看版本
select now();--查看时间

数据库的基本操作

show databases;--显示所有数据库
create database Example charset = utf8;--创建一个名为Example、编码为utf8的数据库。
show create database Example;--查看创建数据库的语句
drop database Example;--删除名为Example的数据库
drop database `Example-04`;--删除名为Example-04的数据库，与上文不同的是``包含的是一个整体。
use Example;--使用名为Example的数据库。
select database();--查看当前选择的数据库

数据表的基本操作

show tables;--查看当前数据库中所有的表
create table student(
id int unsigned not null auto_increment primary key,--创建字段名为id，类型为int unsigned，不能为空，自动递增且为主键。
name varchar(30),--创建字段名为name的30字节的varchar，char与varchar的区别是char(30)固定为多余部分是空格填充的30个字节，varchar(30)为自动延--长的30个字节。
age tinyint unsigned default 0,--创建名为age的tinyint unsigned格式且默认为0的字-段。
high decimal(5,2),--创建名为high，decimal的意思是小数，(5,2)的意思是共五位，保留两位小数。
gender enum('男','女','中性','保密') default '保密',--创建名为gender，在'男','女','中性','保密'中遍历的值，默认为'保密'
cls_id int unsigned
);--创建名为student的数据表
desc student;--查看student表的数据结构
show create table student;--查看创建表的语句
drop table student;--删除名为student的数据表

数据的基本操作
查询数据

insert into student values(0,18,188.88,'male',0,'老王');--插入一条数据
select * from student;--查看student表所有数据

and、or、not的用法

select * from student where age < 18;--选择age小于18的数据
select id,name,gender from student where age < 18;-- 选择age小于18的id,name,gender
select * from student where age >18 and age <28;--and用法
select * from student where age >18 or high >180;--or用法
select * from student where not(age >18 or high >180);--not的用法

update的用法

update student set gender = 1;--设置所有性别都为male
update student set gender = 1 where id = 3;--设置id = 3的gender为male，这里的where作用类似if 

模糊查询like、rlike

select * from student where name like '小%';--%替换一个或多个或没有
select * from student where name like '小_';--_替换一个
select * from student where name rlike '^小.*';--正则表达式匹配以'小'开头的名字
select * from student where name rlike '^小.*明$'；--以小开头，明结尾的正则表达式

条件查询

select * from student where age in (18,21,25);--in表示在不连续的范围内，相当于多个or
select * from student where age not in (18,21,25);--not in 表示不在范围内
select * from student where age between 18 and 34;--between and选择age在18至34连续范围内的数据
select * from student where not age  between 18 and 34;--age不在18-34范围内的数据
select * from student where age not between 18 and 34;--age不在18-34范围内的数据
select * from student where high is null;--判断为空
select * from student where high is not null;--判断不为空

排序

select * from student where not age  between 18 and 34 order by age asc;--按age从小到大排序	
select * from student where not age  between 18 and 34 order by age desc;--按age从大到小排序	
select * from student where not age  between 18 and 34 order by age desc,id desc;--按age从大到小排序,age中一样大的按照id从大到小排序	

聚合

select count(*) from student;
select sum(age) from student;
select avg(age) from student;
select max(age) from student;
select min(age) from student;
select round(sum(age)/count(*),2) from student;

分组

select gender from student group by gender;--按gender分组
select gender,count(*) from student group by gender;--先按gender分组，并显示每组的count
select gender,count(*) from student where gender = 1 group by gender;--计算gender为male的人数
select gender,group_concat(name) from student where gender = 1 group by gender;--查询gender为male的人数，并显示姓名
select gender,group_concat(name) from student group by gender having count(*) > 2;--筛选分组后组数大于2的数据,与where不同，having是对虚拟的查询结果分类，而where是对原表进行筛选。

分页

select * from student limit 0,5;--显示从0开始共5条数据---------limit(（第N页-1）*每页的个数，每页的个数)，limit需要放在语句的末尾

链接查询

select * from student inner join classes on student.cls_id = classes.id;--建立位于student表cls_id与classes表id字段内连接对应的表。
select student.*,classes.name from student inner join classes on student.cls_id = classes.id;--只显示固定字段
select s.*,c.name from student as s inner join classes as c on s.cls_id = c.id;--改表名
select s.*,c.name from student as s inner join classes as c on s.cls_id = c.id order by c.name id;--按照班级排序，随后按id排序
select * from student as s left join classes as c on s.cls_id = c.id;--左连接
select * from student as s left join classes as c on s.cls_id = c.id having c.id is null;--使用having找出null值,从原表查询用where，结果查询用having