SQL语言
概述
SQL:Structure Query Language(结构化查询语言),SQL被美国国家标准局(ANSI)确定为关
系型数据库语言的美国标准,后来被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准,普通话
各数据库厂商在标准的基础上做了自己的扩展,方言
SQL 是一种标准化的语言,它允许你在数据库上执行操作,如创建项目,查询内容,更新内容,
并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。
提示:写完文章后,目录可以自动生成,如何生成可参考右边的帮助文档
SQL语句分类
- DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等。
- DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)增删改。
- DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别。
- DQL(Data Query Language):数据查询语言,用来查询记录(数据)查询。
一、DDL操作数据库
数据类型
常用数据类型:
int:整型
double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为
999.99;默认支持四舍五入
char:固定长度字符串类型; char(10) 'aaa ’ 占10位
varchar:可变长度字符串类型; varchar(10) ‘aaa’ 占3位
text:字符串类型,比如小说信息;
blob:字节类型,保存文件信息(视频,音频,图片);
date:日期类型,格式为:yyyy-MM-dd;
time:时间类型,格式为:hh:mm:ss
timestamp:时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
sql中的运算符:
(1)算术运算符:+,-,*,/(除法),求余(%)
示例:
5/2
5%2
2/5
2%5
(2)赋值运算符:=
注:赋值方向:从右往左赋值
示例: name=‘张三’
(3) 逻辑运算符:
and(并且),or(或者),not(取非)
作用:用于连接多个条件时使用
(4) 关系运算符:
,<,>=,<=,!=(不等于),=(等于),<>(不等于)
补充:查询所有数据:select * from 表名
提示:执行以下操作要先连接数据库服务器
mysql -u root -p //连接数据库命令
1 创建
CREATE DATABASE 语句用于创建新的数据库
示例:
//create database +数据库名
CREATE DATABASE mydb1; //代码意思:创建一个数据库,名为mydb1
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
2 查看数据库
查看当前数据库服务器中的所有数据库`:
示例:
show databases;
查看前面创建的mydb2数据库的定义信息
示例:
//show create database 数据库名;
Show CREATE DATABASE mydb2;
3、修改数据库`
语法格式: alter database 数据库名 character set 编码方式
作用:查看服务器中的数据库,并把mydb2的字符集修改为utf8;
示例:
ALTER DATABASE mydb2 character SET utf8;
4 删除数据库
语法格式: drop database 数据库名
示例:
DROP DATABASE mydb3;
5 其他语句
查看当前使用的数据库
Select database();
切换数据库: use 数据库名
示例:
USE mydb2;
二、 DDL操作表
提示:执行以下操作要先切换到要操作的数据库
切换数据库代码提示:use+数据库名
1.CREATE TABLE语句用于创建新表
语法格式:CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
说明:表名,列名是自定义,多列之间使用逗号间隔,最后一列的逗号不能写
[约束] 表示可有可无
代码如下(示例):
create table student(id int,name varchar(3),age char(1));
2.表的操作
代码如下(示例):
语法格式:drop table 表名;
DROP TABLE table_name;
1、当前数据库中的所有表
代码如下(示例):
show tables;
2、查看表的字段信息
该处使用的url网络请求的数据。
3、增加列:在上面员工表的基本上增加一个image列。
语法格式:alter table 表名 add 新列名 新的数据类型
代码如下(示例):
ALTER TABLE employee ADD image blob;
4、修改job列,使其长度为60。
语法格式:alter table 表名 change 旧列名 新列名 新的数据类型
代码如下(示例):
ALTER TABLE employee MODIFY job varchar(60);
ALTER TABLE employee change job job varchar(60);
5、列名name修改为username
代码如下(示例):
ALTER TABLE user CHANGE name username varchar(100);
6、删除image列,一次只能删一列
语法格式: alter table 表名 drop 列名
代码如下(示例):
ALTER TABLE bnm DROP image;
7、修改表名,表名改为user
语法格式: alter table 旧表名 rename 新表名;
代码如下(示例):
alter table bnm rename users;
8、查看表格的创建细节
语法格式:show create table 表名;
代码如下(示例):
SHOW CREATE TABLE bnm;
9、修改表的字符集为ktm
语法格式: alter table 表名 character set 编码方式
代码如下(示例):
ALTER TABLE user CHARACTER SET ktm;
三、DML操作
提示:DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
主要包括:INSERT 、UPDATE、 DELETE
小知识:
在mysql中,字符串类型和日期类型都要用单引号括起来。
空值:null
1、添加操作:INSERT
(1)插入操作:INSERT:
语法格式:insert into 表名(列名) values(数据值);
代码如下(示例):
insert into student(stuname,stuage,stusex,birthday) values('张三1',18,'a','2000-
1-1');
- 注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
– 非数值的列值两侧需要加单引号
(2)- 同时添加多行
语法格式:insert into 表名(列名) values(第一行数据),(第二行数据),(),();
代码如下(示例):
insert into student(stuname,stuage,stusex,birthday)
values('张三3',18,'a','2000-1-1'),
('张三4',18,'a','2000-1-1'),
('张三5',18,'a','2000-1-1'),
('张三6',18,'a','2000-1-1'),
('张三7',18,'a','2000-1-1'),
('张三8',18,'a','2000-1-1');
注意:列名与列值的类型、个数、顺序要一一对应。
参数值不要超出列定义的长度。
如果插入空值,请使用null
插入的日期和字符一样,都使用引号括起来。
查询表中所有数据:select * from 表名
2、修改(更新)操作:UPDATE:
语法:UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值
代码如下(示例):
//在emp表中把name列中=zhngsan对应的salary列的数据改成原数值加1000
update emp set salary=salary+1000 where name='zhangsan';
3、删除操作:
(1)删除操作:DELETE::
语法 : DELETE from 表名 【WHERE 列名=值】
代码如下(示例):
//删除emp表的数据但表面还在
delete from emp;
//删除emp表中name列中等于张三的一列数据
delete from emp where name='张三';
(2)删除操作:TRUNCATE:
语法 : TRUNCATE from 表名 【WHERE 列名=值】
代码如下(示例):
//删除是把emp表直接DROP掉,然后再创建一个同样的新表。
truncate table emp;
两者区别:
- DELETE 删除表中的数据,表结构还在;删除后的数据可以找回
- TRUNCATE 删除是把表直接DROP掉,然后再创建一个同样的新表。
- 删除的数据不能找回。执行速度比DELETE快。
4、DCL权限安全
权限字符:
项目 | Value | 权限 |
---|---|---|
CREATE | 数据库表或索引 | 创建数据库、表或索引权限 |
DROP | 数据库或表 | 删除数据库或表权限 |
GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 |
REFERENCES | 数据库或表 | |
ALTER | 表 | 更改表,比如添加字段、索引等 |
DELETE | 表 | 删除数据权限 |
INDEX | 表 | 索引权限 |
INSERT | 表 | 插入权限 |
SELECT | 表 | 查询权限 |
UPDATE | 表 | 更新权限 |
CREATE VIEW | 视图 | 创建视图权限 |
SHOW VIEW | 视 | 查看视图权限 |
ALTER ROUTINE | 存储过程 | 更改存储过程权限 |
CREATE ROUTINE | 存储过程 | 创建存储过程权限 |
EXECUTE | 存储过程 | 执行存储过程权限 |
FILE | 服务器主机上的文件访问 | 文件访问权限 |
CREATE TEMPORARY TABLES | 服务器管理 | 创建临时表权限 |
LOCK TABLES | 服务器管理 | 锁表权限 |
CREATE USER | 服务器管理 | 创建用户权限 |
PROCESS | 服务器管理 | 查看进程权限 |
RELOAD | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
REPLICATION CLIENT | 服务器管理 | 复制权限 |
REPLICATION SLAVE | 服务器管理 | 复制权限 |
SHOW DATABASES | 服务器管理 | 查看数据库权限 |
SHUTDOWN | 服务器管理 | 关闭数据库权限 |
SUPER | 服务器管理 | 执行kill线程权限 |
1、创建用户:
语法格式:create user 用户名@指定ip identified by 密码;
代码如下(示例):
//用户@ip //密码
create user ywh@locahost identified by 'wenhao';
2、用户授权:
语法格式:grant 权限1,权限2,…,权限n on 数据库名.* to 用户名@IP;
代码如下(示例):
grant select,insert,update,delete,create on mydb1.* to 'ywh'@locahost;
3、用户权限查询:
语法格式:show grants for 用户名@IP;
代码如下(示例):
show grants for 'ywh'@locahost;
4、撤销用户权限:
语法格式:revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP;
代码如下(示例):
revoke select on mydb1.* from 'ywh'@iocahost;
5、删除用户:
语法格式:drop user 用户名@IP;
代码如下(示例):
drop user 'ywh'@locahost;
五、DQL数据查询
查询关键字:SELECT
语法: SELECT 列名 FROM 表名 【WHERE --> BROUP BY–>HAVING–> ORDER BY】
- 表示所有列
- SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /行条件/
GROUP BY grouping_columns /对结果分组/
HAVING condition /分组后的行条件/
ORDER BY sorting_columns /对结果分组/
LIMIT offset_start, row_count /结果限定/
1、 简单查询
查询表中所有列
代码如下(示例)
//表名
SELECT * FROM stu;
查询指定列
代码如下(示例)
// 三个列名 //表名
SELECT sid, sname, age FROM stu;
2 、条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
=(等于)、!=(不等于)、<>(不等于)、<、<=、>、>=; BETWEEN…AND(在两个条件之间); IN(set)(在什么之内); IS(是) NULL(空); AND(并且);OR(或者); NOT(不);
(1)查询stu表中gender列中为famale,并且age小于50的数据
SELECT * FROM stu WHERE gender='female' AND age<50;
(2)在stu表中查询sid为S_1001,或者sname为liSi的数据
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';
(3)在stu表中查询sid不是S_1001,S_1002,S_1003的数据
SELECT * FROM tab_student WHERE sid NOT IN('S1001','S1002','S_1003');
(4)在stu表中查询age为null的数据
SELECT * FROM stu WHERE age IS NULL;
(5)在stu表中查询age在20到40之间的数据
SELECT * FROM stu WHERE age>=20 AND age<=40;
或者:列名 between 开始值 and 结束值;//注意:1.开始值<结束值 2.包含临界值的
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
(6) 在stu表中查询gender非male的数据
SELECT * FROM stu WHERE gender!='male';
或
SELECT * FROM stu WHERE gender<>'male';
或
SELECT * FROM stu WHERE gender<>'male';
(7) 在stu表中查询snmae不为null的数据
SELECT * FROM stu WHERE NOT sname IS NULL;
或
SELECT * FROM stu WHERE sname IS NOT NULL;
3 、模糊查询
语法: 列名 like ‘表达式’ //表达式必须是字符串
通配符:
_(下划线): 任意一个字符
%:任意0~n个字符,‘张%’
(1)在stu表中查询sname列中由3个字构成的数据
SELECT * FROM stu WHERE sname LIKE '___';
(2)在stu表中查询sname列中由5个字母构成,并且第5个字母为“i”的数据
SELECT * FROM stu WHERE sname LIKE '____i';
(3)在stu表中查询sname列中以“z”开头的数据
SELECT * FROM stu WHERE sname LIKE 'z%';
(4)在stu表中查询sname中第2个字母为“i”的数据
SELECT * FROM stu WHERE sname LIKE '_i%';
(5)在stu表中查询sname中包含“a”字母的数据
SELECT * FROM stu WHERE sname LIKE '%a%';
4 、字段控制查询
(1)去除重复数据进行显示
需要使用关键字:DISTINCT
代码如下(示例)
//列名 //表名
SELECT DISTINCT hj FROM ktm;
(2)查看其中两列之和
//在emp表中查看sal列和comm列之和
SELECT *,sal+comm FROM emp;
comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
SELECT *,sal+IFNULL(comm,0) FROM emp;
(3)给相加的两列名添加别名
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时,是可以省略AS关键字的:
SELECT *,sal+IFNULL(comm,0) total FROM emp;
在代码中如果不加 ’ *,'的符号则只显示两列结果的那一列
SELECT sal+IFNULL(comm,0) total FROM emp
5 、排序
语法: order by 列名 asc/desc;
asc 升序 desc 降序 默认不写的话是升序
(1) 查询ktm表中所有数据,age列按升序排序
SELECT * FROM ktm ORDER BY age ASC;
(2) 查询ktm表中所有数据,sal列按升序排序,如果sal列相同时,则empno列按升序排序。
多列排序:当前面的列的值相同的时候,才会按照后面的列值进行排序
SELECT * FROM ktm ORDER BY sal DESC,empno ASC;
6、 聚合函数
聚合函数是用来做纵向运算的函数:
COUNT(列名):统计指定列不为NULL的记录行数;
MAX(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
(1) COUNT
当需要纵向统计时可以使用COUNT()。
1、 查询emp表中记录数:
就是 查询表中有几行数据
select count(*) from ktm;
2、查询ktm表中comm列中有几行数据:
SELECT COUNT(comm) cnt FROM ktm;
注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。
3、 查询emp表中sal列大于2500的数据:
SELECT COUNT(*) FROM emp WHERE sal > 2500;
4、在emp表中查询有comm的数据,以及有mgr的数据:
SELECT COUNT(comm), COUNT(mgr)FROM emp;
5、SUM
查询KTM表中sal列所有数据的之和:
SELECT SUM(sal) FROM KTM;
6、AVG
统计KTM表中sal列中所有数据的平均值:
SELECT SUM(sal) FROM KTM;
7、MAX
查询ktm表sal列中最大值
SELECT MAX(sal) FROM KTM;
8、MIN
查询ktm表sal列中最小值
SELECT MIN(sal) FROM KTM;
7 、分组查询
当需要分组查询时需要使用关键字:GROUP BY
有分组操作,则select后面能添加的只能是聚合函数和被分组的列名
1、查询deptno列的数据中对应sal列之和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
2、查询deptno列的数据中对应sal列大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
HAVING子句
注:having与where的区别:
**1、**having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2、.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分
组;而HAVING是对分组后数据的约束。
查询工资(sal)总和大于9000的部门编号(deptno)以及工资和:
SELECT deptno, SUM(sal) FROM emp GROUPBY deptno HAVING SUM(sal) > 9000;
LIMIT
LIMIT用来限定查询结果的起始行,以及总行数。
查询5行记录,起始行从0开始:
SELECT * FROM emp LIMIT 0, 5;