(六）PowerShell 操作mysql数据库（SimplySql)

1. 上次试的模块，最后发现只能试用，还有其他的方法，但是需要装connector，或者有data.dll，我想用的是单纯的模块就能满足查询，不需要安装其他软件，这次发现一个SimplySql模块，看说明可以和好多数据库（SQL Server, Oracle, PostgreSql, SQLite, & mySql)）一起使用，而且看配置文件里把connector，或者有data.dll都包到里边了，感觉有戏，试一下，能不能满足我的要求
2. 模块安装以及查看是否安装在会话中，没有的话，引入会话中

PS /Users/sixdog/Documents/PowerShell> Install-Module -Name SimplySql

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its InstallationPolicy value by running the 
Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y
PS /Users/sixdog/Documents/PowerShell> Get-Module SimplySQL                                                             
PS /Users/sixdog/Documents/PowerShell> Get-InstalledModule 

Version              Name                                Repository           Description
-------              ----                                ----------           -----------
1.9.1                SimplySql                           PSGallery            Querying SQL (SQL Server, Oracle, PostgreSql, SQLite, & mySql) the Powe…

PS /Users/sixdog/Documents/PowerShell> import-Module SimplySQL       
PS /Users/sixdog/Documents/PowerShell> Get-Module SimplySQL   

ModuleType Version    PreRelease Name                                ExportedCommands
---------- -------    ---------- ----                                ----------------
Script     1.9.1                 SimplySql                           {Clear-SqlMessage, Close-SqlConnection, Complete-SqlTransaction, Get-SqlConnecti…

PS /Users/sixdog/Documents/PowerShell> 

3.模块怎么用，看看主页的各种文件，学习一下
3.1.首先看下这里about_SimplySql.help.txt，可以看下说明和例子
3.2.MySql的config.ps1文件，大致了解下
3.3使用Get-Help Invoke-SqlQuery查看下例子，好知道大致咋写

PS /Users/sixdog/Documents/PowerShell> Get-Help Invoke-SqlQuery -Examples

NAME
    Invoke-SqlQuery
    
SYNOPSIS
    Executes a query and returns data.
    
    
    -------------------------- EXAMPLE 1 --------------------------
    
    PS > Run a simple query and return the output
    
    Invoke-SqlQuery -Query "SELECT * FROM TABLE"
    
    
    
    
    -------------------------- EXAMPLE 2 --------------------------
    
    PS > Runs a simple query with parameters
    
    Invoke-SqlQuery -Query "SELECT * FROM TABLE WHERE col1=@id' AND colb > @someDate" -Parameters @{id = 1; someDate = (Get-Date)}

3.4.看着和其他语言写sql大差不差，但是突然想起咋连接也不知道咋写,不过这个只有语法，没有例子

PS /Users/sixdog/Documents/PowerShell> get-Help Open-MySqlConnection   

NAME
    Open-MySqlConnection
    
SYNTAX
    Open-MySqlConnection [[-Server] <string>] [[-Database] <string>] -Credential <pscredential> [-ConnectionName <string>] [-CommandTimeout <int>] [-P
    ort <int>] [-SSLMode {None | Required | VerifyCA | VerifyFull}] [<CommonParameters>]
    
    Open-MySqlConnection [[-Server] <string>] [[-Database] <string>] -UserName <string> -Password <string> [-ConnectionName <string>] [-CommandTimeout
     <int>] [-Port <int>] [-SSLMode {None | Required | VerifyCA | VerifyFull}] [<CommonParameters>]
    
    Open-MySqlConnection -ConnectionString <string> [-ConnectionName <string>] [-CommandTimeout <int>] [<CommonParameters>]
    

ALIASES
    None
    

REMARKS
    None


PS /Users/sixdog/Documents/PowerShell> get-Help Open-MySqlConnection -Examples

NAME
    Open-MySqlConnection

ALIASES
    None
    

REMARKS
    None


PS /Users/sixdog/Documents/PowerShell> 

3.5三种连接方式都试了，其实只有带-Credential才能用，其他的说是过期了，-Credential有个问题，密码每次都需要输入，但是确实是可以查询的

Open-MySqlConnection -Server "localhost" -Port "3307" -Credential "root" -Database "test"      
$data = Invoke-SqlQuery -query "SELECT * FROM User"
Write-Output $data

PS /Users/sixdog/Documents/PowerShell> ./test.ps1

PowerShell credential request
Enter your credentials.
Password for user root: ******


id username password
-- -------- --------
 1 张三     223456
 3 李四     123456
 4 王五     123456

PS /Users/sixdog/Documents/PowerShell> 

3.6上边每次都输入密码不是我想要的，我需要的是不用每次输入密码，借鉴了这位老哥帅哥的写法，最后解决了这个问题

$username = 'root'
$password = ConvertTo-SecureString -String "123456" -AsPlainText -Force
$credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $username,$password
Open-MySqlConnection -Server "localhost" -Port "3307" -Credential $credential -Database "test"     
$data = Invoke-SqlQuery -query "SELECT * FROM User"
Write-Output $data

#执行结果可以看到，没有再被提示输入密码
PS /Users/sixdog/Documents/PowerShell> ./test.ps1

id username password
-- -------- --------
 1 张三     223456
 3 李四     123456
 4 王五    123456

PS /Users/sixdog/Documents/PowerShell> 

4.成功，下一章打算试一试增删改查的增删改.