sql与mysql的比較
1、连接字符串
sql :Initial Catalog(database)=x; –数据库名称
Data Source(source)=x; –server
Persist Security Info=True; –集成sql server身份验证
Integrated Security=True –集成windows身份验证
User ID(uid)=x; –username
Password(pwb)=x; –password
mysql:Database=x; –数据库名称
Data Source=x; –server
User Id(uid)=bizhi; –username
Password=x; –password
pooling=false; —
CharSet=utf8; –编码
port=x –端口
2、连接对象
sql :SqlConnection
mysql:MySqlConnection
3、命令：
sql :SqlCommand
mysql:MySqlCommand
4、參数
sql :SqlParameter
mysql:MySqlParameter
5、填充：
sql :SqlDataAdapter
mysql:MySqlDataAdapter
6、查询全部库
sql :select * from [master]..[SysDatabases];
mysql:SHOW DATABASES;
7、显示指定库的全部表
sql :select * from [_DatatBaseName]..[SysObjects] where [type]=’U’;
mysql:USE _DatatBaseName; SHOW TABLES;
8、显示指定库的指定表的全部字段
sql :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、查询当前时间
sql :select getdate();
mysql:SELECT NOW();
10、备份数据库
sql :backup _DatatBaseName to disk=’d:\123.bak’；
mysql:
11、创建数据库
sql :use master
go
–删除数据库
if exists(select * from sysdatabases where name=’kl’)
drop database [HBDataBase]; –删除
go
–创建数据库
create database [kl]
on –主文件
(
name=’kl’,
size=3mb,
filename=’D:\kl.mdf’,
maxsize=100mb,
filegrowth=1mb
)
log on –日志文件
(
name=’kl_Log’,
filename=’D:\kl.ldf’,
size=1mb,
maxsize=5mb,
filegrowth=5%
)
go
mysql:CREATE DATABASE _DatatBaseName;
12、分页
sql :top N
ROW_NUMBER() over(order by xx desc )
mysql:limit
13、加入的自增列
sql :insert into xx(bb) output inserted.vv vlaues(”);
select @@identity;
mysql:SELECT @@session.identity;
