mysql 修改check_mysql的一些初步使用!mysqlcheck mysqladmin 建立删除修改表,库,等...

mysqladmin create testdb

创建testdb数据库

mysqladmin drop testdb

删除testdb数据库

查看当前服务器的状态信息

mysqladmin extended-status

重新载入授权表(类似reload 通常在权限操作之后)

mysqladmin filsh-privileges

给用户设置一个新密码为newbie

mysqladmin -u root password newbie

修改一个用户密码为test

mysqladmin -u root -pnewbie password test

mysqlcheck表维护和维修程序

当想检查某个数据的所有表,或者某个数据库内的某张表时

mysqlcheck -ptest mysql db

mysql.db                                           OK

#####################################################

当想检查多个数据库的时候

mysqlcheck --databases -ptest mysql test

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.proxies_priv                                 OK

mysql.servers                                      OK

mysql.slow_log                                     OK

mysql.tables_priv                                  OK

mysql.time_zone                                    OK

mysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OK

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

#####################################################

当想一次性检查所有数据库的时候

mysqlcheck --all-database -ptest

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

#####################################################

mysqlcheck 修复数据库

mysqlcheck --all-database -ptest

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

#####################################################

mysql检查一个数据库如果发现有错误则自动修复它

mysqlcheck --auto-repair mysql -ptest

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.proxies_priv                                 OK

mysql.servers                                      OK

mysql.slow_log                                     OK

mysql.tables_priv                                  OK

mysql.time_zone                                    OK

mysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OK

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysqldump

mysqldump用来作为数据库备份的程序,备份出来可以跨平台,跨文件系统,跨版本

mysqldump用来备份,mysqlimport用来导入

mysqldump -ptest  hahah > tst.sql,备份,hahah数据库导入到tstsql

mysqlimport -uroot -p --force db_name backup1.sql

# mysqlimport db_name backup1.sql

SQL语句

查看数据库列表

show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| hahah              |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

删除一个数据库

mysql> drop database hahah;

Query OK, 0 rows affected (0.00 sec)

建立一个数据库,并且进入使用它

mysql> create database haha;

Query OK, 1 row affected (0.00 sec)

mysql> use haha;

Database changed

mysql>

mysql建立一个表

mysql> create table reserve (

-> id int not null primary key,

-> bookname text,

-> writer text,

-> bookdate date not null,

-> price float,

-> amount int

-> );

第一行是比较简单的;它说明我们想要建立一个新的名为 reserve 的数据表。

第二行说明我们需要一个数据列叫 ID,这个列的类型应该是一个整数(INT)。这一行

还定义了这个数据列的其他一些信息。首先,由于这一列中的数据是书的编号,每一本书都

应该有一个编号,因此,这一行不允许为空(NOT NULL)。然后,由于编号是每一本书的

唯一的标识符,并且书的编号是不应该重复的,所以这个数据列中的所有值都应该是不重复

的(PRIMARY

KEY)。

第三行很简单;这说明我们需要一个数据列叫 bookname,这个列的类型应该是一个文

本(TEXT),它保存的是书名。

第四行和第三行没有什么不同。它保存书的作者名字。

第五行定义了列名是 date,这个列的类型是日期型(DATE)这个列也不能为空(NOT

NULL)。

第六行和第七行分别定义了 price 列和 amount 列,分别是价格和数量。它们的类型

分别是 FLOAT 和 INT。小心!作为定义的最后一列,amount int 后面可没有跟“;”。

show tables

-> ;

+----------------+

| Tables_in_haha |

+----------------+

| reserve        |

+----------------+

1 row in set (0.00 sec)

desc查看reseve表的结构

mysql> desc reserve;

+----------+---------+------+-----+---------+-------+

| Field    | Type    | Null | Key | Default | Extra |

+----------+---------+------+-----+---------+-------+

| id       | int(11) | NO   | PRI | NULL    |       |

| bookname | text    | YES  |     | NULL    |       |

| writer   | text    | YES  |     | NULL    |       |

| bookdate | date    | NO   |     | NULL    |       |

| price    | float   | YES  |     | NULL    |       |

| amount   | int(11) | YES  |     | NULL    |       |

+----------+---------+------+-----+---------+-------+

向数据库中插入一个表

insert into reserve set

-> id = 1,

-> bookname = "test ",

-> writer = "zhoutao",

-> bookdate = "2011-1-1",

-> price = 75,

-> amount = 50;

查询数据库中表的存储数据select

mysql> select * from reserve;

+----+----------+---------+------------+-------+--------+

| id | bookname | writer  | bookdate   | price | amount |

+----+----------+---------+------------+-------+--------+

|  1 | test     | zhoutao | 2011-01-01 |    75 |     50 |

+----+----------+---------+------------+-------+--------+

1 row in set (0.00 sec)

select count(*)表示表中存储了多少条记录

select count(*) from reserve;

+----------+

| count(*) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

还可以使用where语句对使用的结构进行限制比如

select count(*) from reserve

-> where bookdate >="2011-1-1"

-> ;

+----------+

| count(*) |

+----------+

|        1 |

+----------+

select count(*) from reserve

-> where bookname like "%es%" #可以使用表达式"%es%" es可以理解为过滤条件

-> ;

+----------+

| count(*) |

+----------+

|        1 |

+----------+

上面两条命令可以组合使用

mysql> select * from reserve where bookname like "%es%" and bookdate >= "2011-1-1" ;

+----+----------+---------+------------+-------+--------+

| id | bookname | writer  | bookdate   | price | amount |

+----+----------+---------+------------+-------+--------+

|  1 | test     | zhoutao | 2011-01-01 |    75 |     50 |

+----+----------+---------+------------+-------+--------+

1 row in set (0.00 sec)

修改表中数据!

update reserve set bookname="testhaha" where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from reserve;

+----+----------+---------+------------+-------+--------+

| id | bookname | writer  | bookdate   | price | amount |

+----+----------+---------+------------+-------+--------+

|  1 | testhaha | zhoutao | 2011-01-01 |    75 |     50 |

+----+----------+---------+------------+-------+--------+

delete from reserve where bookname like "%haha%";

Query OK, 1 row affected (0.00 sec)

mysql> select * from reserve;

Empty set (0.00 sec)

注:where语句可选,如果不用,你要清楚你在干什么,

比如

:delete from reserve;

Query OK, 0 rows affected (0.00 sec)

清空了整个表

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值