MySQL操作实例

本文介绍MySQL的简单操作。

连接数据库

安装 mysql 客户端:

1
sudo apt install mysql-client-core-5.7

命令行:

1
2
3
4
5
mysql -uroot -p123456
mysql -h latelee.org -P 3305 -ulatelee -p1qaz@WSX
mysql -h latelee.org -P 3305 -uroot -p1qaz@WSX**>>

mysql -h 127.0.0.1 -P 3306 -u root -p123456

注:不同用户连接,看到的数据库亦不同。

如成功会提示:

1
2
3
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql>后即可输入sql语句。sql语句使用分号“;”作为结束符号。

退出mysql命令行:

1
exit

数据库、表

查看数据库:

1
2
3
4
5
6
7
8
9
10
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

创建数据库,名称为mydb:

1
mysql> CREATE DATABASE mydb;

选择mydb数据库:

1
mysql> USE mydb;

创建数据表user:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// id值为自增
mysql> CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看user数据表字段内容:

1
2
3
4
5
6
7
8
9
10
11
mysql> DESC user;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| email | varchar(255) | YES | | NULL | |
| first_name | varchar(255) | YES | | NULL | |
| last_name | varchar(255) | YES | | NULL | |
| username | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

往user表插入数据:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> INSERT INTO `user` (`id`, `email`, `first_name`, `last_name`, `username`)
VALUES(0,'li@latelee.org','Late','Lee','latelee');

// 自增情况下,不输入ID
mysql> INSERT INTO `user` (`email`, `first_name`, `last_name`, `username`)
VALUES('li@latelee.org','Late','Lee','foobar');
// 注:网上有说法id默认自增为2,但测试未发现

TODO:
有针对地插入数据

默认所有字段

查看已经插入了的数据:

1
2
3
4
5
6
7
mysql>  SELECT * FROM user;
+----+----------------+------------+-----------+----------+
| id | email | first_name | last_name | username |
+----+----------------+------------+-----------+----------+
| 0 | li@latelee.org | Late | Lee | latelee |
+----+----------------+------------+-----------+----------+
1 row in set (0.00 sec)

删除user表所有数据:

1
mysql> DELETE FROM user;

删除user数据表:

1
mysql> DROP TABLE user;

删除数据库mydb:

1
mysql> DROP DATABASE mydb;

更新数据:

1
2
3
mysql>  UPDATE user SET email='you@163.com' WHERE username='foobar';

mysql> UPDATE user SET email='you11@163.com', first_name='Kent' WHERE username='foobar';

查询倒数10条记录:

1
2
SELECT top 10 * FROM [dbo].[RealTimeData] ORDER BY i desc;  // sqlserver
SELECT * FROM RealTimeData ORDER BY id desc limit 10; // mysql

其它

字段为中文:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> CREATE TABLE `userc` (
`信息id` bigint(20) NOT NULL AUTO_INCREMENT,
`邮箱地址` varchar(255) DEFAULT NULL,
`名` varchar(255) DEFAULT NULL,
`姓` varchar(255) DEFAULT NULL,
`姓名` varchar(255) DEFAULT NULL,
PRIMARY KEY (`信息id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> desc userc;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| 信息id | bigint(20) | NO | PRI | NULL | auto_increment |
| 邮箱地址 | varchar(255) | YES | | NULL | |
| 名 | varchar(255) | YES | | NULL | |
| 姓 | varchar(255) | YES | | NULL | |
| 姓名 | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

create database cameradb;
use cameradb;

CREATE TABLE `devinfo` (
`devid` varchar(16) NOT NULL,
`version` varchar(255) DEFAULT NULL,
PRIMARY KEY (`devid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DESC devinfo;

SELECT * FROM devinfo;

INSERT INTO `devinfo` (`devid`, `version`)
VALUES('sn_test001','v1.0');

INSERT INTO `devinfo` (`devid`, `version`)
VALUES('sn_test002','v1.0');

INSERT INTO `devinfo` (`devid`, `version`)
VALUES('sn_test100','v1.0');

INSERT INTO `devinfo` (`devid`, `version`)
VALUES('sn_test200','v1.0');

添加字段
末尾:
ALTER TABLE devinfo ADD ip varchar(16);
开头:
ALTER TABLE devinfo ADD netmask varchar(16) first;
指定字段后:
ALTER TABLE devinfo ADD gateway varchar(16) after devid;

删除字段
ALTER TABLE devinfo drop COLUMN ip;