Web Day15 Database Overview, mysql,sql statements, data query syntax DQL

Overview of the database management system (DBMS)

1. What is a DBMS: a repository of data

> Convenient Search

> Large amount of data can be stored

> Ensure the integrity and consistency of the data

> Safe and Reliable

2. Development of DBMS: Today's mainstream database is a relational database management system (RDBMS uses tables to store data)

3. Common Dbms:orcale, MySQL, SQL Server, DB2, Sybase

4. DBMS = hypervisor + multiple databases (db)

5. DB = multiple table (not just table, but no other components are introduced here)

6. The difference between the structure of the table (that is, the structure of the tables) and the records of tables (i.e. table records)!

7. Applications and DBMS: Applications enable DBMS to store data!

Install MySQL

1. When the MySQL installation is successful, the files are stored in two directories:

> D:\Program files\mysql\mysql Server 5.1:dbms hypervisor

> C:\ProgramData\MySQL\MySQL Server 5.1\data:dbms Database file (this directory will not be deleted when MySQL is uninstalled, you need to delete it manually)

2. mysql Important files

> D:\Program files\mysql\mysql Server 5.1\bin\mysql.exe: Client program to operate the server. However, you must ensure that the server is turned on to connect!

> D:\Program files\mysql\mysql Server 5.1\bin\mysqld.exe: Server program, you must start it before the client can connect to the server.

> D:\Program files\mysql\mysql Server 5.1\bin\my.ini: Server configuration file

3. C:\ProgramData\MySQL\MySQL Server5.1\data

> Each directory in this directory represents a database, such as a MySQL directory under that directory, then your DBMS has a db named MySQL.

> In a database directory there will be 0~n files with the extension frm, and each frm file represents a table. You do not open it with a text editor, it is read and written by the DBMS!

4. My.ini,mysql The most important configuration file

> Configure MySQL Port: Default is 3306, there is no need to modify it;

> Configure character encoding:

*[client] Under Configure client code: DEFAULT-CHARACTER-SET=GBK

*[MYSQLD] Under Configure server code: CHARACTER-SET-SERVER=UTF8

> Configure the maximum binary data size:

* under [mysqld] configuration: max_allowed_packet=8m

C:\ProgramData\MySQL\MySQL Server 5.5\data Directory: There is a directory there is a database!!!

In each database directory, there is a frm file that indicates there is a table!

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

Server and client operations

1. Turn on the server (you must ensure that MySQL is a Windows service): net start MySQL

> See if the process table exists: Mysqld.exe process (present)

2. Shut down the server (you must ensure that MySQL is a Windows service): net stop MySQL

> See if the process table exists: Mysqld.exe process (not present)

Client Login operation

Cmd

1. Login server: Mysql-uroot-p123-hlocalhost

>-U: followed by user name

>-P: following password

>-H: followed by IP

2. Exit the server: Exit or quit

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

SQL Overview

1. What is SQL: Structured Query Language (structuredquery Language).

2. sql function: The client uses SQL to manipulate the server.

> Start mysql.exe, after connecting to the server, you can use SQL to operate the server.

> will use Java programs to connect to the server in the future, and then use SQL to manipulate the server.

3. SQL standards (e.g. SQL99, standards established in 1999):

> by the International Organization for Standardization (ISO), the unified operation of the DBMS (for example, the same statement can be manipulated: MySQL, Oracle, etc.).

4. SQL dialect

> Some DBMS not only supports the SQL standard, but also has its own unique syntax, which is called a dialect! For example, the limit statement can be used only in MySQL

SQL syntax

1. SQL statements can be written in single or multiple lines, ending with semicolons

2. Use spaces and indents to enhance the readability of the statement

3. MySQL is not case-sensitive, it is recommended to use uppercase

SQL Statement Classification (* * * * *)

1. DDL (data definition Language): definition language, used to define database objects: libraries, tables, columns, etc.;

> Create, delete, modify: library, table Structure!!!

2. DML (Data manipulation Language): Database manipulation language, used to define the data of the databases;

> Add, Delete, change: Table record

3. DCL (Data Control Language): The language used to define access rights and security levels;

4. dql***** (data query Language): The Language for Querying records (data).

DDL: The structure of a database or table operation (* * * *)

DML: Updating the records of a table (add, delete, change) (* * * *)

DQL: Query for records of tables (* * * * * * * * * * * *)

DCL: Creation of users, and authorization!

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

Ddl

1. Database

* View all databases: Show DATABASES

* Toggle (Select to operate) database: Use database name

* Database creation: create databases [IF not EXISTS] mydb1 [Charset=utf8]

* Delete databases: drop database [IF EXISTS] Mydb1

* Modify Database encoding: ALTER db mydb1 CHARACTER SET UTF8

2. Data type (column type)

int: integral type

Double: a floating-point type, such as double (5,2), that represents up to 5 bits, which must have 2 decimal places, that is, a maximum value of 999.99;

Decimal: Floating point, use this type in the form of money, because there is no problem of lack of precision;

Char: fixed-length string type; char (255), the length of the data is less than the specified length, up to the specified length!

VARCHAR: variable-length string type; varchar (65535), Zhangsan

Text (CLOB): String type;

> Very Small

> Small

> In

> Large

BLOB: Byte type;

> Very Small

> Small

> In

> Large

Date: The format of the type: YYYY-MM-DD;

Time: The format of the type: HH:MM:SS

Timestamp: timestamp type;

3. Table

* Create a table:

CREATE table [IF not EXISTS] Table name (itcast_0001

Column list Type,

Column list Type,

...

Column list Type

);

* View all table names in the current database: show TABLES;

* View the creation statement for the specified table: Show create TABLE table name (learn);

* View table structure: DESC table name;

* Delete tables: drop table name;

* Modify table: prefix: ALTER table name

> Modified Add Columns:

ALTER Table name ADD (

Column list Type,

Column list Type,

...

);

> Modified column type (if data is already present in the modified column, the new type may affect existing data): ALTER table name MODIFY column list type;

> Modified column name: altertable table name change the original column name of the new column list type;

> Modified Delete column: Altertable table name drop column name;

> Modify Table Name: altertable The original table name RENAME to the new table name;

ALTER TABLE table name

Add (

Column list Type,

Column list Type,

...

Column list Type

)

ALTER TABLE table name

Modify Column list new type

ALTER TABLE table name

Rename to new name

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

DQL:

SELECT * FROM table name

DML (Data manipulation language, which is the operation of table records (add, delete, change)! )

1. Inserting data

* INSERT into table name (column name 1, column Name 2, ...) VALUES (column value 1, column value 2, ...);

> gives the name of the column to insert after the table name, and the other columns that are not specified are equivalent to inserting null values. So inserting a record always inserts a row, not half a row.

> give column values after values, and the order and number must correspond to the columns specified previously

* INSERT into table name values (column value 1, column value 2)

> does not give the column you want to insert, it means inserting all columns.

The number of > values must be the number of columns in the table.

The order of the > values must be the same order as the columns given when the table was created.

2. Modify the data

* UPDATE table Name set column Name 1 = column value 1, column Name 2 = column value 2, ... [WHERE condition]

* Conditions (optional):

The > Condition must be a Boolean value or expression: UPDATE t_person setgender= ' man ', Age=age+1where sid= ' 1 ';

> Operators: =,! =, <>, >, <, >=, <=, between ... And, in (...), is NULL, not, or, and

WHERE age >= and age <= 80

WHERE age between and 80

WHERE name= ' Zhangsan ' OR name= ' liSi '

WHERE name in (' Zhangsan ', ' liSi ')

WHERE is NULL, cannot use the equals sign

WHERE is not NULL

3. Delete data

* DELETE from table name [WHERE condition];

* TRUNCATE table name: TRUNCATE is a DDL statement that deletes the table first and then the CREATE table. and can't roll back!!!

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

All string types in the database must be single-cited and cannot use double-citation!

A single citation is also used for date types!

Insert All Columns

INSERT into Stu (

Number, name, age, gender

)

VALUES (

' itcast_0001 ', ' zhangsan ', ' Male '

);

Insert partial column, no specified Lieme think null value

INSERT into Stu (

Number, name

) Vaklues (

' itcast_0002 ', ' liSi '

)

Insert columns are not given, so the default is to insert all columns! The order of the values is the same as the columns when the table was created

INSERT into Stu VALUES (

' itcast_0003 ', ' Wangwu ', ' female '

);

UPDATE

UPDATE stu SET name= ' xxx ', age=102

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

DCL (understanding)

* A project to create a user! A project corresponding to the database only one!

* This user can only have access to this database, other databases you will not be able to operate!

1. Create a user

*create user username @ip address identified by ' password ';

> users can only log on to the specified IP address

*create user username @ '% ' identified by ' password ';

> Users can log on at any IP address

2. Authorization to the user

*grant permissions 1, ..., Permissions n on database. * To user name @ip address

> Permissions, users, databases

> Assign the user the specified permissions on the specified database

> For example, Grantcreate,alter,drop,insert,update,delete,select on mydb1.* to [email protected];

* Assign Create, alter, DROP, INSERT, UPDATE, delete, select permissions to the User1 user on the MYDB1 database

*grant all on database. * To user name @ip address;

> Assign all permissions on the specified database to the user

3. Revocation of authorization

*revoke permissions 1, ..., Permissions n on database. * FROM username @ip address;

> Revoke a specified user's specified permissions on the specified database

> For example, Revokecreate,alter,drop on mydb1.* from [email protected];

* Revoke User1 user's create, alter, and drop permissions on the MYDB1 database

4. View Permissions

*show GRANTS for user name @ip address

> View permissions for a specified user

5. Delete a user

*drop User Username @ip address

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

DQL--Data query language

Query does not modify database table records!

First, the basic query

1. Field (column) control 1) Query all columns

SELECT * from table name;

SELECT * from EMP;

-where "*" means querying all columns

2) query the specified column

SELECT column 1 [, Column 2, ... Column n] from table name;

SELECT empno, ename, Sal, comm from table name;

3) Complete duplicate records only once

When the multiline record in the query results is identical, only one row is displayed. This is rarely the case when querying all columns in general, but it can always be large when you query only one column (or columns)!

SELECT DISTINCT * | Column 1 [, Column 2, ... Column n] from table name;

SELECT DISTINCT sal from EMP;

--Keep a check on the payroll of the employee table if the same wages are present only once!

4) Column Operations

I number of columns can be added, minus, multiply, divide operations

SELECT sal*1.5 from EMP;

SELECT Sal+comm from EMP;

II string type can do continuous operation

SELECT CONCAT (' $ ', sal) from EMP;

III convert null value

Sometimes you need to convert null to other values, such as com+1000, if the COM column has a null value, then null+1000 is still null, and we want to put null at the current limit.

SELECT ifnull (comm, 0) +1000 from EMP;

--Ifnull (comm, 0): If there is a null value in the Comm, then it is considered as a calculation.

IV Aliases for columns

You may have noticed that when you use a column operation, the names of the columns in the result set that are queried are very bad, and we need to give the column names an individual name so that the column names in the result set display aliases.

SELECT ifnull (comm, 0) +1000 as bonus from EMP;

-Where as can be omitted

2. Condition control 1) Condition query

As with the update and DELETE statements described earlier, the SELECT statement can also use the WHERE clause to control the record.

*select Empno,ename,sal,comm from emp WHERE sal > 10000 and comm are not NULL;

*select empno,ename,sal from emp WHERE sal between 20000 and 30000;

*select empno,ename,job from EMP WHERE job in (' manager ', ' chairman ');

2) Fuzzy query

When you want to inquire about an employee with a name of two characters, you can use the fuzzy query

*select * from emp WHERE ename like ' Zhang _ ';

---fuzzy queries need to use operators: like, where _ matches an arbitrary character, note that only one character is matched instead of multiple.

--The above statement queries the employee with the surname Zhang, whose name consists of two characters.

*select * from emp WHERE ename like ' ___ '; /* Employees with names of 3 characters */

If we want to query the surname Zhang, the name of a few words can be the staff will use "%".

SELECT * from emp WHERE ename like ' Zhang% ';

--where% matches 0~n any character, so the above statement queries all employees surnamed Zhang.

SELECT * from emp WHERE ename '% ';

-Never assume that the above statement is an employee with an alpha character in the middle of the query name, because% matches 0~n characters, so employees whose names begin and end with a will also be queried.

SELECT * from emp WHERE ename like '% ';

--This condition equals and does not exist, but if the name is null the query does not come out!

Second, sort 1) ascending

SELECT * from WHERE the emp ORDER by Sal ASC;

-Sort by Sal, ascending!

-where ASC can be omitted

2) Descending

SELECT * from WHERE emp ORDER by Comm DESC;

-Sort by comm, descending!

--Where DESC cannot be omitted

3) Use multiple columns as sorting criteria

SELECT * from WHERE emp ORDER by Sal ASC, Comm DESC;

-With Sal Ascending, if Sal is the same, use Comm's descending

Three, aggregation function

An aggregate function is used to perform a vertical operation on a column.

1) COUNT

SELECT COUNT (*) from EMP;

--Calculates the number of rows for records that are not NULL for all columns in the EMP table

SELECT COUNT (comm) from EMP;

--Calculate the number of rows in the EMP table for records that are not NULL in the Comm column

2) MAX

SELECT MAX (SAL) from EMP;

-Query Maximum Wages

3) MIN

SELECT MIN (SAL) from EMP;

-Query minimum wage

4) SUM

SELECT SUM (SAL) from EMP;

-Query Payroll

5) AVG

SELECT AVG (SAL) from EMP;

-Query Average Salary

Four, group query

A group query is the grouping of records using a column, and then querying group information.

For example: View the number of records for all departments.

SELECT Deptno, COUNT (*) from the EMP GROUP by Deptno;

Use Deptno grouping to query the department number and the number of records per department

SELECT job, MAX (SAL) from the EMP GROUP by job;

--use job groupings to query the maximum wage for each job

Group conditions

The number of records per group is queried in departmental groupings. The condition is that the number of records is greater than 3

SELECT Deptno, COUNT (*) from EMP GROUP by DEPTNO have COUNT (*) > 3;

V. LIMIT clause (dialect)

Limit is used to limit the starting row of the query result and the total number of rows.

For example: Query start Behavior Line 5th, a total of 3 rows of records

SELECT * from EMP LIMIT 4, 3;

and 4 means start at line 5th, where 3 means to query 3 rows altogether. That is, 5th, 6, 7 rows of records.

SELECT * from emp limit 0, 5;

1. Number of records on one page: 10 rows

2. Enquiry on page 3rd

SELECT * from EMP limit 20, 10;

(Current page-1) * Number of records per page

(3-1) * 10

(17-1) * 8, 8

==============================

Select

From

where

GROUP BY

Having

ORDER BY

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

Web Day15 Database Overview, mysql,sql statements, data query syntax DQL