MySQL 教程（转）

moonsky

MySQL 教程

最近不太忙，写些东西供大家参考，如果能给您带来些帮助，那将是我最大的欣慰。只是些入门级的东西，高级应用还请大家帮忙，多多指教。

初级篇

一、MySQL的安装
　　MySQL的安装非常简单，一路Next就可以了，推荐安装到c:\下。然后复制c:\MySQL\my-example.cnf到c:\windows下改名为：my.ini。把innodb_data_file_path = ibdata1:200M前的“#”去掉，如果你的磁盘空间不富裕的话可以把此项的200M改为20M。这样会省去你180M的空间。
　　在c:\mysql下建一个名为：areware的文件夹（如果有就不必了）。
　　在命令提示符下运行：mysqld -shareware。
　　然后运行:mysql -u root 会出现：
C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.46-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
哦，可爱的mysql>提示符终于出现了。
注:每一个MySQL语句都以分号结尾，中间可以换行。


二、建立新数据库、表

1.建立数据库，数据库名为sampledb，下面是命令和显示的结果。（下同）
mysql> create database sampledb;
Query OK, 1 row affected (0.02 sec)

2.建立表user
首先选择数据库
mysql> use sampledb;
Database changed
然后建立表，命令如下：
mysql> create table users
-> (
-> id int unsigned not null default 0 auto_increment primary key,
-> name varchar(10),
-> sex enum("f","m") not null default "f",
-> birthday date default "0000-00-00",
-> email varchar(30),
-> city varchar(30)
-> );
Query OK, 0 rows affected (0.02 sec)

也可以用下面的方法：
在c:\mysql\bin下建立文本文件my.sql，内容如下：
create table user1
(
id int unsigned not null default 0 auto_increment primary key,
name varchar(10),
sex enum("f","m") not null default "f",
birthday date default "0000-00-00",
email varchar(30),
city varchar(30)
);

在命令提示符下如下操作：
C:\mysql\bin>mysql sampledb<my.sql

会产生同样的结果。

int unsigned为无符号型整数
default指定默认值
auto_increment为自动编号
primary key指定该列为主键
varchar 为可变长字符串括号中为字符串的最大长度
enum() 指定该列为枚举型，括号中为可选值的列表
date 为日期型格式为“yyyy-mm-dd”即“年-月-日”，这道符合中国人的习惯


我们建好了数据库和表，如何显示他们呢？
显示所有你能看到的数据库（就看你拥有多大的权限了）：
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| sampledb |
| test |
+----------+
3 rows in set (0.00 sec)
看到了吧！sampledb 就在里边。

显示当前使用的数据库：
mysql> select database();
+------------+
| database() |
+------------+
| sampledb |
+------------+
1 row in set (0.04 sec)

显示表：
mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| users |
+--------------------+
1 row in set (0.00 sec)

显示表的字段属性：
mysql> describe users;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| sex | enum('f','m') | | | f | |
| birthday | date | YES | | 0000-00-00 | |
| email | varchar(30) | YES | | NULL | |
| city | varchar(30) | YES | | NULL | |
+----------+------------------+------+-----+------------+----------------+
6 rows in set (0.03 sec)

这就是我们的劳动成果。

三、插入数据

插入数据用insert 语句。

1.当指定全部列的值时可用
insert into table_name values(value1,value2,value3……)的形式。

如：
mysql> insert into users values(1,"zhang3","m","1979-05-30","[email protected]","beijing");
Query OK, 1 row affected (0.00 sec)

2.当指定部分列的值时可用

mysql> insert into users(name,sex,email,city) values ("li4","m","[email protected]","shanghai");
Query OK, 1 row affected (0.00 sec)

或者用下列形式：
mysql> insert into users set name="liuhong",birthday="1979-08-10",city="shijiazhuang";
Query OK, 1 row affected (0.00 sec)

前者可以同时增加多条记录

mysql> insert into users(name,birthday,email) values ("xiaofang","1980-12-06","x
[email protected]"),("liuqian","1983-05-28","[email protected]");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

做了这么多到底结果是什么样子呢？请看下一部分


四、数据库的检索


mysql> select * from users;
+----+----------+-----+------------+---------------------+--------------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+---------------------+--------------+
| 1 | zhang3 | m | 1979-05-30 | [email protected] | beijing |
| 2 | li4 | m | 0000-00-00 | [email protected] | shanghai |
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
| 4 | xiaofang | f | 1980-12-06 | [email protected] | NULL |
| 5 | liuqian | f | 1983-05-28 | [email protected] | NULL |
+----+----------+-----+------------+---------------------+--------------+
5 rows in set (0.00 sec)

哈哈，看到我们的劳动成果了吧。

如果我只想显示某些符合条件的纪录，怎么办呢？


这里面有没有同年同月生的呢？还是请MySQL帮我查一下吧！

mysql> select * from users where birthday="1979-08-10";
+----+---------+-----+------------+-------+--------------+
| id | name | sex | birthday | email | city |
+----+---------+-----+------------+-------+--------------+
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
+----+---------+-----+------------+-------+--------------+
1 row in set (0.00 sec)

我想打印一份生日清单，只显示姓名和生日，不知道生日的就不要显示了：
mysql> select name,birthday from users
-> where birthday!="0000-00-00";
+----------+------------+
| name | birthday |
+----------+------------+
| zhang3 | 1979-05-30 |
| liuhong | 1979-08-10 |
| xiaofang | 1980-12-06 |
| liuqian | 1983-05-28 |
+----------+------------+
4 rows in set (0.00 sec)

我想知道谁没填自己的所在城市：
mysql> select * from users
-> where city is null;
+----+----------+-----+------------+------------------+------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+------------------+------+
| 4 | xiaofang | f | 1980-12-06 | [email protected] | NULL |
| 5 | liuqian | f | 1983-05-28 | [email protected] | NULL |
+----+----------+-----+------------+------------------+------+
2 rows in set (0.01 sec)

填写了城市名称的人的资料如何显示呢？
mysql> select * from users
-> where city is not null;
+----+---------+-----+------------+---------------------+--------------+
| id | name | sex | birthday | email | city |
+----+---------+-----+------------+---------------------+--------------+
| 1 | zhang3 | m | 1979-05-30 | [email protected] | beijing |
| 2 | li4 | m | 0000-00-00 | [email protected] | shanghai |
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
+----+---------+-----+------------+---------------------+--------------+
3 rows in set (0.00 sec)

要是表头能换成中文的就好了，看着还方便一些，MySQL早就想到这一点了：
mysql> select id as "编号", name as "姓名", birthday as "生日",city as "所在城市
" from users;
+------+----------+------------+--------------+
| 编号 | 姓名 | 生日 | 所在城市 |
+------+----------+------------+--------------+
| 1 | zhang3 | 1979-05-30 | beijing |
| 2 | li4 | 0000-00-00 | shanghai |
| 3 | liuhong | 1979-08-10 | shijiazhuang |
| 4 | xiaofang | 1980-12-06 | NULL |
| 5 | liuqian | 1983-05-28 | NULL |
+------+----------+------------+--------------+
5 rows in set (0.00 sec)

我想按姓名排序（默认为升序）：
mysql> select * from users order by name;
+----+----------+-----+------------+---------------------+--------------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+---------------------+--------------+
| 2 | li4 | m | 0000-00-00 | [email protected] | shanghai |
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
| 5 | liuqian | f | 1983-05-28 | [email protected] | NULL |
| 4 | xiaofang | f | 1980-12-06 | [email protected] | NULL |
| 1 | zhang3 | m | 1979-05-30 | [email protected] | beijing |
+----+----------+-----+------------+---------------------+--------------+
5 rows in set (0.00 sec)
按出生日期降序排列：
mysql> select * from users order by birthday desc;
+----+----------+-----+------------+---------------------+--------------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+---------------------+--------------+
| 5 | liuqian | f | 1983-05-28 | [email protected] | NULL |
| 4 | xiaofang | f | 1980-12-06 | [email protected] | NULL |
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
| 1 | zhang3 | m | 1979-05-30 | [email protected] | beijing |
| 2 | li4 | m | 0000-00-00 | [email protected] | shanghai |
+----+----------+-----+------------+---------------------+--------------+
5 rows in set (0.00 sec)

五、数据库的更新

把xiaofang的所在城市改为shijiazhuang电子信箱改为：[email protected]：

mysql> update users set city="shijiazhuang",email="[email protected]" where name="xiaofang";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

显示一下结果：
mysql> select * from users
-> where name="xiaofang";
+----+----------+-----+------------+-----------------------+--------------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+-----------------------+--------------+
| 4 | xiaofang | f | 1980-12-06 | [email protected] | shijiazhuang |
+----+----------+-----+------------+-----------------------+--------------+
1 row in set (0.00 sec)


Ok!成功了！


六、删除操作

删除纪录，把li4从数据库中删除

mysql> delete from users where name="li4";
Query OK, 1 row affected (0.02 sec)

看看该记录是不是从数据库中删除了呢？
mysql> select * from users;
+----+----------+-----+------------+-----------------------+--------------+
| id | name | sex | birthday | email | city |
+----+----------+-----+------------+-----------------------+--------------+
| 1 | zhang3 | m | 1979-05-30 | [email protected] | beijing |
| 3 | liuhong | f | 1979-08-10 | NULL | shijiazhuang |
| 4 | xiaofang | f | 1980-12-06 | [email protected] | shijiazhuang |
| 5 | liuqian | f | 1983-05-28 | [email protected] | NULL |
+----+----------+-----+------------+-----------------------+--------------+
4 rows in set (0.00 sec)

删除纪录前要确保where 子句限定的纪录就是所要删除的纪录，如果有多个名为li4的人，where 字句中就要加上其它的条件，比如id 等。删除前最好用select 显示一下where 子句限定的范围，做到准确无误。

删除表
mysql> drop table users;
Query OK, 0 rows affected (0.08 sec)

用show tables 显示一下结果
mysql> show tables;
Empty set (0.03 sec)

已经没有表了。

删除数据库
mysql> drop database sampledb;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)已经没有sampledb了

但是用下面的命令
mysql> select database();
+------------+
| database() |
+------------+
| sampledb |
+------------+
1 row in set (0.03 sec)

sampledb怎么还存在？你试着在里边建一个表看看，你会发现已经不能建了，MySQL报告有错误发生。
我个人认为用select database()显示当前数据库的时候不应该再出现sampledb了，或许这里面还有什么高深的东西，由于我也还是个菜鸟，还请告诉小弟。

中级篇

先让我们建好数据库和表
mysql> create database sampledb;
Query OK, 1 row affected (0.07 sec)

在c:\mysql\bin\下建一个文件：my.sql 内容如下：
#my.sql开始
create table users
(
userid int unsigned not null default 0 auto_increment primary key,
name varchar(10),
sex enum("男","女") not null default "女",
birthday date default "0000-00-00",
email varchar(30),
city varchar(30)
);

create table account
(
id int unsigned not null default 0 auto_increment primary key,
userid int unsigned not null,
bookid int unsigned not null,
bortime date not null,#借书时间
rettime date default null#还书时间
);

create table books
(
bookid int unsigned not null default 0 auto_increment primary key,
author varchar(20),
price float,
name varchar(20)
);

#my.sql结束

然后执行：
C:\mysql\bin>mysql sampledb<my.sql



C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.46-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sampledb;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| account |
| books |
| users |
+--------------------+
3 rows in set (0.00 sec)
看到我们间的三个表了吧。

建立my1.sql内容如下：

#my1.sql开始
insert into users(name,sex,birthday,email,city)
values
("张明","男","1977-6-12","[email protected]","北京"),
("刘芳","女","1980-11-18","[email protected]","石家庄"),
("刘新民","男","1979-1-16","[email protected]","天津"),
("李建军","男","1980-08-07","[email protected]","武汉"),
("李华","女","1978-04-25","[email protected]","石家庄"),
("李欢","男","1983-05-10","[email protected]","济南"),
("张海","男","1986-06-20","[email protected]","温州"),
("赵燕","女","1984-05-31","[email protected]","拉萨"),
("李薇","女","1977-11-16","[email protected]","北京");

#my1.sql结束

执行：
C:\mysql\bin>mysql sampledb<my1.sql
建立my2.sql内容如下：
insert into books(bookid,author,price,name)
values
(1,"沈志强",23,"我的海洋"),
(2,"周海洋",17,"启明星"),
(3,"山本",16,"日本旅游指南"),
(4,"江南剑",21,"血海深仇"),
(5,"菲菲",20,"永不分离"),
(6,"马克",25,"我的中国之行"),
(7,"王雪",15,"我与我的学生们"),
(8,"张海涛",30,"中国与WTO");

C:\mysql\bin>mysql sampledb<my2.sql

建立my3.sql内容如下：

insert into account (userid,bookid,bortime,rettime)
values
(3,4,"2002-11-10","2002-11-15"),
(5,1,"2002-11-10","2002-11-13"),
(4,4,"2002-11-10","2002-11-12"),
(6,2,"2002-11-10","2002-11-14"),
(7,6,"2002-11-11","2002-11-15"),
(9,8,"2002-11-11","2002-11-18"),
(3,6,"2002-11-11","2002-11-16"),
(4,1,"2002-11-11","2002-11-14"),
(6,2,"2002-11-12","2002-11-18"),
(1,6,"2002-11-12","2002-11-16"),
(2,3,"2002-11-12","2002-11-16"),
(3,2,"2002-11-12","2002-11-19");


模糊查询：

%（半角的百分号）代表任意长字符串，长度可以为零，_（半角下划线）代表任意一个字符
显示所有姓李的用户：

mysql> select * from users
-> where name like "李%";
+--------+--------+------+------------+----------------------+--------+
| userid | name | sex | birthday | email | city |
+--------+--------+------+------------+----------------------+--------+
| 4 | 李建军 | 男 | 1980-08-07 | [email protected] | 武汉 |
| 5 | 李华 | 女 | 1978-04-25 | [email protected] | 石家庄 |
| 6 | 李欢 | 男 | 1983-05-10 | [email protected] | 济南 |
| 9 | 李薇 | 女 | 1977-11-16 | [email protected] | 北京 |
+--------+--------+------+------------+----------------------+--------+
4 rows in set (0.00 sec)

显示所有姓名是两个字的用户：
mysql> select * from users
-> where name like "____";# 引号中为四个下划线，一个汉字要用两个字节表示
+--------+------+------+------------+------------------------+--------+
| userid | name | sex | birthday | email | city |
+--------+------+------+------------+------------------------+--------+
| 1 | 张明 | 男 | 1977-06-12 | [email protected] | 北京 |
| 2 | 刘芳 | 女 | 1980-11-18 | [email protected] | 石家庄 |
| 5 | 李华 | 女 | 1978-04-25 | [email protected] | 石家庄 |
| 6 | 李欢 | 男 | 1983-05-10 | [email protected] | 济南 |
| 7 | 张海 | 男 | 1986-06-20 | [email protected] | 温州 |
| 8 | 赵燕 | 女 | 1984-05-31 | [email protected] | 拉萨 |
| 9 | 李薇 | 女 | 1977-11-16 | [email protected] | 北京 |
+--------+------+------+------------+------------------------+--------+
7 rows in set (0.00 sec)


多个库的检索

显示：用户借书清单

mysql> select users.name as "姓名",users.sex as "性别",books.name as "书名",acco
unt.rettime-account.bortime as "天数"
-> from users,books,account
-> where account.userid=users.userid and account.bookid=books.bookid;
+--------+------+--------------+------+
| 姓名 | 性别 | 书名 | 天数 |
+--------+------+--------------+------+
| 刘新民 | 男 | 血海深仇 | 5 |
| 李华 | 女 | 我的海洋 | 3 |
| 李建军 | 男 | 血海深仇 | 2 |
| 李欢 | 男 | 启明星 | 4 |
| 张海 | 男 | 我的中国之行 | 4 |
| 李薇 | 女 | 中国与WTO | 7 |
| 刘新民 | 男 | 我的中国之行 | 5 |
| 李建军 | 男 | 我的海洋 | 3 |
| 李欢 | 男 | 启明星 | 6 |
| 张明 | 男 | 我的中国之行 | 4 |
| 刘芳 | 女 | 日本旅游指南 | 4 |
| 刘新民 | 男 | 启明星 | 7 |
+--------+------+--------------+------+

查询统计：

统计库中共有多少本书：

mysql> select count(name) as "数量" from books;
+------+
| 数量 |
+------+
| 8 |
+------+
1 row in set (0.00 sec)

显示每个人借过几本书：

mysql> select users.name,count(account.userid) as "数量"
-> from users,account
-> where users.userid=account.userid
-> group by account.userid;
+--------+------+
| name | 数量 |
+--------+------+
| 张明 | 1 |
| 刘芳 | 1 |
| 刘新民 | 3 |
| 李建军 | 2 |
| 李华 | 1 |
| 李欢 | 2 |
| 张海 | 1 |
| 李薇 | 1 |
+--------+------+
8 rows in set (0.00 sec)

显示借书数量大于1的用户

mysql> select users.name,count(account.userid) as "times"
-> from users,account
-> where users.userid=account.userid
-> group by account.userid having times>1;
+--------+-------+
| name | times |
+--------+-------+
| 刘新民 | 3 |
| 李建军 | 2 |
| 李欢 | 2 |
+--------+-------+
3 rows in set (0.00 sec)

在这个查询中，“times”最好不要换成中文，否则可能会报错的，我用的系统就是这样（Win XP+MySQL3.23.46）

限制纪录的显示数量：
mysql> select * from account limit 5;
+----+--------+--------+------------+------------+
| id | userid | bookid | bortime | rettime |
+----+--------+--------+------------+------------+
| 1 | 3 | 4 | 2002-11-10 | 2002-11-15 |
| 2 | 5 | 1 | 2002-11-10 | 2002-11-13 |
| 3 | 4 | 4 | 2002-11-10 | 2002-11-12 |
| 4 | 6 | 2 | 2002-11-10 | 2002-11-14 |
| 5 | 7 | 6 | 2002-11-11 | 2002-11-15 |
+----+--------+--------+------------+------------+
5 rows in set (0.02 sec)
从第一条到第五条：
mysql> select * from account limit 1,5;
+----+--------+--------+------------+------------+
| id | userid | bookid | bortime | rettime |
+----+--------+--------+------------+------------+
| 2 | 5 | 1 | 2002-11-10 | 2002-11-13 |
| 3 | 4 | 4 | 2002-11-10 | 2002-11-12 |
| 4 | 6 | 2 | 2002-11-10 | 2002-11-14 |
| 5 | 7 | 6 | 2002-11-11 | 2002-11-15 |
| 6 | 9 | 8 | 2002-11-11 | 2002-11-18 |
+----+--------+--------+------------+------------+
5 rows in set (0.00 sec)

可以看出：上面查询显示的是第2-6条记录，怎么回事呢？原来，在MySQL中首记录的的指针号为0 。

MySQL中关于时间的问题

有没有跟我生日相同的人呢？请看这个查询语句：

mysql> select name,birthday
-> from users
-> where month(birthday)=6 and dayofmonth(birthday)=12
-> ;
+------+------------+
| name | birthday |
+------+------------+
| 张明 | 1977-06-12 |
+------+------------+
1 row in set (0.02 sec)

哦，还真有一个，是1977年出生的。

有今天过生日的吗？

mysql> select name,birthday
-> from users
-> where month(birthday)=month(CURRENT_DATE) and dayofmonth(birthday)=dayofm
onth(CURRENT_DATE);
+------+------------+
| name | birthday |
+------+------------+
| 刘芳 | 1980-11-18 |
+------+------------+
1 row in set (0.00 sec)

今天是刘芳的生日，怎么她自己都忘了？我得提醒她一声，今中午又有饭吃了。

由于本人时间和水平有限，还有好多好多的东西不能完成，只能先写到这里了。欢迎大家接着写下去，把MySQL的经验奉献给大家。

djklhm

mysql的c++库需不需要另外安装？