数据库的简单操作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 '备注'

select count(id) from score;

select sum(mat) from score;

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;

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

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

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

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

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 |
+----+-----+---------+

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

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