听说Mysql你很豪横？-------------管理MySQL数据库基本操作命令

show databases         '查看数据库列表信息的命令是'
mysql> use mysql             ' 查看数据库中的数据表信息命令是'
mysql> show tables;
mysql> describe user;         '显示数据表的结构（字段）命令'

mysql> create database dog;   '创建一个名为dog的数据库'
mysql> use dog;                '进入dog的数据库'
mysql> create table gousheng(id char(3) not null primary key,
name varchar(10) not null,score int(3),address varchar(50) default 'baomi');
Query OK, 0 rows affected (0.01 sec)
'创建一个名为gousheng的表  ，第一个字段名为id  限制为3位数只内0——990 并且不能为空，而且具有唯一性，不能重复，第二个字段是name名字  10个字符以内的，'
'不能为空，第三个字段是成绩，三位数之内，可以为空，第四个字段为家庭地址，50个字符以内的，如果不填写的话，默认是baomi ' 

mysql> drop table kkk.bbb;	'//删除表高危命令谨慎操作'
Query OK, 0 rows affected (0.00 sec)

mysql> drop database kkk;	'//删除库高危命令谨慎操作'
Query OK, 0 rows affected (0.00 sec)

mysql> insert into gousheng(id,name,score,address)values(1,'goupeng',90,'guizhou');
Query OK, 1 row affected (0.00 sec)

mysql> select * from gousheng;
+----+---------+-------+---------+
| id | name    | score | address |
+----+---------+-------+---------+
| 1  | goupeng |    90 | guizhou |
+----+---------+-------+---------+
mysql> insert into gousheng values(2,'gousheng',56,'nanjing');
Query OK, 1 row affected (0.00 sec)
mysql> select * from gousheng;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
| 1  | goupeng  |    90 | guizhou |
| 2  | gousheng |    56 | nanjing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

mysql> update gousheng set name='shang' where id=1;    '将id为1的名字改为shang'
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from gousheng
    -> ;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
| 1  | shang    |    90 | guizhou |
| 2  | gousheng |    56 | nanjing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> update gousheng set name='zhen';     '将所有的名字都改成zhen'
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from gousheng;   
+----+------+-------+---------+
| id | name | score | address |
+----+------+-------+---------+
| 1  | zhen |    90 | guizhou |
| 2  | zhen |    56 | nanjing |
+----+------+-------+---------+
2 rows in set (0.00 sec)

mysql> delete from gousheng where id=1;   '删除id为1的那一列'
Query OK, 1 row affected (0.01 sec)

mysql> select * from gousheng;
+----+------+-------+---------+
| id | name | score | address |
+----+------+-------+---------+
| 2  | zhen |    56 | nanjing |
+----+------+-------+---------+
1 row in set (0.00 sec)

mysql> delete from gousheng;      '删表内所有数据，与drop不同的是  他只是删除数据，表的架构还有 ' 

alter add命令格式：alter table 表名 add字段 类型 其他;

例如，在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0：
   mysql> alter table MyClass add passtest int(4) default '0';

1) 加索引
   mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

例子： mysql> alter table employee add index emp_name (name);

2) 加主关键字的索引
    mysql> alter table 表名 add primary key (字段名);

例子： mysql> alter table employee add primary key(id);

3) 加唯一限制条件的索引
   mysql> alter table 表名 add unique 索引名 (字段名);

例子： mysql> alter table employee add unique emp_name2(cardnumber);

4) 删除某个索引
   mysql> alter table 表名 drop index 索引名;

例子： mysql>alter table employee drop index emp_name;

5) 增加字段
    mysql> ALTER TABLE table_name ADD field_name field_type;

6) 修改原字段名称及类型
    mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

7) 删除字段
    MySQL ALTER TABLE table_name DROP field_name;

 mysql> select name from gousheng;   '过滤出name这一列的数据'
+----------+
| name     |
+----------+
| shang    |
| zhen     |
| gousheng |
+----------+
3 rows in set (0.00 sec)
mysql> select name,score from gousheng;   '过滤出姓名和成绩两列成绩'
+----------+-------+
| name     | score |
+----------+-------+
| shang    |    54 |
| zhen     |    56 |
| gousheng |    66 |
+----------+-------+
3 rows in set (0.00 sec)
mysql> select name,address from gousheng where id=1 or id=3;   '过滤出id为1和id为3的名字和地址，'
+----------+---------+
| name     | address |
+----------+---------+
| shang    | dongbei |
| gousheng | dongbei |
+----------+---------+
2 rows in set (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
'grant all  放通所有权限'
'//all privileges：所有权限'
'%：所有终端 '
'  identified 鉴定；辨认'
'基本格式  to接允许登录用户和终端，by接密码，with  接允许的选项'
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> revoke all privileges on *.* from 'root'@'%';
Query OK, 0 rows affected (0.00 sec)