sql基础语句

准备工作

安装mysql 官网安装 MySQL Community Server

cd到安装目录的bin文件夹 cd mysql/bin

启动mysql服务：net start mysql

登录mysql账号：mysql -u root -p

查看数据库：show databases

创建数据库：create database school character set utf8

使用school数据库：use school

查看当前数据库的表：show tables

删除表：drop table students

删除数据库：drop database school

create 创建

创建students表

create table `students`
(
`id` bigint(20) not null AUTO_INCREMENT,
`class_id` bigint(20) null,
`name` varchar(8) null,
`gender` varchar(2) null,
`score` int(3) null,
primary key(`id`)   
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建classes表

create table `classes`
(
`id` bigint(20) not null AUTO_INCREMENT,
`name` varchar(5) null,
primary key(`id`)   
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into 插入

students表插入数据

insert into students (class_id, name, gender, score) values
  (1, '小明', 'B', 90),
  (1, '小红', 'G', 95),
  (1, '小军', 'B', 88),
  (1, '小米', 'G', 73),
  (2, '小白', 'G', 81),
  (2, '小兵', 'B', 55),
  (2, '小林', 'B', 85),
  (3, '小新', 'G', 91),
  (3, '小王', 'B', 89),
  (3, '小丽', 'G', 88);

id	class_id	name	gender	score
1	1	小明	B	90
2	1	小红	G	95
3	1	小军	B	88
4	1	小米	G	73
5	2	小白	G	81
6	2	小兵	B	55
7	2	小林	B	85
8	3	小新	G	91
9	3	小王	B	89
10	3	小丽	G	88

classes表插入数据

insert into classes (name) values
  ('一班'),
  ('二班'),
  ('三班'),
  ('四班');

id	name
1	一班
2	二班
3	三班
4	四班

select 查询

基础查询 from

select 1;	//测试数据库连接

select * from students;

select name from classes;

select score points from classes;   //列可以重命名

条件查询 where…and…or…in (_,_)…between and like

select * from students where score>=80;

select * from students where gender='B';

select * from students where class_id<>2;

select * from students where score>=80 and gender='G';

select * from students where score>=80 or gender='B';

select name from students where (score < 80 or score > 90) and gender = 'B';

select * from students where score like '9%';

select * from students where name not like '小%';

排序 order by desc

select * from students order by score;

select * from students order by score desc;

select * from students order by class_id,score;

select id, name, gender, score
from students
where class_id = 1
order by score ;

分页查询 limit _ offset _

select * from students order by class_id,score limit 3 offset 0;
//每页3条数据，查询第1页

聚合查询 count(*) sum avg max min group by

select count(*) num from students;   //查询行数  num重命名

select count(*) num from students where gender='B';

select avg(score) average from students where gender='G';  //平均成绩

select class_id,count(*) num from students group by class_id;  //各班人数

select class_id,gender,count(*) num from students group by class_id,gender; 

select class_id,avg(score) average from students group by class_id;

多表查询

select * from students，classes;   //返回列数为两个表列数乘积

连接查询 inner join right outer join left outer join full outer join

select s.id, s.name, c.name class, s.gender, s.score
from students s
inner join classes c
on s.class_id = c.id;

update 更新

update set更新数据

update students set name='大牛', score=66 where id=1;

update students set score=score+10 where score<=80;

delete 删除

delete from students where id=1;