13th-August 1-March 7-MySQL basics

Apsaradb for MySQL basic bitsCN.com was launched on July 15, 13th

I. SQL

SQL: Structured Query Language

Structured Query Language

SQL industrial standard: by ANSI (ISO (international standard orgnation) Core Member)

SQL statements compiled according to industrial standards can run on any database.

Dialect: the SQL statements that can only run on a specific database are called dialects.

Industrial standards and dialects: Mandarin and Zhejiang dialect.

II. composition of the SQL language:

DDL: Data Definition Language

DML: Data Manipulation Language

DQL: Data Query Language

TPL: Transaction processing Language (Transaction Process Language)

DCL: Data Control Language

CCL: pointer control language

III. basic knowledge

Java-defined class ------------- table structure

Java object ------------------- records in the table

MySQL: non-windows environments are case sensitive.

Convention: all keywords are in lower case. Multiple words are separated.

IV. DDL

Purpose: define statements such as databases and table structures.

Keywords: create alter drop truncate (destroy)

Exercise:

1. DDL database operations

Create a database named mydb1.

Mysql> create database MYDB1;

View databases

Mysql> show databases;

View details about creating a database

Mysql> show create database MYDB1;

Create a mydb2 database using the gbk character set.

Mysql> create database MYDB2 characterset gbk;

Create a mydb3 database that uses the gbk character set and has verification rules.

Mysql> create database MYDB3 characterset gbk collate gbk_chinese_ci;

Delete the mydb3 database created earlier

Mysql> drop database MYDB3;

View the database on the server and change the character set of mydb2 to utf8;

Mysql> alter database MYDB2 character setutf8;

2. DDL table structure

Select a database

Mysql> use MYDB1;

Create an employee table

Mysql> create table EMPLOYEE (

IDint,

NAMEvarchar (0, 200 ),

GENDERvarchar (200 ),

BIRTHDAYdate,

ENTRY_DATEdate,

JOBvarchar (200 ),

SALARYfloat (8, 2 ),

RESUMEtext

);

Show all tables in the current database

Mysql> show tables;

View the table structure

Mysql> desc EMPLOYEE;

View table creation details

Mysql> show create table EMPLOYEE;

Add an image column to the employee table above.

Mysql> alter table EMPLOYEE add IMAGEblob;

Modify the job column to 60 in length.

Mysql> alter table EMPLOYEE modify JOBvarchar (60 );

Delete the image column.

Mysql> alter table EMPLOYEE drop IMAGE;

Change the table name to user.

Mysql> rename table EMPLOYEE to USER;

Modify the table character set to utf8

Mysql> alter table USER character setutf8;

Change column name to username

Mysql> alter table USER change NAMEUSERNAME varchar (100 );

V. DML: Data ManipulationLanguage

Used to insert, delete, and modify data into a database table.

Common keywords: INSERT UPDATEDELETE

In MySQL, string and date and time types must be enclosed by single quotation marks.

Special Value: null

Exercise:

Use the insert statement to insert information about three employees into the USER table.

Mysql> insert into USER (ID, USERNAME, GENDER, BIRTHDAY, ENTRY_DATE, JOB, SALARY, RESUME) values (1, 'wangdongxue ', 'female', '2017-09-08 ', '1970-02-17 ', 'CEOs', 2014, 'beautygirl ');

Mysql> insert into USER values (2, 'hangzhou', 'male', '2017-09-08 ', '2017-02-17', 'cto', 1990, 'Strong man ');

Mysql> insert into USER values (3, 'Display as ', 'male', '2017-09-08', '2017-02-17 ', 'UFOs', 1988, 'shuai Pu ');

View all records in the table

Mysql> select * from USER;

View all the codes of the database:

Mysql> show variables like 'character _ set % ';

Character_set_client: character set used by the client

Mysql> set character_set_client = gbk; notification server. the client uses the GBK character set.

Character_set_results: indicates the character set used by the displayed result set.

Mysql> setcharacter_set_results = gbk; the notification server. The character set used by the client to receive the result is gbk.

Change the salary of all employees to 5000 yuan.

Mysql> update USER set SALARY = 5000;

Change the employee's salary to 3000 yuan.

Mysql> update USER set SALARY = 3000 whereUSERNAME = ' ';

Change the salary of an employee whose name is 'hangzhou' to 4000 yuan, and change the job to CEO.

Mysql> update USER setSALARY = 4000, JOB = 'CEO 'where USERNAME = 'wangdong ';

Wang Dongxue's salary was increased by 1000 yuan based on the original salary.

Mysql> update USER set SALARY = SALARY + 1000 where USERNAME = 'wangdongxu ';

Delete the record named "display as" in the table.

Mysql> delete from USER where USERNAME = 'Display ';

Delete all records in the table. (DML statement)

Mysql> delete from USER; # delete one by one

Use truncate to delete records in a table. (DDL statement)

Mysql> truncate USER; # delete the entire table and recreate the table structure.

6. DQL data query language

Query

Keywords: select

Query the names and English scores of all students in the table. (Projection query)

Mysql> select NAME, ENGLISH from STUDENT;

Filter duplicate data in the table.

Mysql> select distinct ENGLISH fromSTUDENT;

Add 10 points of expertise to all students' mathematical scores.

Mysql> select NAME, MATH + 10 from STUDENT;

Calculate the total score of each student.

Mysql> select NAME, CHINESE + ENGLISH + MATHfrom STUDENT;

Use aliases to indicate student scores.

Mysql> select NAME as NAME, total score of CHINESE + ENGLISH + MATH from STUDENT;

Query the scores of students whose names are Wang Wu

Mysql> select * from STUDENT where NAME = 'Wang Wu ';

Query students whose English score is greater than 90

Mysql> select * from STUDENT whereENGLISH> 90;

Query all students whose total score is greater than 200

Mysql> select * from STUDENT where (CHINESE + ENGLISH + MATH)> 200;

The pattern: _ in fuzzy search matches one character % to match any character

Query the students whose English scores are between 80 and 90.

Mysql> select NAME, ENGLISH from STUDENTwhere ENGLISH between 80 and 90;

Students whose mathematical scores are, 90, and 91 are queried.

Mysql> select NAME, MATH from STUDENTwhere MATH in (89,90, 91 );

Query the scores of all students surnamed Li.

Mysql> select * from STUDENT where NAMElike 'Li % ';

The number of students with a query score greater than 80 in mathematics and a Chinese score greater than 80.

Mysql> select * from STUDENT whereMATH> 80 and CHINESE> 80;

Only records that meet the where condition are displayed. the logical operation result is true.

Sorts mathematical scores and outputs them.

Mysql> select NAME, MATH from STUDENTorder by MATH;

Output After sorting the total score, and then output in the order from high to low

Mysql> select NAME, CHINESE + MATH + ENGLISH total score from STUDENT order by total score desc;

Note: order is the keyword. The keyword is treated as a common identifier and is caused by '(not single quotation marks or reverse quotation marks ).

Sort the scores of mathematics students surnamed Li

Mysql> select NAME, MATH from STUDENTwhere NAME like 'Li %' order by MATH desc;

Paging query

Query the first three records

Limit M, N; M the index (starting from 0), N the number of records to be queried at a time

Mysql> select * from STUDENT limit 0, 3;

VII. Data integrity

Data integrity is designed to ensure that the data inserted into the data is correct, which prevents possible input errors.

1. domain integrity (column integrity)

The columns (fields) in the database table must comply with certain data types or constraints.

ID int (11): this field must be an integer and cannot exceed 11

NAME varchar (100) not null: Cannot be null

USERNAME varchar (100) unique: unique if Yes

2. Entity integrity (record integrity)

Specifies that a row (that is, each record) of the table is a unique entity in the table.

Primary key constraint: primary key

ID int primary key; ID is the primary key (it cannot be null and is unique)

Or

ID int,

Primary key (ID)

MySQL: the primary key can automatically grow. Auto_increment (the primary key value is managed and maintained by MySQL data ). Oracle does not automatically grow.

Create table T2 (

ID int primary keyauto_increment, // auto_increment is a dialect, not a standard, only available in MySQL.

NAME varchar (100)

);

3. integrity of reference (integrity of reference): foreign key (multiple tables)

One-to-many, many-to-many, one-to-one

Remote: remote

Collation meaning

Charset character set meaning

Manipulation operation

MySQL:

Timestamp: it is useless to assign a value. if you do not assign a value, the current value is assigned to the field (in the database)

Clob (storing text data) can store bolb (storing binary files.

Engine: InnoDB: indicates that transactions are supported.

1. domain integrity

Not null can be a null string, but it cannot be null.

Unique can be null. If yes, it must be unique.

2. must the field after order by appear after select, that is, after the projection field?Not Required

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

| ID | int (11) | YES | NULL |

| NAME | varchar (200) | YES | NULL |

| GENDER | varchar (200) | YES | NULL |

| BIRTHDAY | date | YES | NULL |

| Entry_date | date | YES | NULL |

| Job | varchar (200) | YES | NULL |

| Salary | float (8, 2) | YES | NULL |

| Resume | text | YES | NULL |

| IMAGE | blob | YES | NULL |

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

9 rows in set (0.01 sec)

Mysql> alter table employee modify JOBvarchar (60 );

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc employee;

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

| ID | int (11) | YES | NULL |

| NAME | varchar (200) | YES | NULL |

| GENDER | varchar (200) | YES | NULL |

| BIRTHDAY | date | YES | NULL |

| Entry_date | date | YES | NULL |

| JOB | varchar (60) | YES | NULL |

| Salary | float (8, 2) | YES | NULL |

| Resume | text | YES | NULL |

| IMAGE | blob | YES | NULL |

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

9 rows in set (0.01 sec)

Mysql> alter table employee

-> Drop image;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> desc employee;

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

| ID | int (11) | YES | NULL |

| NAME | varchar (200) | YES | NULL |

| GENDER | varchar (200) | YES | NULL |

| BIRTHDAY | date | YES | NULL |

| Entry_date | date | YES | NULL |

| JOB | varchar (60) | YES | NULL |

| Salary | float (8, 2) | YES | NULL |

| Resume | text | YES | NULL |

+ ------------ + -------------- + ------ + ----- + --------- + ------- +

8 rows in set (0.01 sec)

Mysql> alter table employee

-> To USER;

ERROR 1064 (42000): You have an error inyour SQL syntax; check the manual that

Corresponds to your MySQL server versionfor the right syntax to use near 'to US

ER 'at line 2

Mysql> rename table employee

-> To USER;

Query OK, 0 rows affected (0.15 sec)

Mysql> show tables;

+ ----------------- +

| Tables_in_mydb1 |

+ ----------------- +

| User |

+ ----------------- +

1 row in set (0.00 sec)

Mysql> show create table user;

+ ------- + -----------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

---- +

| Table | Create Table

|

+ ------- + -----------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

---- +

| User | create table 'user '(

'Id' int (11) default null,

'Name' varchar (200) default null,

'Gender' varchar (200) default null,

'Birthday' date default null,

'Entry _ date' date default null,

'Job' varchar (60) default null,

'Salary 'float (8, 2) default null,

'Sample' text

) ENGINE = InnoDB default charset = utf8 |

+ ------- + -----------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

---- +

1 row in set (0.00 sec)

Mysql> alter table user character setutf8;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> alter table user change nameusername varchar (100 );

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> insert into USER values (2, 'hangzhou', 'male', '2017-09-08 ', '2017-02-17', 'C

TO ', 100000, 'strong man ');

Query OK, 1 row affected (0.07 sec)

Mysql> insert into USER values (1, 'hangzhou', 'male', '2017-09-08 ', '2017-02-17'

, 'Cto', 100000, 'strong man ');

Query OK, 1 row affected (0.05 sec)

Mysql> insert into USER values (1, 'hangzhou', 'male', '2017-09-08 ', '2017-02-17'

, 'Cto', 100000, 'strong man ');

Query OK, 1 row affected (0.00 sec)

Mysql> insert into USER values (1, 'hangzhou', 'male', '2017-09-08 ', '2017-02-17'

, 'Cto', 100000, 'strong man ');

Query OK, 1 row affected (0.07 sec)

Mysql> select * from user;

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| ID | username | GENDER | BIRTHDAY | entry_date | JOB | salary | res

Ume |

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

4 rows in set (0.06 sec)

Mysql> insert into USER values (3, 'Display as ', 'male', '2017-09-08', '2017-02-17 ', 'UFO

', 100000, 'shuai Pu ');

ERROR 1366 (HY000): Incorrect string value: '/xB2/xE9/xCF/xD4/xB3/xC9' for colum

N 'username' at row 1

Mysql> show variables like 'character _ set % ';

+ -------------------------- + ----------------------------------------------------

----- +

| Variable_name | Value

|

+ -------------------------- + ----------------------------------------------------

----- +

| Character_set_client | utf8

|

| Character_set_connection | utf8

|

| Character_set_database | utf8

|

| Character_set_filesystem | binary

|

| Character_set_results | utf8

|

| Character_set_server | utf8

|

| Character_set_system | utf8

|

| Character_sets_dir | D:/Program Files/MySQL Server5.1/share/chars

Ets/|

+ -------------------------- + ----------------------------------------------------

----- +

8 rows in set (0.07 sec)

Mysql> set character_set_client = gbk;

Query OK, 0 rows affected (0.07 sec)

Mysql> select * from user;

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| ID | username | GENDER | BIRTHDAY | entry_date | JOB | salary | res

Ume |

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

4 rows in set (0.00 sec)

Mysql> insert into USER values (3, 'Tang Liuqing ', 'male', '2017-09-08', '2017-02-17 ', 'UFO

', 100000, 'shuai Pu ');

Query OK, 1 row affected (0.02 sec)

Mysql> select * from user;

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| ID | username | GENDER | BIRTHDAY | entry_date | JOB | salary | res

Ume |

+ ------ + ------------- + -------- + ------------ + ------ + ----------- + ----

-------- +

| 2 | wangdong | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 1 | wangdongxue | male | 1990-09-08 | 2014-02-17 | CTO | 100000.00 | str

Ong man |

| 3 |