MYSQL查询语句
MYSQL复杂操作语句
select * from d_user
#查询表中的单条内容
select name from d_user
#查询表中的多条内容
select name, age from d_user
#查询id为36的用户
select * from d_user where id = 22
#查询表中name为yanqin的用户
select * from d_user where name = "yanqin"
#查询age大于35的用户
select * from d_user where age>35
#查询age等于35的用户
select * from d_user where age = 35
#查询age不等于35的用户
select * from d_user where age != 35
select * from d_user where age <> 35
#查询年龄大于35,并且是男人
select * from d_user where age >=35 and gender = 'nan'
#查询年龄大于等于40,或者邮箱为002@qq.COM
select * from d_user where age>=40 or email = '002@qq.com'
#在SQL,NULL查询不能使用= != ,应该使用is is not
select * from d_user where email is null;
#查询当前列表中,用户有多少人
select COUNT(*) from d_user
SELECT count(id) from d_user
#计算出当前用户年龄总和
select SUM(age) from d_user where gender = 'nan';
#计算出当前用户年龄的平均值
select avg(age) from d_user where gender = 'nan';
#查询出当前用户的最大年龄
select max(age) from d_user;
#查询出当前用户年龄的最小值
select min(age) from d_user;
# as别名
select count(*) as counts(任意字符) from d_user;
# as关键字可以省略
select count(*)梁山泊 from d_user;
#求区间范围内的
select * from d_user where age>=18 and age<=35;
#between and
select *from d_user where age between 18 and 35;
#in慎用 查询效率低下
select * from d_user where age in(25,27,28);
#模糊查询
select * from d_user where name like '宋%';
select * from d_user where name like '%江';
#在模糊查询中 _一个下划线表示一个位置
# %任意匹配
select * from d_user where name like '_江';
#order by表示排序 asc (默认)表示升序
select * from d_user order by age asc
#order by表示排序 desc (默认)表示降序
select * from d_user order by age desc
#先以年龄降序排列,如果年龄重复,则按照ID降序排列
select * from d_user order by age desc,id desc
#分组 group by
select age,count(*) from d_user group by age;
#查询同一年大于5的组
#having表示分组后的筛选,如果出现,一定有group by
select age,count(*) as counts from d_user group by age having count(*)>5;
#分页
#分页在不同的DBSN是不一样的 top
/*
当前页 pageNow用户
一页显示多少条 pagesize程序员
共有多少页 pageCount算出来 math.floor(allcount/pagesize)向上取整
共有多少条数据 allcount通过count()查取出来
*/
select * from d_user limit 10,10; (pageNow - 1)*pagesize
#一条完整的查询语句
select字段1,字段2....
from表名
[where条件]
[group by]
[having]
[order by]
[limit]
MYSQL多表查询方法
create table stu(
id int PRIMARY key auto_increment,
name varchar(50) not null,
major varchar(100) not null,
tel varchar(100) unique,
depid varchar(50)
);
create table dep(
id int primary key auto_increment,
name varchar(100) not null,
tel varchar(100) unique
);
#查询名字叫做李四的学生系部
select dep.name from dep,stu
where stu.name = '李四' and stu.depid = dep.id
select depid from stu where stu.name = '李四'
select name from dep
where id = (select depid from stu where stu.name = '李四')
#cross join 交叉查询
#尽量的避免使用交叉查询,因为会产生笛卡尔积现象
select * from stu,dep
#第二种写法
select * from stu cross join dep
#内连接(第一个表 INNER JOIN 第二个表 on 外键)
select * from stu inner join dep on(stu.depid = dep.id)
#内连接的变种写法
select * from stu,dep where stu.depid = dep.id
#外连接
#左外连接,右外连接
#左外连接(按照stuid进行排序)
select * from stu as e left join dep d on(e.depid = d.id)
#右外连接(按照depid进行排序)
select * from stu as e right join dep d on(e.depid = d.id)
select e.*,d.name as depname from stu as e right join dep d on(e.depid = d.id)
函数部分
select length('李仕康'); #utf_8里面一个汉字占用三个字节
select char_length('李仕康'); #不论中英文,都显示当前的字数
select char_length('xxxx');
select trim(' 李仕康 '); #清除字符串两边的空格
select substring('lishikang is good bood !!!',4,5); #(不是从零开始,是从一开始)从第四 位开始,截取五位
select ascii('0');
select ascii('a');
select round(20.49); #四舍五入
select now(); #获取系统当前时间
#curdata + curtime = now
select curdate(); #获取年月日
select curtime(); #获取时分秒
网友评论