- 浏览: 181965 次
文章分类
最新评论
-
luxing44530:
你好, 关于 JDK 7中也有一个支持协程方式的实现 在那呀? ...
Java性能调优笔记 -
songgz:
你是一个会思考的人,前途无量,代码审查显然不能以风格和规则为主 ...
到底该怎么样做代码审查? -
jiaoronggui:
遇到过一模一样的问题
一条Select语句导致瓶颈 -
Leon.Wood:
写出让计算机读懂的代码很容易,写出让人读懂的只有高手才能做到. ...
到底该怎么样做代码审查? -
ajuanlove:
不经常用这玩意
对300万一张表数据,用游标进行循环,不同写法的效率比较
mysql学习之路一(转)
原文章地址:http://www.itpub.net/thread-788049-1-1.html
1 MYSQL安装
先下载安装包:
mysql-5.0.27-win32.zip
mysql-noinstall-6.0.0-alpha-win32.zip
下载了2个版本:一个5.0.27安装版;一个6.0.0非安装版。本人测试用的为5.0.27安装版,安装过程不再赘述。各个操作系统平台的安装可以看MYSQL联机文档。
2 登录及一些基本操作
本章的主要目的是让我们对MYSQL的基础框架有个大概的了解。
1 连接与断开MYSQL服务器
安装完毕登陆MYSQL(有过一些其他数据库基础的人都应该很容易使用这几步):
我们可以利用如下参数查看MYSQL命令的帮助:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql –help
联接MYSQL服务器:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -h localhost -uroot -p888888
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.27-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| root |
+--------------------+
3 rows in set (0.08 sec)
安装完毕,都会有这几个默认的数据库。
注意到目前没有连接到任何数据库。
mysql> SELECT DATABASE();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.78 sec)
mysql> QUIT
Bye
我们也可以在连接MYSQL服务器的时候指定想要连接的数据库,如下:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user=root -p mysql
Enter password: ******
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.27-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
INFORMATION_SCHEMA数据库
我们在介绍MYSQL基本操作的同时顺便把INFORMATION_SCHEM数据库做个简单介绍:
类似其他数据库的数据字典,各个字典含义不做详述,以下摘自MYSQL联机文档:
INFORMATION_SCHEMA提供了访问数据库元数据的方式。
元数据是关于数据的数据,如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和
“系统目录”。INFORMATION_SCHEMA是信息数据库,其中保存着关于MySQL服务器所维护的所有其他数据库的信息。在INFORMATION_SCHEMA
中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。
2 使用数据库
mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT DATABASE();
+--------------------+
| database() |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT VERSION(), CURRENT_DATE, CURDATE(), NOW(), USER();
+---------------------+--------------+------------+---------------------+-------
---------+
| VERSION() | CURRENT_DATE | CURDATE()| NOW() | USER()
|
+---------------------+--------------+------------+---------------------+-------
---------+
| 5.0.27-community-nt | 2007-05-24 | 2007-05-24 | 2007-05-24 17:01:16 | root@l
ocalhost |
+---------------------+--------------+------------+---------------------+-------
---------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'version';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| version | 5.0.27-community-nt |
+---------------+---------------------+
1 row in set (0.13 sec)
3 MYSQL的SHOW命令
前边的例子中我们已经用过了MYSQL的SHOW命令:
mysql> SHOW DATABASES;
SHOW命令可用于获取关于INFORMATION_SCHEMA本身结构的信息。
一些SHOW语句允许使用FROM、WHERE子句,这样,在指定需要显示的行时,可更为灵活。下边给出部分例子:
mysql> SHOW TABLES FROM MYSQL;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
16 rows in set (0.00 sec)
SHOW TABLES命令显示了当前用数据库中的数据库对象列表,而从TABLES视图的查询我们将得到所有数据库下的对象列表。这个例子就是给出了一个查询MYSQL的表相关的系统视图,类似ORACLE中的(DBA_TABLES、USER_TABLES)和SYBASE中的SYSOBJECTS。
mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM TABLES;
+---------------------------------------+-------------+--------+
| table_name | table_type| engine |
+---------------------------------------+-------------+--------+
| CHARACTER_SETS | SYSTEM VIEW | MEMORY |
| COLLATIONS | SYSTEM VIEW | MEMORY |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |
| COLUMNS | SYSTEM VIEW | MyISAM |
| COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY |
| KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY |
| ROUTINES | SYSTEM VIEW | MyISAM |
| SCHEMATA | SYSTEM VIEW | MEMORY |
| SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY |
| STATISTICS | SYSTEM VIEW | MEMORY |
| TABLES | SYSTEM VIEW | MEMORY |
| TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY |
| TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY |
| TRIGGERS | SYSTEM VIEW | MyISAM |
| USER_PRIVILEGES | SYSTEM VIEW | MEMORY |
| VIEWS | SYSTEM VIEW | MyISAM |
| columns_priv | BASE TABLE| MyISAM |
| db | BASE TABLE| MyISAM |
| func | BASE TABLE| MyISAM |
| help_category | BASE TABLE| MyISAM |
| help_keyword | BASE TABLE| MyISAM |
| help_relation | BASE TABLE| MyISAM |
| help_topic | BASE TABLE| MyISAM |
| host | BASE TABLE| MyISAM |
| proc | BASE TABLE| MyISAM |
| procs_priv | BASE TABLE| MyISAM |
| tables_priv | BASE TABLE| MyISAM |
| time_zone | BASE TABLE| MyISAM |
| time_zone_leap_second | BASE TABLE| MyISAM |
| time_zone_name | BASE TABLE| MyISAM |
| time_zone_transition | BASE TABLE| MyISAM |
| time_zone_transition_type | BASE TABLE| MyISAM |
| user | BASE TABLE| MyISAM |
+---------------------------------------+-------------+--------+
33 rows in set (0.03 sec)
mysql> SHOW COLUMNS FROM TABLES;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES| | NULL | |
| TABLE_SCHEMA | varchar(64)| NO | | | |
| TABLE_NAME | varchar(64)| NO | | | |
| TABLE_TYPE | varchar(64)| NO | | | |
| ENGINE | varchar(64)| YES| | NULL | |
| VERSION | bigint(21) | YES| | NULL | |
| ROW_FORMAT | varchar(10)| YES| | NULL | |
| TABLE_ROWS | bigint(21) | YES| | NULL | |
| AVG_ROW_LENGTH| bigint(21) | YES| | NULL | |
| DATA_LENGTH | bigint(21) | YES| | NULL | |
| MAX_DATA_LENGTH | bigint(21) | YES| | NULL | |
| INDEX_LENGTH | bigint(21) | YES| | NULL | |
| DATA_FREE | bigint(21) | YES| | NULL | |
| AUTO_INCREMENT| bigint(21) | YES| | NULL | |
| CREATE_TIME | datetime | YES| | NULL | |
| UPDATE_TIME | datetime | YES| | NULL | |
| CHECK_TIME | datetime | YES| | NULL | |
| TABLE_COLLATION | varchar(64)| YES| | NULL | |
| CHECKSUM | bigint(21) | YES| | NULL | |
| CREATE_OPTIONS| varchar(255) | YES| | NULL | |
| TABLE_COMMENT | varchar(80)| NO | | | |
+-----------------+--------------+------+-----+---------+-------+
21 rows in set (0.06 sec)
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset| Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2| DOS Kamenicky Czech-Slovak| keybcs2_general_ci| 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8| GEOSTD8 Georgian | geostd8_general_ci| 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms| UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)
mysql> SHOW CHARACTER SET like 'big5';
+---------+--------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+--------------------------+-------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
+---------+--------------------------+-------------------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM COLLATIONS WHERE COLLATION_NAME LIKE '%big5%';
+-----------------+--------------------+----+------------+-------------+--------
-+
| COLLATION_NAME| CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN
|
+-----------------+--------------------+----+------------+-------------+--------
-+
| big5_chinese_ci | big5 |1 | Yes | Yes | 1
|
| big5_bin | big5 | 84 | | Yes | 1
|
+-----------------+--------------------+----+------------+-------------+--------
-+
2 rows in set (0.00 sec)
mysql> SHOW GRANTS;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DA2
8842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR ROOT;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Grants for root@%
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%
' IDENTIFIED BY PASSWORD '*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB' WITH GRANT
OPTION |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)
我们也可以通过查询系统表来获得用户的权限:
mysql> SELECT * FROM USER_PRIVILEGES;
下边给出了MYSQL的权限列表功参考:
Privilege Meaning
ALL [PRIVILEGES] Sets all simple privileges except GRANT OPTION
ALTER Enables use of ALTER TABLE
ALTER ROUTINE Enables stored routines to be altered or dropped
CREATE Enables use of CREATE TABLE
CREATE ROUTINE Enables creation of stored routines
CREATE TEMPORARY TABLES Enables use of CREATE TEMPORARY TABLE
CREATE USER Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW Enables use of CREATE VIEW
DELETE Enables use of DELETE
DROP Enables use of DROP TABLE
EXECUTE Enables the user to run stored routines
FILE Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX Enables use of CREATE INDEX and DROP INDEX
INSERT Enables use of INSERT
LOCK TABLES Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Enables use of SHOW FULL PROCESSLIST
REFERENCES Not implemented
RELOAD Enables use of FLUSH
REPLICATION CLIENT Enables the user to ask where slave or master servers are
REPLICATION SLAVE Needed for replication slaves (to read binary log events from the master)
SELECT Enables use of SELECT
SHOW DATABASES SHOW DATABASES shows all databases
SHOW VIEW Enables use of SHOW CREATE VIEW
SHUTDOWN Enables use of mysqladmin shutdown
SUPER Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE Enables use of UPDATE
USAGE Synonym for “no privileges”
GRANT OPTION Enables privileges to be granted
关于SHOW命令我们就简单介绍这么几个,如果想知道更多的SHOW命令可以得到的信息内容可以执行如下命令来获取帮助或者参看MYSQL的联机文档第23章:INFORMATION_SCHEMA信息数据库。
mysql> HELP SHOW
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]
The SHOW statement also has forms that provide information about
replication master and slave servers and are described in [HELP PURGE
MASTER LOGS]:
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL `%' and `_'
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.0/en/extended-show.html.
相关推荐
前一阶段,做一个项目,使用到了mysql,工作之余,顺便收集了一些资料,对mysql初学者,或对此有一点兴趣的同仁有点帮助。
机房精空调分类2023+MySQL学习练习数据库+MySQL学习毕业设计机房精空调分类2023+MySQL学习练习数据库+MySQL学习毕业设计机房精空调分类2023+MySQL学习练习数据库+MySQL学习毕业设计机房精空调分类2023+MySQL学习练习...
个人推荐mysql学习资料, 很不错,好东西不但要珍藏,还要分享
数据库 Mysql转oracle sql脚本转oracle脚本
MySqlFrm.exe是c#版本的frm转sql工具,需要.net 4.0与mysql环境,CMD命令行如下: mysqlfrm <username> <password> <port> 例如: mysqlfrm root pass 3306 c:\dbcopy 会将c:\dbcopy目录下所有的frm转换为...
mysql阳历转农历的mysql 函数mysql阳历转农历的mysql 函数mysql阳历转农历的mysql 函数
MySQL学习资源总贴MySQL学习资源总贴MySQL学习资源总贴
主要介绍了Mysql中文汉字转拼音的实现,并且每个汉字会转换全拼,使用Mysql自定义函数实现,需要的朋友可以参考下
MySQL函数 数字转中文 非金额 处理数据类型 decimal(21,6)
mysql数据库转oracle工具,解压即用,支持表结构转换a
Access-to-MySQL Access数据库转MySQL数据库 DBF-to-MySQL DBF数据库转MySQL数据库 Excel-to-MySQL Excel数据库转MySQL数据库 MSSQL-to-MySQL MSSQL数据库转MySQL数据库 SQL Server数据库转MySQL数据库 MySQL-to-...
MySQL学习笔记
sqlite数据库转mysql数据库
mysql学习用数据库dbt3
mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql5.7镜像安装+mysql学习+mysql5.7镜像包 mysql5.7安装教程+mysql...
MySQL学习源码(MySQL入门教程).zipMySQL学习源码(MySQL入门教程).zipMySQL学习源码(MySQL入门教程).zipMySQL学习源码(MySQL入门教程).zipMySQL学习源码(MySQL入门教程).zipMySQL学习源码(MySQL入门教程).zipMySQL...
Dbmover for Mysql to Oracle 是高效的将Mysql导入到Oracle数据库的工具。 使用DBMover可以灵活定义Mysql和Oracle之间表和字段的对照关系,也可以在DBMover创建一个查询,把查询结果当作源表转入到Oracle中。 ...
从mysql数据库迁移到postgres数据库转换ddl语句的工具,添加了自动更新时间的触发器。
国外大牛写的一个mysql数据库转换postgresql的脚本。亲试可以无错误运行。
数据库mysql学习脑图,对于学习mysql的朋友们会起到一定的帮助!