MySQL Basic knowledge Summary

MySQL Overview

MySQL is a relational database

Connect to MySQL database: Mysql-u root-p

What is SQL: Structured query statements

SQL classification:

DDL: Data Definition language

* Create,alter,drop ...

DML: Data Manipulation language

* Update,insert,delete

DCL: Data Control Language

* Grant,if.

DQL: Data Query Language

* Select

Database additions and deletions check and change

Create DATABASE: Syntax: CREATE DATABASE [character character set collate proofing rules]

To view the database:

View all databases: show databases;

View a database: show create database name;

Modify database: Syntax: ALTER DATABASE name character character Set collate proofing rules

Delete database: Syntax: drop database name;

Switch database: use database name;

View current Usage database: select databases ();

database table Operations

To create a table syntax:

CREATE TABLE Table name (

Field Name type (length) constraint,

Field Name type (length) constraint,

Field Name type (length) constraint

);

Data type:

Java Type: MySQL:

Byte/short/int/long Tinyint/smallint/int/bigint

String Char/varchar

* difference? Char is a fixed-length string, varchar variable-length string.

* CHAR (8) and varchar (8)

* If inserting a string hello inserts into char then insert hello. insert into varchar insert Hello

float float

Double Double

Boolean bit

Date Date/time/datetime/timestamp

* datetime and timestamp are date types that have both date and time.

Difference? DateTime requires the use of external incoming dates. If this value is not passed, it is null. Timestamp uses the system's current time as the default value for this value.

Text file

Binary file BLOB

Oralce using Clob/blob

* * * MYSQL in addition to the string type needs to be set length other types have default lengths ******

• Constraints:

Single-table constraints:

* PRIMARY KEY constraint: Primary key (default is the only non-empty)

* Unique constraint: Unique

* Non-empty constraint: NOT NULL

• Create a table:
• Select a database before creating a table: use a database;

CREATE TABLE Employee (

Eid int primary Key auto_increment,

ename varchar () NOT NULL,

Email varchar (+) Unique,

Birthday date,

Job varchar (20),

Resume text

);

"View of the table"

• See which tables are in the database:

* Show tables;

• To view the table structure:

* DESC table name;

"Delete of table"

• Deletion of the table:

drop table name;

"Modification of the table"

• To modify a table to add a column:

* ALTER TABLE Table name Add Column Name type ( length) constraints;

* ALTER TABLE employee ADD image varchar (50);

• To modify a table Delete column:

* ALTER TABLE Table Name Drop column name;

* ALTER TABLE employee drop job;

• To modify the type length and constraints of a table's columns:

* ALTER TABLE Table name Modify Column Name type ( length) constraints;

* ALTER TABLE employee modify image varchar (n) not null;

• Modify the column name of a table

* ALTER TABLE Table name change Old column name New Column name type ( length) constraints;

* ALTER TABLE employee change image eimage varchar (60);

• Modify Table Name

* Rename Table old table name to The name of the new table;

* Rename table employee to user;

• To modify the character set of a table:

* ALTER TABLE table name Character Set character set;

* ALTER TABLE user character set GBK;

Use SQL to manipulate the records of tables in the database (operations on records of tables for crud) ******

"Insert Record"

• Grammar

* Insert into     table name ( column name, column name,...) VALUES ( value 1, value 2,...); --- Insert the value of the specified column

* Insert into                    table Name VALUES ( value 1, value 2,...); --- insert values for all columns

• Precautions:

* the number of column names corresponds to the number of values.

* The type of the column corresponds to the type of the value. The location also has to correspond.

* the type of the column, if it is a string or date, when writing a value, use single quotation marks to enclose the value.

* The maximum length of the inserted value cannot exceed the maximum length of the column.

"Modify Record"

• Grammar:

* Update Table Set Column name = values, Column name = value [where condition];

• Precautions:

* the column name and value type are also consistent.

* the value cannot exceed the maximum length of the column.

* value is a string or date, you need to use single quotation marks.

• Practice:

* Modify Employee job for all records in the table for worker

* Update employee Set job= ' WORKER ';

* Modify the Employee table to change the mailbox name AAA to [email protected]

* Update employee Set email = ' [email protected] ' where ename = ' aaa ';

* Modify the Employee table to change the mailbox name BBB to [email protected] also modify Job to be HR

* Update employee Set email = ' [email protected] ', job= ' HR ' where ename= ' BBB ';

"Delete Record"

• Grammar:

* Delete from table [where condition];

• Precautions:

* Delete a row of records in a table, cannot delete a column value

* if no conditions are removed for all columns in the table.

• Practice:

* Delete ID is 8 Records of:

* Delete from employee where Eid = 8;

* Delete all records:

* Delete from employee;

• Delete all the records in the TRUNCATE table Table name and delete from Table difference?

* difference:

* TRUNCATE TABLE Delete a table record: delete the entire table. re-create a new table. Truncate belongs to the DDL.

* Delete from Delete a record of a table: delete a single article. Delete belongs to DML .

        * Transaction Management can only act on DML statement. If you use delete in one transaction Delete all records and you can retrieve them.

"Basic Query"

• Query statement:

* SELECT [DISTINCT] *| column name from table [where condition];

"Conditional Query"

• The where statement can be added after:

Keywords for the condition:

=, >, >=, <, <=, <>

You can use placeholders: _ and%: underscores to match one character,%: You can match any number of characters.

"Sort Query"

' ORDER by sorts the data. The default ascending order. (ASC ascending, desc descending)

"Aggregate function"

SUM () sum

' Count () statistics

Max () max value

MIN () Minimum value

AVG () Average

Group

GROUP BY

* Cross Connection :

* SELECT * from a B; ---obtained is the Cartesian product of two tables.

* Inner connection : INNER JOIN--inner can be omitted

* Explicit Intra-connection: SELECT * from A inner join B on condition;

* Implicit internal connection: SELECT * from a a Where condition;

* outer connection : Outer JOIN--outer can be omitted

* LEFT outer connection: OUTER JOIN--select * from A ieft outer join B on condition;

* Right outer connection: OUTER JOIN--select * from A outer join B on condition;

subqueries for multiple table queries

• One SQL statement query needs to rely on another query statement.

SELECT * FROM Customer c,orders o where c.cid = O.cid and C.cid in (SELECT CID from orders WHERE addr like ' Haidian% ');

MySQL Basic knowledge Summary