mysql基础命令行

Mysql Basic

1.Connect to mysql

--initialization
mysql -uroot -p123456 
UPDATE user SET authentication_string="" WHERE user=“root”;--set the password when version surpass 8.0
/*多行注释*/

• every statement should be followed by a semicolon
• if you aren't logging in your host

  $> mysql -h host -u user -p

host and user represent the host name where your MySQL server is running and the user name of your MySQL account.

• if you are logging in your host

  $> mysql -u user -p

*remember to enter the password when asked*

2.Basic Queries

• simple calculator

  mysql> SELECT SIN(PI()/4), (4+1)*5;
  /*output
    +------------------+---------+
    | SIN(PI()/4)      | (4+1)*5 |
    +------------------+---------+
    | 0.70710678118655 |      25 |
    +------------------+---------+
    1 row in set (0.02 sec)*/
  

• allow several statements in a row or a single statement be separated into different row
• If you decide you do not want to execute a query that you are in the process of entering, cancel it by typing \c:

  mysql> SELECT
    -> USER()
    -> \c
    mysql>
  

• other situation

  mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>'\c;
    -- However, you cannot just type \c in this case, because mysql interprets it as part of the string that it is collecting. Instead, enter the closing quote character (so mysql knows you've finished the string), then type \c:
  

3.create a database and a table

3.1 database

SHOW DATABASES;
USE test --if the database "test" exited and it must be given in a single line
mysql> CREATE DATABASE menagerie;--create a new database

• use a database while invoking sql

  $> mysql -h host -u user -p menagerie

3.2 table

mysql> SHOW TABLES;
Empty set (0.00 sec)

• create a table

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

• show the table in detail

    mysql> DESCRIBE pet;--describe the pet table

4.load data into a table

• load via a txt

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;--use tab as a terminator
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';--use \r or\n as a terminator

you could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).

• insert data

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

5.retriving data

SELECT sth FROM table WHERE condition

• selecting all data

SELECT * FROM table--except invisible columns

• fix the data

DELETE FROM table
LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet--reload the txt
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';--correct the only erroneous record

• select a particular row or column

mysql> SELECT * FROM pet WHERE name = 'Bowser';
mysql> SELECT name,birth FROM pet;

• sort columns

mysql> SELECT name,birth FROM pet ODER BY birth;
mysql> SELECT name,birth FROM pet ODER BY birth DESC;--in a descending order
mysql> SELECT name,birth FROM pet ODER BY species,birth DESC;--mutiple sort