MySQL 5.7 common commands

Keywords: MySQL Database Java JDBC

1. Connect mysql

  1. Connect this machine

    mysql -u root -p

  2. Connecting to a remote host

    mysql -h 192.168.1.128 -u root -p

  3. Sign out

    exit;

2. Change password

Format: alter user user name @ host address identified by 'new password';

  1. Change the root password to newroot

    alter user root@local identified by 'newroot';

  2. View user host address method

    use mysql;
select user,host from user;

3. Manage users

You can manage the user table in the mysql database to manage users.

For user management, it is better to grant different permissions to users to manage users.

Add the tom user with the password of tom. You can log in to any host:

create user 'tom'@localhost identified by 'tom';
create user 'tom1'@'192.168.1.%' identified by 'tom1';

4. Management database

  1. show database

    show databases;

    Note: database scrambling

    1. Modify the / etc/my.cnf configuration file: character set server = utf8

    2. In the configuration file of Java connection mysql:

      jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8;

     

  2. Create database

    create database db_name;

     

  3. Delete database

    drop database if exists db_name;

  4. Use database

    mysql> use db_name;
Database changed

  5. Currently selected database

    select database();

    The select command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, etc. Some select commands are as follows:

    select version();               // display mysql Edition
select now();                   // Show current time
select current_date;            // Display date
select ((4 * 7) / 10 ) + 23;    // Calculation

5. Management table

  1. Show all tables

    show tables;

  2. View table structure

    (1) Mode 1:

    mysql> desc orders;
+------------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra         |
+------------+----------+------+-----+---------+----------------+
| order_num  | int(11)  | NO   | PRI | NULL   | auto_increment |
| order_date | datetime | NO   |     | NULL   |               |
| cust_id   | int(11)  | NO   | MUL | NULL   |               |
+------------+----------+------+-----+---------+----------------+
3 rows in set (0.24 sec)

    (2) Mode 2:

    mysql> show columns from orders;
+------------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra         |
+------------+----------+------+-----+---------+----------------+
| order_num  | int(11)  | NO   | PRI | NULL   | auto_increment |
| order_date | datetime | NO   |     | NULL   |               |
| cust_id   | int(11)  | NO   | MUL | NULL   |               |
+------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

    (3) Mode 3:

    mysql> show create table orders\G;
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20011 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
​
ERROR: 
No query specified

    (4) Mode 4:

    mysql> show full fields from orders;
+------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field     | Type     | Collation | Null | Key | Default | Extra         | Privileges                     | Comment |
+------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| order_num  | int(11)  | NULL     | NO   | PRI | NULL   | auto_increment | select,insert,update,references |         |
| order_date | datetime | NULL     | NO   |     | NULL   |               | select,insert,update,references |         |
| cust_id   | int(11)  | NULL     | NO   | MUL | NULL   |               | select,insert,update,references |         |
+------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.00 sec)

    (5) Mode 5:

    mysql> show fields from orders;
+------------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra         |
+------------+----------+------+-----+---------+----------------+
| order_num  | int(11)  | NO   | PRI | NULL   | auto_increment |
| order_date | datetime | NO   |     | NULL   |               |
| cust_id   | int(11)  | NO   | MUL | NULL   |               |
+------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

    (6) Method 6: view a field in the table

    mysql> desc orders order_num;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type   | Null | Key | Default | Extra         |
+-----------+---------+------+-----+---------+----------------+
| order_num | int(11) | NO   | PRI | NULL   | auto_increment |
+-----------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

    (7) Method 7: view the index in the table

    mysql> show index from orders\G;
*************************** 1. row ***************************
        Table: orders
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: order_num
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: fk_orders_customers
 Seq_in_index: 1
  Column_name: cust_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
​
ERROR: 
No query specified

  3. Create table: create a table named test

    mysql> create table test(
   -> id INT(11) NOT NULL AUTO_INCREMENT,
   -> name VARCHAR(255) NOT NULL,
   -> PRIMARY KEY (id)
   -> );
Query OK, 0 rows affected (0.44 sec)

  4. Delete table

    mysql> drop table if exists test;
Query OK, 0 rows affected (0.20 sec)

  5. Insert data into the table:

    Format: insert into table name (field name 1,..., field name n) values (value 1,..., value n);

    mysql> insert into test (name) values ("zhangsan");
Query OK, 1 row affected (0.14 sec)
​
mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.02 sec)

    Insert multiple records:

    mysql> insert into test (name) values ("lisi"),("xiaoming");
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | xiaoming |
+----+----------+
3 rows in set (0.00 sec)

  6. Query table data

    Format: select field 1,..., field n from table name where expression

    (1) Query table all:

    mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | xiaoming |
+----+----------+
3 rows in set (0.00 sec)

    (2) Query the first two lines:

    mysql> select * from test limit 2;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.03 sec)

  7. Delete table data

    Format: delete from table name where expression

    mysql> delete from test where id = 2;
Query OK, 1 row affected (0.13 sec)
​
mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | xiaoming |
+----+----------+
2 rows in set (0.00 sec)

  8. Modify table data

    Format: update [low & priority] TBL & NAME SET col & name1 = expr1 [, col & Name2 = expr2...] [where & definition] [order by...] [limit row & count]

    • UPDATE syntax can UPDATE each column in the original table row with the new value;

    • The SET clause indicates which columns to modify and which values to give;

    • The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated;

    • If the ORDER BY clause is specified, the rows are updated in the specified order;

    • The LIMIT clause is used to give a LIMIT on the number of rows that can be updated.

    mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | xiaoming |
|  4 | lisi     |
+----+----------+
3 rows in set (0.00 sec)
​
mysql> update test set name = "xiaohong" where id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> select * from test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | xiaohong |
|  4 | lisi     |
+----+----------+
3 rows in set (0.00 sec)

  9. Add fields

    Format: alter table tb "| add column" | name1 column "| definition [FIRST | after column" | name]; [FIRST | after column "| name] specifies the location relationship, FIRST represents the FIRST column, after column" | name represents the second column;

    mysql> alter table test add column(
   -> phone INT(11) NULL,
   -> addr VARCHAR(50)
   -> );
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra         |
+-------+--------------+------+-----+---------+----------------+
| id   | int(11)     | NO   | PRI | NULL   | auto_increment |
| name  | varchar(255) | NO   |     | NULL   |               |
| phone | int(11)     | YES  |     | NULL   |               |
| addr  | varchar(50)  | YES  |     | NULL   |               |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

  10. Delete column

    Format: alter table tb? Name drop [column] col? Name1 [, drop col? Name2...];

    • [COLUMN] keyword is optional;

    • When deleting multiple columns, you need to use the DROP keyword, which cannot be directly used or separated;

  11. Modify fields

    (1) Modify column

    ALTER TABLE tb_name
CHANGE [COLUMN] old_col_name new_col_name column_definition #Be sure to specify the type
[FIRST|AFTER col_name];

    (2) Modify column type

    ALTER TABLE tb_name
MODIFY col_name column_definition;

  12. Add constraint

    (1) Add primary key constraint

    Format: ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY index_type;

    • [CONSTRAINT [symbol]] constraint keyword. Symbol means constraint alias. Optional. mysql will create it automatically;

    • [index_type] the index type contains {B+TREE | HASH}. When the storage engine is InnoDB, only B+TREE can be used. The default value is B+TREE. However, InnoDB can have adaptive hash index, that is, the index in the index;

    (2) Add unique constraint

    Format: ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);

    • [INDEX|KEY] indicates whether it is INDEX or KEY. For the difference between INDEX and KEY, please refer to: Database operation -- the difference between key and index

    • [index [name] index name seems to be the same as [CONSTRAINT [symbol]];

    • [index| type] index type, containing {BTREE | HASH}

    (3) Delete constraint

    Format: alter table tb? Name DROP key index? Name;

  13. Add index

    (1) Picasso citation

    Format:

    #General index
ALTER TABLE tb_name
ADD {INDEX|KEY} [index_name](key_part,...) [index_option] ...
​
#Full-text index
ALTER TABLE tbl_name
ADD FULLTEXT [INDEX|KEY] [index_name](key_part,...) [index_option] ...
​
#Spatial index
ALTER TABLE tbl_name
ADD SPATIAL [INDEX|KEY] [index_name](key_part,...) [index_option] ...
​
key_part:
   col_name [(length)] [ASC|DESC]
​
index_type:
    USING {BTREE|HASH}
​
index_option:
   KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

Posted by BlueSkyIS on Sun, 01 Mar 2020 05:05:06 -0800

Hot Keywords

©2024 Programmer Group