SQL语句分类
- DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等。
- DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)。
- DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别。
- DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
*注意sql语句以;结尾
MySQL数据库操作常见指令
数据库的增删改查
- show dataBases;
- use 数据库;
- use table ;
- create database 数据库名
- drop database 数据库名
数据库表的增删改查
- create table t_name (字段 字段类型,。。。。);
- alter table t_name add(增)/modify(改) 字段名 字段名类型
- alter table t_name change 旧字段名 字段名类型 新字段名 字段名类型
- alter table t_name drop 字段名
- desc table t_name (查)
- drop table t_name;
- show table
数据库表中内容的操作
- insert into t_name values(字段值。。。。。);
- update t_name set 字段=值,字段=值,字段=值 where 条件(无条件全改);
- delete from t_name where 条件(无条件全删);
having 和 where 的异同
相同的:
都可以对查询的内容进行条件筛选
不同的:
where直接对磁盘的数据进行筛选
having对分组查询后存放在内存中的数据进行筛选
where查询得到的结果只有一行
having查询得到的结果又很多行,最大和分组得到的行数一致
where之后的条件不能直接是聚合函数
having之后的条件可以使聚合函数
关键字的顺序
- 各关键字书写顺序
- select -> from -> where -> group by -> having -> order by -> limit
- 各关键字的执行顺序
- from-> where-> select-> group by-> having-> order by-> limit
分页查询–每页M条数据,第N页 limit (N-1)*M,M
写一个表达式,每页显示3条内容
- 第一页 limit 0,3
- 第二页 limit 3,3
- 第三页 limit 6,3
- 第四页 limit 9,3
- 。。。
- 第N页 limit (N-1)*3,3
SQL语句的复合语句
select * from t_name whrer 条件1 group by 字段1having 条件
- 先按照条件1查询
- 结果按字段(字段为结果中的字段)分组并按照having中条件筛选分组(去重)
外键约束补充
- 外键约束的注意事项
- 1)所谓的外键,其实是一个表里的一个列,该类必须引用(参考)自另外一张表中的主键。此时该列就称为外键。该表就称为从表,而另一张被参考的表称为主表。
- 2)外键的数据类型必须和主表的主键的数据类型一致。列名可以不同。
- 3)外键可以是null值。
- 4)主表中的主键的值被删除,会影响到外键
域完整性约束
- 插入的字段值要和表结构中的字段数量、类型、顺序保持一致
字段数据类型
int
bigint
char
varchar
text
mediumtext
longtext
time
date
datetime
year
float
double
主键约束
-
primary key
- 一个表中只能存在一个主键,但可以有多个联合主键
- 被设置为主键的列,本列中不能存放重复的元素
- 可以与其他表的列关联,形成外键关系
-
设置外键的方法
-
直接在建表时候指定
- create table tb_name(id int auto_increment primary key,name varchar(20),…);
- create table tb_name(id int auto_increment,name varchar(20),…,primary key(id));
-
在表创建完成后通过修改的方法设置主键
- 修改表中的字段约束,设置为主键
- ALTER TABLE stu MODIFY sid CHAR(6) PRIMARY KEY;
- 通过修改的方法向表中添加主键
- ALTER TABLE stu ADD PRIMARY KEY(sid)
- 修改表中的字段约束,设置为主键
-
设置联合主键
- create table classmate(id int,name varchar(20),addr varchar(20),primary key(name,addr));
-
表和表之间关系的介绍
- 表和表之间的关系
- 一对一
- 你的个人信息(教育信息、身体信息、个人品性等信息);
- 一对多
- 一个班级有多个学生,一个护士对应着多个病人
- 多对多
- 一个学生选择了多门课,一门课可以被多个学生选。
- 一对一
联合查询–union,union all
-
把多种表中查询得到的数据上下拼接起来
-
必须保证列的数量是一致的
-
跟列的名字没有关系
-
union查询得到的结果默认会去除完全重复的记录,表头为Union第一个表
-
union all可以保留重复的数据
-
select * from stu03 union SELECT * from STU01; --查询的表头为stu03 的内容
连接查询-内连接,外连接(左,右外连接,全连接)
-
将查询得到的结果左右拼接起来
-
默认的方式会采用笛卡尔积的形式显示,无效数据很多
-
我们可以采用where限制条件的方式,显示指定的结果
-
还可以使用查询部分字段的方式,去除重复数据
-- 连接查询 select * from stu01,stu02; -- 产生了很多没有用处的结果,可以使用where对查询条件加以限制 select * from stu01,stu02 where stu01.name = stu02.name; -- 可以查询部分字段,去除重复结果 select stu01.*,stu02.addr,stu02.intro from stu01,stu02 where stu01.name=stu02.name; -- 使用别名 select a.*,b.addr,b.intro from stu01 as a,stu02 as b where a.name=b.name;
内连接查询 (select * from A join B on 条件)-交集
-
查询两张表中的交集
-
格式
- select * from A join B on A.name=B.name;
-- 内连接查询 select * from stu01 join stu02 on stu01.name=stu02.name; select * from stu01,stu02 where stu01.name=stu02.name;
左外连接查询 (select * from A left[outer] join B on 条件)
-
查询左侧表中所有的内容 & 右侧表中和左侧表中指定条件相同部分的内容
-
格式
- select * from A left [outer] join B on A.name=B.name;
-- 左[外]连接查询 select * from stu01 LEFT JOIN stu02 on stu01.name=stu02.name; select * from stu01 LEFT OUTER JOIN stu02 on stu01.name=stu02.name;
右外连接查询 (select * from A right[outer] join B on 条件)
-
查询右侧表中所有的内容 & 左侧表中和右侧表中条件形同的部分内容
-
格式
- select * from A right [outer] join B on A.name=B.name;
-- 右[外]连接查询 select * from stu01 RIGHT JOIN stu02 on stu01.name=stu02.name; select * from stu01 RIGHT OUTER JOIN stu02 on stu01.name=stu02.name;
全连接查询=左外连接 +union +右外连接 =全集
- 查询A、B表中所有的内容,把这些内容联合起来,相同条件部分的内容在同一行显示
- MySQL中没有提供全连接查询的方式
- 我们可以把左连接和右连接查询得到的结果使用union联合
- A 和B的位置装换的全连接内容一样
select * from stu01 LEFT JOIN stu02 on stu01.name=stu02.name
union
select * from stu01 RIGHT JOIN stu02 on stu01.name=stu02.name;
子查询
标量子查询
-
把一个单个的查询结果当做条件
-
标量子查询
-
产生满足字段和后面标结果的记录
-
查询高于平均年龄的好汉的信息
- select * from shuihu where age>(select avg(age) from shuihu);
-
行子查询
- 产生一(多)行多列的记录
- 查询出与zhangsan的薪水和部门完全相同的员工的信息
- select * from mpe where sal=(select sal from mpe where ename=‘SCOTT’) and job=(select job from mpe where ename=‘SCOTT’);
- select * from mpe where (sal,job) in (select sal,job from mpe where ename=‘SCOTT’);
列子查询
-
产生一列的记录
-
查询出是主管的员工的信息
-
select * from mpe where mpr in (select empno from mpe where ename=“SCOTT”);
表子查询
-
从产生一张虚拟表查询结果
-
查询出工资最高的前3名员工的信息
- select * from (select * from mpe order by sal desc as t) as a limit 3;
- select * from emp order by sal desc limit 3;
-
注意: 表子查询需要给子查询出的虚拟表起一个别名
ANY和ALL
any
-
any,任意一个,只要有一个满足条件即可
-
查询工资高于任一个经理的分析师的信息
- select * from emp where sal>ANY(select sal from emp where job=“MANAGER”) AND job=“ANALYST”;
all
- 所有的,满足全部条件才可以
- 查询工资高于所有经理的员工信息
- SELECT * FROM EMP where sal>all(select sal from emp where job=“MANAGER”);
some
- 和any的意义、用法完全一样