[大数据] How to using the mysql client program

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> How to using the mysql client program -> 正文阅读

[大数据]How to using the mysql client program

1.How to setting up a mysql user account.

Problem

Need an account for connecting to your MySQL server

Solution

Use CREATE USER and GRANT statements to set up the account.Then use the account name and password to make connection to the server.

[root@DBAMAXWELL ~]# mysql -h localhost -u root -p
Enter password:?
Welcome to the MySQL monitor. ?Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.7.37 MySQL Community Server (GPL)

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> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON cookbook.* to 'cbuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@DBAMAXWELL ~]#?

[root@DBAMAXWELL ~]# mysql -h localhost -u cbuser -p
Enter password: cbpass
Welcome to the MySQL monitor. ?Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.7.37 MySQL Community Server (GPL)

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 |
+--------------------+
1 row in set (0.00 sec)

mysql>?

?2. How to creating a Database and a sample table?

Problem

Create a database and set up tables within it

Solution

CREATE DATABASE
CREATE TABLE
INSERT

mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.00 sec)

mysql> use cookbook;
Database changed

mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);
Query OK, 1 row affected (0.01 sec)

mysql>?

2.How to executing sql statements interactively?

[root@DBAMAXWELL ~]# mysql -h localhost -u cbuser -p -e "select count(*) from limbs" cookbook
Enter password:?
+----------+
| count(*) |
+----------+
| ? ? ? 10 |
+----------+
[root@DBAMAXWELL ~]# mysql -h localhost -u cbuser -p -e "select count(*) from limbs;select now()" cookbook
Enter password:?
+----------+
| count(*) |
+----------+
| ? ? ? 10 |
+----------+
+---------------------+
| now() ? ? ? ? ? ? ? |
+---------------------+
| 2022-03-19 17:33:17 |
+---------------------+
[root@DBAMAXWELL ~]#?

?3. How to executing SQL Statements Read from? a File or Program

Problem?

mysql to read statements stored in a file so that you need not enter them manually.
mysql to read the output from another program.

Solution

To execute the statements in this SQL script file, change location into the tables directory of the recipes distribution and run this command.

total 0
[maxwell@DBAMAXWELL mysql_learn]$ vi limbs.sql
DROP TABLE IF EXISTS limbs;
CREATE TABLE limbs
(
? ? ? ? ?thing VARCHAR(20), # what the thing is
? ? ? ? ?legs INT, # number of legs it has
? ? ? ? ?arms INT # number of arms it has
); ? ? ?
INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

[maxwell@DBAMAXWELL mysql_learn]$ mysql -h localhost -u cbuser -p cookbook < limbs.sql
Enter password:?

The command just shown illustrates how to specify an input file for mysql on the command line.Alternatively,to read a file of SQL statements from within a mysql session.use a source filename command (\.filename)

mysql> source limbs.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)