MySQL的管理员用户名为root，密码默认为空修改root密码MySQL配置好后，启动成功，默认密码是空，但是为了安全，设置密码（MySQL有一个默认用户名为root，密码自己设定：假如设为root）。

1）登录MySQL root用户：打开命令行，执行：Mysql代码1mysql -uroot -p2）修改root密码：Mysql代码2mysql> update er set password="root" where User="root";3mysql> flush privileges;修改该修改密码的语句：update er set password="root" where User="root"; 为：update er set password=password("root") where User="root";详细说明：见最底下的补充说明。

以后再进入MySQL，则为：Mysql代码4mysql -uroot -proot7、常用命令：Mysql代码5show databases;--显示数据库6use databasename; --用数据库7show tables;--显示表8create table tablename(field-name-1 fieldtype-1 modifiers,field-name-2fieldtype-2 modifiers,....);--创建表9alter table tablename add new-fielname new fieldtype--为表加入新列10insert intotablename(fieldname-1,fieldname-2,fieldname-n)valuse(value-1,value-2,value-n)--增11delete from tablename where fieldname=value--删12update tablename set fieldname=new-value where id=1--改13select * from tablename--查14desc tablename--表定义描述15show create table tablename--可以查看引擎16alter table tablename engine=InnoDB--修改引擎17create table tablename(id int(11),name varchar(10) )type=INNODB--建表是设置引擎8、例如：（1）登录MySQL服务器后，查看当前时间，登录的用户以及数据库的版本Mysql代码18mysql> select now(),user(),version();19+---------------------+----------------+-----------+ 20| now() | user() | version() | 21+---------------------+----------------+-----------+ 22| 2012-02-2620:29:51 | root@localhost | |23+---------------------+----------------+-----------+ 241 row in set (0.00 sec)（2）显示数据库列表Mysql代码25mysql> show databases;26+--------------------+27| Database |28+--------------------+29| information_schema |30| mysql |31| performance_schema |32| test |33+--------------------+344 rows in set (0.03 sec)（3）新增数据库并查看Mysql代码35mysql> create database test_db;36Query OK, 1 row affected (0.00 sec)37mysql> show databases;38+--------------------+39| Database |40+--------------------+41| information_schema |42| mysql |43| performance_schema |44| test |45| test_db |46+--------------------+475 rows in set (0.00 sec)（4）选择数据库Mysql代码49Database changed查看已选择的数据库：Mysql代码50mysql> select database();51+------------+52| database() |53+------------+54| test_db |55+------------+561 row in set (0.00 sec)（5）显示当前数据库的所有数据表Mysql代码57mysql> show tables;58Empty set (0.00 sec)（6）新建数据表并查看Mysql代码59mysql> create table person(60 -> id int,61 -> name varchar(20),62 -> sex char(1),63 -> birth date64 -> );65Query OK, 0 rows affected (0.09 sec) Mysql代码66mysql> show tables;67+-------------------+68| Tables_in_test_db |69+-------------------+70| person |71+-------------------+721 row in set (0.00 sec)（7）获取表结构Mysql代码74+-------+-------------+------+-----+---------+-------+ 75| Field | Type | Null | Key | Default | Extra | 76+-------+-------------+------+-----+---------+-------+ 77| id | int(11) | YES | | NULL | | 78| name | varchar(20) | YES | | NULL | | 79| sex | char(1) | YES | | NULL | | 80| birth | date | YES | | NULL | | 81+-------+-------------+------+-----+---------+-------+ 824 rows in set (0.01 sec)或者Mysql代码83mysql> describe person;84+-------+-------------+------+-----+---------+-------+ 85| Field | Type | Null | Key | Default | Extra | 86+-------+-------------+------+-----+---------+-------+ 87| id | int(11) | YES | | NULL | | 88| name | varchar(20) | YES | | NULL | | 89| sex | char(1) | YES | | NULL | | 90| birth | date | YES | | NULL | | 91+-------+-------------+------+-----+---------+-------+ 924 rows in set (0.01 sec)（8）查询表中的数据Mysql代码93mysql> select * from person;94Empty set (0.00 sec)（9）插入数据Mysql代码95mysql> insert into person(id,name,sex,birth)96 -> values(1,'zhangsan','1','1990-01-08');97Query OK, 1 row affected (0.04 sec)查询表中的数据：Mysql代码98mysql> select * from person;99+------+----------+------+------------+100| id | name | sex | birth |101+------+----------+------+------------+102| 1 | zhangsan | 1 | 1990-01-08 |103+------+----------+------+------------+1041 row in set (0.00 sec)（10）修改字段的类型Mysql代码105mysql> alter table person modify sex char(8);106Query OK, 1 row affected (0.17 sec)107Records: 1 Duplicates: 0 Warnings: 0查看字段描述：Mysql代码108mysql> desc person;109+-------+-------------+------+-----+---------+-------+ 110| Field | Type | Null | Key | Default | Extra | 111+-------+-------------+------+-----+---------+-------+ 112| id | int(11) | YES | | NULL | |113| name | varchar(20) | YES | | NULL | |114| sex | char(8) | YES | | NULL | |115| birth | date | YES | | NULL | |116+-------+-------------+------+-----+---------+-------+ 1174 rows in set (0.01 sec)（11）新增一个字段Mysql代码118mysql> alter table person add(address varchar(50));119Query OK, 1 row affected (0.27 sec)120Records: 1 Duplicates: 0 Warnings: 0查看字段描述：Mysql代码121mysql> desc person;122+---------+-------------+------+-----+---------+-------+ 123| Field | Type | Null | Key | Default | Extra | 124+---------+-------------+------+-----+---------+-------+ 125| id | int(11) | YES | | NULL | | 126| name | varchar(20) | YES | | NULL | | 127| sex | char(8) | YES | | NULL | | 128| birth | date | YES | | NULL | |129| address | varchar(50) | YES | | NULL | |130+---------+-------------+------+-----+---------+-------+1315 rows in set (0.01 sec)（12）更新字段内容查看修改前表的内容：Mysql代码132mysql> select * from person;133+------+----------+------+------------+---------+134| id | name | sex | birth | address |135+------+----------+------+------------+---------+136| 1 | zhangsan | 1 | 1990-01-08 | NULL |137+------+----------+------+------------+---------+1381 row in set (0.00 sec)修改：Mysql代码139mysql> update person set name='lisi' where id=1;140Query OK, 1 row affected (0.04 sec)141Rows matched: 1 Changed: 1 Warnings: 0142mysql> select * from person;143+------+------+------+------------+---------+144| id | name | sex | birth | address |145+------+------+------+------------+---------+146| 1 | lisi | 1 | 1990-01-08 | NULL |147+------+------+------+------------+---------+1481 row in set (0.00 sec)149mysql> update person set sex='man',address='China' where id=1;150Query OK, 1 row affected (0.04 sec)151Rows matched: 1 Changed: 1 Warnings: 0152mysql> select * from person;153+------+------+------+------------+---------+154| id | name | sex | birth | address |155+------+------+------+------------+---------+156| 1 | lisi | man | 1990-01-08 | China |157+------+------+------+------------+---------+1581 row in set (0.00 sec)为了方便下面测试删除数据，在向person表中插入2条数据：Mysql代码159mysql> insert into person(id,name,sex,birth,address)160 -> values(2,'wangwu','man','1990-01-10','China');161Query OK, 1 row affected (0.02 sec)162mysql> insert into person(id,name,sex,birth,address) 163 -> values(3,'zhangsan','man','1990-01-10','China');164Query OK, 1 row affected (0.04 sec)165mysql> select * from person;166+------+----------+------+------------+---------+167| id | name | sex | birth | address |168+------+----------+------+------------+---------+169| 1 | lisi | man | 1990-01-08 | China |170| 2 | wangwu | man | 1990-01-10 | China |171| 3 | zhangsan | man | 1990-01-10 | China |172+------+----------+------+------------+---------+1733 rows in set (0.00 sec)（13）删除表中的数据删除表中指定的数据：Mysql代码174mysql> delete from person where id=2;175Query OK, 1 row affected (0.02 sec)176mysql> select * from person;177+------+----------+------+------------+---------+178| id | name | sex | birth | address |179+------+----------+------+------------+---------+180| 1 | lisi | man | 1990-01-08 | China |181| 3 | zhangsan | man | 1990-01-10 | China |182+------+----------+------+------------+---------+1832 rows in set (0.00 sec)删除表中全部的数据：Mysql代码184mysql> delete from person;185Query OK, 2 rows affected (0.04 sec)186mysql> select * from person;187Empty set (0.00 sec)（14）重命名表查看重命名前的表名：Mysql代码188mysql> show tables;189+-------------------+190| Tables_in_test_db |191+-------------------+192| person |193+-------------------+1941 row in set (0.00 sec)重命名：Mysql代码195mysql> alter table person rename person_test;196Query OK, 0 rows affected (0.04 sec)197mysql> show tables;198+-------------------+199| Tables_in_test_db |200+-------------------+201| person_test |202+-------------------+2031 row in set (0.00 sec)（15）新增主键Mysql代码204mysql> alter table person_test add primary key(id);205Query OK, 0 rows affected (0.11 sec)206Records: 0 Duplicates: 0 Warnings: 0207mysql> desc person_test;208+---------+-------------+------+-----+---------+-------+ 209| Field | Type | Null | Key | Default | Extra | 210+---------+-------------+------+-----+---------+-------+ 211| id | int(11) | NO | PRI | 0 | | 212| name | varchar(20) | YES | | NULL | | 213| sex | char(8) | YES | | NULL | | 214| birth | date | YES | | NULL | | 215| address | varchar(50) | YES | | NULL | | 216+---------+-------------+------+-----+---------+-------+ 2175 rows in set (0.00 sec)删除主键：Mysql代码218mysql> alter table person_test drop primary key;219Query OK, 0 rows affected (0.18 sec)220221Records: 0 Duplicates: 0 Warnings: 0222mysql> desc person_test;223+---------+-------------+------+-----+---------+-------+ 224| Field | Type | Null | Key | Default | Extra |225+---------+-------------+------+-----+---------+-------+ 226| id | int(11) | NO | | 0 | |227| name | varchar(20) | YES | | NULL | | 228| sex | char(8) | YES | | NULL | | 229| birth | date | YES | | NULL | | 230| address | varchar(50) | YES | | NULL | | 231+---------+-------------+------+-----+---------+-------+ 2325 rows in set (0.01 sec)（16）删除表Mysql代码233mysql> drop table person_test;234Query OK, 0 rows affected (0.04 sec)235mysql> show tables;236Empty set (0.00 sec)（17）删除数据库Mysql代码237mysql> show databases;238+--------------------+239| Database |240+--------------------+241| information_schema |242| mysql |243| performance_schema |244| test |245| test_db |246+--------------------+2475 rows in set (0.00 sec)248mysql> drop database test_db;249Query OK, 0 rows affected (0.11 sec)250mysql> show databases;251+--------------------+252| Database |253+--------------------+254| information_schema |255| mysql |256| performance_schema |257| test |258+--------------------+2594 rows in set (0.00 sec)（18）查看建表语句Mysql代码260mysql> show create table table_name;补充说明：update er set password="root" where User="root";修改的不是密码，如果按照这个方式修改了，重新登录时将会报错：Mysql代码261mysql> update er set password="root" where User="root";262Query OK, 3 rows affected (0.00 sec)263Rows matched: 3 Changed: 3 Warnings: 0264mysql> exit265Bye266C:\Users\liqiong>mysql -uroot -p267Enter password: ****268ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y269ES)请按照以下方式重新修改密码，即可登录成功：Mysql代码270C:\Users\liqiong>mysql -uroot271Welcome to the MySQL monitor. Commands end with ; or \g.272Your MySQL connection id is 4273Server version: MySQL Community Server (GPL)274Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.275Oracle is a registered trademark of Oracle Corporation and/or its276affiliates. Other names may be trademarks of their respective277owners.278Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.279mysql> update er set password=password("root") where User="root";280Query OK, 3 rows affected (0.00 sec)281Rows matched: 3 Changed: 3 Warnings: 0282mysql> flush privileges;283Query OK, 0 rows affected (0.00 sec)284mysql> exit285Bye286C:\Users\liqiong>mysql -uroot -p287Enter password: ****288Welcome to the MySQL monitor. Commands end with ; or \g.289Your MySQL connection id is 5290Server version: MySQL Community Server (GPL)百度文库- 让每个人平等地提升自我11 291Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 292Oracle is a registered trademark of Oracle Corporation and/or its293affiliates. Other names may be trademarks of their respective294owners.295Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 296mysql>。