数据库的简单操作03-函数

增加数据
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

select count(id) from score;

求和sum

select sum(mat) from score;

平均值avg

select avg(mat) from score;

最大最小值

select max(mat) from score;
select min(chinese)from score;

比较运算符

select mat from score where mat>60;
select * from score where mat>60;

逻辑运算符

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 表名;

select distinct sex from stu;
select distinct chinese from score;

分组

group by

select count(sex),sex from stu group by sex;
+------------+-----+
| count(sex) | sex |
+------------+-----+
|          6 |   0 |
|          5 |   1 |
+------------+-----+

in() 和 not in()

相当于或的关系

select * from score where chinese in (90,80,70,60);
select *from score where chinese not in (50,90);

like 模糊查询

• 我% 前面匹配
• %我 后面匹配
• %我% 全匹配

select *from score where chinese like '7%';
select *from score where chinese like '%7';
select *from score where chinese like '%7%';

limit

limit限制显示个数,要放到最后

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

select *from score order by id desc;
select *from score order by mat desc;

union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

多表查询

select 
s.id,s.name,s.sex,sc.mat,sc.chinese
from 
stu as s,score as sc
where
s.id=sc.id;

子查询

大于平均值的
select *from score where mat >(select avg(mat) from score);