Mysql setup and basic commands

Mysql setup and basic commands

I. mysql setup process

Install mysql on ubuntu

1. sudo apt-get install mysql-server

2. sudo apt-get install mysql-client

3. sudo apt-get install libmysqlclient-dev

2. Basic commands-Logon, logout, addition of databases, addition of tables, deletion of databases, deletion of tables, display of databases, and display tables

1. log on and exit

Mysql-h host-u username-p password

Example:

Mysql-h127.0.0.1-uroot-p123456

2. Exit

Quit or exit <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KCjxwPjOhos/Uyr61scewyv2 + 3b/ipc9wpgo8cd5zag93icagzgf0ywjhc2vzwvcd4kpha + pgltzybzcm9 "http://www.2cto.com/uploadfile/Collfiles/20150112/2015011209474434.png" alt = "\">

4. Select a database:

Use database_name;

Example:

Use test:

5. list all tables of the current database

Show tables;

6. Create a database:

Create databasedatabase_name;

7. delete a database

Drop database database_name;

8. display the table data structure:

Describe table_name;

9. Create a table:

Use the create table statement to create a table:

The following information must be provided:

The name of the new table, which is given after create table:

Column names and definitions, separated by commas.

The actual table definition (all columns) is in parentheses, and each column is separated by a comma. This table consists of nine columns. Each column starts with a column name, followed by the column data type.

The primary key of a table can be specified with the primary key keyword when the table is created again. Here, cust_id is specified as the primary key column. The entire statement ends with a semicolon after the right parentheses.

When creating a table, you can specify whether the column is a NULL column or not null column. If the column is NULL, the value of this column is NOT provided during insertion, the not null column is the value of this column that must be provided when a record is inserted.

The primary key must be unique. If a single column is used, its values must be unique. If multiple columns are used, the combined values of these values must be unique.

When creating a primary key consisting of multiple columns, each column name should be given in a comma-separated list.

AUTO_INCREMENT: tells mysql to automatically increment each row added to this column. mysql automatically increments this column every time an INSERT operation is executed.

Default value, which is specified by the default keyword.

InnoDB is a reliable transaction processing engine;

MyISAM is a high-performance engine. As the default engine, MyISAM supports full text search, but does not support transaction processing.

III. Basic commands-select commands

1. Display basic information:

2. display the columns in the table:

Select column_name_list from table_name;

(1) query several columns:

Select name, sex, birth, birthaddr from mytable;

(2) query a column:

Select name from mytable;

(3) display and sort a column:

Select column_name from table_name order by column_name;

Example:

Select name from mytable order by name;

By default, desc is added at the end of the command;

Example: select name from mytable order by name desc;

(4) only show certain rows in a column:

Select column_name from table_name LIMIT line_total;

Display the total line_total rows starting from the first row for column_name:

Example:

Select name from mytable LIMIT 2;

A column is displayed. Some rows starting from a row are displayed, that is, the starting row to be displayed is set and the total number of rows to be displayed is set:

Select name from mytable LIMIT line_total OFFSET line_index;

That is, the line line_index + 1 is displayed, and the line line_total is displayed.

From the above, we can see that starting from 3rd rows.

3. query all data:

Select * from mytable;

4. display specific rows:

Select * from table_name where colomn_name = "name ";

Example:

Select * from mytable where name = "Tom ";

Display specific rows by combining conditions:

Select * from table_name where column_name1 = "value1" and column_name2 = "value2 ";

Example:

Select * from mytable where sex = "f" and birthaddr = "China ";

5. display the first or last one after sorting: Use order by and limit together:

For example: show the youngest person:

Select * from mytable order by birth desc limit 1;

6. Filter Based on the search conditions specified by the select and where statements:

Select column_name_list from table_name where column_name = "value ";

Example:

Find out the information named Tom:

Select * from mytable where name = "Tom ";

7. The select statement has a special where clause that can be used to check columns with NULL values. This where clause is the is null clause:

Select column_name1 from table_name where column_name2 is null;

Example:

Select birthaddr from mytable where name is null;

8. where clause:

Multiple where clauses are allowed. These clauses are performed in the "and" or "or" clause mode:

For example:

Select a record whose name and sex are both NULL:

Select * from mytable where name is null and sex is null;

When multiple and or operators are used, brackets must be used to differentiate the real logic.

Iv. LIKE Operator

1. percent (%) wildcard

% Indicates the number of times any character appears.

For example, pick out the person born in October 1987:

Select * from mytable where birth LIKE "1987-10% ';

2. Underline _ wildcard characters

Match a single character

3. Delete unnecessary spaces on the left:

RTrim ()

4. Delete unnecessary spaces on the right:

LTrim ()

5. Case sensitivity:

Example: select name, upper (name) as name_upper from mytable order by name;

Select name, lower (name) as name_lower from mytable order by name;

5. Summarize data

1. Aggregate functions

There are several examples of this type of search:

Determine the number of rows in the table;

Obtain the sum of the row groups in the table;

Find the maximum, minimum, or average values of the table column.

For example:

Select count (name) as total from mytable:

6. Text Search:

1. Full text search

Two functions match () and against () are used to perform a full text search. match () specifies the column to be searched, and against specifies the search method to be used:

VII. insert data

1. syntax structure:

Insert into table_name (table info structure) value (value_list );

For example:

Insert into mytable (name, sex, birth, birthaddr) value ("Max", "m", "1886-11-06", "German ");

2. You can insert multiple data entries in the same statement:

Insert into mytable (name, birth, sex, birthaddr) value ("Linda", "2001-09-16", "f", "Russia"), ("Green ", "1698-12-08", "m", "Italy ");

8. Update and delete data

1. Update Data

Update a specific row: use where as a limit

Update all rows: Do not use where

Update statement: contains three parts:

The table to be updated;

Column names and their new values;

Determine the filtering condition of the row to be updated;

Update table name set column name = "name" where filter Condition

Example:

Update mytable set name = "tt" where birthaddr = "America"

2. delete data

Use the delete statement:

Delete a specific row from a table: A restriction condition must be added, that is, the where statement;

Delete all rows from the table;

9. Change the table structure

Use the alter table statement to change the TABLE structure:

After alter table, you must follow the name of the TABLE to be changed and the list of tables to be changed;

1. Add a column:

Example:

Alter table mytable ADD height float;

2. delete a column:

Example:

Alter table mytable drop column height;

3. Change the table name:

Rename table old table name to new table name