您的位置:新葡亰496net > 网络数据库 > 多表查询

多表查询

发布时间:2019-09-22 11:05编辑:网络数据库浏览(81)

    学习mysql必备工具即安装mysql客户端;mysql安装教程在网上有很多,在此处就不在仔细说明;

    一、约束之主键约束

    一、约束之主键约束

    1、数据库

     1 数据库概念(了解)

    1.1 什么是数据库

    数据库就是用来存储和管理数据的仓库!

    数据库存储数据的优先:可存储大量数据;

    l  方便检索;

    l  保持数据的一致性、完整性;

    l  安全,可共享;

    l  通过组合分析,可产生新数据。

     

    1.2 数据库的发展历程

    l  没有数据库,使用磁盘文件存储数据;

    l  层次结构模型数据库;

    l  网状结构模型数据库;

    关系结构[c1] 模型数据库:使用二维表格来存储数据;

    l  关系-对象模型数据库;

     

      MySQL就是关系型数据库!

     

    1.3 常见数据库

    l  Oracle(神喻):甲骨文(最高!);

    l  DB2:IBM;

    l  SQL Server:微软;

    l  Sybase:赛尔斯;

    l  MySQL:甲骨文;

     

    1.4 理解数据库

    l  RDBMS = 管理员(manager) 仓库(database)

    l  database = N个table

    l  table:

    • 表结构:定义表的列名和列类型!
    • 表记录:一行一行的记录!

     

     

    我们现在所说的数据库泛指“关系型数据库管理系统(RDBMS

    • Relational database management system[c2] )”,即“数据库服务器”。

     

    当我们安装了数据库服务器后,就可以在数据库服务器中创建数据库,每个数据库中还可以包含多张表。

     

    数据库表就是一个多行多列的表格。在创建表时,需要指定表的列数,以及列名称,列类型等信息。而不用指定表格的行数,行数是没有上限的。下面是tab_student表的结构:

     

     

    当把表格创建好了之后,就可以向表格中添加数据了。向表格添加数据是以行为单位的!下面是s_student表的记录:

     

    s_id

    s_name

    s_age

    s_sex

    S_1001

    zhangSan

    23

    male

    S_1002

    liSi

    32

    female

    S_1003

    wangWu

    44

    male

     

      大家要学会区分什么是表结构,什么是表记录。

     

    1.5 应用程序与数据库

      应用程序使用数据库完成对数据的存储!

     

     

    1、数据库

     1 数据库概念(了解)

    1.1 什么是数据库

    数据库就是用来存储和管理数据的仓库!

    数据库存储数据的优先:可存储大量数据;

    l  方便检索;

    l  保持数据的一致性、完整性;

    l  安全,可共享;

    l  通过组合分析,可产生新数据。

     

    1.2 数据库的发展历程

    l  没有数据库,使用磁盘文件存储数据;

    l  层次结构模型数据库;

    l  网状结构模型数据库;

    关系结构[c1] 模型数据库:使用二维表格来存储数据;

    l  关系-对象模型数据库;

     

      MySQL就是关系型数据库!

     

    1.3 常见数据库

    l  Oracle(神喻):甲骨文(最高!);

    l  DB2:IBM;

    l  SQL Server:微软;

    l  Sybase:赛尔斯;

    l  MySQL:甲骨文;

     

    1.4 理解数据库

    l  RDBMS = 管理员(manager) 仓库(database)

    l  database = N个table

    l  table:

    • 表结构:定义表的列名和列类型!
    • 表记录:一行一行的记录!

     

     

    我们现在所说的数据库泛指“关系型数据库管理系统(RDBMS

    • Relational database management system[c2] )”,即“数据库服务器”。

     

    当我们安装了数据库服务器后,就可以在数据库服务器中创建数据库,每个数据库中还可以包含多张表。

     

    数据库表就是一个多行多列的表格。在创建表时,需要指定表的列数,以及列名称,列类型等信息。而不用指定表格的行数,行数是没有上限的。下面是tab_student表的结构:

     

     

    当把表格创建好了之后,就可以向表格中添加数据了。向表格添加数据是以行为单位的!下面是s_student表的记录:

     

    s_id

    s_name

    s_age

    s_sex

    S_1001

    zhangSan

    23

    male

    S_1002

    liSi

    32

    female

    S_1003

    wangWu

    44

    male

     

      大家要学会区分什么是表结构,什么是表记录。

     

    1.5 应用程序与数据库

      应用程序使用数据库完成对数据的存储!

     

     

    下面将仔细介绍一下关于SQL语句:

    约束:约束是添加在列上的,用来约束列的。

    约束:约束是添加在列上的,用来约束列的。

    2 安装MySQL数据库

    2.1 安装MySQL

      参考:MySQL安装图解.doc

     

    2.2 MySQL目录结构

    MySQL的数据存储目录为data,data目录通常在C:Documents and SettingsAll UsersApplication DataMySQLMySQL Server 5.1data位置。在data下的每个目录都代表一个数据库。

    MySQL的安装目录下:

    l  bin目录中都是可执行文件;

    l  my.ini文件是MySQL的配置文件;

     

    2 安装MySQL数据库

    2.1 安装MySQL

      参考:MySQL安装图解.doc

     

    2.2 MySQL目录结构

    MySQL的数据存储目录为data,data目录通常在C:Documents and SettingsAll UsersApplication DataMySQLMySQL Server 5.1data位置。在data下的每个目录都代表一个数据库。

    MySQL的安装目录下:

    l  bin目录中都是可执行文件;

    l  my.ini文件是MySQL的配置文件;

     

    SQL语句:结构化查询语言(Structured Query Language)

    1、主键约束(唯一标识):非空、唯一、被引用

    1、主键约束(唯一标识):非空、唯一、被引用

    3 基本命令

    3.1 启动和关闭mysql服务器

    l  启动:net start mysql;

    l  关闭:net stop mysql;

     

    在启动mysql服务后,打开windows任务管理器,会有一个名为mysqld.exe的进程运行,所以mysqld.exe才是MySQL服务器程序。

     

    3.2 客户端登录退出mysql

    在启动MySQL服务器后,我们需要使用管理员用户登录MySQL服务器,然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序:mysql.exe

    l  登录:mysql -u root -p 123 -h localhost;

    • -u:后面的root是用户名,这里使用的是超级管理员root;
    • -p:后面的123是密码,这是在安装MySQL时就已经指定的密码;
    • -h:后面给出的localhost是服务器主机名,它是可以省略的,例如:mysql -u root -p 123;

    l  退出:quit或exit;

     

    在登录成功后,打开windows任务管理器,会有一个名为mysql.exe的进程运行,所以mysql.exe是客户端程序。

     

    3 基本命令

    3.1 启动和关闭mysql服务器

    l  启动:net start mysql;

    l  关闭:net stop mysql;

     

    在启动mysql服务后,打开windows任务管理器,会有一个名为mysqld.exe的进程运行,所以mysqld.exe才是MySQL服务器程序。

     

    3.2 客户端登录退出mysql

    在启动MySQL服务器后,我们需要使用管理员用户登录MySQL服务器,然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序:mysql.exe

    l  登录:mysql -u root -p 123 -h localhost;

    • -u:后面的root是用户名,这里使用的是超级管理员root;
    • -p:后面的123是密码,这是在安装MySQL时就已经指定的密码;
    • -h:后面给出的localhost是服务器主机名,它是可以省略的,例如:mysql -u root -p 123;

    l  退出:quit或exit;

     

    在登录成功后,打开windows任务管理器,会有一个名为mysql.exe的进程运行,所以mysql.exe是客户端程序。

     

    SQL语法

    • 当表的某一列被指定为主键后,该类就不能为空,不能有重复值出现
    • 创建表时指定主键的两种方式:

      CREATE TABLE stu(

      sid  CHAR(6) PRIMARY KEY,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10)
      

      );

      CREATE TABLE stu(

      sid  CHAR(6) ,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10),
      PRIMARY KEY(sid)
      

      );

    • 当表的某一列被指定为主键后,该类就不能为空,不能有重复值出现
    • 创建表时指定主键的两种方式:

      CREATE TABLE stu(

      sid  CHAR(6) PRIMARY KEY,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10)
      

      );

      CREATE TABLE stu(

      sid  CHAR(6) ,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10),
      PRIMARY KEY(sid)
      

      );

    2、SQL语句

     

    2、SQL语句

     

    1. SQL语句可以在单行或多行书写,以分号结尾
    2. 可使用空格和缩进来增强语句的可读性
    3. MySQL不区别大小写,建议使用大写

      指定sid列为主键列,即为sid列添加主键约束

      指定sid列为主键列,即为sid列添加主键约束

    1        SQL概述

    1.1 什么是SQL

    SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中,例如:MySQL、Oracle、SQL Server等。SQ标准(ANSI/ISO)有:

    l  SQL-92:1992年发布的SQL语言标准;

    l  SQL:1999:1999年发布的SQL语言标签;

    l  SQL:2003:2003年发布的SQL语言标签;

     

    这些标准就与JDK的版本一样,在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

    虽然SQL可以用在所有关系型数据库中,但很多数据库还都有标准之后的一些语法,我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言,其它数据库都不支持!当然,Oracle或SQL Server都有自己的方言。

     

    1.2 语法要求

    l  SQL语句可以单行或多行书写,以分号结尾;

    l  可以用空格和缩进来来增强语句的可读性;

    l  关键字不区别大小写,建议使用大写;

     

    1        SQL概述

    1.1 什么是SQL

    SQL(Structured Query Language)是“结构化查询语言”,它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中,例如:MySQL、Oracle、SQL Server等。SQ标准(ANSI/ISO)有:

    l  SQL-92:1992年发布的SQL语言标准;

    l  SQL:1999:1999年发布的SQL语言标签;

    l  SQL:2003:2003年发布的SQL语言标签;

     

    这些标准就与JDK的版本一样,在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

    虽然SQL可以用在所有关系型数据库中,但很多数据库还都有标准之后的一些语法,我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言,其它数据库都不支持!当然,Oracle或SQL Server都有自己的方言。

     

    1.2 语法要求

    l  SQL语句可以单行或多行书写,以分号结尾;

    l  可以用空格和缩进来来增强语句的可读性;

    l  关键字不区别大小写,建议使用大写;

     

    SQL语句分类:

    • 修改表时指定主键:
    • 修改表时指定主键:
    2 分类

    l  DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;

    l  DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);

    l  DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;

    l  DQL(Data Query Language):数据查询语言,用来查询记录(数据)。

     

    2 分类

    l  DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;

    l  DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);

    l  DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;

    l  DQL(Data Query Language):数据查询语言,用来查询记录(数据)。

     

    1. DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
        > 创建、删除、修改:库、表结构!!!
    2. DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);
        > 增、删、改:表记录
    3. DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;
    4. DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
      ddl:数据库或表的结构操作
      dml:对表的记录进行更新(增、删、改)
      dql:对表的记录的查询
      dcl:对用户的创建,及授权!

    ALTER TABLE stu ADD PRIMARY KEY(sid);

    ALTER TABLE stu ADD PRIMARY KEY(sid);

    3 DDL(数据定义语言)

    3.1 基本操作

    l  查看所有数据库名称:SHOW DATABASES; 

    l  切换数据库:USE mydb1,切换到mydb1数据库;

    3.2 操作数据库

    l  创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1;

    创建数据库,例如:CREATE DATABASE mydb1,创建一个名为mydb1的数据库。如果这个数据已经存在,那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1,在名为mydb1的数据库不存在时创建该库,这样可以避免报错。

     

    l  删除数据库:DROP DATABASE [IF EXISTS] mydb1;

    删除数据库,例如:DROP DATABASE mydb1,删除名为mydb1的数据库。如果这个数据库不存在,那么会报错。DROP DATABASE IF EXISTS mydb1,就算mydb1不存在,也不会的报错。

     

    l  修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8

    修改数据库mydb1的编码为utf8。注意,在MySQL中所有的UTF-8编码都不能使用中间的“-”,即UTF-8要书写为UTF8。

     

    新葡亰496net,3.3 数据类型

    MySQL与Java一样,也有数据类型。MySQL中数据类型主要应用在列上。

     

    常用类型:

    l  int:整型

    l  double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;

    l  decimal:泛型型,在表单钱方面使用该类型,因为不会出现精度缺失问题;

    l  char:固定长度字符串类型;

    l  varchar:可变长度字符串类型;

    l  text:字符串类型;

    l  blob:字节类型;

    l  date:日期类型,格式为:yyyy-MM-dd;

    l  time:时间类型,格式为:hh:mm:ss

    l  timestamp:时间戳类型;

     

    3.4 操作表

    l  创建表:

    CREATE TABLE 表名(

      列名 列类型,

      列名 列类型,

      ......

    );

    例如:

    CREATE TABLE stu(

             sid        CHAR(6),

             sname      VARCHAR(20),

             age            INT,

             gender     VARCHAR(10)

    );

     

    再例如:

    CREATE TABLE emp(

             eid             CHAR(6),

             ename     VARCHAR(50),

             age            INT,

             gender     VARCHAR(6),

             birthday  DATE,

             hiredate  DATE,

             salary       DECIMAL(7,2),

             resume    VARCHAR(1000)

    );

     

    l  查看当前数据库中所有表名称:SHOW TABLES; 

    l  查看指定表的创建语句:SHOW CREATE TABLE emp,查看emp表的创建语句;

    l  查看表结构:DESC emp,查看emp表结构;

    l  删除表:DROP TABLE emp,删除emp表;

     

    l  修改表:

    1. 添加列:给stu表添加classname列:

    ALTER TABLE stu ADD (classname varchar(100));

     

    1. 修改列类型:修改stu表的gender列类型为CHAR(2):

    ALTER TABLE stu MODIFY gender CHAR(2);

     

    1. 修改列名:修改stu表的gender列名为sex:

    ALTER TABLE stu change gender sex CHAR(2);

     

    1. 删除列:删除stu表的classname列:

    ALTER TABLE stu DROP classname;

     

    1. 修改表名称:修改stu表名称为student:

    ALTER TABLE stu RENAME TO student;

     

    3 DDL(数据定义语言)

    3.1 基本操作

    l  查看所有数据库名称:SHOW DATABASES; 

    l  切换数据库:USE mydb1,切换到mydb1数据库;

    3.2 操作数据库

    l  创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1;

    创建数据库,例如:CREATE DATABASE mydb1,创建一个名为mydb1的数据库。如果这个数据已经存在,那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1,在名为mydb1的数据库不存在时创建该库,这样可以避免报错。

     

    l  删除数据库:DROP DATABASE [IF EXISTS] mydb1;

    删除数据库,例如:DROP DATABASE mydb1,删除名为mydb1的数据库。如果这个数据库不存在,那么会报错。DROP DATABASE IF EXISTS mydb1,就算mydb1不存在,也不会的报错。

     

    l  修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8

    修改数据库mydb1的编码为utf8。注意,在MySQL中所有的UTF-8编码都不能使用中间的“-”,即UTF-8要书写为UTF8。

     

    3.3 数据类型

    MySQL与Java一样,也有数据类型。MySQL中数据类型主要应用在列上。

     

    常用类型:

    l  int:整型

    l  double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;

    l  decimal:泛型型,在表单钱方面使用该类型,因为不会出现精度缺失问题;

    l  char:固定长度字符串类型;

    l  varchar:可变长度字符串类型;

    l  text:字符串类型;

    l  blob:字节类型;

    l  date:日期类型,格式为:yyyy-MM-dd;

    l  time:时间类型,格式为:hh:mm:ss

    l  timestamp:时间戳类型;

     

    3.4 操作表

    l  创建表:

    CREATE TABLE 表名(

      列名 列类型,

      列名 列类型,

      ......

    );

    例如:

    CREATE TABLE stu(

             sid        CHAR(6),

             sname      VARCHAR(20),

             age            INT,

             gender     VARCHAR(10)

    );

     

    再例如:

    CREATE TABLE emp(

             eid             CHAR(6),

             ename     VARCHAR(50),

             age            INT,

             gender     VARCHAR(6),

             birthday  DATE,

             hiredate  DATE,

             salary       DECIMAL(7,2),

             resume    VARCHAR(1000)

    );

     

    l  查看当前数据库中所有表名称:SHOW TABLES; 

    l  查看指定表的创建语句:SHOW CREATE TABLE emp,查看emp表的创建语句;

    l  查看表结构:DESC emp,查看emp表结构;

    l  删除表:DROP TABLE emp,删除emp表;

     

    l  修改表:

    1. 添加列:给stu表添加classname列:

    ALTER TABLE stu ADD (classname varchar(100));

     

    1. 修改列类型:修改stu表的gender列类型为CHAR(2):

    ALTER TABLE stu MODIFY gender CHAR(2);

     

    1. 修改列名:修改stu表的gender列名为sex:

    ALTER TABLE stu change gender sex CHAR(2);

     

    1. 删除列:删除stu表的classname列:

    ALTER TABLE stu DROP classname;

     

    1. 修改表名称:修改stu表名称为student:

    ALTER TABLE stu RENAME TO student;

     

    了解Sql语句的分类以后,下面我们将仔细总结一下关于DDL的使用:

    • 删除主键:
    • 删除主键:
    4 DML(数据操作语言)

    4.1 插入数据

    语法:

    INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

    INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

    INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');[崔3] 

     

    语法:

    INSERT INTO 表名 VALUES(值1,值2,…)

    因为没有指定要插入的列,表示按创建表时列的顺序插入所有列的值:

    INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

     

      注意:所有字符串数据必须使用单引用!

     

    4.2 修改数据(update)

    语法:

    UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

    UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

    UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

    UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

    UPDATE stu SET gender=’female’ WHERE gender IS NULL

    UPDATE stu SET age=age 1 WHERE sname=’zhaoLiu’;

     

    4.3 删除数据

    语法:

    DELETE FROM 表名 [WHERE 条件]

    DELETE FROM stu WHERE sid=’s_1001’003B

    DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;

    DELETE FROM stu;[崔4] 

     

    语法:

    TRUNCATE TABLE 表名

    TRUNCATE TABLE stu;[崔5] 

     

    虽然TRUNCATE和DELETE都可以删除表的所有记录,但有原理不同。DELETE的效率没有TRUNCATE高!

    TRUNCATE其实属性DDL语句,因为它是先DROP TABLE,再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的,但DELETE删除的记录是可以回滚的(回滚是事务的知识!)。

     

    4 DML(数据操作语言)

    4.1 插入数据

    语法:

    INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

    INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

    INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');[崔3] 

     

    语法:

    INSERT INTO 表名 VALUES(值1,值2,…)

    因为没有指定要插入的列,表示按创建表时列的顺序插入所有列的值:

    INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

     

      注意:所有字符串数据必须使用单引用!

     

    4.2 修改数据(update)

    语法:

    UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

    UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

    UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

    UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

    UPDATE stu SET gender=’female’ WHERE gender IS NULL

    UPDATE stu SET age=age 1 WHERE sname=’zhaoLiu’;

     

    4.3 删除数据

    语法:

    DELETE FROM 表名 [WHERE 条件]

    DELETE FROM stu WHERE sid=’s_1001’003B

    DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;

    DELETE FROM stu;[崔4] 

     

    语法:

    TRUNCATE TABLE 表名

    TRUNCATE TABLE stu;[崔5] 

     

    虽然TRUNCATE和DELETE都可以删除表的所有记录,但有原理不同。DELETE的效率没有TRUNCATE高!

    TRUNCATE其实属性DDL语句,因为它是先DROP TABLE,再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的,但DELETE删除的记录是可以回滚的(回滚是事务的知识!)。

     

    从宏观的层面我们先介绍基于数据库的操作:

    ALTER TABLE stu DROP PRIMARY KEY;

    ALTER TABLE stu DROP PRIMARY KEY;

    5 DCL

    5.1 创建用户

    语法:

    CREATE USER 用户名@地址 IDENTIFIED BY '密码';

    CREATE USER user1@localhost IDENTIFIED BY ‘123’;[崔6] 

    CREATE USER user2@’%’ IDENTIFIED BY ‘123’;[崔7] 

     

    5.2 给用户授权

      语法:

    GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

    GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

    GRANT ALL ON mydb1.* TO user2@localhost;

     

    5.3 撤销授权

      语法:

      REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

    REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

     

    5.4 查看用户权限

    语法:

    SHOW GRANTS FOR 用户名

    SHOW GRANTS FOR user1@localhost;

     

    5.5 删除用户

    语法:

    DROP USER 用户名

    DROP USER user1@localhost;

     

    5.6 修改用户密码

    语法:

    USE mysql;

    UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;

    FLUSH PRIVILEGES;

    UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’;

    FLUSH PRIVILEGES;

     

    5 DCL

    5.1 创建用户

    语法:

    CREATE USER 用户名@地址 IDENTIFIED BY '密码';

    CREATE USER user1@localhost IDENTIFIED BY ‘123’;[崔6] 

    CREATE USER user2@’%’ IDENTIFIED BY ‘123’;[崔7] 

     

    5.2 给用户授权

      语法:

    GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

    GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

    GRANT ALL ON mydb1.* TO user2@localhost;

     

    5.3 撤销授权

      语法:

      REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

    REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

     

    5.4 查看用户权限

    语法:

    SHOW GRANTS FOR 用户名

    SHOW GRANTS FOR user1@localhost;

     

    5.5 删除用户

    语法:

    DROP USER 用户名

    DROP USER user1@localhost;

     

    5.6 修改用户密码

    语法:

    USE mysql;

    UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;

    FLUSH PRIVILEGES;

    UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’;

    FLUSH PRIVILEGES;

     

     *查看所有数据库:SHOW DATABASES
    * 切换(选择要操作的)数据库:USE 数据库名
    * 创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]
    * 删除数据库:DROP DATABASE [IF EXISTS] mydb1
    * 修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8

    2、主键自增长

    2、主键自增长

    3、数据查询语法(DQL)

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

    语法:

    SELECT selection_list /*要查询的列名称*/

      FROM table_list /*要查询的表名称*/

     

      WHERE condition /*行条件*/

     

      GROUP BY grouping_columns /*对结果分组*/

     

      HAVING condition /*分组后的行条件*/

     

      ORDER BY sorting_columns /*对结果分组*/

     

      LIMIT offset_start, row_count /*结果限定*/

     

    创建名:

    l  学生表:stu

    字段名称

    字段类型

    说明

    sid

    char(6)

    学生学号

    sname

    varchar(50)

    学生姓名

    age

    int

    学生年龄

    gender

    varchar(50)

    学生性别

     

    CREATE TABLE stu (

             sid    CHAR(6),

             sname               VARCHAR(50),

             age            INT,

             gender     VARCHAR(50)

    );

    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);

     

    l  雇员表: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 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);

     

    l  部分表:dept

    字段名称

    字段类型

    说明

    deptno

    int

    部分编码

    dname

    varchar(50)

    部分名称

    loc

    varchar(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');

     

    3、数据查询语法(DQL)

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

    语法:

    SELECT selection_list /*要查询的列名称*/

      FROM table_list /*要查询的表名称*/

     

      WHERE condition /*行条件*/

     

      GROUP BY grouping_columns /*对结果分组*/

     

      HAVING condition /*分组后的行条件*/

     

      ORDER BY sorting_columns /*对结果分组*/

     

      LIMIT offset_start, row_count /*结果限定*/

     

    创建名:

    l  学生表:stu

    字段名称

    字段类型

    说明

    sid

    char(6)

    学生学号

    sname

    varchar(50)

    学生姓名

    age

    int

    学生年龄

    gender

    varchar(50)

    学生性别

     

    CREATE TABLE stu (

             sid    CHAR(6),

             sname               VARCHAR(50),

             age            INT,

             gender     VARCHAR(50)

    );

    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);

     

    l  雇员表: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 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);

     

    l  部分表:dept

    字段名称

    字段类型

    说明

    deptno

    int

    部分编码

    dname

    varchar(50)

    部分名称

    loc

    varchar(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');

     

    -- 首先先进行查询数据库,下面是我的数据库
    mysql> show databases;
     -------------------- 
    | Database           |
     -------------------- 
    | information_schema |
    | bookstore          |
    | bos                |
    | category           |
    | chatroom           |
    | crm                |
    | dangdang           |  |
    | maven              |
    | mmall              |
    | mybaits            |
    | mysql              |
    | o2o                |
    | performance_schema |
    | registersystem     |
    | spring6            |
    | spring_day03       |
    | springmvc          |
     -------------------- 
    29 rows in set (0.53 sec)
    
    --创建数据库Sql语句
    mysql> create database if not exists mydb1 charset=utf8;
    Query OK, 1 row affected (0.04 sec)
    
    -- 删除数据库的SQL语句
    mysql> drop database mydb1;
    Query OK, 0 rows affected (0.24 sec)
    
    -- 确认数据库是否删除
    mysql> show databases;
     -------------------- 
    | Database           |
     -------------------- 
    | information_schema |
    | bookstore          |
    | bos                |
    | category           |
    | chatroom           |
    | crm                |
    | dangdang           |
    | maven              |
    | mmall              |
    | mybaits            |
    | mysql              |
    | o2o                |
    | performance_schema |
    | registersystem     |
    | spring6            |
    | spring_day03       |
    | springmvc          |
     -------------------- 
    29 rows in set (0.00 sec)
    
    • 因为主键列的特性是:必须唯一,不能为空,所以我们通常会指定主键为整型,然后设置其自动增长,这样可以保证在插入数据时主键列的唯一和非空特性。
    • 创建表时指定主键自增长

      CREATE TABLE stu(

      sid  INT PRIMARY KEY AUTO_INCREMENT,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10)
      

      );

    • 修改表时设置主键自增长:

    • 因为主键列的特性是:必须唯一,不能为空,所以我们通常会指定主键为整型,然后设置其自动增长,这样可以保证在插入数据时主键列的唯一和非空特性。
    • 创建表时指定主键自增长

      CREATE TABLE stu(

      sid  INT PRIMARY KEY AUTO_INCREMENT,
      sname  VARCHAR(20),
      age  INT,
      sex  VARCHEAR(10)
      

      );

    • 修改表时设置主键自增长:

    1 基础查询

    1.1 查询所有列

    SELECT * FROM stu;

     

    1.2 查询指定列

    SELECT sid, sname, age FROM stu; 【select sid,sname,sid sname from stu;】

    可以一次查询多列

     

    1 基础查询

    1.1 查询所有列

    SELECT * FROM stu;

     

    1.2 查询指定列

    SELECT sid, sname, age FROM stu; 【select sid,sname,sid sname from stu;】

    可以一次查询多列

     

     2. 数据类型(列类型)

    ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

    ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

    2 条件查询

    2.1 条件查询介绍

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

    l  =、!=、<>、<、<=、>、>=;

    l  BETWEEN…AND; (左边两端都包含)

    l  IN(set);

    l  IS NULL; update stu set sage=20 where sage=null; sage=null永远是false,所以要用is null。

    l  IS NOT NULL

    l  AND;

    l  OR;

    l  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 NOT sname IS NULL;

    或者

    SELECT *

    FROM stu

    WHERE sname IS NOT NULL;

     

    2 条件查询

    2.1 条件查询介绍

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

    l  =、!=、<>、<、<=、>、>=;

    l  BETWEEN…AND; (左边两端都包含)

    l  IN(set);

    l  IS NULL; update stu set sage=20 where sage=null; sage=null永远是false,所以要用is null。

    l  IS NOT NULL

    l  AND;

    l  OR;

    l  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 NOT sname IS NULL;

    或者

    SELECT *

    FROM stu

    WHERE sname IS NOT NULL;

     

    int:整型
    double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
    decimal:浮点型,在表单钱方面使用该类型,因为不会出现精度缺失问题;
    char:固定长度字符串类型; char(255),数据的长度不足指定长度,补足到指定长度!
    varchar:可变长度字符串类型; varchar(65535), zhangSan
    text(clob):字符串类型;
      > 很小
      > 小
      > 中
      > 大
    blob:字节类型;
      > 很小
      > 小
      > 中
      > 大
    date:日期类型,格式为:yyyy-MM-dd;
    time:时间类型,格式为:hh:mm:ss
    timestamp:时间戳类型;

    • 修改表时删除主键自增长:
    • 修改表时删除主键自增长:
    3 模糊查询(单词中包含a字母)

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

    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”开头的学生记录  (%表示其余的位数,“%”匹配0~n个任何字母)

    SELECT *

    FROM stu

    WHERE sname LIKE 'z%';

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

     

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

    SELECT *

    FROM stu

    WHERE sname LIKE '_i%';

     

    3.5 查询姓名中包含“a”字母的学生记录(where sname like '%y%' and sage>15;可以结合使用)

    SELECT *

    FROM stu

    WHERE sname LIKE '%a%';

     

    3 模糊查询(单词中包含a字母)

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

    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”开头的学生记录  (%表示其余的位数,“%”匹配0~n个任何字母)

    SELECT *

    FROM stu

    WHERE sname LIKE 'z%';

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

     

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

    SELECT *

    FROM stu

    WHERE sname LIKE '_i%';

     

    3.5 查询姓名中包含“a”字母的学生记录(where sname like '%y%' and sage>15;可以结合使用)

    SELECT *

    FROM stu

    WHERE sname LIKE '%a%';

     

    对表操作的语法格式和案例:

    ALTER TABLE stu CHANGE sid sid INT ;

    ALTER TABLE stu CHANGE sid sid INT ;

    4 字段控制查询(去除重复记录,字段加减,添加别名等)

    4.1 去除重复记录( distinct )

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

    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 给列名添加别名(AS …)会生成新的total列

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

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

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

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

     

    4 字段控制查询(去除重复记录,字段加减,添加别名等)

    4.1 去除重复记录( distinct )

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

    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 给列名添加别名(AS …)会生成新的total列

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

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

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

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

     

    语法:

    • 测试主键自增长:
    • 测试主键自增长:
    5 排序( ORDER BY _____ ASC ascend;   DESC descend)

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

    SELECT *

    FROM stu

    ORDER BY sage ASC;

    或者

    SELECT *

    FROM stu

    ORDER BY sage;

     

     

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

    SELECT *

    FROM stu

    ORDER BY age DESC;

     

    5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序(order by …asc,…desc)

    SELECT * FROM emp

    ORDER BY sal DESC,empno ASC;

     

     

    5 排序( ORDER BY _____ ASC ascend;   DESC descend)

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

    SELECT *

    FROM stu

    ORDER BY sage ASC;

    或者

    SELECT *

    FROM stu

    ORDER BY sage;

     

     

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

    SELECT *

    FROM stu

    ORDER BY age DESC;

     

    5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序(order by …asc,…desc)

    SELECT * FROM emp

    ORDER BY sal DESC,empno ASC;

     

     

     表
    * 创建表:
      CREATE TABLE [IF NOT EXISTS] 表名(ITCAST_0001
        列名 列类型,
        列名 列类型,
        ...
        列名 列类型
      );
    * 查看当前数据库中所有表名称:SHOW TABLES;
    * 查看指定表的创建语句:SHOW CREATE TABLE 表名(了解);
    * 查看表结构:DESC 表名;
    * 删除表:DROP TABLE 表名;
    * 修改表:前缀:ALTER TABLE 表名
      > 修改之添加列:
        ALTER TABLE 表名 ADD (
          列名 列类型,
          列名 列类型,
          ...
        );
      > 修改之修改列类型(如果被修改的列已存在数据,那么新的类型可能会影响到已存在数据):ALTER TABLE 表名 MODIFY 列名 列类型;
      > 修改之修改列名:ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
      > 修改之删除列:ALTER TABLE 表名 DROP 列名;
      > 修改表名称:ALTER TABLE 原表名 RENAME TO 新表名;

    INSERT INTO stu VALUES(NULL,'zhangsan',23,'man');

    INSERT INTO stu(sname,age,sex) VALUES(NULL,'zhangsan',23,'man');

    INSERT INTO stu VALUES(NULL,'zhangsan',23,'man');

    INSERT INTO stu(sname,age,sex) VALUES(NULL,'zhangsan',23,'man');

    6 聚合函数

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

    l  COUNT( …):统计指定列不为NULL的记录行数;

    l  MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;

    l  MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;

    l  SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;

    l  AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

    l  CONCAT(): mysql特有的,连接字符串

    l  ROUND(AVG(),2)保留两位小数

     

     

    6.1 COUNT

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

    l  查询emp表中记录数:

    SELECT COUNT(*) AS cnt FROM emp;

     

    l  查询emp表中有佣金的人数:

    SELECT COUNT(comm) cnt FROM emp;

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

     

    l  查询emp表中月薪大于2500的人数:

    SELECT COUNT(*) FROM emp

    WHERE sal > 2500;

     

    l  统计月薪与佣金之和大于2500元的人数:(先判断有没有为null的,有的话用IFNULL)

    SELECT COUNT(*) AS cnt FROM emp WHERE sal IFNULL(comm,0) > 2500;

     

    l  查询有佣金的人数,以及有领导的人数:

    SELECT COUNT(comm), COUNT(mgr) FROM emp;

     

    6.2 SUM和AVG

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

    l  查询所有雇员月薪和:

    SELECT SUM(sal) FROM emp;

     

    l  查询所有雇员月薪和,以及所有雇员佣金和:

    SELECT SUM(sal), SUM(comm) FROM emp;

     

    l  查询所有雇员月薪 佣金和:

    SELECT SUM(sal IFNULL(comm,0)) FROM emp;

     

    l  统计所有员工平均工资:

    SELECT SUM(sal), COUNT(sal) FROM emp;

    或者

    SELECT AVG(sal) FROM emp;

     

    6.3 MAX和MIN

    l  查询最高工资和最低工资:

    SELECT MAX(sal), MIN(sal) FROM emp;

     

    6.4 CONCAT(’ ‘, ename, ‘,’ job )【mysql特有,连接字符串】

     

     

    6.5 ROUND(AVG(sal),2)保留两位小数

     

    6 聚合函数

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

    l  COUNT( …):统计指定列不为NULL的记录行数;

    l  MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;

    l  MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;

    l  SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;

    l  AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;

    l  CONCAT(): mysql特有的,连接字符串

    l  ROUND(AVG(),2)保留两位小数

     

     

    6.1 COUNT

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

    l  查询emp表中记录数:

    SELECT COUNT(*) AS cnt FROM emp;

     

    l  查询emp表中有佣金的人数:

    SELECT COUNT(comm) cnt FROM emp;

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

     

    l  查询emp表中月薪大于2500的人数:

    SELECT COUNT(*) FROM emp

    WHERE sal > 2500;

     

    l  统计月薪与佣金之和大于2500元的人数:(先判断有没有为null的,有的话用IFNULL)

    SELECT COUNT(*) AS cnt FROM emp WHERE sal IFNULL(comm,0) > 2500;

     

    l  查询有佣金的人数,以及有领导的人数:

    SELECT COUNT(comm), COUNT(mgr) FROM emp;

     

    6.2 SUM和AVG

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

    l  查询所有雇员月薪和:

    SELECT SUM(sal) FROM emp;

     

    l  查询所有雇员月薪和,以及所有雇员佣金和:

    SELECT SUM(sal), SUM(comm) FROM emp;

     

    l  查询所有雇员月薪 佣金和:

    SELECT SUM(sal IFNULL(comm,0)) FROM emp;

     

    l  统计所有员工平均工资:

    SELECT SUM(sal), COUNT(sal) FROM emp;

    或者

    SELECT AVG(sal) FROM emp;

     

    6.3 MAX和MIN

    l  查询最高工资和最低工资:

    SELECT MAX(sal), MIN(sal) FROM emp;

     

    6.4 CONCAT(’ ‘, ename, ‘,’ job )【mysql特有,连接字符串】

     

     

    6.5 ROUND(AVG(sal),2)保留两位小数

     

    //创建表
    mysql> create table tb_stu(
        -> number char(10),
        -> name varchar(50),
        -> age int,
        -> gender varchar(10)
        -> );
    Query OK, 0 rows affected (0.24 sec)
    
    -- 查看表
    mysql> show tables;
     ---------------- 
    | Tables_in_mydb |
     ---------------- 
    | tb_stu         |
     ---------------- 
    1 row in set (0.00 sec)
    
    -- 查看表结构
    mysql> desc tb_stu;
     -------- ------------- ------ ----- --------- ------- 
    | Field  | Type        | Null | Key | Default | Extra |
     -------- ------------- ------ ----- --------- ------- 
    | number | char(10)    | YES  |     | NULL    |       |
    | name   | varchar(50) | YES  |     | NULL    |       |
    | age    | int(11)     | YES  |     | NULL    |       |
    | gender | varchar(10) | YES  |     | NULL    |       |
     -------- ------------- ------ ----- --------- ------- 
    4 rows in set (0.08 sec)
    
    -- 添加表的字段
    mysql> alter table tb_stu
        -> add(
        -> education varchar(50)
        -> );
    Query OK, 0 rows affected (0.30 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show tables;
     ---------------- 
    | Tables_in_mydb |
     ---------------- 
    | tb_stu         |
     ---------------- 
    1 row in set (0.00 sec)
    
    mysql> desc tb_stu;
     ----------- ------------- ------ ----- --------- ------- 
    | Field     | Type        | Null | Key | Default | Extra |
     ----------- ------------- ------ ----- --------- ------- 
    | number    | char(10)    | YES  |     | NULL    |       |
    | name      | varchar(50) | YES  |     | NULL    |       |
    | age       | int(11)     | YES  |     | NULL    |       |
    | gender    | varchar(10) | YES  |     | NULL    |       |
    | education | varchar(50) | YES  |     | NULL    |       |
     ----------- ------------- ------ ----- --------- ------- 
    5 rows in set (0.02 sec)
    
    -- 修改表的字段信息
    mysql> alter table tb_stu
        -> modify education varchar(30);
    Query OK, 0 rows affected (0.31 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc tb_stu;
     ----------- ------------- ------ ----- --------- ------- 
    | Field     | Type        | Null | Key | Default | Extra |
     ----------- ------------- ------ ----- --------- ------- 
    | number    | char(10)    | YES  |     | NULL    |       |
    | name      | varchar(50) | YES  |     | NULL    |       |
    | age       | int(11)     | YES  |     | NULL    |       |
    | gender    | varchar(10) | YES  |     | NULL    |       |
    | education | varchar(30) | YES  |     | NULL    |       |
     ----------- ------------- ------ ----- --------- ------- 
    5 rows in set (0.02 sec)
    
    -- 删除表的字段
    mysql> alter table tb_stu
        -> drop education;
    Query OK, 0 rows affected (0.31 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc tb_stu;
     -------- ------------- ------ ----- --------- ------- 
    | Field  | Type        | Null | Key | Default | Extra |
     -------- ------------- ------ ----- --------- ------- 
    | number | char(10)    | YES  |     | NULL    |       |
    | name   | varchar(50) | YES  |     | NULL    |       |
    | age    | int(11)     | YES  |     | NULL    |       |
    | gender | varchar(10) | YES  |     | NULL    |       |
     -------- ------------- ------ ----- --------- ------- 
    4 rows in set (0.02 sec)
    
    --修改表的名称
    mysql> alter table tb_stu
        -> rename to tb_student;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> show tables;
     ---------------- 
    | Tables_in_mydb |
     ---------------- 
    | tb_student     |
     ---------------- 
    1 row in set (0.00 sec)
    

    3、非空约束

    3、非空约束

    7 分组查询(一般查询主信息(当前组最高值,人数,等等),不能查询个人信息)

     

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

     

    7.1 分组查询(一般后面跟count(*))

    l  查询每个部门的部门编号和每个部门的工资和:

    SELECT deptno, SUM(sal)

    FROM emp

    GROUP BY deptno;

     

    l  查询每个部门的部门编号以及每个部门的人数:

    SELECT deptno,COUNT(*)

    FROM emp

    GROUP BY deptno;

     

    l  查询每个部门的部门编号以及每个部门工资大于1500的人数:

    SELECT deptno,COUNT(*)

    FROM emp

    WHERE sal>1500

    GROUP BY deptno;

     

    7.2 HAVING子句(分组后的约束)

    l  查询工资总和大于9000的部门编号以及工资和:

    SELECT deptno, SUM(sal)

    FROM emp

    GROUP BY deptno

    HAVING SUM(sal) > 9000;

      

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

     

    7 分组查询(一般查询主信息(当前组最高值,人数,等等),不能查询个人信息)

     

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

     

    7.1 分组查询(一般后面跟count(*))

    l  查询每个部门的部门编号和每个部门的工资和:

    SELECT deptno, SUM(sal)

    FROM emp

    GROUP BY deptno;

     

    l  查询每个部门的部门编号以及每个部门的人数:

    SELECT deptno,COUNT(*)

    FROM emp

    GROUP BY deptno;

     

    l  查询每个部门的部门编号以及每个部门工资大于1500的人数:

    SELECT deptno,COUNT(*)

    FROM emp

    WHERE sal>1500

    GROUP BY deptno;

     

    7.2 HAVING子句(分组后的约束)

    l  查询工资总和大于9000的部门编号以及工资和:

    SELECT deptno, SUM(sal)

    FROM emp

    GROUP BY deptno

    HAVING SUM(sal) > 9000;

      

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

     

     DML(数据操作语言,它是对表记录的操作(增、删、改)!)

    因为某些列不能设置为null值,所以可以对添加非空约束。

    因为某些列不能设置为null值,所以可以对添加非空约束。

    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页记录应该怎么查呢?

    l  第一页记录起始行为0,一共查询10行;

    l  第二页记录起始行为10,一共查询10行;

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

     

    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页记录应该怎么查呢?

    l  第一页记录起始行为0,一共查询10行;

    l  第二页记录起始行为10,一共查询10行;

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

     

    1. 插入数据
      * INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
        > 在表名后给出要插入的列名,其他没有指定的列等同与插入null值。所以插入记录总是插入一行,不可能是半行。
        > 在VALUES后给出列值,值的顺序和个数必须与前面指定的列对应
      * INTERT INTO 表名 VALUES(列值1, 列值2)
        > 没有给出要插入的列,那么表示插入所有列。
        > 值的个数必须是该表列的个数。
        > 值的顺序,必须与表创建时给出的列的顺序相同。

    2. 修改数据
      * UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]
      * 条件(条件可选的):
        > 条件必须是一个boolean类型的值或表达式:UPDATE t_person SET gender='男', age=age 1 WHERE sid='1';
        > 运算符:=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND

    例如:

    例如:

    4、完整性约束

      完整性约束是为了表的数据的正确性!如果数据不正确,那么一开始就不能添加到表中。

     

    4、完整性约束

      完整性约束是为了表的数据的正确性!如果数据不正确,那么一开始就不能添加到表中。

     

    DCL

    CREATE TABLE stu (
       sid INT PRIMARY KEY AUTO_INCREMENT,
       sname  VARCHAR(20) NOT NULL,
       age   INT,
       sex  VARCHAR(10)
    );
    
    CREATE TABLE stu (
       sid INT PRIMARY KEY AUTO_INCREMENT,
       sname  VARCHAR(20) NOT NULL,
       age   INT,
       sex  VARCHAR(10)
    );
    
    1 主键(primary key)

    当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识,而学生的姓名是不能做唯一标识的,因为学习有可能同名。

    主键列的值不能为NULL,也不能重复!

      指定主键约束使用PRIMARY KEY关键字

     

    l  创建表:定义列时指定主键:

    CREATE TABLE stu(

                      sid        CHAR(6) PRIMARY KEY,

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10)

    );

    l  创建表:定义列之后独立指定主键:

     

    CREATE TABLE stu(

                      sid        CHAR(6),

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10),

                       PRIMARY KEY(sid)

    );

     

    l  修改表时指定主键:

    ALTER TABLE stu

    ADD PRIMARY KEY(sid);

     

    l  删除主键(只是删除主键约束,而不会删除主键列):

    ALTER TABLE stu DROP PRIMARY KEY;

     

    1 主键(primary key)

    当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识,而学生的姓名是不能做唯一标识的,因为学习有可能同名。

    主键列的值不能为NULL,也不能重复!

      指定主键约束使用PRIMARY KEY关键字

     

    l  创建表:定义列时指定主键:

    CREATE TABLE stu(

                      sid        CHAR(6) PRIMARY KEY,

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10)

    );

    l  创建表:定义列之后独立指定主键:

     

    CREATE TABLE stu(

                      sid        CHAR(6),

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10),

                       PRIMARY KEY(sid)

    );

     

    l  修改表时指定主键:

    ALTER TABLE stu

    ADD PRIMARY KEY(sid);

     

    l  删除主键(只是删除主键约束,而不会删除主键列):

    ALTER TABLE stu DROP PRIMARY KEY;

     

    * 一个项目创建一个用户!一个项目对应的数据库只有一个!
    * 这个用户只能对这个数据库有权限,其他数据库你就操作不了了!

    对sname列设置了非空约束。

    对sname列设置了非空约束。

    2 主键自增长(非空,唯一,可被引用:整型才可以自增长:AUTO_INCREMENT)

    MySQL提供了主键自动增长的功能!这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值 1,也就不会出现重复主键的可能了。

    l  创建表时设置主键自增长(主键必须是整型才可以自增长):

    CREATE TABLE stu(

                       sid INT PRIMARY KEY AUTO_INCREMENT,

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10)

    );

     

    l  修改表时设置主键自增长:(关键字:change)

    ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

     

    l  修改表时删除主键自增长:

    ALTER TABLE stu CHANGE sid sid INT;

     

    2 主键自增长(非空,唯一,可被引用:整型才可以自增长:AUTO_INCREMENT)

    MySQL提供了主键自动增长的功能!这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值 1,也就不会出现重复主键的可能了。

    l  创建表时设置主键自增长(主键必须是整型才可以自增长):

    CREATE TABLE stu(

                       sid INT PRIMARY KEY AUTO_INCREMENT,

                      sname      VARCHAR(20),

                      age            INT,

                      gender     VARCHAR(10)

    );

     

    l  修改表时设置主键自增长:(关键字:change)

    ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

     

    l  修改表时删除主键自增长:

    ALTER TABLE stu CHANGE sid sid INT;

     

    1. 创建用户
        * CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
          > 用户只能在指定的IP地址上登录
        * CREATE USER 用户名@'%' IDENTIFIED BY '密码';
          > 用户可以在任意IP地址上登录

    2. 给用户授权
        * GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址
          > 权限、用户、数据库
          > 给用户分派在指定的数据库上的指定的权限
          > 例如;GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
            * 给user1用户分派在mydb1数据库上的create、alter、drop、insert、update、delete、select权限
        * GRANT ALL ON 数据库.* TO 用户名@IP地址;
          > 给用户分派指定数据库上的所有权限

    3. 撤销授权
        * REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;
          > 撤消指定用户在指定数据库上的指定权限
          > 例如;REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
            * 撤消user1用户在mydb1数据库上的create、alter、drop权限

    4. 查看权限
        * SHOW GRANTS FOR 用户名@IP地址
          > 查看指定用户的权限

    5. 删除用户
        * DROP USER 用户名@IP地址

    4、唯一约束

    4、唯一约束

    3 非空(NOT NULL)

    指定非空约束的列不能没有值,也就是说在插入记录时,对添加了非空约束的列一定要给值;在修改记录时,不能把非空列的值设置为NULL。

    l  指定非空约束:

    CREATE TABLE stu(

                       sid INT PRIMARY KEY AUTO_INCREMENT,

                       sname VARCHAR(10) NOT NULL,

                      age            INT,

                      gender     VARCHAR(10)

    );

    当为sname字段指定为非空后,在向stu表中插入记录时,必须给sname字段指定值,否则会报错:

    INSERT INTO stu(sid) VALUES(1); 【报错】

     

      插入的记录中sname没有指定值,所以会报错!

     

    3 非空(NOT NULL)

    指定非空约束的列不能没有值,也就是说在插入记录时,对添加了非空约束的列一定要给值;在修改记录时,不能把非空列的值设置为NULL。

    l  指定非空约束:

    CREATE TABLE stu(

                       sid INT PRIMARY KEY AUTO_INCREMENT,

                       sname VARCHAR(10) NOT NULL,

                      age            INT,

                      gender     VARCHAR(10)

    );

    当为sname字段指定为非空后,在向stu表中插入记录时,必须给sname字段指定值,否则会报错:

    INSERT INTO stu(sid) VALUES(1); 【报错】

     

      插入的记录中sname没有指定值,所以会报错!

     

    *****DQL -- 数据查询语言
      查询不会修改数据库表记录!

    车库某些列不能设置重复的值,所以可以对列添加唯一约束。

    车库某些列不能设置重复的值,所以可以对列添加唯一约束。

    4 唯一(UNIQUE)

    还可以为字段指定唯一约束!当为字段指定唯一约束后,那么字段的值必须是唯一的。这一点与主键相似!例如给stu表的sname字段指定唯一约束:

    CREATE TABLE tab_ab(

             sid INT PRIMARY KEY AUTO_INCREMENT,

             sname VARCHAR(10) UNIQUE

    );

     

      INSERT INTO sname(sid, sname) VALUES(1001, 'zs');

    INSERT INTO sname(sid, sname) VALUES(1002, 'zs');

      当两次插入相同的名字时,MySQL会报错!

     

    4 唯一(UNIQUE)

    还可以为字段指定唯一约束!当为字段指定唯一约束后,那么字段的值必须是唯一的。这一点与主键相似!例如给stu表的sname字段指定唯一约束:

    CREATE TABLE tab_ab(

             sid INT PRIMARY KEY AUTO_INCREMENT,

             sname VARCHAR(10) UNIQUE

    );

     

      INSERT INTO sname(sid, sname) VALUES(1001, 'zs');

    INSERT INTO sname(sid, sname) VALUES(1002, 'zs');

      当两次插入相同的名字时,MySQL会报错!

     

    一、 基本查询

    例如:

    例如:

    5 外键(外键就是用来约束这一列的值必须是另一张表的主键值)

    主外键是构成表与表关联的唯一途径!

    外键是另一张表的主键!例如员工表与部门表之间就存在关联关系,其中员工表中的部门编号字段就是外键,是相对部门表的外键。

    我们再来看BBS系统中:用户表(t_user)、分类表(t_section)、帖子表(t_topic)三者之间的关系。Topic id    user id  section id

     

     

     

    例如在t_section表中sid为1的记录说明有一个分类叫java,版主是t_user表中uid为1的用户,即zs!

    例如在t_topic表中tid为2的记录是名字为“Java是咖啡”的帖子,它是java版块的帖子,它的作者是ww。

    外键就是用来约束这一列的值必须是另一张表的主键值!!!

     

    l  创建t_user表,指定uid为主键列:

    CREATE TABLE t_user(

             uid   INT PRIMARY KEY AUTO_INCREMENT,

             uname     VARCHAR(20) UNIQUE NOT NULL

    );

     

    l  创建t_section表,指定sid为主键列,u_id为相对t_user表的uid列的外键:

    CREATE TABLE t_section(

                       sid    INT PRIMARY KEY AUTO_INCREMENT,

                       sname      VARCHAR(30),

                       u_id INT,

                       CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)

    );

    l  修改t_section表,指定u_id为相对t_user表的uid列的外键:

    ALTER TABLE t_section

    ADD CONSTRAINT fk_t_user (自定义外键别名)

    FOREIGN KEY(u_id)

    REFERENCES t_user(uid);

    l  修改t_section表,删除u_id的外键约束:

    ALTER TABLE t_section

    DROP FOREIGN KEY fk_t_user;

     

    5 外键(外键就是用来约束这一列的值必须是另一张表的主键值)

    主外键是构成表与表关联的唯一途径!

    外键是另一张表的主键!例如员工表与部门表之间就存在关联关系,其中员工表中的部门编号字段就是外键,是相对部门表的外键。

    我们再来看BBS系统中:用户表(t_user)、分类表(t_section)、帖子表(t_topic)三者之间的关系。Topic id    user id  section id

     

     

     

    例如在t_section表中sid为1的记录说明有一个分类叫java,版主是t_user表中uid为1的用户,即zs!

    例如在t_topic表中tid为2的记录是名字为“Java是咖啡”的帖子,它是java版块的帖子,它的作者是ww。

    外键就是用来约束这一列的值必须是另一张表的主键值!!!

     

    l  创建t_user表,指定uid为主键列:

    CREATE TABLE t_user(

             uid   INT PRIMARY KEY AUTO_INCREMENT,

             uname     VARCHAR(20) UNIQUE NOT NULL

    );

     

    l  创建t_section表,指定sid为主键列,u_id为相对t_user表的uid列的外键:

    CREATE TABLE t_section(

                       sid    INT PRIMARY KEY AUTO_INCREMENT,

                       sname      VARCHAR(30),

                       u_id INT,

                       CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)

    );

    l  修改t_section表,指定u_id为相对t_user表的uid列的外键:

    ALTER TABLE t_section

    ADD CONSTRAINT fk_t_user (自定义外键别名)

    FOREIGN KEY(u_id)

    REFERENCES t_user(uid);

    l  修改t_section表,删除u_id的外键约束:

    ALTER TABLE t_section

    DROP FOREIGN KEY fk_t_user;

     

    1. 字段(列)控制
      1) 查询所有列
       SELECT * FROM 表名;
       SELECT * FROM emp;
       --> 其中“*”表示查询所有列
    CREATE TABLE stu (
       sid INT PRIMARY KEY AUTO_INCREMENT,
       sname  VARCHAR(20) NOT NULL UNIQUE,
       age   INT,
       sex  VARCHAR(10)
    );
    
    CREATE TABLE stu (
       sid INT PRIMARY KEY AUTO_INCREMENT,
       sname  VARCHAR(20) NOT NULL UNIQUE,
       age   INT,
       sex  VARCHAR(10)
    );
    
    6 表与表之间的关系(看视频)

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

    • 在t_card表中添加外键列(相对t_person表),并且给外键添加唯一约束;
    • 给t_card表的主键添加外键约束(相对t_personr表),即t_card表的主键也是外键。

     

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

     

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

     

    6 表与表之间的关系(看视频)

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

    • 在t_card表中添加外键列(相对t_person表),并且给外键添加唯一约束;
    • 给t_card表的主键添加外键约束(相对t_personr表),即t_card表的主键也是外键。

     

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

     

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

     

    2) 查询指定列
     SELECT 列1 [, 列2, ... 列N] FROM 表名;
     SELECT empno, ename, sal, comm FROM 表名;

    二、概念模型

    二、概念模型

    5、编码

     

    5、编码

     

    3) 完全重复的记录只一次
     当查询结果中的多行记录一模一样时,只显示一行。一般查询所有列时很少会有这种情况,但只查询一列(或几列)时,这总可能就大了!
     SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
     SELECT DISTINCT sal FROM emp;
     --> 保查询员工表的工资,如果存在相同的工资只显示一次!

    1、对象模型:在Java中是domain ,例如:User、Student .

    1、对象模型:在Java中是domain ,例如:User、Student .

    1 查看MySQL编码(SHOW VARIABLES LIKE 'char%')

      SHOW VARIABLES LIKE 'char%';

     

     

    因为当初安装时指定了字符集为UTF8,所以所有的编码都是UTF8。

    l  character_set_client:你发送的数据必须与client指定的编码一致!!!服务器会使用该编码来解读客户端发送过来的数据;

    l  character_set_connection:通过该编码与client一致!该编码不会导致乱码!当执行的是查询语句时,客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致,那么转换就不会出现问题;

    l  character_set_database:数据库默认编码,在创建数据库时,如果没有指定编码,那么默认使用database编码;

    l  character_set_server:MySQL服务器默认编码;

    l  character_set_results:响应的编码,即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码;

     

    1 查看MySQL编码(SHOW VARIABLES LIKE 'char%')

      SHOW VARIABLES LIKE 'char%';

     

     

    因为当初安装时指定了字符集为UTF8,所以所有的编码都是UTF8。

    l  character_set_client:你发送的数据必须与client指定的编码一致!!!服务器会使用该编码来解读客户端发送过来的数据;

    l  character_set_connection:通过该编码与client一致!该编码不会导致乱码!当执行的是查询语句时,客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致,那么转换就不会出现问题;

    l  character_set_database:数据库默认编码,在创建数据库时,如果没有指定编码,那么默认使用database编码;

    l  character_set_server:MySQL服务器默认编码;

    l  character_set_results:响应的编码,即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码;

     

    4) 列运算
     I 数量类型的列可以做加、减、乘、除运算
       SELECT sal*1.5 FROM emp;
       SELECT sal comm FROM emp;

    2、关系模型:在数据库中表,1对多,1对1,多对多。

    2、关系模型:在数据库中表,1对多,1对1,多对多。

    2 控制台编码

    修改character_set_client、character_set_results、character_set_connection为GBK,就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。

     

    控制台的编码只能是GBK,而不能修改为UTF8,这就出现一个问题。客户端发送的数据是GBK,而character_set_client为UTF8,这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码,那么只能修改character_set_client为GBK了。

    服务器发送给客户端的数据编码为character_set_result,它如果是UTF8,那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码,所以只能把character_set_result修改为GBK。

    l  修改character_set_client变量:set character_set_client=gbk;

    l  修改character_set_results变量:set character_set_results=gbk;

     

    设置编码只对当前连接有效,这说明每次登录MySQL提示符后都要去修改这两个编码,但可以通过修改配置文件来处理这一问题:配置文件路径:D:Program FilesMySQLMySQL Server 5.1 my.ini

     

     

    2 控制台编码

    修改character_set_client、character_set_results、character_set_connection为GBK,就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。

     

    控制台的编码只能是GBK,而不能修改为UTF8,这就出现一个问题。客户端发送的数据是GBK,而character_set_client为UTF8,这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码,那么只能修改character_set_client为GBK了。

    服务器发送给客户端的数据编码为character_set_result,它如果是UTF8,那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码,所以只能把character_set_result修改为GBK。

    l  修改character_set_client变量:set character_set_client=gbk;

    l  修改character_set_results变量:set character_set_results=gbk;

     

    设置编码只对当前连接有效,这说明每次登录MySQL提示符后都要去修改这两个编码,但可以通过修改配置文件来处理这一问题:配置文件路径:D:Program FilesMySQLMySQL Server 5.1 my.ini

     

     

     II 字符串类型可以做连续运算
       SELECT CONCAT('$', sal) FROM emp;

    三、外键约束

    三、外键约束

    3 MySQL工具

    使用MySQL工具是不会出现乱码的,因为它们会每次连接时都修改character_set_client、character_set_results、character_set_connection的编码。这样对my.ini上的配置覆盖了,也就不会出现乱码了。

     

    3 MySQL工具

    使用MySQL工具是不会出现乱码的,因为它们会每次连接时都修改character_set_client、character_set_results、character_set_connection的编码。这样对my.ini上的配置覆盖了,也就不会出现乱码了。

     

     III 转换NULL值
       有时需要把NULL转换成其它值,例如com 1000时,如果com列存在NULL值,那么NULL 1000还是NULL,而我们这时希望把NULL当前0来运算。
       SELECT IFNULL(comm, 0) 1000 FROM emp;
       --> IFNULL(comm, 0):如果comm中存在NULL值,那么当成0来运算。

    • 外键必须是另一表的主键的值(外键要引用主键。)
    • 外键可以重复
    • 外键可以为空
    • 外键必须是另一表的主键的值(外键要引用主键。)
    • 外键可以重复
    • 外键可以为空

    6、MySQL数据库备份与还原

     

    6、MySQL数据库备份与还原

     

     IV 给列起别名
       你也许已经注意到了,当使用列运算后,查询出的结果集中的列名称很不好看,这时我们需要给列名起个别名,这样在结果集中列名就显示别名了
       SELECT IFNULL(comm, 0) 1000 AS 奖金 FROM emp;
       --> 其中AS可以省略

    1、创建时添加外键约束

    1、创建时添加外键约束

    1、备份和恢复数据

     

    1 生成SQL脚本(不加分号;)

    在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本,但要注意,脚本文本中只包含数据库的内容,而不会存在创建数据库的语句!所以在恢复数据时,还需要自已手动创建一个数据库之后再去恢复数据。

      mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

     

     

     

    现在可以在C盘下找到mydb1.sql文件了!

    注意,mysqldump命令是在Windows控制台下执行,无需登录mysql!!!

     

    2 执行SQL脚本

    执行SQL脚本需要登录mysql,然后进入指定数据库,才可以执行SQL脚本!!!

    执行SQL脚本不只是用来恢复数据库,也可以在平时编写SQL脚本,然后使用执行SQL 脚本来操作数据库!大家都知道,在黑屏下编写SQL语句时,就算发现了错误,可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码,然后执行之!

    SOURCE C:mydb1.sql

     

     

     

      注意,在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突!例如在脚本文件中存在create table a的语句,而当前数据库中已经存在了a表,那么就会出错!

     

    还可以通过下面的方式来执行脚本文件:

    mysql -uroot -p123 mydb1<c:mydb1.sql

    mysql –u用户名 –p密码 数据库<要执行脚本文件路径

     

     

    这种方式无需登录mysql!

     

     

    1、备份和恢复数据

     

    1 生成SQL脚本(不加分号;)

    在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本,但要注意,脚本文本中只包含数据库的内容,而不会存在创建数据库的语句!所以在恢复数据时,还需要自已手动创建一个数据库之后再去恢复数据。

      mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径

     

     

     

    现在可以在C盘下找到mydb1.sql文件了!

    注意,mysqldump命令是在Windows控制台下执行,无需登录mysql!!!

     

    2 执行SQL脚本

    执行SQL脚本需要登录mysql,然后进入指定数据库,才可以执行SQL脚本!!!

    执行SQL脚本不只是用来恢复数据库,也可以在平时编写SQL脚本,然后使用执行SQL 脚本来操作数据库!大家都知道,在黑屏下编写SQL语句时,就算发现了错误,可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码,然后执行之!

    SOURCE C:mydb1.sql

     

     

     

      注意,在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突!例如在脚本文件中存在create table a的语句,而当前数据库中已经存在了a表,那么就会出错!

     

    还可以通过下面的方式来执行脚本文件:

    mysql -uroot -p123 mydb1<c:mydb1.sql

    mysql –u用户名 –p密码 数据库<要执行脚本文件路径

     

     

    这种方式无需登录mysql!

     

     

    1. 条件控制
      1) 条件查询
        与前面介绍的UPDATE和DELETE语句一样,SELECT语句也可以使用WHERE子句来控制记录。
        * SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
        * SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
        * SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');
      2) 模糊查询
        当你想查询姓张,并且姓名一共两个字的员工时,这时就可以使用模糊查询
        * SELECT * FROM emp WHERE ename LIKE '张_';
        --> 模糊查询需要使用运算符:LIKE,其中_匹配一个任意字符,注意,只匹配一个字符而不是多个。
        --> 上面语句查询的是姓张,名字由两个字组成的员工。
        * SELECT * FROM emp WHERE ename LIKE '___'; /*姓名由3个字组成的员工*/
    CREATE TABLE dept (
        deptno INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(50)
    );
    insert into dept values(10,'研发部');
    insert into dept values(20,'人力部');
    insert into dept values(30,'财务部');
    
    CREATE TABLE emp (
        empno INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(50),
        deptno INT,
        CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) 
    );
    
    CREATE TABLE dept (
        deptno INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(50)
    );
    INSERT INTO dept VALUES(10,'研发部');
    INSERT INTO dept VALUES(20,'人力部');
    INSERT INTO dept VALUES(30,'财务部');
    
    INSERT INTO emp(empno,ename) VALUES(null,'zhangsan');
    INSERT INTO emp(empno,ename,deptno) VALUES(null,'lisi',10);
    
    
    INSERT INTO emp(empno,ename,deptno) VALUES(null,'zhangsan',80);
    /* Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 
    (`mydb2`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
    */
    
    CREATE TABLE dept (
        deptno INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(50)
    );
    insert into dept values(10,'研发部');
    insert into dept values(20,'人力部');
    insert into dept values(30,'财务部');
    
    CREATE TABLE emp (
        empno INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(50),
        deptno INT,
        CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) 
    );
    
    CREATE TABLE dept (
        deptno INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(50)
    );
    INSERT INTO dept VALUES(10,'研发部');
    INSERT INTO dept VALUES(20,'人力部');
    INSERT INTO dept VALUES(30,'财务部');
    
    INSERT INTO emp(empno,ename) VALUES(null,'zhangsan');
    INSERT INTO emp(empno,ename,deptno) VALUES(null,'lisi',10);
    
    
    INSERT INTO emp(empno,ename,deptno) VALUES(null,'zhangsan',80);
    /* Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 
    (`mydb2`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
    */
    

    7、多表查询(重要)

    多表查询有如下几种:

    l  合并结果集;

    l  连接查询

    • 内连接
    • 外连接

    ²  左外连接

    ²  右外连接

    ²  全外连接(MySQL不支持)

    • 自然连接

    l  子查询

     

    7、多表查询(重要)

    多表查询有如下几种:

    l  合并结果集;

    l  连接查询

    • 内连接
    • 外连接

    ²  左外连接

    ²  右外连接

    ²  全外连接(MySQL不支持)

    • 自然连接

    l  子查询

     

      如果我们想查询姓张,名字几个字可以的员工时就要使用“%”了。
      SELECT * FROM emp WHERE ename LIKE '张%';
      --> 其中%匹配0~N个任意字符,所以上面语句查询的是姓张的所有员工。
      SELECT * FROM emp WHERE ename LIKE '%阿%';
      --> 千万不要认为上面语句是在查询姓名中间带有阿字的员工,因为%匹配0~N个字符,所以姓名以阿开头和结尾的员工也都会查询到。
      SELECT * FROM emp WHERE ename LIKE '%';
      --> 这个条件等同与不存在,但如果姓名为NULL的查询不出来!

    2、修改表时添加外键约束:

    2、修改表时添加外键约束:

    1 合并结果集(UNION , UNION ALL列数、列类型必须相同。)
    1. 作用:合并结果集就是把两个select语句的查询结果合并到一起!
    2. 合并结果集有两种方式:

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

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

    (两个4d)

     

     

    1. 要求:被合并的两个结果:列数、列类型必须相同。
    1 合并结果集(UNION , UNION ALL列数、列类型必须相同。)
    1. 作用:合并结果集就是把两个select语句的查询结果合并到一起!
    2. 合并结果集有两种方式:

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

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

    (两个4d)

     

     

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

    二、排序
    1) 升序
      SELECT * FROM WHERE emp ORDER BY sal ASC;
      --> 按sal排序,升序!
      --> 其中ASC是可以省略的
    2) 降序
      SELECT * FROM WHERE emp ORDER BY comm DESC;
      --> 按comm排序,降序!
      --> 其中DESC不能省略
    3) 使用多列作为排序条件
      SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;
      --> 使用sal升序排,如果sal相同时,使用comm的降序排

    ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERNCES dept(deptno);

    1 alter table t_student1 add constraint 外键名 foreign key(作为外键的列名) references t_class1(主键列名);
    2 ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERNCES dept(deptno);
    
    2 连接查询

    连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。(4*3条记录)

     

     

    连接查询会产生笛卡尔积,假设集合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[q8] ;

     

     

     

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

    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;[q9] 

     

    2.1 内连接(…inner join… on ..=..)

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

    SELECT *

    FROM emp e

    INNER[q10]  JOIN dept d

    ON[q11]  e.deptno=d.deptno;

     

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

     

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

     

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

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

    左连接:

    SELECT * FROM emp e

    LEFT OUTER[q12]  JOIN dept d

    ON e.deptno=d.deptno;

     

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

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

     

     

    2.3 右连接  …right outer join ..on…=…

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

    SELECT * FROM emp e

    RIGHT OUTER JOIN dept d

    ON e.deptno=d.deptno;

     

     

     

     

    连接查询心得

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

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

     

    2 连接查询

    连接查询就是求出多个表的乘积,例如t1连接t2,那么查询出的结果就是t1*t2。(4*3条记录)

     

     

    连接查询会产生笛卡尔积,假设集合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[q8] ;

     

     

     

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

    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;[q9] 

     

    2.1 内连接(…inner join… on ..=..)

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

    SELECT *

    FROM emp e

    INNER[q10]  JOIN dept d

    ON[q11]  e.deptno=d.deptno;

     

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

     

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

     

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

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

    左连接:

    SELECT * FROM emp e

    LEFT OUTER[q12]  JOIN dept d

    ON e.deptno=d.deptno;

     

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

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

     

     

    2.3 右连接  …right outer join ..on…=…

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

    SELECT * FROM emp e

    RIGHT OUTER JOIN dept d

    ON e.deptno=d.deptno;

     

     

     

     

    连接查询心得

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

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

     

    三、聚合函数
      聚合函数用来做某列的纵向运算。
    1) COUNT
      SELECT COUNT(*) FROM emp;
      --> 计算emp表中所有列都不为NULL的记录的行数
      SELECT COUNT(comm) FROM emp;
      --> 云计算emp表中comm列不为NULL的记录的行数
    2) MAX
      SELECT MAX(sal) FROM emp;
      --> 查询最高工资
    3) MIN
      SELECT MIN(sal) FROM emp;
      --> 查询最低工资
    4) SUM
      SELECT SUM(sal) FROM emp;
      --> 查询工资合
    5) AVG
      SELECT AVG(sal) FROM emp;
      --> 查询平均工资

    四、数据库关系模型

    3、删除外键约束语法

    3 自然连接(natural join ,natural left join ,natural right join)

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

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

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

    SELECT * FROM emp NATURAL JOIN dept;[q13] 

    SELECT * FROM emp NATURAL LEFT JOIN dept;[q14] 

    SELECT * FROM emp NATURAL RIGHT JOIN dept;[q15] 

     

    3 自然连接(natural join ,natural left join ,natural right join)

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

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

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

    SELECT * FROM emp NATURAL JOIN dept;[q13] 

    SELECT * FROM emp NATURAL LEFT JOIN dept;[q14] 

    SELECT * FROM emp NATURAL RIGHT JOIN dept;[q15] 

     

    四、分组查询
      分组查询是把记录使用某一列进行分组,然后查询组信息。
      例如:查看所有部门的记录数。
      SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
      --> 使用deptno分组,查询部门编号和每个部门的记录数
      SELECT job, MAX(SAL) FROM emp GROUP BY job;
      --> 使用job分组,查询每种工作的最高工资

    1、一对一关系

    -- MySql 语法
    alter table t_student1 drop foreign key 外键名;
    
    4 子查询

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

    l  子查询出现的位置:

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

    l  当子查询出现在where后作为条件时,还可以使用如下关键字:

    • any
    • all

    l  子查询结果集的形式:(下面的例子分别对应这4种情况)

    • 单行单列(用于条件)
    • 单行多列(用于条件)
    • 多行单列(用于条件)
    • 多行多列(用于表)

    条件

     * (***)单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (**)多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (*)单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

    * (***)多行多列:SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

    练习:

    1. 工资高于甘宁的员工。

    分析:

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

     

    第一步:查询甘宁的工资

    SELECT sal FROM emp WHERE ename='甘宁'

     

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

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

     

    结果:

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

    总结:

    l  子查询作为条件

    l  子查询形式为单行单列

     

    1. 工资高于30部门所有人的员工信息(ALL)

    分析:

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

     

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

    SELECT sal FROM emp WHERE deptno=30;

     

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

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

     

    结果:

    SELECT * FROM emp WHERE sal > ALL [q16] (SELECT sal FROM emp WHERE deptno=30)

     

    总结:

    l  子查询作为条件

    l  子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

     

    1. 查询工作和工资与殷天正完全相同的员工信息

    分析:

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

     

    第一步:查询出殷天正的工作和工资

    SELECT job,sal FROM emp WHERE ename='殷天正'

     

    第二步:查询出与殷天正工作和工资相同的人 in(….)匹配

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

     

    结果:

    SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')

    总结:

    l  子查询作为条件

    l  子查询形式为单行多列

     

    1. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址(表中取结果)

    分析:

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

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

    条件:员工编号为1006

     

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

    SELECT ename, sal FROM emp e WHERE empno=1006

     

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

    SELECT e.ename, e.sal, d.dname, d.loc

    FROM emp e, dept d

    WHERE e.deptno=d.deptno AND empno=1006

     

    第二步中的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=1006

     

    l  子查询作为表

    l  子查询形式为多行多列

     

     

     

    4 子查询

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

    l  子查询出现的位置:

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

    l  当子查询出现在where后作为条件时,还可以使用如下关键字:

    • any
    • all

    l  子查询结果集的形式:(下面的例子分别对应这4种情况)

    • 单行单列(用于条件)
    • 单行多列(用于条件)
    • 多行单列(用于条件)
    • 多行多列(用于表)

    条件

     * (***)单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (**)多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (*)单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

    * (***)多行多列:SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

    练习:

    1. 工资高于甘宁的员工。

    分析:

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

     

    第一步:查询甘宁的工资

    SELECT sal FROM emp WHERE ename='甘宁'

     

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

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

     

    结果:

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

    总结:

    l  子查询作为条件

    l  子查询形式为单行单列

     

    1. 工资高于30部门所有人的员工信息(ALL)

    分析:

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

     

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

    SELECT sal FROM emp WHERE deptno=30;

     

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

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

     

    结果:

    SELECT * FROM emp WHERE sal > ALL [q16] (SELECT sal FROM emp WHERE deptno=30)

     

    总结:

    l  子查询作为条件

    l  子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

     

    1. 查询工作和工资与殷天正完全相同的员工信息

    分析:

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

     

    第一步:查询出殷天正的工作和工资

    SELECT job,sal FROM emp WHERE ename='殷天正'

     

    第二步:查询出与殷天正工作和工资相同的人 in(….)匹配

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

     

    结果:

    SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')

    总结:

    l  子查询作为条件

    l  子查询形式为单行多列

     

    1. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址(表中取结果)

    分析:

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

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

    条件:员工编号为1006

     

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

    SELECT ename, sal FROM emp e WHERE empno=1006

     

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

    SELECT e.ename, e.sal, d.dname, d.loc

    FROM emp e, dept d

    WHERE e.deptno=d.deptno AND empno=1006

     

    第二步中的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=1006

     

    l  子查询作为表

    l  子查询形式为多行多列

     

     

     

      组条件
      以部门分组,查询每组记录数。条件为记录数大于3
      SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

      在表中建立一对一关系比较特殊,需要让其中一张表的主键,即是主键又是外键。

    四、数据库关系模型

    五、limit子句(方言)
      LIMIT用来限定查询结果的起始行,以及总行数。
      例如:查询起始行为第5行,一共查询3行记录
      SELECT * FROM emp LIMIT 4, 3;
      --> 其中4表示从第5行开始,其中3表示一共查询3行。即第5、6、7行记录。

    CREATE TABLE hasband (
        hid INT PRIMARY KEY AUTO_INCREMENT,
        hname VARCHAR(50)
    );
    
    CREATE TABLE wife (
        wid INT PRIMARY KEY AUTO_INCREMENT,
        wname VARCHAR(50),
        CONSTRAINT fk_wife_hasband FOREIGN KEY (wid)  REFERENCES hasband(hid) 
    );
    

    1、一对一关系

     约束
    * 约束是添加在列上的,用来约束列的!

    2、多对多关系

      在表中建立一对一关系比较特殊,需要让其中一张表的主键,即是主键又是外键。

    1. 主键约束(唯一标识)
        ****非空***
        ****唯一***
        ****被引用****(学习外键时)

    在表中建立多对多关系需要使用中间表,即需要三张表,在中间表中使用两个外键,分别引用其他两张表的主键。

    CREATE TABLE hasband (
        hid INT PRIMARY KEY AUTO_INCREMENT,
        hname VARCHAR(50)
    );
    
    CREATE TABLE wife (
        wid INT PRIMARY KEY AUTO_INCREMENT,
        wname VARCHAR(50),
        CONSTRAINT fk_wife_hasband FOREIGN KEY (wid)  REFERENCES hasband(hid) 
    );
    

      * 当表的某一列被指定为主键后,该列就不能为空,不能有重复值出现。
      * 创建表时指定主键的两种方式:
        >
        CREATE TABLE stu(
        sid        CHAR(6) PRIMARY KEY,
        sname    VARCHAR(20),
        age        INT,
        gender    VARCHAR(10)
        );
        指定sid列为主键列,即为sid列添加主键约束
        >
        CREATE TABLE stu(
        sid        CHAR(6),
        sname    VARCHAR(20),
        age        INT,
        gender    VARCHAR(10),
        PRIMARY KEY(sid)
        );
        指定sid列为主键列,即为sid列添加主键约束
      * 修改表时指定主键:ALTER TABLE stu ADD PRIMARY KEY(sid);
      * 删除主键:ALTER TABLE stu DROP PRIMARY KEY;

    CREATE TABLE student (
        sid INT PRIMARY KEY ,
        ......
    );
    
    CREATE TABLE teacher(
        tid INT PRIMARY KEY ,
        ......
    );
    
    CREATE TABLE stu_tea (
        sid INT,
        tid INT,
        ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY (sid)  REFERENCES student(sid) ,
        ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY (tid)  REFERENCES teacher(tid) 
    );
    

    2、多对多关系

    1. 主键自增长
        * 因为主键列的特性是:必须唯一、不能为空,所以我们通常会指定主键类为整型,然后设置其自动增长,这样可以保证在插入数据时主键列的唯一和非空特性。
        * 创建表时指定主键自增长
        CREATE TABLE stu(
              sid INT PRIMARY KEY AUTO_INCREMENT,
      多表查询。        sname    VARCHAR(20),
              age        INT,
              gender    VARCHAR(10)
        );
        * 修改表时设置主键自增长:ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
        * 修改表时删除主键自增长:ALTER TABLE stu CHANGE sid sid INT;
        * 测试主键自增长:
          > INSERT INTO stu VALUES(NULL, 'zhangSan',23,'male');
          > INSERT INTO stu(sname,age,gender) VALUES('zhangSan',23,'male');

    2. 非空约束
        * 因为某些列不能设置为NULL值,所以可以对列添加非空约束。
        * 例如:
        CREATE TABLE stu(
              sid INT PRIMARY KEY AUTO_INCREMENT,
              sname    VARCHAR(20) NOT NULL,
              age        INT,
              gender    VARCHAR(10)
        );
        * 对sname列设置了非空约束

    3. 唯一约束
        * 车库某些列不能设置重复的值,所以可以对列添加唯一约束。
        * 例如:
        CREATE TABLE stu(
              sid INT PRIMARY KEY AUTO_INCREMENT,
              sname    VARCHAR(20) NOT NULL UNIQUE,
              age        INT,
              gender    VARCHAR(10)
        );
        * 对sname列设置了非空约束

    4. 概念模型

    在中间表中建立关系,如:

    在表中建立多对多关系需要使用中间表,即需要三张表,在中间表中使用两个外键,分别引用其他两张表的主键。

    对象模型:可以双向关联,而且引用的是对象,而不是一个主键!
    关系模型:只能多方引用一方,而且引用的只是主键,而不是一整行记录。

    INSERT INTO stu_tea VALUES(5,1);

    INSERT INTO stu_tea VALUES(2,2);

    INSERT INTO stu_tea VALUES(3,2);

    CREATE TABLE student (
        sid INT PRIMARY KEY ,
        ......
    );
    
    CREATE TABLE teacher(
        tid INT PRIMARY KEY ,
        ......
    );
    
    CREATE TABLE stu_tea (
        sid INT,
        tid INT,
        ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY (sid)  REFERENCES student(sid) ,
        ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY (tid)  REFERENCES teacher(tid) 
    );
    

    对象模型:在java中是domain!!!例如:User、Student
    is a
    has a(关联)
      > 1对1
      > 1对多
      > 多对多
    use a  

    五、多表查询

    在中间表中建立关系,如:

    关系模型:在数据库中表!!!

    1、分类

    INSERT INTO stu_tea VALUES(5,1);

    INSERT INTO stu_tea VALUES(2,2);

    INSERT INTO stu_tea VALUES(3,2);

      当我们要完成一个软件系统时,需要把系统中的实体抽取出来,形成概念模型。
      例如部门、员工都是系统中的实体。概念模型中的实体最终会成为Java中的类、数据库中表。
      实体之间还存在着关系,关系有三种:
      * 1对多:例如每个员工都从属一个部门,而一个部门可以有多个员工,其中员工是多方,而部门是一方。
      * 1对1:例如老公和老婆就是一对一的关系,一个老公只能有一个老婆,而一个老婆只能有一个老公。
      * 多对多:老师与学生的关系就是多对多,一个老师可以有多个学生,一个学生可以有多个老师。

    • 合并结果集
    • 连接查询
    • 子查询

    五、多表查询

      概念模型在Java中成为实体类(javaBean)
      类就使用成员变量来完成关系,一般都是双向关联!
      多对一双向中关联,即员工关联部门,部门也关联员工
      class Employee {//多方关联一方
         ...
         private Department department;
      }
      class Department {//一方关联多方
         ...
         private List<Employee> employees;
      }

    2、合并结果查询

    1、分类

      class Husband {
         ...
         private Wife wife;
      }
      class Wife {
         ...
         private Husband
      }

    • 要求被合并表中,结果集列的类型和列数相同
    • UNION,去除重复行
    • UNION ALL,不去除重复行
    • 合并结果集
    • 连接查询
    • 子查询

      class Student {
         ...
         private List<Teacher> teachers
      }
      class Teacher {
         ...
         private List<Student> students;
      }

    SELECT * FROM 表1名

    UNION ALL

    SELECT * FROM 表2名;

    2、合并结果查询

    1. 外键约束
        * 外键必须是另一表的主键的值(外键要引用主键!)
        * 外键可以重复
        * 外键可以为空
        * 一张表中可以有多个外键!

    3、连接查询

    • 要求被合并表中,结果集列的类型和列数相同
    • UNION,去除重复行
    • UNION ALL,不去除重复行

      概念模型在数据库中成为表
      数据库表中的多对一关系,只需要在多方使用一个独立的列来引用1方的主键即可
      /*员工表*/
      create talbe emp (
        empno int primary key,/*员工编号*/
        ...
        deptno int/*所属部门的编号*/
      );
      /*部门表*/
      create table dept (
        deptno int  primary key,/*部门编号*/
        ...
      );
      emp表中的deptno列的值表示当前员工所从属的部门编号。也就是说emp.deptno必须在dept表中是真实存在!
      但是我们必须要去对它进行约束,不然可能会出现员工所属的部门编号是不存在的。这种约束就是外键约束。
      我们需要给emp.deptno添加外键约束,约束它的值必须在dept.deptno中存在。外键必须是另一个表的主键!

    ①分类

    SELECT * FROM 表1名

    UNION ALL

    SELECT * FROM 表2名;

      语法:CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键)
      创建表时指定外键约束
      create talbe emp (
        empno int primary key,
        ...
        deptno int,
        CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)  
      );

    • 内连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接(mysql不支持)
      • 自然连接(属于一种简化方式)

    3、连接查询

      修改表时添加外键约束
      ALERT TABLE emp
      ADD CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

    ②内连接

    ①分类

      修改表时删除外键约束
      ALTER TABLE emp
      DROP FOREIGN KEY fk_emp_deptno;/*约束名称*/  

    • 方言:SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.xx=别名2.xx;
    • 内连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接(mysql不支持)
      • 自然连接(属于一种简化方式)
    1. 数据库一对一关系
        在表中建立一对一关系比较特殊,需要让其中一张表的主键,即是主键又是外键。
        create table husband(
          hid int PRIMARY KEY,
          ...
        );
        create table wife(
          wid int PRIMARY KEY,
          ...
          ADD CONSTRAINT fk_wife_wid FOREIGN KEY(wid) REFERENCES husband(hid)
        );
        其中wife表的wid即是主键,又是相对husband表的外键!
        husband.hid是主键,不能重复!
        wife.wid是主键,不能重复,又是外键,必须来自husband.hid。
        所以如果在wife表中有一条记录的wid为1,那么wife表中的其他记录的wid就不能再是1了,因为它是主键。
        同时在husband.hid中必须存在1这个值,因为wid是外键。这就完成了一对一关系。

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

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

    ②内连接

      *****从表的主键即是外键!

         以条件筛选去除笛卡尔积中无用的信息。

    • 方言:SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.xx=别名2.xx;
    1. 数据库多对多关系
        在表中建立多对多关系需要使用中间表,即需要三张表,在中间表中使用两个外键,分别引用其他两个表的主键。
        create table student(
          sid int PRIMARY KEY,
          ...
        );
        create table teacher(
          tid int PRIMARY KEY,
          ...
        );
    • 标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;

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

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

      create table stu_tea(
        sid int,
        tid int,
        ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY(sid) REFERENCES student(sid),
        ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid)
      );
      这时在stu_tea这个中间表中的每条记录都是来说明student和teacher表的关系
      例如在stu_tea表中的记录:sid为1001,tid为2001,这说明编号为1001的学生有一个编号为2001的老师
      sid    tid
      101    201 /*编号为101的学生有一个编号为201的老师*/
      101    202 /*编号为101的学生有一个编号为202的老师*/
      101    203 /*编号为101的学生有一个编号为203的老师*/
      102    201 /*编号为102的学生有一个编号为201的老师*/
      102    204 /*编号为102的学生有一个编号为204的老师*/
    select *
    from emp, dept, (select * from emp)
    where
    group by
    having
    order by
    limit

    SELECT e.ename, e.sal , d.dname  FROM emp e INNER JOIN dept d ON  e.deptno=d.deptno;

         以条件筛选去除笛卡尔积中无用的信息。

    多表查询
      1. 分类:
        * 合并结果集(了解)
        * 连接查询
        * 子查询

    • 自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2 ;
    • 标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;

    合并结果集
      * 要求被合并的表中,列的类型和列数相同
      * UNION,去除重复行
      * UNION ALL,不去除重复行

    SELECT e.ename, e.sal , d.dname  FROM emp e NATURAL JOIN dept d;

    SELECT e.ename, e.sal , d.dname  FROM emp e INNER JOIN dept d ON  e.deptno=d.deptno;

    SELECT * FROM cd
    UNION ALL
    SELECT * FROM ab;

    • 内连接查询出的所有记录都满足条件
    • 自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2 ;

    连接查询
      1. 分类
        * 内连接
        * 外连接
          > 左外连接
          > 右外连接
          > 全外连接(MySQL不支持)
        * 自然连接(属于一种简化方式)

    ③外连接

    SELECT e.ename, e.sal , d.dname  FROM emp e NATURAL JOIN dept d;

      2. 内连接
        * 方言:SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
        * 标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
        * 自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
        * 内连接查询出的所有记录都满足条件。
     
      3. 外连接
        * 左外:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
          > 左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能出来。左表中不满足条件的记录,右表部分都为NULL
        * 左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
        * 右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
          > 右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能出来。右表不满足条件的记录,其左表部分都为NULL
        * 右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
        * 全链接:可以使用UNION来完成全链接

    • 左外:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
      • 左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能出来。左表中不满足条件的记录,右表部分都为null。
    • 内连接查询出的所有记录都满足条件

    子查询
      :查询中有查询(查看select关键字的个数!)
      1. 出现的位置:
        * where后作为条件存在
        * from后作为表存在(多行多列)

    SELECT e.ename, e.sal , IFNULL(d.dname,'无部门') AS dname  FROM emp e LEFT OUTER JOIN dept d ON  e.deptno=d.deptno;

    ③外连接

      2. 条件
        * (***)单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
        * (**)多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
        * (*)单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
        * (***)多行多列:SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

    • 左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    • 右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
      • 右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能出来。右表中不满足条件的记录,左表部分都为null。
    • 右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    • 全链接:可以使用UNION来完成全连接。
    • 左外:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
      • 左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能出来。左表中不满足条件的记录,右表部分都为null。

     

    SELECT e.ename, e.sal , d.dname 
    FROM emp e LEFT OUTER JOIN dept d 
    ON e.deptno=d.deptno
    UNION
    SELECT e.ename, e.sal , d.dname 
    FROM emp e RIGHT OUTER JOIN dept d 
    ON e.deptno=d.deptno;

    SELECT e.ename, e.sal , IFNULL(d.dname,'无部门') AS dname  FROM emp e LEFT OUTER JOIN dept d ON  e.deptno=d.deptno;

    4、子查询

    • 左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    • 右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
      • 右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能出来。右表中不满足条件的记录,左表部分都为null。
    • 右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
    • 全链接:可以使用UNION来完成全连接。

    查询中有查询(查看select关键字的个数)

    SELECT e.ename, e.sal , d.dname 
    FROM emp e LEFT OUTER JOIN dept d 
    ON e.deptno=d.deptno
    UNION
    SELECT e.ename, e.sal , d.dname 
    FROM emp e RIGHT OUTER JOIN dept d 
    ON e.deptno=d.deptno;

    ①出现的位置

    4、子查询

    • WHERE后作为条件存在
    • FROM后作为表存在(多行多列)

    查询中有查询(查看select关键字的个数)

    ②条件

    ①出现的位置

    • 单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件) ;
    • WHERE后作为条件存在
    • FROM后作为表存在(多行多列)

    SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

    ②条件

    • 多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN,ALL,ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件);
    • 单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件) ;

    SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE job='经理') ;

    SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

    • 单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2)IN (SELECT 列1,列2 FROM 表2 别名2 WHERE 条件);
    • 多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN,ALL,ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件);

    SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno from emp WHERE deptno=30) ;

    SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE job='经理') ;

    • 多行多列:SELECT * FROM 表1 别名1,(SELECT......)表2 别名2 WHERE 条件;
    • 单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2)IN (SELECT 列1,列2 FROM 表2 别名2 WHERE 条件);

     

    SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno from emp WHERE deptno=30) ;

    • 多行多列:SELECT * FROM 表1 别名1,(SELECT......)表2 别名2 WHERE 条件;

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:多表查询

    关键词: