王明昌博客如果你对该文章中的内容有疑问/不解,可以点击此处链接提问
要注明问题和此文章链接地址 点击此处跳转
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
增加数据 insert into 表名 values(1,'ss'),(null,'xx'),(null,'cc'); 更新数据 update 表名 set name=值 where ID=值; 删除数据 delete fron 表名 where id=""; 查看数据 select *from 表名 改字符集 set names 'gbk'; 备份数据库(先退出数据库) mysqldump -uroot -proot sql3 >e:sql3.sql 还原数据库 mysql -uroot -proot < C:\backup.sql 备注 COMMENT '备注' |
统计count
|
1 2 |
select count(id) from score; |
求和sum
|
1 2 |
select sum(mat) from score; |
平均值avg
|
1 2 |
select avg(mat) from score; |
最大最小值
|
1 2 3 |
select max(mat) from score; select min(chinese)from score; |
比较运算符
|
1 2 3 |
select mat from score where mat>60; select * from score where mat>60; |
逻辑运算符
|
1 2 3 4 5 6 |
select *from score where mat>60 and chinese>60; select *from score where mat>60 or chinese >60; select *from score where mat!=90; |
去重
distinct只能跟要去重的字段
#select distinct 去重字段 from 表名;
|
1 2 3 |
select distinct sex from stu; select distinct chinese from score; |
分组
group by
|
1 2 3 4 5 6 7 8 9 |
select count(sex),sex from stu group by sex; +------------+-----+ | count(sex) | sex | +------------+-----+ | 6 | 0 | | 5 | 1 | +------------+-----+ |
in() 和 not in()
相当于或的关系
|
1 2 3 4 |
select * from score where chinese in (90,80,70,60); select *from score where chinese not in (50,90); |
like 模糊查询
- 我% 前面匹配
- %我 后面匹配
- %我% 全匹配
|
1 2 3 4 |
select *from score where chinese like '7%'; select *from score where chinese like '%7'; select *from score where chinese like '%7%'; |
limit
limit限制显示个数,要放到最后
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select *from score limit 6; select *from score limit 1, 6; +----+-----+---------+ | id | mat | chinese | +----+-----+---------+ | 2 | 55 | 70 | | 3 | 67 | 94 | | 4 | 56 | 90 | | 5 | 69 | 87 | | 6 | 89 | 43 | | 7 | 88 | 66 | +----+-----+---------+ |
排序order by
==排序==
默认正序asc 倒序desc
|
1 2 3 4 |
select *from score order by id desc; select *from score order by mat desc; |
union
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
|
1 2 3 4 |
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 |
多表查询
|
1 2 3 4 5 6 7 |
select s.id,s.name,s.sex,sc.mat,sc.chinese from stu as s,score as sc where s.id=sc.id; |
子查询
|
1 2 3 |
大于平均值的 select *from score where mat >(select avg(mat) from score); |
