SQL语句整理

显示

// 显示所有数据库
show databases;
// 显示当前数据库所有的表
show tables;
// 描述表的结构
describe tabeName;
// 查看创建表的SQL语句
SHOW CREATE TABLE students;
// 支持的存储引擎
SHOW ENGINES
// 查询表的存储引擎
show table status from 数据库名 where name='表名'

##创建表和数据库

// 数据库
create database database_name;
// 进入数据库
use 数据库名
// 表
CREATE TABLE table_name
(
column_name1 data_type(size) 约束,
column_name2 data_type(size),
column_name3 data_type(size),
....
) 其他设置;
// 其他设置
ENGINE=InnoDB // 存储引擎
DEFAULT CHARSET=utf8mb4 字符集
// 约束
COMMENT " " // 注释
not null     // 不能存储NULL值
default    // 插入值得时候没有指定它的值就填充默认值
unique    // 保证某列的每行必须有唯一的值
PRIMARY KEY // 主键约束 。必须有值且值唯一
// 自增，需要插入null。指定起始值 ALTER TABLE Persons AUTO_INCREMENT=100
PRIMARY KEY AUTO_INCREMENT  
CHECK  //  保证列中的值符合指定的条件
FOREIGN KEY // 外键约束，指向另一张表的列

// 表相关操作 -----------
// 删除约束
alter table 表名 drop 约束名;
// 修改存储引擎
alter table table_name engine=MyISAM

数据类型
##插入行

// 值中的数量要和表中的列数量一样，一一对应的
INSERT INTO table_name VALUES (value1,value2,value3,...);

// 没有指定的列会被插入NULL值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
);

查询

1. 普通查询

// 查询所有
select * from table_name;
// 指定列
select column_name,column_name from table_name;
// 设置别名和显示
select column_name alias_name,column_name from table_name alias_name; 
// 多个表查询
select * from table_name, table_name;
select t1.id, t2.name from table_name t1, table_name t2;

2. 条件查询

// 条件查询
select * from table_name where columns_name = '';

// 运算符
=
<>   不等于
>
<
>=
<=

// 连接符-----------
and    // id = 1 and age = 20 两个为真返回数据
or   其中一个为真返回数据
BETWEEN    在某个范围内 BETWEEN 1 AND 20


// ---------------
LIKE   搜索某种模式
SELECT column_name FROM table_name WHERE column_name LIKE ('pattern');
pattern: 
%  0个或多个字符
_   一个字符

REGEXP 
SELECT column_name FROM table_name WHERE column_name REGEXP ('pattern');
[]   在[]中任何单个字符
[^]  不在[]中的单个字符

//----------------
IN    指定针对某个列的多个可能值
// City 值符合其中一个
SELECT * FROM Customers WHERE City IN ('Paris','London');

3. 排序

// 升序
select * from table_name ORDER BY columns_name;
// 倒序
select * from table_name ORDER BY columns_name DESC;

4. 限制条数

select * from table_name  limit 数量 offset 位置;

5. 多表联合查询

// 将两个表的结果合成一张，列需一致
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

// -------------
SELECT * FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

INNER JOIN：如果表中有至少一个匹配，则一起返回两个表的行
LEFT JOIN：左表返回所有的行,右表不符合null
RIGHT JOIN：右表返回所有的行,左表不符合null
FULL outer JOIN：只要其中一个表中存在匹配，则返回行。sql server 支持

##更新（替换）

// 不指定where将更新所有的
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

##删除

// 删除table中符合条件的一行记录
DELETE FROM table_name
WHERE some_column=some_value;

drop database databe_name
drop table table_name
ALTER TABLE table_name DROP INDEX index_name  // 删除索引

##增加、修改、删除列

// 删除
alter table table_name drop column column_name;
// 修改列的数据类型
alter table table_name modify column column_name datatype;
// 修改列的名字
alter table table_name change column 原来列名 修改的列名 datatype;
// 增加
alter table table_name add column_name datatype;

##视图

// 创建
CREATE VIEW view_name AS SELECT ProductID,ProductName FROM Products

// 查询
select * from view_name

// 更新
CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name

// 删除
drop view view_name

##判断NULL值

where column_name is null; // 判断是否为NULL值
where column_name is not null; // 判断是否不为空

##别名
为列或表创建别名，查询是以这个别名显示

// 列
select customerID as id from customer
// 输出
+----+
- id -
+----+
- 1 -
+----+

// 表
select c.ID from customer as c;
// 输出
+----+
- ID -
+----+
- 1 -
+----+

事务

// 执行到commit 才写进数据库
BEGIN;
select * from table;
select * from table;
ROLLBACK;

MySql常用函数

select abs(x);

// 数学---------
ABS(x)   返回x的绝对值
CEILING(x)   向上取整
FLOOR(x)   向下取整
ROUND(x,y)返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)   返回数字x截短为y位小数的结果
RAND() 返回０到１内的随机值

// 聚合函数--------------
AVG() 平均值
COUNT() 非Null列数
MIN() 最小值
MAX() 最大值
SUM() 和
GROUP_CONCAT() 返回由属于一组的列值连接组合而成的结果

// 字符串函数-----------------------
CONCAT(s1,s2...,sn) 将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x) 返回字符串str中最左边的x个字符
LENGTH(s) 返回字符串str中的字符数
LTRIM(str)  从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

//日期和时间函数---------
CURRENT_DATE() 2020-01-11
CURTIME() 20:13:22
NOW()  2020-01-11 20:13:22 当前时间
DATE_FORMAT('2020-07-20 18:20:01','%c') 格式化日期
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)  一个月的第几天(1~31)
DAYOFYEAR(date)   一年的第几天(1~366)
DAYNAME(date)   星期名 Monday
FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(date)   小时值(0~23)
MINUTE(date)   分钟值(0~59)
MONTH(date)   月份值(1~12)
MONTHNAME(date)   返回date的月份名 July
WEEK(date) 一年中第几周(0~53)
YEAR(date)   年份(1000~9999)

//加密函数------
MD5()    计算字符串str的MD5校验和
SHA()  计算字符串str的安全散列算法(SHA)校验和

// 系统信息函数--------------------
DATABASE()   返回当前数据库名
BENCHMARK(count,expr)  将表达式expr重复运行count次
CONNECTION_ID()   返回当前客户的连接ID
FOUND_ROWS()   返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER()  返回当前登陆用户名
VERSION()   返回MySQL服务器的版本

DATE_FORMAT 符号参考

修改数据库密码

set password for root@localhost = password('新密码');