Sql server and mysql command for DB connection.
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.