Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to use MySQL sentence". Friends who are interested might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use the MySQL sentence.

Start using

All of my SQL statements below are run based on MySQL 5.6 +.

MySQL is a relational database (Relational Database Management System). A relational database consists of one or more tables, as shown in the figure:

Header (header): the name of each column

Col: a collection of data with the same data type

Row: the specific information used by each line to describe a person / thing

Value (value): specific information about the row, each value must be the same as the data type of the column

Key: the method used in a table to identify a particular person whose value is unique in the current column.

Log in to MySQLmysql-h 127.0.0.1-u user name-pmysql-D selected database name-h hostname-u user name-pmysql > exit # quit using "quit;" or "\ Q;" the same effect mysql > status; # displays various information of the current mysql's version mysql > select version (); # displays the current mysql version information mysql > show global variables like 'port'; # View MySQL port number to create the database

The operation of the table requires the advanced use library name

-- create a database named samp_db with the database character encoding specified as gbkcreate database samp_db character set gbk;drop database samp_db;-- delete the library show databases; named samp_db-- display the list of databases. Use samp_db;-- Select the created database samp_dbshow tables;-- display all the table names under samp_db describe table name;-- display the structure delete from table name of the data table;-- empty the records in the table to create the database table.

Use the create table statement to create the table, a common form of create table:

Syntax: create table table name (column declaration)

CREATE TABLE `password` (`id`int) 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 is not required 1-need to reset password', `mobile` varchar (20) NOT NULL DEFAULT''COMMENT' mobile phone', `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), create unique index, do not allow duplicate UNIQUE INDEX idx_user_mobile (`mobile`) ENGINE=InnoDB DEFAULT CHARSET=utf8COMMENT=' user table information'

Attribute interpretation of data types

NULL: data columns can contain null values

NOT NULL: data columns are not allowed to contain null values

DEFAULT: default

PRIMARY:KEY primary key

AUTO_INCREMENT: auto increment, suitable for integer types

UNSIGNED: it means that the numerical type can only be positive.

CHARACTER SET name: specify a character set

COMMENT: description of a table or field

Add, delete, change and check SELECT

The SELECT statement is used to select data from a table.

Syntax: SELECT column name FROM table name

Syntax: SELECT * FROM table name

-- Table station takes individual name s, table station does not contain fields id=13 or 14, and id is not equal to 4, and only shows idSELECT s.id from station s WHERE id in (1313 14) and user_id not in (4) -- the data SELECT LastName FROM Persons-- result set that selects the LastName column from the table Persons automatically deduplicates the data SELECT DISTINCT Company FROM Orders-- the table Persons field Id_P equals the value of the Orders field Id_P, and the result set displays the LastName and FirstName fields of the Persons table The OrderNo fields SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P of the Orders table-- the easiest way to sort gbk and utf8 mixed in English and Chinese-- ci is case insensitive, that is, "case insensitive" SELECT tag, COUNT (tag) from news GROUP BY tag order by convert (tag using gbk) collate gbk_chinese_ci SELECT tag, COUNT (tag) from news GROUP BY tag order by convert (tag using utf8) collate utf8_unicode_ci;UPDATE

The Update statement is used to modify the data in the 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 update user set name= (select name from user1 where user1. ID = 1) where id= (select id from user2 where user2. Name = 'Sue') taken from another result;-- update the row of id=1 in the table orders. Update its title field UPDATE `orders` set title='. Here is the title 'WHERE id=1;INSERT.

The INSERT INTO statement is used to insert a new row into the table.

Syntax: INSERT INTO table name VALUES (value 1, value 2....)

Syntax: INSERT INTO table name (column 1, column 2, etc.) VALUES (value 1, value 2pm.)

-- insert a field LastName = JSLite field Address = shanghaiINSERT INTO Persons (LastName, Address) VALUES ('JSLite',' shanghai') into table Persons;-- insert field aqum1 and field b=2INSERT INTO meeting SET axi1 into table meeting -SQL implements the code that inserts data from one table into another-- if you only want to import specified fields, you can use this method:-- INSERT INTO target table (field 1, field 2,...) SELECT field 1, field 2,... FROM source table; INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.idyst1terDelete

The DELETE statement is used to delete rows from a table.

Syntax: DELETE FROM table name WHERE column name = value

Delete all rows and empty the table without deleting the table_ name table. DELETE FROM table_name-- or DELETE * FROM table_name-- delete the Person table field LastName = 'JSLite' DELETE FROM Person WHERE LastName =' JSLite'-delete two pieces of data DELETE from meeting where id in with tables meeting id of 2 and 3; WHERE

The WHERE clause is used to specify the criteria for selection.

Syntax: SELECT column name FROM table name WHERE column operator value

Select data SELECT * FROM Persons WHERE Year > 1965AND and OR whose Year field is greater than 1965 from the table Persons

AND-if both the first condition and the second condition hold

OR-if only one of the first and second conditions holds

AND-- deletes the meeting table fields-- id=2 and user_id=5 data and-- id=3 and user_id=6 data DELETE from meeting where id in (2) and user_id in (5);-- uses AND to display all people with the last name "Carter" and the first name "Thomas": SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter' OR-- uses OR to display all people with the last name "Carter" or "Thomas": SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'ORDER BY

Statement sorts records in ascending order by default.

The ORDER BY-statement is used to sort the result set according to the specified column.

DESC-sorts the records in descending order.

ASC-sorts the records in order.

If Company is the letter in table Orders, the company name SELECT Company will be displayed in alphabetical order, OrderNumber FROM Orders ORDER BY Company-- followed by DESC will show SELECT Company in descending order, OrderNumber FROM Orders ORDER BY Company DESC-- Company will show company name in descending order, and OrderNumber will show SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASCIN in order.

The IN-operator allows us to specify multiple values in the WHERE clause.

The IN-operator is used to specify a range, and each item in the range is matched. IN value rule, separated by commas, all in parentheses.

Syntax: SELECT field name "FROM" table name "WHERE" field name "IN ('value one', 'value two',...)

Select fields LastName from table Persons equal to Adams, CarterSELECT * FROM Persons WHERE LastName IN ('Adams','Carter') NOT

The NOT-operator is always used with other operators in front of the filter.

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

The UNION-operator is used to merge the result sets of two or more SELECT statements.

-- list all different employee names in the China table (Employees_China) and the United States (Employees_USA) SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA-- list the number_station aliases in the pic_url,-- station table in the meeting table set to pic_url to avoid errors in the field-- sort SELECT id,pic_url FROM meeting UNION ALL SELECT id,number_station AS pic_url FROM station ORDER BY update_at;AS by update time

As-can be understood as: as, as, as; alias

It is common to rename a column or table name.

Syntax: select column_1 as column 1 column column 2 as column 2 from table as table

The sentence "SELECT * FROM Employee AS emp--" means to find all the data in the Employee table and name the Employee table emp. After you name a table, you can use emp instead of Employee.-- such as SELECT * FROM emp.SELECT MAX (OrderPrice) AS LargestOrderPrice FROM Orders-- below to list the maximum value of the Orders field OrderPrice column of the table.-- the result set column does not display the OrderPrice display LargestOrderPrice-- displays the name column SELECT t.name from (SELECT * from users_profile a) AS t in the table users_profile. -- Table user_accounts named alias ua, table users_profile named alias up-- satisfies the condition that table user_accounts field id equals table users_profile field user_id-- result set shows only mobile and name columns SELECT ua.mobile,up.name FROM user_accounts as ua INNER JOIN users_profile as up ON ua.id = up.user_id;JOIN

Used to query data from two or more tables based on the relationship between the columns in those tables.

JOIN: returns a row if there is at least one match in the table

INNER JOIN: the INNER JOIN keyword returns a row when there is at least one match in the table.

LEFT JOIN: returns all rows from the left table even if there is no match in the right table

RIGHT JOIN: returns all rows from the right table even if there is no match in the left table

FULL JOIN: returns a row as long as there is a match in one of the tables

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

COUNT allows us to count how many pieces of data have been selected in the table.

Syntax: SELECT COUNT ("field name") FROM "table name"

Table Store_Information has several store_name columns that are not blank. "IS NOT NULL" means "this field is not blank". SELECT COUNT (Store_Name) FROM Store_Information WHERE Store_Name IS NOT NULL;-- get the total number of Persons tables SELECT COUNT (1) AS totals FROM Persons;-- get the same total number of station fields user_id select user_id, count (*) as totals from station group by user_id;MAX

The MAX function returns the maximum value in a column. The NULL value is not included in the calculation.

Syntax: SELECT MAX ("field name") FROM "table name"

List table Orders field OrderPrice column maximum, result set column does not display OrderPrice display LargestOrderPriceSELECT MAX (OrderPrice) AS LargestOrderPrice FROM Orders trigger

Syntax:

Create trigger

{before | after} # before or after departure

Insert | update | delete # indicates the type of statement that activates the trigger

On # which table to operate on

The execution interval of the for each row # trigger, where for each row tells the trigger to perform an action every other row, rather than once on the entire table.

DELIMITER $--Custom closing symbol CREATE TRIGGER set_userdate BEFORE INSERT on `message`for EACH ROWBEGIN UPDATE `user_ termints` SET status=1 WHERE openid=NEW.openid;END$DELIMITER;-- restore closing symbol

OLD and NEW are not case sensitive

NEW uses NEW.col_name and has no old lines. In the DELETE trigger, only OLD.col_name can be used, and there are no new lines.

OLD uses OLD.col_name to refer to the columns of a row before the update

Add Index General Index (INDEX)

Syntax: ALTER TABLE table name ADD INDEX index name (field name)

-directly create index CREATE INDEX index_user ON user (title)-add index ALTER TABLE table_name ADD INDEX index_name ON (column (length)) by modifying table structure-- add normal index (INDEX) ALTER TABLE `table` ADD INDEX index_name (name) to name field in user table-create index CREATE TABLE `table` (`id` int (11) NOT NULL AUTO_INCREMENT at the same time when creating table `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 (PRIMARY key)

Syntax: ALTER TABLE table name ADD PRIMARY KEY (field name)

-- add a primary key index (PRIMARY key) ALTER TABLE `user` ADD PRIMARY key (id) to the id field in the user table; unique index (UNIQUE)

Syntax: ALTER TABLE 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 table name ADD FULLTEXT (field name)

-- add full-text index (FULLTEXT) ALTER TABLE `user` ADD FULLTEXT (description) to the description field in the user table, and add multi-column index

Syntax:

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3)

-- add a normal index (INDEX) ALTER TABLE user ADD INDEX name_city_age (name (10), city,age) with the name name_city_age to the name, city, age fields in the user table; the time to establish the index

Columns that appear in WHERE and JOIN need to be indexed, but not exactly:

MySQL uses the index only for =, BETWEEN,IN

Indexes are also used in some LIKE.

When LIKE queries with the wildcard characters% and _, MySQL does not use indexes.

-- city and age need to be indexed at this point-- because the userame of the mytable table also appears in the JOIN clause, it is also necessary to index it. SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND M. cityboy 'Shanghai'; SELECT * FROM mytable WHERE username like'admin%';-and the following sentence will not be used: SELECT * FROM mytable WHEREt Name like'%admin';-therefore, you should pay attention to the above differences when using LIKE.

Matters needing attention of index

The index will not contain columns with null values

Use short index

Do not operate on the column. The index will fail.

Modify and add columns of the table after creation

Syntax: alter table table name add column name column data type [after insertion location]

Example:

-- append column address: alter table students add address char (60) to the end of table students;-- insert column birthday after column named age: alter table students add birthday date after age;-- insert column weeks: alter table students add column `Secrets` varchar (5) not null default "after `number_ people` after column named number_people; modify column

Syntax: alter table 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 name char (16) not null;-- must have the type attribute alter table students change name name char (16) COMMENT 'here is the name' before modifying the COMMENT. -- it is recommended to use modify when modifying column properties. There is no need to rebuild the table-- change is used to modify column names. This requires rebuilding the table alter table meeting modify `Secrets` varchar (20) NOT NULL DEFAULT "" COMMENT "Open date Monday to Sunday: 0q6, separated by English commas at intervals; delete columns

Syntax: alter table table name drop column name

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

Syntax: alter table table name rename new table name

Rename the students table to workmates: alter table students rename workmates; to clear the table data

Method 1: delete from table name

Method 2: truncate from "table name"

DELETE:1. DML language 2. Can fall back; 3. Can be deleted conditionally

TRUNCATE:1. DDL language 2. Unable to fall back; 3. By default, all table contents are deleted; 4. Deletion is faster than delete.

-clear the data in the table as workmates without deleting the table. Delete from workmates;-- deletes all data in the workmates table, and cannot restore truncate from workmates; to delete the entire table

Syntax: drop table table name

-- Delete workmates table: drop table workmates; deletes the entire database

Syntax: drop database database name

Delete samp_db database: drop database samp_db; so far, I believe you have a deeper understanding of "how to use MySQL statements". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.