基本SQL语句

定义

结构化查询语言(Structured Query Language)简称SQL(发音：/?es kju? ?el/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

分类

DML（Data Manipulation Language）数据操纵语言

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

DDL（Data Definition Language）数据库定义语言

如：create table之类

DCL（Data Control Language）数据库控制语言

如：grant、deny、revoke等，只有管理员才有相应的权限

DQL(Data Query Language）数据查询语言

SQL不区分大小写

数据库操作

数据库结构

建库

create database XXXXX default character set utf8;

创建数据库的同时使用utf-8

删库

drop database cbg2011;

查看所有数据库

show databases;

使用数据库

use cbg2011;

表的常用操作

创建表

create table tb_door(

id int primary key auto_increment,

door_name varchar(100),

tel varchar(50)

);

修改表

更改表结构

添加列

alter table tb_door add column money NUMERIC(7,2);

删除列

alter table tableName drop column columnName

删除表

drop table tb_door;

修改列类型

alter table tableName alter column columnName varchar(4000)

查看表结构

show tables; //查看所有表

desc tb_door; //描述表信息,数值类型

表数据操作

更改

插入记录

insert into tb_door values(null,‘永和大王1店‘,666);

insert into tb_door values(null,‘ 永和大王2店‘,888);

删除记录

Delete from tb_door where id=2;

查询记录

SELECT * FROM tb_door;

修改记录

update tb_door set tel=555 where id=1;

查询

distinct

使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

where

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename=‘tony‘ and deptno=2 --相当于两个条件的&关系

select * from emp where ename=‘tony‘ or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

统计案例

#2015年以前入职的老员工

SELECT * FROM emp WHERE DATE_FORMAT(hiredate,‘%Y-%m-%d‘)<‘2015-01-01‘; SELECT * FROM emp WHERE YEAR(hiredate)<2015

#2019年以后签约的员工，日期进行格式转换后方便比较

SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,‘%Y-%m-%d‘))<=2019;

注:

MySQL DATE_FORMAT() 函数

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法

DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。 ‘%Y-%m-%d‘

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),‘%b %d %Y %h:%i %p‘)
DATE_FORMAT(NOW(),‘%m-%d-%Y‘)
DATE_FORMAT(NOW(),‘%d %b %y‘)
DATE_FORMAT(NOW(),‘%d %b %Y %T:%f‘)

结果类似：

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

like

select * from emp where ename like ‘l%‘ --以l开头的

select * from emp where ename like ‘%a‘ --以a结束的

select * from emp where ename like ‘%a%‘ --中间包含a的

select * from emp where ename like ‘l__‘ --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的 select * from emp where (mgr is not null )--过滤字段值不为空的

between and

SELECT * FROM emp

select * from emp where sal<3000 and sal>10000

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

查看全部

Select count(*) from tb_door;

聚合 aggregation

根据一列统计结果 和 普通列一起参与结果显示时需要通过order by 来显示结果,不然报错 .

count

select count(*) from emp --选择全部显示, 但是mysql底层经过优化

select count(1) from emp --效果和*一样 ,但是会选择null

select count(comm) from emp --慢,只统计非NULL的,因为选择了指定列

max / min

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

order by

Select * from tb_door order by tel desc;

当结果中存在聚合列,且同时存在非聚合列,非聚合列必须出现在分组中，否则业务意义不对,

非聚合列需要作为筛选条件出现

having

having 子句类似where过滤返回的结果

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal) #查询条件,类似where,但是group by只能配合having

deptno出现的次数

SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno) #次数多的

数据类型

命名规则

1. 字段名必须以字母开头，尽量不要使用拼音
2. 长度不能超过30个字符（不同数据库，不同版本会有不同）
3. 不能使用SQL的保留字，如where,order,group
   只能使用如下字符az、AZ、0~9、$ 等
4. Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
5. 多个单词用下划线隔开，而非java语言的驼峰规则

字符

1. char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
2. varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。
3. 以utf8编码计算的话，一个汉字在u8下占3个字节

其中不同数据库版本长度限制可能会有不同

数字

1. tinyint,int整数类型
2. float,double小数类型
3. numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
4. decimal和numeric表示精确的整数数字

日期

1. date 包含年月日
2. time时分秒
3. datetime包含年月日和时分秒
4. timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

字段约束

主键约束

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

主键自增策略

当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK

基础函数

lower

SELECT ‘ABC‘,LOWER(‘ABC‘) from dept; --数据转小写

upper

select upper(dname) from dept --数据转大写

length

select length(dname) from dept --数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,‘123‘) X from dept --拼接数据

replace

select dname,replace(dname,‘a‘,‘666‘) X from dept --把a字符替换成666

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

–直接四舍五入取整

select comm,round(comm) from emp
–四舍五入并保留一位小数

select comm,round(comm,1) from emp
–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

uuid

SELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

now

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

转义字符

‘作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select ‘ab‘cd‘ -- 单引号是一个SQL语句的特殊字符

select ‘ab‘cd‘ --数据中有单引号时,用一个\转义变成普通字符

小结

char和varchar有什么区别？

char为定长字符串，char(n)，n最大为255

varchar为不定长字符串，varchar(n)，n最大长度为65535

char(10)和varchar(10)存储abc，那它们有什么差别呢？

char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。

datetime和timestamp有什么区别？

数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。

date 是 年与日

time是 时分秒

datetime年月日时分秒，存储和显示是一样的

timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数

中文乱码

如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：

set names utf8;

set names gbk;

设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。

那为何会造成乱码呢？

Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：

create database yhdb charset utf8;

但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。

我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。

注释

/* 很多注释内容 */

#行注释内容

-- 行注释内容，这个使用较多

主键、外键、唯一索引的区别？

• Primary Key 主键约束，自动创建唯一索引
• Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
• Unique Index 唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop、delete和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

入门sql语句

事务 transaction

概念

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行.

事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态.

事务特性 A C I D 4个

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1. 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
4. 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

事务隔离分为不同级别，包括

1. 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
2. 读提交（read committed） Oracle默认的隔离级别
3. 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
4. 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别

1. 开启事务：start transaction；
2. 结束事务：commit（提交事务）或rollback（回滚事务）

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消.

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

提交 commit

多条语句时，批量执行，事务提交

有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

mysql的事务默认就是开启的 -- 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; #关闭事务的自动提交

INSERT INTO user (id) VALUES(25);#成功

INSERT INTO user (id) VALUES(5);#已经存在5了，会失败

COMMIT; #手动提交事务

回滚 rollback

多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事务回滚，就不会再提交了

表强化

约束 constraints

非空约束 not null

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user(

id INT AUTO_INCREMENT,

NAME VARCHAR(30) UNIQUE NOT NULL,

age INT,

phone VARCHAR(20) UNIQUE NOT NULL,

email VARCHAR(30) UNIQUE NOT NULL,

PRIMARY KEY (id)

);

DESC tb_user;

id为自增主键，null值无效，数据库会自动用下一个id值替代

age因为运行为null，所以可以设置为null

INSERT INTO tb_user (id,age) VALUES(NULL,NULL);

外键约束 foreign key

sql