MYSQL常识和基本操作

Archer

mysql常识和基本操作
mysql常识和基本操作


字段类型
　　 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

　　 BLOB或TEXT类型，最大长度为65535(2^16-1)个字符。

　　 6．VARCHAR

　　 变长字符串类型。

　　 1: 显示数据库
　
　　 SHOW DATABAS，

　　 2：当前选择的数据库，

　　 mysql> SELECT DATABASE();
　　 +------------+
　　 │ DATABASE() │
　　 +------------+
　　 │ test　;
　　 +------------+
　　 │ DATABASE() │
　　 +------------+
　　 │ test　　　 │
　　 +------------+

　　 当前数据库包含的表信息：

　　 mysql> SHOW TABLES;
　　 +---------------------+
　　 │ Tables in test　
　　 mysql> SHOW TABLES;
　　 +---------------------+
　　 │ Tables in test　　　│
　　 +---------------------+
　　 │ mytable1　　　　　　│
　　 │ mytable2　　　　　　│
　　 +---------------------+

　　 获取表结构

　　 mysql> desc mytable1;
　　 +---------+-------------+------+-----+---------+-------+
　　 │ Field　 mysql> desc mytable1;
　　 +---------+-------------+------+-----+---------+-------+
　　 │ Field　 │ Type　　│ Null │ Key │ Default │ Extra │
　　 +---------+-------------+------+-----+---------+-------+
　　 │ s1　　│ varchar(20) │ YES　│NULL　　│　　　 │
　　 +---------------------+------+-----+---------+-------+



　　 5.导入数据库表
　　 （1）创建.sql文件

　　 （2）先产生一个库如auction.c:mysqlbin>mysqladmin -u root -p creat auction，会提
示输入密码，然后成功创建。

　　 （2）导入auction.sql文件

　　 c:mysqlbin>mysql -u root -p auction < auction.sql。

　　 通过以上操作，就可以创建了一个数据库auction以及其中的一个表auction。

　　 6．修改数据库

　　 （1）在mysql的表中增加字段：

　　 alter table dbname add column userid int(11) not null primary key
auto_increment;

　　 这样，就在表dbname中添加了一个字段userid，类型为int(11)。

　　 7．mysql数据库的授权

　　 mysql>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

Archer

MYSQL的常用显示命令   
使用一段时间MYSQL+APACHE
经常需要查询MYSQL命令
不胜其烦。
故整理出来很多命令放在一起
希望朋友们不用再边做程序边翻书：）
这里是第一部分，常用显示命令：
1、显示数据库列表。
show databases;
2、显示库中的数据表：
use mysql； show tables;
3、显示数据表的结构：
describe 表名;
4、建库：
create database 库名;
5、建表：
use 库名；
create table 表名 (字段设定列表)；
6、删库和删表:
drop database 库名;
drop table 表名；
7、将表中记录清空：
delete from 表名;
8、显示表中的记录：
select * from 表名

Archer

MYSQL初学者使用指南

有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接MYSQL、修改密
码、增加用户等方面来学习一些MYSQL的常用命令。
一、连接MYSQL。
格式： mysql -h主机地址 -u用户名 －p用户密码
1、例1：连接到本机上的MYSQL。
首先在打开DOS窗口，然后进入目录 mysqlbin，再键入命令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 （回车）
二、修改密码。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
1、例1：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令
mysqladmin -uroot -password ab12
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
2、例2：再将root的密码改为djg345。
mysqladmin -uroot -pab12 password djg345
三、增加新用户。（注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分
号作为命令结束符）
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"
例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插
入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：
grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";
但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的
任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见例2。
例2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查
询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样
用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的
web页来访问了。
grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
如果你不想test2有密码，可以再打一个命令将密码消掉。
grant select,insert,update,delete on mydb.* to test2@localhost identified by "";


(下篇)
在上篇我们讲了登录、增加用户、密码更改等问题。下篇我们来看看MYSQL中有关数据库方面的操
作。注意：你必须首先登录到MYSQL中，以下操作都是在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 valuess('','glchengang','深圳一中','1976-10-10');
insert into teacher valuess('','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密码 < c:\school.sql
如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注
释去掉即可使用）。

四、将文本数据转到数据库中
1、文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替.
例：
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
2、数据传入命令 load data local infile "文件名" into table 表名;
注意：你最好将文件复制到\mysql\bin目录下，并且要先用use命令打表所在的库 。

五、备份数据库：（命令在DOS的\mysql\bin目录下执行）
mysqldump --opt school>school.bbb
注释:将数据库school备份到school.bbb文件，school.bbb是一个文本文件，文件名任取，打开看
看你会有新发现。

后记：其实MYSQL的对数据库的操作与其它的SQL类数据库大同小异，您最好找本将SQL的书看看。
我在这里只介绍一些基本的，其实我也就只懂这些了，呵呵。最好的MYSQL教程还是“晏子“译的
“MYSQL中文参考手册“不仅免费每个相关网站都有下载，而且它是最权威的。可惜不是象"HP4
中文手册"那样是chm的格式，在查找函数命令的时候不太方便

Archer

mysql经验点滴


关于mysql自动增加字段
create table test (user varchar(10),id int AUTO_INCREMENT not null,primary key(id))
一个特殊用法：
找出id为最大值的记录，即最近插入的记录。
select * from test where id is NULL
这条语句有好大的作用。

mysql和sql server一个语法区别：
sql server中：
查出前10条记录语法：select top 10 * from user
mysql中：
查出前10条记录语法：select * from user limit 10

mysql和access的一个语法区别：
access字段类型有：
number,text,memo
mysql中对应的类型为：
int或bigint，varchar或char,text
其中text在access中为字符串型，而在mysql中为备注型。

当然还有好多区别，大家可以看一下mysql文档就知道了。

Archer

MySQL的几点用法：

在php4中对MySQL进行操作常用的方法是：
<?php
$hostname="localhost";
$username="root";//用户名
$password=" "; //密码
$dbname="mysql";//mysql为要使用的数据库
mysql_connect($hostname,$username,$password);
mysql_select_db("$dbname");
$sql="select * from table ";
$result=mysql_query($sql);
?>

通过对$result进行操作提取记录
对于日期和时间函数可用以下sql语句：
SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
来选择当前日期前30天的记录！

启动和停止MySQL服务：

NET START mysql
NET STOP mysql

你可以这样杀死MySQL服务器，执行：

C:mysqlbinmysqladmin -u root shutdown

通过执行下列命令，你能测试MySQL是否正在工作：
C:mysqlbinmysqlshow
C:mysqlbinmysqlshow -u root mysql
C:mysqlbinmysqladmin version status proc
C:mysqlbinmysql test

为root用户增加一个口令：（下列例子通过删除匿名用户，允许任何人存取“测试”数据库）

C:mysqlbinmysql mysql
或者C:mysqlbinuse mysql
mysql>select * from user
>g
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> QUIT
C:mysqlbinmysqladmin reload
C:mysqlbinmysqladmin -u root

Archer

mysql中文排序的方法


mysql在查询字符串时是大小写不敏感的,在编绎mysql时一般以ISO-8859字符集作为默认的字符
集,因此在比较过程中中文编码字符大小写转换造成了这种现象,比较应急的方法是对于包含中文
的字段加上"binary"属性,使之作二进制比较, 例如将
"name char(10)"改成"name char(10)binary"."




MySQL数据库结构和数据的导出和导入



导出要用到MySQL的mysqldump工具，基本用法是：

shell> mysqldump [OPTIONS] database [tables]

如果你不给定任何表，整个数据库将被导出。

通过执行mysqldump --help，你能得到你mysqldump的版本支持的选项表。

注意，如果你运行mysqldump没有--quick或--opt选项，mysqldump将在导出结果前装载整个结果
集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。

mysqldump支持下列选项：

--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
--add-drop-table
在每个create语句之前增加一个drop table。
--allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
-c, --complete-insert
使用完整的insert语句(用列名字)。
-C, --compress
如果客户和服务器均支持压缩，压缩两者间所有的信息。
--delayed
用INSERT DELAYED命令插入行。
-e, --extended-insert
使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）
-#, --debug[=option_string]
跟踪程序的使用(为了调试)。
--help
显示一条帮助消息并且退出。
--fields-terminated-by=...
　
--fields-enclosed-by=...
　
--fields-optionally-enclosed-by=...
　
--fields-escaped-by=...
　
--fields-terminated-by=...
这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。
LOAD DATA INFILE语法。
-F, --flush-logs
在开始导出前，洗掉在MySQL服务器中的日志文件。
-f, --force,
即使我们在一个表导出期间得到一个SQL错误，继续。
-h, --host=..
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。
-l, --lock-tables.
为开始导出锁定所有表。
-t, --no-create-info
不写入表创建信息(CREATE TABLE语句）
-d, --no-data
不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！
--opt
同--quick --add-drop-table --add-locks --extended-insert --lock-tables。
应该给你为读入一个MySQL服务器的尽可能最快的导出。
-pyour_pass, --password[=your_pass]
与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口
令。
-P port_num, --port=port_num
与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用
Unix套接字。）
-q, --quick
不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。
-S /path/to/socket, --socket=/path/to/socket
与localhost连接时（它是缺省主机)使用的套接字文件。
-T, --tab=path-to-some-directory
对于每个给定的表，创建一个table_name.sql文件，它包含SQL CREATE 命令，和一个
table_name.txt文件，它包含数据。 注意：这只有在mysqldump运行在mysqld守护进程运行的同
一台机器上的时候才工作。.txt文件的格式根据--fields-xxx和--lines--xxx选项来定。
-u user_name, --user=user_name
与服务器连接时，MySQL使用的用户名。缺省值是你的Unix登录名。
-O var=option, --set-variable var=option设置一个变量的值。可能的变量被列在下面。
-v, --verbose
冗长模式。打印出程序所做的更多的信息。
-V, --version
打印版本信息并且退出。
-w, --where='where-condition'
只导出被选择了的记录；注意引号是强制的！
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

最常见的mysqldump使用可能制作整个数据库的一个备份：

mysqldump --opt database > backup-file.sql

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的：

mysqldump --opt database │ mysql --host=remote-host -C database

由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了：

shell> mysqladmin create target_db_name
shell> mysql target_db_name < backup-file.sql

Archer

MySQL的数据类型和建库策略

无论是在小得可怜的免费数据库空间或是大型电子商务网站，合理的设计表结构、充分利用空间
是十分必要的。这就要求我们对数据库系统的常用数据类型有充分的认识。下面我就将我的一点
心得写出来跟大家分享。

一、数字类型。数字类型按照我的分类方法分为三类：整数类、小数类和数字类。
我所谓的“数字类”，就是指DECIMAL和NUMERIC，它们是同一种类型。它严格的说不是一种数字
类型，因为他们实际上是将数字以字符串形式保存的；他的值的每一位（包括小数点）占一个字
节的存储空间，因此这种类型耗费空间比较大。但是它的一个突出的优点是小数的位数固定，在
运算中不会“失真”，所以比较适合用于“价格”、“金额”这样对精度要求不高但准确度要求
非常高的字段。
小数类，即浮点数类型，根据精度的不同，有FLOAT（单精度）和DOUBLE（双精度）两种。它们的
优势是精确度，FLOAT可以表示绝对值非常小、小到约 1.17E-38 （0.000...0117， 小数点后面
有37个零）的小数，而DOUBLE更是可以表示绝对值小到约 2.22E-308 （0.000...0222， 小数点
后面有307个零）的小数。FLOAT类型和DOUBLE类型占用存储空间分别是4字节和8字节。如果需要
用到小数的字段，精度要求不高的，当然用FLOAT了！可是说句实在话，我们“民用”的数据，哪
有要求精度那么高的呢？这两种类型至今我没有用过——我还没有遇到适合于使用它们的事例。
用的最多的，最值得精打细算的，是整数类型。从只占一个字节存储空间的TINYINT到占8个字节
的BIGINT，挑选一个“够用”并且占用存储空间最小的类型是设计数据库时应该考虑的。
TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT占用存储空间分别为1字节、2字节、3字节、4字节
和8字节，就无符号的整数而言，这些类型能表示的最大整数分别为255、65535、16777215、
4294967295和18446744073709551615。如果用来保存用户的年龄（举例来说，数据库中保存年龄
是不可取的），用TINYINT就够了；九城的《纵横》里，各项技能值，用SMALLINT也够了；如果要
用作一个肯定不会超过16000000行的表的AUTO_INCREMENT的IDENTIFY字段，当然用 MEDIUMINT 不
用 INT ，试想，每行节约一个字节，16000000行可以节约10兆多呢！

二、日期时间类型。
日期和时间类型比较简单，无非是 DATE、TIME、DATETIME、TIMESTAMP和YEAR等几个类型。只对
日期敏感，而对时间没有要求的字段，就用DATE而不用DATETIME是不用说的了；单独使用时间的
情况也时有发生——使用TIME；但最多用到的还是用DATETIME。在日期时间类型上没有什么文章
可做，这里就不再详述。

三、字符（串）类型。
不要以为字符类型就是 CHAR ！CHAR和VARCHAR的区别在于CHAR是固定长度，只要你定义一个字段
是CHAR(10),那么不论你存储的数据是否达到了10个字节，它都要占去10个字节的空间；而
VARVHAR则是可变长度的，如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个
字符，把它定义为 VARCHAR(10)是最合算的，VARCHAR 类型的实际长度是它的值的(实际长度
+1)。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度呀！从这个“+1”中也应该看
到，如果一个字段，它的可能值最长是10个字符，而多数情况下也就是用到了10个字符时，用
VARCHAR就不合算了：因为在多数情况下，实际占用空间是11个字节，比用CHAR(10)还多占用一个
字节！
举个例子，就是一个存储股票名称和代码的表，股票名称绝大部分是四个字的，即8个字节；股票
代码，上海的是六位数字，深圳的是四位数字。这些都是固定长度的，股票名称当然要用 CHAR
(8) ；股票代码虽然是不固定长度，但如果使用VARVHAR(6)，一个深圳的股票代码实际占用空间
是5个字节，而一个上海的股票代码要占用7个字节！考虑到上海的股票数目比深圳的多，那么用
VARCHAR(6)就不如CHAR(6)合算了。
虽然一个CHAR或VARVHAR的最大长度可以到255，我认为大于20的CHAR是几乎用不到的——很少有
大于20个字节长度的固定长度的东东吧？不是固定长度的就用VARCHAR！大于100的VARCHAR也是几
乎用不到的——比这更大的用TEXT就好了。TINYTEXT，最大长度为255，占用空间也是(实际长度
+1)；TEXT，最大长度65535，占用空间是(实际长度+2)；MEDIUMTEXT，最大长度16777215，占用
空间是(实际长度+3)；LONGTEXT，最大长度4294967295，占用空间是(实际长度+4)。为什么
“+1”？“+2”？“+3”？“+4”？你要是还不知道就该打PP了。这些可以用在论坛啊、新闻
啊，什么的，用来保存文章的正文。根据实际情况的不同，选择从小到大的不同类型。

四、枚举和集合类型。
枚举（ENUM）类型，最多可以定义65535种不同的字符串从中做出选择，只能并且必须选择其中一
种，占用存储空间是一个或两个字节，由枚举值的数目决定；集合（SET）类型，最多可以有64个
成员，可以选择其中的零个到不限定的多个，占用存储空间是一个到八个字节，由集合可能的成
员数目决定。
举个例子来说，在SQLServer中，你可以节约到用一个Bit类型来表示性别（男/女），但MySQL没
有Bit，用TINTINT？不，可以用ENUM(帅哥,美眉)！只有两种选择，所以只需一个字节——跟
TINYINT一样大，但却可以直接用字符串帅哥和美眉来存取。真是太方便啦！

好了，MySQL的数据类型介绍得差不多，我的建库策略也随着介绍数据类型介绍给大家一些。但这
只是其中一部分，篇幅有限，不能再细说；其他的，就靠各人在对数据类型理解的基础上，多多
实践、多多讨论。