第一部分 数据库之SQL语句

96
孙浩j
2017.11.29 12:09* 字数 5446

注:1.mysql是一种关系型数据库

        2.大小写不敏感

        3.字符串用单引号,若字符串里有单引号,则可以用两个单引号表示一个单引号  ‘assad’’a‘表示 assad'a

一、数据库概述

数据:数据库中存储的基本对象

数据库(DataBase,DB):指长期保存在计算机的存储设备上,有组织可共享的大量数据集合

数据库管理系统(DataBase Management System,DBMS):用于数据定义、操纵、数据库运行管理,建立维护

数据库系统:数据库+数据库管理系统+应用系统+数据管理员


二、数据库的配置与测试

官网下载 mysql 和Navicat for mysql

1.启动和终止mysql

(1)计算机--》管理--》服务--》打开关闭mysql

(2)dos窗口  net  start mysql          net stop mysql

2.进入mysql(默认账号root)

在bin目录里启动dos窗口

mysql -uroot -p密码

3.进入数据库

use 数据库名


三、Mysql数据类型

1、整型

MySQL数据类型含义(有符号)

tinyint(m)1个字节  范围(-128~127)

smallint(m)2个字节  范围(-32768~32767)

int(m)4个字节  范围(-2147483648~2147483647)

bigint(m)8个字节  范围(+-9.22*10的18次方)

2、浮点型(float和double)

float(m,d)单精度浮点型    8位精度(4字节)     m总个数,d小数位

double(m,d)双精度浮点型    16位精度(8字节)    m总个数,d小数位

设一个字段定义为float(5,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。

浮点数有精度损失,比如插入了123.14可能变成123.12

3、定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。

decimal(m,d) 

4、字符串

char(n)固定长度,n最大为255.char(10)不足用空字符补齐(效率高)

varchar(n)可变长度,n最大255,varchar(10)最大为10,  一定要指定长度

text可变长度,最多65535个字符,很少用,

5、日期

data  只表示日期

time  只表示时间

注:1112 mysql识别为00:11:12而11:12mysql识别为11:12:00

YEAR 表示年

datatime 表日期+时间


四、SQL语句

解释性语言,写一句执行一句,不需要整体编译执行

desc 表名 观察表结构

表dual,用来进行表达式运算

起别名  select <列名>[As]别名(别名若有特殊字符加双引号)


分类

DDL(*)(Data DefinitionLanguage):数据定义语言,create、ALTER、 DROP

DML(**)(Data ManipulationLanguage):数据操作语言,insert delete update

DCL(Data Control Language):数据控制语言,grant remove

DQL(*****)(Data Query Language):数据查询语言,select

*注意:sql语句以;结尾


操作数据库(了解)

1.创建

Create database mydb1;

Create database mydb2 character set gbk;

2.查询

查看当前数据库服务器中的所有数据库

Show databases;

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

Show create database mydb2;

3..修改

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

alter database mydb2 character set utf8;

4.删除

Drop database mydb3;

5.其他

查看当前使用的数据库

Select database();

6.切换数据库

Use mydb2;



操作表(重点)

DDL数据定义语言

对表的增删改

create

语法:

create table <表名>(

<列名> 数据类型 [列级完整性约束条件],

<列名> 数据类型 [列级完整性约束条件],

...

[表级完整性约束条件(可同时约束多列)]

);


数据完整性

作用:保证用户输入的数据保存到数据库中是正确的。

完整性的分类:

1.实体完整性:

2.域完整性:

3.引用完整性:


列级约束条件

1.实体完整性

实体:即表中的一行(一条记录)代表一个实体(entity)

实体完整性的作用:标识每一行数据不重复。

约束类型:主键约束(primary key唯一约束(unique)自动增长列(auto_increment)

(1)主键约束(primary key)

注:每个表中要有一个主键,且只能有一个

特点:数据唯一,且不能为null,插入数据的组合中任何一个数据都不能是null

(2)唯一约束(unique):特点:数据不能重复。可null

(3)自动增长列(auto_increment),一个表只能有一个自增列(只能添加到主键约束或唯一约束上)

2.域完整性

域完整性的作用:域代表当前单元格,限制此单元格的数据正确,

域完整性约束:数据类型非空约束(not null)默认值约束(default) check约束(mysql不支持)check(sex='男'orsex='女')

(1)not null 不为空  null可为空

(2)default 默认值        给变量赋默认值

3.引用完整性(参照完整性)

外键约束

列级约束直接声明后添加:deptno int REFERENCES dept(deptno)

被约束的列必须是另一个表中的具有唯一性的一列

注:1.往具有外键约束的字段中添加数据,这个数据必须是另一张表中指定字段中出现的数据

2.被约束的字段和约束的字段不一定要名字相同

3.删除表的时候先删除添加外键约束的表,再删除参照的表

实际开发中很少使用外键约束,而更多使用的是业务逻辑进行判断


表级约束条件

(1)constraint <约束名> unique(列1,列2) 表示组合唯一

(2)primary key(classid,stuid)  联合主键,两个值决定主键值,

(3)CONSTRAINT   外键约束名  foreign key (sid) references student(sid主键));

本表的sid依赖student表中的主键sid


Drop

Drop Table <表名>  有关联表先解除关联再删表


Alter(是针对表结构的更改)

注:alter针对表结构的更改,可以通过图形化工具直接操作,作为了解

1.添加列  

Alter Table <表名> ADD <新列名><数据类型>[完整性约束条件]  

(after 字段名(指定字段后添加)/first(第一列添加))

2.删除列

ALTER TABLE <表名> DROP <列名>

3.修改列数据类型

ALTER TABLE <表名> MODIFY <列名> <数据类型>[约束条件]

4.修改表名

ALTER TABLE <旧表名> RENAME TO <新表名>

5.修改字段名

ALTER TABLE <表名>    CHANGE <旧列名> <新列名> <新数据类型>

6.增补约束

(1)主键  ALTER TABLE <表名> ADD CONSTRAINT <约束名> (约束类_表名_列名)PRIMARY KEY[UNIQUE] <(列名)>

(2)外键 ALTER TABLE <表名> ADD CONSTRAINT <约束名> FOREIGN KEY <(列名)> REFERENCES <表名><(列名)>

(3)默认值 ALTER TABLE <表名> ALTER <列名> SET DEFAULT 默认值

(4)自增列  ALTER  TABLE <表名> MODIFY  COLUMN<列名>  <数据类型>  NOT NULL  <约束(主键或者唯一性约束)>     AUTO_INCREMENT


7.删除约束

1.主键 ALTER TABLE<表名> DROP PRIMARY KEY

2.唯一值 ALTER TABLE <表名> DROP INDEX <列名>

3. 外键 ALTER TABLE <表名> DROP FOREIGN KEY 约束名

4.删除自增长  ALTER TABLE <表名> MODIFY <列名> <数据类型>


DML数据操作语言

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。

INSERT 

1.插入不存在的数据  INSERT INTO <表名> [(列1,列2,列3...)] values (' 值1','值2 ','值3 '),(' 值1','值2 ','值3 ')..

注:插全部数据的时候可以不写列名

2.将查询的数据查到数据表

INSERT INTO <表名> [(列1,列2....)]  SELECT 查询语句


UPDATE(针对值的更改)

UPDATE <表名> SET 列名1=值表达式[,列名2=值表达式].....[WHERE 条件表达式]


DELETE

DELETE FROM <表名> [WHERE 表达式]

例:删除id小于10的数据记录

DELETE 删除的数据 FROM student WHERE ID<10

TRUNCATE删除是把表直接DROP掉,然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

Truncate Table<表名> 清空表数据


DQL数据查询语言

查询返回的结果集是一张虚拟表。

SELECT

实际应用时,查询是不可用select * 并且一定要写条件,因为公司数据很多,不写条件会引起死机

SELECT [ALL|DISTINCT] <列名>,[<列名>...]

FROM <表名或视图名> [表名或视图名]

[WHERE <条件表达式>]

[GROUP BY <列名> [HAVING] <条件表达式>]]

[ORDER BY <列名>[ASC|DESC]]    [LIMIT m,n];

*---->代表所有

DISTINCT:消除查询到的重复结果(若后面跟着多个列,去除的是重复组合)

LIMIT m,n    m表示开始位置,n表示长度(获取查询结果的一部分内容),注:第一行数据为0

执行顺序:FROM   WHERE   GROUP BY   HAVING  SELECT  DISTINCT   ORDER BY  LIMIT


条件查询WHERE 

=、<=>安全等可判断两者是否都为null,!=、<>不等、<、<=、>、>=;

AND 并且  OR或    NOT  非 

BETWEEN 条件1  AND 条件2 表示两者之间     / NOT BETWEEN 条件1  AND 条件2

<列名> in ()在括号里的值取,not in

IS NULL   表示为空   IS NOT NULL 不为空


通配符LIKE(Not Like不包含)

_ 下划线表示一个字符  例: LIKE '孙_'

%表示任意长度          例: LIKE '孙%'     LIKE '%M%'出现过M

[m,n]m到n个长度


聚合函数

sum()   avg()   min()   max() count()

聚合函数可以嵌套   max(avg(sal))  查询平均值最大的一组

注:聚合函数统计的是非NULL的数据,

如果一个列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL:

SELECT *,sal+IFNULL(列名,0) FROM emp;


分组

GROUD BY (根据某个条件对数据进行分组)  虽说是分组但是分组后的数据是无法直接全部打印出来的,强行打印每组只会显示每组的一个数据。(可以使用GROUP_CONCAT(字段)函数来显示分组数据)

GROUD BY可以对两个属性进行分组,这时分的组是以两个属性的组合进行分的组,组和相同为一组。

分组可以理解成为,把你group by后面的字段作为一个整体,输出一条你想得到的一种聚合函数类型的数据,所以分组和聚合函数息息相关。

分组函数的是每一组多行输入,单行输出 这里就解释了  select ename max(sal) from emp  ,因为ename可能是多个,就产生了多行输出,产生了无法匹配的问题

注:出现在查询语句里的字段,如果没出现在聚合函数中,就一定要出现在groud by中不然会出现上述问题

SELECT s_id,count(*) AS total

from fruits

group by s_id with rollup    

注:with rollup表示的是所有count(*)的和,也就是所说的总计!


HAVING(对于聚合函数的统计结果进行筛选)


having与where的区别:

1.having是在分组后对数据进行过滤.

where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)

where后面不可以使用分组函数。

WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。


排序

ORDER BY <列1>[ASC|DESC],[<列1>[ASC|DESC]]   先排列1,列1相同再排列2

DESC倒序,ASC正序(默认)


表与表之间的关系

一对一:例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。设计从表可以有两种方案:

在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束;

给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。

一对多(多对一):最为常见的就是一对多!一对多和多对一,这是从哪个角度去看得出来的。t_user和t_section的关系,从t_user来看就是一对多,而从t_section的角度来看就是多对一!这种情况都是在多方创建外键!

多对多:例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。


多表查询(重点)

多表查询有如下几种:

1.合并结果集;UNION、UNION ALL

2.连接查询

内连接[INNER] JOIN ON

外连接OUTER JOIN ON

左外连接LEFT [OUTER] JOIN

右外连接RIGHT [OUTER] JOIN

自然连接NATURAL JOIN

3.子查询


合并结果集

1.合并结果集

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

合并结果集有两种方式:

UNION:去除重复记录,例如:SELECT* FROM t1 UNION SELECT * FROM t2;

UNION ALL:不去除重复记录,例如:SELECT * FROM t1 UNION ALL SELECT * FROM t2。

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


2.连接查询(重点)

连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。

        连接查询会产生笛卡尔积,假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

        那么多表查询产生这样的结果并不是我们想要的,那么怎么去除重复的,不想要的记录呢,当然是通过条件过滤。通常要查询的多个表之间都存在关联关系,那么就通过关联关系去除笛卡尔积。

         你能想像到emp和dept表连接查询的结果么?emp一共14行记录,dept表一共4行记录,那么连接后查询出的结果是56行记录。也就你只是想在查询emp表的同时,把每个员工的所在部门信息显示出来,那么就需要使用主外键来去除无用信息了。


使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

注:在多表查询中,在使用列时必须指定列所从属的表,例如emp.deptno表示emp表的deptno列。

(1)内连接

分为隐式和显示,显示内连接:将表间的逗号用Inner join表示,where用on表示(一个join跟一个on)

SELECT *

FROM emp e Inner JOIN dept d

ON e.deptno=d.deptno;

特点:查询结果必须满足条件,两个表中都有的数据才会显示出来


自连接:把同一张表当做多张表对待(特殊内连接)

求7369员工编号、姓名、经理编号和经理姓名

同一张表无法控制不同行的mgr和empno相同,如果只查一张表另mgr和empno相等比的是同一行。

所以如果比较条件是同一张表内不同行的数据使用自连接,将一张表拷贝一份

SELECTe1.empno , e1.ename,e2.mgr,e2.ename

FROM emp e1, emp e2

WHERE e1.mgr = e2.empno ANDe1.empno = 7369;

注意:内连接一定要给表起别名,让数据库知道你在调用表名时到底调用的是两张相同表的哪一张


(2)外连接(基础表中的数据必须都出现,另一张没有的用null填充)

左外连接:左边基础表     右外连接:右边基础表

       还用上面的例子其中emp表中“张三”这条记录中,部门编号为50,而dept表中不存在部门编号为50的记录,所以“张三”这条记录,不能满足e.deptno=d.deptno这条件。但在左连接中,因为emp表是左表,所以左表中的记录都会查询出来,即“张三”这条记录也会查出,但相应的右表部分显示NULL。

SELECT <列名>

FROM TABLE1 LEFT | RIGHT JOIN TABLE2

ON TABLE1.列=TABLE2.列

连接后的表是两个表笛卡尔积的结果,注意使用主外键关系去除不需要的记录


(3)自然连接

自动找到主外键关系(自己找到连接条件)

SELECT * FROM emp NATURAL JOIN dept;    //内连接

SELECT * FROM emp NATURAL LEFT JOIN dept;    //左链接

SELECT * FROM emp NATURAL RIGHT JOIN dept;    //右链接


(4)子查询(重点)

一个select语句中包含另一个完整的select语句。

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

求每个组里最大工资的员工

        出现的问题:当薪水只要满足是分组里三组中任意一组工资最大值就可以选出来,也就是说第一组的某个人的薪资可能是第二组的最大值,这时也会被选了出来

解决办法:这是应该将子查询结果根据部门编号和最大工资建一张新表,然后进行连接查询

子查询常出现的位置

where后,作为被查询的一条件的一部分;

from后,作表;

 单行单列(用于条件)

 单行多列(用于条件)

 多行单列(用于条件)

 多行多列(用于表,分组)

>all表示大于所有    >any表示大于任意一个,只要大于一个就行(子查询位于where后)

exists和in 的区别,

exists是先执行主查询然后看主查询的条件是否在子查询中存在

in是先执行子查询,然后看主查询的条件是否在子查询中

注:1.子查询结果集少,主查询结果集多,用in

      2.子查询结果集多,主查询结果集少,用exists

     3 .in一般表示的是某一字段的时在子查询中就进行主查询,而exist表示的是只要子查询查询到了数据就进行主查询


例题:

1.    查询工资最高的人名和薪水:子查询查出最高工资,主查询查出有最高薪水的人名和工资

(1)SELECT NAME,SAL    FROM EMP  WHERE SAL=(SELECT MAX(SAL) FROM EMP)

若不使用子查询:SELETE NAME,MAX(SAL)  这样是不对的,最大数据只有一个,所以这个查询只能查询一个对应的名字,而实际上最大薪水对应的不是一个人

(2)SELECT NAME,SAL    FROM EMP  WHERE SAL>(SELECT MAX(SAL) FROM EMP)

(3)SELECT NAME,SAL    FROM EMP  WHERE SAL in(SELECT MAX(SAL) FROM EMP)

表示sal是子查询查询出来众多值的一个

2.工资高于JONES的员工。

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

3.查询工作和工资与MARTIN(马丁)完全相同的员工信息(重点!!!)

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')

3.有2个以上直接下属的员工信息(当子查询的结果查询的是分组后的结果返回的是多列不可用等号用in)

SELECT * FROM emp WHERE empno IN(

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

4.不用组函数,求薪水最大值(面试题)

思路:使用自连接,查询1表中工资中小于2表中工资的数组组成新表,这是从原表中查询工资不再新表里的数据就是最高工资

或者表1的某个工资值大于等于表2中所有的工资

5.查询字段为null的信息

SELECT * FROM EMP WHERE loc


将查询结果插入到表中

INSERT INTO 表名 (column_list1)

SELECT (column_list2) FROM table_name2 where (condition)

column_list1表示要插入哪些列

column_list2的字段数必须和column_list1相同

Java学习第二阶段
Web note ad 1