xml地图|网站地图|网站标签 [设为首页] [加入收藏]
SQL获取数据库名,mysql字符集和字符排序
分类:数据库

mysql的字符集和字符序:
    字符序:字符序(Collation)是指在同一字符集内字符之间的比较规则
    一个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation)

SQL获取数据库名,表名,列名,说明等信息 转自汉城节度使 http://www.cnblogs.com/tuqun/p/3724000.html
/**************************************************************************************************************
1、获取所有用户名:
islogin='1'   :表示帐户
islogin='0'   :表示角色
status='2'   :表示用户帐户
status='0'   :表示系统帐户
**************************************************************************************************************/
SELECT name
FROM   sysusers
WHERE  status = '0'
       AND islogin = '1'
/**************************************************************************************************************
2、获取所有数据库名:
**************************************************************************************************************/
SELECT name
FROM     master..sysdatabases
ORDER BY name
/**************************************************************************************************************
3、获取所有表名:
XType='U'    :表示所有用户表;
XType='S'    :表示所有系统表;
**************************************************************************************************************/
SELECT name
FROM     数据库名..sysobjects
WHERE    xtype = 'U'
ORDER BY name
/**************************************************************************************************************
4、获取所有字段名:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id('表名')
/**************************************************************************************************************
5、获取数据库所有类型:
**************************************************************************************************************/
SELECT name
FROM   systypes
/**************************************************************************************************************
6、获取主键字段:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id('表名')
       AND colid = (SELECT TOP 1 keyno
                    FROM   sysindexkeys
                    WHERE  id = Object_id('表名'))
/**************************************************************************************************************
7、获取表字段的基本信息:
a.
**************************************************************************************************************/
SELECT 字段名 = Rtrim(b.name)
       ,主键 = CASE 
               WHEN h.id IS NOT NULL THEN 'PK'
               ELSE ''
             END
       ,字段类型 = Type_name(b.xusertype)
                + CASE 
                    WHEN b.colstat & 1 = 1 THEN '[ID('
                                                 + CONVERT(VARCHAR,Ident_seed(a.name))
                                                 + ','
                                                 + CONVERT(VARCHAR,Ident_incr(a.name))
                                                 + ')]'
                    ELSE ''
                  END
       ,长度 = b.length
       ,允许空 = CASE b.isnullable 
                WHEN 0 THEN 'N'
                ELSE 'Y'
              END
       ,默认值 = Isnull(e.TEXT,'')
       ,字段说明 = Isnull(c.VALUE,'')
FROM     sysobjects a
         INNER JOIN  sys.all_objects aa
           ON a.id=aa.object_id 
              AND  schema_name(schema_id)='dbo'
         ,syscolumns b
         LEFT OUTER JOIN sys.extended_properties c
           ON b.id = c.major_id
              AND b.colid = c.minor_id
         LEFT OUTER JOIN syscomments e
           ON b.cdefault = e.id
         LEFT OUTER JOIN (SELECT g.id
                                 ,g.colid
                          FROM   sysindexes f
                                 ,sysindexkeys g
                          WHERE  (f.id = g.id)
                                 AND (f.indid = g.indid)
                                 AND (f.indid > 0)
                                 AND (f.indid < 255)
                                 AND (f.status & 2048) <> 0) h
           ON (b.id = h.id)
              AND (b.colid = h.colid)
WHERE    (a.id = b.id)
         AND (a.id = Object_id('表名'))  --要查询的表改成你要查询表的名称
ORDER BY b.colid
/**************************************************************************************************************
b.
**************************************************************************************************************/
SELECT 表名 = CASE 
              WHEN a.colorder = 1 THEN d.name
              ELSE ''
            END
       ,表说明 = CASE 
                WHEN a.colorder = 1 THEN Isnull(f.VALUE,'')
                ELSE ''
              END
       ,字段序号 = a.colorder
       ,字段名 = a.name
       ,标识 = CASE 
               WHEN Columnproperty(a.id,a.name,'IsIdentity') = 1 THEN '√'
               ELSE ''
             END
       ,主键 = CASE 
               WHEN EXISTS (SELECT 1
                            FROM   sysobjects
                            WHERE  xtype = 'PK'
                                   AND name IN (SELECT name
                                                FROM   sysindexes
                                                WHERE  indid IN (SELECT indid
                                                                 FROM   sysindexkeys
                                                                 WHERE  id = a.id
                                                                        AND colid = a.colid))) THEN '√'
               ELSE ''
             END
       ,类型 = b.name
       ,字段长度 = a.length
       ,占用字节数 = Columnproperty(a.id,a.name,'PRECISION')
       ,小数位数 = Isnull(Columnproperty(a.id,a.name,'Scale'),0)
       ,允许空 = CASE 
                WHEN a.isnullable = 1 THEN '√'
                ELSE ''
              END
       ,默认值 = Isnull(e.TEXT,'')
       ,字段说明 = Isnull(g.[value],'')
FROM     syscolumns a
         LEFT JOIN systypes b
           ON a.xusertype = b.xusertype
         INNER JOIN sysobjects d
           ON (a.id = d.id)
              AND (d.xtype = 'U')
              AND (d.name <> 'dtproperties') 
          INNER JOIN  sys.all_objects c
            ON d.id=c.object_id 
                AND  schema_name(schema_id)='dbo'
         LEFT JOIN syscomments e
           ON a.cdefault = e.id
         LEFT JOIN sys.extended_properties g
           ON (a.id = g.major_id)
              AND (a.colid = g.minor_id)
         LEFT JOIN sys.extended_properties f
           ON (d.id = f.major_id)
              AND (f.minor_id = 0)
--where d.name='要查询的表'         --如果只查询指定表,加上此条件
ORDER BY a.id
         ,a.colorder
/***************************************************************************************************************
c. 前两种不完整~
****************************************************************************************************************/
-- 資料庫中所有資料表:透過內定的Stored Procedure sp_tables
EXEC sp_tables @table_name = '%',@table_owner = 'dbo',@table_qualifier = @DBName; 
--    取得資料表Schema
EXEC sp_columns @TableName 
--    取得欄位說明、備註(Extended Property):這個是透過Sql內定的Function來處理
SELECT * FROM  ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default) 
--    取得主索引
EXEC sp_pkeys @TableName 



/**************************************************************************************************************
表及字段描述信息处理示例
**************************************************************************************************************/

--创建表
CREATE TABLE 表 (
  a1  VARCHAR(10)
  ,a2 CHAR(2))
--为表添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '人员信息表' , N'user' , N'dbo' , N'table' , N'表' , NULL , NULL
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'人员信息表' , 
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表'



--为字段a1添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '姓名' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a1'
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'人员信息表' , 
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表', 
@level2type=N'COLUMN',@level2name=N'a1'




--为字段a2添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '性别' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a2'
--更新表中列a1的描述属性:
EXEC Sp_updateextendedproperty 'MS_Description' , '字段1' , 'user' , dbo , 'table' , '表' , 'column' , a1
--显示表的描述属性
SELECT *
FROM   ::fn_listextendedproperty(NULL,'user','dbo','table','表','column',NULL)
--删除表中列a1的描述属性:
EXEC Sp_dropextendedproperty 'MS_Description' , 'user' , dbo , 'table' , '表' , 'column' , a1
--删除测试
DROP TABLE 表

1、事务(Transaction):

    mysql的字符集和字符序有四个级别的默认设置:服务器级,数据库级,数据表级,字段级

 

事务是将一系列数据操作绑成一个整体进行统一管理。

如果一事务执行成功,则咋子该事务中进行的所有数据更改均会提交,称为数据库中的永久成部分。

如果事务执行是遇到错误且必须取消或回滚,则数据将全部恢复到操作前的状态,所有数据的更改均被清除。

定义:

事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所欲的命令作为一个整体起向系统提交侧小操作请求,即这一组数据库命令要么都执行,要么都不执行。

  Eg:

转账过程就是一个整体

它需要两条UPDATE语句来完成,这两条语句是一个整体

如果其中任一条出现错误,则整个转账业务也应取消,两个账户中的余额应恢复到原来的数据,从而确保转账前和转账后的余额不变,即都是1001元

事务是作为单一逻辑工作单元执行一系列操作。一个逻辑工作单位必须有四个属性,

即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability),这些特性通常简称为ACID。

●原子性:事务是一个完整的操作,事务的各步操作(各元素)是不可分的(原子的),要么都执行,要么都不执行

●一致性:当事务完成时,数据必须处于一致状态。

●隔离性:对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,他不应以任何方式依赖于或影响其他事务。

● 持久性:事务的持久性指不管系统是否发生了故障,事务处理的结果都是永久的。

    mysql中的字符序的命名按照规范,以字符序对应的字符集名称开头.以_ci(大小写不敏感),_cs(大小写敏感)或者_bin(按编码值比较)
        例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的

2、执行事务:

    mysql字符集设置:
        系统变量:
            – character_set_server:默认的内部操作字符集
            – character_set_client:客户端来源数据使用的字符集
            – character_set_connection:连接层字符集
            – character_set_results:查询结果字符集
            – character_set_database:当前选中数据库的默认字符集
            – character_set_system:系统元数据(字段名等)字符集
            – 还有以collation_开头的同上面对应的变量,用来描述字符序

默认设置下,枚举SQL语句就是一个事务,即执行SQL语句后自动提交,为了达到将几个操作作为一个整体的目的,

需要使用BEGIN或START TRANSACTION开启一个事务,或者执行命令SETAUTOCOMMIT = 0,

来禁止当前回话的自动提交后,后面的语句作为事务的开始。

MySQL中支持事务的存储引擎有InnoDB和BDB

执行事务的语法:

SQL使用下列语句来管理事务。

(1)开始事务

语法

BEGIN;   //开始的意思  

或者  

START  TRANSACTION;

这个语句显式地标记一个事务的起始点。

(2)提交事务

语法

COMMIT;

这个语句标志一个事务成功提交。

自事务开始至提交语句之间执行的所有数据更新将永久地保存在数据库数据文件中,并释放连接是占用的资源。

(3)回滚(撤销)事务

语法

ROLLBACK;

清除自事务起始点至该语句所做的所有数据更新操作,将数据状态回滚到事务开始前,并释放由事务控制的资源。

设置值自动提交关闭或开始:

MySQL中默认开启自动提交模式,即为指定开启事务时,

每条SQL语句都是单独的事务执行完毕自动提交,可以关闭自动提交模式,手动提交或回滚事务。

语法

SET  autocommit = 0 | 1;

值为0:关闭自动提交。

值为1:开启自动提交。

                           当执行SET autocommit = 0后,即关闭自动提交,

从下一条SQL语句开始则开启新事务,需使用COMMIT或ROLLBACK语句结束该事务。

编写事务的原则:

事务尽可能简短;

事务中访问的数据量尽量最少;

查询数据时尽量不要使用事务;

在事务处理过程中尽量不要出现等待用户输入的操作;

        MySQL中的字符集转换过程:
            1.MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection
            2.进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下
                   - 使用每个数据字段的CHARACTER SET设定值
                   - 若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准)
                   - 若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值
                   - 若上述值不存在,则使用character_set_server设定值
            3.将操作结果从内部操作字符集转换为character_set_results

3、视图:

        检测字符集问题的命令;
                SHOW CHARACTER SET;
                SHOW COLLATION;
                SHOW VARIABLES LIKE ‘character%’;
                SHOW VARIABLES LIKE ‘collation%’;
                SQL函数HEX、LENGTH、CHAR_LENGTH
                SQL函数CHARSET、COLLATION

视图是保存在数据库中的SELECT查询。因此,对查询执行的大多数操作饿可在视图上进行。

使用视图的原因有两点:一个是处于安全考虑,用户不必看到整个数据库的结构,而隐藏部分数据;另一个是符合用户日常业务逻辑,使他们更容易理解数据。

定义:

       视图是另一种查看数据库中一个或多个表中数据的方法。

视图是一种虚拟表,通常是作为来自一个或多个表的行或列的子集创建的。当然,它也可以包含全部的行和列。

但是,视图并不是数据库中存储的数值的集合,它的行和列来自查询中引用的表。在执行是,他直接显示来自于表中的数据。

视图充当着查询中指定表筛选器。定义视图的查询可以基于一个或多个表,也可以基于其他视图、当前数据库或其他数据库。

视图是一张虚拟表

u 表示一张表的部分数据或多张表的综合数据

u 其结构和数据是建立在对表的查询基础上

视图中不存放数据

u 数据存放在视图所引用的原始表中

一个原始表,根据不同用户的不同需求,可以创建不同的视图

视图通常用来进行一下三种操作:

(1)筛选表中的行。

(2)防止为经许可的用户访问敏感数据。

(3)将多个物理数据表抽象为一个逻辑数据表。

(4)降低数据库的复杂程度

对最终用户的好处:

(1)结果更容易理解。创建视图是,可以将列名改为有意义的名称,使用户更容易理解列所有表的内容。在视图中修改名不会影响基表的列名。

(2)获得数据更容易。很多人对SQL不太了解,因此对他们来说,创建对多个表的复杂查询很困难,可以通过创建视图来方便用户访问多个表中的数据。

对开发人员的好处:

(1)限制数据检索更容易。开发人员于是需要隐藏某些行或列中的信息。通过使用视图,用户可以灵活地访问他们的数据,同时保证同一个表或其他表中的其他数据的安全性。要实现这一个目标,可以在创建视图时将对用户保密的列排出在外。

(2)维护应用程序更方便。调试视图表调试查询更容易,跟踪视图中各个步骤的错误更为容易,这是因为所有的步骤都是视图的组成部分。

        注意事项:
            1.my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库
            的应用程序产生任何作用
            2.对字段进行的SQL操作通常都是以内部操作字符集来进行的,不受连接字符集设置的影响

4、创建和使用视图:

        总结:
            mysql的字符集可以细化到一个库,一张表,一列.但是一般是使用默认的设置
                1.编译mysql时,指定了一个默认的字符集,这个字符集是latin1
                2.安装mysql时,可以在配置文件中指定一个默认的字符集,如果没有指定,这个值继承编译时的字符集
                3.启动mysqld时,可以使用character_set_server来指定默认的字符集,如果没有指定就继承配置文件中的配置
                4.安装mysql时选择多语言支持,在程序安装时会自动将配置设置为UTF-8

使用SQL语句创建视图

           语法:

CREATE  VIEW  视图名

AS

<SELECT语句>

注:在SQL语句名命规范中,视图一般以view_xxx或v_xxx的样式来命名;

与创建数据表相同,在创建视图之前,如果数据库中已存在同名视图,需要先删除在创建。

使用SQL语句删除视图

语法

DROP  VIEW  [IF  EXISTE] 视图名;

使用SQL语句看视图数据

语法

SELECT  字段1,字段2,…… FROM  view_name;

                使用查询语句SELECT执行视图的SQL代码,可获得数据结果集。

查看所有视图

USE information_schema;

SELECT * FROM viewsG;

使用视图的注意事项

(1)每个视图中可以使用多个表。

(2)与查询相似,一个视图可以嵌套另一个视图,但最好不要超过三层。

(3)对视图数据进行添加、更新和删除操作直接引用表中的数据。

(4)当视图数据来自多个表时,不允许添加和删除数据。

注:使用视图修改数据会有许多限制,一般在实际开发中视图仅用作查询

            默认情况下的mysql默认字符集是latin1

5、索引:

        修改默认字符集:
            1.最简单的修改方法:
                在mysql的配置文件中加入default-character-set = utf8
                                    character_set_server = utf8
                    修改完后重启服务器
            2.在线修改字符集
                     mysql> SET character_set_client = utf8;
                     mysql> SET character_set_connection = utf8;
                     mysql> SET character_set_database = utf8;
                     mysql> SET character_set_results = utf8;
                     mysql> SET character_set_server = utf8;
                     mysql> SET collation_connection = utf8;
                     mysql> SET collation_database = utf8;
                     mysql> SET collation_server = utf8;

索引提供指针有存储在表中指定列的数据值,在根据指定的排序列这些指针。

数据库使用索引的方式与使用书的目录很相似;通过搜索索引找到特定的值,在跟随指针到包含该值的行。

 索引是一种有效组合数据的方式,为快速查找到指定记录

   作用:

  大大提高数据库的检索速度

  改善数据库性能

  MySQL索引按存储类型分类

  B-树索引:InnoDB、MyISAM均支持

  哈希索引

MySQL中,常用的索引有以下六类。

1. 普通索引:普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。

2.唯一索引:唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值,则一般情况下多数数据库不允许创建唯一索引。

允许有空值

3.主键索引:在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。

           主键列中的每个值是非空、唯一的

4.复合索引:在创建索引是,并不是只能对其他一列创建索引,与创建主键一样,可以多个列组合作为索引,这种索引称为符合索引。

5.全文索引:全文索引的作用是在定义索引的列上支持值的全文查找,允许这些索引列中插入重复的空值和重复值。

6.空间索引:空间索引是对空间数据类型的列建立的索引,如GEOMETRY、POINT等。

            设置了表的默认字符集为utf8并且通过UTF-8编码发送查询,存入数据库的仍然是乱码.那connection连接层上可能出了问题
            解决方法是在发送查询前执行一下下面这句:SET NAMES 'utf8';它相当于下面的三句指令:
                SET character_set_client = utf8;
                SET character_set_results = utf8;
                SET character_set_connection = utf8;

6、使用索引: 

自己周末放假没事学习的心得,如有不对请指出.谢谢!

创建索引:使用CREATE INDEX语句可以在以经存在的表上添加索引:

语法

CREATE  [UNIQUE | FULLTEXT | SPATIAL] INDEX  index_name

ON  table_name  (column_name[length]……)

其中:

(1)UNIQUE | FULLTEXT | SPATIAL:分别表示唯一索引、全文索引的空间索引,为可选参数。

(2)index_name:指定索引名。

(3)column_name:指定需要创建索引的列。

(4)length:指定索引长度,可选参数,只有字符创类型才能指定索引长度。

(5) table_name:指定创建索引的表名。

删除索引:

语法

DROP  INDEX  index_name  ON  table_name;

  注:删除表时,该表的所有索引将同时被删除。

删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

按照下列标准选择建立索引的列

u 频繁搜索的列

u 经常用作查询选择的列

u 经常排序、分组的列

u 经常用作连接的列(主键/外键)

不要使用下面的列创建索引

u 仅包含几个不同值的列

u 表中仅包含几行

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:SQL获取数据库名,mysql字符集和字符排序

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文