20-minute basic MySQL entry, 20-minute mysql entry

20-minute basic MySQL entry, 20-minute mysql entry

Start to use

MySQL is a Relational Database Management System. A Relational Database consists of one or more tables:

Header ): Name of each column;
Column (row ): A set of data with the same data type;
Row (col ):Each row is used to describe the specific information of a person/object;
Value ):The specific information of the row. Each value must be of the same data type as the column;

Log on to MySQL

Mysql-h 127.0.0.1-u username-database name selected by pmysql-D-h hostname-u username-pmysql> exit # exit mysql> quit # exit

Create a database

Operations on tables must be performed first in the database.Use Database Name;

-- Create a database named samp_db and specify the database character encoding as gbkcreate database samp_db character set gbk; drop database samp_db; -- delete the database named "show databases"; -- display the database List. Use samp_db; -- select the created database samp_dbshow table name; -- display all the table names under samp_db describe table names; -- display the table structure delete from table name; -- clear table records

Create a database table

You can use the create table statement to create a table. The common form of create table is Syntax: create table Name (column Declaration );

Create table 'user _ accounts' ('id' int (100) unsigned not null AUTO_INCREMENT primary key, 'Password' varchar (32) not null default ''comment 'user password ', 'reset _ password' tinyint (32) not null default 0 COMMENT 'user type: 0-password reset NOT required; 1-password reset required ', 'mobile' varchar (20) not null default ''comment 'mobile', 'create _ at' timestamp (6) not null default CURRENT_TIMESTAMP (6), 'Update _ at' timestamp (6) not null default CURRENT_TIMESTAMP (6) on update CURRENT_TIMESTAMP (6), -- creates a unique index. Duplicate unique index idx_user_mobile ('mobile') is NOT allowed ')) ENGINE = InnoDB default charset = utf8COMMENT = 'user table information ';

Description of data type attributes

NULL:A data column can contain NULL values;
Not null:Data Columns cannot contain NULL values;
DEFAULT:Default value;
PRIMARY:KEY primary KEY;
AUTO_INCREMENT:Auto increment, applicable to integer type;
UNSIGNED:It indicates that the value type can only be positive;
Character set name:Specifies a character set;
COMMENT:Table or field description;

Add, delete, modify, and query

SELECT

SELECT statements are used to SELECT data from a table.
Syntax: SELECT column name FROM Table Name
Syntax: SELECT * FROM Table Name

-- Table abc two id table abc does not contain the query of field a = B, only show the id

SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);

-- Select the data in the LastName column from the table Persons

SELECT LastName FROM Persons

-- Duplicate data is automatically removed from the result set.

SELECT DISTINCT Company FROM Orders 

UPDATE

The Update statement is used to modify data in a table.
Syntax: UPDATE table name SET column name = new value WHERE column name = A Value

-- The update statement sets the field value to the field obtained from another result.

Update user set name = (select name from user1 where user1. id = 1) where id = (select id from user2 where user2. name = 'susu ')

INSERT

The insert into statement is used to INSERT new rows INTO the table.
Syntax: insert into table name VALUES (value 1, value 2 ,....)
Syntax: insert into Table Name (column 1, column 2,...) VALUES (value 1, value 2 ,....)

-- Insert a field LastName = Wilson field Address = shanghai to the table Persons.

INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');INSERT INTO meeting SET a=1,b=2;

DELETE

The DELETE statement is used to DELETE rows in a table.
Syntax: delete from table name WHERE column name = Value

-- Delete all rows without deleting the table_name table and clear the table. Delete from table_name -- or DELETE * FROM table_name -- DELETE the Person table field LastName = 'wilson 'delete from Person WHERE LastName = 'wilson' -- DELETE two data tables with the meeting id of 2 and 3. DELETE from meeting where id in (2, 3 ); -- The Id_P field of the Persons table is equal to the Id_P value of the Orders field. -- The result set displays the LastName and FirstName fields of the Persons table and the SELECT p field of the OrderNo field of the Orders table. lastName, p. firstName, o. orderNo FROM Persons p, Orders o WHERE p. id_P = o. id_P

WHERE

The WHERE clause specifies the criteria for selection.
Syntax: SELECT column name FROM table name WHERE column operator Value

-- Select data with the Year field greater than 1965 from the Persons Table

SELECT * FROM Persons WHERE Year>1965

AND OR

AND-if both the first AND second conditions are true, OR-if either of the first AND second conditions is true;

AND

-- DELETE data in the meeting table field -- id = 2 and user_id = 5 and data in the -- id = 3 and user_id = 6 DELETE from meeting where id in (2, 3) and user_id in (5, 6); -- use AND to display all people with the last name "Carter" and the name "Thomas: SELECT * FROM Persons WHERE FirstName = 'Thomas 'AND LastName = 'cart ';

OR

-- Use OR to show all "Carter" OR "Thomas": SELECT * FROM Persons WHERE firstname = 'Thomas 'OR lastname = 'cart'

ORDER

The statement sorts records in ascending order by default.
The order by-statement is used to sort the result set based on the specified column.
DESC-sort records in descending order.
ASC-sort records in sequence.

-- Company is a letter in the table Orders, and the Company name SELECT Company and OrderNumber FROM Orders order by Company are displayed in alphabetical ORDER -- SELECT Company is displayed in descending ORDER when the Company is followed by desc, orderNumber FROM Orders order by Company DESC -- Company displays Company names in descending ORDER, and OrderNumber displays SELECT Company, OrderNumber FROM Orders order by Company DESC, OrderNumber ASC

IN
The IN-operator allows us to specify multiple values IN the WHERE clause.
The IN-operator is used to specify the range. Each entry IN the range matches. The IN value rule, separated by commas (,). All values are placed IN brackets.
Syntax: SELECT "field name" FROM "table name" WHERE "field name" IN ('value 1', 'value 2 ',...);

-- Select the LastName field from the Persons table to be Adams or Carter.

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

NOT
NOT-operators are always used with other operators and used before filtering.

SELECT vend_id, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

AS
As-can be understood as: Used as, as, as; alias
It is generally used to rename the column name or table name.
Syntax: select column_1 as column 1, column_2 as column 2 from table as table

SELECT * FROM Employee AS emp -- this statement is used to search for the data in all the Employee tables and name the Employee table emp. -- After you name a table, you can replace Employee with emp below. -- for example, SELECT * FROM emp. select max (OrderPrice) AS LargestOrderPrice FROM Orders -- list the maximum value of the OrderPrice column of the table Orders field, -- the result set column does not show the OrderPrice display LargestOrderPrice -- display the name column SELECT t in the table users_profile. name from (SELECT * from users_profile a) AS t; -- table user_accounts name alias ua, table users_profile name alias up -- meet condition table user_accounts field id equals table users_profile field user_id -- result set only displays SELECT ua for the mobile and name columns. mobile, up. name FROM user_accounts as ua inner join users_profile as up ON ua. id = up. user_id;

JOIN

Queries data from two or more tables based on the relationship between columns.
JOIN: If the table has at least one match, the row is returned.
Inner join: if at least one match exists in the table, the inner join keyword returns the row.
Left join: returns all rows from the LEFT table even if no match exists in the right table.
Right join: returns all rows from the RIGHT table even if no match exists in the left table.
Full join: if one of the tables matches, the row is returned.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName;

SQL Functions

COUNT
COUNT allows us to COUNT how many pieces of data are selected in the table.
Syntax:Select count ("field name") FROM "table name ";

-- There are several store_name columns in the Store_Information table that are not blank. -- "Is not null" means "This column is not blank. Select count (Store_Name) FROM Store_Information WHERE Store_Name is not null; -- obtain the total number of Persons tables select count (1) AS totals FROM Persons; -- obtain the total number of select user_id, count (*) as totals from station group by user_id in the same table station field;

MAX
The MAX function returns the maximum value in a column. NULL values are not included in calculation.
Syntax: select max ("field name") FROM "table name"

-- List the maximum value of the OrderPrice column in the table Orders field,
-- The result set column does not show the OrderPrice display LargestOrderPrice

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

Add Index

General INDEX)
Syntax: alter table name add index name (field name)

-- Create index index_user ON user (title) -- add index alter table table_name add index index_name ON (column (length) by modifying the TABLE structure )) -- ADD a common INDEX (INDEX) alter table 'table' add index index_name (name) to the name field of the user table) -- create table 'table' ('id' int (11) not null AUTO_INCREMENT, 'title' char (255) character set utf8 COLLATE utf8_general_ci not null, 'content' text character set utf8 COLLATE utf8_general_ci NULL, 'time' int (10) null default null, primary key ('id '), INDEX index_name (title (length) -- delete index drop index index_name ON table

PRIMARY key Index)
Syntax: alter table name add primary key (field name)

-- ADD the PRIMARY key index (PRIMARY key) alter table 'user' add primary key (id) to the id field of the user TABLE );

UNIQUE Index)
Syntax: alter table name add unique (field name)

-- ADD a UNIQUE index (UNIQUE) alter table 'user' add unique (creattime) to the creattime field in the user TABLE );

Full text index (FULLTEXT)
Syntax: alter table name add fulltext (field name)

-- ADD full-text index (FULLTEXT) alter table 'user' add fulltext (description) to the description field of the user TABLE );

Add multi-column Index
Syntax: alter table table_name add index index_name (column1, column2, column3)

-- ADD the name, city, and age fields in the user TABLE to the normal INDEX (INDEX) alter table user add index name_city_age (name (10), city, age );

Index creation time
You need to create an index for the columns that appear in the WHERE and JOIN operations, but this is not exactly the case:

• MySQL only uses indexes for <, <=, =,>,> =, BETWEEN, and IN.
• In some cases, LIKE will also use indexes.
• MySQL does not use indexes when LIKE starts with "%" and.
  

-- In this case, you need to create an index for the city and age. -- because the userame of the mytable table also appears in the JOIN clause, it is also necessary to create an index for it. SELECT t. name FROM mytable t left join mytable m ON t. name = m. username WHERE m. age = 20 AND m. city = 'shanghai'; SELECT * FROM mytable WHERE username like 'admin % '; -- the following statement does not use: SELECT * FROM mytable WHEREt Name like' % admin'; -- therefore, pay attention to the above differences when using LIKE.

Notes for Indexing

• The index does not contain columns with NULL values.
• Use short Indexes
• Do not perform Index Calculation on columns.
  

Table modification after creation

Add Column
Syntax: alter table name add column Name Data Type [after Insert Location];
Example:

-- Append the address: alter table students add address char (60) to the end of the students table; -- Insert the column birthday: alter table students add birthday date after age after the column named age;

Modify columns
Syntax: alter table name change column name column new name new data type;

-- Rename the table tel column to telphone: alter table students change tel telphone char (13) default "-"; -- change the data type of the name column to char (16 ): alter table students change name char (16) not null;

Delete column
Syntax: alter table Name drop column name;

-- Delete the birthday column in the students table: alter table students drop birthday;

Rename a table
Syntax: alter table name rename new table name;

-- Rename the students table as workmates: alter table students rename workmates;

Clear table data
Syntax: delete from table name;

-- Clear the table as data in workmates without deleting the table. Delete from workmates;

Delete the entire table
Syntax: drop table name;

-- Delete a workmates table: drop table workmates;

Delete the entire database
Syntax: drop database name;

-- Delete the samp_db database: drop database samp_db;

Reference manual
Http://www.bkjia.com/w3school/ SQL /index.asp.htm
Http://www.1keydata.com/cn/sq...

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.