Learn notes MySQL common basic operations

Set a password for the MySQL database root account
[Email protected] ~]# mysqladmin-u root password ' 123456 '

To connect to a database:
You can use the command: Mysql–u root–p to connect to the database, but only to connect to the local library, many times you need to connect to a host database in the network:
Note:-U account-p password-h specifies the IP of the remote host
Connect to a local library: [[email protected] ~]# mysql-u root-p
Enter Password:
Connect to a library in the network: [[email protected] ~]# mysql-uroot-p-h192.168.1.1-p3306
Enter Password:
-P: The binding port used to specify MySQL for the remote host, default is 3306

Query the current library:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 rows in Set (0.01 sec)

To query a table in a library:
To switch to a library first:
mysql> use MySQL
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
-A can ignore fields that read all tables when switching libraries
mysql> use mysql-a;
Database changed
List all tables:
Mysql> Show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| Columns_priv |
| db |
| Event |
| Func |
| General_log |
| Help_category |
| Help_keyword |
| help_relation |
| Help_topic |
| Host |
| Ndb_binlog_index |
| Plugin |
| Proc |
| Procs_priv |
| Servers |
| Slow_log |
| Tables_priv |
| Time_zone |
| Time_zone_leap_second |
| Time_zone_name |
| time_zone_transition |
| Time_zone_transition_type |
| user |
+---------------------------+
Rows in Set (0.00 sec)

To view all the fields of a table:
mysql> DESC DB;

\g: Displayed as a line:
mysql> desc db\g;
1. Row

See which user is currently
Mysql> Select User ();
+----------------+
| User () |
+----------------+
| [Email protected] |
+----------------+
1 row in Set (0.00 sec)

To view the commands for the current database:
Mysql> Select Database ();
+------------+
| Database () |
+------------+
| MySQL |
+------------+
1 row in Set (0.00 sec)

Create a new library:
mysql> CREATE DATABASE db1;
Query OK, 1 row Affected (0.00 sec)
View the new library you just created
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| DB1 |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

To view the version of the current database:
Mysql> select version ();
+-----------+
| Version () |
+-----------+
| 5.1.73 |
+-----------+
1 row in Set (0.00 sec)

To view the current status of MySQL:
Mysql> Show status;
+-----------------------------------+----------+
| variable_name | Value |
+-----------------------------------+----------+
| aborted_clients | 0 |
| aborted_connects | 3 |

To view MySQL parameters:
Mysql> Show variables;
To view the queue for the current MySQL server:
Mysql> show Processlist;
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+-------+------------------+
| 11 | Root | localhost | MySQL | Query | 0 | NULL | Show Processlist |
+----+------+-----------+-------+---------+------+-------+------------------+
1 row in Set (0.00 sec)

Create a new table:
mysql> use DB1; #切换到库里面先
Database changed
Mysql> CREATE TABLE test (ID int (), name char (40));
Query OK, 0 rows affected (0.03 sec)
To view the table you just created:
mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ID | Int (20) | YES | | NULL | |
| name | CHAR (40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in Set (0.00 sec)

Insert data:
mysql> INSERT INTO test values (1, ' Le ');
Query OK, 1 row Affected (0.00 sec)
To view the data in a table:
Mysql> select * from test;
+------+------+
| ID | name |
+------+------+
| 1 | Le |
+------+------+
1 row in Set (0.00 sec)

To empty data from a table:
mysql> TRUNCATE TABLE test;
Query OK, 0 rows Affected (0.00 sec)

To view the data in a table:
Mysql> select * from test;
Empty Set (0.00 sec)

Create a regular user and authorize
Mysql> Grant all on . to User2 identified by ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)
All: Indicates that all permissions are available
.: The former represents all libraries, the latter represents all tables
To authorize a user of a network host:
Mysql> Grant all on DB1. To ' user2 ' @ ' 192.168.159.136 ' identified by ' 1234567 ';
Query OK, 0 rows Affected (0.00 sec)

Query statement:
The first method:
Mysql> Select COUNT () from Mysql.user;
+----------+
| Count () |
+----------+
| 7 |
+----------+
1 row in Set (0.00 sec)
Mysql.user represents a table in the MySQL library, and COUNT (*) indicates how many rows are in the table

The second method:
Mysql> Select db from Mysql.db;
+---------+
| db |
+---------+
| Test |
| test_% |
| DB1 |
+---------+
3 Rows in Set (0.00 sec)

Younger brother is a beginner, the wrong place wants the big boys to point out!

Learn notes MySQL common basic operations