**MySQL的入门**
学习 数据库技术已成为趋势,相信各位小伙伴对MySQL不陌生吧,下面我将给大家介绍一下MySQL的基础及用法,希望对各位有所帮助!
一、数据库
- 数据库Database
存放数据和管理数据的仓库,DB
持久化存储数据。其实数据库就是一个文件系统,关系型数据库才能查到磁盘上的文件
和普通文件相比,便于存储和管理数据
关系型数据库使用统一的方式操作数据库–SQL
- 常见的数据库软件
Oracle:收费的大型数据库,Oracle 公司的产品
DB2:收费的关系型数据库,IBM公司,多用于银行系统
MySql:开源免费的数据库,小型,现属于oracle公司
SQL Server:收费的,属于MicroSoft公司,java语言用的比较少
SQLite:嵌入式的微型数据库,应用在手机端,如Android
常用的关系型数据库:MySql Oracle
2.关系型数据库(RDBMS)(Relational Database Management System)
将数据以表格的形式进行存储,如课程表、工资表等。
3.RDBMS 术语:
数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起。来像一个简单的电子表格
列: 一列(数据元素) 包含了相同的数据,
行:一行(=元组,或记录)是一组相关的数据,
表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。
冗余:数据冗余是指同一个数据在系统中多次重复出现。
一般情况下,应尽量减少数据冗余,保证数据的一致性,但在某些情况下,也需要适当增加数据冗余度。冗余的目的:为方便处理而使同一信息在不同地点有不同的表现形式。
主键:主键是唯一的,不能重复,一个数据表中只能包含一个主键。你可以使用主键来查询数据
外键:外键用于关联两个表,一张表中引用另一张表中的数据。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
4.SQL简介
结构化查询语言(Structured Query Language)是一种特殊的编程语言,是一种数据库查询和设计语言,用于存取数据以及查询、更新、管理关系数据库系统。是专门为关系型数据库设计出来的
- 分类:
数据定义语言(DDL:Data Definition Language),用于结构管理
create alter drop
数据操纵语言(DML:Data Manipulation Language),对表中数据的操作
select(DQL) insert update delete
数据控制语言(DCL:Data Control Language),用于权限管理
grant(分配) revoke(回收) deny(拒绝) - SQL通用语法
① SQL 语句可以单行或多行书写,以分号结尾。
② 可使用空格和缩进来增强语句的可读性。
③ MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
④ 3 种注释
* 单行注释: – 注释内容 或 # 注释内容(mysql 特有)
* 多行注释: /* 注释 */
二、mysql
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
Mysql是开源的,所以不需要支付额外的费用。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
MySQL支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
Mysql是可以定制的,采用了GPL协议,你可以修改源码来开发自己的Mysql系统。
配置
MySQL服务启动
1. 手动,服务里面启动 计算机—>右键—>管理—>服务
2. cmd–> services.msc 打开服务的窗口
3. 以管理员身份打开cmd
net start mysql : 启动mysql的服务
net stop mysql:关闭mysql服务
MySQL登录
1. mysql -uroot -p密码
2. mysql -hlocalhost -uroot -p密码
3. mysql --host=localhost --user=root --password=密码
MySQL退出
1. exit
2. quit
3.ctrl+c
4.\q
MySQL目录结构
1. MySQL安装目录:basedir=“D:\mysql”
* 配置文件 my.ini
2. MySQL数据目录:datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
数据库:文件夹
表:文件
三,数据库管理CRUD
- C(Create):创建
- 创建数据库:
create database 数据库名称; - 创建数据库,判断不存在,再创建:
create database if not exists 数据库名称; - 创建数据库,并指定字符集
create database 数据库名称charset字符集名;
- 创建数据库:
- R(Retrieve):查询
- 查询所有数据库的名称:
show databases; - 查询数据库的创建语句
show create database 数据库名称;
Show create database 数据库名称\G; – 显示格式更清晰
- 查询所有数据库的名称:
- U(Update):修改
- 修改数据库的字符集
alter database 数据库名称 charset 字符集名称;
- 修改数据库的字符集
- D(Delete):删除
- 删除数据库
drop database 数据库名称; - 判断数据库存在,存在再删除
drop database if exists 数据库名称;
- 删除数据库
- 使用数据库
- 查询当前正在使用的数据库名称
select database(); - 使用数据库
use 数据库名称;
- 查询当前正在使用的数据库名称
- 查看数据库当前状态
status;
四,mysql数据类型 - 整数类型
整型 字节 范围
Tinyint 1 (-128 - 127)
Smallint 2 (-32768~32767)
Mediumint 3 (-8388608~8388607)
int 4 (-2147483648~2147483647)
Bigint 8 (±9.22*10的18次方)
实际应用中,tinyint,int通常用的比较多 - 小数类型:浮点类型和定点类型
浮点型 含义
float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数,d小数位
定点数Decimal:
浮点型在数据库中存放的是近似值,m,d会自动确认,可省略不写。而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。默认是(10,0)
系统根据存储的数据分配存储空间,每大概9个数就会分配四个字节进行存储,同时小数和整数部分是分开的
浮点数的应用:通常用来保存一些数据量非常大,大到可以不用那么精确的数据,,一般使用double
定点数的应用:涉及到钱的时候有可能会用到
- 字符串:
字符串(char,varchar,_text) 含义
char(n) 固定长度,最多255个字符
varchar(n) 可变长度,最多65535个字符
Tinytext 可变长度,最多255个字符
Text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
Longtext 可变长度,最多2的32次方-1个字符
char和varchar:
1.char(n) ,n可以不写,默认为1,若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
varchar(n) ,n必须写
2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字符的空间,varchar是存入的实际字符数
3.char类型的字符串检索速度要比varchar类型的快。
- 日期和时间类型:
日期时间类型 含义
Date 3字节 YYYY-MM-dd 日期 ‘2008-12-02’
Time 3字节 HH:ii:ss 时间 ‘12:25:36’
Datetime 8字节 日期时间 ‘2008-12-02 22:06:44’
Timestamp 自动存储记录修改时间 时间戳类型,表示从格林威治时间计算开始
若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。
五, Mysql约束
对表中的数据进行限定,保证数据的正确性、有效性和完整性MYSQL中,常用的几种约束:
1. 非空约束:not null
2. 主键约束:primary key
3. 唯一约束:unique
4. 外键约束:foreign key
5. 默认约束:default
-
非空约束:not null,某一列的值不能为null
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
name VARCHAR(20) NOT NULL – name为非空
);
2. 创建表完后,添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
3. 删除name的非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);- 主键约束:primary key。
- 注意:
- 含义:非空且唯一
- 一张表只能有一个主键
- 主键就是表中记录的唯一标识
- 在创建表时,添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
Create table tableName(
id int,
name varchar(30) not null,
pass varchar(30) not null,
primary key(字段1,[字段2])
); - 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
Alter table tableName add primary key(字段1[,字段2],…); - 删除主键
ALTER TABLE stu drop primary key; - 自动增长:
- 如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
- 在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
); - 删除自动增长
ALTER TABLE stu MODIFY id INT; - 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
- 注意:
- 主键约束:primary key。
-
唯一约束:unique,某一列的值不能重复
1. 注意:
* 唯一约束可以有NULL值,但是只能有一条记录为null
2. 在创建表时,添加唯一约束
create table tableName(
id int primary key auto_increment,
name varchar(20) unique);
create table tableName(
id int primary key auto_increment,
name varchar(20) ,
unique key(name) );
3. 删除唯一约束
ALTER TABLE stu DROP INDEX name;
4. 在表创建完后,添加唯一约束
ALTER TABLE stu MODIFY name VARCHAR(20) UNIQUE;
Alter table tableName add unique key(name)- 外键约束:foreign key,让表与表产生关系,从而保证数据的正确性。
-
在创建表时,可以添加外键
- 语法:
create table 表名(
…
,
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
- 语法:
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; -
创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称); -
级联操作
- 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; - 分类:
- 级联更新:ON UPDATE CASCADE
- 级联删除:ON DELETE CASCADE
外键的约束非常强大,可能会影响正常的业务逻辑,一般在开发中,不会使用外键。
- 添加级联操作
-
- 外键约束:foreign key,让表与表产生关系,从而保证数据的正确性。
-
默认约束default
- DEFAULT约束只会在使用INSERT语句时体现出来,INSERT语句中,如果被DEFAULT约束的位置没有值,那么这个位置将会被DEFAULT的值填充
2. 创建表时添加默认值
create table test3(
id int primary key auto_increment,
name varchar(20) default ‘tom’
) - 创建表之后添加默认值
alter table test3 modify name varchar(20) default ‘jack’;
alter table test3 alter name set default ‘jack’; - 删除默认值
alter table test3 modify name varchar(20);
alter table test3 alter name drop default;
六,数据库表管理(DDL)
- C(Create):创建
- 创建表:
create table tableName(
id int auto_increment primary key,
name varchar(30),
pass varchar(30)
); - 复制表结构(不复制数据):
Create table tableName like anotherTableName;
复制部分表结构(where后的条件不成立不复制数据)
create table tableName select id,name form user where 1=0; # 只复制id和name结构,不复制数据
复制表结构和数据
create table tabName select * from olderName;
复制表结构和部分数据
create table tableName select * from user where id>10;# 复制表user中id>10的数据到Copy2
- R(Retrieve):查询
- 查询某个数据库中所有的表名称
show tables; - 查询表结构
desc 表名;
- 查询某个数据库中所有的表名称
- U(Update):修改
- 修改表名
alter table 表名 rename to 新的表名;
rename table 表名 to 新表名;
- 修改表名
- 修改表的字符集
alter table 表名 charset 字符集名称;- 添加一列
alter table 表名 add 列名 数据类型; - 修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型; - 修改列的属性
alter table 表名 modify 列名 新数据类型。。。; - 删除列
alter table 表名 drop 列名;
- 添加一列
-
D(Delete):删除
drop table 表名1,[表名2,表名3]
drop table if exists 表名
七, 数据的操作(DML) -
增加数据
insert into 表名(列名1,列名2 )values(值1, 值2);
注意:- 列名和值要一一对应。
- 如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,…值n); - 除了数字类型,其他类型需要使用引号(单双都可以)引起来
- 一次性可以插入多条数据insert into表名(name) values(“tom”),(”jack”);
- 不可以为null的列一定要插入值,可以为null的列可插入null或者不插入默认为null;
insert into users (id,name,pass,age,sex,borndate) values (2,‘李四’,‘lisi’,20,‘女’,null);
insert into users (id,name,pass,age,sex) values (2,‘李四’,‘lisi’,20,‘女’);
-
修改数据
- 更改某一列:
UPDATE tableName SET colName = 新值 [ , 列名 = 新值 ] WHERE 列名称 = 某值; - 更改所有:(不加条件)
Update tableName set colName = 新值;
- 删除数据:
Delete from 表名 [ where 列名= 值 ] ;
- 如果要删除所有记录
delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作
再次插入,id会从断点开始自增
delete可以回滚 - TRUNCATE TABLE 表名; – 推荐使用,效率更高 先删除表,然后再创建一张一样的表,跟数据多少没关系。
再次插入,id的值从1开始
truncate不可以回滚
- 查询表中数据
- 基础语法
Select column
from tableName
where 条件
group by 分组
having 条件
order by 排序
limit 限制 - 基础查询
① 多个字段的查询
select 字段名1,字段名2… from 表名;
② 如果查询所有字段,则可以使用*来替代字段列表。
select * from 表名;
③ 去除重复:distinct
Select distinct 字段名 from 表名;
多个字段同时去重,就是name与id同时重复会去掉
select distinct name, id from A;
④ 计算列
一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算,放在select中)
Select id,name,(chinese+math+english) total_score from student order by total_score desc;
⑤ +
如果两个都是数值,则正常做数值运算
select 90+100; #190
如果有一方位字符,则尝试转换,如果转换成功,则做运算
select ‘120’+80;
如果转换失败,则当做0继续做运算
select ‘Tom’+100; #select 0+100; 100
如果有一方为null,则结果都为null
select null+50; # null
⑥ 起别名 as,as可以省略
Select name as n from user;
Select name n from user;
⑦ concat 字符串拼接
Select concat('a’,’b’,’c’);
拼接查询的结果,会将两列的值做拼接显示
select concat(name1,pass) AS 姓名密码 from user;
如果拼接字段时列中的值可能为null,则可以用ifnull函数
# ifnull(判空表达式,如果为null返回的值)
select concat(name1,IFNULL(pwd,'aaa')) from user; #如果pwd为空则拼接aaa
3) 条件查询
① where子句后面跟条件
Select列名 from表名 where 列 运算符 值;
② 运算符
a)= <> != > < >= <=
b)between and
c)like 模糊查询 not like
%:表示任意个或多个字符。可匹配任意类型和长度的字符。
n%:表示以n为开始;
%n:表示以n为结束;
%n%:表示包含n的内容
:表示任意单个字符
n:表示以n开头,后面为任意字符的;
n:表示以任意字符开头,后面为n的;
n_m:表示以n为开头,后面为任意一个字符,在后面为m,最后为任意一个字符的;
如果要匹配的字符中出现特殊字符,如_或者%,需要转义
通过斜杠转义
匹配名字中第二个字符是下划线的用户信息
select * from users where name like ‘__%’
通过escape指定转义
匹配名字中第二个字符是下划线的用户信息
select * from users where name like ‘@%’ escape ‘@’
此处转义符是自定义,可以是任意字符,后面定义即可使用
d)in(值1,值2,值3) not in(值1,值2,值3) 值不能出现转义字符
e)and 或者 &&
f)or 或者 ||
g)is null is not null
h)not 或者 !
not between … and …
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
SELECT * FROM student WHERE age = 20;
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
Select * from user where name = ‘张三’ and pass= ‘z123’;
Select * from user where name = ‘张三’ && pass= ‘z123’;
Select * from user where name = ‘张三’ or id = 1;
Select * from user where name = ‘张三’ || id = 1;
– 查询年龄大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
– 查询年龄22岁,18岁,25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25)
– 查询英语成绩为null
SELECT * FROM student WHERE english IS NULL;
– 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
– 查询姓张的有哪些?
SELECT * FROM student WHERE NAME LIKE ‘张%’;
– 查询姓名第二个字是英的人
SELECT * FROM student WHERE NAME LIKE "英%";
– 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '__’;
– 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE ‘%德%’;
4)排序查询
语法:order by 子句
order by 排序字段1 排序方式1 , 排序字段2 排序方式2…
* 排序方式:
* ASC:升序,默认的。DESC:降序。
* 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
select * from employees order by salary desc;
select * from employees where age not between 30 and 60 order by id desc;
select *,ifnull(salary,0)*12 年薪 from employees order by ifnull(salary,0)*12 ASc;
select *,ifnull(salary,0)*12 年薪 from employees order by 年薪 desc;
查询所有员工信息,按照姓名字节长度降序排序
select * from employees order by length(name) desc;
查询年龄大于30的员工信息,先按照年龄升序,再按照工资降序
select * from employees where age>30 order by age asc,salary desc;
5)分页查询
① 语法:limit 开始的索引,每页查询的条数;
② 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
– 每页显示3条记录
SELECT * FROM student LIMIT 0,3; – 第1页
SELECT * FROM student LIMIT 3,3; – 第2页
③ limit 是一个MySQL"方言"
5) 聚合函数
Upper()函数 将文本转换为大写
AVG() 返回某一列的平均值,数字类型
Max()返回某一列的最大值,数字类型、datetime
MIN()返回某一列的最小值(null不计入),数字类型、datetime
SUM()返回某一列的和,数字类型
count()返回某一列的行数
一般选择非空的列,主键
Select count(id) from tableName;//不计入null值
Select count(*) from tableName;//计入null值
6) 日期函数
now 返回当前系统日期+时间
select now();
curdate 返回当前系统日期,不包含时间
select curdate();
curtime 返回当前系统时间,包含日期
select curtime();
获取参数中的年、月、日、时、分、秒
获取当前时间的年
select year(now());
获取自定义参数中的年
select year(‘2020-01-15’);
查询表中的参数获取年
select year(birthed) from users;
获取月,中文显示
select month(now());
获取月,英文显示
select monthname(now());
#获取日
select day(now());
获取时
select hour(now());
获取分
select minute(now());
获取秒
select second(now())
Mysql默认值(default)不支持函数,可以用timestamp时间戳类型
CREATE TABLE test1(id int auto_increment PRIMARY KEY,date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
7)流程控制函数
if函数:if。。else的效果。类似三目运算
select if(10<5,‘大’,‘小’);
查询用户有没有成年
select age,if(age>18,‘成年人’,‘未成年’) from users where age is not null;
case函数
case 要判断的字段或表达式
when 常量1 then 要显示的值1【或语句1;】
when 常量2 then 要显示的值2【或语句2;】
。。。
else 要显示的值n【或语句n;】
end;
查询用户姓名,如果是20,显示老油条,如果是30,显示中年,如果是40,显示老年。。。
select age,
case age
when 20 then ‘老油条’
when 30 then ‘中年’
when 40 then ‘老年’
else ‘没有’
end AS ‘年龄分类’
from users;
7) 分组查询
group by:将数据分为多个分组,是为了分组后进行数据统计的,如果只是想看数据显示,那么group by没有什么含义。group by按照指定字段分组后,只会保留每组的第一条记录
Select 列名,count(*) AS num from 表名 group by 列名
SELECT age,COUNT(age) FROM USER GROUP BY age
Eg:错误案例
SELECT name1,COUNT(age) FROM USER GROUP BY age//有问题,不正确
多分组:对已经分组的数据再分组
语法:group by 字段1,字段2;//先按字段1分组,分组之后将结果再按字段2进行分组
MySql中,分组有默认的排序功能,会按照分组字段进行排序默认升序(降序使用DESC)
SELECT age,SUM(age) FROM USER GROUP BY age DESC;
Having:筛选分组结果,WHERE 关键字无法与聚合函数一起使用。放于group by之后,having后面的属性必须是在前面查过的,包括group by
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
SELECT Customer,SUM(OrderPrice) total_price FROM Orders
GROUP BY Customer
HAVING total_price<2000
– 按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
– 按照班级跟年龄分组查询平均分数
select avg(score),age,cid from student group by age,cid;
– 按照班级跟年龄分组查询平均分数,按照均平分倒序,年龄正序
select avg(score),age,cid from student group by
age,cid order by avg(score) desc,age asc;
强调:having是在group by之后,group by 是在where之后,where的时候表示将数据从磁盘拿到内存,别名是在内存里存在的,where之后的所有操作都是内存操作
8) 数据库的设计
多表之间的关系
1) 一对一
如:人和身份证分析:一个人只有一个身份证,一个身份证只能对应一个人
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
2) 一对多(多对一):
如:部门和员工,一个部门有多个员工,一个员工只能对应一个部门
实现方式:在多的一方建立外键,指向一的一方的主键。
3) 多对多:
如:学生和课程,一个学生可以选择很多门课程,一个课程也可以被很多学生选择
实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
9) 多表连接查询:
将多张表连接在一起进行查询。在数据库设计中遵循着关系来设计,一对一,一对多,多对多,保证数据的完整性
多表等值连接的结果为两表的交集部分
n表连接,至少要n-1连接条件
表的别名:
SELECT column_name
FROM table_name
AS alias_name
Select name from (select name,age from user) as a;
列的别名:
SELECT column_name AS alias_name
FROM table_name
①笛卡尔积:
有两个集合A,B .取这两个集合的所有组成情况。
要完成多表查询,需要消除无用的数据
Select A.,B. from A,B;
② 内连接(自然连接)
从一张表中取出所有记录去另外一张表中利用匹配条件进行匹配,成功则保留,否则放弃。常见的多表连接查询便是一种内连接
内连接一般用在对数据用精确要求的地方,两表中必须都能进行数据匹配
基本语法:
隐式内连接:
select 要查询的内容 from 表名1,表名2 where 条件
显示内连接:
select 要查询的内容 from 表名1 inner join 表名2 on 条件
select 要查询的内容 from 表名1 inner join 表名 2 on 条件 inner join 表名3 on 条件
select ename,dname from employees,departments where employees.did = departments.id;
select ename,dname from employees e,departments d where e.did = d.id;
查询id在2到4之间的老师和课程名
select tname,cname from teacher t,course c where c.tid=t.id and t.id between 2 and 4;
查询每门课程选修的学生人数
select conut(*),cname from student,course where student.cid=c.id group by cname;
查询每门课程选修的学生人数,人数大于2的,按课程名排序
select conut(),cname from student,course where student.cid=c.id group by cname having count()>2 order by cname desc;
③ 自连接:将一张表通过起别名的方式当做两张表使用
查询学号为1的学生的所有同学
select s2.name,s2.cid from student s1,student s2 where s2.cid=s1.cid and s1.id=1;
查询同时学习语文和数学的学生信息
select name,cid from
student,grade g1,grade g2
where student.id = g1.stu_id and g1.lessname=‘语文’
and student.id = g2.stu_id and g2.lessname=‘数学’;
④ 外连接
左外连接:以左边的表为主表,右边的表为从表,显示左边的表的全部信息,若从表中没有主表的对应的数据,则以null值填充;
Select * from 表1 left join(left outer join)表2 on 条件;
– 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称
SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
右外连接:与左外连接相反,以右边的表为主表,左边的表为从表,显示右边的表的全部信息,若从表中没有主表的对应的数据,则以null值填充;
Select * from 表2 right join(right outer join)表1 on 条件;
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
10) 子查询:
子查询允许把一个查询嵌套在另一个查询当中。
子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询。
按位置分类:
select 中
from 后也就是数据源中
where 条件中
group by 和order by 中无实用意义
员工表
部门表
子查询不同情况
1. 子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。
运算符: > >= < <= =
– 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的:
子查询可以作为条件,使用运算符in、not in来判断
-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3. 子查询的结果是多行多列的:
子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11'
select * from (select * from t5 order by score desc) s group by name;
5, 数据库设计的范式
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
第一范式(1NF):是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库满足第一范式。
第一范式的合理遵循需要根据系统给的实际需求来确定。比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成为一个数据库表的字段就行,但是如果系统经常访问“地址”属性中的“城市”部分,那么一定要把“地址”这个属性重新拆分为省份、城市、详细地址等多个部分来进行存储,这样对地址中某一个部分操作的时候将非常方便,这样设计才算满足数据库的第一范式。如下图。
地址: 江苏省南京市玄武区XXX
第二范式(2NF):在第一范式的基础上更进一层,第二范式需要确保数据库表中每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下图。
这里产生一个问题:这个表中是以订单编号和商品编号作为联合主键,这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关,而仅仅是与商品的编号相关,所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分,把商品信息分离到另一个表中,把订单项目表也分离到另一个表中,就非常完美了,如下图。
第三范式(3NF):需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系,而不可以在订单表中添加关于客户其他信息(比如姓名、所属公司)的字段,如下面这两个表所示的设计就是一个满足第三范式的数据库表。
这样在查询订单信息的时候,就可以使用客户编号来引用客户信息表中的记录,也不必在订单信息表中多次输入客户信息的内容,减小了数据冗余。