Sql server and mysql command for DB connection.

Pankaj kushwaha
2 min readJan 24, 2021

Comparison of sql and mysql

1. Connection string
sql: Initial Catalog(database)=x; — database name
Data Source(source)=x;
— server Persist Security Info=True; — integrated sql server authentication
Integrated Security =True — integrated windows authentication
User ID(uid)=x;
— user name Password(pwb)=x; — password

mysql:Database=x; — database name
Data Source=x; — server
User Id (uid)=bizhi; — User name
Password=x; — Password
pooling=false; -
CharSet=utf8; — Encoding
port=x — Port

2, connection object
sql:SqlConnection
mysql:MySqlConnection

3. Command:
sql:SqlCommand
mysql:MySqlCommand

4. Parameter
sql:SqlParameter
mysql:MySqlParameter

5. Fill:
sql:SqlDataAdapter
mysql:MySqlDataAdapter

6. Query all libraries
sql: select * from [master].. [SysDatabases];
mysql:SHOW DATABASES;

7. Display all tables
sql of the specified library : select * from [_DatatBaseName]..[SysObjects] where [type]=’U’;
mysql:USE _DatatBaseName; SHOW TABLES;

8. Show All fields
sql of the specified table of the specified library : select top 0 * from [_DatatBaseName]..[_TableName];
select * from [syscolumns] where [ID]=OBJECT_ID(‘BPM..OrderFollow’);
mysql:USE _DatatBaseName; SHOW COLUMNS FROM _TableName;
USE _DatatBaseName; DESCRIBE _TableName; 9.Query

current time
sql:select getdate();
mysql:SELECT NOW(); 10.Backup

database
sql:backup _DatatBaseName to disk=’d:\ 123.bak’;
mysql:

11. Create database
sql: use master
go

— delete database
if exists(select * from sysdatabases where name=’kl’)
drop database [HBDataBase]; — delete
go

— create database
create database [kl]
on — Main file
(
name=’kl’,
size=3mb,
filename=’D:\kl.mdf’,
maxsize=100mb,
filegrowth=1mb
)
log on — log file
(
name=’kl_Log’,
filename=’D:\kl.ldf’,
size=1mb,
maxsize=5mb,
filegrowth=5%
)
go
mysql:CREATE DATABASE _DatatBaseName;

12. Paging
sql: top N
ROW_NUMBER() over(order by xx desc)
mysql: limit

13. Added auto-
increment column sql: insert into xx(bb) output inserted.vv vlaues(‘’);
select @@ identity;
mysql:SELECT @@session.identity;

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.

--

--

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS