My MySQL learning record

---restore content starts---

Mysql

Concept: is a collection of tables, is a large classification

Installation

Installation File Download path: (Leoning blog)

http://www.cnblogs.com/SmartNing/p/5178616.html

1. Configure Video

The first step: Download the green package, do not install, placed in the specified directory (if placed in The server directory of the C drive ).

1, the mysql-5.5.22.rar decompression to the C - disk server directory, if you want to extract to other directories you need to modify ( modify ini file )

C:\server\mysql-5.5.22\my.ini in the

Basedir= "c:/server/mysql-5.5.22/"(file storage path)

Datadir= "c:/server/mysql-5.5.22/data/"

to the appropriate path.

account password for db instance:root, 888

then create the mysql service under cmd

to create a MYSQL5 service:

CD c:\server\mysql-5.5.22\bin\

Mysqld--install MySQL5--defaults-file=c:\server\mysql-5.5.22\my.ini

Note: If the service Catalog creation error occurs, you need to first delete the service.

the command is:SC delete mysql5// mysql here is the service name you want to delete

Restart the machine after deletion, and then create the mysql service under the re-enter doc

basic data types for MySQL

1.10 binary numbers. int (integer) numeric (decimal)

'-' indicates a negative value

. delimiter for decimal point floating point number

Example: Numeric (10,2) 10 is a character, 2 is 2 digits after the decimal point.

2.16 binary Numbers

3. String char fixed number of values

Varchat the number is not fixed but there is a limit

4. Dates and times date ()

DateTime ()

For example: 2001-12-12, 12:12:12

5.null:null is a special value that indicates that there is no value, and that it is not the same as "0" and an empty string.

common mysql commands

first go to mysql directory:C:\server\mysql-5.5.22\bin

Login:mysql-u root-p 888

1. Display all existing databases on the server:show databases

2. Create DATABASE syntax in the server:

Create databases db_name character Set UTF8 collate utf8_general_ci

2. How to switch to the specified database:use db_name

3. Delete databases: Drop databasedb_name

4. Build Table statement: CREATE table ' News '. ' topic ' (' ID ' INT NOT NULL auto_increment, ' topicname ' VARCHAR () ' is not NULL, PRIMARY KEY (' id ')

) Charset=utf8;

Note: UTF8 (the International Standard encoding format)

5. Insert data: Insert Tb_name values (); means inserting data sequentially

Insert Tb_name (field 1, Field 2,......) VALUES (' Value of field 1 ', ' Value of field 2 ',......);

Note: Numeric type without quotation marks

6. Modify Field command:

Upadate tb_name Set field 1= value, field 2= value where condition

6. Enquiry:

Show All information:

Example: SELECT * from Ming.adress_list;

Projection fields:

Cases SELECT name,position from Ming.adress_list;

To Alias a field:

Example: SELECT name ' name ', tel ' phone ' from ming.adress_list;

Filter criteria Query:

Example: SELECT * from ming.adress_list where name= ' Tony Leung ';

% wildcard for fuzzy queries:

Example: SELECT * FROM Ming.adress_list where tel like ' 136% ';

SELECT * FROM Ming.adress_list where tel like '%234 ';

Usage of and:

Example: SELECT * FROM Ming.adress_list where tel like ' 136% ' and the name like ' beam% ';

Use of OR:

Example: SELECT * FROM Ming.adress_list where tel like ' 136% ' or the name like ' Zhang% ';

Use of not:

Example: SELECT * FROM Ming.adress_list where tel isn't like ' 136% ';

(3) Aggregation function:

1.count function:

Count the number of query records (only the results of the statistics are displayed, no specific information is displayed).

Select COUNT (*) from Tb_name;

2.sum function: Sum

Select sum (field name) from Tb_name;

3.avg function: Averaging

Select AVG (field name) from Tb_name;

4.max function: Maximum value

Select max (field name) from Tb_name;

5.min function: Minimum value

Select min (field name) from Tb_name;

sort order by;

Example: SELECT * from Ming.adress_list ORDER by ID ASC;

SELECT * from Ming.adress_list ORDER by id DESC;

SELECT * from Ming.adress_list ORDER by age ID desc;

ASC: Ascending; desc: descending;

(3) Group query:Group by+ keyword

Query items are aggregate functions and grouped columns, and grouped columns can have more than one.

Query with filter criteria: "Filter the grouped results using the having statement

At the same time meet 2 price adjustment (both, and)

Group BY (A, B) having count (*) >1

(4) Connection query:

INNER join: Gets the record that has a connection matching relationship in two tables.

How to use: SELECT ... INNER JOIN ... The ON statement is as follows

SELECT Article.aid,article.title,user.username from article INNER JOIN user on article.uid = User.uid

                                                           ,         &NB Sp                              ↑    & nbsp                  ↑              ↑& nbsp             ↑

Main Table from table primary table. Foreign key from table. Primary key

equals where statement SELECT article.aid,article.title,user.username from article,user WHERE article.uid = User.uid

Subquery: Two queries appear in a query;

Example: SELECT * from adress_list where primary key = or in (the select Waijian from users where condition);

Use sub-query as projection;

Example: Now query all the students Sno, CNO and rank column. Between ....  And ... Within a range.

Select Sno,cno, (select rank from grade where score. degree between low and UPP) ' rank ' from score;

Limit statement: select * FROM table limit m,n

where M is starting from 0, representing the first record, n means starting from section m+1 and taking N.

SELECT * FROM tablename limit 2,4

Note: If you use limit in a subquery, you cannot use the not in,in to <> not equal;

Example: Query score a record of a non-highest score for a student who has chosen to learn more than one course.

Select Sno,count (*) from score where Sno <> (select Sno from score order BY degree desc-Limit 1) Group by Sno have COUNT (*) >1;

Note: The subtraction of two dates in a MySQL statement equals the number of years, that is, the entire birthday-another birthday = number of years;

Example: 2017-12-27--2016-1-23 = 1;

Example: The SNO, sname and Sbirthday of all students who were born in the same year were queried and studied for 108 students.

Select Sno,sname,sbirthday from student where sbirthday-(select Sbirthday from student where sno= ' 108 ') = 0;

Use sub-query as projection;

Example: Now query all the students Sno, CNO and rank column. Between ....  And ... Within a range.

Select Sno,cno, (select rank from grade where score. degree between low and UPP) ' rank ' from score;

DISTINCT: Remove the duplicate results.

My MySQL learning record