net start mysql

mysql -u用户名 -p密码

create database 数据库名称

show databases;

use 数据库的名字；

alter database 数据库名称 default character set 编码方式 collate 编码方式_bin ;

drop database 数据库名称；

create table 表名 
 
{ 
 
    字段名 1,数据类型[完整性约束条件], 
 
    字段名 2,数据类型[完整性约束条件], 
 
    ... 
 
    字段名 n,数据类型[完整性约束条件], 
 
}

create table student2(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
)

desc 表名字 ；

方式一：

drop table 表名；

方式二：

drop table if exists 表名；

添加一行数据
格式一:所有字段设置值,值的顺序与表中字段的顺序对应
说明:主键列是自动增长,插入时需要占位,通常使用0或者default或者null来占位,插入成功后以实际数据为准

insert into 表名 value(...)
例：
insert into student2 values(0,“亚瑟”,27,178.9)

若指向name里插入数据
insert into 表名(列) values(...)
例：
insert into student2(name) valuse("亚瑟2")

添加多行数据
insert into 表名(列1、列2、列3) values(...),(...),(...)

update 表名 set 列1=值1,列2=值2...where 条件

例:修改id为5的学生数据,姓名改为狄仁杰,年龄改为20

update student2 set age=20,name="狄仁杰" where id=5

delete from 表名 where 条件

例：删除id为6的学生数据

delete from student2 where id = 6

逻辑删除
1、设计表,给表添加一个字段isdelete,1代表删除,0代表没有删除
2、把所有的数据iadelete都改为0
3、删除某一条数据时,更新他的isdelete为1
4、当要查询数据时,只查询isdelete为0的数据

create table students(
studentNo varchar(100) primary key,
name varchar(100),
sex varchar(100),
hometown varchar(100),
age tinyint(4),
class varchar(100),
card varchar(100)
)

insert into students values 
('001','王昭君','女','北京','20','1班','3403122123402120'),
('002','诸葛亮','男','上海','18','2班','3403122123402120'),
('003','张飞','男','南京','24','3班','3403122123402120'),
('004','白起','男','安徽','22','4班','3403122123402120'),
('005','大乔','女','天津','19','3班','3403122123402120'),
('006','孙尚香','女','河北','18','1班','3403122123402120'),
('007','百里玄策','男','山西','20','2班','3403122123402120'),
('008','小乔','女','河南','15','3班','3403122123402120')

查询所有字段
select * from 表名;

查询指定字段
在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
select 列1,列2,,列3... from 表名；

表名、字段名
select students.name,students.age from students

可以通过as给表起别名
select s.name,s.age from students as s

例：查询学生姓名有哪几种？
select sex from students

使用where子句对表中的数据筛选,符号条件的数据会出现在结果集中
select 字段1、字段2、字段3... from 表名 where 条件；

例：
select * from students where id = 1;

例1：查询小乔的年龄
select age from students where name="小乔”

例2：查:20岁以下的学生
select * from students where age<20

例3：查询家乡不在北京的学生
select *from students where hoetown!="北京"

例1:查询年龄小于20的女同学
select * from students where age<20 and sex="女"

例2:查询女学生或1班的学生
select * from students where sex="女" or class="1班"

例3:查询非天津的学生
select * from students where not hometown="天津"

例1:查询姓孙的学生
select * from students where name like "孙%"

例2:查询姓孙且名字是一个字的学生
select * from students where name like "孙_"

例3:查询叫乔的学生
select * from students where name like "%孙"

例4:查询姓名含白的学生
select * from students where name like "%白%"

例1、查询家乡是北京或上海或广东的学生
select * from students where in("北京"、"上海"、"广东")

例2、查询年龄为18至20的学生
select * from students where age between 18 and 20

例1:查询没有填写身份证的学生
select * from students where card is null

例2:查询填写了身份证的学生

select * from students where is not null

select * from 表名
order by 列1 asc|desc,列2 asc|desc...

例1:查询所有学生信息,按年龄从小到大排序
select * from students order by age

例2:查询所有学生信息,按年龄从大到小排序,年龄相同时,再按学号从小到大排序
select * from students order by age desc,studentNo

例1:查询学生总数
select count(*) from students

例2:查询女生的最大年龄
select max(age) from students where sex = "女"

例3:查询1班的最小年龄
select min(age) from students where class = "1班"

sum(列)表示求此列的和
例4:查询北京学生的年龄和
select sum(age) from students where hometown="北京"

avg(列)表示求此列的平均值
例5:查询女生的平均年龄
select avg(age) from students where sex = "女"

select 列1,列2,聚合... from 表名group by 列1,2...

例1：查询各种性别的人数
select sex,count(*) from students group by sex

例2:查询各种年龄的人数
select age,count(*) from students group by age

例3:查询各个班各有多少男生女生

select class,sex,count(*) from students group by class,sex

select列1,列2,聚合...from表名 group by列1,列2,列3....having列1...聚合..

例1:查询男生总人数
方案一：
select count(*) from students where sex="男"

方案二：
select sex,count(*) from students group by sex having sex="男"

例2:查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
select class,min(age),max(age),avg(age) from students group by class having class!="1班"

select * from 表名 limit start（开始）,count

从start开始,获取count条数据
start索引从0开始

例1:查询前3行学生信息
select * from students limit 0,3

例2:查询第4到第6行学生信息
select * from students limit 3,3  

已知:每页显示m条数据,求:显示第n页的数据
select * from students limit (n-1)*m,m

distinct 去重，去掉重复的字段

语法：
select distinct 列1，列2 from 表1 where ....

例：用一条SQL语句查询出每门课都大于80分的学生姓名 
create table score(
id int primary key auto_increment,
name varchar(20),
subject varchar(20),
score int);

insert into score values
(null,'张三','语文',81),
(null,'张三','数学',75),
(null,'李四','语文',76),
(null,'李四','数学',90),
(null,'王五','语文',81),
(null,'王五','数学',100),
(null,'王五 ','英语',90);

答案：
select distinct name from score  where  name not in (select distinct name from score where score<=80)

drop table if exists courses;
create table courses(
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
)

insert into courses values
('1','数据库'),
('2','qtp'),
('3','linux'),
('4','系统测试'),
('5','单元测试'),
('6','测试过程')

drop table if exists scores;
create table scares (
id int(10) unsigned primary key auto_increment,
courseNo int (10),
studentNo varchar(10),
score tinyint(4).
)

insert into scores values
('1','1','001','90'),
('2','1','002','75'),
('3','2','002','93'),
('4','3','001','86'),
('5','3','003','76'),
('6','4','004','70'),
('7','5','005','86'),
('8','6','006','97'),

方式一
select * from 表1,表2 where 表1.列 = 表2.列

方式二（又称内连接）
select *from 表1 
inner join 表2 on 表1.列 = 表2.列

例1:查询学生信息及学生的成绩
select * from student as stu,scores as sc where stu.studentNo = sc.studentNo

select *from student as stu 
inner join scores as sc on stu.studentNo = sc.studentNo

例2:查询课程信息及课程的成绩
select *from courses as cs,scores as sc where cs.courseNo=sc.courseNo

select *from courses as cs 
inner join scores as sc on cs.courseNo = sc.courseNo

例3:查询学生生信息及学生课程对应的成绩
select *from students,courses,scores where students.studentNo = scores.courseNo and scores.courseNo = courses.courseNo

select * from students
inner join scores on students.studentNo = scores.studentNo
inner join courses on  scores.courseNo = courses.courseNo

例4:查询王昭君的成绩,要求显示姓名、课程号、成绩
select students.name,courses.courseNo,scores.score from students where students.studentNo = scores.studentNo and students.name = "王昭君"

select students.name,courses.courseNo,scores.score from students
inner join scores on studnets.studentNo = scores.studnetNo
inner join courses on  scores.courseNo = courses.courseNo
and students.name = "王昭君"

例5:查询王昭君的数据库成绩,要求显示姓名、课程名、成绩
select students.name,courses.name,scores.score from students,courses,scores where students.studentNo = scores.studentNo and scores.courseNo =courses.courseNo and students.name = "王昭君" and courses.name = "数据库"

select students.name,courses.name,scores.score from students,courses,scores

inner join scores on scores.courseNo = courses.courseNo inner join students on students.studentNo = scores.studentno where students.name = "王昭君" and courses.name = "数据库" 
例6:登询所有学生的数据库成绩,要求显示姓名、课程名、成绩 
select students.name,courses.name,scores.score from students,courses,scores

inner join scores on scores.courseNo = courses.courseNo 

inner join students on students.studentNo = scores.studentno where courses.name = "数据库" 
例7:查询男生中最高成绩,要求显示姓名、课程名、成绩 select students.name,courses.name,scores.score from students,courses,scores

inner join scores on scores.courseNo = courses.courseNo 

inner join students on students.studentNo = scores.studentno where sex = "男" order by score desc limit 1

select * from 表1
left join 表2 on 表1.列=表2.列

join前边的表称为左表
例1:查询所有学生的成绩,包括没有成绩的学生
select scores.score from students
left join scores on students.studengNo = scores.studentNo

例2:查询所有学生的成绩,包括没有成绩的学生,需要显示课程名
select scores.score,courses.name from students
left join scores on students.studengNo = scores.studentNo
left join courses on coursrs.courseNo = scores.coursrNo

select * from 表1
right join 表2 on 表1.列=表2.列

添加两门课程
insert into courses values
(0,'语文'),
(0,'数学')

例1:查询所有课程的成绩,包括没有成绩的课程
select * from scores
right join courses on scores.courseNo=courses.courseNo

例2:查询所有课程的成绩,包括没有成绩的课程,包括学生信息
select * from scores
right join courses on scores.courseNo=courses.courseNo
left join student on students.studetNo= scores.scoreNo

create table areas(
aid int primary key,
atitle varchar (28).
pid int
)
insert into areas values
('130000','河北省',NULL),
('130100','石家庄市',130000),
('130400','邯郸市',130000),
('130600','保定市',130000),
('130700','张家口市',130000),
('130800','承德市',130000),
('410000','河南省',NULL),
('410100','郑州市',410000),
('410300','洛阳市',410000),
('410500','安阳市',410000),
('410700','新乡市',410000),
('410800','焦作市',410000)

例1:查询河南省所有的城市

数据从一个表中取多次，必须取别名
select * from areas as sheng,areas as shi
where sheng.aid=shi.pid and sheng.atitle = '河南省'

添加区县数据
insert into areas values
('410101','中原区','410100'),
('410102','二七区','410100'),
('410103','金水区','410100')

例2:查询郑州市的所有区县
select * from areas as sheng,areas as shi 
where sheng.aid = shi.pid and sheng.atitle = '郑州市'

例3:查询河南省的所有区县
select * from areas as sheng,areas as shi,areas as qu
where sheng.aid = shi.pid and sheng.atitle = '河南省' and shi.aid = qu.pid

标量子查询
例1:查询班级学生的平均年龄
查询班级学生平均年龄
select avg(age) from students
查询大于平均年龄的学生
select * from students where age > 21.4167
select * from students where age > (select avg(age) from students);

例2:查询王昭君的成绩,要求显示成绩
select score from scores where  studentNo=(select studentNo from students where name = '王昭君')

列级子查询
例3:查询18岁的学生的成绩,要求显示成绩
学生表中查询18岁的学生的学号
select studentNo from students where age=18
成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006'.)
select * from scores where studentNo in (select studentNo from students where age=18)

行级子查询
例4:查询男生中年龄最大的学生信息
select * from students where sex='男'
select * from students where (sex,age)=('男',30)
select * from students where (sex,age) =(select sex,age from students where sex='男' order by age desc limit 1

表级子查询
例5:查询数据库和系统测试的课程成绩
select * from scores
inner join 
(select * from courses where name in ('数据库'，‘系统测试’)) as c on scores.courseNo= c.courseNo

select * from students where age in (select age from students where age between 18 and 20)