Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the basic knowledge points of getting started with mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, start to use

MySQL is a relational database (Relational Database Management System). A relational database consists of one or more tables, which generally include the following:

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 / object. The value of the key is unique in the current column.

Log in to MySQLmysql-h 127.0.0.1-u user name-database name selected by pmysql-D-h hostname-u user name-p III, 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 utf8 create database samp_db character set utf8; 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_db show tables;-- display all the table names under the samp_db describe table name;-- display the structure of the data table delete from table name;-- clear the records in the table 4. Create a database table.

Use the create table statement to create the table. Common form of create table: syntax: create table table name (column declaration)

-- if there is a user_accounts table in the database, drop it from the database: DROP TABLE IF EXISTS `user_ accounts`; 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-No need to reset password 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=utf8 COMMENT=' 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

5. Add, delete, modify and check

1 、 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 id SELECT s.id from station s WHERE id in (1313 14) and id not in (4) -- selecting LastName column data SELECT LastName FROM Persons from table Persons-- selecting id=3 data from table users, and pulling only one data (which is said to optimize performance) SELECT * FROM users where id=3 limit 1-- the result set automatically deduplicates the data SELECT DISTINCT Company FROM Orders-- the table Persons field Id_P equals the value of the Orders field Id_P,-- 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

2 、 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 ='Su') taken from another result;-- update the row of data in the table orders to update its title field UPDATE `orders` set title='. Here is the title 'WHERE id=1.

3 、 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 = shanghai INSERT INTO Persons (LastName, Address) VALUES ('JSLite',' shanghai') into table Persons;-- insert field aqum1 and field bread2 INSERT INTO meeting SET astat1 into table meeting -- SQL implements the code to insert 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.idroom1;-insert a piece of data into the table charger, and update the `type` and `update_ at` fields to the table charger if it already exists. INSERT INTO `charger` (`id`, `type`, `create_ at`, `charger at`) VALUES (3Zhi 2Jing, May 2017, May 18, November 11, 2011) ON DUPLICATE KEY UPDATE `id` = VALUES (`id`), `type` = VALUES (`type`), `update_ at` = VALUES (`update_ at`)

4 、 DELETE

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-- deletes the Person table field LastName = 'JSLite' DELETE FROM Person WHERE LastName =' JSLite'-- deletes two pieces of data DELETE from meeting where id in with tables meeting id of 2 and 3 (2 and 3)

5 、 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 with Year field greater than 1965 from table Persons SELECT * FROM Persons WHERE Year > 1965VI, operator

1. AND and OR

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

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

1.1 、 AND

-- Delete 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);-- use AND to display all people with the last name "Carter" and "Thomas": SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

1.2 、 OR

Use OR to display all people with the last name "Carter" or "Thomas": SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

2 、 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.

-- Company is the letter in the table Orders, then the company name SELECT Company is displayed in alphabetical order, OrderNumber FROM Orders ORDER BY Company-- followed by DESC shows SELECT Company in descending order, OrderNumber FROM Orders ORDER BY Company DESC-- Company shows the company name in descending order, and OrderNumber displays SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC in order

3 、 IN

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 from table Persons LastName equals Adams, Carter SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

4 、 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 BY prod_name

5 、 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)-- list the pic_url in the meeting table,-- set the number_station alias in the station table to pic_url to avoid errors in the field-- sort the SELECT id,pic_url FROM meeting UNION ALL SELECT id,number_station AS pic_url FROM station ORDER BY update_at by update time. -- query the total number of records of both the products table and the comments table through UNION syntax, and sort SELECT 'product' AS type, count (*) as count FROM `products`union select' comment' as type, count (*) as count FROM `comments` order by count by count

6 、 AS

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

SELECT * FROM Employee AS emp-this sentence 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 below. -- such as SELECT * FROM emp. SELECT MAX (OrderPrice) AS LargestOrderPrice FROM Orders-- list table Orders field OrderPrice column maximum,-- result set column does not display OrderPrice display LargestOrderPrice-- display name column SELECT t.name from (SELECT * from users_profile a) AS t in table users_profile -- Table user_accounts naming alias ua, Table users_profile naming alias up-- satisfying the condition that table user_accounts field id equals table users_profile field user_id-- the result set only shows 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

7 、 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 (MySQL is not supported and is implemented through LEFT JOIN + UNION + RIGHT JOIN)

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName; VII, SQL function

1 、 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 table station fields user_id select user_id, count (*) as totals from station group by user_id

2 、 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 LargestOrderPrice SELECT MAX (OrderPrice) AS LargestOrderPrice FROM Orders

3 、 round

The ROUND function is used to round numeric fields to the specified number of decimal places.

Syntax: SELECT ROUND (column_name,decimals) FROM table_name

-- list the integers whose name and price are rounded to the nearest SELECT ProductName, ROUND (UnitPrice,0) as UnitPrice FROM Products

4 、 format

The FORMAT function is used to format the display of fields.

Syntax: SELECT FORMAT (column_name,format) FROM table_name

-displays the name and price corresponding to the date of each day (the display format of the date is "YYYY-MM-DD") SELECT ProductName, UnitPrice, FORMAT (Now (), 'YYYY-MM-DD') as PerDate FROM Products

5 、 ucase

The UCASE function converts the value of the field to uppercase.

Syntax: SELECT UCASE (column_name) FROM table_name

Select the contents of the "LastName" and "FirstName" columns, and convert the "LastName" column to uppercase. SELECT UCASE (LastName) as LastName,FirstName FROM Persons

6 、 lcase

The LCASE function converts the value of the field to lowercase.

Syntax: SELECT LCASE (column_name) FROM table_name

Select the contents of the "LastName" and "FirstName" columns, and then convert the "LastName" column to lowercase SELECT LCASE (LastName) as LastName,FirstName FROM persons8, triggers

Syntax: create trigger {before | after} # before or after insert | update | delete # indicates the type of statement that activates the trigger. On # operates on which table for each row # the execution interval of the trigger, and for each row tells the trigger to perform an action every other row, rather than on the entire table.

Delimiter $CREATE TRIGGER set_userdate BEFORE INSERT on `message` for EACH ROW BEGIN set @ statu = new.status;-- declare the replication variable statu if @ statu = 0 then-- determine whether statu is equal to 0 UPDATE `message` SET status=1 WHERE openid=NEW.openid; end if; END $DELIMITER;-- restore the ending 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 an index

1. 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 `user` ADD INDEX index_name (name) to name field in user table-create index CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT at the same time when creating the 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

2. 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

3. 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

4. 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

5. 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) named name_city_age to the name, city, age fields in the user table

6. 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 WHERE Name like'%admin';-therefore, you should pay attention to the above differences when using LIKE. Modification of the table after creation

1. Add columns

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

Example:

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

2. Modify the 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;-- there must be a 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 the column name. This requires rebuilding the table alter table meeting modify `Secrets` varchar (20) NOT NULL DEFAULT''COMMENT' open from Monday to Sunday, separated by English commas.' The `id` column of the `user` table is modified to a string type of length of 50, which cannot be empty. `FIRST` is placed in the first column alter table `user`user`id` varchar (50) NOT NULL FIRST

3. Delete columns

Syntax: alter table table name drop column name

-- delete the birthday column in table students: alter table students drop birthday

4. Rename the table

Syntax: alter table table name rename new table name

Rename the students table to workmates: alter table students rename workmates

5. Clear the table data

Method 1: delete from table name; method 2: truncate table "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;-all data in the workmates table is deleted and truncate table workmates cannot be restored

6. Delete the whole table

Syntax: drop table table name

-- Delete workmates table: drop table workmates

7. Delete the entire database

Syntax: drop database database name

-- Delete samp_db database: drop database samp_db; 11, other

1. SQL deletes duplicate records

-- look for redundant duplicate records in the table. Duplicate records are judged by select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1) based on a single field (peopleId). Delete redundant duplicate records from the table, and duplicate records are judged based on a single field (peopleId). Leave only the smallest rowid record delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId) > 1)-look up redundant duplicate records (multiple fields) select * from vitae a where (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)-delete redundant duplicate records in the table (multiple fields) Only the smallest record of rowid is left delete from vitae a where (a.peopleIdline a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)-look up redundant duplicate records in the table (multiple fields) Does not include the smallest record of rowid select * from vitae a where (a.peopleIdgrama.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1) "what are the basic knowledge points of mysql introduction" here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.