SQL Java + 数据库 知识点大全

DOL : 数据库定义语言
用来定义数据库对象:库,表,列等
(CREATE ,ALTER, DROP)
DML : 数据库操作语言
用来定义数据库记录
(INSERT, UPDATE, DELETE)
DCL : 数据库控制语言
用来定义访问权限和安全级别
DQL : 数据库查询语言
用来查询记录
(SELECT)
注意: sql语句要以;结尾
sql语句
1, DOL, 操作数据库,表,列等
关键字:CREATE, ALTER, DROR
1.1.1, 创建
CREATE DATABASE mydb1;

创建并设置编码
CREATE DATABASE mydb2 CHARACTER SET gbk;

1.1.2 查询
SHOW DATABASES;

1.1.3 删除
DROP DATABASE mydb2;

1.1.4 修改
查看数据库,并把字符集修改为utf8
ALTER DATABASE mydb1 CHARACTER SET utf8;

1.1.5 其他
查看当前正在使用的数据库
SELECT database();


切换数据库
USE mydb1;


1.2 操作数据库
创建表
语法:
create table 表名(
字段1 字段类型
字段2 字段类型
…
字段n 字段类型
)
常用的字段类型:
int - 整型
double - 浮点 如double(5, 2)表示最多5位,其中必须由2位小数 即:最大值999.99
char - 固定长度字符串类型 如char(10) 对应值’abc ‘
varchar - 可变字符串类型,如varchar(10)对应值’abc’
text - 字符串类型
blob - 字节类型
date - 日期类型 格式:yyyy-MM-dd
time - 时间类型 格式:hh:mm:ss
timestamp - 时间戳类型 格式:yyyy-MM-dd hh:mm:ss 会自动赋值
datetime - 日期时间类型 格式:yyyy-MM-dd hh:mm:ss
CREATE TABLE student ( 
name VARCHAR(10), 
age INT, 
score DOUBLE 
);


查询数据库中的表
SHOW TABLES;


查询表中的字段信息
DESC student;


在表中增加一列num(int 类型)
ALTER TABLE student ADD num INT;


修改num列
ALTER TABLE student MODIFY num DOUBLE;


删除num列, 一次只能删出一列
ALTER TABLE student DROP num;


修改表名
RENAME TABLE student TO stu;


查询表格的创建细节
SHOW CREATE TABLE stu;


修改表格的字符集
ALTER TABLE stu CHARACTER SET gbk;


修改列名
ALTER TABLE stu CHANGE name username VARCHAR(10);


删除表
DROP TABLE stu;


2, DML(重要)
对表中的数据进行 增删改操作
INSERT UPDATE DELETE
(查询表中所有数据
select *from 表名;)
2.1 插入
DESC stu; 
SELECT *FROM stu;


INSERT INTO stu(列名1, 列名2,列名3) VALUES (列值1, 列值2, 列值3);
注意: 列名与列值的类型,个数,顺序要一致
若要不知道内容,可插入空值,null
若要插入的日期等信息,也要用引号括起来
INSERT INTO stu (username, age, score, birthday) VALUES (‘tom’, 11, 55.5, ‘2000-03-22’);


批量插入
INSERT INTO stu VALUES 
(‘sokeaa1’, 12, NULL , ‘1990-03-30’), 
(‘bikebbc1’, 33, 34.4, ‘2001-03-03’), 
(‘jarycccc1’, 22, 33.4, ‘1994-04-06’);


2.2 修改
语法:
UPDATE 表名 SET 要做的具体修改操作 WHERE(可选) 列名=值;
所有的age的信息都改成50
UPDATE stu SET age = 50;


将名字叫’tom’的人的分数改为100
UPDATE stu SET score = 100 WHERE username = ‘tom1’;


将名字叫’tom2’的人的分数改为99.9,年龄改为10
UPDATE stu SET score = 99.9, age = 11 WHERE username = ‘tom2’;


将名字叫’tom3’的分数增加10
UPDATE stu SET score = score + 10 WHERE username = ‘tom3’; 
SELECT *FROM stu;


2.3 删除
DELETE FROM 表名 (WHERE 列名=值);
删除所有数据
DELETE FROM stu;


删除年龄为10的所有记录
DELETE FROM stu WHERE age = 10;


删除表中记录
与DELETE方式的区别:
DELETE删除的是数据,表的结构还在
TRUNCATE是直接把表删掉,再创建一个同样的新表
删除的数据不能找回,速度比DELETE快
TRUNCATE TABLE stu;


3. DQL
数据查询语句(重要)
关键字: select
语法: select 列名 from 表名
(where(行条件) ..
group by(对结果分组) ..
having(分组后的行条件) ..
order by(排序)..)
3.1 基础查询
3.1.2 查询所有
SELECT *FROM stu;


3.1.2 查询指定列
SELECT username,age FROM stu;


3.2 条件查询
条件查询就是在查询时给出where子句,在where子句中可以使用如下运算符以及关键字
= != <> < <= > >=
between…and
in(set)
is null is not null
and
or
not
3.2.1查询年龄为50,名字为’tom1’的信息
SELECT *FROM stu WHERE age = 50 AND username = ‘tom1’;


3.2.2查询分数为55.5或者名字为’tom1’的信息
SELECT *FROM stu WHERE score = 55.5 OR username = ‘tom1’;


3.2.3查询分数为55.5,99.9,158的信息
SELECT *FROM stu WHERE score IN (55.5, 99.9, 100);


3.3.4查询分数不是55.5,99.9,158的信息
SELECT *FROM stu WHERE score NOT IN (55.5, 99.9, 100);


3.3.5查询生日为null的记录
SELECT *FROM stu WHERE birthday IS NULL;


3.3.6查询分数在55.5到100之间的记录
SELECT *FROM stu WHERE score BETWEEN 55.5 AND 100; 
SELECT *FROM stu WHERE score >=55.5 AND score<=100;


3.3.7查询年龄不是50的记录
SELECT *FROM stu WHERE age != 50; 
SELECT *FROM stu WHERE NOT age = 50; 
SELECT *FROM stu WHERE age <> 50;


3.3.8查询生日不为null的记录
SELECT *FROM stu WHERE birthday IS NOT NULL;


3.3 模糊查询
如, 当想查询姓名中包含某个字母的学生时,需要使用模糊查询
关键字like
通配符: _任意一个字符 %任意0~n个字符
3.3.1 查询名字由4个字母组成的学生信息
一个匹配任意一个字母,4个代表4个任意字母组成
SELECT *FROM stu WHERE username LIKE ‘__‘;


3.3.2 查询名字由4个字母组成,并且最后一个字母为1的学生信息
SELECT *FROM stu WHERE username LIKE ‘___1’;


3.3.3 查询姓名以t开头的学生信息
SELECT *FROM stu WHERE username LIKE ‘t___’ ;


3.3.4 查询姓名中第二个字母为o的学生信息
SELECT *FROM stu WHERE username LIKE ‘o_‘;


3.3.5 查询姓名中包含o的学生信息
SELECT *FROM stu WHERE username LIKE ‘%o%’;


3.4 字段控制查询
3.4.1 去除重复记录
SELECT age FROM stu;
SELECT DISTINCT age FROM stu;


3.4.2查看学生年龄和分数之和
注意: 因为相加的两列都是数值类型,所以可以做数学运算
若有一列不是,就会出错
SELECT *,age+score FROM stu;
SELECT *,age+score FROM stu;


3.4.3 判空
ifnull(score, 0)若null就返回0,否则返回score
SELECT *,age+ifnull(score, 0) FROM stu;


3.4.5 别名
给列添加别名
SELECT *,age+ifnull(score,0) AS total FROM stu;


as 可以省略
SELECT *,age+ifnull(score,0)total FROM stu;


3.5 排序
asc(默认)升序
desc 降序
查询所有的学生信息,按年龄升序排序
SELECT *FROM stu ORDER BY age ASC ; 
SELECT *FROM stu ORDER BY age;


查询所有的学生信息,按年龄降序排序
SELECT *FROM stu ORDER BY age DESC ;


查询所有的学生信息,按年龄降序排序,若年龄相同,则按分数升序排序
SELECT *FROM stu ORDER BY age DESC, score ASC ; 
SELECT *FROM stu ORDER BY age DESC, score;


3.6 聚合函数
用来做纵向运算
count() - 统计指定列不为null的记录行数
max() - 计算指定列的最大值,若为字符串使用字符串比较规则
min() - 计算指定列的最小值,若为字符串使用字符串比较规则
sum() - 计算指定列的数值和,若不是数值类型,计算结果为0
avg() - 计算指定列的平均值,若不是数值类型,计算结果为0
3.6.1 count
查询表中的记录数,并给列起别名cnt
SELECT count(*) AS cnt FROM stu;


查询表中有分数的记录数,并给列起别名cnt
null 的不算
SELECT count(score)AS cnt FROM stu;


查询表中有分数大于90的学生人数,并给列起别名cnt
SELECT count(*) AS cnt FROM stu WHERE score > 90;


查询表中分数与年龄之和大于120的学生人数
SELECT count(*)AS cnt FROM stu WHERE ifnull(score,0) + ifnull(age,0)>120;


分别统计有分数的人数,有生日的人数
SELECT count(score), count(birthday) FROM stu ;


3.6.2 sum() 和 avg
纵向求年龄和
SELECT sum(age)FROM stu;


查询所有学生年龄和以及分数和
SELECT sum(age),sum(score)FROM stu;


查询所有学生(年龄+分数)和
SELECT sum(age+ifnull(score,0))FROM stu ;


统计所有学生的平均年龄
SELECT avg(age)FROM stu;


3.6.3 max() 和 min()
查询最高分数和最低分数
SELECT max(score),min(score)FROM stu;


分组查询
当需要分组查询时,需要使用group by 子句
如 查询的分数和,要使用到年龄分组
注意: 凡和聚合函数同时出现的列名,一定要写在group by 之后
查询每个年龄的年龄值和每个年龄的分数和
SELECT age,sum(score)FROM stu GROUP BY age;


查询每个年龄的年龄值和每个年龄的人数
SELECT age,count(*)FROM stu GROUP BY age;


查询每个年龄的年龄值和每个年龄分数大于56的人数
SELECT age,count(*) FROM stu WHERE score>56 GROUP BY age;


having子句
having与where的对比
1.having是在分组之后对数据进行过滤
where是在分组之前对数据进行过滤
2.having后面可以使用聚合函数
where后面不可以使用聚合函数
where 是对分组前进行筛选,如果某行几轮没有满足where子句的条条件 那么这行记录不会参加分组
having是对分组后的数据进行的约束
查询分数总和大于100的年龄以及具体分数
SELECT age,sum(score)FROM stu GROUP BY age HAVING sum(score) > 100;


limit方言
limit方言是用来限定查询结果的起始行,以及总行数的
语法:limit起始行,总行数
查询stu表中前5行记录
SELECT *FROM stu LIMIT 0,5;


查询stu表中3~7行的记录
SELECT *FROM stu LIMIT 2,5;


数据的完整性
作用:保证用户输入的数据保存到数据库中是正确有效的
约束: 主键约束(primary key)
唯一约束(unique)
自动增长列(auto_increment)
主键约束(primary key)
特点:数据唯一,且不能为null
添加主键的第一种方式
CREATE TABLE person( 
id INT PRIMARY KEY , 
name VARCHAR(50)


);


添加主键的第二种方式
比第一种方式的优势在于:可以创建联合主键
CREATE TABLE person( 
id INT , 
name VARCHAR(50), 
PRIMARY KEY (id) 
);


添加主键的第三种方式
CREATE TABLE person( 
id INT , 
name VARCHAR(50)


); 
ALTER TABLE person ADD PRIMARY KEY (id);


唯一约束(unique)
CREATE TABLE person1( 
id INT , 
name VARCHAR(50)UNIQUE


);


主键与唯一约束的区别
1.对于一个表来说,主键只能有一个, 唯一约束可以有多个
2.主键约束的字段不能为null, 唯一约束的字段可以为null
INSERT INTO person (id, name) VALUES (1,’tom1’);


自动增长列(auto_increment)
给主键添加的属性,自动增长的数值,列只能是整数类型
注意:若删除之前增长的序号,后面再添加的序号不会重新开始
而是会接着被删除的那一列的序号
CREATE TABLE teacher( 
id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(50) 
); 
INSERT INTO teacher (name) VALUES (‘tom’); 
SELECT *FROM teacher;


非空约束:not null
CREATE TABLE teacher1( 
id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(50)NOT NULL 
);


非空约束的字段不能插入空值
INSERT INTO teacher1 (name) VALUES (NULL );


默认值约束:default
CREATE TABLE teacher11( 
id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(50)NOT NULL , 
sex VARCHAR(10)DEFAULT ‘man’ 
); 
INSERT INTO teacher11 (name) VALUES (‘tom’); 
INSERT INTO teacher11 (name, sex) VALUES (‘tom’,’woman’); 
INSERT INTO teacher11 (name, sex) VALUES (‘tom’,DEFAULT ); 
SELECT *FROM teacher11;


“` 
SHOW DATABASES ; 
DROP DATABASE mydb; 
CREATE DATABASE mydb; 
SELECT database(); 
SHOW DATABASES ; 
USE mydb; 
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 
) ;


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 emp VALUES(7902,’MILLE’,’CLERK’,7782,’1982-01-23’,1200,NULL,50);


CREATE TABLE dept( 
deptno INT, 
dname VARCHAR(14), 
loc VARCHAR(13) 
); 
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’);


员工表
SELECT * FROM emp;


部门表
SELECT * FROM dept;


多表查询(重要)
多表查询分为:
合并结果集- UNION UNION ALL
连接查询 - 内连接(INNER) JOIN ON
外连接 OUTER JOIN ON
左外连接 LEFT (OUTER) JOIN
右外连接 RIGHT (OUTER) JOIN
自然连接 NATURAL JOIN
子查询
1, 合并结果集
UNION - 去重查询
UNION ALL - 不去重查询
要求: 被合并的两个结果列数和列类型必须相同
SELECT *FROM emp UNION SELECT *FROM dept; 
SELECT *FROM emp UNION ALL SELECT *FROM dept;


2, 连接查询
注意: 此种情况出现的是笛卡尔积,类似排列组合
SELECT *FROM emp,dept;


筛选出我们想要的结果
SELECT *FROM emp,dept WHERE emp.deptno = dept.deptno;


2.1内连接
上面的连接语句就是内连接,只不过不是SQL标准的查询方式
SELECT *FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;


INNER 可以省略
SELECT *FROM emp JOIN dept ON emp.deptno=dept.deptno;


别名
SELECT *FROM emp e JOIN dept d ON e.deptno=d.deptno;


2.2外连接(左连接 右连接)
查询出的结果存在不满足条件的可能
左连接
先查询左表(主), 再查询右表, 右表中满足条件的显示出来,不满足的显示null
SELECT *FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;


右连接
SELECT *FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;


3 自然连接
与内连接一样,会自动匹配相同名字,相同类型的字段
SELECT *FROM emp NATURAL JOIN dept; 
SELECT *FROM emp NATURAL LEFT JOIN dept; 
SELECT *FROM emp NATURAL RIGHT JOIN dept;


子查询 (非常重要)
SELECT *FROM emp;


工资高于JONES的员工
1,找到JONES的工资
SELECT sal FROM emp WHERE ename=’JONES’;


2,找到工资高于2957的人
SELECT *FROM emp WHERE sal>2957;


3,拼在一起
SELECT *FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=’JONES’);


查询与SCOTT同一部门的员工
SELECT *FROM emp WHERE job =(SELECT job FROM emp WHERE ename=’SCOTT’);


工资高于30号部门所有的员工信息
SELECT *FROM emp WHERE sal>(SELECT max(sal) FROM emp WHERE deptno=’30’);


查询工作和工资与MARTIN完全相同的员工信息
SELECT *FROM emp WHERE (job,sal)IN (SELECT job,sal FROM emp WHERE ename=’MARTIN’);


查询有2个以上直接下属的员工信息
SELECT *FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING count(mgr)>1);


查询7369员工编号,姓名,经理编号,经理姓名
SELECT e.empno,e.ename,e.mgr, e1.ename FROM emp e ,emp e1 WHERE e.mgr=e1.empno AND e.empno=’7369’;


表与表之间的关系
一对多
多对一
多对多
一对一
一对多 多对一(常见)
CREATE TABLE stu( 
sid INT PRIMARY KEY , 
name VARCHAR(50), 
sex VARCHAR(10) DEFAULT ‘男’ 
);


CREATE TABLE score( 
id INT, 
score DOUBLE, 
sid INT 
);


多对多
CREATE TABLE teacher( 
tid INT PRIMARY KEY , 
name VARCHAR(50) 
); 
CREATE TABLE student( 
sid INT PRIMARY KEY , 
name VARCHAR(50) 
); 
CREATE TABLE tea_stu_rel_( 
tid INT, 
sid INT 
);


一对一,在一对多的模型下 将关联的两个字段设置为主键
CREATE TABLE stu1( 
sid INT PRIMARY KEY , 
name VARCHAR(50), 
sex VARCHAR(10) DEFAULT ‘男’ 
);


CREATE TABLE score1( 
id INT , 
score DOUBLE, 
sid INT PRIMARY KEY 
);


外键约束: FOREIGN KEY
CREATE TABLE stu( 
sid INT PRIMARY KEY , 
name VARCHAR(50), 
sex VARCHAR(10) DEFAULT ‘男’ 
);


CREATE TABLE score( 
id INT, 
score DOUBLE, 
sid INT, – 外键类型一定要与关联的主键类型一致 
#方式1 
CONSTRAINT fk_score_sid FOREIGN KEY (sid) REFERENCES stu(sid) 
);


方式2
注意:当表中已经存在不合法数据,就无法再添加外键约束
ALTER TABLE score ADD CONSTRAINT fk_score_sid FOREIGN KEY (sid) REFERENCES stu(sid) ;