SQL语法基础汇总

---

三年前的存稿

默认端口号 3306
超级用户名 root
登录 mysql -uroot -p / mysql -uroot -proot
退出 exit / quit
服务器版本 SELECT VERSION();
当前日期 SELECT NOW();
当前用户 SELECT USER();
备份 mysqldump -uroot -p 数据库名称 > 保存的路径
还原 create database1->use database1 -> source 文件路径
启动 net start mysql
关闭 net stop mysql
注释 #
结尾 ;
创建数据库 CREATE DATABASE
修改数据库 ALTER DATABASE
删除数据库 DROP DATABASE
关键字与函数名称需全部大写；数据库、表、字段名均小写。为了方便理解记忆，笔记统一采用小写。

---

库操作

//查询所有数据库的名称
show databases;
//查询某个数据库的字符集
show create database sql1;
//查询当前正在使用数据库
select database();


//创建数据库
create database sql2;
//当创建的数据库存在时,上述语句报错,故需要进行判断
create database if not exists sql3;
//创建一个数据库并设置字符集gbk
create database sql4  character set gbk;
//以上操作可合并
create database if not exists sql5 character set gbk;


//修改数据库的字符集
alter database sql6 character set utf8;


//删除数据库(删除一个不存在的会报错)
drop database sql5;
//删除时不存在判断
drop database if exists sql5;


//使用数据库
use sql1;

表操作

//查询某个数据库中所有表名称
use sql1;
show tables;

//查询表结构
desc tab1;


//创建表(最后一列不加逗号)
create table stu1(
   id int,
   name varchar(32),
   age int,
   score double(4,1),
   brirthday data,
   insert_time timestamp
);
//常用type
int
double(4,2) //表示最大4位,小数保留2位
data
varchar(8) //表示最长长度为8个字符,monkey有6个字符,猴子有2个字符

//复制创建一样的表
create table stu2 like stu1;


//删除表
drop table tab1;
drop table if exists tab1;


//修改表名
alter table tab1 rename to tab2;
//修改表字符集
alter table tab1 character set utf8;
//添加一列
alter table tab1 add listname type;
alter table stu1 add gender varchar(10); 
//修改列名
alter table tab1 change 列名 新列名 新数据类型;
alter table tab1 change gender sex varchar(20);
alter table tab1 modify sex varchar(10);
//删除列
alter table 表名 drop 列名;

---

增删改表中数据

//添加数据,除数字类型以外,其他类型需要单或双引号引起来
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
insert into 表名 values(值1,值2,...值n);


//删除数据
delete from 表名; //删除表中全部数据!!!(not safe)
truncate table 表名; //删除表,然后创建一个一样的空表(效率高)


delete from 表名 [where 条件]
delete from stu where id=1;


//修改数据
update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件];
//不加条件导致表中全部记录被改变

查询表中数据

基础查询
//查询指定数据
select 字段1,字段2,...from 表名;
//查询所有字段
select * from 表名;
//☆distinct去除重复 //☆或者使用group by去重
select distinct 字段1,.. from 表名;
//计算列,null参与的计算进行判断
select 字段1,字段2,字段1 + ifnull(字段2,0) from 表名;
//列计算后列名的别名
select 字段1,字段2,字段1 + ifnull(字段2,0) as 别名 from 表名;


条件查询
//
select * from 表名 where 条件;
select * from 表名 where age > 10;
select * from 表名 where age = 10;
select * from 表名 where age < 10;
select * from 表名 where age != 10;
select * from 表名 where age <> 10;//同上
select * from 表名 where age >= 20 and age <= 30;
select * from 表名 where age between 20 and 30;//同上
select * from 表名 where age = 19 or age = 20 or age = 21;
select * from 表名 where age in (19,20,21);//同上
//null不能用=或者!=判断
select * from 表名 where age is null;
select * from 表名 where age is not null;


模糊查询
//查询姓'王'的有哪些人
select * from 表名 where name like '王%';
//查询第二个字是 云 的人
select * from 表名 where name like '_云%';
//查询名字是三个字的人
select * from student where name like '___';//这里3个_
//姓名中包含 云 的人
select * from 表名 where name like '%云%';


排序查询
//学生表stu中math成绩
select * from stu order by math;//不指定方式默认为升序方式
select * from stu order by math asc;//asc升序
select * from stu order by math desc;//desc降序
//在数学成绩基础上排英语成绩
select * from stu order by math asc , english asc;
//升序排序asc可以省略,降序排列不能省略
//order by 后对所有排序字段有效,升序和降序要给每个字段单独设定
select * from stu order by gpa desc,age desc;


聚合函数:纵向一列
//聚合排除null
//计算个数
select count(主键) from stu;
select count(name) from stu;
select count(ifnull(english,0)) from stu;//如果english为null,记为0,这样不会因为null而被排除个数在外

//计算最大最小
select max(math) from student;
select min(math) from student;
//计算和
select sum(math) from student;//排除null
//计算平均
select avg(math) from student;


分组查询
//按性别分组,分别查询男女的数学平均分,人数
select sex,avg(math),count(id) from stu group by sex;
//使用having与where的区别
having:出现having的原因:where关键字无法与聚合函数一起使用,having关键字放在group by关键字后面,针对分组后的数据进行筛选;where的操作对象是一条记录的条件关系;但涉及到多条记录时,比如是通过一组一组的对象,再过滤平均问问小于5的小组;即"having 可以从一组组的记录中过滤掉不符合要求的其他组"
//场景:查询出发帖和回帖平均活跃度满足条件的学校,并显示其平均活跃度
select university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile group by university
having avg(question_cnt)<5 or avg(answer_cnt)<20
//场景:计算每个大学的平均发帖活跃度,并按平均活跃度进行升序排序
select university, avg(question_cnt) as avg_question_cnt  from user_profile group by university order by avg_question_cnt


//指定位置取多条数据
limit n # 从第0+1条开始,取n条数据,是limit 0,n的缩写
limit m,n # 从第m+1条开始,取n条数据
limit n offset m # 从m+1开始,取n条数据
//例如:从表中读取前两条信息
select * from u_table limit 2;
select * from u_table limit 0,2;
select * from u_table limit 2 offset 0;

分页查询
//每页显示三条内容,转到第一页
select * from stu limit 0,3;//第一页(3×0)
select * from stu limit 3,3;//第二页(3×1)
//通项:开始索引 = (当前页码 - 1)*每页显示条数

---

约束

//主键约束(非空且唯一,一张表只能有一个主键,+ primary key)
1.创建表时主键约束
create table stu (
    id int primary key,
    name varchar(20)
);

2.创建完才添加主键
alter table stu modify id int primary key;

3.删除主键
alter table stu drop primary key;

4.自动增长:数值类型在添加数据时自动增长
create table stu (
    id int primary key auto_increment,
    name varchar(20)
);
insert into stu values(null,'b')//这里主键值可以用null因为有自动增长存在

5.添加自动增长
alter table stu modify id int auto_increment;
6.删除自动增长
alter table stu modify id int;


//非空约束(+ not null)
1.创建表时约束非空
create table stu(
    id int,
    name varchar(20) not null
);

2.创建表时没有约束,后面要约束
alter table stu modify name varchar(20) not null;

3.删除name的非空约束
alter table stu modify name varchar(20);


//唯一约束(+ unique)
1.创建表时唯一约束(null可以同时存在)
create table stu(
    in int,
    phone_number varchar(20) unique
);

2.创建表后添加唯一约束
alter table stu modify phone_number varchar(20) unique;

3.删除唯一约束(注:跟非空约束删除不一样)
alter table stu drop index phone_number;


//外键约束
create table stu(
   id int primary key, #主键
   name varchar(20) not null, #非空
   gender char(1) check(gender='男' or gender='女'), #检查
   seat int unique, #唯一
   age int default 18, #默认
   classId int,
   constraint class_id foreign key(classid) references classinfo(id) #外键
   #外键简单写法
   #foreign key (classid) references classinfo(id)
);
create table classinfo(
   id int primary,
   infor varchar(20)
);

多表关系

create table tab_category(
    cid int primary key auto_increment, #主键自动增长
    cname varchar(100) not null unique #非空且唯一
);
create table tab_route(
    rid int primary key auto_increment, #主键自动增长
    rname varchar(100) not null unique, #非空且唯一
    price double,
    rdate date,
    cid int, #外键
    foreign key (cid) references tab_category(cid)
);
create table tab_user(
    uid int primary key auto_increment,
    username varchar(100) unique not null,
    password varchar(30) not null,
    name varchar(100),
    birthday date,
    sex char(1) default '男', #默认是男
    telephone varchar(11),
    email varchar(100)
);
create table tab_favorite(
    rid int,
    uid int,
    date datetime,
    primary key(rid,uid), #复合键,同一用户不能收藏同一路线两次
    foreign key (rid) references tab_route(rid),
    foreign key (uid) references tab_user(uid)
);

数据库设计的范式

---

多表查询操作

select * from database1,database2;
//结果为笛卡尔积


//子查询

//场景:查询所有浙江大学的用户设备信息
# 子查询
select device_id,question_id,result 
from question_practice_detail 
where device_id=(
    select device_id 
    from user_profile
    where university="浙江大学"
)
# 连接查询
select question_practice_detail.device_id,question_id,result
from question_practice_detail,user_profile
where question_practice_detail.device_id=user_profile.device_id and user_profile.university='浙江大学';

笛卡尔积：两个集合A，B，取两个集合的所有组成结果

//内连接查询
select id_list from tab1 [inner] join tab2 on 条件;
//外链接查询

//子查询

1、男同学 列出：学号、姓名、系
ΠSno,Sname,Sdept(σSsex='男'(Student))

2、男同学 列出：学号、课程号、成绩
ΠSno,Cno,Grade(σSsex='男'(Student∞SC))
ΠSno,Cno,Grade(σSsex='男'(Student)∞SC)

3、CS系+男同学 列出：学号、姓名、课程号
ΠSno,Sname,Cno(σSsex='男'∧Sdept='CS'(Student∞SC))
ΠSno,Sname,Cno(σSsex='男'∧Sdept='CS'(Student)∞SC)

4、CS系+数据库名称 列出：学号、成绩
ΠSno,Grade(σSdept='CS'∧Cname='数据库'(Student∞SC∞Course))
ΠSno,Grade(σSdept='CS'(Student)∞SC∞σCname='数据库'(Course))

5、男同学 列出：课程号
ΠCno(σSsex='男'(Student∞SC))
ΠCno(σSsex='男'(Student)∞SC)
ΠSno,Cno(SC)÷ΠSno(σSsex='男'(Student))

6、选择课程号C01 列出:学号、姓名
ΠSno,Sname(Student∞σCno='C01'(SC))
ΠSno,Cno(SC)÷ΠCno(σCno='C01'(SC))∞ΠSname(Student)

7、没有选择课程号C01 列出：学号、姓名
ΠSno,Cno(SC)÷ΠCno(σCno<>'C01'(SC))∞ΠSname(Student)
ΠSno,Sname(Student)-ΠSno,Sname(Student∞σCno='C01'(SC))

1.供应工程j1零件的sno
Πsno(σjno='j1'(SPJ))

2.供应工程j1零件p1的sno
Πsno(σjno='j1'∧pno='p1'(SPJ))

3.供应工程j1为红色的sno
Πsno(σjno='j1'(SPJ)∞σcolor='红'(P))

4.没有使用天津供应商生成的红色零件的jno
Πjno(SPJ)-Πjon(σcity='天津'(S)∞SPJ∞σcolor='红'(P))

5.至少用了供应商s1供应的全部零件的jno
Πjno,pno(SPJ)÷Πpno(σsno='s1'(SPJ))