一、概念
1、什么是数据库
数据库(Database,DB)是长期存储在计算机内的、有组织的、可共享的、统一管理的相关数据的集合。按照字面的理解,数据库就是存放数据的仓库。
2、什么是sql
SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中,例如:MySQL、Oracle、SQL Server等。
sql语法要求
- SQL语句可以单行或多行书写,以分号结尾;
- 可以用空格和缩进来来增强语句的可读性;
- 关键字不区别大小写,建议使用大写;
二、数据定义语言
1、基本操作
-
查看所有数据库:show databases;
-
切换数据库:use mydb1,切换到mydb1数据库;
2、操作数据库
-
创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1;
-
删除数据库:DROP DATABASE [IF EXISTS] mydb1;
-
修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8
3、数据类型
- int:整型
- double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
- decimal:泛型型,在表单线方面使用该类型,因为不会出现精度缺失问题;
- char:固定长度字符串类型;(当输入的字符不够长度时会补空格)
- varchar:固定长度字符串类型(当输入的字符不够长度时不会补空格);
- text:字符串类型;
- blob:字节类型;
- date:日期类型,格式为:yyyy-MM-dd;
- time:时间类型,格式为:hh:mm:ss
- timestamp:时间戳类型;
4、数据查询语言(单表查询)
数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。
建立表stu;表emp;表dept
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(2)
);
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
插入相关数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
1、基本语法:
select 列名 from 表名;
'*' 通配符,表示所有
select * from 表名; //查询所有
比如查询stu表中的编号,名字,年龄
SELECT sid, sname, age FROM stu;
2、条件查询
(1)between 条件1 and 条件2;
(2)and:'与'关系
(3)or:'或'关系
(4)in(集合):查询在'集合'中的记录
(5)not in(集合):查询不在'集合'中的记录
查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu
WHERE sid IN ('S_1001','S_1002','S_1003');
3、模糊查询
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
(1)通配符
% :表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
_ : 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。
(2)关键字: like
查询姓名中包含“a”字母的学生记录
SELECT *
FROM stu
WHERE sname LIKE '%a%';
4、字段查询控制
1)去掉重复记录:
SELECT DISTINCT 列名 FROM 表名;
5、排序:order by子句,默认是升序(ASC).降序是DESC
6、聚合函数:
(1)COUNT():统计指定列不为NULL的记录行数
(2)sum():求和
(3)avg():求平均值
(4)max():最大值
(5)min():最小值
查询emp表中记录数
SELECT COUNT(*) AS cnt FROM emp;
7、分组查询:
(1)group by:分组
(2)having:对分组后的记录进行筛选
查询工资总和大于9000的部门编号以及工资和
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
8、分页查询:用来限定查询结果的起始行,以及总行数。 —>是MySQL特有的关键字
SELECT * FROM emp LIMIT 0, 5;
三、多表连接查询
表连接分为内连接和外连接。 他们之间最主要的区别:内连接仅选出两张表中互相匹配的记录,外连接会选出其他不匹配的记录。
1、笛卡尔积:将两张表的所有字段进行合并,两张表中的记录进行组合
select * from 表1,表2;
2、连接查询(join):基础是笛卡尔积
(1)内连接(inner join):保留关系中符合条件的记录
A、自然连接(NATURAL JOIN):不设置连接条件,根据表中相同的字段进行记录匹配,去掉重复的字段。
SELECT * FROM dept NATURAL join emp;
从结果中可以看出重复字段被删除
B、等值连接(INNER JOIN…ON…):连接的表要有相同的字段,匹配字段值相同的记录
在关键字'ON'后使用"="进行连接条件的设置
SELECT * FROM dept t1 INNER JOIN emp e1 ON t1.deptno = e1.deptno;
结果只有相同(deptno)字段才会显示
(2)外连接(outer join):在表的笛卡尔集中,不仅保留所有匹配的数据记录,而且还会保留部分不匹配的数据记录
A、左外连接(left join):又称左连接,将笛卡尔积中满足条件的记录和左表中不满足条件的记录匹配出来。
(左边全包含,右边只包含满足条件的)
select * from 左表 left join 右表 on 条件;
下面将emp表和dept表做一个左连接
SELECT * FROM dept t1 LEFT JOIN emp e1 ON t1.deptno = e1.deptno;
在dept表中增加两行元素如图
此时dept表中有五个deptno数据,此时emp中只有三个deptno数据
可以看到在dept表中添加数据都能显示出来,而emp表中不能将新增的数据关联上,并显示为null。
B、右外连接(right join):又称右连接,将笛卡尔积中满足条件的记录和右表中不满足条件的记录匹配出来
(右边全包含,左边只包含满足条件的)
select * from 左表 right join 右表 on 条件;
SELECT * FROM dept t1 RIGHT JOIN emp e1 ON t1.deptno = e1.deptno;
在emp 表中增加两行元素如图
此时dept表中有三个deptno数据,此时emp中只有五个deptno数据
可以看到在emp 表中添加数据都能显示出来,而dept表中不能将新增的数据关联上,并显示为null。