SQL基础学习总结

岛雨QA
293 阅读15分钟

MySQL概述

安装

Mac上的安装可参考：www.cnblogs.com/moyand/p/90…

图形化软件可以使用：Sequel Pro

启动及退出

启动：mysql -u root -p [-h(IP地址)]

退出：exit / quite

概述

结构化查询语言SQL，分类如下：

  • DDL数据定义语言，用来定义数据库对象库、表、列等
    • 如create、drop、alter...等
  • DCL数据控制语言，用来定义访问权限和安全等级
    • 如grant、if...等
  • DML数据操纵语言，用来定义数据库记录（数据）
    • 如insert、update、delete...等
  • DQL数据查询语言，用来查询记录（数据）
    • 如select...等

SQL语句

操作数据库

创建数据库create

create database [if not exists] 数据库名称 [character set 字符集 collate 字符集校对规则];

案例：

show databases;
create database test111;
create database test222 character set gbk;
create database test333 character set utf8 collate utf8_bin;

查看数据库show

查看所有数据库： show databases;

查看某个数据库的定义信息： show create database 数据库名称;

案例：

show create database test333;

修改数据库alter

alter database 数据库名称 character set 字符集 collate 字符集校对规则;

案例：

alter database test222 character set utf8;
show create database test222;

删除数据库drop

drop database [if exists] 数据库名称;

案例：

drop database test333;

切换数据库use

use 另一个数据库的名字;

案例：

use test1;

操作数据库中的表💥

创建表create🔥

create table [if not exists] 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束, ... 字段名称 字段类型(长度));

字段类型对应：

Java数据类型MySQL字段类型
byte / short / int / longtinyint / smallint / int / bigint
floatfloat
doubledouble
booleanbit
char/Stringchar/varchar
Datedate / time / datetime / timestamp
Filebit

区别：

  • char和varchar的区别：
    • char代表固定长度的字符或字符串。如char(8)，存入“hello”，那么数据库会使用三个空格补全；超过长度则报错。
    • varchar代表可变长度的字符串。如varchar(8)，存入“hello”，那么数据库存入是就是5长度。
  • datetime和timestamp的区别:
    • datetime，既有日期又有时间，未传入时则使用null存入到数据库中
    • timestamp，也叫时间戳，既有日期又有时间，未传入时则使用当前系统时间存入到数据库中
  • BLOB和TEXT的区别:
    • BLOB是二进制文件，TEXT是文本文件

单表约束的分类:

  • 主键约束 primary key。注意：主键约束默认就是唯一非空的
  • 唯一约束 unique
  • 非空约束 not null

案例：

CREATE TABLE user1(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username varchar(20) UNIQUE,
	password varchar(20) NOT NULL,
	age INT,
	birthday DATE
);

查看表show🔥

show tables; // 查看某个数据库下面的所有的表

desc 表名; // 查看某个表的结构信息 image.png

修改表alter

alter table 表名 add 字段名 类型(长度) 约束; // 修改表：添加列

alter table 表名 modify 列名 新类型(新长度) 新约束; // 修改表：修改列类型、长度和约束

alter table 表名 drop 列名; // 修改表：删除列

alter table 表名 change 旧列名 新列名 类型(长度) 约束; // 修改表：修改列名称

rename table 旧表名 to 新的表名; // 修改表：修改表名

或：alter table 表名 rename to 新的表名; // 修改表：修改表名

alter table 表名 character set 字符集; // 修改表：修改表的字符集

案例：

desc user1;
alter table user1 add image varchar(100);
alter table user1 modify image varchar(150);
alter table user1 drop age;
alter table user1 change image picture varchar(150);
rename table user1 to user2;
desc user;

删除表drop

drop table 表名;

案例：

drop table user2;

操作数据库中表的记录💥

添加表记录insert🔥

insert into 表名 (字段名1，字段名2，字段名3，...) values (值1，值2，值3，...) //插入某些字段

insert into 表名 values (值1，值2，值3，...) // 插入所有字段

案例：

insert into user (id, username, password) values (null, 'user1', 'test123');
insert into user values (null, 'user2', 'qwer123', 28, '1990-1-1');
insert into user (id, username, password) values (null, '张三', 'test123');

修改表记录update🔥

update 表名 set 字段名=值,字段名=值,字段名=值... [where 条件];

多个条件中间用 and 或者 or 连接。

运算符：=、!=、<>（不等于）、>、<、>=、<=、BETWEEN…AND、IN(...)、IS NULL、NOT、OR、AND

案例：

update user set username='xinghaha',password='12306' where id = 1;
update user set birthday='1990-01-01'; // 修改全部记录的

删除表记录delete🔥

delete from 表名 [where 条件]; // 如果没有条件，则默认删除表中的所有记录

truncate table 表名；

案例：

delete from user where username='user2';

删除表中记录有两种方法：

  • delete from 表名；
    • 删除所有记录，属于DML语句；事务可以作用在DML语句上；可以回滚
    • 一条记录一条记录的删除；
  • truncate table 表名；
    • 删除所有记录，属于DDL语句；事务无法控制DDL；无法回滚
    • 删除整个表，然后重新创建一个结构一样的表；

查看表记录select🔥

1-基本查询

select [distinct] *|字段名 as 别名 from 表名 [条件]; // distinct-去重

案例：

// 查询所有学生的考试信息
select * from exam;
// 查询所有学生的英语成绩和姓名
select name,english from exam;
// 查询学生英语信息，去掉重复值
select distinct english from exam;
// 查询所有学生及对应的总成绩
select name,(english+chinese+math) from exam;
// 查询所有学生及对应的总成绩，且换个字段名
select name,english+chinese+math sum from exam;

2-条件查询

select [distinct] *|字段名 from 表名 where 条件表达式;

  • 条件表达式中可以使用 >,<,>=,<=,<>,=
  • 可以使用 like 进行模糊查询
    • 使用下划线_ 代表一个字符
    • 使用百分号% 代表任意个字符
  • 可以使用 in 进行范围查询
  • 条件关联：and or not

案例：

// 条件查询：查询名字为李四的学生
select * from exam where name='李四';
// 条件查询：查询名字为李四并且英语成绩大于90分的学生
select * from exam where name='李四' and english>90;
// 条件查询：模糊：查询姓李的所有学生的信息
select * from exam where name like '李%';
// 条件查询：查询英语成绩是69，75，89学生的信息
select * from exam where english in (69, 75, 89);

3-排序查询

order by 字段名称 asc/desc; // asc：升序--默认就是升序的；desc：降序

案例：

// 条件查询：排序：按语文成绩查询学生信息
select * from exam order by chinese desc;
// 条件查询：排序：先按语文成绩降序排序，如果语文成绩相同，按英语成绩升序排序
select * from exam order by chinese desc,english asc;
// 条件查询：排序：查询姓李学生的，且按照英语成绩降序排序
select * from exam where name like '李%' order by english desc;

4-聚合函数

sum() 求和

// 条件查询：聚合查询：获取所有学生英语成绩总和
select sum(english) from exam;
// 条件查询：聚合查询：获取所有学生英语成绩总和和数学成绩总和
select sum(english),sum(math) from exam;
// 条件查询：聚合查询：查询姓李的学生英语成绩的总和
select sum(english) from exam where name like '李%';
// 聚合查询：查询所有学生的总成绩
select sum(english)+sum(chinese)+sum(math) from exam;

count() 统计个数

// 聚合查询：获得学生的总数
select count(*) from exam;

max()  获取列最大值

// 聚合查询：获取数学成绩的最高分
select max(math) from exam;

min() 获取列最小值

// 聚合查询：获取数学成绩的最低分
select min(math) from exam;

avg()   获取平均值

// 聚合查询：获取班上学生的语文成绩的平均值
select avg(chinese) from exam;

5-分组查询

group by 字段名

create table orderitem(
	id int primary key auto_increment,
	product varchar(20),
	price double
);
insert into orderitem values(null, '电视', 1299);
insert into orderitem values(null, '电视', 2999);
insert into orderitem values(null, '洗衣机', 1000);
insert into orderitem values(null, '洗衣机', 3000);
insert into orderitem values(null, '洗衣机', 8000);
insert into orderitem values(null, '冰箱', 2666);
insert into orderitem values(null, '空调', 5000);
insert into orderitem values(null, '冰箱', 5432);

select * from orderitem;

// 分组查询：按商品名称统计个数
select product,count(*) from orderitem group by product;
// 分组查询：统计每类商品金额总和
select product,sum(price) from orderitem group by product;
// 分组查询：统计每类商品金额总和在5000及以上的
select product,sum(price) from orderitem group by product having sum(price)>=5000;
// 分组查询：统计每类商品金额总和在5000及以上的，且按总金额升序排列
select product,sum(price) from orderitem group by product having sum(price)>=5000 order by sum(price) asc;

6-limit子句

Limit 用来限定查询结果的起始行，以及总行数

select * from 表名 limit 8,5; // 从第9行开始查,查询五行

SQL语句总结🔥🔥

select [distinct] 字段|* from 表名 .. where 条件表达式 ... group by ... having ... order by ...

多表设计

主键约束

主键约束：唯一标识。非空、唯一、被引用

指定主键的两种方式：

CREATE TABLE stu(
    Sid CHAR(6) PRIMARY KEY AUTO_INCREATE, -- 设置主键并自增长
    Sname VARCHAR (20),
)；

CREATE TABLE stu(
    Sid CHAR(6),
    Sname VARCHAR (20),
    PRIMARY KEY(Sid)
)；

修改和删除主键：

修改表时指定主键: ALTER TABLE stu ADD PRIMARY KEY(sid);
删除主键：ALTER TABLE stu DROP PRIMARY KEY;

设置外键约束

外键作用：用来保证多表之间的数据的完整性。如：

// 在员工表上添加外键，关联到部门表
alter table employee add foreign key (dno) references dept (did);
// 设置为非空
alter table employee modify dno int not null;

案例：

create table dept(
	did int primary key auto_increment,
	dname varchar(20)
);
insert into dept values (null, '市场部');
insert into dept values (null, '人事部');
insert into dept values (null, '教研部');
select * from dept;

create table employee(
	eid int primary key auto_increment,
	ename varchar(20),
	salary double,
	birthday date,
	sex varchar(10),
	dno int
);
insert into employee values (null, '张三', 8000, '1990-01-01', '男', 3);
insert into employee values (null, '李四', 9000, '1999-12-24', '男', 1);
insert into employee values (null, '王五', 10000, '1995-05-02', '男', 2);
insert into employee values (null, '赵六', 10000, '1990-01-01', '男', 3);
insert into employee values (null, '孙七', 20000, '1990-01-01', '男', 1);
select * from employee;

alter table employee add foreign key (dno) references dept (did);
alter table employee modify dno int not null;
insert into employee values (null, '哈哈', 8000, '1990-01-01', '男', null);

添加最后一个部门为空的记录时会报错：

image.png

结果：

image.png

表关系

少的一方是主，多的一方是从

  • 一对多

    • 如：一个部门下可以有多个员工，一个员工只能属于一个部门
    • 建表原则：需要在多的一方（员工）表里面，创建一个外键，用于指向一的一方（部门）表的主键。

  • 多对多

    • 如：一个学生可以选择多门课程，一门课程可以有多个学生选择
    • 建表原则：需要创建第三张表，在第三张表中，至少有两个字段作为外键，分别指向多对多双方的主键。 image.png

  • 一对一

    • 如：一个公司只有一个注册地址，一个注册地址只能对应一个公司

    • 唯一外键对应方式

      image.png

    • 主键对应方式

      image.png

多表查询💥

前置：库表准备

需求：选课系统，包含班级、学生、课程的实体。

分析：
    班级和学生之间，一个班级多含多个学生，一个学生只能属于一个班级，属于一对多的关系
    班级是一，学生是多，建表的时候，需要在学生表里创建一个外键，指向班级
    
    学生和课程之间，一个学生可以选多门课程，一门课程可以有多个学生选择，属于多对多的关系
    需要去创建中间表，里面有两个字段分别指向双方的主键

班级表（班级ID、班级名字、班级人数）

CREATE TABLE `classes` (
  `cid` int NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) NOT NULL DEFAULT '',
  `cnum` int NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
image.png

学生表（学生ID、姓名、性别、生日、所在班级ID）

CREATE TABLE `student` (
  `sid` int NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `sex` varchar(10) NOT NULL DEFAULT '',
  `birthday` date NOT NULL,
  `cno` int NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_classid` (`cno`),
  CONSTRAINT `fk_student_classid` FOREIGN KEY (`cno`) REFERENCES `classes` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
image.png

课程表（课程ID、课程名称）

CREATE TABLE `course` (
  `cid` int NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
image.png

创建课程和学生之间的关系，实际上是创建中间一张表（中间表本身的ID、关联学生ID、关联课程ID、学生该课程的分数）

CREATE TABLE `student_course` (
  `scid` int NOT NULL AUTO_INCREMENT,
  `sno` int NOT NULL,
  `cno` int NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`scid`),
  KEY `fk_student_score_2stu` (`sno`),
  KEY `fk_student_score_2course` (`cno`),
  CONSTRAINT `fk_student_score_2course` FOREIGN KEY (`cno`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_student_score_2stu` FOREIGN KEY (`sno`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
image.png

前置：数据准备

班级表数据准备：

insert into classes values (null, '01班', 20);
insert into classes values (null, '02班', 30);
insert into classes values (null, '03班', 32);
insert into classes values (null, '04班', 41);
image.png

学生表数据准备:

insert into student values (null, '张三', '男', '1990-09-01', 1);
insert into student values (null, '李四', '女', '1990-02-12', 1);
insert into student values (null, '王五', '男', '1993-03-16', 1);
insert into student values (null, '赵六', '男', '1992-08-09', 2);
insert into student values (null, '田七', '女', '1996-12-12', 2);
insert into student values (null, '老八', '男', '1996-12-12', 2);
insert into student values (null, '九九', '男', '1994-05-07', 3);
insert into student values (null, '哈哈', '女', '1993-09-19', 3);
insert into student values (null, '张张', '女', '1995-01-31', 4);
image.png

课程表数据准备:

insert into course values (null, 'Java');
insert into course values (null, 'PHP');
insert into course values (null, 'C++');
image.png

学生课程关联表数据准备:

insert into student_course values (null, 1, 1, 85);
insert into student_course values (null, 1, 3, 72);
insert into student_course values (null, 2, 2, 66);
insert into student_course values (null, 2, 3, 99);
insert into student_course values (null, 3, 2, 98);
insert into student_course values (null, 3, 3, 76);
insert into student_course values (null, 4, 1, 74);
insert into student_course values (null, 5, 1, 54);
insert into student_course values (null, 5, 2, 79);
insert into student_course values (null, 5, 3, 94);
insert into student_course values (null, 6, 2, 81);
insert into student_course values (null, 7, 1, 77);
insert into student_course values (null, 7, 3, 86);
insert into student_course values (null, 8, 3, 70);
insert into student_course values (null, 9, 1, 80);
insert into student_course values (null, 9, 2, 81);
insert into student_course values (null, 9, 3, 85);
image.png

交叉连接查询

交叉连接 cross join（了解）-查询到的是两个表的笛卡尔积。也叫自然连接：

select * from 表1 cross join 表2; // 方式1

select * from 表1,表2; // 方式2

案例：

// 班级和学生表交叉连接查询  (4*9=36，取笛卡尔积)
select * from classes cross join student;
select * from classes,student;
image.png image.png

内连接查询

内连接得到的是两个表的共有的部分（即满足条件的）

显示内连接：在SQL中显示的调用inner join关键字

隐示内连接：在SQL中没有调用inner join关键字

【标准】：select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx;

【方言】：select * from 表1 别名1,表2 别名2 where 别名1.xx=别名2.xx;

案例：

// 班级和学生表连接查询-显示内连接
select * from classes c inner join student s on c.cid=s.cno;
select * from classes c,student s where c.cid=s.cno;
image.png

外连接查询

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为 NULL：

select * from 表1 别名1 left outer join 表2 别名2 on 关联条件; // 左外连接

：右表记录无论是否满是条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为 NULL：

select * from 表1 别名1 right outer join 表2 别名2 on 关联条件; // 右外连接

外连接有一主一次，左外即左表为主！

案例：

// 左外连接
select * from classes c left outer join student s on c.cid=s.cno;
// 右外连接
select * from classes c right outer join student s on c.cid=s.cno;
image.png

内连接和外连接的区别

image.png

子查询🔥

子查询本质上是SQL语句的嵌套。出现的位置：

  • where 后作为条件存在
  • from 后作为表存在(多行多列）

条件：

单行单列： SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=] （SELECT 列 FROM 表2 别名2 WHERE 条件）

多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN，ALL，ANY](SELECT 列 FROM 表2 别名2 WHERE 条件)

单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1,列2 FROM 表2 别名2 WHERE 条件)

多行多列：SELECT * FROM 表1 别名1 (SELECT ....)别名2 WHERE 条件

带in的字查询

案例：

// 查询学生生日在95年之后的班级的信息
select * from classes where cid in (select cno from student where birthday > '1995-01-01');
image.png

带exists的子查询

如果子句是true，则前面的语句会执行

select * from classes where exists (select cno from student where birthday > '1995-01-01');
// 子语句为true，前面执行输出全部
image.png

带any的子查询

大于任意一个信息的，条件会进行查询

select * from classes where cid > any (select cno from student);
// 除了1不大于其他的，剩余的都大于任意一个cno
image.png

带all的子查询

select * from classes where cid > all (select cno from student);
// 没有任何cid大于所有的cno
image.png

多表查询综合案例🔥

需求：查询班级的名称和班级的实际总人数

select c.cname,count(*) from classes c,student s where c.cid=s.cno group by c.cname;
image.png 
需求：查询学生的姓名和学生所选所有课程的平均成绩

select s.sname,avg(sc.score) from student s,student_course sc where s.sid=sc.sno group by s.sname;
image.png 
需求：查询学生的姓名和学生选课的总数，显示选课超过1门的学生

select s.sname,count(*) from student s,student_course sc where s.sid=sc.sno group by s.sname having count(*) >=2;
image.png 
需求：查询平均成绩大于80分的学生的总数

select count(*) from student s where s.sid IN (select sc.sno from student_course sc group by sc.sno having avg(sc.score)>80);
image.png

事务

事务的基本操作

事务：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全部成功，要么全部失败。

开启事务： start transaction;

提交事务： commit;

回滚事务： rollback;

案例：

create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);

insert into account values (null, '小王', 10000);
insert into account values (null, '小张', 9000);

select * from account;

start transaction;
update account set money=money-1000 where name='小张';
update account set money=money+1000 where name='小王';
commit;
start transaction;
update account set money=money-1000 where name='小张';
update account set money=money+1000 where name='小王';
rollback;

事务的特性

  • 原子性
    • 事务的不可分割，组成事务的各个逻辑单元不可分割
  • 一致性
    • 事务执行的前后，数据完整性保持一致
  • 隔离性
    • 事务执行不应该受到其他事务的干扰
  • 持久性
    • 事务一旦结束（提交或回滚），数据就持久化到数据库中

事务的隔离级别

如果不考虑隔离性（一个事务执行受到其他事务的干扰），会引发一些安全问题，主要体现在读取数据上：

  • 脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致
  • 不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致
  • 虚读/幻读：一个事务读到了另一个事务已经提交的insert的数据，导致多次查询结果不一致

设置事务隔离级别

  • read uncommitted
    • （效率高，安全性低）
    • 脏读、不可重复读、虚读/幻读都有可能发生
  • read committed
    • （oracle默认这个）
    • 避免脏读；但是不可重复读、虚读/幻读是有可能发生的
  • repeatable read
    • （MySQL默认这个）
    • 避免脏读、不可重复读，但是虚读/幻读是有可能发生的
  • serializable
    • （最安全，效率最低）
    • 串行化的，避免脏读、不可重复读、虚读/幻读

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别 //设置事务的隔离级别

SELECT @@tx_isolation; // 查看当前的隔离级别

avatar
文章
阅读
粉丝