SQL
SQL语句分类
- Data Definition Language (DDL 数据定义语言) 如:建库,建表。
- Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改。
- Data Query Language(DQL 数据查询语言),如:对表中的查询操作。
- Data Control Language(DCL 数据控制语言),如:对用户权限的设置。
SQL语法
-
每条语句以分号结尾。
-
SQL中不区分大小写,关键字中认为大写和小写是一样的。
-
有三种注释:
-
--空格
单行注释 -
/**/
多行注释 -
#
mysql特有的注释方式
-
DDL
DDL操作数据库
#创建数据库的几种方式
# 创建数据库
CREATE DATABASE 数据库名;
# 判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
# 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
# 查看数据库
# 查看所有的数据库
SHOW DATABASES;
# 查看某个数据库的定义信息
SHOW CREATE DATABASE 数据库名;
#修改数据库
#修改数据库的默认字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
#删除数据库
#删除一个数据库
DROP DATABASE 数据库名;
#使用数据库
#查看正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
#使用/切换数据库
USE 数据库名;
DDL操作表结构
# 创建表
#创建一张数据表
CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);
# 创建一个表结构相同的表
CREATE TABLE 新表名 LIKE 旧表名;
# 查看表
# 查看某个数据库中的所有表
SHOW TABLES;
# 查看表结构
DESC 表名;
# 查看创建表的SQL语句
SHOW CREATE TABLE 表名;
# 删除表
# 直接删除表
DROP TABLE 表名;
# 判断表是否存在,如果存在则删除表
DROP TABLE IF EXISTS 表名;
# 修改表结构
# 添加表列
ALTER TABLE 表名 ADD 列名 类型;
#修改表列的类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
# 修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
# 删除列 DROP
ALTER TABLE 表名 DROP 列名;
# 修改表名
RENAME TABLE 表名 TO 新表名;
# 修改字符集 character set
ALTER TABLE 表名 character set 字符集;
DML
用于对表中的数据进行增删改操作。
# 插入表记录
# 插入全部字段
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3); # 所有字段名都写出来
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…); # 不写字段名
# 插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...); #没有添加数据的字段会使用NULL
# 更新表记录
# 不带条件修改数据
UPDATE 表名 SET 字段名=值; # 修改所有的行
# 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
# 删除表记录
# 不带条件删除数据
DELETE FROM 表名;
# 带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
# 使用truncate删除表中所有记录
TRUNCATE TABLE 表名; # 相当于删除表的结构,再创建一张表
DQL
查询不会对数据库中的数据进行修改,只是一种显示数据的方式。
# 简单查询
# 查询所有行和列的数据
SELECT * FROM 表名;
# 查询指定列
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
# 指定列的别名进行查询
# 使用别名的好处: 显示的时候使用新的名字,并不修改表的结构。
# 对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
# 对列和表同时指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
# 清除重复值
# 查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;
# 查询结果参与运算(必须是数值类型)
# 某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;
# 某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名;
# 条件查询
# 条件查询的语法
SELECT 字段名 FROM 表名 WHERE 条件;
# in关键字
SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
# 范围查询
# 范围查询语法
SELECT 字段名 FROM 表名 WHERE 字段 BETWEEN 值 1 AND 值 2;
# like关键字
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
# % 匹配任意多个字符串
# - 匹配一个字符
排序
# 排序
# 单列排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC][DESC];
# ASC:升序,默认值; DESC:降序
# 组合排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1[ASC][DESC], 字段名2[ASC][DESC];
聚合
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数
max(列名)
求这一列的最大值min(列名)
求这一列的最小值avg(列名)
求这一列的平均值count(列名)
统计这一列有多少条记录sum(列名)
对这一列求总和
# 聚合函数语法
SELECT 聚合函数(列名) FROM 表名;
# 统计个数需要加上NULL
SELECT 聚合函数(IFNULL(列名,默认值)) FROM 表名;
# 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。
使用举例:
# 查询年龄大于 20 的总数
select count(*) from student where age>20;
# 查询数学成绩总分
select sum(math) 总分 from student;
# 查询数学成绩平均分
select avg(math) 平均分 from student;
# 查询数学成绩最高分
select max(math) 最高分 from student;
# 查询数学成绩最低分
select min(math) 最低分 from student;
分组
分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组。一般分组会跟聚合函数一起使用。
# 分组语法
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
使用举例:
# 查询男女各多少人
# (1) 查询所有数据,按性别分组。
# (2) 统计每组人数。
select sex, count(*) from student3 group by sex;
# 查询年龄大于 25 岁的人,按性别分组,统计每组的人数。
# (1) 先过滤掉年龄小于 25 岁的人。
# (2) 再分组。
# (3) 最后统计每组的人数。
select sex, count(*) from student3 where age > 25 group by sex;
having
having
与 where
的区别:
where 子句
- 对查询结果进行分组前,将不符合 where 条件的行去掉,即在分组之前过滤数据,即先过滤再分组。
- where 后面不可以使用聚合函数。
having 子句
- having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。
- having 后面可以使用聚合函数。
limit
LIMIT 是限制的意思,所以 LIMIT 的作用就是限制查询记录的条数。
# limit
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子
句][LIMIT 子句];
# limit语法格式
LIMIT offset,length;
# offset:起始行数,从 0 开始计数,如果省略,默认就是 0
# length: 返回的行数
使用举例:
# 查询学生表中数据,从第 3 条开始显示,显示 6 条
select * from student3 limit 2,6;
# 如果第一个参数是 0 可以省略写:
select * from student3 limit 5;
# 最后如果不够 5 条,有多少显示多少
select * from student3 limit 10,5;
DCL
创建用户
# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# '用户名':将创建的用户。
# '主机名':指定该用户在哪个主机上可以登陆,如果是本地用户可用 localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%。
# '密码':该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。
举例:
# 创建 user1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123
create user 'user1'@'localhost' identified by '123';
# 创建 user2 用户可以在任何电脑上登录 mysql 服务器,密码为 123
create user 'user2'@'%' identified by '123';
给用户授权
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
# GRANT...ON...TO: 授权关键字
# 权限: 授予用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
# 数据库名.表名: 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
# '用户名'@'主机名': '用户名'@'主机名'
撤销权限
REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机名';
# REVOKE…ON…FROM: 撤销授权的关键字
# 权限: 用户的权限,如 CREATE、ALTER、SELECT、INSERT、UPDATE 等,所有的权限则使用 ALL
# 数据库名.表名: 对哪些数据库的哪些表,如果要取消该用户对所有数据库和表的操作权限则可用*表示,如*.*
# '用户名'@'主机名': 给哪个用户撤销
查看权限
SHOW GRANTS FOR '用户名'@'主机名';
删除用户
DROP USER '用户名'@'主机名';
修改管理员密码
mysqladmin -uroot -p password 新密码
修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');
# 注意:需要在登陆 MySQL 的情况下操作,新密码要加单引号