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

Editor to share with you an example of the basic grammar of MYSQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

User management

New user and password: foo as name, 123 as password, locahost as fixed address to log in

# the following are two creation methods: CREATE USER foo@localhost IDENTIFIED BY '123' insert into mysql.user (Host,User,Password) values ("localhost", "test", password ("1234")); # as long as you change the user and password flush privileges

Set and change the user password:

# the following are three ways to change SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword') # if the currently logged in user SET PASSWORD = PASSWORD ('newpassword') update mysql.user set password=password (' newpassword') where User='username' and Host='host'#, as long as you change the user and password flush privileges

Delete a user:

Delete FROM user Where User='test' and Host='localhost';flush privileges;# deletes the user's database drop database testDB; # Deletes the account and permissions drop user username @ localhost

Authorization:

GRANT INSERT,DELETE,UPDATE,SELECT ON databasename.tablename TO 'username'@'host'flush privileges

Description:

(1) the operation permissions of privileges- users, such as SELECT, INSERT, UPDATE

(2) databasename-database name, tablename- table name, which can be expressed if you want to grant the user the corresponding operation permissions on all databases and tables, such as. *

(3) after newly setting users or changing passwords, you need to use flush privileges to refresh the system permission table of MySQL, otherwise access will be denied. Another way is to restart the mysql server to make the new settings take effect.

View permissions:

Show grants for root@localhost

Remove permissions:

# reverse operation of GRANT to remove permission REVOKE SELECT ON db_name.* TO name

Log in to remote MySQL (ubuntu): mysql-h-P remote port-u user-p password

# need remote ip and port: 10.10.101.111mysql 30061 remote mysql user and password mysql-h 10.10.101.111-P 30061-u root-p 123456

MYSQL data type

Database

View the database: SHOW DATABASES

Create a database: CREATE DATABASES db_name

Use database: USE db_name

Delete database: DROP DATABASE db_name

Set database encoding: set names utf8

Table creation table: CREATE TABLE table_name (id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR (60) NOT NULL,score TINYINT UNSIGNED NOT NULL DEFAULT 0 primary KEY (id)) ENGINE=InnoDB;// sets the storage engine of the table, commonly used InnoDB and MyISAM;InnoDB are reliable and support transactions; MyISAM efficiently does not support full-text search

Set table code: create table name (…) ENGINE=InnoDB DEFAULT CHARSET=utf8

Copy table: CREATE TABLE tb_name2 SELECT * FROM tb_name

Partial replication: CREATE TABLE tb_names SELECT id,name FROM tb_namr

Create a temporary table: CREATE TEMPORARY TABLE tb_name

Temporary tables: used in client-server sessions to handle specific transactions, saving space and privacy

View the available tables in the database: SHOW TABLES

View table structure: DESCRIBE tb_name; or SHOW COLUMNS FROM tb_name

Delete table: DROP [TEMPORARY] TABLE [IF EXISTS] tb_name [, tb_name2... .]

DROP TABLE IF EXISTS `db_ room`, `Student`

Table alias: SELECT a.title.content.u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid

Table rename: RENAME TABLE name_old TO name_new; or ALTER TABLE name_old RENAME name_new

Change the table structure: ALTER TABLE tb_name ADD [CHANGE, RENAME, DROP]

ALTER TABLE tb_name ADD COLUMN address varchar (80) NOT NULL;ALTER TABLE tb_name DROP address;ALTER TABLE tb_name CHANGE score score SMALLINT (4) NOT NULL; data

Insert data:

INSERT INTO tb_name (id,name,score) VALUES (NULL,' Zhang San', 140), (NULL,' Zhang Si', 178), (NULL,' Zhang Wu', 134)

Note: insert multiple pieces of data directly followed by a comma

Insert the retrieved data:

INSERT INTO tb_name (name,score) SELECT name score FROM tb_name2

Update data

UPDATE tb_name SET score=180 WHERE id=2

UPDATE tablename SET columnName=NewValue [WHERE condition]

Delete data:

DELETE FROM tb_name WHERE id=3

Conditional control

Where statement:

SELECT * FROM tb_name WHERE id=3

Group by

Group by interpretation

GROUP BY and WHERE joint query:

Select column a, aggregate function from table name where filter condition group by column a having filter condition

When combined, where comes first and group by comes after. That is, where is used to filter the set of records in select xx from xx, and then group by is used to group the filtered results. Having sentences are used to filter the grouped results.

HAVING statement:

SELECT * FROM tb_name GROUP BY score HAVING count (*) > 2

Having usage

* the above three practices:

Reprint: analysis of mysql group by usage (detailed)

Related condition control character:

=, >, = 2) as tbot # find these students, then calculate their average score select name,avg (score) from stu where name in (select name from (select name,count (*) as gk from stu having gk > = 2) as t) group by name

Exists subquery: (take the outer query result to the inner layer to see whether the inner layer query is valid)

# query which columns have commodities, column table category, merchandise table goodsselect cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id); MySQL function

Distinct: deduplicated Select player_id,distinct (task_id) from task

Distinct removes duplicate samples (multiple fields)

Select distinct Student.Sno,Sname from Student (another way)

String concatenation-- CONCAT ()

SELECT CONCAT (name, "= >", score) FRON tb_name

Mathematical function:

AVG 、 SUM 、 MAX 、 MIN 、 COUNT

Text processing function:

TRIM 、 LOCATE 、 UPPER 、 LOWER 、 SUNSTRING

Operator:

+, -, *,\

Time function:

DATE (), CURTIME (), DAY (), YEAR (), NOW ()... ..

JOIN detailed explanation

Join is used to connect fields in multiple tables.

... FROM table1 INNER | LEFT | RIGHT JOIN table2 ON conditiona

JOIN is roughly divided into the following three categories according to its functions:

* INNER JOIN (inner join, or equivalent join): get the records of the join matching relationship between the two tables

* LEFT JOIN (left join): get the complete record in the left table (table1), that is, there is no matching record in the right table (table2)

* RIGHT JOIN (right join): contrary to LEFT JOIN, a complete record is obtained in the right table (table2), that is, there is no matching record in the left table (table1)

Note: mysql does not support Full join, but you can use the UNION keyword to combine LEFT JOIN and RIGHT JOIN to simulate FULL join.

Specific reference blog Mysql Join syntax parsing and performance analysis

UNION rule

UNION is used to combine results from multiple SELECT statements into a result set: in multiple SELECT statements, the corresponding columns should have the same field properties

SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2...

The difference between UNION and UNION ALL:

When using UNION, MySQL deletes duplicate records in the result set, while with UNION ALL, MySQL returns all records and is more efficient than UNION.

Refer to blog MySQL UNION and UNION ALL grammar and usage

View

A view is a table derived from one or more tables and is a virtual table. Only the definition of the view is stored in the database, but not the data in the view, which is stored in the original table.

Background: improved security and query performance

-simplify the operation and define a view for frequently used queries so that users do not have to specify conditions for the same query operation

-increase the security of the data. Through the view, users can only query and modify the specified data.

Save space, if the content is always consistent, then we do not need to maintain the contents of the view, maintain the contents of the real table, we can ensure the integrity of the view

-improve the logical independence of the table, and the view can shield the impact of changes in the original table structure.

How it works: when the view is called, the sql in the view is executed to fetch data. The contents of the view are not stored, but the data is derived when the view is referenced. This does not take up space, and because it is an instant reference, the contents of the view are always consistent with the contents of the real table. Update the view to update the real table.

Views and databases:

The data in the view depends on the data in the original table, and once the data in the table changes, the data displayed in the view will also change.

The creation and deletion of the view only affects the view itself, not the corresponding basic table.

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the base table. For updatable views, there must be an one-to-one relationship between the rows in the view and the rows in the base table. There are also specific other structures that make the view unupdatable. More specifically, a view is not updatable if it contains any of the following structures.

Although the data can be updated in the view, there are many restrictions. In general, it is best to use the view as a virtual table for querying data, rather than updating the data through the view. Because, when updating data with a view, if you do not fully consider the limitations of updating data in the view, the data update may fail.

Indexes

10W pieces of data, retrieving nickname='css'

General: SELECT * FROM award WHERE nickname = 'css': mysql needs to scan the whole table and scan 10W pieces of data to find this data

Index: build an index on nickname, then mysql only needs to scan a row of data

Indexes are divided into single-column indexes (primary key index, only index, general index) and combined indexes.

Single column index: an index contains only one column, and a table can have multiple single column indexes.

Composite index: a composite index contains two or more columns

Single column index:

General index. This is the most basic index.

ALTER table SC ADD INDEX Sno_Index (Sno); / / Note symbol, not single quotation mark

Note: field: CHAR,VARCHAR, type, index: length can be less than the actual length of the field. If it is BLOB and TEXT, the length must be specified.

Unique index: a unique index requires the values of all classes to be unique, just like primary key indexes. But it allows null values.

Create UNIQUE INDEX sname ON Student (Sname)

Primary key index, null values are not allowed:

Rule: int is superior to varchar and is generally created when creating a table, preferably columns that are not related to other fields of the table or columns that are not related to the business. It is generally set to int and is AUTO_INCREMENT self-increasing type.

Combinatorial index

Just because a table contains multiple single-column indexes does not mean that it is a combined index. generally speaking, a combined index contains multiple fields but only index names.

Create: CREATE INDEX Tno_Tname_index ON Teacher (Tno,Tname)

Full-text index

If a general index is established on a text field (text), then only the characters in front of the field content of the text are indexed, and the character size is specified according to the size declared when the index is indexed.

Build: ALTER TABLE tablename ADD FULLTEXT (column1, column2)

Delete index

DORP INDEX IndexName ON TableName

View Index

Show index from tblname

This reference blog details the mysql index, which is written in detail.

Index principle: index principle

Storage

A set of SQL statements that are compiled and stored in a database in order to perform specific functions. Stored procedures are useful when you want to execute the same function on different applications or platforms, or when you encapsulate specific functions. A stored procedure is similar to a programmable function. In MySQL, a single Store Procedure (SP) is not an atomic operation, and the way to make the entire stored procedure an atomic operation is at the beginning of the body of the stored procedure.

Note: the non-transaction causes the stored procedure to have no atomicity, that is, some successes and failures of the process, and the transaction increases atomicity. Even if the execution process goes wrong, the operation before the error will not be really executed. Http://www.cnblogs.com/fnlingnzb-learner/p/6861376.html

Advantages:

1. Fast execution: the stored procedure is precompiled and optimized by the query optimizer.

two。 Can be called and modified many times

3. Flexible function: can be written with flow control statements to complete complex operations

4. Security: set stored procedure permissions to ensure data security

5. Reduce traffic: when calling a stored procedure, the network can only transmit this call statement

Grammar

Structure:

CREATE PROCEDURE procedure name ([[IN | OUT | INOUT] parameter name data type [, [IN | OUT | INOUT] parameter name data type …]]) [characteristics...] Process body

DELIMITER / / CREATE PROCEDURE myproc (OUT s int) BEGIN SELECT COUNT (*) INTO s FROM students; END / / DELIMITER

Explanation:

(1) Delimiter: mysql defaults to ";", "DELIMITER / /" declares the delimiter, and finally "DELIMITER;" restores the delimiter

(2) parameters: input, output, input and output parameters {IN,OUT,INOUT} refer to mysql storage

Variable

DECLARE local variable: DECLARE var_name [,...] Type [DEFAULT value]

To provide a default value for a variable, include a DEFAULT clause. Value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL. The scope of a local variable is in the BEGIN where it is declared. Within the END block

Variable SET statement: SET var_name = expr [, var_name = expr]

The referenced variable may be a variable declared in a subroutine, or a global server variable

SELECT... INTO statement: SELECT col_name [,...] INTO var_name [,...] Table_expr

Stores the selected column directly to a variable

Basic common functions: refer to blog: Mysql stored procedure

String class: the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1

CHARSET (str) / / return string character set CONCAT (string2 [,...]) / / concatenate string INSTR (string, substring) / / returns the position where substring first appeared in string, there is no return 0 LCASE (string2) / / convert to lowercase LEFT (string2, length) / / fetch length characters LENGTH (string) / / string length LOAD_FILE (file_name) / / read from the file LOCATE (substring, string [) Start_position] is the same as INSTR, but you can specify the starting position LPAD (string2, length, pad) / / add pad to the beginning of string repeatedly until the string length is length LTRIM (string2) / / remove the front-end space REPEAT (string2, count) / / repeat count times REPLACE (str, search_str, replace_str) / / replace search_str RPAD (string2, length, pad) with replace_str in str / / add it with pad after str Until the length is length RTRIM (string2) / / remove the backend space STRCMP (string1, string2) / / compare the size of the two strings character by character, SUBSTRING (str, position [, length]) / / start with the position of str, take length characters, TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) / / remove the specified character UCASE (string2) / / convert it to uppercase RIGHT (string2) Length) / / take the last length character of string2 SPACE (count) / / generate count spaces

Mathematics class

ABS (number2) / / absolute value BIN (decimal_number) / / Decimal to binary CEILING (number2) / / rounding up CONV (number2,from_base,to_base) / / binary conversion FLOOR (number2) / / rounding down FORMAT (number,decimal_places) / / reserved decimal HEX (DecimalNumber) / / convert hexadecimal Note: if a string can be passed into HEX (), its ASC-11 code is returned If HEX ('DEF') returns 4142143, you can also pass a decimal integer and return its hexadecimal code. For example, HEX (25) returns 19 LEAST (number, number2 [,..]) / / minimum MOD (numerator, denominator) / / congruent POWER (number, power) / / Index RAND ([seed]) / / Random number ROUND (number [, decimals]) / / rounded, decimals is decimal] Note: not all return types are integers As follows: SIGN (number2) / /

Date and time class

ADDTIME (date2, time_interval) / / add time_interval to date2 CONVERT_TZ (datetime2, fromTZ, toTZ) / / convert time zone CURRENT_DATE () / / current date CURRENT_TIME () / / current time CURRENT_TIMESTAMP () / / current timestamp DATE (datetime) / / return the date part of datetime DATE_ADD (date2, INTERVAL d_value d_type) / / add date or time DATE_FORMAT (datetime) to date2 FormatCodes) / / use formatcodes format to display datetime DATE_SUB (date2, INTERVAL d_value d_type) / / subtract one time DATEDIFF (date1, date2) / / two date differences DAY (date) / / return date DAYNAME (date) / / English DAYOFWEEK (date) / / week (1-7) 1 is DAYOFYEAR (date) / / the day of the year EXTRACT (interval_name FROM date) / / the specified part of the MAKEDATE (year, day) extracted from the date / / gives the date string MAKETIME (hour, minute, second) / / the generation time string MONTHNAME (date) / / English month name NOW () / / current time SEC_TO_TIME (seconds) / second conversion time STR_TO_DATE (string) Format) / / string conversion time, displayed in format format TIMEDIFF (datetime1, datetime2) / / two time differences TIME_TO_SEC (time) / / time seconds] WEEK (date_time [ Start_of_week]) / / week YEAR (datetime) / / year DAYOFMONTH (datetime) / / Day of month HOUR (datetime) / hour LAST_DAY (date) / / month Last date of date MICROSECOND (datetime) / / microsecond MONTH (datetime) / / month MINUTE (datetime) / / minute return symbol, plus or minus or 0 SQRT (number2) / / squared cursor

Definition: the identification of swimming, which gives all the results relative to the ordinary one-time query; the function of the cursor is to analyze and process a piece of data sample, like a pointer.

Use:

1. Disclaimer: declare cursor name cursor for select_statement

two。 Open: open cursor name

3. Value: fetch cursor name into var1,var2 [, …]

4. Off: close cursor name

Business

Mysql transactions are mainly used to deal with data with large amount of operations and high complexity. For example, when you delete something, you have to delete itself and what it depends on. All these actions form a transaction.

Note:

-MYSQL: only databases or tables of the Innodb database engine support transactions

-transaction processing is used to maintain database integrity, that is, to ensure that all or none of the batch SQL statements are executed.

-transactions are used to manage insert,update,delete statements

The transaction meets four conditions:

1. Atomicity of transactions: success or failure

two。 Stability: illegal data, transaction withdrawn

3. Isolation: transactions run independently

4. Reliability: when a crash occurs, the InnoDB data table driver uses log files to reconstruct and modify

Refer to blog affairs

Import and export

Export the entire database: mysqldump-u user name-p database name > exported file name

Export a table: mysqldump-u user name-p database name table name > exported file name

Export a data structure: mysqldump-u dbuser-p-d-add-drop-table dbname > d:/dbname_db.sql (- d has no data-add-drop-table adds a drop table before each create statement)

Import the database:

Use database; source dvvlamp dbname.sql; performance-optimized query instance

Database questions: student table, course schedule, course schedule. There are three basic tables in the teaching database:

Student table Student (Sno,Sname,Age,Sex), whose attribute indicates the student's student number, name, age and gender

Course selection schedule SC (Sno,Cno,score), whose attribute indicates the student's student number, course number and grade of the course studied.

Course (Cno,Cname,Tho), whose attributes represent the course number, the course name, and the name of the teacher

Teacher table Teacher (Tno,Tname), whose attributes represent teacher number and teacher name

The following topics are all for the above three basic tables.

* Import sql file: source course.sql; sql file encoding format: UTF-8 without BOM

Drop database IF EXISTS db_school;CREATE database db_school;use db_school;DROP TABLE IF EXISTS `db_ course`.`Student`; create table Student (Sno varchar (20), Sname varchar (50), Age smallint, Sex varchar (5), primary key (Sno) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `Course` Create table Course (Cno varchar (20), Cname varchar (50), Tno varchar (20), primary key (Cno)) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `db_ `.`SC`; create table SC (Sno varchar (20), Cno varchar (20), score int, primary key (Sno,Cno)) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `db_ room`.`Teacher` Create table Teacher (Tno varchar (20), Tname varchar (50), primary key (Tno)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Student` (Sno,Sname,Age,Sex) VALUES ('001Zhe Magic' Chen Yi', 25Phenna'); INSERT INTO `Student` (Sno,Sname,Age,Sex) VALUES ('002Ji' Guo er', 20pm ZNV'); INSERT INTO `Student` (Sno,Sname,Age,Sex) VALUES ('003Jing' Zhang San', 25pnv`) INSERT INTO `Student` (Sno,Sname,Age,Sex) VALUES; INSERT INTO `Student` (Sno,Sname,Age,Sex) VALUES; INSERT INTO `Teacher` (Tno,Tname) VALUES (001mil Zhang); INSERT INTO `Teacher` (Tno,Tname) VALUES (002pl 'Wang'); INSERT INTO `Teacher` (Tno,Tname) VALUES INSERT INTO `Teacher` (Tno,Tname) VALUES ('004Masters' Liu); INSERT INTO `Teacher` (Tno,Tname) VALUES ('005minus' Mr. Hu'); INSERT INTO `Course` (Cno,Cname,Tno) VALUES ('001Yuzhong' Chinese', 'Mr. Zhang'); INSERT INTO `Course` (Cno,Cname,Tno) VALUES ('002Math', 'Miss Wang'); INSERT INTO `Course` (Cno,Cname,Tno) VALUES ('003Zhi' English', 'teacher Qian') INSERT INTO `Course` (Cno,Cname,Tno) VALUES ('004mpl' Physics', 'Miss Liu'); INSERT INTO `Course` (Cno,Cname,Tno) VALUES ('005pm' Politics', 'Miss Hu'); INSERT INTO `SC` (Sno,Cno,score) VALUES ('001Zuo 50); INSERT INTO `SC` (Sno,Cno,score) VALUES (' 001pc002 Zuo 60); INSERT INTO `SC` (Sno,Cno,score) VALUES ('001ZM003Zhon70) INSERT INTO `SC` (Sno,Cno,score) VALUES ('001pc004) VALUES; INSERT INTO `SC` (Sno,Cno,score) VALUES (' 001pc005) VALUES); INSERT INTO `SC` (Sno,Cno,score) VALUES ('002pc001) VALUES (' 002pc002) VALUES ('002pr) 80); INSERT INTO `SC` (Sno,Cno,score) VALUES (' 002ZWE) 70) INSERT INTO `SC` (Sno,Cno,score) VALUES (60); INSERT INTO `SC` (Sno,Cno,score) VALUES (50); INSERT INTO `SC` (Sno,Cno,score) VALUES (81); INSERT INTO `SC` (Sno,Cno,score) VALUES (82); INSERT INTO `SC` (Sno,Cno,score) VALUES (83) INSERT INTO `SC` (Sno,Cno,score) VALUES (INSERT INTO `SC` (Sno,Cno,score) VALUES)

Write a SQL sentence that retrieves the course numbers of all the courses taken by female students.

Select Cno from Student,SC where Student.Sno=SC.Sno AND Student.Sex='nv'

Write the following SQL statement for the insert operation: insert the average grade for each course in the SC table into another existing table, SC _ C (centering journal CNAME, AVG_GRADE), where AVG_GRADE is the average grade for each course.

# create SC_C table first, and create insert into SC_C select SC.Cno,Cname,AVG (score) AS Avg_score FROM Course,SC WHERE Course.Cno=SC.Cno GROUP BY SC.Cno by yourself

Try to write the following SQL statement for delete operation: delete the tuple of Miss Wang's female students from the SC table.

Delete from SC where Sno in (select Sno from Student where Sex='nv') AND Cno in (Mr. select Cno from Course where Tno=' Wang')

Query the student numbers of all students whose grades in "001" course are higher than those in "002" course.

Select a.Sno from (select Sno,score FROM SC where Cno='001') AS a, (select Sno,score FROM SC where Cno='002') AS b WHERE a.score > b. Score / specify the column with tablename.columnname when the same column name exists in two tables

Check all students' student numbers, names, number of courses chosen and total grades.

Select Student.Sno,Student.Sname,count (SC.Cno), sum (SC.score) from Student left OUTER join SC on Student.Sno=SC.Sno group by Student.Sno,Student.Sname; above are all the contents of this article "MYSQL basic Grammar examples". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.