MySQL查询整个库、指定库、指定表的存储使用大小_Crystalqy的博客

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

1：查询整个数据库，整个库的大小；单位转换为G:

 select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'G') as data  from information_schema.TABLES;

2：查询mysql数据库，某个库的大小:

select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'G') as data  from information_schema.TABLES where   table_schema = 'cxm_service_data_base';

3：查看库中某个表的大小:

select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'G') as data  from information_schema.TABLES where table_schema = 'cxm_service_data_base' and table_name = 'dk_sale_order_head';

datadir=/home/ mysql /data log-bin=/home/ mysql / mysql -bin log-error=/home/ mysql /log/ mysql d.log pid-file =/home/ mysql / mysql d.pid bind-address = 0.0.0.0 server-id = 1 # 表示是本机的序号为1,一般来讲就是master的意思 skip-grant-tables skip-name-resolve # 禁止 MySQL 对外部连接进行DNS解析，使用这一选项可以消除 MySQL 进行DNS解析的时间。但需要注意，如果开启该选项， # 则所有远程主机连接授权都要使用 IP地址方式，否则 MySQL 将无法正常处理连接请求 #skip-networking back_log = 600 # MySQL 能有的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，这就起作用， # 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在 MySQL 暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。 # 如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果 MySQL 的连接数据达到max_connections时，新来的请求将会被存在堆栈中， # 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。 # 另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。 # 你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。 max_connections = 500 # MySQL 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于 MySQL 会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。 max_connect_errors = 6000 # 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。 open_files_limit = 65535 # MySQL 打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个， # 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个. table_open_cache = 128 # MySQL 每打开一个表，都会读入一些数据到table_open_cache缓存中，当 MySQL 在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64 # 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)； # 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上 max_allowed_packet = 1000000000 # 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或 MySQL d必须返回大的结果行时 MySQL d才会分配更多内存。 # 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。 binlog_cache_size = 1M # 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size 大小 32K max_heap_table_size = 67108864 # 定义了用户可以创建的内存表 (memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变 tmp_table_size = 67108864 # MySQL 的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。 # 大多数临时表是基于内存的(HEAP) 表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。 # 如果某个内部heap（堆积）表大小超过tmp_table_size， MySQL 可以根据需要自动将内存中的heap 表改为基于硬盘的MyISAM 表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值， MySQL 同时将增加heap 表的大小，可达到提高联接查询速度的效果 read_buffer_size = 4194304 # MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区， MySQL 会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。 # 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能 read_rnd_buffer_size = 4194304 # MySQL 的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时， # MySQL 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 MySQL 会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大 sort_buffer_size = 4194304 # MySQL 执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让 MySQL 使用索引而不是额外的排序阶段。 # 如果不能，可以尝试增加sort_buffer_size变量的大小 join_buffer_size = 8388608 # 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享 thread_cache_size = 8 # 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中， # 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程， # 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–> 表示要调整的值) # 根据物理内存设置规则如下： # 1G —> 8 # 2G —> 16 # 3G —> 32 # 大于3G —> 64 #query_cache_size = 8M # MySQL 的查询缓冲大小（从4.0.1开始， MySQL 提供了查询缓冲机制）使用查询缓冲， MySQL 将SELECT语句和查询结果存放在缓冲区中， # 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据 MySQL 用户手册，使用查询缓冲最多可以达到238%的效率。 # 通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况， # 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低， # 这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲 #query_cache_limit = 2M # 指定单个查询能够使用的缓冲区大小，默认1M key_buffer_size = 1048576 # 指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大， # 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads， # 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低， # 至少是1:100，1:1000更好(上述状态值可以使用 SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低 ft_min_word_len = 4 # 分词词汇最小长度，默认4 transaction_isolation = REPEATABLE-READ # MySQL 支持4种事务隔离级别，他们分别是： # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. # 如没有指定， MySQL 默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED log_bin = mysql -bin binlog_format = mixed expire_logs_days = 30 #超过30天的binlog删除 slow_query_log = 1 long_query_time = 1 #慢查询时间超过1秒则为慢查询 slow_query_log_file = /home/ mysql /data/ mysql -slow.log performance_schema = 0 explicit_defaults_for_timestamp #lower_case_table_names = 1 #不区分大小写 skip-external-locking # MySQL 选项以避免外部锁定。该选项默认开启 default-storage-engine = InnoDB #默认存储引擎 innodb_file_per_table = 1 # InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间 # 独立表空间优点： # 1．每个表都有自已独立的表空间。 # 2．每个表的数据和索引都会存在自已的表空间中。 # 3．可以实现单表在不同的数据库中移动。 # 4．空间可以回收（除drop table操作处，表空不能自已回收） # 缺点： # 单表增加过大，如超过100G # 结论： # 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files innodb_open_files = 500 # 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300 innodb_buffer_pool_size = 1048576 # InnoDB 使用一个缓冲池来保存索引和原始数据, 不像MyISAM. # 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少. # 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80% # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸. # 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, # 所以不要设置的太高. innodb_write_io_threads = 4 innodb_read_io_threads = 4 # innodb 使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4 # 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启 MySQL 服务,允许值的范围从 1-64 innodb_thread_concurrency = 0 # 默认设置为 0, 表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量 innodb_purge_threads = 1 # InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。 # 从 MySQL 5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单 # 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1 innodb_flush_log_at_trx_commit = 2 # 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由 mysql 的master thread线程来执行的。 # 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1 # 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。 # 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。 # 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有缓存的，所以这个写入并不能保证数据已经写入到物理磁盘 # 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。 # 设为0的话， mysql d进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。 # 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能 innodb-buffer-pool-size = 128M innodb_log_buffer_size = 4194304 # 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。 MySQL 开发人员建议设置为1－8M之间 innodb_log_file_size = 268435456 # 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间 innodb_log_files_in_group = 3 # 为提高性能， MySQL 可以以循环方式将日志文件写到多个文件。推荐设置为3 innodb_max_dirty_pages_pct = 90 # innodb主线程刷新缓存池中的数据，使脏数据比例小于90% innodb_lock_wait_timeout = 120 # InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒 bulk_insert_buffer_size = 1024M # 批量插入缓存大小，这个参数是针对MyISAM 存储引擎来说的。适用于在一次性插入100-1000+条记录时，提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。 myisam_sort_buffer_size = 1024M # MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区 myisam_max_sort_file_size = 10G # 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出 myisam_repair_threads = 1 # 如果该值大于1，在Repair by sorting过程中并行创建MyISAM 表索引(每个索引在自己的线程内) interactive_timeout = 28800 # 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在 mysql _real_connect()中使用 CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时） wait_timeout = 28800 # 服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值， # 取决于客户端类型(由 mysql _real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时） # MySQL 服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到 MySQL Server处理完相应的操作后， # 应该断开连接并释放占用的内存。如果你的 MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开， # 最终肯定会达到 MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。 # 在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大， # 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。 [ mysql dump] quick max_allowed_packet = 16M #服务器发送和接受的最大包长度 [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M Android数据存取之Databases 在Android平台上可以操作数据库，这是第一次接触Android时的惊艳之一。在Android平台上，绑定了SQLite数据库，这个数据库系统也是极具性格的，它的最大的应用场景是嵌入式系统，进一步了解可以参看这里。如果有JDBC的经验，那么在这里会容易的多。Android中操作数据库首先要通过一个类：android.database.sqlite.SQLiteOpenHelper。它封装了如何打开一个数据库，其中当然也包含如果数据库不存在就创建这样的逻辑。看一个例子： view plaincopy to clipboardprint? 1. pubilc class DatabaseHelper extends SQLiteOpenHelper { 2. private static final String DATABASE_NAME = "com.roiding.simple.note"; 3. private static final int DATABASE_VERSION = 1; 4. private static final String NOTES_TABLE_NAME = "notes"; 6. DatabaseHelper(Context context) { 7. super(context, DATABASE_NAME, null, DATABASE_VERSION); 10. @Override 11. public void onCreate(SQLiteDatabase db) { 12. db.execSQL("CREATE TABLE " + NOTES_TABLE_NAME 13. + " (id integer primary key autoincrement, name text);"); 14. } 16. @Override 17. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 18. db.execSQL("DROP TABLE IF EXISTS notes"); 19. onCreate(db); 20. } 21. } 这里面，如下的语句需要解释： • super(context, DATABASE_NAME, null, DATABASE_VERSION) 数据库连接的初始化，中间的那个null，是一个CursorFactory参数，没有仔细研究这个参数，暂时置空吧。 • public void onCreate(SQLiteDatabase db) 这里面的onCreate是指数据库 onCreate时，而不是DatabaseHelper的onCreate。也就是说，如果已经指定 database已经存在，那么在重新运行程序的时候，就不会执行这个方法了。要不然，岂不是每次重新启动程序都要重新创建一次数据库了！在这个方法中，完成了数据库的创建工作。也就是那个execSQL()方法。 • public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 在程序的开发维护过程中，数据库的结构可能会有变化，那么这个方法就有用处了。在DatabaseHelper这个对象一创建时，就已经把参数 DATABASE_VERSION传入，这样，如果Android发现此版本与现有版本不一致，就会调用这个onUpgrate方法。于是，可以在这里面实现一些数据的upgrade工作，比如说创建一个临时表，将数据由临时表中转到新的表结构中。需要注意的是，这里面的onUpgrade是在版本不一致时调用，也就是说不管当前需要的版本高于现有版本还是低于现有版本，都会出发这个方法，类似的这种情况，就需要对oldVersion和 newVersion进行判断之后再决定使用什么策略来更新数据。在Android中，数据库存放在 /data/data/PACKAGE_NAME/databases 目录下。接下来就可以使用这个Helper来操作数据库了，操作数据库也就无非是增、删、改、查。先看一个增的例子： view plaincopy to clipboardprint? 1. public static void insert(Context context, String s) { 2. DatabaseHelper 避免编辑转储文件 Mysql dump创建的转储文件原本是无害的，但它很容易被尝试去编辑。然而，人们应该知道在任何情况下的试图修改这些文件被证明是有危险的。直观地看对这些文件的改动会导致数据库损坏，从而导致系统的退化。为了让你的系统免受任何麻烦，你必须避免编辑 MySQL 转储文件。 MyISAM 块大小大多数开发者忘记了这一事实，文件系统往往需要一个大的MyISAM块以保证高效运行。许多开发者不知道块大小的设置。.MYI文件存储在myisam_block_size的设置里，这个设置项可用来修改大的块尺寸。MyISAM块大小的默认值是1K，这不是当前大多数系统的恰当设置。因此，开发者应该考虑指定一 MYSQL 常用命令 1.导出整个数据库 mysql dump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) mysql dump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysql dump -u 用户名 -p 数据库名表名> 导出的文件名 mysql dump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 3.导出一个数据库结构 mysql dump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库 A:常用source 命令进入 mysql 数据库控制台，如 mysql -u root -p mysql >use 数据库然后使用 source命令，后面参数为脚本文件(如这里用到的.sql) mysql >source wcnc_db.sql B: 使用 mysql dump命令 mysql dump -u username -p dbname < filename.sql C: 使用 mysql 命令 mysql -u username -p -D dbname 2、退出 MySQL ：quit或exit 二、库操作 1、、创建数据库命令：create database 例如：建立一个名为xhkdb的数据库 mysql > create database xhkdb; 2、显示所有的数据库命令：show databases （注意：最后有个s） mysql > show databases; 3、删除数据库命令：drop database 例如：删除名为 xhkdb的数据库 mysql > drop database xhkdb; 4、连接数据库命令： use 例如：如果xhkdb数据库存在，尝试存取它： mysql > use xhkdb; 屏幕提示：Database changed 5、查看当前使用的数据库 mysql > select database(); 6、当前数据库包含的表信息： mysql > show tables; （注意：最后有个s）三、表操作，操作之前应连接某个数据库 1、建表命令：create table ( [,.. ]); mysql > create table MyClass( > id int(4) not null primary key auto_increment, > name char(20) not null, > sex int(4) not null default '0', > degree double(16,2)); 2、获取表结构命令： desc 表名，或者show columns from 表名 mysql >DESCRIBE MyClass mysql > desc MyClass; mysql > show columns from MyClass; 3、删除表命令：drop table 例如：删除表名为 MyClass 的表 mysql > drop table MyClass; 4、插入数据命令：insert into [( [,.. ])] values ( 值1 )[, ( 值n )] 例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为3 的名为Wang 的成绩为96.5. mysql > insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59); 5、查询表中的数据 1)、查询所有行命令： select from where 例如：查看表 MyClass 中所有数据 mysql > select * from MyClass; 2）、查询前几行数据例如：查看表 MyClass 中前2行数据 mysql > select * from MyClass order by id limit 0,2; mysql > select * from MyClass limit 0,2; 6、删除表中数据命令：delete from 表名 where 表达式例如：删除表 MyClass中编号为1 的记录 mysql > delete from MyClass where id=1; 7、修改表中数据：update 表名 set 字段=新值,… where 条件 mysql > update MyClass set name='Mary' where id=1; 7、在表中增加字段：命令：alter table 表名 add字段类型其他; 例如：在表 MyClass中添加了一个字段passtest，类型为int(4)，默认值为0 mysql > alter table MyClass add passtest int(4) default '0' 8、更改表名：命令：rename table 原表名 to 新表名; 例如：在表 MyClass名字更改为YouClass mysql > rename table MyClass to YouClass; 更新字段内容 update 表名 set 字段名 = 新内容 update 表名 set 字段名 = replace(字段名,'旧内容','新内容'); 文章前面加入4个空格 update article set content=concat('　　',content); 1．INT[(M)] 型：正常大小整数类型 2．DOUBLE[(M,D)] [ZEROFILL] 型：正常大小 (双精密)浮点数字类型 3．DATE 日期类型：支持的范围是1000-01-01到9999-12-31。 MySQL 以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列 4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度 5．BLOB TEXT类型，最大长度为65535(2^16-1)个字符。 6．VARCHAR型：变长字符串类型 5.导入数据库表（1）创建.sql文件　　（2）先产生一个库如auction.c: mysql bin> mysql admin -u root -p creat auction，会提示输入密码，然后成功创建。　　（2）导入auction.sql文件　　 c: mysql bin> mysql -u root -p auction grant select,insert,delete,create,drop 　　 on *.* (或test.*/user.*/..) 　　 to 用户名@localhost 　　 identified by '密码'；　　如：新建一个用户帐号以便可以访问数据库，需要进行如下操作： mysql > grant usage 　　 -> ON test.* 　　 -> TO testuser@localhost; 　　 Query OK, 0 rows affected (0.15 sec) 　　此后就创建了一个新用户叫：testuser，这个用户只能从localhost连接到数据库并可以连接到test 数据库。下一步，我们必须指定 testuser这个用户可以执行哪些操作： mysql > GRANT select, insert, delete,update 　　 -> ON test.* 　　 -> TO testuser@localhost; 　　 Query OK, 0 rows affected (0.00 sec) 　　此操作使testuser能够在每一个test数据库中的表执行SELECT，INSERT和DELETE以及UPDATE 查询操作。现在我们结束操作并退出 MySQL 客户程序： mysql > exit 　　 Bye9! 1: 使用 SHOW语句找出在服务器上当前存在什么数据库： mysql > SHOW DATABASES; 2:2、创建一个数据库 MYSQL DATA mysql > Create DATABASE MYSQL DATA; 3:选择你所创建的数据库 mysql > USE MYSQL DATA; (按回车键出现Database changed 时说明操作成功！) 4:查看现在的数据库中存在什么表 mysql > SHOW TABLES; 5:创建一个数据库表 mysql > Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1)); 6:显示表的结构： mysql > DESCRIBE MYTABLE; 7:往表中加入记录 mysql > insert into MYTABLE values ("hyq","M"); 8:用文本方式将数据装入数据库表中（例如D:/ mysql .txt） mysql > LOAD DATA LOCAL INFILE "D:/ mysql .txt" INTO TABLE MYTABLE; 9:导入.sql文件命令（例如D:/ mysql .sql） mysql >use database; mysql >source d:/ mysql .sql; 10:删除表 mysql >drop TABLE MYTABLE; 11:清空表 mysql >delete from MYTABLE; 12:更新表中数据 mysql >update MYTABLE set sex="f" where name='hyq'; 以下是无意中在网络看到的使用 MySql 的管理心得, 摘自:http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html 在windows中 MySql 以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql 命令启动。而Linux中启动时可用“/etc/rc.d/init.d/ mysql d start"命令，注意启动者应具有管理员权限。刚安装好的 MySql 包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、 root帐户设置密码，可用如下命令进行： use mysql ; delete from User where User=""; update User set Password=PASSWORD('newpassword') where User='root'; 如果要对用户所用的登录终端进行限制，可以更新User 表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令： mysql -uroot -p; mysql -uroot -pnewpassword; mysql mydb -uroot -p; mysql mydb -uroot -pnewpassword; 上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用 root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。 MySql 的用户管理是通过 User 表来实现的，添加新用户常用的方法有两个，一是在User 表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下： grant all on mydb.* to NewUserName@HostName identified by "password" ; grant usage on *.* to NewUserName@HostName identified by "password"; grant select,insert,update on mydb.* to NewUserName@HostName identified by "password"; grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password"; 若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User 表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User 表相应字段，也可以使用 REVOKE操作。下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释：全局管理权限： FILE: 在 MySQL 服务器上读写文件。 PROCESS: 显示或杀死属于其它用户的服务线程。 RELOAD: 重载访问控制表，刷新日志等。 SHUTDOWN: 关闭 MySQL 服务。数据库 /数据表 /数据列权限： Alter: 修改已存在的数据表 (例如增加/删除列)和索引。 Create: 建立新的数据库或数据表。 Delete: 删除表的记录。 Drop: 删除数据表或数据库。 INDEX: 建立或删除索引。 Insert: 增加表的记录。 Select: 显示/搜索表的记录。 Update: 修改表中已存在的记录。特别的权限： ALL: 允许做任何事(和root一样)。 USAGE: 只允许登录--其它什么也不允许做。 --------------------- MYSQL 常用命令有很多朋友虽然安装好了 mysql 但却不知如何使用它。在这篇文章中我们就从连接 MYSQL 、修改密码、增加用户等方面来学习一些 MYSQL 的常用命令。　　有很多朋友虽然安装好了 mysql 但却不知如何使用它。在这篇文章中我们就从连接 MYSQL 、修改密码、增加用户等方面来学习一些 MYSQL 的常用命令。　　　一、连接 MYSQL 格式： mysql -h主机地址 -u用户名－p用户密码　　　　1、例1：连接到本机上的 MYSQL 首先在打开DOS窗口，然后进入目录 mysql bin，再键入命令 mysql -uroot -p，回车后提示你输密码，如果刚安装好 MYSQL ，超级用户root是没有密码的，故直接回车即可进入到 MYSQL 中了， MYSQL 的提示符是： mysql > 　　　　2、例2：连接到远程主机上的 MYSQL 假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令： mysql -h110.110.110.110 -uroot -pabcd123 　　　　（注:u与root可以不用加空格，其它也一样）　　　　3、退出 MYSQL 命令： exit （回车）　　二、修改密码　　　　格式： mysql admin -u用户名 -p旧密码 password 新密码　　　1、例1：给root加个密码ab12。首先在DOS下进入目录 mysql bin，然后键入以下命令 mysql admin -uroot -password ab12 　　　　注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。　　　　2、例2：再将root的密码改为djg345 mysql admin -uroot -pab12 password djg345 MYSQL 常用命令（下）　　一、操作技巧　　1、如果你打命令时，回车后发现忘记加分号，你无须重打一遍命令，只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打，完后用分号作结束标志就OK。　　2、你可以使用光标上下键调出以前的命令。但以前我用过的一个 MYSQL 旧版本不支持。我现在用的是 mysql -3.23.27-beta-win。　　二、显示命令　　1、显示数据库列表。　　show databases; 　　刚开始时才两个数据库： mysql 和test。 mysql 库很重要它里面有 MYSQL 的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。　　2、显示库中的数据表：　　use mysql ；／／打开库，学过FOXBASE的一定不会陌生吧　　show tables; 　　3、显示数据表的结构：　　describe 表名; 　　4、建库：　　create database 库名; 　　5、建表：　　use 库名；　　create table 表名 (字段设定列表 )；　　6、删库和删表 : 　　drop database 库名; 　　drop table 表名；　　7、将表中记录清空：　　delete from 表名; 　　8、显示表中的记录：　　select * from 表名; 三、一个建库和建表以及插入数据的实例　　drop database if exists school; //如果存在SCHOOL则删除　　create database school; //建立库 SCHOOL 　　use school; //打开库 SCHOOL 　　create table teacher //建立表 TEACHER 　　id int(3) auto_increment not null primary key, 　　name char(10) not null, 　　address varchar(50) default '深圳', 　　year date 　　); //建表结束　　//以下为插入字段　　insert into teacher values('','glchengang','深圳一中','1976-10-10'); 　　insert into teacher values('','jack','深圳一中','1975-12-23'); 　　注：在建表中（1）将ID设为长度为3的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key 　　（2）将NAME设为长度为10的字符字段　　（3）将ADDRESS设为长度50的字符字段，而且缺省值为深圳。varchar和char有什么区别呢，只有等以后的文章再说了。　　（4）将YEAR设为日期字段。　　如果你在 mysql 提示符键入上面的命令也可以，但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\ mysql \bin，然后键入以下命令： mysql -uroot -p密码 school.bbb 　　注释:将数据库 school备份到school.bbb文件，school.bbb是一个文本文件，文件名任取，打开看看你会有新发现。一.SELECT语句的完整语法为： SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} FROM tableexpression[,…][IN externaldatabase] [WHERE…] [GROUP BY…] [HAVING…] [ORDER BY…] [WITH OWNERACCESS OPTION] 用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。 1 FROM子句 FROM 子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 得到的复合结果。如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。例：下列SQL语句返回所有有定单的客户： SELECT OrderID,Customer.customerID FROM Orders Customers WHERE Orders.CustomerID=Customers.CustomeersID 2 ALL、DISTINCT、DISTINCTROW、TOP谓词 (1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。例：SELECT ALL FirstName,LastName FROM Employees (2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。 (3) DISTINCTROW 如果有重复的记录，只返回一个 (4) TOP显示查询头尾若干记录。也可返回记录的百分比，这是要用 TOP N PERCENT子句（其中N 表示百分比）例：返回5%定货额最大的定单 SELECT TOP 5 PERCENT* FROM [ Order Details] ORDER BY UnitPrice*Quantity*(1-Discount) DESC 3 用 AS 子句为字段取别名如果想为返回的列取一个新的标题，或者，经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。例：返回FirstName字段取别名为NickName SELECT FirstName AS NickName ,LastName ,City FROM Employees 例：返回新的一列显示库存价值 SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock FROM Products 二 .WHERE 子句指定查询条件 1 比较运算符比较运算符含义 = 大于等于 <= 小于等于 !> 不大于 !#1/1/96# AND OrderDate#96-1-1# 也可以表示为： WHERE OrderDate>Datevalue(‘1/1/96’) 使用 NOT 表达式求反。例：查看96年1月1日以后的定单 WHERE Not OrderDateQuantity 另一种方法是用 Microsof JET SQL 独有的 JNNER JOIN FROM table1 INNER JOIN table2 ON table1.field1 comparision table2.field2 其中comparision 就是前面WHERE子句用到的比较运算符。 SELECT FirstName,lastName,OrderID,CustomerID,OrderDate FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID INNER JOIN不能连接Memo OLE Object Single Double 数据类型字段。在一个JOIN语句中连接多个ON子句 SELECT fields FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field1 AND ON table1.field2 compopr table2.field2 OR ON table1.field3 compopr table2.field3 SELECT fields FROM table1 INNER JOIN （table2 INNER JOIN [( ]table3 [INNER JOER] [( ]tablex[INNER JOIN] ON table1.field1 compopr table2.field1 ON table1.field2 compopr table2.field2 ON table1.field3 compopr table2.field3 外部连接返回更多记录，在结果中保留不匹配的记录，不管存不存在满足条件的记录都要返回另一侧的所有记录。 FROM table [LEFT|RIGHT]JOIN table2 ON table1.field1comparision table.field2 用左连接来建立外部连接，在表达式的左边的表会显示其所有的数据例：不管有没有定货量，返回所有商品 SELECT ProductName ,OrderID FROM Products LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID 右连接与左连接的差别在于：不管左侧表里有没有匹配的记录，它都从左侧表中返回所有记录。例：如果想了解客户的信息，并统计各个地区的客户分布，这时可以用一个右连接，即使某个地区没有客户，也要返回客户信息。空值不会相互匹配，可以通过外连接才能测试被连接的某个表的字段是否有空值。 SELECT * FROM talbe1 LEFT JOIN table2 ON table1.a=table2.c 1 连接查询中使用 Iif函数实现以0值显示空值 Iif 表达式： Iif(IsNull(Amount,0,Amout) 例：无论定货大于或小于￥50，都要返回一个标志。 Iif([Amount]>50,?Big order?,?Small order?) 五. 分组和总结查询结果在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。 GROUP BY 子句的语法 SELECT fidldlist FROM table WHERE criteria [GROUP BY groupfieldlist [HAVING groupcriteria]] 注：Microsoft Jet数据库 Jet 不能对备注或OLE对象字段分组。 GROUP BY字段中的Null值以备分组但是不能被省略。在任何SQL合计函数中不计算Null值。 GROUP BY子句后最多可以带有十个字段，排序优先级按从左到右的顺序排列。例：在‘WA’地区的雇员表中按头衔分组后，找出具有同等头衔的雇员数目大于1人的所有头衔。 SELECT Title ,Count(Title) as Total FROM Employees WHERE Region = ‘WA’ GROUP BY Title HAVING Count(Title)>1 JET SQL 中的聚积函数聚集函数意义 SUM ( ) 求和 AVG ( ) 平均值 COUNT ( ) 表达式中记录的数目 COUNT (* ) 计算记录的数目 MAX 最大值 MIN 最小值 VAR 方差 STDEV 标准误差 FIRST 第一个值 LAST 最后一个值六. 用Parameters声明创建参数查询 Parameters声明的语法: PARAMETERS name datatype[,name datatype[, …]] 其中name 是参数的标志符,可以通过标志符引用参数. Datatype说明参数的数据类型. 使用时要把PARAMETERS 声明置于任何其他语句之前. PARAMETERS[Low price] Currency,[Beginning date]datatime SELECT OrderID ,OrderAmount FROM Orders WHERE OrderAMount>[low price] AND OrderDate>=[Beginning date] 七. 功能查询所谓功能查询 ,实际上是一种操作查询 ,它可以对数据库进行快速高效的操作.它以选择查询为目的,挑选出符合条件的数据,再对数据进行批处理.功能查询包括更新查询 ,删除查询 ,添加查询 ,和生成表查询 . 1 更新查询 UPDATE子句可以同时更改一个或多个表中的数据.它也可以同时更改多个字段的值. 更新查询语法: UPDATE 表名 SET 新值 WHERE 准则例:英国客户的定货量增加5%,货运量增加3% UPDATE OEDERS SET OrderAmount = OrderAmount *1.1 Freight = Freight*1.03 WHERE ShipCountry = ‘UK’ 2 删除查询 DELETE子句可以使用户删除大量的过时的或冗于的数据. 注:删除查询的对象是整个记录. DELETE子句的语法: DELETE [ 表名.*] FROM 来源表 WHERE 准则例: 要删除所有94年前的定单 DELETE * FROM Orders WHERE OrderData<#94-1-1# 3 追加查询 INSERT子句可以将一个或一组记录追加到一个或多个表的尾部. INTO 子句指定接受新记录的表 valueS 关键字指定新记录所包含的数据值. INSERT 子句的语法: INSETR INTO 目的表或查询 (字段1,字段2,…) valueS(数值1,数值2,…) 例:增加一个客户 INSERT INTO Employees(FirstName,LastName,title) valueS(‘Harry’,’Washington’,’Trainee’) 4 生成表查询可以一次性地把所有满足条件的记录拷贝到一张新表中.通常制作记录的备份或副本或作为报表的基础. SELECT INTO子句用来创建生成表查询语法: SELECT 字段1,字段2,… INTO 新表 [IN 外部数据库 ] FROM 来源数据库 WHERE 准则例:为定单制作一个存档备份 SELECT * INTO OrdersArchive FROM Orders 八. 联合查询 UNION运算可以把多个查询的结果合并到一个结果集里显示. UNION运算的一般语法: [ 表 ] 查询 1 UNION [ALL] 查询 2 UNION … 例:返回巴西所有供给商和客户的名字和城市 SELECT CompanyName,City FROM Suppliers WHERE Country = ‘Brazil’ UNION SELECT CompanyName,City FROM Customers WHERE Country = ‘Brazil’ 缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项 UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同. 每一个查询参数中可以使用 GROUP BY 子句或 HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用 OREER BY子句. 九. 交叉查询交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部. Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法: TRANSFORM aggfunction SELECT 语句 GROUP BY 子句 PIVOT pivotfield[IN(value1 [,value2[,…]]) ] Aggfounction指SQL聚积函数, SELECT语句选择作为标题的的字段, GROUP BY 分组 Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制它的取值. value代表创建列标题的固定值. 例:显示在1996年里每一季度每一位员工所接的定单的数目: TRANSFORM Count(OrderID) SELECT FirstName&’’&LastName AS FullName FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID WHERE DatePart(“yyyy”,OrderDate)= ‘1996’ GROUP BY FirstName&’’&LastName ORDER BY FirstName&’’&LastName POVOT DatePart(“q”,OrderDate)&’季度’ 十 .子查询子查询可以理解为套查询 .子查询是一个SELECT语句. 1 表达式的值与子查询返回的单一值做比较表达式 comparision [ANY|ALL|SOME](子查询 ) ANY 和SOME谓词是同义词,与比较运算符(=,,,=)一起使用 .返回一个布尔值True或 False.ANY的意思是, 表达式与子查询返回的一系列的值逐一比较,只要其中的一次比较产生True结果,ANY测试的返回 True值(既WHERE子句的结果),对应于该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的一系列的值的比较都产生 True结果,才回返回True值. 例:主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品 SELECT * FROM Products WHERE UnitPrice>ANY (SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25) 2 检查表达式的值是否匹配子查询返回的一组值的某个值 [NOT]IN(子查询 ) 例:返回库存价值大于等于1000的产品. SELECT ProductName FROM Products WHERE ProductID IN (SELECT PrdoctID FROM [Order DEtails] WHERE UnitPrice*Quantity>= 1000) 3检测子查询是否返回任何记录 [NOT]EXISTS (子查询 ) 例:用EXISTS检索英国的客户 SELECT ComPanyName,ContactName FROM Orders WHERE EXISTS (SELECT * FROM Customers WHERE Country = ‘UK’ AND Customers.CustomerID= Orders.CustomerID) table_schemaas'数据库 ', sum(table_rows)as'记录数', sum(truncate(data_length/1024/1024, 2))as'数据容量(MB)', sum(truncate(index_le... Mysql 数据库的数据大小查看：1、进入information_schema 数据库（存放了其他的数据库的信息） mysql -> use information_schema;2、查询所有数据的大小： mysql -> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;3、查看指定数据库的大小：比如查看 MySQL 数据库占用操作系统内存，主要分为两部分：第一部分为：innodb 存储引擎占用的buffer pool size，一部分为myism 存储引擎占用的内存大小第二部分为： MySQL 数据库应用会话占用的内存大小 1.切换数据库 useinformation_sch. 在 mysql 中有一个information_schema数据库，这个数据库中装的是 mysql 的元数据，包括数据库信息、数据库中表的信息等。所以要想查询数据库占用磁盘的空间大小可以通过对information_schema数据库进行操作。 information_schema中的表主要有： schemata 表：这个表里面主要是存储在 mysql 中的所有的数据... 1.char的长度不可变，二varchar的长度是可变的； 2.char的存取速度比varchar要快，应为长度固定，方便程序的存储和查找； 3.char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节； 4.一般用 char 类型的存放固定的数据； 5.两者的存储数据都非unicode的字符数据。字符串：（1）Char：固定长度，长度为n个字节，取值范围1~8 000，存储大小是n个字节。（2）Varchar：可变长度，n 的取值范围为1-8 000 。max 指定最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加两个字节。所输入数据的长度可以为0个字符。（3）Text：长度可变，最大长度为2^31-1（2 147 483 647）个字符。 Unicode字符串：（1）Nchar：n 个字符的固定长度的Unicode字符数据。n值必须在1~4 000 之间。存储大小为两倍的n字节。 mysql > use information_schema; mysql > select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES; +-----------+ | data | +-----------+ | 3052.76MB | +-----------+ 1 row in set (0.02 sec) 统计一下所有库数... 4、查看指定数据库的某个表的大小比如查看数据库 sys 中 sys_config 表的大小。1、进入information_schema 数据库（存放了其他的数据库的信息）3、查看指定数据库的大小：比如查看数据库 sys 的大小。...