SQL常用场景&语句

目录

• SQL常用场景&语句
  • • 一、显示命令
    • 二、建库建表&删库删表
    • 三、基础数据检索
    • 四、运算符
      • 算数运算符
      • 比较运算符
      • 逻辑运算符
      • 位运算符
    • 五、数据过滤
      • 使用where子句
      • 使用模糊查询&通配符
      • 使用正则表达式
      • 子查询
    • 六、排序与限制
    • 七、修改表数据&更新表
    • 八、数据处理函数
    • 九、分组查询
    • 十、连接查询
      • 1. 内连接（join 或 inner join）
      • 2. 左外连接（left join）
      • 3. 右外连接（right join）
      • 4. 完全连接(full join)
    • 十一、集合查询
      • union/union all
    • 十二、数据库导出、导入
    • 十三、DB密码与用户

一、显示命令

显示数据库列表:

show databases;

显示库中的数据表:

use dbname;
show tables;

显示数据表的结构:

describe tablename;
show columns from tablename;

二、建库建表&删库删表

建库：

create database dbname;

建表：

create table tabelname (字段设定);

删库：

drop database dbname;

删表：

drop table tablename;

将表中记录清空：

-- delete仅清空表内数据，自增id会被保留（下次继续从这个自增id开始）
delete from tablename;

-- truncate会把自增id一起清除，truncate成功会返回0
truncate table tablename；

MySQL中drop、delete、truncate的异同

三、基础数据检索

检索所有数据：

select * from tablename

检索单（多）个列：

select column1 from tablename;
select column1, column2 from tablename;

查看当前时间：

-- mysql
NOW();	-- 使用方式 SELECT NOW(); 输出格式YYYY-MM-DD HH:MM:SS 
CURRENT_DATE() / CURDATE() ;	-- 使用方式 SELECT CURRENT_DATE() 或 SELECT CURDATE(); 输出格式YYYY-MM-DD
CURRENT_TIME() / CURTIME() ;	-- 使用方式 SELECT CURRENT_TIME() 或 SELECT CURTIME(); 输出格式HH:MM:SS 

-- 例子：可以在建表的时候，让当前时间作为默认值填入表记录
CREATE TABLE Orders
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)
-- 如此做，在插入数据时，会没填入OrderDate会自动填入当前时间

-- sql server
select getdate(）

查询并为字段换名：

select column1 as 列1 from tablename;

四、运算符

算数运算符

字符	说明
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余数

SELECT 1+2, 1-2, 1*2, 10/4, 10%4, 10 DIV 4, 10 MOD 4;

比较运算符

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
IS NULL	为空
IS NOT NULL	不为空

比较运算符经常与where搭配使用，在下一节的数据过滤中再展开，这里主要讲一下<=>。

<=>是安全等于，他为NULL安全的等值比较，NULL的值是没有任何意义的。所以=号运算符不能把NULL作为有效的结果。其他功能类似=。

-- =
select null=null;

+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

-- <=>
select null<=>null;

+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

select null<=>1;

+----------+
| null<=>1 |
+----------+
|        0 |
+----------+

<=>与=的区别：与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

<=>作用：

• 可以判断一个值是否非空，例如1 IS NULL可以等价为1<=>NULL。
• 当两个操作数都有可能出现NULL值并且需要比较一致时。例如存在a，b两个可能存在NULL值的列需要进行相等比较时，可以使用a<=>b

逻辑运算符

和比较运算符一样，常用于where子句中

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

运算符号	作用
&	按位与
|	按位或
^	按位异或
~	取反
<<	左移
>>	右移

-- 位与运算符 &
SELECT 10 & 15,9 & 4 & 2;
+---------+-----------+
| 10 & 15 | 9 & 4 & 2 |
+---------+-----------+
|      10 |         0 |
+---------+-----------+

-- 位异或运算符 ^
SELECT 10^15,1^0,1^1;
+-------+-----+-----+
| 10^15 | 1^0 | 1^1 |
+-------+-----+-----+
|     5 |   1 |   0 |
+-------+-----+-----+

-- 位左移运算符 <<
SELECT 1<<2,4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
|    4 |   16 |
+------+------+

-- 位右移运算符 >>
SELECT 1>>1,16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
|    0 |     4 |
+------+-------+

-- 位取反运算符 ~
SELECT ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1                   | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 |                     1 |
+----------------------+-----------------------+

五、数据过滤

使用where子句

-- 使用where子句，可以用等号来选择查询条件，也可以搭配各种运算符来检索
select * from tablename where column1 = 100;

-- 与比较运算符搭配
select * from tablename where column1 > 100;

select * from tablename where column1 not in ('A', 'B');

select * from tablename where column1 between 30 and 50;

select * from tablename where column1 is not null;

-- 与逻辑运算符搭配
select * from tablename where column1 > 100 and column2 < 100;

select * from tablename where column1 > 100 or column2 < 100;

select * from tablename where column1 not 100;

-- 与位运算符搭配
select * from tablename where column1&15 > 0;  
-- column1与15进行位运算，看是否大于0，column1的数据应该是整型。
-- 假设有column1中有一个数字是10，10 的补码为 1010，15 的补码为 1111，按位与运算之后，结果为 1010，即整数 10，这行数据就会被筛选出来。
-- 这种场景可以用于批量指定查询条件

使用模糊查询&通配符

-- 使用like操作符

select * from tablename where name like 'H%'	-- 以H开头
select * from tablename where name like '%ka%' -- 包含ka
select * from tablename where name like 'Hika__' -- 下划线_通配符(用途和%一样,不过_只匹配单个字符)

使用正则表达式

-- 使用正则表达式需要用REGEXP关键字，并在REGEXP后面跟上正则表达式内容
select * from tablename where name regexp 'Hikari';
select * from tablename where name regexp 'H.';
select * from tablename where name regexp 'Hikari|Irakih'	-- OR匹配

子查询

• 子查询也称作内查询或者嵌套查询
• 先于主查询被执行，其结果将作为外层查询的条件
• 在增删改查中都可以使用子查询，支持多层嵌套

-- 查询时常与in一起使用
select * from tablename where column1 in (select * from tablename where column2 = 'aaa');

-- 在插入中使用
insert into tablename2 * from tablename1 where column1 in (select * from tablename where column2 = 'aaa');

-- 在修改中使用
update tablename2 set column3=101 where column1 in (select * from tablename where column2 = 'aaa');

-- 在删除中使用
delete from tablename2  where column1 in (select * from tablename where column2 = 'aaa');

六、排序与限制

使用order by排序数据：

select * from tablename order by column;		-- 默认按升序排序
select * from tablename order by column ASC;	-- 升序排序
select * from tablename order by column DESC;	-- 降序排序
select * from tablename order by column1 DESC,column2 DESC;	-- 多字段排序

限制结果条目：select column1，column2，... from 库名 limit 位置偏移量

select * from tablename limit 3;  	-- 获取前3行数据
select * from tablename limit 3, 3; -- 3，3表示从第三行开始数，显示后三行
select * from tablename limit 3 offset 3; -- 等价与上一条

1. limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
2. limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

七、修改表数据&更新表

修改表中数据：

update tablename set column1=100 where name=’Hikari';

往表中插入记录：

insert into tablename values ('xxx', 'xxx', 'xxx'); -- 这里要能和表头一一对上, 一定要与字段在表中定义的顺序一致
insert into tablename (column1, column2, column3) values ('xxx', 'xxx', 'xxx'); 

在表中增加字段：

alter table tablename add column4 int(4) default 0;

修改字段：

-- 修改字段类型
alter table tablename modify column2 char(10);

-- 修改字段名称:ALTER  TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;
alter table tablename change column3 name varchar(16) not null comment '名称';

-- 修改字段默认值
alter table tablename alter column1 set default 100;

-- 删除默认值
alter table tablename alter column1 drop default;

重命名数据表：

alter table tablename rename tablerename;
-- 或
rename table tablename1 to tablerename1, tablename2 to tablerename2;

删除字段：

alter table tablename dorp column4;

修改数据表类型：

alter table tablename engine=MYISAM;

八、数据处理函数

文本处理函数：

LEFT() 返回串左边的字符
LENGTH() 返回串的长度
LOCATE() 找出串的一个子串
LOWER() 将串转换为小写
LTRIM() 去掉串左边的空格
RIGHT() 返回串右边的字符
RTRIM() 去掉串右边的空格
SOUNDEX() 返回串的SOUNDEX值
SUBSTRING() 返回子串的字符
UPPER() 将串转换为大写

日期和时间处理函数：

ADDDATE() 增加一个日期（天、周等）
ADDTIME() 增加一个时间（时、分等）
CURDATE() 返回当前日期
CURTIME() 返回当前时间
DATE() 返回日期时间的日期部分
DATEDIFF() 计算两个日期之差
DATE_ADD() 高度灵活的日期运算函数
DATE_FORMAT() 返回一个格式化的日期或时间串
DAY() 返回一个日期的天数部分
DAYOFWEEK() 对于一个日期，返回对应的星期几
HOUR() 返回一个时间的小时部分
MINUTE() 返回一个时间的分钟部分
MONTH() 返回一个日期的月份部分
NOW() 返回当前日期和时间
SECOND() 返回一个时间的秒部分
TIME() 返回一个日期时间的时间部分
YEAR() 返回一个日期的年份部分

SELECT DATE("2022-04-09");    -- 2022-04-09
SELECT DATEDIFF('2001-01-01','2001-02-02') -- -32

数值处理函数：

ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
MOD(x,y) 返回除操作的余数
PI() 返回圆周率
RAND() 返回一个随机数
ROUND() 返回离 x 最近的整数
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切
POW(x, y) / POWER(x,y) 返回 x 的 y 次方　

SELECT ABS(-1) -- 返回1
SELECT COS(2);
SELECT PI() -- 3.141593
SELECT POW(2,3) -- 8
SELECT RAND() -- 0.93099315644334
SELECT ROUND(1.23456) -- 1

聚集函数：

AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

SELECT COUNT(*) FROM TABLE;//总行数
SELECT COUNT(字段) FROM TABLE;//字段不为NULL的行数
SELECT SUM(价格字段) FROM TABLE;//字段值总和
SELECT AVG(价格字段) FROM TABLE;//字段值平均值
SELECT MAX(价格字段) AS '最大价格' FROM TABLE;//字段最大值
SELECT MIN (价格字段) AS '最小价格' FROM TABLE;//字段最小值

注：

1.当聚集函数遇到空值时，除了count(*)外，都跳过空值而只处理非空值。

2.聚集函数只能用于select子句和group by中的having子句。

九、分组查询

• 使用GROUP BY语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组

-- 计算各个名字出现的次数
select count(*), name from tablename group by name;

-- 与 order by 一起使用
select count(*), column2 from tablename group by column2 order by column2 desc

-- 使用with rollup
-- 比如统计一个用户（username）的登录次数（signin_count）
select username, sum(signin_count) from user_table group by username with rollup;
-- 查询的最后一行记录了总登录次数（所以username那里时NULL）

十、连接查询

1. 内连接（join 或 inner join）

INNER JOIN 与 JOIN 是相同的。内连接只会返回符合条件的行，对于无法匹配的行则不返回。

示意图：

select t1.column1 from tablename1 as t1 inner join tablename2 as t2 on t1.column2 = t2.column2 -- 这里的inner join写成join也一样

2. 左外连接（left join）

从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

示意图：

select t1.column1 from tablename1 as t1 left join tablename2 as t2 on t1.column2 = t2.column2

3. 右外连接（right join）

从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

示意图：

select t1.column1 from tablename1 as t1 right join tablename2 as t2 on t1.column2 = t2.column2

4. 完全连接(full join)

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。

示意图：

select t1.column1 from tablename1 as t1 full join tablename2 as t2 on t1.column2 = t2.column2

十一、集合查询

union/union all

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

select column1 from tablename1
union
select column2 from tablename2

union和union all的区别在于前者会去掉重复元素，并会对产生的结果排序（因为union是排完序再去重），后者则简单的合并结果后就返回了。

交集、差集用where就能实现

十二、数据库导出、导入

导出数据库/表：

/* 格式
1. 导出所有数据库
mysqldump -u [数据库用户名] -p -A>[备份文件的保存路径]
或
mysqldump -u [数据库用户名] -p --all-databases>[备份文件的保存路径]

2. 导出指定数据库
mysqldump -u [数据库用户名] -p [数据库名]>[备份文件的保存路径]

3. 只导出数据，不导出数据结构
mysqldump -u [数据库用户名] -p -t [数据库名]>[备份文件的保存路径]

4. 导出数据库中的存储过程和函数
mysqldump -u [数据库用户名] -p -R [数据库名]>[备份文件的保存路径]

5. 导出表
mysqldump -u [数据库用户名] -p [数据库名] [表名]>[备份文件的保存路径]
*/

-- 例子
mysqldump -u root -p xxx > /home/xxx.sql

导入数据库：

-- source sql文件路径
source /home/xxx.sql

-- 使用 < 符号
-- mysql -u root –p < [备份文件的保存路径]

十三、DB密码与用户

修改密码:

/*
1. 首先进入目录mysql\bin
2. 格式：mysqladmin -u用户名 -p旧密码 password 新密码
3. 按以上格式更新密码
*/
mysqladmin -u root -p 1234 password 4321

-- 另一种方法
UPDATE mysql.user SET password=PASSWORD("new password") WHERE User="root";
FLUSH PRIVILEGES;

显示当前用户：

SELECT USER();

新增用户：

-- grant 权限 on 数据库.表 to 用户名@登录主机 identified by “密码”
-- *.*表示所有数据库的所有表
-- %表示任意主机都能登录
grant select,insert,update,delete on *.* to test1”%" identified by “abc”;

grant select,insert,update,delete on mydb.* to test2@localhost identifiedby “abc”;

删除用户：

delete from user where user='用户名' and host='localhost';