MySQL数据库入门笔记

一、数据库概述

1.数据库简介

数据库(DataBase,DB):指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。

数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

注意:通常情况下,经常会用数据库来表示他们使用的数据库软件,这经常会引起混淆,确切的说,数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的。

2.常见数据库管理系统

1) Oracle:目前比较成功的关系型数据库管理系统。运行稳定、功能齐全、性能超群、技术领先。主要应用在大型的企业数据库领域。

2) DB2:IBM的产品。

3) SQL Server:Microsoft的产品。软件界面友好、易学易用,在操作性和交互性方面独树一帜。

4) PostgreSQL:加州大学伯克利分校以教学目的开发的数据库系统,支持关系和面向对象的数据库,属于自由数据库管理系统。

5) MySQL:免费的数据库系统。被广泛用于中小型应用系统。体积小、速度快、总体拥有成本低,开放源代码。2008年被SUN收购,2009年SUN被Oracle收购。

3.数据库的安装和卸载

卸载:

停止MySQL

net stop mysql

1添加删除程序中卸载MySQL

2到安装目录删除MySQL

3删除:C:\Documents and Settings\All Users\Application Data\MySQL

C:\ProgramData\MySQL

4查看注册表:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services

搜索mysql,找到一律干掉!

============================

安装

 

 

 

 

 

 

 

 

  

 

 

 

 

 

 

 

 

4.数据库服务的启动和关闭

启动数据库服务:net start mysql

关闭数据库服务:net stop mysql

5.数据库的连接

登录MySQL:mysql -uroot -proot

退出MySQL:exit | quit

查看数据库:

show databases;

6.命令脚本操作

使用DOS窗口进行登录:

远程连接

    一般在公司开发中,可能会将数据库统一搭建在一台服务器上,所有开发人员共用一个数据库,而不是在自己的电脑中配置一个数据库

    运行命令:

mysql -hip地址 -uroot -p

 

    -h后面写要连接的主机ip地址

    -u后面写连接的用户名

    -p回车后写密码

数据库操作:

    创建数据库

create database 数据库名 charset=utf8;

    删除数据库

drop database 数据库名;

    切换数据库

use 数据库名;

    查看当前选择的数据库

select database();

表操作:

    查看当前数据库中所有表

show tables;

    创建表

auto_increment表示自动增长
create table 表名(列及类型);

    如:

create table students(
    id int auto_increment primary key,
    sname varchar(10) not null
);

    修改表

alter table 表名 add|change|drop 列名 类型;

如:

alter table students add birthday datetime;

    删除表

drop table 表名;

    查看表结构

desc 表名;

    更改表名称

rename table 原表名 to 新表名;

    查看表的创建语句

show create table '表名';

数据操作:

    查询

select * from 表名;

    增加

全列插入:

insert into 表名 values(...);

缺省插入:

insert into 表名(列1,...) values(值1,...);

同时插入多条数据:

insert into 表名 values(...),(...)...;

insert into 表名(列1,...) values(值1,...),(值1,...)...;

主键

主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准。

    修改

update 表名 set 列1=值1,... where 条件

    删除:

delete from 表名 where 条件

     逻辑删除,本质就是修改操作update

alter table students add isdelete bit default 0;

如果需要删除则:

update students isdelete=1 where ...;

备份与恢复:

数据备份:

    进入DOS管理员界面

     进入mysql库目录(配置环境变量了就需要)

cd /mysql

    运行mysqldump命令:

mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;

按提示输入mysql的密码

数据恢复:

    连接mysql,创建数据库

    退出连接,执行如下命令:

mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql

根据提示输入mysql密码

7.图形窗口操作

使用SQLyog或者navicat

8.数据库服务器、数据库和表的关系

所谓安装数据库服务器,只是在机器上装了一个数据库管理程序,这个管理程序可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。

为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体的数据。

数据库服务器、数据库和表的关系如图所示:

9.数据在数据库中的存储方式

 

二、SQL概述

1.什么是SQL?

SQL:Structure Query Language。

SQL被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后来被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。

SQL优点:

  • 不是某个特定数据库供应商专有的语言。几乎所有重要的数据库管理系统都支持SQL。
  • 简单易学,该语言的语句都是由描述性很强的英语单词组成,且这些单词的数目不多。
  • 高度非过程化,即用SQL操作数据库,只需指出“做什么”,无须指明“怎么做”,存取路径的选择和操作的执行由DBMS自动完成。

2.SQL的分类

它所支持的SQL提供以下功能:

  • DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
  • DML(Data Manipulation Language):数据操作语言,用来操作数据库表中的记录(数据);
  • DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
  • DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;

三、创建数据库和数据表

DDL:Data Definition Language

作用:用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。

常用关键字:

CREATE ALTER DROP

创建数据库:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
create_specification:
    [DEFAULT] CHARACTER SET charset_name //指定字符集
  | [DEFAULT] COLLATE collation_name  //指定数据库字符集的比较方式

查看、删除数据库:

显示数据库语句:

SHOW DATABASES;

显示数据库创建语句:

SHOW CREATE DATABASE db_name;

数据库删除语句:

DROP DATABASE  [IF EXISTS]  db_name;

修改、备份、恢复数据库:

ALTER  DATABASE  [IF NOT EXISTS] db_name   
[alter_specification [, alter_specification] ...]
alter_specification:     [DEFAULT] CHARACTER SET charset_name 
|   [DEFAULT] COLLATE collation_name

创建数据表:

注意:创建表前,要先使用use db语句使用库。

CREATE TABLE table_name (
    field1  datatype,
    field2  datatype,
    field3  datatype
)character set 字符集 collate 校对规则;

field:指定列名 datatype:指定列类型

注意:创建表时,要根据需保存的数据创建相应的列,并根据数据的类型定义相应的列类型。例:user对象

id          int                              
name     string
password   string
birthday   date

MySQL常用数据类型

分类

数据类型

说明

数值类型

BIT(M)

TINYINT [UNSIGNED] [ZEROFILL]

BOOL,BOOLEAN

SMALLINT [UNSIGNED] [ZEROFILL]

INT [UNSIGNED] [ZEROFILL]

BIGINT [UNSIGNED] [ZEROFILL]

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

位类型。M指定位数,默认值1,范围1-64

带符号的范围是-128到127。无符号0到255。

使用0或1表示真或假

2的16次方

2的32次方

2的64次方

M指定显示长度,d指定小数位数

表示比float精度更大的小数

文本、二进制类型

CHAR(size) char(20)

VARCHAR(size)  varchar(20)

BLOB   LONGBLOB

TEXT(clob)          LONGTEXT(longclob)

固定长度字符串

可变长度字符串

二进制数据

大文本

时间日期

DATE/DATETIME/TimeStamp

日期类型(YYYY-MM-DD)  (YYYY-MM-DD HH:MM:SS),TimeStamp表示时间戳,它可用于自动记录insert、update操作的时间

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。

CLOB(Character Large Object)

用于存储大量的文本数据

大字段有些特殊,不同数据库处理的方式不一样,大字段的操作常常是以流的方式来处理的。而非一般的字段,一次即可读出数据。

pst.setClob(2, new FileReader("d:\a.txt"););
pst.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("".getBytes()))));

TEXT

  • TINYTEXT:最大长度255(28-1)字符的TEXT
  • TEXT:最大长度65535(216-1)字符的TEXT
  • MEDIUMTEXT:最大长度16,777,215(224-1)字符的TEXT
  • LONGTEXT:最大长度4,294,967,295或4G(232-1)字符的TEXT

BLOB(Binary Large Object)

用于存储大量的二进制数据

大字段有些特殊,不同数据库处理的方式不一样,大字段的操作常常是以流的方式来处理的。而非一般的字段,一次即可读出数据。

pst.setClob(2, new FileInputStream("d:\a.txt"));

BLOB:

  • TINYBLOB:最大长度255(28-1)字节的BLOB
  • BLOB[(M)]:最大长度65535(216-1) 字节的BLOB
  • MEDIUMBLOB:最大长度16,777,215(224-1) 字节的BLOB
  • LONGBLOB:最大长度4,294,967,295或4G(232-1) 字节的BLOB

修改表:

使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.

ALTER TABLE table_name
ADD    (column datatype [DEFAULT expr]
   [, column datatype]...); 
ALTER TABLE table_name
MODIFY    column datatype [DEFAULT expr]
   [, column datatype]...;
ALTER TABLE table_name
DROP       (column);

修改表的名称:

Rename table 表名 to 新表名;

修改表的字符集:

alter table student character set utf8;

四、数据操作

DML:Data Manipulation Language

作用:用于向数据库表中插入、删除、修改数据。

常用关键字:

INSERT UPDATE DELETE

1.insert插入语句

使用 INSERT 语句向表中插入数据。

INSERT INTO    table [(column [, column...])]   VALUES    (value [, value...]); 
  • 插入的数据应与字段的数据类型相同。
  • 数据的大小应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。
  • 在values中列出的数据位置必须与被加入的列的排列位置相对应。
  • 字符和日期型数据应包含在单引号中。
  • 插入空值,不指定或insert into table value(null)

mysql中文乱码

mysql有六处使用了字符集,分别为:client 、connection、database、results、server 、system。

  • client是客户端使用的字符集。
  • connection是连接数据库的字符集设置类型,如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。       
  • database是数据库服务器中某个库使用的字符集设定,如果建库时没有指明,将使用服务器安装时指定的字符集设置。    
  • results是数据库给客户端返回时使用的字符集设定,如果没有指明,使用服务器默认的字符集。       
  • server是服务器安装时指定的默认字符集设定。       
  • system是数据库系统使用的字符集设定。

2.update修改语句

使用 update语句修改表中数据。

UPDATE tbl_name   
SET col_name1=expr1 [, col_name2=expr2 ...]    
[WHERE where_definition];
  • UPDATE语法可以用新值更新原有表行中的各列。
  • SET子句指示要修改哪些列和要给予哪些值。
  • WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。

3.delete删除语句

使用 delete语句删除表中数据。

delete from name  [WHERE where_definition];
  • 如果不使用where子句,将删除表中所有数据。
  • Delete语句不能删除某一列的值(可使用update)
  • 使用delete语句仅删除记录,不删除表本身。如要删除表,使用drop table语句。
  • 同insert和update一样,从一个表中删除记录将引起其它表的参照完整性问题,在修改数据库数据时,头脑中应该始终不要忘记这个潜在的问题。
  • 删除表中数据也可使用TRUNCATE TABLE 语句,它和delete有所不同,参看mysql文档。

delete和truncate的区别:

  • delete 删除表中的数据,表结构还在;删除后的数据可以找回
  • truncate删除是把表直接drop掉,然后再创建一个同样的新表。删除的数据不能找回。执行速度比delete快。

五、数据类型

常用数据类型:

  • 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

六、查询

DQL数据查询语言 (重要)

数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。

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

查询关键字:SELECT 

语法: 

SELECT 列名 FROM表名
【WHERE --> GROUP BY -->HAVING--> ORDER BY】;

语法:

复制代码
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/
复制代码

1.基础查询

1.1 查询所有列

SELECT * FROM stu;

1.2 查询指定列

SELECT sid, sname, age FROM stu;

2.条件查询

2.1 条件查询介绍

条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:

  • =、!=、<>、<、<=、>、>=;
  • BETWEEN…AND;
  • IN(set);
  • IS NULL; IS NOT NULL
  • AND;
  • OR;
  • NOT;

2.2 查询性别为女,并且年龄50的记录

SELECT * FROM stu WHERE gender='female' AND ge<50;

2.3 查询学号为S_1001,或者姓名为liSi的记录

SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi'

2.4 查询学号为S_1001,S_1002,S_1003的记录

SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003'); 

2.5 查询学号不是S_1001,S_1002,S_1003的记录

SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003'); 

2.6 查询年龄为null的记录

SELECT * FROM stu WHERE age IS NULL

2.7 查询年龄在20到40之间的学生记录

SELECT * FROM stu WHERE age>=20 AND age<=40;

或者

SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

2.8 查询性别非男的学生记录

SELECT * FROM stu WHERE gender!='male';

或者

SELECT * FROM stu WHERE gender<>'male';

或者

SELECT * FROM stu WHERE NOT gender='male';

2.9 查询姓名不为null的学生记录

SELECT * FROM stu WHERE sname IS NOT NULL;

或者

SELECT * FROM stu WHERE NOT sname IS NULL;

3.模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

通配符:

  • _ 任意一个字符
  • %:任意0~n个字符

3.1 查询姓名由5个字母构成的学生记录

SELECT * FROM stu WHERE sname LIKE '_____';

模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母,5个“_”表示5个任意字母。

3.2 查询姓名由5个字母构成,并且第5个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '____i'

3.3 查询姓名以“z”开头的学生记录

SELECT * FROM stu WHERE sname LIKE 'z%';

其中“%”匹配0~n个任何字母。

3.4 查询姓名中第2个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '_i%'

3.5 查询姓名中包含“a”字母的学生记录

SELECT * FROM stu WHERE sname LIKE '%a%';

4.字段控制查询

4.1 去除重复记录

去除重复记录(两行或两行以上记录中系列的上的数据都相同),例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT:

SELECT DISTINCT sal FROM emp; 

4.2 查看雇员的月薪与佣金之和

  因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不是数值类型,那么会出错。

SELECT *,sal+comm FROM emp; 

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

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

4.3 给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total:

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时,是可以省略AS关键字的:

SELECT *,sal+IFNULL(comm,0)  total FROM emp;

5.排序

5.1 查询所有学生记录,按年龄升序排序

SELECT * FROM stu ORDER BY sage ASC;

或者

SELECT * FROM stu ORDER BY sage; 

5.2 查询所有学生记录,按年龄降序排序

SELECT * FROM stu ORDER BY age DESC

5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

6.聚合函数

聚合函数是用来做纵向运算的函数:

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

6.1 COUNT

当需要纵向统计时可以使用COUNT()。

  • 查询emp表中记录数:
SELECT COUNT(*) AS cnt FROM emp;
  • 查询emp表中有佣金的人数:
SELECT COUNT(comm) cnt FROM emp;

注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。

  • 查询emp表中月薪大于2500的人数:
SELECT COUNT(*) FROM emp WHERE sal > 2500;
  •  统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
  • 查询有佣金的人数,有领导的人数:
SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2 SUM和AVG

当需要纵向求和时使用sum()函数。

  • 查询所有雇员月薪和:
SELECT SUM(sal) FROM emp;
  • 查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal), SUM(comm) FROM emp;
  • 查询所有雇员月薪+佣金和:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
  •  统计所有员工平均工资:
SELECT AVG(sal) FROM emp;

6.3 MAX和MIN

  • 查询最高工资和最低工资:
SELECT MAX(sal), MIN(sal) FROM emp;

7.分组查询

当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部门来分组。

注:凡和聚合函数同时出现的列名,一定要写在group by 之后

7.1 分组查询

  • 查询每个部门的部门编号和每个部门的工资和:
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;

7.2 HAVING子句

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

注:having与where的区别:

1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤。

2.having后面可以使用聚合函数(统计函数),where后面不可以使用聚合函数。

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

8.limit方言

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

8.1 查询5行记录,起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意,起始行从0开始,即第一行开始!

8.2 查询10行记录,起始行从3开始

SELECT * FROM emp LIMIT 3, 10;

8.3 分页查询

如果一页记录为10条,希望查看第3页记录应该怎么查呢?

  • 第一页记录起始行为0,一共查询10行;
  • 第二页记录起始行为10,一共查询10行;
  • 第三页记录起始行为20,一共查询10行;

8.3 查询代码的书写顺序和执行顺序

  • 查询语句书写顺序:
    selectfrom- where- group by- having- order by-limit
  • 查询语句执行顺序:
    from - where -group by - having - select - order by-limit

七、数据完整性

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

确保数据的完整性 = 在创建表时给表中添加约束

完整性分类

  • 实体完整性
  • 域完整性
  • 引用完整性

1.实体完整性

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

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

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

1.1主键约束(primary key)

特点:数据唯一,且不能为null

例:

第一种添加方式:

CREATE TABLE student(
    id int primary key,
    name varchar(50)
);

第一种添加方式:此种方式优势在于,可以创建联合主键

CREATE TABLE student(
    id int,
    name varchar(50),
    primary key(id)
);
CREATE TABLE student(
    id int,
    name varchar(50),
    primary key(id,name)
);

第三种添加方式:

CREATE TABLE student(
    id int,
    name varchar(50)
);
ALTER TABLE student ADD  PRIMARY KEY (id); 

1.2唯一约束(unique):

CREATE TABLE student(
    id int primary key,
    name varchar(50) unique
);

1.3自动增长列(auto_increment)

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

CREATE TABLE student(
    id int primary key auto_increment,
    name varchar(50)
);
INSERT INTO student(name) values(‘tom’);

2.域完整性

域完整性的作用:限制此单元格的数据正确,不对照此列的其它单元格比较

域代表当前单元格

域完整性约束:数据类型 非空约束(not null) 默认值约束(default)

Check约束(mysql不支持) check();

1.1 数据类型:(数值类型、日期类型、字符串类型)

1.2 非空约束:not null

CREATE TABLE student(
    id int pirmary key,
    name varchar(50) not null,
    sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);

1.3 默认值约束 default

CREATE TABLE student(
    id int pirmary key,
    name varchar(50) not null,
    sex varchar(10) default ‘男’
);
insert into student1 values(1,'tom','');
insert into student1 values(2,'jerry',default);

3.引用完整性

外键约束:FOREIGN KEY

例:

CREATE TABLE student(
    sid int pirmary key,
    name varchar(50) not null,
    sex varchar(10) default ‘男’
);
create table score(
    id int,
    score int,
    sid int , -- 外键列的数据类型一定要与主键的类型一致
    CONSTRAINT fk_score_sid foreign key (sid) references student(id)
);

第二种添加外键方式。

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

八、表与表之间的关系

  • 一对一:例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。设计从表可以有两种方案:一对多(多对一):最为常见的就是一对多!一对多和多对一,这是从哪个角度去看得出来的。t_user和t_section的关系,从t_user来看就是一对多,而从t_section的角度来看就是多对一!这种情况都是在多方创建外键!
    •   在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束;
    •   给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。
  • 多对多:例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。

多表查询有如下几种:

  • 合并结果集;UNION 、  UNION ALL
  • 连接查询子查询
    •   内连接  [INNER] JOIN  ON
    •   外连接  OUTER JOIN ON
      •     左外连接 LEFT [OUTER] JOIN
      •     右外连接 RIGHT [OUTER] JOIN
      •     全外连接(MySQL不支持)FULL JOIN
    •   自然连接  NATURAL JOIN

1.合并结果集

  1. 作用:合并结果集就是把两个select语句的查询结果合并到一起!
  2. 合并结果集有两种方式:
  • 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;

上面查询结果会把两张表的所有列都查询出来,也许你不需要那么多列,这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname 
FROM emp,dept 
WHERE emp.deptno=dept.deptno;

还可以为表指定别名,然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname 
FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno;

2.1 内连接

上面的连接语句就是内连接,但它不是SQL标准中的查询方式,可以理解为方言!SQL标准的内连接为:

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

内连接的特点:查询结果必须满足条件。例如我们向emp表中插入一条记录:

其中deptno为50,而在dept表中只有10、20、30、40部门,那么上面的查询结果中就不会出现“张三”这条记录,因为它不能满足e.deptno=d.deptno这个条件。

2.2 外连接(左连接、右连接)

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

左连接:

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

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

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

2.3 右连接

右连接就是先把右表中所有记录都查询出来,然后左表满足条件的显示,不满足显示NULL。例如在dept表中的40部门并不存在员工,但在右连接中,如果dept表为右表,那么还是会查出40部门,但相应的员工信息为NULL。

SELECT * FROM emp e 
RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

连接查询总结

连接不限与两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件

3.自然连接

大家也都知道,连接查询会产生无用笛卡尔积,我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式,它会自动找到这一等式:

  • 两张连接的表中名称和类型完全一致的列作为条件,例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到!

当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题!

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后,作表;
  • 当子查询出现在where后作为条件时,还可以使用如下关键字:
    • any
    • all
  • 子查询结果集的形式:
    • 单行单列(用于条件)
    • 单行多列(用于条件)
    • 多行单列(用于条件)
    • 多行多列(用于表)

练习:

1、工资高于JONES的员工。

分析:

查询条件:工资>JONES工资,其中JONES工资需要一条子查询。

第一步:查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

第二步:查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果:

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

2、查询与SCOTT同一个部门的员工。

  • 子查询作为条件
  • 子查询形式为单行单列

3、工资高于30号部门所有人的员工信息

分析:

SELECT * FROM emp WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30); 

查询条件:工资高于30部门所有人工资,其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步:查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步:查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步});

结果:

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);
  • 子查询作为条件
  • 子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

4、查询工作和工资与MARTIN(马丁)完全相同的员工信息

分析:

查询条件:工作和工资与MARTIN完全相同,这是子查询

第一步:查询出MARTIN的工作和工资

SELECT job,sal FROM emp WHERE ename='MARTIN';

第二步:查询出与MARTIN工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步});

结果:

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

5、有2个以上直接下属的员工信息

SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);
  • 子查询作为条件
  • 子查询形式为单行多列

6、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

分析:(无需子查询)

查询列:员工名称、员工工资、部门名称、部门地址

查询表:emp和dept,分析得出,不需要外连接(外连接的特性:某一行(或某些行)记录上会出现一半有值,一半为NULL值)

条件:员工编号为7788

第一步:去除多表,只查一张表,这里去除部门表,只查员工表

SELECT ename, sal FROM emp e WHERE empno=7788;

第二步:让第一步与dept做内连接查询,添加主外键条件去除无用笛卡尔积

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, dept d 
WHERE e.deptno=d.deptno AND empno=7788;

第二步中的dept表表示所有行所有列的一张完整的表,这里可以把dept替换成所有行,但只有dname和loc列的表,这需要子查询。

第三步:查询dept表中dname和loc两列,因为deptno会被作为条件,用来去除无用笛卡尔积,所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步:替换第二步中的dept

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, (SELECT dname,loc,deptno FROM dept) d 
WHERE e.deptno=d.deptno AND e.empno=7788;
  • 子查询作为表
  • 子查询形式为多行多列

5.自连接

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

SELECT e1.empno , e1.ename,e2.mgr,e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369

练习:

求各个部门薪水最高的员工所有信息

select e.* from emp e,
--部门最高工资
(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal;

九、函数

1.字符串函数

复制代码
 1 CHARSET(str)    -- 返回字串字符集
 2 CONCAT(string2 [,... ])        -- 连接字串
 3 INSTR(string, substring)    -- 返回substring在string中出现的位置,没有返回0
 4 UCASE(string2)    -- 转换成大写
 5 LCASE(string2)    -- 转换成小写
 6 LEFT(string2,length)    -- 从string2中的左边起取length个字符
 7 LENGTH(string)    -- string长度
 8 REPLACE(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 9 STRCMP(string1, string2)    -- 逐字符比较两字串大小
10 SUBSTRING(str, position[, length])    -- 从str的position开始,取length个字符
11 LTRIM(string2) RTRIM(string2) trim    -- 去除前端空格或后端空格
复制代码

查看字符的ascii码值ascii(str),str是空串时返回0

select ascii('a');

查看ascii码值对应的字符char(数字)

select char(97);

拼接字符串concat(str1,str2...)

select concat(12,34,'ab');

包含字符个数length(str)

select length('abc');

截取字符串

left(str,len)返回字符串str的左端len个字符
right(str,len)返回字符串str的右端len个字符
substring(str,pos,len)返回字符串str的位置pos起len个字符
select substring('abc123',2,3);

去除空格

复制代码
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
trim([方向 remstr from str)返回从某侧删除remstr后的字符串str,方向词包括both、leading、trailing,表示两侧、左、右
select trim('  bar   ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
复制代码

返回由n个空格字符组成的一个字符串space(n)

select space(10);

替换字符串replace(str,from_str,to_str)

select replace('abc123','123','def');

大小写转换,函数如下

lower(str);
upper(str);
select lower('aBcD');

2.数学函数

复制代码
ABS(number2)    -- 绝对值
BIN(decimal_number)    -- 十进制转二进制
CEILING(number2)    -- 向上取整
CONV(number2, from_base, to_base)    -- 进制转换
FLOOR(number2)    -- 向下取整
FORMAT(number,decimal_places)    -- 保留小数位数
HEX(DecimalNumber)    -- 转十六进制    
LEAST(number, number2[ ,..])    -- 求最小值    
MOD(numerator,denominator)    -- 求余
RAND([seed])    -- RAND([seed])
复制代码

求绝对值abs(n)

select abs(-32);

求m除以n的余数mod(m,n),同运算符%

select mod(10,3);
select 10%3;

地板floor(n),表示不大于n的最大整数

select floor(2.3);

天花板ceiling(n),表示不小于n的最大整数

select ceiling(2.3);

求四舍五入值round(n,d),n表示原数,d表示小数位置,默认为0

select round(1.6);

求x的y次幂pow(x,y)

select pow(2,3);

获取圆周率PI()

select PI();

随机数rand(),值为0-1.0的浮点数

select rand();

还有其它很多三角函数,使用时可以查询文档

3.日期时间函数

复制代码
ADDTIME(date2, time_interval)    -- 将time_interval加到date2
CURRENT_DATE()    -- 当前日期
CURRENT_TIME()    -- 当前时间
CURRENT_TIMESTAMP()    -- 当前时间戳
DATE(datetime)    -- 返回datetime的日期部分
DATE_ADD(date2, INTERVAL d_value d_type)    -- 在date2中加上日期或时间
DATE_SUB(date2, INTERVAL d_value d_type)    -- 在date2上减去一个时间
DATEDIFF(date1, date2)    -- 两个日期差
NOW()    -- 当前时间
YEAR|Month|DATE(datetime)    -- 年月日
复制代码

获取子值,语法如下

复制代码
year(date)返回date的年份(范围在1000到9999)
month(date)返回date中的月份数值
day(date)返回date中的日期数值
hour(time)返回time的小时数(范围是0到23)
minute(time)返回time的分钟数(范围是0到59)
second(time)返回time的秒数(范围是0到59)
select year('2016-12-21');
复制代码

日期计算,使用+-运算符,数字后面的关键字为year、month、day、hour、minute、second

select '2016-12-21'+interval 1 day;

日期格式化date_format(date,format),format参数可用的值如下

  • 获取年%Y,返回4位的整数
  • 获取年%y,返回2位的整数
  • 获取月%m,值为1-12的整数
  • 获取日%d,返回整数
  • 获取时%H,值为0-23的整数
  • 获取时%h,值为1-12的整数
  • 获取分%i,值为0-59的整数
  • 获取秒%s,值为0-59的整数
select date_format('2016-12-21','%Y %m %d');

当前日期current_date()

select current_date();

当前时间current_time()

select current_time();

当前日期时间now()

select now();

十、视图

对于复杂的查询,在多次使用后,维护是一件非常麻烦的事情

解决:定义视图

视图本质就是对查询的一个封装

定义视图

create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

视图的用途就是查询

select * from stuscore;

十一、事务

当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回

使用事务可以完成退回的功能,保证业务逻辑的正确性

事务四大特性(简称ACID) 

  • 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
  • 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
  • 隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
  • 持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障

要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务

查看表的创建语句

show create table students;

修改表的类型

alter table '表名' engine=innodb;

事务语句

  • 开启begin;
  • 提交commit;
  • 回滚rollback;

示例1

步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表

【终端1】

select * from students;

【终端2】

begin;
insert into students(sname) values('张飞');

步骤2:

【终端1】

select * from students;

步骤3:

【终端2】

commit;

【终端1】

select * from students;

示例2

步骤1:打开两个终端,连接mysql,使用同一个数据库,操作同一张表

【终端1】

select * from students;

【终端2】

begin;
insert into students(sname) values('张飞');

步骤2:

【终端1】

select * from students;

步骤3:

【终端2】

rollback;

【终端1】

select * from students;

 

 
posted @   Gordon_FANG  阅读(110)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示