MySQL use notes (ii)

first, start and close the MySQL service

windows under
Start
(Method 1) Administrator rights into CMD, execute net start MySQL
(Method 2) Administrator rights into CMD, execute mysqld-uroot--console
(Method 3) in the computer--service--mysql, through the way of the graphical interface to start
Close
(Method 1) Administrator rights into CMD, execute net stop MySQL
(Method 2) Administrator rights into CMD, execute mysqladmin-uroot shutdown
(Method 3) on the computer--service--mysql, closed via graphical interface

Linux under
Start
(Method 1) Execute CD MySQL executable directory under terminal, such as Cd/usr/bin, execute
./mysqld_safe &
(Method 2) Perform service MySQL start under terminal (this method requires a RPM-style installation of MySQL line)

Close
(Method 1) Perform mysqladmin-uroot under terminal shutdown
(Method 2) Service stop MySQL (this method requires a RPM-mode installation of MySQL only)

Common Exceptions
Startup time failed, prompt [ERROR] InnoDB: .\ibdata1 can‘t be opened in read-write mode . Solution:
(1) Open Task Manager, terminate Mysqld.exe
(2) Open the MySQL installation directory data folder, delete Ib_logfile0, Ib_logfile1
(3) Restart MySQL

Second, connect the database

(Method 1) Connect via MySQL Workbench graphical interface
(Method 2) under the command line, execute the mysql-u user name-p password-h host IP, as
mysql -u root -p, enter the password after entering, do not write-H indicates the use of the native IP address.

III. Classification of SQL statements

MySQL supports standard SQL statements and has its own extension statements. SQL statements can be divided into three categories:
(1) A DDL (data definition Language) Data definition statement that defines different data segments, databases, tables, columns, indexes, and other database objects. The commonly used statement keywords include create, drop, alter, and so on.
(2) DML (Data manipulation Language)
Data manipulation statements for adding, deleting, updating, and querying database records, and for checking data integrity. Commonly used statement keywords include insert, delete, UPDATE, select.
(3) DCL (Data Control Language)
A data control statement that controls the level of permission and access to different data segments directly. These statements define the database, table, field, user's access rights, and security level, and the main statement keyword contains grant, REVOKE, and so on.

DML statements operate only on data within a database table, and DDL statements can modify the table definition and structure.
At the MySQL command line, the statement ; ends with each statement followed, followed by a ; carriage return, before it is executed.

IV. DDL Statement Operations

(4.1) Create a database
create database dbname;
(4.2) Display the current database
show databases;
When you install MySQL, 4 databases are created automatically:
(a) Information_schema: Some database object information in the system, such as user table information, column information, permission information, character set information, partition information, is mainly saved.
(b) Cluster: Storage of the system's cluster information
(c) MySQL: Stored User rights information for the system
(d) Test: A testing database that is automatically created by the system, which can be used by any user

(4.3) Select database
use dbname;
The operation after that is done under the selected database. For example, show tables; all tables under this database are displayed.

(4.4) View current information
statusThat displays information about the current connection, such as the database used, the user, the character set of the database, and so on.

(4.5) Deleting a database
drop database dbname;
In MySQL, the result of the drop statement shows ' 0 rows affected '

(4.6) CREATE table  
create Table Tbname ( 
Column1_name column1_constraints, 
column2_name column2_constraints, 
...  

     For example, create an EMP table that includes ename (name), HireDate (hire date), Sal (Salary), Deptno department number three fields, with the field type varchar (10) , Date, decimal (10,2), int (2)  
create table EMP ( 
ename varchar (ten),  
HireDate date, 
Sal Decimal (10, 2),  
Deptno int (2))  


(4.6) View table definition
desc tbname;, desc can view the table definition, but the output information is not comprehensive enough to show create table tbname \G; view more detailed information, such as the storage engine, character set, etc. The "\g" option is to allow records to be arranged vertically in a field to better display longer-content records.

(4.7) Delete a table
drop table tbname;

(4.8) Modify the table
In most cases, the modification of the table structure is done through the ALTER TABLE statement, and the commonly used operations include:
<1> modifying fields
alter table tbname modify column_name column_definition [first|after col_name]
such as ALTER TABLE EMP modify ENAME varchar (20); #将enmae列类型修改为varchar (20)
<2> Add fields
alter table tbname add column_name column_definition [first|after col_name]
For example, ALTER TABLE EMP add age int (3) #在emp表中添加列age
<3> Delete Fields
alter table tbname drop column_name
For example, ALTER TABLE EMP drop age;
<4> Rename Field
alter table tbname change old_col_name new col_name [new_col_definition] [first|after col_name]#[] indicates that the content it contains is optional
For example, ALTER TABLE EMP change age Age1 Int (4), #将age字段改名为age1, and modify the field type to int (4)
Both change and modify can modify the definition of a table, except that the change is required to write two times, but changes can modify the column name, and modify cannot.
<5> Modifying the order of field permutations
The Add/change/modify action statement above has the [first|after col_name] option to modify the position of the field in the table, the Add new field is added by default to the last position of the table, and the change/modify default does not change the position of the field.
For example, ALTER TABLE EMP add birth date after ename; #将新增的birth字段加在ename之后
ALTER TABLE EMP Modify age int (3) first; #将字段age放在最前面
<6> Change table name
alter table tbname rename new_name

v. DML statements

(5.1) Insert record &NBSP;
& nbsp The
can also not specify field names, but values should follow the order of the fields in the table, including fields with controllable fields, non-empty but with default values, and self-increment fields, which can be used without appearing in the field list after the insert, and only the value of the corresponding field name is written after the values.  
    insert statement can insert multiple records at once, in the format:  

INSERT into Tbname (field1, Field2, ... fieldn) values< Span class= "Apple-converted-space" >&NBSP;
(Val11, val12, ... val1n),  
(Val21, val22, ... val2n), &NBSP;
.... &NBSP;
(VALK1, Valk2, ... valkn)

(5.2) Update record &NBSP;
 
     in MySQL, the update command can update data in multiple tables at the same time, in the following format:  
update T1, T2, ... tn set T1.FIELD1=EXPR1, ... TN.FIELDN=EXPRN [Where Condition]  
such as Udpate emp A, Dept B set A.sal =b.sal*b.deptno, b.deptname=a.ename where A.deptno=b.deptno; #emp表 (recorded as a) and Dept table (recorded as B), a contains employee information, including the employee's salary and the department number in which it resides ; b. Store departmental information, including department number and department name. For an employee information record of a, through its department number DEPTNO, from B to find the deptno corresponding deptname ...

(5.3) Deleting records
If the record is no longer needed, you can delete it with the delete command
delete from tbname [where condition]
In MySQL, you can delete data from more than one table at a time, in the following format:
delete t1,t2....tn from t1,t2...tn [where condition]If the name of the table after the from is the alias, the delete is followed by the corresponding alias, otherwise a syntax error is prompted.
Whether single table or multiple tables, if you do not add a where condition, all records of the table are deleted.

(5.4) Inquiry record
select field1, field2,....fieldn from tbname [where condition]
<1> General Enquiry&NBSP;
For example: &NBSP;
Select Age, Deptno from emp where sal > deptno = 1; &NBSP;
Select * from Emp; <2> query non-repeating records &NBSP;
Sometimes you need to remove duplicate records from the table and display them. Can be implemented with the DISTNICT keyword: &NBSP;
Select distinct deptno from Emp;&NBSP;
<3> Sorting and restricting &NBSP;
If you need to return a result set sorted by a field, you can use the order by implementation: &NBSP;
 
Field2 [ DESC|ASC] FIELDN[DESC|ASC] &NBSP;
Collation default ASC (Ascending sort)

(5.4) Polymerization
Summarize the data, group by
select [field1, field2 ... fieldn] fun_name from tbname [where where_condition] 
[group by field1, field2....fieldn] [with rollup] [having where_condition]

Fun_name represents the aggregation operation to be done, that is, the aggregation function, commonly used are sum,count,max,min,avg, etc.
The group BY keyword indicates the fields to be aggregated by category, such as the number of employees to be classified by department, and the department should be written behind GROUP by
With rollup optional syntax, indicating whether the aggregated results of the classification are re-aggregated
Having keyword means filtering the results of the classification after the condition
the difference between having and where : Having is to filter the results of the aggregation, and where is to filter the records before aggregation, and if the logic allows, wherever possible, use where to filter records, thus reducing the result set and improving efficiency.

For example: &NBSP;
Select COUNT (1) from EMP; statistics company number of employees &NBSP;
Select Deptno, COUNT (1) from the EMP group by DEPTNO; count the numbers in each department &NBSP;
Select Deptno, COUNT (1) from the EMP group by DEPTNO with rollup; statistics of the number of departments, final summary &NBSP;
Select Deptno, COUNT (1) from the EMP group by DEPTNO have count (1) > 1; Statistics department is more than 1 departments to And the number of the department &NBSP;
Select Max (val), Min (val), Sum (val) from EMP; statistic the largest, smallest, Total Salary value

(5.5) Table connection &NBSP;
     Table joins are required when you need to display fields from multiple tables at the same time. Table joins are divided into inner joins and outer joins , the main difference is that the inner join selects only two tables in the matching records, the outer joins will select other mismatched records, commonly used within the connection. &NBSP;
For example: &NBSP;
Connect Select ename, Deptname from EMP, dept where emp.deptno = Dept.deptno; &NBSP;
Outer joins are divided into left and right connections, and left joins are records that contain all the tables on the left, even if some records do not match them in the table on the right; &NBSP;
Right join refers to the records in all the right tables, even if there are no records in the left table that match them. &NBSP;
For example: &NBSP;
Select ename, Deptname from the EMP left joins dept on Emp.deptno=dept.deptno;emp Table, the connected results contain all the ename in the EMP table, Even if the deptno of the ename corresponding employee does not exist in the Dept table.

(5.6) subquery &NBSP;
     in some cases, When a query is made, the required condition is the result of another select, which requires a subquery. The keywords used for subqueries mainly include in, not in, =,! =, exists, not exists, etc. &NBSP;
For example: &NBSP;
Select * from emp where Deptno in (select Deptno from dept); finds only those records in Dept's EMP that deptno exist in the Deptno table. &NBSP;
     If the subquery record number is unique, you can also use = instead of in. &NBSP;
     In some cases, subqueries can be converted to table joins, for example: &NBSP;
Select * from emp where deptno in (select Deptno from dept); &NBSP;
after conversion to table connection, for select emp.* from EMP, dept where Emp.deptno=dept.deptno;

(5.7) record joint
If you need to query the data of two tables in accordance with a certain query criteria, the results are merged together to display, you need to use the Union and the union ALL keyword to achieve such a function:
select * from t1 union|union all 
select * from t2 union|union all 
.... 
select * from tn; 

The difference between Union and union all is that union all merges the result set directly, and union is distinct the result of union all once, eliminating the result of duplicate records.

For example, a collection of department numbers in the EMP and dept tables is displayed:
Select Deptno from EMP UNION ALL select Deptno from dept, which shows all deptno that appear in the EMP table and Deptno table, removing duplicates: Select Deptno from EMP Union Select Deptno from dept;

Six, DCL statements

The DCL statement is primarily used by DBAs to manage the use of objects in the system, and is seldom used by developers in general.
For example, create a database user Zl with Select/insert permissions on all tables in the Sakila database:
Mysql-u root-p
Grant Select,insert on Sakila.*-' ZL ' @ ' localhost ' identified by ' 123 '; At this time, the database Sakila a new user Zl, the password is 123, and has a select, Insert Permissions.
Due to changes in permissions, you need to modify the permissions of Zl, retract the insert, only select operations on the data:
Mysql-u root-p
Revoke insert on Sakila.* from ' Zl ' @ ' localhost ';

vii. querying meta-data information

    msql 5.5, a new database Information_schema was provided to record metadata information in MySQL. Metadata refers to data, such as table name, column name, column type, index name, and various property names of tables. The database is a virtual database, there are no related directories and files physically, the table displayed by show tables is not actually present, all the views. &NBSP;
     Some of the more commonly used views: &NBSP;
Schemata: Provides information about all the databases in the current MySQL example, the results of show databases from the table; &NBSP;
Tables: Provides information about the tables in the database, including views, detailing which schema, table type, table engine, creation time, and so on, a table belongs to. Show tables from SchemaName results from the table; &NBSP;
Colunms: Provides column information in the table. Detailed description of all columns of a table and information about each column, show columns from schemaname.tablename results from the table; &NBSP;
Statistics: This table provides information about table indexes

Use database meta information for some operations, such as deleting all tables prefixed with TMP under database test1:
Select Concat (' drop table test1. ', table_name, '; ') from tables where table_schema= ' test1 ' and table_name like ' tmp% ';

MySQL use notes (ii)