The basics of MySQL learning notes

The foundation of Database learning

① Open Database Interconnect CONNECTIVITY,ODBC

② structured querying language (structured query Language)

③ into mysql:mysql-u cz-p cz;

Database:show databases of ④ display system;

⑤ create database:create database name;

⑥ choose Database:use name;

⑦ Deleting a database: Drop the DB name; or table

⑧ view table: show tables; (must be executed after selecting the database (use name;)

⑨ View table structure: desc name;

⑩ Create table:create table name (column value type, column value type,...)

MySQL is not case-sensitive, but for string matching, there are lower (), upper () functions, to transform case, ********** problem **************

12 Data manipulation:

A) Check: SELECT * from Tbname;

1. Alias mechanism:
   1. Column alias: As or a space, automatically the column name of the column that precedes it as the column name for the output.
   2. Table aliases: Select e.ename,e.job from EMP e;
2. Example: Select Salary+bo

b) Increase:

1. INSERT INTO Tbname (Listing 1, listing 2 ...) values (parameter 1, parameter 2 ...);
2. INSERT into tbname values (parameter 1, parameter 2 ...);

c) by deleting:

1. Delete from Tbname;
2. Delete from tbname where condition, conditional delete

d) Change: Update tbname set to specify = value where

1. Update Tbname Set list = value;//Modify All
2. Update Tbname Set list = value;//Condition modification

13 Item Query:

A) comparison operator:> < = <> Not equal to NOTE: MySQL support! =, but other databases do not necessarily support

b) Logical operator: and OR note: MySQL support && | |, but does not represent other database support

c) Arithmetic operators:

1. *  +  -  /
2. such as: Select Ename,job,salary+ifnull (bonus,0) Sal, salary*12 yearsal from EMP;

D) Interval judgment: Where list between column value A and column value D;

e) Collection query: in exists efficiency issues

f) Link wildcard:

1. Percent percent (%) A percent semicolon represents 0, one or more characters.
2. Percent percent (%) A percent semicolon represents 0, one or more characters.

g) NULL Characteristics:

1. Any data with null addition result is NULL
2. General functions: Ifnull (column name, value) if the column name is empty, the value is value.oracle and SQL Server is NVL.
3. Ignored by group function.
4. The default is minimal in MySQL, so it is also smaller than negative, the largest in Oracle.
5. When compared, use is null, which is not NULL, for comparison.

h) The SQL TOP clause, which is used to get a record of the top N or x percent from the table. Note: All databases do not support the TOP clause. For example, MySQL supports the limit clause to get the number limits for records, and Oracle uses RowNum to get a limit on the number of records.

i) Sort by:

1. Format: SELECT column-list from table_name [WHERE condition] [ORDER by Column1, Column2,: ColumnN] [ASC | DESC];
2. For example: SELECT * from EMP where deptno=10 ORDER by empno ASC;

14 Sub-query:

A) sub-query as a precondition for the main query: for example: SELECT * from emp where salary > (select Salary from emp where ename = ' cz ');

b) Having and where difference: Having is a filter group and where is the filter record they have their differences.

1. When a group filter is used
2. Other cases in where
3. Must be used with GROUP by.
4. With GROUP by there is a having (it's just a filter condition)

c) When subqueries return multiple rows:

1. All modifies the matching relationship between the main query and the subquery.
2. such as: Select Ename,job,salary from emp where salary > All (select Salary from emp where ename= ' Lacus ');

15 correlated subqueries: When a subquery is queried, MySQL needs to establish a temporary table for the query results of the inner query statement. The outer query statement then queries the record in the temporary table. After the query is complete, MySQL needs to revoke these temporary tables. As a result, the speed of the subquery is affected somewhat. If the amount of data queried is large, the effect increases accordingly. In MySQL, you can use connection queries to replace subqueries. Such as:

16 Linked subqueries: You can use connection queries in MySQL to replace subqueries. Connection queries do not need to establish temporary tables, which are faster than subqueries.

17 Set of functions (ignoring null):

A) Features:

1. Ignore null
2. If you have a normal query in the statement, you must do the grouping process. Group BY (). The column processing of the general situation, after grouping the conditional query is having;
3. such as: Select Job,count (empno) count_emp from the EMP group by DEPTNO have count_emp>1;

b) count () counts, regardless of weight.

c) Sum ()

d) AVG ()

e) Min ()

f) Max ()

18 Formatted output:

A) Format (data, number of decimal digits) such as: Select Ename,job,format ((Salary+ifnull (bonus,0))/1.2) Sal from EMP;

b) Select Date_format (now (), '%y-%m-%d ');

19 General functions (do not ignore null):

A) Now (): Get the current detail time of the system

b) curdate ():

c) Current_timestamp ();//detail time

d) localtime ();//detail time

e) Format (data, number of decimal digits)

The management of database learning

Remote database Link Link: To remotely link to a database server, first ensure that the server's firewall allows remote linking, and then manage the remote link in the database running state. The configuration is as follows:

1. Determine that the firewall on the server does not block port 3306. MySQL default port is 3306, need to determine the firewall does not block 3306 port, otherwise remote is unable to connect via 3306 port to MySQL. If you specified a different port when you installed MySQL, open the port number that you specified for MySQL in the firewall. If you don't know how to set up a firewall on your server, check with your server administrator.

2, add to allow remote connection to MySQL users and authorized.

1) First log in to MySQL with the root account

In the Windows Host, click Start menu, Run, enter "cmd", go to the console, MySQL Bin directory, and enter the following command. 2) Create a remote login user and authorize

Format: Grant permission on the database name. Table name user @ Login host identified by "User password";

For example: Grant all privileges the discuz.* to [e-mail protected] ' 123.123.123.123 ' identified by ' 123456 ';

Explanation: Authorizing a TED user to use 123456 password via IP 123.123.123.123 discuz All tables on the database.

①all privileges: Indicates all permissions, of course you can choose Select Updata, etc.

②on discus.*: Represents the permissions on all tables of the discus database. *. * If all databases are all tables

③to Ted: To authorize a TED user

④@ "IP": indicates that IP access is allowed, of course "%" means any IP

⑤identified by ' 123456 ': Indicates that the user is logged in with password 123456.

3, flush privileges; Make the changes effective.

The basics of MySQL learning notes