Javase Basic-mysql Basic knowledge points

Mysql

MySQL is a relational database management system, is an open source software. With PHP and Apache can be better performance, but also can work on many platforms . Orcale is a database that creates multiple users, and MySQL is a user who creates multiple databases.

Installation considerations:

Port: 3306; Default User: Root; CharSet: set to GBK or utf-8;

Remote login can be enabled during installation to create anonymous users;

Can install the graphical interface, easy to operate

Type of MySQL column:

Numeric type:

smallint (): Accounted for 2 bytes

Int (): occupies 4 bytes, integer is a synonym for int, and is 4 bytes

BigInt (): Accounted for 8 bytes

float (x, y): occupies 4 bytes where x represents the decorated value altogether x bits, y represents a total of y digits after the decimal point

Double (x, y): 8 bytes x, y, and float usage

String type:

char (): fixed string length, sex char (2), different from Orcale in Sex char (3), MySQL neutron one child accounted for 2 bytes

　　varchar (): variable length string length, different from Orcale in VARCHAR2 () representing variable length strings

varchar (): more flexible to use, char is faster to process.

Date/Time Type:

Date: ' Yyyy-mm-dd ', or ' yyyy/mm/dd ', differs from Orcale in the format of dates ' January-December-2018 '

DateTime: ' Yyyy-mm-dd HH:MM:SS ' detailed date, including hours and seconds

Timestamp: ' Yyyy-mm-dd HH:MM:SS ' timestamp

A comparison of datetime and timestamp (understand, usually datetime):

Same point: datetime and timestamp columns have the same display format and are displayed in a fixed format of 19 characters, in the format: Yyyy-mm-dd HH:MM:SS

Different points: the range is different. DateTime format Displays and retrieves datetime values that are supported in the range ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ' timestamp values cannot be earlier than 1970 or later than 2037

storage is different. Datetime:8 byte storage, the actual format is the date format, independent of the time zone, timestamp:4 bytes stored in UTC format , and the conversion of the time zone, the current time zone is stored when the conversion, retrieval and then back to the current time zone.

Primary key self-increment: auto_increment, unlike orcale using sequence.

MySQL Syntax:

　1. Data definition language (Create,drop,alter and other statements)

Create Database School; Create a database

Use school; Specify the default database for operation, and the default database must be specified for data operation

CREATE table student (); Create a Student information table under the default database

Show columns from student; Displays the information for each column in a given table, showing the structure of the custom table

Show tables; Show all tables under the default database

show databases; Show all the Databases

The show create DATABASE//display is used for a given DB statement, or you can use the show create schema;

Show create table student; Displays the SQL statement for the table, displaying the specified table structure

drop table student; Delete Student Table

Drop Database School//Delete school databases

2. Data Query Language (SELECT statement)

SELECT * from student; Querying the information in the student table　　

SELECT * FROM student where sno > 5 and sex = ' male '

Select COUNT (*) from student; Total rows of data in the query table

DESC student; View the structure of a table

3. Data manipulation language (insert,delete,update, etc.) is for the data in the table

INSERT into student values (); Like Orcale, it's all about adding data to a table.

INSERT into student (Sno,sname,sex) VALUES (1, ' Zhang San ', ' Male ')

Delete from student where Sno = 1; Delete Row data in the student table that is numbered 1

Update student Set sname = ' Lisi ' where sno = 3; Overwrite the sname in the row data that is numbered 3 in the student table as ' Lisi '

4. Data Control Language (Grant,revoke,commit,rollback, etc.) is for a database or table

5. Change the table structure statement:

ALTER TABLE student add email varchar () unique; Add a list of emails to the student table, provided that the unique non-repetition

ALTER TABLE student Add (email varchar)///Also add a column of information to the student table

ALTER TABLE student Rename to Student2; Modify the table name, student becomes Student2

ALTER TABLE student Modify email varchar () not NULL; Modifying the properties of a column

ALTER TABLE student drop email; Delete Column

　6. Multi-table queries and foreign key associations:

　　Multi-table queries are based on foreign key associations between multiple tables. No foreign key association does not exist for multiple table queries

Create Master and PET tables, associated with mid and masterid foreign keys

ALTER TABLE PET Add constraint Fk_masterid foreign key (MasterID (foreign key)) references Master (mid) (Association key)

Unlike Orcale when added using constraints, the others are the same

ALTER TABLE pet drop FOREIGN key fk_masterid; Delete a FOREIGN Key association

Unlike Orcale removed using ALTER TABLE pet drop Fk_masterid;　　　

mysql> Select * from master m joins pet p on (m.mid = P.masterid);
+-----+----------+-----+-----+-----------+----------+
| mid | sname | age | pid | pname | masterid |
+--+---------------+-----+------+--------------+-------------+
| 1 | zhangsan | 1 | luckey | 1 |
| 1 | zhangsan | 2 | xiaoxiong | 1 |
| 1 | zhangsan | 18 | 4 |       Luckey | 1 |
| 2 | Lisi | 18 |    3 |       Uzi | 2 |
| 2 |  Lisi | 18 | 5 |       Xiaoxiong | 2 |
| 3 | wangwu | 18 | 6 |        Uzi | 3 |
| 4 | wangwu2 | 7 | luckey | 4 |
| 4 | wangwu2 | 8 | xiaoxiong | 4 |
| 4 | wangwu2 | 18 | 9 |      Uzi | 4 |
+--+--------------+-------+-----+--------------+-------------+

7. Paging statements:

SELECT * FROM table student limit (start-1) *limit, limit; Where start is page number, limit is the number of bars displayed per page

In MySQL, take help_category as an example

SELECT * from Help_category limit 11, 5; Represents 5 data starting from line 12th　

mysql> SELECT * from help_category limit 11,5;
+------------------+-----------------------------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-----------------------------------------------+--------------------+-----+
| 12 | Encryption Functions | | |
| 13 | LineString Properties | | |
| 14 | Miscellaneous Functions | | |
| 15 | Logical Operators | | |
| 16 | Functions and Modifiers for use with GROUP by | | |
+------------------+-----------------------------------------------+--------------------+-----+

8. Import and Export

Method 1 (graphical interface operation): Directly to the table operation, select database----> Right click to select Dump SQL file-----> select Address to save the SQL file; That you want to import in database----> Right-click Select Run SQL file----> import the appropriate SQL file.

Method 2 (Command-line operation): Operation under DOS command

Export: Mysqldump-uroot-proot database name > directory SQL file (D:\school.sql)//full database everywhere

Mysqldump-uroot-proot database Name Table name > directory SQL File//Only one table everywhere

Import: Mysql-root-proot database name < directory SQL File//general use of this

SQL file in MySQL >source directory

　　　　　　

　　　　　　

　　

　　　　　　　

　　　　　　

　　　　

　　

　　

Javase Basic-mysql Basic knowledge points