笔记：MySQL中的DDL、DML、DQL、约束等知识以及Navicat的安装教程

简介

• 1、英文：Structured Query Language（结构化查询语言）简称SQL
• 2、SQL语句可以单行或多行书写，以分号结尾
• 3、MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。
• 4、注释：
  • 单行注释：-- 注释内容(注意有一个空格) 或 # 注释内容(MySQL特有的注释方式)
  • 多行注释：/* 注释内容 */

SQL分类

• DDL（Data Definition Language）数据定义语言，用来定义数据库对象：数据库、表、列等
• DML（Data Manipulation Language）数据操作语言，用来对数据库中的数据进行增删改
• DQL（Data Query Language）数据查询语言，用来查询数据库表的记录（数据）
• DCL（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

图形化客户端工具Navicat

• Navicat for Mysql是管理和开发MySQL或MariaDB 的理想解决方案
• 这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面
• 官网：http://www.navicat.com.cn
• 下载安装参考教程：https://www.jb51.net/article/199496.htm

DDL (数据定义语言)

<1>DDL – 操作数据库：

• 1、查询：SHOW DATABASES；

• 2、创建：

  ​ （1）创建数据库：CREATE DATABASE 数据库名称；

  ​ （2）加IF判断：CREATE DATABASE IF NOT EXITSTS 数据库名称；

• 3、删除：

  ​ （1）删除数据库：DROP DATABASE数据库名称；

  ​ （2）加IF判断：DROP DATABASE IF EXITSTS 数据库名称；

• 4、使用当前数据库：

  ​ （1）查看当前使用的数据库：SELECT DATABASE（）；

  ​ （2）使用数据库：USE 数据库名称；

<2>DDL --操作表：

• 创建（Creat）：

  • CREATE TABLE 表名（

    ​ 字段名1 数据类型1，

    ​ 字段名2 数据类型2，

    ​ …

    ​ 字段名n 数据类型n

    ）；

    **tips：**括号中最后一行不能加逗号！

  • 数据类型：

    ​ （1）数值类型：INT、DOUBLE、TINYINT(小整数)、SMALLINT(大整数)、DECIMAL(小数值)…

      - double的使用：double(总长度，小数点后保留的位数)
    

    ​ （2）日期和世家类型：DATE(年月日)、TIME(时分秒)、YEAR(年份)、DATETIME(年月日时分秒)、TIMESTAMP(时间冲，年月日，不建议使用，最大表示时间2038年)

    ​ （3）字符串类型：CHAR（最大字长）、VARCHAR（最大字长）…

    • char与varchar区别：char为定长字符串、varchar为可变字符串。例如name char(10),存储姓名为张三的占10个字符空间，name varchar(10)，存储姓名为张三的占2个字符空间；char存储的性能高但浪费空间（以空间换时间），varchar节约空间但存储的性能低（以时间换空间）。
    • char和varchar使用场景：当能确定字符长度固定的时候可以用char，比如性别；当存储用户名或者密码的时候，考虑到空间的浪费情况，使用varchar更合适一些。

    例如：mysql>create table student(

      	   -> id int,
      	    -> name varchar(10),
      	    -> gender char(1),
      	   -> birthday date,
    		   -> score double(5,2),-- 成绩小数点后面保留两位
     		   -> eamil varchar(60),
      		  -> tel varchar(15),
      		  -> status tinyint
      		  -> );
    

• 查询（Retrieve）：

  • 查询当前数据库下所有的数据库名称：SHOW STABLES;

  • 查询表结构：DESC 表名称；

    例如：mysql> show tables;
    ±--------------+
    | Tables_in_db1 |
    ±--------------+
    | db_user |
    | student |
    ±--------------+
    2 rows in set (0.00 sec)

    ​ mysql> desc student;
    ±---------±------------±-----±----±--------±------+
    | Field | Type | Null | Key | Default | Extra |
    ±---------±------------±-----±----±--------±------+
    | id | int | YES | | NULL | |
    | name | varchar(10) | YES | | NULL | |
    | gender | char(1) | YES | | NULL | |
    | birthday | date | YES | | NULL | |
    | score | double(5,2) | YES | | NULL | |
    | eamil | varchar(60) | YES | | NULL | |
    | tel | varchar(15) | YES | | NULL | |
    | status | tinyint | YES | | NULL | |
    ±---------±------------±-----±----±--------±------+
    8 rows in set (0.00 sec)

• 修改（Update）：

  ​ （1）修改表名：ALTER TABLE 表名 RENAME TO 新的表名；

  ​ （2）添加一列：ALTER TABLE 表名 ADD 列名 数据类型；

  ​ （3）修改数据类型：ALTER TABLE 表名 MODIFY 列名 数据类型；

  ​ （4）修改类名和数据类型：ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型；

  ​ （5）删除列：ALTER TABLE 表名 DROP 列名；

• 删除（Delete）：

  ​ （1）删除表：DROP TABLE 表名；

  ​ （2）加IF判断：DROP TABLE IF EXISTS 表名；

DML(数据操作语言)

• 添加数据：

  （1）给指定列添加数据：

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

  ​ **例如：**INSERT into stu(id,name) values(1,“张三”);

  （2）给全部列添加数据（列名的列名可以省略）：

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

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

  ​ **例如：**INSERT into stu (id,name,gender,birthday,score,eamil,tel,status) values (2,‘李四’,‘男’,‘1998-12-5’,88.50,‘lisi1234@cast.cn’,‘17386112809’,1);

  （3）批量添加数据（列名的列名可以省略）：

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

  ​ 或 INSERT INTO 表名 VALUES(值1，值2,…),(值1,值2,…),(值1,值2,…)…;

  ​ **例如：**INSERT into stu values
  (4,‘张三’,‘女’,‘2001-05-09’,89.50,‘123445678@cast.cn’,‘1232132312’,1),(5,‘张彤彤’,‘男’,‘2001-05-09’,89.50,‘12376567123@cast.cn’,‘1232143’,1);

• 修改数据：

  （1）UPDATE 列名 SET 列名1=值1, 列名2=值2,…[WHERE 条件];

  ​ **例如：**update stu set gender = ‘女’, tel = ‘13086330257’
  where name = ‘张彤彤’;

  ​ **注：**修改语句中如果不加条件，则所有数据都将被修改！

• 删除数据：

  （1）DELETE FROM 表名 [WHERE 条件1 AND 条件2…];

  ​ **例如：**DELETE from stu where id=1 and name=‘张三’;

  ​ **注：**删除语句中如果不加条件，则所有数据都将被删除！

DQL(数据查询语言)

​

• 查询语法：

  ​ SELECT

  ​ 字段列表

  ​ FROM

  ​ 表名列表

  ​ WHERE

  ​ 条件列表

  ​ GROUP BY

  ​ 分组列表

  ​ HAVING

  ​ 分组后条件

  ​ ORDER BY

  ​ 排序字段

  ​ LIMIT

  ​ 分页限定

• 查询类别：

  1、基础查询

  ​ （1）基础查询：SELECT 信息 FROM 表名；

  ​ 例如：SELECT name,id from stu;

  ​ 例如：SELECT * from stu；

  ​ （2）去除重复查询字段：加关键字DISTINCT
  例如：select DISTINCT address from stu；

  ​ （3）起别名：AS

  ​ 例如：SELECT addr AS 住址 from stu；

  2、条件查询（where）

  ​ （1）语法：SELECT 字段列表 FROM 表名 WHERE 条件列表;

  ​ （2）条件：

  符号	功能
  >	大于
  <	小于
  =	等于
  >=	大于等于
  <=	小于等于
  <>或!=	不等于
  BETWEEN…AND…	范围之间
  IN(…)	多选一
  LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
  IS NULL	是null
  IS NOT NULL	不是null
  AND或&&	并且
  OR或||	或者
  NOT或!	非，不是

   - 其中LIKE用于模糊查询，如：
  

  ​ – 查询姓李的学生信息
  select * from info where name like ‘李%’;

  ​ – 查询姓名第二个字是佳的学生信息
  select * from info where name like ‘_佳%’;

  ​ – 查询姓名含有“悦”字的学生信息
  select * from info where name like ‘%悦%’;

  3、分组查询（group by）

  ​ （1）先介绍聚合函数查询：

   - 概念：将一列数据作为一个整体，进行纵向计算。
  
   	- 聚合函数分类：		
  

  函数名	功能
  count(列名)	统计数量（一般选用部位null的列）
  max(列名)	最大值
  min(列名)	最小值
  sum(列名)	求和
  avg(列名)	求平均值

  • 聚合函数语法：SELECT 聚合函数名(列名) FROM 表;

    ​ **注：**null值不参与所有聚合函数运算。

    （2）分组查询语法：SELECT 字段名 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

    ​ **注：**分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

    ​ WHERE和HAVING的区别：

    • 执行机制不一样：where是分组之前进行限定的，不满足where条件，则不参与分组，而having是分完组之后对结果进行过滤。
    • 可判断的条件不一样：where不能对聚合函数进行判断，having可以。

    ​ **执行顺序：**where>聚合函数>having

  4、排序查询（order by）

  ​ （1）语法：SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

  ​ （2）排序方式：

     1. ASC：升序排序（默认值）
  
     2. DESC：降序排序
  
        例如：
  
        ​	-- 查询学生信息，按照学号升序排序
        	select * from info order by xuehao;
  
        ​	-- 查询学生信息，按照学号降序排序
        	select * from info order by xuehao desc;
  
  
        ​	-- 查询姓李的学生信息，按照学号排序
        	select * from info where name like '李%' order by xuehao; 
  
  
        ​	-- 查询学生姓名，按照Java成绩降序排序
        	select name,xuehao,Java from score order by Java desc;
  
        **注：**如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序。
  

  5、分页查询（limit）

  ​ （1）分页查询语法：SELECT 字段列表 FROM 表名 LIMIT 其实索引,查询条数；

  ​ 例如： – 从0开始查询3条数据

  ​ select * from info limit 0,3;

  ​ – 每页显示3条数据，查询第1页数据，每页3条数据
  select * from info limit 0,3;

  ​ – 每页显示3条数据，查询第2页数据，每页3条数据
  select * from info limit 1,3;

  ​ – 每页显示3条数据，查询第6页数据，每页3条数据
  select * from info limit 5,3;

  - 起始索引：从0开始
  
    计算公式：起始索引= (当前页码-1)*每页显示的条数
  

  • tips：

    分页查询limit是MySQL数据库的方言

    Oracle分页查询使用rownumber

    SQL Server分页查询使用top

约束

• 约束的概念：

  ​ （1）约束是作用于表中列上的规则，用于限制加入表的数据

  ​ （2）约束的存在保证了数据库中数据的正确性、有效性和完整性

• 约束的分类：

  约束名称	描述	关键字
  非空约束	保证列中所有数据没有null值	NOT NULL
  唯一约束	保证列中所有数据各不相同	UNIQUE
  主键约束	主键是一行数据的唯一表示要求非空且唯一	PRIMARY KEY
  检查约束	保证列中的值满足某一条件	CHECK
  默认约束	保存数据时，未指定则采用默认值	DEFAULT
  外键约束	外键让两个表的数据之间简历链接，保证数据的一致性和完整性	FOREING KEY

  tips：

  ​ （1）DEFAULT：默认约束，当不赋值的时候默认为0，当赋值为null时不会进行默认，还是null值。

  ​ （2）AUTO_INCREAMENT：自动增长，当列是数字类型且唯一约束；例如用到主键id，不赋值可以实现自动增长。

  ​ （3）MySQL不支持检查约束。

• 非空约束：

  ​ （1）概念：非空约束用于保证列中所有数据不能有NULL值

  ​ （2）语法：

  • 添加约束

    ​ – 创建表时添加非空约束
    CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    );

    ​ – 建完表后添加非空约束
    – ALTER TABLE 表名MODIFY 字段名数据类型NOT NULL;