MySQL操作实例

技术杂铺 967 字 次

本文介绍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
show variables like 'lower_case_table_names';

默认情况上表名区分大小写，将 lower_case_table_names 设为 1，不区分。

修改root密码：

1
set password for root@localhost = password('123');

数据库操作

数据库、表

查看数据库：

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
2
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
19
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=utf8mb4;

// 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=utf8mb4;

查看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=utf8mb4;

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
37
38
39

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=utf8mb4;

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;

修改表名：
alter table test rename testfoo;

积累的问题

mysql 新版本密码配置连接问题。
问题：使用 navicat 连接不了 mysql8.0，提示：2059 Authentication plugin 'caching_sha2_password' cannot be loaded。客户端版本较低，不支持。
解决： 

1
2
3
4
5
6
更改加密方式：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
更改密码：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
刷新权限：
FLUSH PRIVILEGES;