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;//指定位置取多条数据
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;
//结果为笛卡尔积

笛卡尔积：两个集合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))