Mysql常用语法及练习-CSDN博客

一、基本语法

1、创建数据库

create DATABASE 数据库名 CHARSET=utf8;

2、使用数据库

use 数据库名;

数据库查看、使用、创建、删除

show databases; 查看所有的数据库

select database()；查看当前所在数据库

drop database 数据库名; 删除数据库

3、建表语句

create TABLE 表名(
sid int PRIMARY KEY,
sname varchar(12) NOT NULL,
score int);
复制代码

约束语句：

①设置默认值：DEFAULT '默认值' ②设置非空：NOT NULL ③设置唯一：UNIQUE(字段名) ④设置主键：PRIMARY KEY(字段名) ⑤设置外键：FOREIGN KEY(字段名) REFERENCES 表名(字段名) ⑥设置CHECK约束：CHECK(字段名 BETWEEN xx AND xx)

4、修改数据表

①添加字段 alter table 表名 add 列名类型;

②修改字段(主要修改列的类型及约束，不重新修改列名)

alter table 表名 modify 列名类型及约束;

③修改字段(重命名)

alter table 表名 change 原名新名类型及约束;

④修改表(删除字段)

alter table 表名 drop 列名;

5、删除表

drop table 数据表;

6、重命名表

rename table 旧表名 to 新表名;

二、增删改、聚合

1、插入数据

①全部数据

insert into 表名 values(全部列的值);

②部分数据

insert into 表名(字段1,字段2...) values (值1,值2...)

③多条数据

insert into 表名 values (全部列的值),(全部列的值),(全部列的值);

2、删除数据

注意:在修改或者删除数据的时候一定腰指定条件，斗则可能造成所有数据被污染或者清空

delete from 表名 where 条件 ``

例如：delete from student where id=1;

3、修改数据

update 表名 set 字段名=新值 where 条件

例如：update student set sname='李四' where id=1;

4、清空数据(将全表清空掉吗并且自增主键从1开始)

truncate 表名;

5、聚合函数

where子句中不能使用聚合函数，因为聚合函数对结果集进行操作，而where子句运行时还没有筛选出结果集，所以此时使用聚合函数会报错；与where相比，having是用来筛选分组的，跟在group by之后，所以having子句运行时结果集已经被筛选出，此时可以使用聚合函数进行二次筛选。

①返回某列平均值(AVG(字段名)) 例：select AVG(score) from test;

②返回某列最大值(MAX(字段名)) 例： select max(score) from test;

③返回某列最小值(MIN(字段名))

例：select min(score) from test;

④返回某列之和(sum(字段名))

例：select sum(score) from test;

⑤返回某列个数(count(字段名)) select count(id) from test;

Group by使用：在原始表中新加一个sex字段(一般尽量避免修改)

alter table test add sex char(2) default '男'

Group by 与聚合函数使用。

例：将各个性别人数统计出来：select sex, count(sex) from test group by sex;

Group by 与having与聚合函数使用。

having可以进行二次筛选

例：将数量大于1的性别筛选出来 select sex，count(sex) from test group by sex having count(sex)>1;

三、查询常见语句

1、查询数据

select * from 表名；

select * from 表名 where 条件;

select 字段 as 别名 from 表名 where 条件;

表结构：

学生表 student(s#,sname,sage,ssex)

课程表 course（c#,cname,T#）

成绩表 sc(s#,c#,score)

教师表Teacher(T#,tname)

1.查询001课程比002课程成绩高的所有学生的学号：select sc1.s# from sc sc1 join sc sc2 on sc1.s# = sc2.s# where sc1.c# = '001' and sc2.c# = '002' and sc1.score > sc2.score

2.查询平均成绩大于60分的同学的学号和平均成绩：select s#, avg(score) from sc group by s# having avg(score)>60;

3.查询所有同学的学号，姓名，选课数，总成绩：select student.s#,student.name ,count(sc.c#) ,sum(sc.score) from student left join sc on student.s# = sc.s#

注：内连接要计算笛卡尔积，这里使用左外连接效率更高

4.查询姓李的老师个数： select count(T#) from Teacher where tname like "李%"

注：模糊查询, %代表任意字符，_代表任一单个字符，[……]代表是括号中字符中的单个字符, [^……]代表不是括号中字符中的单个字符

5.查询没有学过叶萍老师课的同学学号，姓名： select studet.s# student.sname from student where s# not in (select distinct(sc.s#) from sc, course, teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tname = "叶萍")；

三表联合 + 子查询

6.查询学过001和002课程的同学的姓名学号： select student.s3 student .sname from student join course on student.s# = course.s# where(course.c# = '001' union course.c# = '002');

7.查询学过叶萍老师课的同学的学号，姓名：select student.s# student.sname from student where s# in(select distinct(sc.s#) from sc course teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tnmae = "叶萍")

注：distinct为去重函数

8.查询002成绩比001低的同学的学号，姓名： select student.s# student .name from student where student.s# in(select sc1.s# from sc sc1 join sc sc2 on sc1.s# = sc2.s# where s1.c# = 001 and s2.c# = 002 and sc1.score> sc2.score);

9.查询所有课程成绩小于60分的同学的学号，姓名：select student.s# student.sname from student join sc on student.s# = sc.s# where sc.score > 60;

10.查询没有学全所有课的同学的学号和姓名：select student.s# student.sname from student,sc where student.s# = sc.s# group by student.s#,student.sname having count(c#) < (select count(c#)from course);

11.查询至少有一门课程与学号1001的同学所学相同的同学的学号和姓名： select student.s# ,student.sname from student join sc where student.s# = sc.s# and c# in select c# from sc where s# = '1001'

12.查询至少学过学号为001同学所有一门课的其他同学的学号和姓名： select distinct student.s#,student,sname from SC join student on SC.s# = student.s# where c# in(select c# from sc where s#='001');

后续再更新~