数据库基础3 SQL语言

通用语法

  1. 可以单行或多行书写，以分号结尾。
  2. SQL语句可以使用空格缩进来增强可读性
  3. 不区分大小写
  4. 注释 使用# 或者 – 当同行注释

SQL分类

主要四类

分类

全称

说明

DDL

Definition

数据定义语言，定义数据库对象

DML

Manipulation

数据操作语言，增删改

DQL

Query

数据查询语言，查询表中记录

DCL

Control

数据控制语言，创建用户，控制访问权限

DDL

数据库操作

● 查询
show databases;
select database();
● 创建
create database if not exists **;

● 删除
drop database [if exists] **;
● 使用
use **;

表操作-查询

● 查询当前数据库所有表
show tables;
● 查询表结构
desc table_name;
● 查询表的建表语句
show create table table_name ;

表操作-创建

create table name(
 name 数据类型,
 …
 )

表操作-数据类型

● 数值类型

类型

大小

有符号范围

无符号范围

TINYINT

1byte

(-128,127)

(0,255)

SMALLINT

2byte

(-32768,32767)

MEDIUMINT

3byte

(-8388608,8388607)

INT

4byte

BIGINT

8byte

(-263,263-1)

FLOAT

4byte

DOUBLE

8byte

DECIMAL

依赖于M精度

（全数位数）和D标度(小数位数)

● 字符串类型


分类

类型

大小（bytes）

描述

字符串类型

CHAR

0-255

定长字符串

VARCHAR

0-65535

变长字符串，但是性能较差

TINYCHAR

0-255

不超过255字符的二进制数据

TINYTEXT

0-255

短文本字符串

BLOB

0-65535

长文本数据

TEXT

0-65535

长文本数据

MEDIUMBLOB

0-16777215

中等长度文本数据

MEDIUMTEXT

0-16777215

中等长度文本数据

LONGBLOBT

0-4294967295

极大文本数据

LONGTEXT

0-4294967295

极大文本数据

● 日期时间类型

类型

大小

范围

格式

描述

DATE

3

1000-01-01 至 9999-12-31

日期值

TIME

3

-839:59:59 至 839:59:59

YEAR

1

1901- 2155

DATETIME

8

1000-01-01 00:00:00 ----- 9999-12-31 23:59:59

混合日期和时间值

TIMESTEP

4

1970-01-01 00:00:00 ----- 2038-01-19 03:14:07

时间戳

birthday date

案例 根据需求创建表

设计一张员工信息表，要求如下：

  1. 编号
  2. 员工工号
  3. 员工姓名
  4. 性别
  5. 年龄
  6. 身份证号码
  7. 入职时间
create table emp(
  id int comment 'identity number',
  work_id varchar(10) ,
  name varchar(10),
  gender char(1),
  age tinyint unsigned,
  idcard char(18) comment'身份证号',
  entrydate date comment '入职时间'
);

表操作-修改

● 添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

● 修改数据类型

ALTER TABLE 表名 MODIFY 新字段名 类型(长度);

● 修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度);

● 删除字段

ALTER TABLE 表名 DROP 字段名;

● 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

表操作-删除

● 删除表

DROP TABLE [IF EXISTS] 表名;

● 删除指定表，并重新创建表

TRUNCATE TABLE 表名;

删除表时，表中数据都会被删除。

DML

介绍

对数据库中表中的数据记录进行增删改
● 添加数据 INSERT
● 修改数据 UPDATE
● 删除数据 DELETE

添加数据

● 给指定字段添加数据

INSERT INTO 表名 (字段名1，...) VALUES (值1，...);

● 给全部数据添加数据

INSERT INTO 表名 VALUES (值1，...);

● 批量添加数据

INSERT INTO 表名 (字段名1，...) VALUES (值1，...),(值1，...);
INSERT INTO 表名 VALUES (值1，...)，(值1，...);

注意：
● 插入数据时，指定的字段顺序需要与值的顺序一一对应
● 字符串和日期型数据应该包含在引号中
● 插入数据大小，应该在字段的规定范围内。

修改数据

删除数据

– 插入与修改字段数据类型

INSERT INTO employee (id, work_id, name, gender, age, idcard, entrydate, username)
    VALUES (1,'332','lIHUA','m',23,'1411231664825645548','2010-09-10','laoli');
select * from employee;
alter table employee change gender gender char(1) character set utf8 ;
alter table employee change name name varchar(10) character set utf8 ;
insert into employee
    VALUES (4,'034','庄sir','男',28,'1411231664825656478','2010-09-10','laocao'),
           (5,'035','黄sir','男',28,'1411231664985656478','2010-09-10','laohuang');

-- 更新
update employee set name = '庄仔',gender = '女'  where id = 4;
update employee set entrydate = '2009-01-02';

-- 删除符合条件数据
delete from employee where employee.gender = '女';
-- 删除所有员工
delete from employee;

DQL

介绍

DQL是数据查询语句，用来查询数据库中表的记录。
查询关键字：SELECT
语法

SELECT  字段列表
FROM    表名列表
WHERE    条件列表
GROUP BY  分组字段列表
HAVING    分组后条件列表
ORDER BY  排序字段列表
LIMIT     分页查询
基本查询
条件查询

● 语法
● 条件

比较运算符：

大于小于等于
BETWEEN … AND …
IN
LIKE
IS NULL

逻辑运算符：

AND
OR
NOT

聚合函数 (count 、max、min、avg、sum)

count 、max、min、avg、sum

分组查询

group by
● 分组之前过滤使用 where ，分组之后过滤使用having
● where 不能对聚合函数进行判断，having可以

排序查询

order by

-- 排序查询
-- 1.根据薪水对员工进行升序排序  ## 默认升序
select * from employees order by salary asc;
-- 2.根据入职时间，降序排序
select * from employees order by hiredate desc;
-- 3.根据薪水升序排序，若相同按照入职时间降序排序
select * from employees order by salary asc, hiredate desc ;
分页查询

LIMIT 起始索引， 查询记录数;
● 起始索引从0开始，起始索引 = (页码 - 1) * 每页显示记录数。
● 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
● 如果查询的是第一页可以省略。简写为 limit 10

执行顺序

首先执行 from
之后执行where 条件
之后执行 group by
之后查看select
之后看按什么排序 order by
最后执行分页顺序 limit

DCL

介绍

数据控制语句，管理数据库用户，控制数据库的访问权限。

管理用户

  1. 查询用户
USE mysql;
SELECT * FROM user;
  1. 创建用户
-- 创建用户  itcast , 只能在当前主机访问， 密码 123456
create user 'itcast'@'localhost' identified by '123456'; ## 默认没有权限
-- 创建用户 heima, 可以在任意主机访问数据库， 密码 wsdheima
create user 'heima'@'%' identified by 'wsdheima';
  1. 修改用户密码
-- 修改密码 heima 为 123456
alter user 'heima'@'%' identified with mysql_native_password by '13246';
  1. 删除用户
-- 删除用户
drop user 'heima'@'%';

权限控制

权限

说明

ALL

所以权限

SELECT

查询数据

INSERT

插入数据

UPDATE

ALTER

DROP

DELETE

CREATE

  1. 查询权限
  2. 授予权限
  3. 撤销权限
show grants for 'itcast'@'localhost';

grant all on myemployees.* to 'itcast'@'localhost';

revoke all on myemployees.* from 'itcast'@'localhost';