Cathy学Java——数据库_cathy 数据库

数据库概述
数据库(DataBase, DB) :指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。(文件系统)
数据库管理系统( DataBase Management System, DBMS) :指一种 操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统-一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。
数据库:存储、维护和管理数据的集合。
常见的数据库
Oracle 大型的企业数据库领域
DB2 IBM公司
SQL server Microsoft的产品
PostgreSQL 加州大学伯克利分校 教学
MySQL 免费的数据库系统 被广泛应用的中小型应用系统。 2008 年被SUM收购，2009年SUM被Oracle收购
MySQL登录
打开命令行cmd
输入mysql -u root -p,然后密码,
接着输入show databases;显示数据库
接着输入exit; 退出数据库

修改mysql root用户密码
使用mysqladmin命令:
mysqladmin -u root -p旧密码 password新密码
SQL
概述
➢SQL: Structure Query Language。( 结构化查询语言)
➢SQL被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后来被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。
➢各数据库厂商都支持ISO的SQL标准。普通话
➢各数据库厂商在标准的基础上做了自己的扩展。方言
sqI的分类
DDL (Data Definition Language )
数据定义语言，用来定义数据库对象:库、表、列等; CREATE、ALTER、 DROP
DML (Data Manipulation Language)
数据操作语言，用来定义数据库记录(数据) ; INSERT、 UPDATE、 DELETE
DCL (Data Control Language )【DBA来做】
数据控制语言，用来定义访问权限和安全级别;
DQL ( Data Query Language )
数据查询语言，用来查询记录(数据)。SELECT
注意: sql 语句以;结尾
DDL:操作数据库、表、列等
掌握如何使用的关键字: CREATE、ALTER、DROP

DDL（操作数据库、表、列等）

操作数据库
创建

Create database mydb1;
Create database mydb2 character set gbk;
Create database mydb3 character set gbk COLLATE gbk- chinese_ ci;
1
2
3

COLLATE :指排序规则
查询
查看当前数据库服务器中的所有数据库

show databases;
1

查看前面创建的mydb2数据库的定义信息

Show  create database mydb2;
1

删除
删除前面创建的mydb3数据库

Drop database mydb3;
1

修改
查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database mydb2 character set utf8;
1

其他
查看当前使用的数据库

select database();
1

切换数据库

use mydb2;
1

开启\关闭日志文件和查看日志

show variables like '%general_log%';
set global general_log = on;
set global general_log = off;
1
2
3

操作数据库表
语法:
create table表名(
字段1字段类型,
字段2字段类型，
字段n字段类型
);
常用的数据类型

当前数据库中的所有表

SHOW TABLES;
1

查看表的字段信息

DESC employee;
1

增加一个image列

ALTER TABLE emplovee ADD image blob;
1

修改job列长度为60

ALTER TABLE employee MODIFY job varchar(60);
1

删除image列

ALTER TABLE employee DROP image;//一次只 能删除一个列
1

表名改为user

RENAME TABLE employee TO user;
1

查看表格的创建细节

SHOW CREATE TABLE user; 
1

修改表的字符集为gbk

ALTER TABLE user CHARACTER SET gbk;
1

列名name修改为username

ALTER TABLE user CHANGE name username varchar(100);
1

删除表

DROP TABLE user ;
1

DML

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
INSERT、UPDATE、 DELETE
小知识:
在mysql中，字符串类型和日期类型都要用单引号括起来。‘tom’ ‘2015-09-04’ 空值: null
插入操作: INSERT
语法: INSERTINTO 表名(列名1，列名2…) VALUES(列值 1，列值…);
注意:列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
值不要超出列定义的长度。
如果插入空值，请使用null .
插入的日期和字符一样，都使用引号括起来。
修改操作UPDATE

语法: UPDATE 表名SET 列名1=列值1，列名2=列值2。。。WHERE 列名=值
练习
将所有员工薪水修改为5000元。
UPDATE emp SET salary=5000
将姓名为zhangsan的员工薪水修改为3000元。
UPDATE emp SET salary=3000 WHERE name=’zhangsan’;
将姓名为lisi的员工薪水修改为4000元,性别改为女。，
UPDATE emp SET salary=4000,gender='female' WHERE name='lisi';
将wangwu的薪水在原有基础上增加1000元。
UPDATE emp SET salary=salary+1000 WHERE name=' wangwu';
1
2
3
4
5
6
7
8
9
10

删除操作DELETE

语法:
DELETE FROM 表名[WHERE 列名=值]
删除表中名称为’zs’ 的记录。
DELETE FROM emp WHERE name=‘zs’ ;
删除表中所有记录。
DELETE FROM emp; 
使用truncate删除表中记录。
TRUNCATE TABLE emp;
注:
DELETE删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE删除是把表直接DROP掉，然后再创建一一个 同样的新表。删除的数据不能找回。
执行速度比DELETE快。
1
2
3
4
5
6
7
8
9
10
11
12

查询关键字SELECT语法

SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表的名称*/
WHERE codition/*行条件*/
GROUP BY grouping_columns/*对结果分组*/
HAVING condition/*分组后的行条件*/
ORDER BY sorting_coumns/*对结果的排序*/
LIMIT offset_start,row_count/*结果限定*/
这个关键字的顺序不能乱写，就按这个顺序来
1
2
3
4
5
6
7
8

#创建数据库
CREATE TABLE  stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(6)
);

#插入数据一般情况
INSERT INTO  stu(sid,sname,age,gender) VALUES ('30001','刘三姐',20,'female');
#少一个字段的情况
INSERT INTO  stu(sid,sname,gender) VALUES ('30002','刘四姐','female');
#省略表头的插入
INSERT INTO  stu VALUES ('30003','刘二姐',20,'female');

#查询所有的列
SELECT * FROM stu;
#查询指定的列
SELECT sid,sname FROM stu;
#3.查询性别为女，并且年龄50的记录【=】
SELECT* FROM stu WHERE gender='female' and age = 50;
#查询性别为女，并且年龄大于50的记录
SELECT * FROM stu WHERE gender='female' and age >= 50;
#4.查询学号为s_1001，或子排名为lisi的记录【oR】
SELECT * FROM stu WHERE sid='s_1001'OR sname='lisi';
#5.查询学号为S_1001，S_1002，S_1003的记录【in范围内】
SELECT * PROM stu WHERE sid IN ('s_1001','s_1002','s_1003');
#6.查询学号不是S_1001，s_1002，S_1003的记录
select*from stu where sid not in ('s_1001','s_1002','s_1003');
#7.查询年龄为null的记录
select* from stu where age is null;
#8.查询年龄在20到40之间的学生记录
select* from stu where age >= 20 and age <= 40;
select* from stu where age between 20 and 40;
条件查询介绍
条件查询就是在查询时给出WHERE 子句,在WHERE子句中可以使用如下运算符及关键
字:
=、!=、<>、<、<=、>、>=;
BETWEEN...AND;
IN(set);
ISNULL;IS NOT NULL
AND;
OR;
NOT;
#9.查询性别非男的学生记录!=
SELECT *FROM stu where gender = 'female';
SELECT *FROM stu where gender != 'male';
SELECT *FROM stu where gender <> 'male';
#10.查询姓名不为null的学生记录【NOT NULL1】
SELECT * FROM stu WHERE sname is not NULL;
SELECT * FROM stu_WHERE NOT sname ISNULL;

#模糊查询
#当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
#06.视频LIKE模糊查询【_:匹配一个字符】
#查询姓名由5个字母构成的学生记录(5个_)
SELECT *FROM stu WHERE sname LIKE '______':
#查询姓名由5个字母构成，并且第5个字母为"i“的学生记录(4个下划线)
SELECT * FROM stu WHERE sname LIKE '____i':
#查询姓名以"z"开头的学生记录【%匹配n个字符】
SELECT FROM stu WHERE sname LIKE 'z%' ;
#查询姓名中第2个字母为"i"的学生记录
SELECT *FROM stu WHERE sname LIKE '_i%';
#查询姓名中包含“a”字母的学生记录
SELECT *FROM stu WHERE sname LIKE '%a%';
#字段控制查询
#去除重复的记录

#去除重复记求:使用DISTINCT
#查询员工工资
SELECT sal FROM emp;
sELECT DISTINCT sal FROM emp;#去除重复工资
#DISTINCT 不要和多个字段使用，如果使用就无效
SELECT DISTINCT sal FROM emp;#去除重复工资
#查看员工的月薪与提成之和（但是这个查出来是虚表，如果这个sal和comm有一个是空，最后的值就空）
SELECT * , sal+comm FROM emp;
#怎么解决呢就是用IFNULL 这样的话，就把comm中的null转换为0了
SELECT * , sal+IFNULL(comm,0)FROM emp;
给列名添加别名AS
SELECT *,sal+IFNULL (comm,0)AS 'total'FROM emp;
SELECT *,sal+IFNULL(comm,0)AS '总金额'FROM emp;
#查询部分字段【一般开发中不会起中文的别名】
SELECT ename'员工名字'，job '员工职位',sal '月薪', comm'提成' FROM emp;
#排序order by列名asc(默认)升序desc降序
#查询所有学生记录，按年龄升序【由小到大】排序
SELECT FROM stu ORDER BY age;
SELECT * FROM stu ORDER BY age ASC;
#查询所有学生记录，按年龄降序排序
SELECT *FROM stu ORDER BY age DESC;
#查询所有雇员，按月薪降序排序，如果月薪相同时，按编号降序排序
#荒【掌握多个字段排序】
SELECT *FROM emp ORDER BY sal DESC, empno DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

聚合函数

聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;
MAX():计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算;
MIN(）:计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算;
SUM():计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0;
AVG():计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0;

#聚合函数
#查询emp表中记录数COUNT():统计指定列不为NUL的记录行数;
SELECT COUNT(*)FROM emp;
#查询emp表中有佣金的人数
SELECT COUNT (comm)FROM emp;
SELECT COUNT (comm) FROM emp WHERE comm != 0;
#查询emp表中月薪大于2500的人数
SELECT count(*)FROM emp WHERE sal > 2500;
#统计月薪与佣金之和大于两千五的人
SELECT count(*)FROM emp WHERE sal+FNULL(comm,0) >2500;
#查询有佣金的人数，有领导的人数
SELECT COUNT (comm) FROM emp WHERE　mgr IS NOT NULL;
#查询所有雇员月薪和【sum】
SELECT SUM(sal)FROM emp;
#查询所有雇员月薪和，以及所有雇员佣金和:
SELECT SUM(sal), suM(comm)FROM emp;
#查询所有雇员月薪+佣金和
SELECT SUM(sal)+SUM ( comm）'月总支出'FROM emp;
#记住这里下边的语句不能用SUM（sal+somm） 因为这里的somm有NULL值，出现NULL的时候，会导致sal的值不统计了
SELECT SUM(sal + IF NULL(comm,0)) '月总支出' FROM emp;
#统计所有员工的评价工资【AVG求平均函数的】
SELECT AVG (sal)FROM　emp;
#查询最高工资和最低工资
SELECT MAX(sal), MIN(sal)FROM emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

分组查询

#查询员工，按部门编号分组
SELECT * FROM　emp GROUP BY deptno;
#查询每个部门的部门编号和每个部门的工资和:此时求和是按部门求和
SELECT deptno,SUM(sal)FROM emp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门的人数
SELECT deptno,COUNT(*)FROM emp GROUP BY deptno;
#个部门工资大于1500的人数
SELECT deptno,COUNT(*）FROM emp WHERE sal > 1500 GROUP BY deptno;


1
2
3
4
5
6
7
8
9
10
11

HAVING子句

注: having与where的区别:
1.having是在分组后对数据进行过滤.（没有GROUP BY　不能写having）
where是在分组前对数据进行过滤
2.having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数。
WHERE 是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组;而HAVING是对分组后数据的约束。

#查询工资总和大于9000的部门编号以及工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)> 9000;

1
2
3

LIMIT方言

LIMIT用来限定查询结果的起始行，以及总行数。

#LIMIT用来限定查询结果的起始行，以及总行数。查询5行记录，起始行从О开始
SELECT* FROM emp LIMIT 0, 5;
#注意，起始行从0开始，即第一行开始!查询10行记录，起始行从3开始
SELECT*FROM emp LIMIT 3,10;
#LIMIT方言是mysql独有，oracle和 sgL server没有Limit这种用法
--LIMIT 用于分页显示数据
#假设员工表有14条数据，分页显示，每页显示5条数据，总要需要3页显示
#第一页数据当前页数p=1-10，【开始的位置=(p-1)每贡的条数（5）】
SELECT * FROM emp LIMIT 0,5;
#第二页数据
SELECT * FROM emp LIMIT 5,5;
#第三页数据
SELECT * FROM emp LIMIT 10,5;
1
2
3
4
5
6
7
8
9
10
11
12
13

数据的完整性

作用:保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性在创建表时给表中添加约束
完整性的分类：
实体完整性
域完整性
引用完整性

实体完整性

实体:即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用:标识每一行数据不重复。
约束类型:主键约束(primary key）唯一约束(unique)自动增长列(auto_increment)

主键约束（primary key）

特点：数据唯一，且不为NULL

#12视频主键约束（primary key):数据唯一[不能重复]，且不能为null
#一般会给一张表添加—个主键
DROP TABLE student;
CREATE TABLE student (
id int PRIMARY KEY,
name varchar (20)
);
INSERT INTO student VALUES (1, 'mayun');
INSERT INTO student VALUES (NULL,'mayun'); #会报错
#第二种添加主建的方式
CREATE TABLE student (
id int ,
name varchar (20),
PRIMARY KEY(id)
);
#联合主键
CREATE TABLE student (
id int,
name varchar (20),
PRIMARY KEY (id,name),
INSERT INTO student VALUES(2,'mayun')
);
#第三种方式添加主键
CREATE TABLE student (
id int,
name varchar (20)
);
ALTER TABLE student ADD PRIMARY KEY(id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

唯一约束

#3.唯一约束(unique)
CREATE TABLE student (
id int PRIMARY KEY,
name varchar (20)UNIQUE
);
INSERT INTO student VALUES(1, 'mayun');
INSERT INTO student VALUES(1, 'mayun');#主键不能重复
INSERT INTO student VALUES(2, 'mayun');#name字段重复

1
2
3
4
5
6
7
8
9

自动增长列(auto increment)

给主键添加自动增长的数值，列只能是整数类型，但是如果删除之前增长的序号，后面再添加的时候序号不会重新开始，而是会接着被删除的那一列的序号

INSERT INTO student (name) VALUES ('mayun') ;#id没有传值count at row l
INSERT INTO student VALUES('mayun');#Column count doesn 't match value count at row 1
INSERT INTO student (id,name)VALUES('mayun');#Column count doesn 't match value
首先知道列名可以省略，然后这个前提是，插入的值和列名对应好，如果不对应好就要，采取id自增的形式了
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (20)UNIQUE);
INSERT INTO student (name) VALUES ('mayun');#这里就可以不用写id了
注：一定要符合整数列，而且增长只会，一直增不会返回

还要注意这个，自动添加是自动增长的，如果添加就会一直增长，不会补全原来的

1
2
3
4
5
6
7
8
9
10
11
12

域完整性

非空约束 NOT NULL

-- 非空约束 not null
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL
);
INSERT INTO student (name) VALUES (NULL);  插入失败
1
2
3
4
5
6

默认值约束 default

-- 默认值约束 default
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (20)UNIQUE NOT NULL,
gender varchar (6) DEFAULT 'nan'
);
INSERT INTO student (name,gender) VALUES ('mayun','m');
INSERT INTO student (name,gender) VALUES ('mayun1',NULL);# 不会把空的变成默认的，插入的字段还是null
INSERT INTO student (name) VALUES ('mayun2');
1
2
3
4
5
6
7
8
9

外键约束: FOREIGN KEY

学生成绩的关系
-- 外键约束: FOREIGN KEY
-- 学生表
CREATE TABLE student(
id int primary key AUTO_INCREMENT,
name varchar(50) not null,
gender char(1) default '男'
);
INSERT INTO student (name) VALUES ('张三');
INSERT INTO student (name,gender) VALUES ('李四','女');

-- 成绩表
-- CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 表名(主键) 
create table score(
id int primary key AUTO_INCREMENT,
score int,
name varchar(10) NOT NULL,
sid int,
CONSTRAINT fk_student_score FOREIGN KEY(sid) REFERENCES student(id)
);
INSERT INTO score (score,name,sid) VALUES (90,'java',1);
INSERT INTO score (score,name,sid) VALUES (95,'java',2);
INSERT INTO score (score,name,sid) VALUES (95,'java',3);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

表与表的关系

一对一

人和身份证的关系

一对多

对于学生表来说，就是一对多，对于成绩表来说就是多对一

多对多

老师和学生表
多对多要靠中间表来连接

多表查询

多表查询有如下几种:

合并结果集：UNION 、 UNION ALL
连接查询
内连接[INNER]JOIN ON
外连接OUTER JOIN ON
左外连接LEFT [OUTER]JOIN
右外连接 RIGHT [OUTER]JOIN
全外连接**（MySQL 不支持）**FULL JOIN
自然连接―NATURALJOIN
子查询

合并结果集

作用:合并结果集就是把两个select语句的查询结果合并到一起!
合并结果集有两种方式:

UNION:去除重复记录，例如:SELECT *FROM t1 UNION SELECT*FROM t2;
UNION ALL:不去除重复记录，例如:SELECT*FROM t1 UNION ALL SELECT * FROM t2;
1
2

要求:被合并的两个结果:列数、列类型必须相同

#18UNION，UNION ALL合并结果使用
1.如果使用UNION，操作的表必须要有相同的列，并且每个列类型都是一样
CREATE TABLE employee china(
id int,
name varchar (50)
);
CREATE TABLE employee usa (
id int,
name varchar (50)
);
INSERT，INTO employee_usa VALUES (1, 'michal');
INSERT INTO employee_usa VALUES (2, 'lucy');
INSERT INTO employee_usa VALUES (3, 'anmy');
INSERT INTO employee_china VALUES (1，'马云');
INSERT INTO employee_china VALUES (2，'郭永峰');
INSERT INTO employee_china VALUES (3,'马化腾');
INSERT INTO employee _usa VALUES (4, 'vincent');
INSERT INTO employee_china VALUES (4, 'vincent');

查询一下两个表
SELECT  * FROM employee_china;
SELECT *  FROM employee_usa;
现在要求用查询两个表同时结合成一个数据表
# 合并两张表
UNION去除重复
SELECT *FROM employee china UNION SELECT* FROM employee_usa;
UNION ALL不除重复
SELECT* FROM employee china UNION ALL SELECT * FROM employee_usa;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

连接查询（重要）

内连接

#01.视频查询-内连接INNER，JOIN
SELECT * FROM employee e , department d WHERE e.depno = d.id;
SELECT e.id,e.name,e.depno, d.name FROM employee e , department d WHERE e.depno
#以后查询时，需要什么数据，就搜索什么数据，不把多余的列显示，提高性能
#上面的写法就是内连接，理解成MysQ方言，也就是在其它数据不一定能这样写，Oracle
1
2
3
4
5

标准的内连接

# 标准的内连接
SELECT * FROM employee e INNER JOIN department d WHERE e.depno = d.id;
# 在标准的内连接之中还可以对where条件换一种关键字的写法

sELECT* FROM employee e INNERJOIN department d oN e.depno = d.id;#工作中用这种写法
# 去除重复列
SELECT e.id,e.name,e.depno,d.name FROM employee e INNER JoIN department d ON e.depno = d.id;

1
2
3
4
5
6
7
8

外连接（左连接、右连接）

外连接的特点:查询出的结果存在不满足条件的可能。

外连接
INSERT INTO department VALUE(10005,'保洁部');
INSERT INTO employee (id,name)VALUES(12,'ela' );



1
2
3
4
5
6

左连接

是先查询出左表(即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

#左连接[LEFT JOIN]==左外连接[LEFT OUTER JOIN]:下面两种写法等价
--左连接的特性:左右表条件不满足时，左表显示数据，右表显示数据为NULL
--右连接RIGHT JOIN=RIGHT OUTER JOIN
--右连接的特性:左右表条件不满足时，左表显示NULL,右表显示数据
SELECT e.id，e.name,e.depno ,d.name FROM employee e LEFT OUTER JOIN department d ON e.depno = d.id;
SELECT e.id,e.name,e.depno,d.name FROM employee e LEFT JOIN department d ON e.depno = d.id;
1
2
3
4
5
6

右连接

--右连接 RIGHT JOIN = RIGHT OUTER JOIN【右外连接】
--右连接的特性:左右表条件不满足时，左表显示NULL,右表显示数据
SELECT  e.id,e.name,e.depno,d.name FROM employee e RIGHT OUTER JOIN department d ON e.depno = d.id;
SELECT e.id,e.name,e.depno,d.name FROM employee e RIGHT OUTER JOIN  department d ON e.depno = d.id,

--左连接:右表满足条件的会显示空，而上面内连接，右表不满足条件的就不会显示
1
2
3
4
5
6

#笛卡儿积
#连接查询
SELECT *FROM employee_china, employee_usa;
CREATE TABLE department (
id int,
name varchar (50)
);
INSERT INTO department VALUE(10001,'销售部');
INSERT INTO department VALUE(10002,'咨询部');
INSERT INTO department VALUE(10003,'人事部');
INSERT INTO department VALUE(10004,'我术部');

CREATE TABLE employee (
id int,T
name varchar (50)，
depno int
);
INSERT INTO employee VALUES (1,'tony',10001);
INSERT INTO employee VALUES(2,'lucy',10001);
INSERT INTO employee VALUES(3,'mia',10001);
INSERT INTO employee VALUES(4,'amy',10002);
INSERT INTO employee VALUES (5,'jerry',10002);
INSERT INTO employee VALUES(6,'micheal',10003);
INSERT INTO employee VALUES (7,'lily',10003);
INSERT INTO employee VALUES (8,'elain',10004);
INSERT INTO employee VALUES(9,'ruly',10004);
INSERT INTO employee VALUES (10,'kk',10004);
INSERT INTO employee VALUES(11,'cici',10004);

# 笛卡儿积的过滤
SELECT * FROM employee, department; 
# 加上别名
SELECT FROM employee e,department d WHERE e.depno = d.id;
# 没加别名
SELECT * FROM employee , department WHERE  employee.id = department.id;

# 去掉重复行 和取别名
SELECT e.id '员工ID'，e.name '员工名字',d.id'部门编号',d.name'部门名称'
FROM employee e, department d
WHERE e.depno = d.id;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1t2。
SELECTFROM employee_china,employee_usa;
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

连接心得

连接查询心得:
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

如果连接表没有声明FOREIGN Key，就会出现连接不安全的情况

自连接

有一个这个表，求一个员工的员工编号和姓名，和他的经理编号和姓名

--求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno'员工编号'，e1.ename,e1.mgr'经理编号',e2.ename'经理名字'
EROMemp el,emp e2
WHERE e1.mgr = e2.empno AND ei.empno = 7369;

1
2
3
4
5

自然连接

但是有个前提条件:两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno

#03.自然连接:不用添加主外键的等式，也可以去除笛卡集的数据
--但是有个前提条件:两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno
SELECT * FROM t_employee NATURAL JOIN t_department;
SELECT*FROM t employee e INNER JOIN t department d oe.depno =d.depno;#内连接
SELECT* FROM t_employee INNER JOIN t_department USING(depno);#等效于自然连接

SELECT * FROM t_employee NATURAL LEFT JOIN t_department;#自然左连接效果等于
SELECT* FROM t_employee NATURAL RIGHT JOIN t_department;#自然右连接效果等于

1
2
3
4
5
6
7
8
9

子查询

一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

• 子查询出现的位置:
  where后，作为条为被查询条件的一部分;
  from后，作表;
• 当子查询出现在 where后作为条件时，还可以使用如下关键字:
  any
  all
• 子查询结果集的形式:
  单行单列（用于条件）
  单行多列（用于条件）
  多行单列（用于条件）
  多行多列（用于表）

#整个测试数据
CREATE TABLE depart (
dgpno int PRIMARY KEY,
name varchar (50),#部门名称
location varchar (255),#部门地点
setuptime TIMESTAMP#部门成立时间
);

CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
depno INT,
CONSTRAINT fk_emp_depart FOREIGN KEY(depno)REFERENCES depart(depno)
);

INSERT INTO depart VALUE(30，'销售部'，'广州'，NULL);
INSERT INTO depart VALUE(20,'咨询部'，'深圳'，NULL);
INSERT INTO depart VALUE(10，'技术部'，'深圳'，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,NUL,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,NUL，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，'198411-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER'，'SALESMAN'，7698,'19备1-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,'EORD'，'ANALYST'，7566,'1981-12-03',3000，NULL,20);
INSERT INTO emp values(7934,'MILLER'，'CLERK'，7782,'1982-01-23',1300，NULL，,10);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

子查询的举例

--1.工资高于JONES的员，
--JONES的工资
SELECT sal FROM emp WHERE ename = 'JONES';#单行单列，用于条件
SELECT*FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');

--2.查询与SCOTT同—个部门的员工
--找出SCOTT的部门编号
SELECT depno FROM emp WHERE ename = 'SCOTT';#单行单列
SELECT * FROM emp WHERE depno = (SELECT depno FROM emp WHERE ename = 'SCOTT');

--3.工资高于30号部门J所有人的员工信息
SELECT max(sal)　FROM emp WHERE depno = 30;
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE depno= 30);
-- 第二种写法，用关键字all
SELECT sal FROMemp WHERE depno = 30;#单列多行
SELECT * FROM emp WHERE sal > ALL(SELECTsal FRoM emp wHERE depno.
30);#内部会执行最大值操作
--4.查询工资和与MARTIN（马丁）完全相同的员工信息
SELECT job,sal FROM emp WHERE ename = 'MARTIN';#单行多列
SELECT* FROM emp WHERE(job,sal)IN(SELECT job,sal FROM emp WHERE ename=
'MARTIN') ;
--不算马丁的信息
SELECT * FROM emp
WHERE ename !='MARTIN'
AND(job,sal)IN(SELECT job,sal FROM emp WHERE ename ='MARTIN');
--5.有2个以上直接下属的员工信息【查找经理信息，并且这个经理有2个以上下属的员工信息----也就是找经理】
--查出经理编号、和mar出现的次数
SELECT mgr,COUNT(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY(mgr);
--分组之后的条件要用HAVING
SELECT mgr,COUNT(mgr)  FROM emp WHERE mgr IS NOT NULL GROUP BY(mgr) HAVING COUNT(mgr)>2;


--查出经理编号、和mar出现的次数
SELECT mgr FROM emp GROUP BY(mgr)HAVING COUNT(mgr) >= 2;
SELECT*FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY(mgr)HAVING COUNT(mgr)>= 2);

--显示下属的个数  多行多列
WHERE tl.empno = t2.mgr;
SELECT* FROM emp t1 ,(sELECT mgr,coUNT (mgr)FROM emp cRoP BY(mgr)HAVING CcouNT(mgr)>= 2) t2


--6.查询员工编号为7788的员工名称、员工工资、部门名称、部门地址--多行多列
sELECT e.ename'员工名称',e.sal '工资'，d.name'部门名称',d.location
FROM emp e,depart d
WHERE e.depno = d.depno;
--性能好，
SELECT e.ename '员工名称',e.sal '工资'，d.name '部门名称' ,d.location
FROMemp e,(SELECT depno,name,location FROM depart) d
WHERE e.depno = d.depno;


SELECT depno,name , location FROM depart;
SELECT * FROM depart;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

练习

--求名个部门薪水最高的员工所有信息求每个部门最高的工资
--先求出最高工资
SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno;


SELECT * FROM 
emp t1,(SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno) t2
WHERE ti.sal = t2.maxsal;


1
2
3
4
5
6
7
8
9
10

总结

SELECT  FROM emp;

# 手创建—个学生表
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
ender vARCHAR(6)
);
# 插入数据
INSERT INTO stu(sid,sname,age,gender)VALUES ('30001','刘三姐',20,'female');
INSERT_INTO stu (sid,sname, gender)VALUES (' 30002'，'刘四姐'， 'female' );
#如果是插入所有字段，字段的指定可以省略
INSERT INTO stu VALUES( '30003','刘二姐',20，'female');
SELECT * FROM stu;
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 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);
# 创建员工表
CREATE TABLE emp (
empno INT,
ename VARCHAR( 5o),
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，'A工LEN','SALESMAN',7698,'1981-02-20',1600,300,30);
工NSERT INTO emp values(7521,'WARD','S2工ESNAN',7698,'1981-02-22 ',1250,500,30);
工NSERT INTO emp values(7366,'JONES','MANAEER',7939,'1981-04-02'，2975，NOLL,20);
INSERT INTO emp values(76S4，'2ARDLN','SAEESMAN',7699,'1981-09-28',1250,1400,30)
INSERT INTO emp values(7598,'BLAKE','1ANACER',7839,'1981-0S-02'，2850,NUL，30);
INSERT INTO emp values(7788，'CLARE','MANAGER',7839,'1981-06-02'，2435,NUL，10);
INSERT INTO emp values(7?88,'SCOTT','ANALYST',7586,'1987-04-19',3800, NULL,20);
INSERT INTO emp values(7639,'BING','2RESZDENT',NULL,'1901-11-17',5000,NULL，10);
INSERT INTO emp values(7144，'TURRER','SALESMAN',7899,'1981-09-08',1500,0,30);
INSERT INTO emp values(7976,'ADAMS','CLERE',7788,'1987-O5-23',1100,NULL,20);
INSERT INTO emp values(7900,'TANES','CLERE',7596,'1981-12-23' ,950,NUL,30);
INSERT INTO emp values(7902,'PORE','ANALYST',7566,'1981-12-03',3000,NUOLL,20);
INSERT INTO emp values(7871 'MILLER'，'CLERK',7782,'1982-01-23',1300,NULL,10);
#创建部门表
CREATE TABLE dept (
ceptno 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(4,'OPERATIONS','BOSTON' );
#1.查询表的所有列
select * from  stu;
#2.查询表的部力列
select sid, sname from stu;
#3.查询性别为女，并且午龄50的记录=l
SELECT* FROM stu WHEREgender='female' and age = 50;
#查询性别为女，并且年龄大于50的记录
SELECT* FROM stu WHERE gender='female' and age >= 50;
#4.查询学号为s_1001，或者姓名为lisi的记录【oR或者】
SELECT*FROM stu WHERE sid='s_1001' OR sname='lisi';
#5.查询学号为s_1001，s_1002，S_1003的记录【in范围内】
SELECT * FROM stu WHERE sid IN ('s_1001','s_1002','s_1003');
#文件保存的路径
#C:\Users\10301\Documents \Navicat\MysoL\servers\mysql\mydb2\mysq101.sgl
#C:\Users\用户名\Documents \Wavicat\MysoL\servers\mysgl\数据库\保存的文件名
#6.查询学号不是s_1001，S_1002，S_1003的记录【NoT IN不在某个范围内】
SELECT* FROM stu WHERE sid NOT IN ('S_1001','s_1002','s_1003');
#7.查询年龄为null的记录【字段null】
SELECT *FROMstu WHERE age rs NULL;
#8.查询年龄在20到40之间的学生记录【BETWEEN...AND...在区间】
SELECT * FROM stu WHERE age >= 20 AND age <=40;
SELECT* FROM stu WHERE age BETWEEN 20 AND 40;
#9.查询性别非男的学生记录【!= <>这个是非的意思】
SELECT *FROM stu where gender = 'female';
SELECT * FROM stu where gender != 'male';
SELECT *FROM stu where gender <>'male' ;
#10.查询姓名不为null的学生记录【NOT NULL】
SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE NOT sname IsNULL;
1#06.视频工工KE―模糊查询【_:匹配—个字符】
查询姓名由5个字母构成的学生记录
SELECT*FROM stu WHEREsname LIKE'__';
#查询姓名由5个字母构成，并且第5个字母为"“的学生记录
SELECT *FROMstu WHERE sname LIKE '__i';
#查询姓名以"z"开头的学生记录【%匹配n个字符】
SELECT *
FROM stu
WHERE sname LIKE 'z号';
#查询姓名中第2个字母为"i"的学生记录
SELECT *FROM stu WHERE sname LIKE '_i号';
#查询姓名中包含"a”字母的学生记录
SELECT* FROM stu WHERE sname LIKE '%a%';
#07视频.字段控制查询
#丢除重复记录:使用DIST工NCT
#查询员工工资
SELECT sal,ename FROM emp ;
#DISTINCT不要和多个字段使用，如果使用就无效
SELECT DISTINCT sal FROM emp;去除重复工资
#查看员工的月薪与提成之
SELECT*,sal+comm PROM emp;#在提成没有空的情况下，是没有问题
SELECT *, sal+IFNULL(comm,0)FROM emp;
#给列名添加别名As as也是可以省略
SELECT *,sal+IFNULL (comm,0) AS 'total'FROM emp;
SELECT*,sal+IFNULL(comm,0) AS'总金额' FROM emp;
SELECT *,sal+IFNULL (comm,0)'总金额' FROM emp;
#查询部分字段【—般开发中不会取中文别名】
SELECT ename '员工名字',job '员工职位',sal '月薪', comm '提成' FROM emp;
#08.视频排序order by列名asc(默认）升序desc降序【由大到小】
#查询所有学生记录，按年龄升序【由小到大】排序
SELECT *FROMstu ORDERBY age;
SELECT *FROM stu ORDERBY age ASC;
#查询所有学生记录，接按年龄降序排字
SELECT *FROMstu ORDER，BY age DESC;
#查询所有雇员，按月薪降序排序，如果月薪相同时，按编号降序排序
#【兰握多个字段排序】
SELECT * FROMempORDER BY sal DEsC, empno DESC;
#o9.视预案合函数
#查询emp表中记录数cOUNT():统计列不为NULL的记录行数;
SELECT COUNT(*)FROM emp;
#查询emp表中有佣金的人数
SELECT COUNT (comm)FROM emp;
#SELECT COUNT (comm) FROM emp WHEREcomm!= 0 ;
#09.视频聚合函数
#查询emp表中记录数cOUNT():统计指定列不为NUL的记录行数;
SELECT COUNT(*)FROM emp ;
#查询emp表中有佣金的人数
SELECT COUNT (comm)FROM emp ;
#SELECT COUNT (comm)FROM emp WHERE comm!= 0;
查询emp表中月薪大于2500的人数
SELECT count(*)FROM emp WHERE sal > 2500;
统计月薪与佣金之和大于2500元的人数:
SELECT * FROM emp WHERE sal+IFNULL(comm,0) > 2500;
SELECT count(*)FROM emp WHERE sal+IFNULL(comm,0) >2500;
#查询有佣金的人数，有领导的人数
SELECT COUNT (comm) FROM emp WHERE mgr IS NOT NULL;
#查询所有雇员月薪和【sum】
SELECT SUM(sal)FROM emp;
查询所有雇员月薪和，以及所有雇员佣金和:
SELECT SUM(sal),SUM (comm)FROM emp;
#查间所有雇员月薪+佣金和
SELECT SUM(sal)+SUM(comm)'月总支出' FROM emp;
SELECT SUM(sal + IFNULL(comm,0)) '月总支出'FROM emp;
#充计所有员工平均工资【avg求平均数】
SELECT AVG (sal)FROM emp;
#查询最高工资和最低工资MAX(expr)MIN(expr)
SELECT MAX(sal),MIN (sal)FROM emp;
#查询员工，按部门编号分组
SELECT *FROM emp GROUP BY deptno;
#查询每个部门的部门编号和每个部门的工资和:此时求和是按部门求和
SELECT deptno,SUM(sal)FROM emp BY deptno;
#查询每个部门的部门编号以及每个部门的人数:
SELECT deptno,COUNT(*)FROM emp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno,COUNT(*) FROM emp WHERE sal > 1500 GROUP BY deptno;
#11视频1AV工NG
--ivingwhere的区别:
--1.having是在分组后对数据进行过滤. ctrl +/注释
--where是在分组前对数据进行过滤
--2.having后面可以使用聚合函数（统计函数)
--where后前不可以使用聚合函数
#查询工资总和大于9000的部门编号以及工资和
SELECT deptno,SUM(sal) FROMemp GROUP BY deptno HAVNG SUM(sal) > 9000;
#LIMIT方言是mysql独有，oracle和 sOL server没有Limit这种用法
——LIMIT用于分页显示数据
--假设员工表有14条数据，分页显示，每页显示5条数据，总要需要3页显示
--第一页数据当前页数p=1-10，【开始的位置= (p-1)〉每页的条数（5）】
SELECT*FROM emp LIMIT 0,5;
-第二页数据
SELECT *FROM emp LIMIT 5,5;
-—第—页数据
SELECT *FROM emp LIMIT 10,5;
#12视频主键约束(primary key):数据唯一[不能重复]，且不能为null
#——般会给—张表添力加—个主键
DROP TABLE student;
CREATE TABLE student (
id int PRIMARY KEY,
name varchar (2o)
) ;
INSERT，INTO student VALUES(1, 'mayun' ) ;
INSERT INTO student VALUES (NULL, 'mayun' ) ;
#另一种方式添加主键
CREATE TABLE student (
id int,
name varchar (2o),
PRIMARYKEY(id)
;
#联合主键
CREATE TABLE student (
id int,
name varchar (20),
PRIMARY KEY(id, name));
INSERT INTO student VALUES(1, 'mayun' ) ;
INSERT INTO student VALUES(1, 'mahuteng' ) 
INSERT INTO student VALUES(2, 'mahuteng' ) ;
#第三种方式添加主建
CREATE TABLE student (
id int,
name varchar (20));
ALTER TABLE student ADD PRIMARY KEY(id) ;
A工TERTABLE student ADD PRIMARY KEY(id) ;
#13.唯一约束(unicue)
CREATE TABLE student (
id int PRIMARY KEY,
name varchar (20)UNIOUE
) ;
INSERT INTO student VALUES(1, 'mayun' ) ;
INSERT INTO student VALUES(1, 'mayun');#主键不能重复
INSERT INTO student VALUES(2, 'mayun') ;#name字段重复
#14.视频为自动增长(auto increment)
INSERT INTO student VALUES('mayun ' );#Column courit doesn't match valus count at row 1
INSERT INTO student(id,name)VALUES('mayun' ); #"Column count doesn't match valus count at row 1

INSERT INTO student (name)VALUES ('mayun') ;#id没有传值
INSERT INTO student (id,name)VALUES(2,'ggl');
INSERT INTO student (id,name)VALUES(3,'xd' );
--创建一张自动递增id的表 auto_increment只能用在数字的列
CREATETABLE student (
id int PRIMARY KEY AUT_INCREMENT,
name varchar (20)UNIQUE
) ;
INSERT INTO student (name) VALUES ( 'mayun ' );
INSERT INTO student (name) VALUES ( ' mahuateng' );
INSERT INTO student (name) VALUES ( 'liyanhong' ) ;
INSERT INTO student (name)VALUES (' liugiangdong' );
INSERT INTO student (id,name)VALUES(3,'liyanhong' ) ;


-- 非字约束:not null
CREATE TABLE student (
id int PRIMARYKEY AUTO_INCREMENT，
name varchar (20)UNIQUE NOT NULL
);
INSERT INTO student (name)VALUES(NULL);
--认值约束defaul
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (20)UNIQUE NOT NULL,
gender varchar (6)DEFAULT 'male'
INSERT INTO student (name, gender)VALUES ( 'mayun ' , 'male ' );
INSERT INTO student (name) VALUES ( 'mahuateng' ) ;
INSERT INTO student (name,gender)VALUES ('mahuateng1'，NOLE);#不会把null变成默
#16.外键约束
--step i.仓建两张表
--学生表
CREATE TABLE student (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (20)NOT NULL,
gender char(1)DEFAULT '男',
);
INSERT INTO student (name) VALUES ( ' zhangsan' );
INSERT INTO student (name, gender)VALUES ('lisi'，'女' );
--成绩表
--添力加外键的语法格式:  笔试题
-- CONSTRAINT 外键名称 FOREKEY(外键字段)REFERENCES表名(主键)
CREATE TABLE score (
id int PRIMARY KEY AUTO_INCREMENT,
score int,
name varchar (10)NOTNULL,
sidint,
CONSTRAINT fk_student_score FOREIGN KEY(sid)REFERENCES student(id)
);
INSERT INTO score (score,name,sid)VALUES (90,'数学'，1);
INSERT INTO score (score,name,sid)VALUES (93,'数学'，2);
INSERT INTO score (score,name,sid)VALUES(98,'数学'，3);

#i8UNION，UN工ON A工工合并结果使用
--1.如果使用UNION,操作的表必须要有相同的列，并且每个列类型都是一样
CREATE TABLE employee_china (
id int,
name varchar(50)
) ;
CREATE TABLE empioyee usa (
id int,
name varchar (50));
INSERT INTO employee_usav VALUES (1,'michal' );
INSERT 工NTO employee_usa VALUES (2,'lucy' );
INSERT INTO employee_usa VALUES (3,' anmy' ) ;
INSERT 工NTO employee_china VALUES (1,'永马云');
INSERT INTO employee_china VALUES (2，'郭峰');
INSERT INTO employee_china VALUES (3,'马化腾');
INSERT INTO employee_usa VALUES (4,'vincent' );
INSERT INTO employee_china VALUES (4, 'vincent' );
SELECT * FROM employee_china;
SELECT * FROM employee_usa;
#合并两张表
--UNION丢除重复
SELECT* FROM employee_china UNION SELECT* FROM employee_usa;
--UNIONALL不除重复
SELECT * FROM emplgyee_china UNION ALL SELECT* FROM employee_usa;
#笛卡尔集
#连接查询
SELECT*FROMemployee_china, employee_usa;
CREATETABLE department (
id int,
name varchar (50)
);
#笛卡尔集tl(4）*七2(4)=4*4条记录
影连接查询
SELECT *FROM employee_china, employee_usa;
CREATETABLE department (
id int,
name varchar (50));
INSERT INTO department VALUE(10001,'销售部');
INSERT INTO department VALUE(10002,'咨询部');
INSERT INTO department VALUE(10003,'人事部');
INSERT INTO department VALUE(10004,'技术部');
cREATETABLEemployee (
id int,
name varchar(5o),
depno int
INSERT INTO employee VALUES(1,'tony',10001);
INSERT INTO employee VALUES(2, 'lucy',10001);
INSERT INTO employee VALUES(3,'mia',10001);
INSERT INTO employee VALUES(4, ' amy' ,10002);
INSERT INTO employee VALUES(5,'jerry' ,10002);
INSERT INTO employe VALUES(6,'micheal',10003) ;
INSERT INTO employee VALUES(7,'lily',10003);
INSERT INTO employee VALUES(8,'elain',10004);
INSERT INTO employee VALUES(9,'ruly',10004);
INSERT INTO employee VALUES(10,'kk',10004);
INSERT INTO employee VALUES(11,'cici',10004);
#笛卡尔集过滤
SELECT * FROM employee e, department d WHERE e.depno = d.id;
SELECT* FROM employee , department WHERE employee.depno = department.id;
SELECT e.id'员工ID'，e.name '员工名字',d.id '部门编号',d.name'部门名称1
FROM employee e, department d
WHERE e.depno = d.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341

MySQL中的函数

时间和日期函数

#日期函数
SELECT ADDTIME('2018-03-15 12:00:00','1:12:20');
--运行结果 2018-03-15 13:12:20
1
2
3

创建一个学生表

#varchar的长度最多255,如果字符超过255，用text
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar (10),
entry_date date);
#如果时间格式是date类型，只保留年月日，时分秒会省略
#插入一条数据
INSERT INTO student values (1,'my','2020-10-17 11:30:30');
#在时分秒加上加上一个值
SELECT ADDTIME('2018-03-15 12:00:00','1:12:20');

#在月份、年份、日加上一个值
SELECT DATE_ADD(entry_date,INTERVAL 2 MONTH) FROM student;
SELECT DATE_ADD(entry_date,INTERVAL 2 YEAR)FROM student;
#在月份、年份、日减上一个值
SELECT DATE_SUB(entry date,INTERVAL2 YEAR)FROM student;

#当前时间,用于插入当前时间
SELECT NOW();
INSERT INTO student VALUES(2,'mht',NOW());
SELECT CURRENT_DATE();#获取当前年月日
SELECT CURRENT_TIME();#获取当前时分秒
SELECT CURRENT_TIMESTAMP();#获取当前的年月日时分秒
SELECT DATEDIFF('2018-03-15','2018-03-12');#两个时间的天数差
SELECT DATE(CURRENT_TIMESTAMP ());#用了两个函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

字符串函数

#字符串相关函数
--字符串拼接
SELECT CONCAT ("hu"，'kakxi');
SELECT CONCAT(name,entry_date)FROM student;
--字符串查找
SELECT INSTR('hukaixi','hu');
--UCASE(str）转大写
SELECT UCASE(name) FROM student;
--LCASE(str）转小写
SELECT LCASE(name) FROM student;
--`LEET`(str,ler)从左边截取len长度字符串
SELECT LEFT('ILoveU',5):
--获取名字长度
SELECT　name,LENGTH(name)'长度'FROM　student;
--替换
SELECT　REPLACE('MHt','Ht', 'DM') ;
--STRCMP (stringl ,string2 )比较两个字符串的大小
--结果只有3个 0:相等1:左边字符大-1:右边的字符串
SELECT STRCMP ( 'cc' , 'aa' ) ;
--"SUBSTRING载取字符串
SELECT SUBSTR('ILoveU',2,4);
--LTRIM( string2 )去除左空格
SELECT　LTRIM(' gyf ');
--RTRIM(string2 )丢除右空格
SELECT　RTRIM('gyf ');
--"trim丢除前后空格
SELECT　TRIM(' gyf ');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

数学函数

SELECT ABS (-11);景取色寸直
SELECT BIN(8);#转二进制
--CE工工工NG(number2 )向上取整
SELECT CEILING(14.5);
SELECT CEILING(-14.5);
--FLOOR(number2 )向下取整
SELECT FLOOR(14.5);
--FORMAT(number , decimal_places )保留小数位数
SELECT FORMAT(3.141515,4);
SELECT FORMAT(3.141515,2);
--HEX(DecimalNumber )转卡手六进制
SELECT LCASE(HEX(15));--转换为小写的
--LEAST(number , number2[，..])求最小值
SELECT LEAST(10,4,6) ;
--MOD(numerator , denominator )求余
SELECT MOD(10,4);#10%3 
--RAND([seedl)RAND( [seed]）随机数0~
SELECT RAND();
SELECT RAND(123424);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

来自郭永丰