web 后端——SQL_后端怎么在后端中直接使用sql语句-CSDN博客

本文链接：https://blog.csdn.net/qq_41956860/article/details/104646749

参考：https://www.w3school.com.cn/sql/sql_quickref.asp

配置

过程(win)

[mysqld]skip-grant-tables

# 设置密码永不过期
default_password_lifetime=0
# 设置密码期限120天
#　default_password_lifetime=120

# 设置3306端口
port=3306

# 设置mysql的安装目录
basedir=D:\InstallLocation\mysql-8.0.18

# 设置mysql数据库的数据的存放目录
datadir=D:\InstallLocation\mysql-8.0.18\Data

# 允许最大连接数
max_connections=200

# 允许连接失败的次数。
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

bin目录添加到环境变量
命令行下mysqld -install, 然后就可以在服务中看到mysql
初始化 mysqld --initialize-insecure --user=mysql
启动服务 net start mysql
输入mysql -u root -p(初始没有密码，直接enter)，然后输入以下命令修改密码

# 切换到mysql数据库
use mysql;
#设置密码：password:新密码；user: 用户
update user set authentication_string=password('123456') where user='root';
# 刷新MySQL的系统权限相关表
flush privileges;

设置远程连接

#进入Mysql  -u:指用户； -p指密码
mysql -u root -p
Enter password: 密码
# 切换到mysql数据库
use mysql;
#设置user用户远程访问
GRANT ALL ON *.* TO user@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
# 刷新MySQL的系统权限相关表
flush privileges;

踩坑

当用mysql -uroot -p登陆的时候，出现 Access denied for user ‘root’@‘localhost’ (using password: YES)的错误，需要改动my.ini 第一行：[mysqld]skip-grant-tables
出现 You must reset your password using ALTER USER statement before executing this statement.错误，意思是当前账户密码过期了，需要重新设置密码，可以输入ALTER USER USER() IDENTIFIED BY 'newPassword'。另外可以在my.ini中设置用户过期时间。

ADO

ADO 指 ActiveX 数据对象（ActiveX Data Objects）,是一个微软的 Active-X 组件,会随微软的 IIS 被自动安装,是一个访问数据库中数据的编程接口

CURD

create、INSERT

INSERT INTO
插入一行，INSERT INTO Persons VALUES (‘Gates’, ‘Bill’, ‘Xuanwumen 10’, ‘Beijing’)
指定列插入，INSERT INTO Persons (LastName, Address) VALUES (‘Wilson’, ‘Champs-Elysees’)

update

UPDATE：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE Person SET FirstName = ‘Fred’ WHERE LastName = ‘Wilson’

retrieve

SELECT，例：SELECT * FROM Persons WHERE FirstName='Bush'
SELECT DISTINCT 返回结果去掉了重复
操作符
= 等于
<> 不等于
BETWEEN 在某个范围内
LIKE 搜索某种模式（通配符：%，_ ，[charlist]，[^charlist] ）
AND、OR
IN :WHERE LastName IN ('Adams','Carter')
排序
默认升序，降序DESC：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

HAVING

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

TOP 规定要返回的记录的数目
SELECT TOP number|percent column_name(s)FROM table_name
MYSQL中的等价语法：
SELECT column_name(s) FROM table_name LIMIT number
别名Alias
表的SQL AliasSELECT column_name(s) FROM table_name AS alias_name
列的SQL Alias SELECT column_name AS alias_name FROM table_name
表的链接
JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、INNER JOIN
例：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

并集UNION
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

表的数据备份SELECT INTO

SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons

delete

DELETE
DELETE FROM Person WHERE LastName = 'Wilson'

表操作

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。主要的DDL语句：

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
TRUNCATE TABLE TABLE 清空表

查看数据库和表

列出所有数据库show databases;
查看当前数据库select database();
更改当前数据库 use dbname;
查看所有表 show tables;

表修改

增加列：
ALTER TABLE table_name
ADD column_name datatype
删除列：
ALTER TABLE table_name 
DROP COLUMN column_name
改变列数据类型：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

表约束

约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。
主要有以下几种约束：

NOT NULL：约束强制字段始终包含值。否则就无法插入新记录或者更新记录
UNIQUE：UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
AUTO_INCREMENT 自动加1(不算约束)

MYSQL例子

CREATE TABLE Orders
(
Id_O int NOT NULL ,
LastName varchar(255) NOT NULL UNIQUE,
Id_P int,
OrderDate date DEFAULT GETDATE()
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)，
CHECK (Id_P>0)
)

还可以用 alter 增加或删除约束
可以用 constraint 一次增加多个约束并命名

建表后添加表约束，如

ALTER TABLE tablename DROP id;
ALTER TABLE tablename ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST

索引

例：降序索引
CREATE INDEX PersonIndex
ON Person (LastName DESC) 

例：多重索引
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

删除索引
ALTER TABLE table_name DROP INDEX index_name

视图View

新建 CREATE VIEW … AS …

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

撤销

SQL DROP VIEW Syntax
DROP VIEW view_name

其它

date

MYSQL主要内建函数

NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY

null

无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。
我们必须使用 IS NULL 和 IS NOT NULL 操作符。
如：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

处理NULL
IFNULL(UnitsOnOrder,0), 如果UnitsOnOrder是null，返回0

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

数据类型

MYSQL字符类型：

CHAR(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)
保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X’,‘Y’,‘Z’)
SET 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

number类型：

TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。

date类型

DATE()
日期。格式：YYYY-MM-DD
注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()
日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP()
时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME() 时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()
2 位或 4 位格式的年。
注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

SQL 函数

参考：https://www.w3school.com.cn/sql/sql_functions.asp

用户

创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';, host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%。登陆密码可以为空。
例：

CREATE USER 'javacui'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'javacui'@'172.23.0.0/255.255.0.0' IDENDIFIED BY '987654321'; 
CREATE USER 'javacui'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'javacui'@'%';