目录
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SOL语句不区分大小写,关键字建议使用大写
- 注释:
- 单行注释:-- 注释内容或 #注释内容(MySQL特有)
- 多行注释:
/*注释内容*/
SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL-数据库操作
-
查询
-
查询所有数据库
SHOW DATABASES;
-
查询当前数据库
SELECT DATABASE()
-
-
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
-
删除
DROP DATABASE [IF EXISTS] 数据库名;
-
使用
USE 数据库名;
DDL-表操作-查询
-
查询当前数据库所有表
SHOW TABLES;
-
查询表结构
DESC 表名;
-
查询指定表的建表语句
SHOW CREATE TABLE 表名;
DDL-表操作-创建
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
......
字段n 字段n类型[COMMENT 字段n注释],
)[COMMENT 表注释];
注意:最后一个字段不需要加逗号。
字段类型
- int:整数数据类型
- numeric:数字类型
- float:浮点类型
- bit:逻辑型数据类型,即是/否,0/1
- date:年月日
- datetime:混合日期和时间值
- char(10):定长字符串,固定长度
- varchar(50):字符串类型,长度可以更改,主要用于存取少量文本数据
- varchar(MAX):字符串类型,主要用于存取大量文本、图片、动画等数据
create table userinfo(
id int comment '编号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';
DDL-表操作-修改
- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT注释] [约束];
ALTER TABLE user ADD nickname varchar(20) comment'昵称';
- 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
alter table userinfo change nickname username varchar(30) comment"昵称";
- 删除字段
ALTER TABLE 表名 DROP 字段名;
alter table userinfo drop username;
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
alter table user rename to usertest;
DDL-表操作-删除
- 删除表
DROP TABLE [IF EXISTS] 表名;
- 删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
注意:在删除表时,表中的全部数据也会被删除。
DML-介绍
DML英文全称是Data Manipulation Lanquage(数据操作语言),用来对数据库中表的数据记录进行增删改操作。
- 添加数据 ( insert )
- 修改数据 ( update )
- 删除数据 ( delete )
DML-添加数据
- 给指定字段添加数据
sqlINSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);
- 给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2);
- 批量添加数据
INSERT INTO 表名(字段名1,字段名2) VALUES(值1,值2),(值1,值2),(值1,值2),(值1,值2);
INSERT INTO 表名 VALUES(值1,值2),(值1,值2),(值1,值2);
注意:
-
插入数据时,指定的字段顺序需要与值的顺序时一一对应的。
-
字符串和日期型数据应该包括在引号中。
-
插入的数据大小,应该在字段的规定范围内。
DML-修改数据
UPDATE 表名 SET 字段名1=值1,字段名1=值1,...[WHERE 条件];
update userinfo set name='张三' where id=1;
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
DML-删除数据
DELETE FROM 表名 [WHERE 条件]
注意:
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。
DQL-介绍
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
DQL-基本查询
- 查询返回多个字}段
SELECT 字段1,字段2,字段3 FROM 表名;
SELECT * FROM 表名;
- 设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]FROM 表名;
select name as '姓名' address as '地址' from userinfo;
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL-条件查询
- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN … AND … | 在某个范围之内(含最小、最大值)(规则只能写从小到大) |
IN(…) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是null |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
查询没有年龄信息的用户(没有信息就是null)
SELECT * FROM newuser WHERE age IS NULL;
查询有年龄信息的用户
SELECT * FROM newuser WHERE age IS NOT NULL;
查询年龄在15(包含)岁到20(包含)岁之间的员工信息
SELECT * FROM newuser WHERE age >= 15 && age <= 20;
SELECT * FROM newuser WHERE age BETWEEN 15 AND 20;
查询年龄为19且id为7的用户
SELECT * FROM newuser WHERE age = 19 && id = 7;
查询年龄等于18或28或40的用户
SELECT * FROM newuser WHERE age = 18 or age = 28 or age = 40;
SELECT * FROM newuser WHERE age in(18,28,40);
查询姓名为两个字的用户
SELECT * FROM newuser WHERE name like '_ _';
查询姓名最后一个字是文的用户
SELECT * FROM newuser WHERE name like '%文';
DQL-聚合函数
-
介绍
- 将一列数据作为一个整体,进行纵向计算。
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法 ( 注意:null 值不参与所有聚合函数运算 )
SELECT 聚合函数(字段列表) FROM 表名;
统计该表中用户数量
SELECT COUNT(*) FROM newuser;
SELECT COUNT(id) FROM newuser;
统计该表中用户平均年龄
SELECT avg(age) FROM newuser;
统计该表中用户最大年龄
SELECT max(age) FROM newuser;
统计石家庄市员工年龄之和
SELECT sum(age) FROM newuser where address = '石家庄市';
DQL-分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
- where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组,而having是分组之后对结果进行过滤
- 判断条件不同: where不能对聚合函数进行判断,而having可以。
根据性别分组,统计男性员工 和 女性员工的数量
select gender , count(*) from userinfo group by gender;
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress,count(*) from userinfo where age < 45 group by workaddress having count(*) >=3;
注意:
-
执行顺序:where > 聚合函数 > having
-
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
DQL-排序查询
- 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式,字段2 排序方式2;
SELECT * FROM workinfo_table ORDER BY age DESC;
- 排序方式
- ASC:升序(默认值)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段经进行排序。
DQL-分页查询
SELECT 字段列表 FROM 表名 LIMT 起始索引,查询记录数;
注意:
-
起始索引从0开始,起始索引 = (查询页码 -1)* 每页显示记录数。
-
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中式LIMIT。
-
如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10
DCL-介绍
DCL英文全称是Data ControlLanquage(数据控制语言),用来管理数据库 用户、控制数据库的访问 权限。
DCL-用户管理
- 查询用户
USE mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_passworld BY '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
创建用户 testa 只能够在当前主机local host访问,密码123456
create user 'testa'@'localhost' identified by '123456';
创建用户 testb 可以在任意主机访问该数据库 密码123456
create user 'testb'@'%' identified by '123456';
修改用户 testa 的访问密码为1234
alter user 'testa'@'%' identified with mysql_native_passworld by '1234';
删除用户 testb
drop user 'testb'@'localhost';
DCL-权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
权限 | 说明 |
---|---|
ALL,ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
grant all on userinfo.* to 'testa'@'%';
- 撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';
revoke all on userinfo.* from 'testa'@'%';
注意:
- 多个权限之间,使用逗号分隔
- 授权时,数据库名和表名可以使用*进行通配,代表所有