数据库基础3 SQL语言

数据库基础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;

2. 创建用户

-- 创建用户  itcast , 只能在当前主机访问， 密码 123456
create user 'itcast'@'localhost' identified by '123456'; ## 默认没有权限
-- 创建用户 heima, 可以在任意主机访问数据库， 密码 wsdheima
create user 'heima'@'%' identified by 'wsdheima';

3. 修改用户密码

-- 修改密码 heima 为 123456
alter user 'heima'@'%' identified with mysql_native_password by '13246';

4. 删除用户

-- 删除用户
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';