MySql入门（1）

mysql安装

sudo apt-get install mysql-server
sudo apt-get isntall mysql-client
sudo 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 20
Server 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 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>

创建数据库并赋予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 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server 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 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.02 sec)

mysql> use testdb
Database 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: 0

mysql> 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)