SQL基础上（基于MySQL）

1 SQL概述

1.1 什么是SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。

SQL标准（ANSI/ISO）有：
1、SQL-92：1992年发布的SQL语言标准；
2、SQL:1999：1999年发布的SQL语言标签；
3、SQL:2003：2003年发布的SQL语言标签；

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

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

1.2 语法要求

1、SQL语句可以单行或多行书写，以分号结尾；
2、可以用空格和缩进来来增强语句的可读性；
3、关键字不区别大小写，建议使用大写；

2 SQL分类

DDL：数据定义语言，用来定义数据库对象：库、表、列等（建库、建表）。
DML：数据操作语言，用来定义数据库记录（数据），进行插入、删除、更新操作。
DCL：数据控制语言，用来设置访问权限和安全级别。
DQL：数据查询语言，用来查询记录（数据）。

3 如何在Navicat中执行SQL语句

双击打开Navicat，打开MySQL80连接。

点击“新建查询”。

进入如下页面，在本页面就可以写SQL语句。点击运行，就会执行SQL语句。

4 DDL数据定义语言（MySQL）

4.1 基本操作

1、查看所有数据库

show databases;

2、切换数据库

use 数据库名;

切换到info数据库：

use info;

4.2 操作数据库

1、创建数据库

create database [if not exists] 数据库名;

[if not exists]：表示如果给定的“数据库名”不存在，则创建该数据库；若存在该数据库，不会再次创建该数据库了。

如果不加“IF NOT EXISTS”，当要创建的数据库存在时，就会报错。

2、删除数据库

drop database [if exists] 数据库名;

[if exists]：若要删除的数据库存在则删除，若不存在则不删除。

如果不加“IF EXISTS”，则如果该数据库不存在，就会报错。

3、修改数据库的编码格式

ALTER DATABASE 数据库名 CHARACTER SET 字符集名;

例如：修改数据库mydb1的编码为utf8

ALTER DATABASE mydb1 CHARACTER SET utf8

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

5 数据类型

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

类型	描述
int	整型
double	浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99
decimal	泛型类型。即可以存放int型，又可以存放double型，不会损失数据的精度
char	固定长度字符串（当输入的字符串不够长度时会补空格）
varchar	固定长度字符串 （当输入的字符串不够长度时不会补空格)）
text	字符串类型
blob	字节类型
date	日期类型（格式为：yyyy-MM-dd）
datetime	日期时间格式（格式为：hh:mm:ss）
timestamp	时间戳

6 操作表

6.1 创建表

CREATE TABLE 表名(
   列名 列类型,
   列名 列类型,
   ……
);

接下来以Sales数据库，以及其中的表Employees来进行实例说明。首先在Sales数据库中创建表Employees，其数据如下：

列名	数据类型	是否为空
编号	char(6)	No
姓名	char(8)	No
性别	char(2)	No
部门	varchar(16)	Yes
电话	varchar(20)	Yes
地址	varchar(50)	Yes

创建Employees表，SQL语句如下：

CREATE TABLE Employees(
  编号 CHAR(6) NOT NULL,
  姓名 CHAR(8) NOT NULL,
  性别 CHAR(2) NOT NULL,
  部门 VARCHAR(16),
  电话 VARCHAR(20),
  地址 VARCHAR(50)
);

6.2 查看表结构

desc 表名;

示例：查看employees表

desc employees;

6.3 删除表

drop table 表名;

示例：删除表employees

drop table employees;

6.4 修改表

1、添加列

alter table 表名 add (列名 数据类型(长度));

示例：给employees表添加一列：年龄 char(10)

ALTER TABLE Employees ADD (年龄 CHAR(10));

2、修改列的数据类型

alter table 表名 modify 列名 新数据类型(长度);

示例：给employees表中的年龄一列，修改char(10)到varchar(10)

ALTER TABLE Employees MODIFY 年龄 VARCHAR(10);

3、修改列名

alter table 表名 change 原列名 新列名 数据类型(长度);

示例：修改employees表中列名年龄改成年纪

ALTER TABLE employees CHANGE 年龄 年纪 CHAR(2);

4、删除列

alter table 表名 drop 列名;

示例：删除employees表中年纪一列

ALTER TABLE employees DROP 年纪;

5、修改表名

alter table 原表名 rename to 新表名;

示例：将employees表改名为employee

ALTER TABLE employees RENAME TO employee;

7 DML数据操作语言

7.1 插入数据

1、默认给所有列插入数据

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

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值（注意：所有字符串类型的数据必须使用单引号括起来）

示例：为employee表插入数据

INSERT INTO employee VALUES('e_001','一','男','财务部','111','北京');

2、给指定的列插入数据
前提：未插入数据的列允许为null

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

示例：为employee插入数据

INSERT INTO employee(编号,姓名,性别,部门) VALUES('e_002','二','女','财务部');

7.2 修改数据

update 表名 set 列名1=值1,列名=值2... [where 条件];

注意：判断列为空的方法是is null

示例：修改employee表中第二条数据

UPDATE employee SET 电话='222',地址='上海' WHERE 编号='e_002';

7.3 删除数据

1、删除满足条件的记录

delete from 表名 where 条件;

示例：删除表中编号为“e_001”的数据

DELETE FROM employee WHERE 编号='e_001';

2、删除所有记录：

--方式1
TRUNCATE TABLE 表名;
--方式2
delete from 表名;

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

TRUNCATE其实属于DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的。

8 DCL数据控制语言

8.1 创建用户

--地址为本地地址，也可以写成127.0.0.1。
create user '用户名'@地址 IDENTIFIED  BY '密码';
--或者可以写为以下的形式
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
--在这里%是通配符，表示不管什么地址都会创建用户

示例：创建user1用户，密码为123

CREATE USER 'user1'@localhost IDENTIFIED BY '123';

创建好后可以输入cmd打开命令提示符窗口验证是否可以登录到数据库。

8.2 给用户授权

如果只是创建用户，该用户只能登录到数据库，并不能进行任何的操作，因此需要给用户授权。

GRANT 权限1,权限2... ON 数据库.*  TO '用户名'@地址;

权限的种类主要有：

权限	说明
CREATE	创建
ALTER	修改
DROP	删除（库、表）
INSERT	插入
UPDATE	更新
DELETE	删除（记录）
SELECT	选择

给用户授予全部权限可以写成：

--方式1，下面的权限也可以选择的写上去，表示部分授权
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON 数据库.* TO 用户@localhost;
--方式2
GRANT ALL ON 数据库.* TO 用户@localhost;

示例：为user1授予create权限

GRANT CREATE ON sales.* TO user1@localhost;

在命令提示符窗口使用user1创建一个表s1。

create table s1(id int,name varchar(20));

再查看sales数据库，多了s1这样一张表：

如果使用user1用户查看表的结构，则回报错，因为没有权限：

desc s1;

8.3 查看用户权限

SHOW GRANTS FOR '用户名'@地址;

示例：查看user1用户的权限

SHOW GRANTS FOR 'user1'@localhost;

8.4 显示所有用户

SELECT user from mysql.user;

示例：显示数据库中所有的用户

8.5 撤销授权

REVOKE 权限1, … , 权限n ON 数据库.* FROM '用户名'@地址;

示例：撤销user1用户的create权限

REVOKE CREATE ON sales.* FROM 'user1'@localhost;

接下来展示一下user1的权限：，只有一个权限了：

SHOW GRANTS FOR user1@localhost;

8.6 修改用户密码

alter user '用户名'@localhost identified by '新密码';

示例：修改用户user1的密码为121212

ALTER USER 'user1'@localhost IDENTIFIED BY '121212';

8.7 删除用户

drop user '用户名'@地址;

示例：删除user1用户

DROP USER 'user1'@localhost;