本文共 13239 字,大约阅读时间需要 44 分钟。
MySQL Windows安装包说明: 1、mysql-5.5.20-win32.msi:Windows 安装包,图形化的下一步下一步的安装。 2、mysql-5.5.20.zip,这个是windows源文件,需要编译,对应的Linux源文件是mysql-5.5.20.tar.gz 3、mysql-5.5.20-win32.zip,这个文件解包后即可使用,是编译好的windows32位Mysql。 1、下载mysql-5.5.20-win32.zip,解压到D:\dev,D盘的dev文件夹下就会出现mysql-5.5.20-win32目录,将其重命名为mysql。 2、配置MYSQL的环境变量 新增系统变量MYSQL_HOME: D:\dev\mysql 在PATH变量的最后面添加: ;%MYSQL_HOME%\bin 保存即可。 3、打开文件my-default.ini另存为my.ini,删除my.ini中的所有配置,在my.ini文件中加入如下简单配置:(my.ini是保存在与my-default.ini同一个目录下的)(#表示注释) - # The following options will be passed to all MySQL clients
- [client]
- #password = your_password
- port = 3306
-
- [mysql]
- #设置mysql客户端的字符集
- default-character-set = utf8
-
- # The MySQL server
- [mysqld]
- port = 3306
- #设置mysql的安装目录
- basedir = D:\dev\mysql
- #设置mysql数据库的数据存放目录,必须是data或者\xxx-data
- datadir = D:\dev\mysql\data
- #设置服务器段的字符集
- character_set_server = utf8
4、注册服务 开始菜单,搜索cmd,单击右键“以管理员身份运行”,输入命令: - mysqld --install mysql5 --defaults-file=d:\dev\mysql\my.ini
或着, 开始菜单,搜索cmd,单击右键“以管理员身份运行”,输入命令: 删除服务(开始菜单,搜索cmd,单击右键“以管理员身份运行”): 在“服务”中就会出现mysql这一项。 5、启动服务(开始菜单,搜索cmd,单击右键“以管理员身份运行”): 停止服务: 6、服务启动后: 登录MySQL服务器: 命令格式: - mysql -h hostname -u username -p
或 - mysql -hhostname -uusername -p
命令说明:mysql命令将调用MySQL监视程序,这是一个可以将我们连接到MySQL服务器端的客户端命令行工具。 选项说明: -h选项:用于指定所希望连接的主机,即运行MySQL服务器的机器。如果在运行MySQL服务器的机器上运行该命令,则可以忽略该选项和hostname参数;如果不是,必须用运行MySQL服务器的主机名称来代替主机名称参数。 -u命令:用于指定连接数据库时使用的用户名称。 -p命令:用于指定用户输入的密码 此时我本机安装了MYSQL,可忽略该选项和hostname参数: 注: MySQL的管理员用户名为root,密码默认为空 修改root密码 MySQL配置好后,启动成功,默认密码是空,但是为了安全,设置密码(MySQL有一个默认用户名为root,密码自己设定:假如设为root)。 1)登录MySQL root用户: 打开命令行,执行: 2)修改root密码: - mysql> update mysql.user set password="root" where User="root";
- mysql> flush privileges;
修改该修改密码的语句:update mysql.user set password="root" where User="root"; 为: update mysql.user set password=password("root") where User="root"; 详细说明:见最底下的补充说明。 以后再进入MySQL,则为: 7、常用命令: - create database new_dbname;--新建数据库
- show databases;--显示数据库
- use databasename;--使用数据库
- select database();--查看已选择的数据库
-
- show tables;--显示当前库的所有表
- create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..)[ENGINE=engine_name];--创建表
- create table tablename select statement;--通过子查询创建表
- desc tablename;--查看表结构
- show create table tablename;--查看建表语句
-
- alter table tablename add new_fielname new_fieldtype;--新增列
- alter table tablename modify fieldname new_fieldtype;--修改列
- alter table tablename_old rename tablename_new;--表重命名
-
- insert into tablename(fieldname1,fieldname2,fieldnamen) valuse(value1,value2,valuen);--增
- delete from tablename [where fieldname=value];--删
- update tablename set fieldname1=new_value where filename2=value;--改
- select * from tablename [where filename=value];--查
-
- show engines;--查看mysql现在已提供的存储引擎:
- show variables like '%storage_engine%';--查看mysql当前默认的存储引擎
- show create table tablename;--查看某张表用的存储引擎(结果的"ENGINE="部分)
- alter table tablename ENGINE=InnoDB--修改引擎
- create table tablename(fieldname1 fieldtype1,fieldname2 fieldtype2,..) ENGINE=engine_name;--创建表时设置存储引擎
8、例如: (1)登录MySQL服务器后,查看当前时间,登录的用户以及数据库的版本 - mysql> select now(),user(),version();
- +---------------------+----------------+-----------+
- | now() | user() | version() |
- +---------------------+----------------+-----------+
- | 2012-02-26 20:29:51 | root@localhost | 5.5.20 |
- +---------------------+----------------+-----------+
- 1 row in set (0.00 sec)
(2)显示数据库列表 - mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 rows in set (0.03 sec)
(3)新增数据库并查看 - mysql> create database test_db;
- Query OK, 1 row affected (0.00 sec)
-
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- | test_db |
- +--------------------+
- 5 rows in set (0.00 sec)
(4)选择数据库 - mysql> use test_db;
- Database changed
查看已选择的数据库: - mysql> select database();
- +------------+
- | database() |
- +------------+
- | test_db |
- +------------+
- 1 row in set (0.00 sec)
(5)显示当前数据库的所有数据表 - mysql> show tables;
- Empty set (0.00 sec)
(6)新建数据表并查看 - mysql> create table person(
- -> id int,
- -> name varchar(20),
- -> sex char(1),
- -> birth date
- -> );
- Query OK, 0 rows affected (0.09 sec)
- mysql> show tables;
- +-------------------+
- | Tables_in_test_db |
- +-------------------+
- | person |
- +-------------------+
- 1 row in set (0.00 sec)
(7)获取表结构 - mysql> desc person;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(1) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 4 rows in set (0.01 sec)
或者 - mysql> describe person;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(1) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 4 rows in set (0.01 sec)
(8)查询表中的数据 - mysql> select * from person;
- Empty set (0.00 sec)
(9)插入数据 - mysql> insert into person(id,name,sex,birth)
- -> values(1,'zhangsan','1','1990-01-08');
- Query OK, 1 row affected (0.04 sec)
查询表中的数据: - mysql> select * from person;
- +------+----------+------+------------+
- | id | name | sex | birth |
- +------+----------+------+------------+
- | 1 | zhangsan | 1 | 1990-01-08 |
- +------+----------+------+------------+
- 1 row in set (0.00 sec)
(10)修改字段的类型 - mysql> alter table person modify sex char(8);
- Query OK, 1 row affected (0.17 sec)
- Records: 1 Duplicates: 0 Warnings: 0
查看字段描述: - mysql> desc person;
- +-------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(8) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- +-------+-------------+------+-----+---------+-------+
- 4 rows in set (0.01 sec)
(11)新增一个字段 - mysql> alter table person add(address varchar(50));
- Query OK, 1 row affected (0.27 sec)
- Records: 1 Duplicates: 0 Warnings: 0
查看字段描述: - mysql> desc person;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(8) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- | address | varchar(50) | YES | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 5 rows in set (0.01 sec)
(12)更新字段内容 查看修改前表的内容: - mysql> select * from person;
- +------+----------+------+------------+---------+
- | id | name | sex | birth | address |
- +------+----------+------+------------+---------+
- | 1 | zhangsan | 1 | 1990-01-08 | NULL |
- +------+----------+------+------------+---------+
- 1 row in set (0.00 sec)
修改: - mysql> update person set name='lisi' where id=1;
- Query OK, 1 row affected (0.04 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- mysql> select * from person;
- +------+------+------+------------+---------+
- | id | name | sex | birth | address |
- +------+------+------+------------+---------+
- | 1 | lisi | 1 | 1990-01-08 | NULL |
- +------+------+------+------------+---------+
- 1 row in set (0.00 sec)
-
- mysql> update person set sex='man',address='China' where id=1;
- Query OK, 1 row affected (0.04 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- mysql> select * from person;
- +------+------+------+------------+---------+
- | id | name | sex | birth | address |
- +------+------+------+------------+---------+
- | 1 | lisi | man | 1990-01-08 | China |
- +------+------+------+------------+---------+
- 1 row in set (0.00 sec)
为了方便下面测试删除数据,在向person表中插入2条数据: - mysql> insert into person(id,name,sex,birth,address)
- -> values(2,'wangwu','man','1990-01-10','China');
- Query OK, 1 row affected (0.02 sec)
-
- mysql> insert into person(id,name,sex,birth,address)
- -> values(3,'zhangsan','man','1990-01-10','China');
- Query OK, 1 row affected (0.04 sec)
-
- mysql> select * from person;
- +------+----------+------+------------+---------+
- | id | name | sex | birth | address |
- +------+----------+------+------------+---------+
- | 1 | lisi | man | 1990-01-08 | China |
- | 2 | wangwu | man | 1990-01-10 | China |
- | 3 | zhangsan | man | 1990-01-10 | China |
- +------+----------+------+------------+---------+
- 3 rows in set (0.00 sec)
(13)删除表中的数据 删除表中指定的数据: - mysql> delete from person where id=2;
- Query OK, 1 row affected (0.02 sec)
-
- mysql> select * from person;
- +------+----------+------+------------+---------+
- | id | name | sex | birth | address |
- +------+----------+------+------------+---------+
- | 1 | lisi | man | 1990-01-08 | China |
- | 3 | zhangsan | man | 1990-01-10 | China |
- +------+----------+------+------------+---------+
- 2 rows in set (0.00 sec)
删除表中全部的数据: - mysql> delete from person;
- Query OK, 2 rows affected (0.04 sec)
-
- mysql> select * from person;
- Empty set (0.00 sec)
(14)重命名表 查看重命名前的表名: - mysql> show tables;
- +-------------------+
- | Tables_in_test_db |
- +-------------------+
- | person |
- +-------------------+
- 1 row in set (0.00 sec)
重命名: - mysql> alter table person rename person_test;
- Query OK, 0 rows affected (0.04 sec)
-
- mysql> show tables;
- +-------------------+
- | Tables_in_test_db |
- +-------------------+
- | person_test |
- +-------------------+
- 1 row in set (0.00 sec)
(15)新增主键 - mysql> alter table person_test add primary key(id);
- Query OK, 0 rows affected (0.11 sec)
- Records: 0 Duplicates: 0 Warnings: 0
-
- mysql> desc person_test;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | id | int(11) | NO | PRI | 0 | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(8) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- | address | varchar(50) | YES | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 5 rows in set (0.00 sec)
删除主键: - mysql> alter table person_test drop primary key;
- Query OK, 0 rows affected (0.18 sec)
- Records: 0 Duplicates: 0 Warnings: 0
-
- mysql> desc person_test;
- +---------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+-------------+------+-----+---------+-------+
- | id | int(11) | NO | | 0 | |
- | name | varchar(20) | YES | | NULL | |
- | sex | char(8) | YES | | NULL | |
- | birth | date | YES | | NULL | |
- | address | varchar(50) | YES | | NULL | |
- +---------+-------------+------+-----+---------+-------+
- 5 rows in set (0.01 sec)
(16)删除表 - mysql> drop table person_test;
- Query OK, 0 rows affected (0.04 sec)
-
- mysql> show tables;
- Empty set (0.00 sec)
(17)删除数据库 - mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- | test_db |
- +--------------------+
- 5 rows in set (0.00 sec)
-
- mysql> drop database test_db;
- Query OK, 0 rows affected (0.11 sec)
-
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
(18)查看建表语句 - mysql> show create table table_name;
补充说明: update mysql.user set password="root" where User="root";修改的不是密码,如果按照这个方式修改了,重新登录时将会报错: - mysql> update mysql.user set password="root" where User="root";
- Query OK, 3 rows affected (0.00 sec)
- Rows matched: 3 Changed: 3 Warnings: 0
-
- mysql> exit
- Bye
-
- C:\Users\liqiong>mysql -uroot -p
- Enter password: ****
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
- ES)
请按照以下方式重新修改密码,即可登录成功: - C:\Users\liqiong>mysql -uroot
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.5.20 MySQL Community Server (GPL)
-
- Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
-
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
-
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
- mysql> update mysql.user set password=password("root") where User="root";
- Query OK, 3 rows affected (0.00 sec)
- Rows matched: 3 Changed: 3 Warnings: 0
-
- mysql> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> exit
- Bye
-
- C:\Users\liqiong>mysql -uroot -p
- Enter password: ****
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 5
- Server version: 5.5.20 MySQL Community Server (GPL)
-
- Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
-
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
-
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
- mysql>
转载地址:http://nyvsi.baihongyu.com/