- MySQL 语句

最新推荐文章于 2024-02-29 14:41:56 发布

盗情

最新推荐文章于 2024-02-29 14:41:56 发布

阅读量401

点赞数 1

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/qq_37546891/article/details/94554974

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在这里插入图片描述

语句规范

关键字与函数名全部大写；
数据库名称、表名称、字段名称全部小写；
存储过程第一个单词首字母小写，后面单词首字母大写；
SQL语句必须以分号结尾；

三大范式

范式设计的越详细，对于某些实际操作可能会更好，但是不一定都是好处，范式的设计最终以实际开发时的需求来决定；

第一范式 - 1NF

数据表中的所有字段都是不可分割的原子值；

例如：创建表时，将地址用一个字段address表示，就不满足第一范式，因为address还可以拆分成'国家','省份','城市','详细地址'；但是这不是的，如果查询中不会按具体的国家、省份等详细字段查询，可以不拆分address，也就是不用非得满足第一范式；

第二范式 - 2NF

必须是满足第一范式的前提下，第二范式要求，除主键的每一列都必须完全依赖于主键，也就是说不能有任何一列与主键没有关系；

例如：
订单表中有字段：订单编号、产品编号、订单名称、产品名称；订单编号和产品编号为联合主键；这时候，订单名称只依赖于订单编号，与产品编号没有关系，产品名称只依赖于产品编号而与订单编号没有关系；这时出现了除主键外的其他列：订单名称、产品名称，只依赖于主键的部分字段，不完全依赖主键，这就违反了第二范式；

解决办法：拆表
将订单编号、订单名称创建成一个表，产品编号、产品名称创建成一个表，然后再创建一张维护多对多关联关系的中间表，中间表只有两个字段：订单表和产品表的主键；

第三范式 - 3NF

在第二范式的基础上，任何非主属性不依赖于其他非主属性（在第二范式的基础上消除传递依赖），除主键列的其他列之间不能有传递依赖关系；

部门表中，有部门编号、部门名称、部门简介三个字段；那么在员工信息表中，有部门编号字段，就不能再有部门名称字段；否则，员工信息表中的两个非主属性列：部门编号、部门名称之间就有依赖关系了；

建表约束

约束保证数据的完整性和一致性；

约束分为表级约束、列级约束：根据约束所针对的字段的数目的多少决定的；

约束针对某一个字段约束称为列级约束；
约束针对两个或以上字段进行使用的话，则称为表级约束；

1、非空约束 - NOT NULL

2、主键约束 - PRIMARY KEY

主键：是数据库表中能唯一标识一条记录的一个或多个属性的集合；

每张数据表只能有一个主键；
主键不重复：主键保证记录的唯一性；
主键不为空：自动为 NOT NULL；
主键创建时自动添加索引；
主键不一定和AUTO_INCREMENT一起使用，可以单独使用，但是AUTO_INCREMENT必须和主键一起使用；
设置某字段为主键，并且设置为自增时，插入记录时INSERT name 后面，VALUE前面，的小括号里面的字段名不能省略；
主键单独使用时，插入记录时给主键在赋值时必须保证主键的值唯一；
将主键设置为自增时，字段类型可以使int、浮点型；其中浮点型小数位必须为0；

设置主键方式：

1、创建表时添加主键：
直接在字段后面添加： id int primary key auto_increment,
以联合主键的形式：primary key(id, name)
2、alter table tableName add primary key(id);
3、alter table tableName modify id int primary key;
4、删除主键约束：alter table tableName drop primary key(id);

3、自增约束 - auto_increment

4、唯一约束 - UNIQUE

唯一约束：表中除了主键字段之外的其他字段设置唯一约束可以保证字段值唯一；

唯一约束的字段可以为空值；
每张数据表中可以有多个唯一约束；

设置约束方式和设置主键方式一样；

5、默认约束 - DEFAULT

插入记录的时候，如果没有给字段传值(允许为空)，就会使用默认值；可以和enum结合使用；

6、外键约束 - FOREING KEY

只有InnoDB引擎支持外键

外键：表的外键是另一张表的主键；外键可以有多个，可以为空；
作用：可以将一张表与其他表关联起来，保证数据的一致性、完整性；

外键约束要求：

父表和子表必须使用相同的存储引擎，必须是InnoDB，而且禁止使用临时表；
外键列和参照列必须具有相似的数据类型，其中数字的长度、或是否有符号位必须相同，而字符长度可以不同；若不相同，会报错1005；
外键列和参照列必须创建索引，若参照列不创建索引的话，MySQL会自动创建索引，外键列不创建索引不会自动创建索引；
主表中主键列没有的数据值，在从表中不可以使用；主表中的记录被从表使用时，不能被删除；

简单命令

mysqld -install：安装mysqld；
mysqld --initialize：初始化mysqld；
net start/stop mysql：启动/停止mysql服务；
set password for 'root'@'localhost' =password('123456');：修改root密码；

~> mysql -uroot -p -P -h：登录mysql；分别指定用户名、密码、IP地址、端口号；
~> quit； exit； \q;：退出mysql ；

select version();：显示当前服务器版本；
select database();：显示用户当前打开的数据库；
select now();：显示当前日期；
select user();：显示当前用户；
select @@autocommit;：查看事务是否开启，默认是1，开启；
set autocommit=false;：取消事务，取消自动提交；

show warnings;：查看警告信息；

show databases;：查看当前服务器下的数据库列表（MySQL创建之后默认有四个数据库）；
show create database name; ：显示数据库创建的时候所使用的指令是多少；

show tables [db_name]：查看数据表（tables后面加上mysql，显示所有数据表；加上库名显示库里数据表；默认显示当前数据库里面的表）；
show create table 表名;：查看创建的表；
describe tableName：查看创建好的数据表的结构；

show full fields from tableName;：查看表的字段的所有信息；
show columns from tbl_name：查看数据表的结构；

show index from name \G：显示表的索引，\G表示以表格的形式显示；

        Table: aaa       
   Non_unique: 0        // 0表示索引列表中不能包括重复值；1表示索引值可重复；
     Key_name: PRIMARY  // 索引的名称
 Seq_in_index: 1	    // 索引中的列序号，从1开始
  Column_name: id 	    // 索引列
    Collation: A        // 列以什么方式存储在索引中；A表升序，NULL表无分类
  Cardinality: 0        // 索引中唯一值的数目的估计值；
     Sub_part: NULL     // 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
       Packed: NULL     // 指示关键字如何被压缩。如果没有被压缩，则为NULL
         Null:          // 
   Index_type: BTREE    // 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
      Comment:          //
Index_comment:          //

explain关键字显示MySQL如何使用索引来处理select语句，以及连接表，可以帮助选择更好的索引和写出更优化的查询语句；
explain select surname,first_name form a,b where a.id=b.id \G;

create database dbName;：创建数据库(编码方式默认latin);
create database if not exists dbName character set gbk;：创建数据库的时候添加参数，设置编码方式为gbk；
alter database dbName character set utf8;：修改数据库编码方式；
alter table tableName character utf8;：修改数据表编码方式；
alter table tableName change sname sname varchar(20) character set utf-8;：修改字段编码方式；
show variables like '%char%';：查看当前字符集编码；

Mac下修改MySQL默认字符编码：
sudo vim /etc/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

drop database dbName;：删除数据库；
use dbName：打开(使用)数据库，（数据库得存在）；

创建数据表 - create table

create table [if not exists]  tableName(
	列名1  数据类型  列的约束,
	列名1  数据类型  列的约束
);
--------------------------------------------------------------------------------
// 主表
create table person(  
    id int primary key
); 
// 从表
create table  user(   
    id int primary key auto_increment,  // 在某一字段直接添加主键约束、自增约束
    name varchar unique                 // 设置唯一约束
    sex ENUM('1','2','3') DEFAULT '3';  // 设置默认约束
    person_id int,                      // 设=定义外键列，外键的类型要和主表的类型一致；
    foreign key(person_od) references person(id)  // 设置外键
);

create table  user(
    id int ,
    name varchar,
    primary key(id, name),  // 联合主键
    unique(name)            // 唯一约束
);

插入数据 - insert

insert into tableName
(字段名1, 字段名2, 字段名3, ......)   // values()里面填写所有字段值时，这行可以不写；
values('字段1的值','字段2的值','字段3的值',...);

省略所有字段名，需要给所有字段赋值，否则报错；

删除数据 - delete

delete from tableName where ......

修改数据 - alter

update  tableName  set  name='zxj'  where ...

查询数据 - select

select  */指定字段名  from  tableName   where  ......

在这里插入图片描述

1、查询student表的所有记录；

select * from student;

2、查询student表中的所有记录的sname、ssex、class列；

select sname, ssex, class from student;

3、查询教师所有的单位即不重复的depart列；
distinct：去重

select distinct depart from teacher;

4、查询score表中成绩在60到80之间的所有记录；
between … and … ：获取区间
and：表示并且

select * from score where degree between 68 and 80;
select * from score where degree  > 68 and degree < 80;

5、查询score表中成绩为85、86或88的记录；
in(… , …)：或关系查询

select * from score where degree in(85,86,80);

6、查询student表中“95031”班，或性别为“女”的同学记录；
or：表示或者

select * from student where class='95031' or ssex='女';

7、以class降序查询student表的所有记录；
order by XX asc/desc：升序/降序

select * from student order by class desc;

8、以cno升序、degree降序查询score表的所有记录；

select * from score order by cno asc, degree desc;

9、查询“95031”班学生人数；
count ：统计

select count(*) from student where class='95031';

10、查询score表中最高分的学生学号和课程号；（子查询或排序）
子查询

select sno, cno from score where degree=(select max(degree) from score);

limit 起始位置取数个数：取范围；

select sno, cno from score order by degree desc  limit 0,1;
//（从第0个开始取，取一个）(有bug)

11、查询每门课程的平均成绩；
avg(X)：平均值
group by X：分组

select cno, avg(degree) from score group by cno;

12、查询score表中至少有2名学生选修的并以3开头的课程的平均成绩；
having：过滤
like：模糊查询

select cno, avg(degree) from score
group by cno
having count(cno)>=2
and cno like '3%';

13、查询分数大于70，小于90的sno列；

select sno, degree from score where degree between 70 and 90;
select sno, degree from score where degree > 70 and degree < 90;

14、查询所有学生的sname、cno、degree列；
连表查询

select sname, cno, degree from student, score where student.sno = score.sno;

15、查询所有学生的sno、cname、degree列；

select sno, cname, degree from course, score where course.cno = score.cno;

16、查询所有学生的sname、cname、degree列；

select sname, cname, degree from student, course, score
where score.sno = student.sno 
and score.cno = course.cno;

17、查询“95031”班的学生的每门课的平均分；

95031班的学生学号：select sno from student where sno='95031';
查95031班学生分数：select * from score where sno in (..)
查课程cno、平均分avg(degree)

select cno, avg(degree) from score 
where sno in (select sno from student where class='95031')
group by cno;

18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录；

“109”号同学“3-105”成绩：select degree from score where sno='109' and cno='3-105'
选修“3-105”课程：select * from score where cno='3-105'

select * from score 
where cno='3-105'
and degree > (select degree from score where sno='109' and cno='3-105');

19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录；

学号为“109”、课程号为“3-105”的成绩：select degree from score where sno='109' and cno='3-105'

select * from score 
where degree > (select degree from score where sno='109' and cno='3-105');

20、查询和学号为108、101的同学同年出生的所有学生的sno、sname、sbirthday列；
year：获取日期的年

学号为108、101的同学的出生日期：select year(sbirthday) from student where sno in(108,101);

select sno, sname, sbirthday from student 
where year(sbirthday) in (select year(sbirthday) from student where sno in(108,101));

21、查询“张旭”教师任课的学生成绩；

“张旭”教师号：select tno from teacher where tname='张旭';
“张旭”教的课程号：select cno from course where tno=(..)

select * from score 
where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));

22、查询选修某课程的同学人数多于5人的教师姓名；

课程人数大于5的cno：select cno from score group by cno having count(*)>5;
根据cno查询tno：select tno from course where cno=(..);

select tname from teacher 
where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));

23、查询95033班和95031班全体学生记录；

select * from student where class in(95031, 95033);

24、查询存在有85分以上成绩的课程cno；

select cno, degree from score where degree > 85;

25、查询“计算机系”教师所教课程的成绩表；

“计算机系”教师号：select tno from teacher where depart="计算机系";
根据tno查cno：select cno from course where tno in(..);

select * from score where cno in(select cno from course where tno in(select tno from teacher where depart="计算机系"));

26、查询“计算机系”与“电子工程系”不同职称的教师的tname, prof；
union求并集

select * from teacher where depart="计算机系" 
and prof not in(select prof from teacher where depart="电子工程系")
union
select * from teacher where depart="电子工程系" 
and prof not in(select prof from teacher where depart="计算机系");

27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno,sno,degree，并按照Degree从高到低次序排序；
>any：其中至少一个高于

选修编号为“3-245”的同学成绩：select degree from score where cno='3-245';

select cno, sno, degree from score where cno="3-105"
and degree>any(select degree from score where cno='3-245')
order by degree desc;

28、查询选修编号为“3-105”课程，且成绩高于选修编号为“3-245”的同学的cno,sno,degree；
>all：高于

select cno, sno, degree from score where cno="3-105"
and degree>all(select degree from score where cno='3-245');

29、查询所有教师和同学的name,sex,birthday；
union：求并集
as：别名，查询出来的列的显示的名称

select tname as name, tsex as sex, tbirthday as birthday from teacher 
union
select sname, ssex, sbirthday from student;

30、查询所有“女”教师和“女”同学的name，sex，birthday；

select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex='女'
union
select sname, ssex, sbirthday from student where ssex='女';

31、查询成绩比课程平均成绩低的同学的成绩表；
将一个表用两次，as两个别名，一个用来取平均数，量表使用cno相等进行连接

select * from score as a where degree < (select avg(degree) from score as b where a.cno = b.cno);

在这里插入图片描述

32、查询所有任课教师的tname，depart；(任课教师要在course表中安排了课程)

select tname, depart from teacher where tno in(select tno from course);

33、查询至少有2名男生的班级号；

select class from student where ssex='男' group by class having count(*) > 1;

34、查询student表中不姓王的同学记录；
not like：模糊查询

select * from student where sname not like '王%';

35、查询student表中每个学生的姓名和年龄；（年龄=当前年份-出生日期）
year(now())：获取当前年份
year(sbirthday)：获取出生日期的年份

select sname, year(now())-year(sbirthday) as age from student;

36、查询student表中最大和最小的sbirthday日期值；
max(),min()：求最大、最小值

select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;

37、以班号和年龄从大到小的顺序查询student表中的全部记录；

select * from student order by class desc,sbirthday asc;

38、查询“男”教师及其所上的课程；

select * from course where tno in(select tno from teacher where tsex='男');

39、查询做高分学生的sno，cno，degree列；

select sno, cno, degree from score where degree=(select max(degree) from score);

40、查询和“李军”同性别的所有学生sname；

select sname from student where ssex = (select ssex from student where sname='李军');

41、查询和“李军”同性别、同班的所有学生sname；

select sname from student 
where ssex = (select ssex from student where sname='李军') 
and class = (select class from student where sname='李军');

45、查询所有选修“计算机导论”课程的“男”同学的成绩表；

select * from score 
where cno = (select cno from course where cname='计算机导论')
and sno in (select sno from student where ssex='男');

46、建立一个grade表，查询所有同学的sno，cno，grade列；

select sno, cno, grade from score, grade where degree between low and upp;

SQL 四种连接查询 - join

连接查询好处：表之间可以不用创建外键，也能通过字段的值相等来连表查询；

内连接：inner join 或 join
左外连接：left join 或 left outer join
右外连接：right join 或 right outer join
完全外链接：full join 或 full outer join （MySQL不支持）

在这里插入图片描述

两张表没有创建外键；

1、inner join 查询： inner join … on …

内连接查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录；

select * from person join card on person.cardId = card.id;

在这里插入图片描述

2、left join 查询：

左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来，如果没有相等的，就会补NULL；

select * from person left join card on person.cardId = card.id;

在这里插入图片描述

3、right join 查询：

右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来，如果没有相等的，就会补NULL；

select * from person right join card on person.cardId = card.id;

在这里插入图片描述

4、full join 全外连接查询：
MySQL不支持full join，可以使用union实现全连接效果；

两个表中的数据都查询出来，没有的值补NULL；

// select * from person full join card on person.cardId = card.id;

select * from person left join card on person.cardId = card.id
union 
select * from person right join card on person.cardId = card.id;

在这里插入图片描述

查询练习 - test

student：
sno：  学号
sname：姓名
ssex： 性别
sbirthday：出生日期
class：班级

teacher：
tno：编号
tname：姓名
tsex：性别
tbirthday：出生日期
prof：职称
depart：所在部门

course：
cno：课程号
cname：课程名
tno：教师编号

score：
sno：学号
cno：课程号
degree：成绩

create table student(
	sno       varchar(20) primary key comment '学号',
	sname     varchar(20) not null    comment '姓名',
	ssex      varchar(20) not null    comment '性别',
	sbirthday datetime                comment '出生日期',
	class     varchar(20)             comment '班级'
);
create table teacher(
	tno       varchar(20) primary key comment '教师编号',
	tname     varchar(20) not null    comment '教师姓名',
	tsex      varchar(20) not null    comment '教师编号',
	tbirthday datetime                comment '出生日期',
	prof      varchar(20) not null    comment '职称',
	depart    varchar(20) not null    comment '所在部门'
);
create table course(
	cno   varchar(20) primary key comment '课程号',
	cname varchar(20) not null    comment '课程名称',
	tno   varchar(20) not null    comment '教师编号',
	foreign key(tno) references teacher(tno)
);
create table score(
	sno    varchar(20) not null comment '学号',
	cno    varchar(20) not null comment '课程号',
	degree decimal              comment '成绩',
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno),
	primary key(sno,cno)
);
create table grade(
	low int(3),
	upp int(3),
	grade char(1)
);

insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆君','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-02-20','95033');
insert into student values('108','张全蛋','男','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');
insert into student values('110','张飞','男','1974-06-03','95038');

insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1971-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

insert into score values('103','3-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');
insert into score values('101','3-105','90');
insert into score values('102','3-105','91');
insert into score values('104','3-105','89');

insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

连接练习 - testJoin

create table person(
    id int,
    name varchar(20),
    cardId int
);
create table card(
    id int,
    name varchar(20)
);

insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'邮政卡');

insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

盗情

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
- MySQL 语句

文章目录语句规范三大范式第一范式 - 1NF第二范式 - 2NF第三范式 - 3NF建表约束1、非空约束 - NOT NULL2、主键约束 - PRIMARY KEY3、自增约束 - auto_increment4、唯一约束 - UNIQUE5、默认约束 - DEFAULT6、外键约束 - FOREING KEY简单命令创建数据表 - create table插入数据 - insert删除数据 ...
复制链接

扫一扫