MySql入门（1）

mysql安装

sudo apt-get install mysql-serversudo apt-get isntall mysql-clientsudo apt-get install libmysqlclient-dev

期间会让你设置root的密码，记住就行。

mysql使用

连接数据库

格式：

$mysql -h hostname -u username -ppassword databasename

例子：

$ mysql -h localhost -u root -p //本地的hostname为localhost，-h localhost可以不写。Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 20Server version: 5.6.31-0ubuntu0.15.10.1 (Ubuntu)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

创建数据库并赋予jindg有完全访问权

用create命令创建一个testdb数据库，grant命令设置权限：
create命令格式：

create database name; //创建数据库create table name; //创建表

grant命令格式如下，具体参考：mysql 赋给用户权限 grant all privileges on

mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;

例子：

mysql> create database testdb;Query OK, 1 row affected (0.00 sec)mysql> grant all on testdb.* to jindg@localhost identified by '123';Query OK, 0 rows affected (0.00 sec)

使用自己的数据库

$ mysql -h localhost -u jindg -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.6.31-0ubuntu0.15.10.1 (Ubuntu)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || testdb             |+--------------------+2 rows in set (0.02 sec)mysql> use testdbDatabase changed

通过use选择自己的数据库。

创建一个表

mysql> create table stb_info(ChipNo int, ChipId bigint, ChipInfo varchar(176));Query OK, 0 rows affected (0.24 sec)mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| stb_info         |+------------------+1 row in set (0.00 sec)

查看表结构

mysql> describe stb_info;+----------+--------------+------+-----+---------+-------+| Field    | Type         | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| ChipNo   | int(11)      | YES  |     | NULL    |       || ChipId   | bigint(20)   | YES  |     | NULL    |       || ChipInfo | varchar(176) | YES  |     | NULL    |       |+----------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)

修改表名称

mysql> rename table stb_info to chip_data    -> ;Query OK, 0 rows affected (0.10 sec)mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| chip_data        |+------------------+1 row in set (0.00 sec)

用insert向表中添加数据

mysql> insert into chip_data values(1, 2130641167, '0001005401000542781248150200106E532500EE25DDE97EFF010F0D2187900300160100010202037B289B06958A7BE1EA310041D57FB9E3030016020002020203C1D12BD6E817DCABAC66B9C');Query OK, 1 row affected (0.06 sec)

用 load data 从文件中插入数据

这个命令可以很高效率的将文件导入mysql中来，但是一开始用load data会报如下错误，看上去像是权限不够。百度之，参考：【MySQL实践经验】LOAD DATA INFILE 报错 ERROR 1148 (42000) 或 ERROR 1045 (28000)的解决办法 和 .my.cnf。

mysql> load data infile '/home/jindg/abs/project/gx3011b/序列化数据/20151225/test.dat.pers' into table chip_data fields terminated by ' ' (`ChipId`,`ChipInfo`);ERROR 1045 (28000): Access denied for user 'jindg'@'localhost' (using password: YES)

我这里讲两个我尝试过的方法：
方法1：

$ mysql --local-infile -h localhost -u jindg -p //加入--local-infile参数mysql> load data local infile '/home/jindg/abs/project/gx3011b/序列化数据/20151225/ADDING_CWEKIC_SARFT_P1_NTC3012_8633E146_863B8260_NDSCA_20151210_124532.dat.pers' into table chip_data fields terminated by ' ' (`ChipId`,`ChipInfo`); //加入local参数

方法2：不用手动增加参数：–local-infile，那么需要在～目录新建.my.cnf，编辑它，输入:

[mysql]local-infile

然后重启mysql服务：

jindg@nc:~$ sudo service mysql restart

进入数据库：

$ mysql  -h localhost -u jindg -p //不再需要加入--local-infile参数mysql> load data local infile '/home/jindg/abs/project/gx3011b/序列化数据/20151225/test.dat.pers' into table chip_data fields terminated by ' ' (`ChipId`,`ChipInfo`); //依然得加入local参数

相比而言方法2更方便些。

加入数据让表的ID自动增加

前面从文件中插入数据，我并没有插入ChipNo，当你查看表内容的时候，你会发现ChipNo实际是NULL，本来我的想法是每加入一条数据，ChipNo能自动加1，这里需要用到alter命令，具体使用参考mysql中的alter语句的经典用法 。

mysql> describe chip_data    -> ;+----------+--------------+------+-----+---------+-------+| Field    | Type         | Null | Key | Default | Extra |+----------+--------------+------+-----+---------+-------+| ChipNo   | int(11)      | NO   | PRI | 0       |       || ChipId   | bigint(20)   | YES  |     | NULL    |       || ChipInfo | varchar(176) | YES  |     | NULL    |       |+----------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table chip_data modify ChipNo int auto_increment;Query OK, 999993 rows affected (28.17 sec)Records: 999993  Duplicates: 0  Warnings: 0mysql> describe chip_data;+----------+--------------+------+-----+---------+----------------+| Field    | Type         | Null | Key | Default | Extra          |+----------+--------------+------+-----+---------+----------------+| ChipNo   | int(11)      | NO   | PRI | NULL    | auto_increment || ChipId   | bigint(20)   | YES  |     | NULL    |                || ChipInfo | varchar(176) | YES  |     | NULL    |                |+----------+--------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

从表中检索数据

通过select命令来操作数据库，具体参考：MySQL中select语句详解

// 通过ChipNO来检索mysql> select * from chip_data where ChipNO=1;+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| ChipNo | ChipId     | ChipInfo                                                                                                                                                  |+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+|      1 | 2130641167 | 0001005401000542781248150200106E532500EE25DDE97EFF010F0D2187900300160100010202037B289B06958A7BE1EA310041D57FB9E3030016020002020203C1D12BD6E817DCABAC66B9C |+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)// 通过ChipId来检索mysql> select * from chip_data where ChipId=2130641167; +--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| ChipNo | ChipId     | ChipInfo                                                                                                                                                  |+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+|      1 | 2130641167 | 0001005401000542781248150200106E532500EE25DDE97EFF010F0D2187900300160100010202037B289B06958A7BE1EA310041D57FB9E3030016020002020203C1D12BD6E817DCABAC66B9C |+--------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

删除表

用delete命令删除，格式如下：

delete from table; //删除整张表delete form table where table_index=key; //删除table_index为key的数据

mysql> delete from chip_data;Query OK, 999993 rows affected (15.80 sec)mysql> select * from chip_data    -> ;Empty set (14.20 sec)mysql> select count(*) from chip_data    -> ;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.98 sec)mysql> select * from chip_data where ChipNo<10;//删除ChipNo小于10的数据Empty set (0.00 sec)