数据库概述
MySQL数据库
SQL--DDL
SQL--DML
SQL--DQL
多表设计_关联查询
Mysql数据库概念
MySQL
是一个关系型数据库管理系统
,
由瑞典
MySQL AB
公司开发,目
前属于
Oracle
旗下产品。
MySQL
流行的关系型数据库管理系统。
MySql
是一种关系数据库管理系统。
MySql
软件是一种开放源码软件
,
你可以修改源码来开发自己的
Mysql
系统。
MySql
数据库服务器具有快速、可靠和易于使用的特点。
MySql
使用标准的
sql
语言
,
并且支持多种操作系统
,
支持多种语言
.
mysql
商业版与社区版
MySQL
商业版是由
MySQL AB
公司负责开发与维护
,
需要付费才能使用
MySQL
社区版是由分散在世界各地的
MySQL
开发者、爱好者一起开发与维
护,可以免费使用
Mysql数据库安装
MySQL
的安装
(
参考安装资料
)
命令行方式连接
mysql
登录:
mysql [-hlocalhost -P3306]
(本机可省略)
-uroot -p
(可以直
接写密码,不能有空格)
-h
:主机名
-P
:端口号
-u
:用户名
-p
:密码
退出:
exit
Mysql数据库
MySQL
的常用命令
查看当前所有的数据库:
show databases;
选择指定的库:
use
库名
查看当前的所有表:
show tables;
查看其他库的所有表:
show tables from
库名
;
查看
mysql
版本
select version();
安装可视化客户端工具
SQLyog / Navicat
sql
SQL
优点:
不是某个特定数据库供应商专有的语是言,几乎所有
DBMS
都支持
SQL
简单易学,灵活使用可以进行非常复杂和高级的数据库操作
DDL
常用的语句:create ,alter,drop,rename创建
,
删除数据库
-- 创建数据库
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
-- 删除数据库
DROP DATABASE IF EXISTS schooldb
-- 数据库一旦创建,名字不能修改
-- 修改数据库字符集
ALTER DATABASE schooldb CHARSET utf8
DDL
设 计 表
对于具体的某一个表,在创建之前,需要确定表的下列特征:
表名
(
表信息
)
表中的字段
字段的数据类型和长度
哪些约束
char
(
n
) 长度为
n
的定长字符串
,
最大长度
255
个字符
varchar(n
) 最大长度为
n
的可变长字符串
date
日期, 包含年月日
datetime
年月日 时分秒
-- 创建表
CREATE TABLE student(
number INT,
NAME VARCHAR(6),
gander CHAR(1),
birthday DATE,
phone VARCHAR(11),
height DECIMAL(3,2),
reg_time DATETIME
)
设计表
(
数据类型
)
浮点
decimal
数据类型
(M,D)
M
:精度,数据的总长度;
D
:标度,小数点后的长度
-- 创建表
CREATE TABLE student(
number INT,
NAME VARCHAR(6),
gander CHAR(1),
birthday DATE,
phone VARCHAR(11),
height DECIMAL(3,2),
reg_time DATETIME
)
主键:
在一张表中代表唯一的一条记录
,
不能为空
,
不能重复
约束
:
PRIMARY KEY
设置主键约束
NOT NULL
不能为空约束
UNIQUE
唯一性约束
检查约束 设置条件
外键约束
主键自动增长
,
设置为自动增长时
,
只能为整数类型
AUTO_INCREMENT
默认值
DEFAULT default_value
字段注释
:
-- 创建表
CREATE TABLE student(
number INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(6) NOT NULL,
gander CHAR(1),
birthday DATE,
phone VARCHAR(11) NOT NULL UNIQUE,
height DECIMAL(3,2) CHECK(height<3),
reg_time DATETIME
)
创建表
-- 创建表
CREATE TABLE student(
number INT,
NAME VARCHAR(6),
gander CHAR(1),
birthday DATE,
phone VARCHAR(11),
height DECIMAL(3,2),
reg_time DATETIME
)
删除表
-- 删除表
DROP TABLE student
插入数据
INSERT INTO epmloyee SET NAME='王五',gender='男',age=25,money=3000
INSERT INTO epmloyee SET NAME='李明',gender='男',age=23,money=2500
INSERT INTO epmloyee SET NAME='王二小',gender='男',age=23,money=2356
INSERT INTO epmloyee SET NAME='陈发',gender='男',age=22,money=3600
INSERT INTO epmloyee SET NAME='小明',gender='男',age=21,money=3100
INSERT INTO epmloyee SET NAME='苏琦',gender='男',age=24,money=2800
INSERT INTO epmloyee SET NAME='王丽',gender='女',age=19,money=1800
INSERT INTO epmloyee SET NAME='李芳',gender='女',age=18,money=1900
修改数据
-- 修改表 添加一个新的一列
ALTER TABLE epmloyee ADD deptid INT
外键约束
-- 外键约束
ALTER TABLE jieyue ADD CONSTRAINT fk_jieyue_people_peopleid FOREIGN KEY(peopleid)REFERENCES people(id)
ALTER TABLE jieyue ADD CONSTRAINT fk_jieyue_book_bookid FOREIGN KEY(bookid)REFERENCES book(id)
ALTER TABLE jieyue ADD CONSTRAINT fk_jieyue_guanliyuan_guanliyuanid FOREIGN KEY(guanliyuanid)REFERENCES guanliyuan(id)
删除数据
DELETE FROM
表名
WHERE
条件
TRUNCATE TABLE
表名
;
清空整张表
DQL-基础查询
查询结果处理:
特定列查询
:select column1,column2 from table
全部列查询
: select * from table
算数运算符
:+ - * /
排除重复行
: select distinct column1,column2 from table
查询函数:
select
函数
; /
例如
version()
-- leghth(列名) 以字节为单位
SELECT LENGTH(NAME),gender FROM student
-- CHAR_LENGTH(列名) 以字符为单位
SELECT CHAR_LENGTH(NAME),gender FROM student
-- concat(str1,str2...)链接多个字符串 as 别名
SELECT CONCAT(NAME,':',CHAR_LENGTH(NAME))AS NAME,gender FROM student
-- upper()转为大写 lower()转为小写
SELECT UPPER(NAME),LOWER(NAME)FROM student
-- substring(字符串,开始位置,截取长度) 开始位置从1开始
SELECT SUBSTRING(NAME,1,2)FROM student
-- instr(字符串,指定的字符) 返回指定字符首次出现的位置 找不到返回0
SELECT INSTR(NAME,'o')FROM student
-- trim(字符串) 默认是去掉字符串前后的空格
SELECT TRIM(NAME),NAME FROM student
-- trim(指定的字符串 from 字符串) 可以去掉前后指定的子串
SELECT TRIM('a' FROM NAME),NAME FROM student
-- 左填充 右填充到指定长度
SELECT LPAD(NAME,5,'a'),RPAD(NAME,5,'b')FROM student
-- replace(列,'old','new') 替换
SELECT REPLACE(NAME,'o','O')FROM student
now()
:返回当前系统日期
+
时间
curdate()
:返回当前系统日期,不包含时间
curtime()
:返回当前时间,不包含日期
-- 日期函数
-- NOW(),年月日 时分秒 系统当前时间
-- CURDATE(),年月日
-- CURTIME() 时分秒
SELECT NOW(),CURDATE(),CURTIME()FROM student
-- year(日期) 将日期格式化为年
SELECT YEAR(birthday),MONTH(birthday)FROM student
-- STR_TO_DATE('2004-3-3','%Y-%m-%d')将字符串日期 格式化为 日期类型
SELECT STR_TO_DATE('2004-3-3','%Y-%m-%d')FROM student
-- DATE FORMAT (birthday,'%Y-%m') 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m')FROM student
-- DATEDIFE(CURDATE(),birthday) 计算两个日期之间的相差的天数
SELECT DATEDIFF(CURDATE(),birthday)FROM student
sum
求和、
avg
平均值、
max
最大值、
min
最小值、
count
计数
(非空)
-- 分组函数 聚合函数(查询多行,返回一行) 统计函数
SELECT SUM(height) FROM student -- 求和
SELECT AVG(height) FROM student -- 平均值
SELECT MAX(height) FROM student -- 最大值
SELECT MIN(height) FROM student -- 最小值
SELECT COUNT(*) FROM student -- 统计个数
模糊查询
LIKE
:是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值
或数值型.
通配符: % 任意多个字符
between and 两者之间,包含临界值;
in 判断某字段的值是否属于in列表中的某一项
IS NULL(为空的)或 IS NOT NULL(不为空的)
-- 条件查询
-- select * from 表名 where 条件
-- and 并且
SELECT * FROM student WHERE gender = '男'AND height>1.80
-- or 多个条件只需要满足一个即可
SELECT * FROM student WHERE gender = '男'OR height>1.80
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '女'
-- like'库%' 模糊匹配
SELECT * FROM student WHERE NAME LIKE '库%'
SELECT * FROM student WHERE height>=1.65 AND height<=1.98
-- BETWEEN 1.65 AND 1.98 两者之间
SELECT * FROM student WHERE height BETWEEN 1.65 AND 1.98
-- in(值1,值2,值n) 在给定的值中
SELECT * FROM student WHERE height IN(1.78,1.88,1.98)
SELECT * FROM student WHERE height NOT IN(1.78,1.88,1.98)
-- 查询身高为null值的数据
SELECT * FROM student WHERE height IS NULL
SELECT * FROM student WHERE height IS NOT NULL
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序,desc代表的是降序,如果不写,默认是升序
order by子句中可以支持单个字段、多个字段
-- 排序 order by 列 asc(升序) desc(降序)
SELECT * FROM student WHERE number>0 ORDER BY number ASC
SELECT * FROM student WHERE number>0 ORDER BY number DESC
SELECT * FROM student WHERE number>0 ORDER BY height ASC
SELECT * FROM student WHERE number>0 ORDER BY reg_time DESC
数量限制
limit子句:对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
-- 数量限制 实现分页查询 limit 开始位置,每次查询数量
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3
SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 6,3
多表设计_关联查询
数据库设计范式
第二范式就是要有主键,要求其他字段都依赖于主键。
没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。
其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其
他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依
赖于主键,也就成了唯一的
1.第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就 说明该数据库表满足了第一范式。
2. 第二范式就是要有主键,要求其他字段都依赖于主键。
没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。 • 其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,
其他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依 赖于主键,也就成了唯一的
3.第三范式,确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含 已在其它表中包含的非主关键字信息
子查询
SELECT s.num,s.name sname,m.name mname,
GROUP_CONCAT(c.name) cname
FROM student s LEFT JOIN major m ON s.majorid=m.id
LEFT JOIN stu_course sc ON sc.stunumber=s.num
LEFT JOIN course c ON c.id=sc.courseid
GROUP BY s.name,m.name,s.num
-- 标量子查询
SELECT * FROM student WHERE num=(SELECT MAX(num)FROM student)
-- 表子查询
SELECT *FROM (SELECT NAME,num,reg_time rt FROM student)a WHERE a.name='李明'
-- 列子查询
SELECT *FROM student WHERE NAME IN(SELECT NAME FROM student WHERE NAME IN('李明','刘一科'))
-- 3.查询出每个部门工资最高的员工信息
SELECT *FROM
(SELECT deptid,MAX(money) m FROM epmloyee GROUP BY deptid) a
INNER JOIN epmloyee e ON a.deptid=e.deptid AND a.m=e.money
外键约束
-- 外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid)REFERENCES major(id)
约束名规则:
例:FK_ForeignTable_PrimaryTable_On_ForeignColumn
1、当主表中没有对应的记录时,不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录,不能从主表中删除该行
4、删除主表前,先删从表
DROP TABLE stu
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 修改表 为表添加一个列 一对多 多对一
ALTER TABLE student ADD majorid INT
-- 外键 在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空 唯一 检查
-- 外键有两种情况:
-- 1.不加外键约束
-- 可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有关系
-- 2.添加外键约束
-- 添加外键约束后,两张表操作时,不能导致外键列和主键列对应关系不成立
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid)REFERENCES major(id)
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid
-- 学生选课 一个学生至少选择两个课程
-- 课程信息表 多对多关系
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 添加一个学生选课表 学生和课程关系表 放一个学生学号外键,放一个课程外键
CREATE TABLE student_course(
stunumber INT,
courseid INT
)
ALTER TABLE student_course ADD CONSTRAINT fk_student_course_stunumber FOREIGN KEY(stunumber)REFERENCES student(number)
ALTER TABLE student_course ADD CONSTRAINT fk_student_course_courseid FOREIGN KEY(courseid)REFERENCES course(id)