# 基础

MySQL 数据类型 (opens new window)
创建用户、授权、删除用户 (opens new window) (特别是新安装数据库后)

-- 显示用户列表
SELECT User FROM mysql.user;
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- eg:
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY ''; 
CREATE USER 'pig'@'%';

-- 链接：https://www.jianshu.com/p/3d7be4cbd536
CREATE USER 'www-data'@'localhost' IDENTIFIED BY 'www-data'; 


-- 授予用户权限
grant all privileges on awesome.* to 'www-data'@'%' identified by 'www-data';
eg:
创建本地用户及给予权限
CREATE USER 'panda'@'localhost' IDENTIFIED BY 'Pan98';
GRANT ALL PRIVILEGES ON *.* TO 'panda'@'localhost' WITH GRANT OPTION;
创建用户及给予权限（远程登录）
CREATE USER 'panda'@'%' IDENTIFIED BY 'Pan98';
GRANT ALL PRIVILEGES ON *.* TO 'panda'@'%' WITH GRANT OPTION;
grant all privileges on *.* to 'panda'@'%' identified by 'Pan98';
-- oneinstack https://oneinstack.com/question/298/
grant all privileges on *.* to db_user@'%' identified by 'db_pass';
-- 刷新权限
flush privileges;
--查看权限：
show grants for 'username'
show grants for 'root';
+---------------------------------------------------+
| Grants for root@%                                 |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%'                  |
| GRANT ALL PRIVILEGES ON `awesome`.* TO 'root'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

# 数据库操作

# 登录数据库

$ mysql -u root -p
Enter password: （输入密码，如果密码为空的话直接Enter下一步）

# 创建数据库:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name 
eg:
CREATE DATABASE t1;
CREATE DATABASE IF NOT EXISTS t1;
CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
CREATE DATABASE blog CHARACTER SET utf8;

# 授予用户新数据库的访问权限

# 删除数据库:

drop database mydatabase;
drop database if exists mydatabase;

# 修改数据库:

alter即修改的意思。

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name 
-- eg:修改字符编码方式
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- 修改表名：
alter table 表名 rename to 新表名;
-- 增加列
alter table YOUR_tableName add YOUR_columnName varchar(30)
-- 修改列名：
alter table 表名 change 列名 新列名 varchar(30);
-- 修改列类型；列属性?
alter table YOUR_tableName alter column YOUR_columnName varchar(4000)
alter table 表名 modify 列名 varchar(22);
-- 删除列
alter table YOUR_tableName drop column YOUR_columnName

ORACLE alter column type

-- 修改列类型
alter table
   table_name
modify
(
   column_name    varchar2(30)
);

MySQL提示符:

\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户

查看当前服务器下的数据库列表:

SHOW {DATABASES | SCHEMA} [LIKE 'pattern' | WHERE expr];
eg:
SHOW DATABASES;

打开数据库:

USE 数据库名称;
eg:
USE test1;

关闭数据库:

mysql> exit;

创建数据库表:

CREATE TABLE [IF NOT EXISTS] table_name(
	column_name data_type,
	...
);
-- eg:
CREATE TABLE tb1(
	username VARCHAR(20),
	age TINYINT UNSIGNED,
	salary FLOAT(8,2) UNSIGNED
);

查看数据表结构:

SHOW COLUMNS FROM tbl_name;
eg:
SHOW COLUMNS FROM tb1;

# 表操作

操作数据

CRUD boy必备技能

# 插入记录:

-- 语法
INSERT [INFO] tbl_name [(col_name,...)] VALUES(val,...);
-- 例子:
INSERT tb1 VALUES('Tom',25,7800.12);
INSERT tb1 (username,salary) VALUES('Tom',7599.5);

记录查找

-- 语法
SELECT expr,... FROM tbl_name
-- 例一：查找全部
SELECT * FROM tb1;

主键

SELECT expr,... FROM tbl_name
eg:
//查找全部
CREATE TABLE tb2( username VARCHAR(20) NOT NULL, id SMALLINT UNSIGNED PRIMARY KEY);

# 删除

DELETE语句的基本语法是：

DELETE FROM <你的表名> WHERE <你的条件>;

例如，我们想删除students表中id=1的记录，就需要这么写：

DELETE FROM students WHERE id=1;

# 更新

Syntax 语法

UPDATE table-name 
SET column-name = value, column-name = value, ...
WHERE condition

Example 例子

UPDATE school
SET course = 'mysqli', teacher = 'Tanzania', student = 'you'
WHERE id = 6

# 查

-- sqlite 
select * FROM Files WHERE "domain" = 'AppDomainGroup-group.com.fish.stream' limit 0,10

-- 
select 'rm -v /' || substr(fileID, 1, 2) || '/' || fileID FROM Files WHERE "domain" = 'AppDomainGroup-group.com.fish.stream' limit 0,10

# 常用

字符串处理之「包含」

INSTR() 函数处理速度快，限制条件少；示例如下：

-- address包含北京判断
SELECT * FROM students WHERE INSTR(address, 'beijing') > 0

SELECT * FROM students WHERE name like '康%';

CONTAINS

在建立索引的限制条件下，才可以使用该函数必须在检索列建立索引，不然会报错

SELECT * FROM students WHERE contains(name,'康');

regexp_replace正则替换

-- 去掉结尾的空格或数字
UPDATE  M_LIST_COMBINATION SET NAME = regexp_replace(NAME,'[0-9 ]*$','')  FROM M_LIST_COMBINATION where PARENT_ID = '215O00000652'

# 中高级

# 数据库操作

# column字段重新排序

eg: email 本来在server前面，现在调整到server后面

ALTER TABLE `users` CHANGE `email` `email` VARCHAR(50) AFTER `server` ;

# 表操作

# CASE WHEN

select 
       CASE
         WHEN USERCODE = '11072152'  AND PWD = 'D0C1312BF1780032D75D9D8CD2F226F5'  THEN
          (SELECT ORG_ID FROM lms.V_USERORG where USER_ID = u.user_id)
         ELSE
          0
       END ORG_ID
  FROM lms.V_USERINFO u WHERE USERCODE = '11072152'
   
  
   from M_ROLE_USER t where userid = '88888888'
   USERSTATUSTYPE = '3'


   select  distinct b.org_id,a.userstatustype from lms.v_userinfo a inner join lms.v_userorg b on a.USER_ID=b.user_id 
where a.usercode='11072152'

多条记录取第一条用where rownum = 1

select * from (
    SELECT em.times, em.SCORE,em.exam_id as examid
    FROM T_EXAM_MEMBER em
    WHERE em.EXAM_ID = #{id}
      AND em.USERID = #{userId}
    UNION ALL
    SELECT e.EXAMNUM as times,to_char(e.MAXMARK) as score ,to_char(e.EXAM_ID) as examid
    FROM lms.V_CLASS_EXAM_USER_SHARE e
    WHERE e.EXAM_ID = #{id} AND e.USERID = #{userId}
) where rownum = 1

# 多表查询

INNER JOIN

以下二者等价

SELECT  s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s,classes c
where s.class_id = c.id;

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

# 只查前100条

-- 只查前100条,0-100使用OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
select * from T_USER_CONDUCT_LOG where USER_ID  = '12005285' order by dt desc ,0-100使用OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

# 占位符

以下placeholder是占位符字段名，0是占位字段类型，字符串可以用 '1'

SELECT
    hat,
    shoe,
    boat,
    0 as placeholder
FROM
    objects

# 避免重复插入

-- 不用这种
CREATE TABLE t (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR,
    other       INT
);

-- 用这种带UNIQUE的
CREATE TABLE t (
    id          INTEGER PRIMARY KEY,
    name        VARCHAR UNIQUE,
    other       INT
);

-- 插入10次一样的数据
INSERT OR REPLACE into t_friend (friendId,nickname,pic,status,type,friendRemark,address,socialId,sex,friendVerification) values('123465','123465','123465','123465','123465','123465','123465','123465','123465','123465')

# 函数

# lpad函数

https://www.cnblogs.com/520future/p/7463309.html

lpad函数是Oracle数据库函数，lpad函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解，l是left的简写，pad是填充的意思，所以lpad就是从左边填充的意思。

语法格式如下：

lpad( string, padded_length, [ pad_string ] )

string

准备被填充的字符串；

padded_length

填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string

填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

示例1：

> select lpad('abcde',10,'x') from dual;
LPAD('ABCDE',10,'X')
--------------------
xxxxxabcde

示例2：

> select lpad('abcde',10,'oq') from dual;
LPAD('ABCDE',10,'OQ')
---------------------
oqoqoabcde

示例3：

> select lpad('abcde',2) from dual;
LPAD('ABCDE',2)
---------------
ab

# SUBSTR

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------结果如下：
CDEF


SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------结果如下：
CDEF


select SUBSTR('listCombination/A0003', INSTR('listCombination/A0003', '/', 1, 1) + 1) from  dual;
----输出
A0003

# INSTR

#address是否包含beijing
instr(address, 'beijing') > 0

INSTR 函数(INSTR、 insb、 INSTRC、 INSTR2和 INSTR4)使用字符在字符串中搜索子字符串，并返回字符串中指定子字符串的第一个字符的位置。函数在如何确定要返回的子字符串的位置方面有所不同。

返回值：搜索成功时为非零整数，搜索不成功时为0(零)。

语法：

{INSTR | INSTRB | INSTRC | INSTR2 | INSTR4} (string , substring [, position [, occurrence]])

参数说明：

position

函数在字符串中的哪个位置开始搜索

occurrence

一个 INTEGER，指示函数应该搜索哪个字符串匹配项。值必须是正值。默认值为1，表示函数搜索子字符串的第一个匹配项。

# 排序

-- 多重排序
ORDER BY SHOW_ORDER ASC ,CREATE_TIME  DESC
-- 字符串转数字排序
ORDER BY to_number(en_name)

# VIEW

CREATE VIEW 创建视图 (opens new window)

使用 CREATEVIEW 语句定义视图（VIEW），视图是基于一个或多个表或视图的逻辑表。视图本身不包含数据。视图所基于的表称为基表（base tables）。

-- https://stackoverflow.com/a/13742917
-- 查询所有视图(对于这个查询，您需要 dba 特权)
select view_name from dba_views

-- 查询所有可访问的视图 (已登录的用户可访问)
select view_name from all_views

-- 查询已登录用户所拥有的视图
select view_name from user_views

# subprogram

https://www.tutorialspoint.com/plsql/plsql_procedures.htm

您使用 PL/SQL 创建并存储在数据库中的程序单元的主要类型是独立的过程、函数和包（procedures and functions, and packages）。一旦存储在数据库中，这些 PL/SQL 组件(统称为存储过程即stored procedures)就可以用作几个不同应用程序的构建块。Oracle - Developing and Using Stored (opens new window)

子程序是执行特定任务的程序单元/模块。这些子程序被组合成更大的程序。这基本上就是所谓的模块化设计。

一个子程序可以被另一个子程序（或称为调用程序的程序）调用。

PL/SQL 提供了两种子程序：

Functions - 这些子程序返回一个值，主要用于计算和返回一个值

Procedures - 这些子程序不直接返回值，主要用于执行操作

# FUNCTION

没测试
CREATE OR REPLACE FUNCTION pw_calculate_score(evaluation_id IN NUMBER
                         , performance_id IN NUMBER)
                           RETURN NUMBER AS
    n_score       NUMBER(1,0);                     -- a variable
    n_weight      NUMBER;                          -- a variable
    max_score     CONSTANT NUMBER(1,0) := 9;       -- a constant limit check
    max_weight    CONSTANT NUMBER(8,8) := 1;       -- a constant limit check
  BEGIN
    RETURN NULL;
  END pw_calculate_score;

# PROCEDURE

https://stackoverflow.com/a/1819461
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE')

创建一个过程：

使用 CREATE OR REPLACE PROCEDURE 语句创建过程。CREATE OR REPLACE PROCEDURE 语句的简化语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Example:

下面的示例创建一个简单的过程，该过程在执行时在屏幕上显示字符串“ helloworld! ”。

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/




-- oracle官网的
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

当使用 SQL 提示执行上面的代码时，将产生以下结果：

Procedure created.

我的可用Demo：

create OR REPLACE PROCEDURE DEMO2(
list_id in VARCHAR2,result_rk out sys_refcursor)
is
BEGIN
    open result_rk for SELECT * FROM M_LIST_COMBINATION_RELATION WHERE LIST_ID=list_id;
END ;

# Tips

# SQL 语法检查

piliapp.com (opens new window)

# SQL格式化

SQL格式化 (opens new window)

# 约定

SQL关键字总是大写，以示突出，表名和列名均使用小写

# # 日期


select sysdate from dual;
--输出>> 2020年9月16日 下午4:26:53

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
-- 2020-09-16 16:24:57

select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;
-- 2020-09-16 16:09:52

# count(*) vs count(1)

https://stackoverflow.com/a/181286/4493393

表达式有两个额外的修饰符: ALL 和 DISTINCT。它们决定是否丢弃副本。因为 ALL 是默认值，所以count(1)与 count (ALL 1)相同，这意味着重复项将被保留。

由于表达式“1”对每一行的计算结果为非空，如果没有删除重复项，COUNT (1)应该总是返回与 COUNT (*)相同的数字（未验证）

# command not found

PeterPoker:~ wangchao$ mysql
-bash: mysql: command not found

原因，MacOS上MySQL的dmg安装包把程序安装到了/usr/local/mysql/bin/下

查看系统环境变量： vi ~/.bash_profile 会发现此文件不包含/usr/local/mysql/bin/

解决方法1：

vi ~/.bash_profile
i
添加一行：export PATH="/usr/local/mysql/bin:$PATH"
完成！

解决方法2：

创建软连接，命令如下：

sudo ln -s /usr/local/mysql/bin/* /usr/bin

小科普：
ln的链接分软链接和硬链接两种：
1、软链接就是：“ln –s 源文件 目标文件”，只会在选定的位置上生成一个文件的镜像，不会占用磁盘空间，类似与windows的快捷方式。
2、硬链接ln源文件目标文件，没有参数-s， 会在选定的位置上生成一个和源文件大小相同的文件，无论是软链接还是硬链接，文件都保持同步变化。

Mac 下 MySQL 的 root 密码忘记 (opens new window)

# Error

Q：exact fetch returns more than requested number of rows

A：查询结果如果大于1个，不要用INTO

如果 SELECT INTO 语句返回的值不是1行，那么它将抛出一个错误：如果它返回0行，您将得到一个no_data_ found 异常；如果返回的行数超过1行，那么就会出现行数过多的异常。除非您知道总是仅有一个薪水大于3000的雇员，否则不要在这里使用 SELECT INTO 语句。

https://stackoverflow.com/a/19779546

← Docker笔记