【 Teaching Content 】

1. Introduction to common databases

2. Install and configure the Mysql database

3.SQL database creation statement

4.SQL data table structure operation statement

5.SQL data table record operation statement

6. Backup and restore Mysql database.

7. Multi-table design (foreign key constraint)

Select * from multiple tables (cartesian product)

【 Teaching Summary 】

[Stage 1]

Objective: Common database introduction

What is a database?

Is a file system, through the standard SQL language operation file system data —- used to store software system data

What is a relational database? Save the relational data model (see figure below)

Oracle, a specialized database vendor, acquired BEA, SUN, MySQL ——- charging large databases for any system and any platform

MySQL is an early open source free database product. LAMP combination Linux + Apache + MySQL + PHP is completely open source and free. Since MySQL was acquired by Oracle, paid versions have appeared since 6.0

DB2 IBM database products large toll database websphere server used together

SYBASE medium scale database charging PowerDesigner database modeling tool

SQL Server Microsoft database product charge medium scale database, operating system requirements are Windows and.net together to use

Java developers mainly use MySQL, Oracle, and DB2 databases

【 School Effect 】

Be able to figure out what a database is.

Be able to figure out what a relational database is.

【 knowledge point transition 】

Mysql database installation and configuration.

[Stage 2]

Mysql database installation and configuration

MySQL > install MySQL

1, the unloading

In the mysql installation directory my.ini

Datadir =”C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/

  1. Uninstall MySQL in control Panel

  2. Delete the mysql installation directory

  3. Delete the MySQL data file directory

2, installation,

Select custom installation

MySQL Server default location c: program Files \ MySQL directory

C:\Documents and Settings\All Users\Application Data\MySQL

Configure mysql after the installation

  1. Configure the default mysql character set

Default latin1 is equivalent to ISO-8859-1 instead of UTF8

  1. Include Bin Directory in Window Path Select the mysql/ Bin Directory configuration environment variable Path —-

  2. Enter the password of super administrator root

Start the CMD window. Enter mysql -u root -p and press enter 123 ====. The mysql> Installation succeeded is displayed

3. Reset the root password

  1. Run services. MSC to stop the mysql server

  2. Enter mysqld –skip-grant-tables on CMD to start the server without moving the cursor (do not close the window)

  3. Enter mysql -u root -p without password

use mysql;

update user set password=password(‘abc’) WHERE User=’root’;

  1. Close both CMD Windows in task Manager to end the mysqld process

  2. Restart the mysql service on the service Management page

The password is changed.

MySQL server internal storage result:

A single database server can create multiple databases

A database can create multiple tables

Each data table is used to hold data records

[Learning Effect]

Know how to install and configure mysql database.

【 knowledge point transition 】

Next, learn about database creation in SQL statements.

[Stage 3]

[requirements: want to let the database in accordance with our requirements to operate data, how to do? Analysis: need to tell our requirements to the database. How to tell? Must say the database understand the language. SQL language.

What about those operations? CRUD. Check the API. 】

Target: Operating the database part of a SQL statement

SQL statement Structured query statement

Features: Non-procedural one SQL statement one execution result

In order to use SQL to write complex programs, various database vendors to enhance SQL, SQL Server TSQL, Oracle PLSQL

Because SQL statement is a standardized general operation of the database statement, so as long as the learned SQL statement, then learn to operate the mainstream relational database market.

SQL statement classification is classified by function (definition, manipulation, control, query)

DDL data definition language, defines tables, libraries, views

DML adds, modifies, and deletes data table records

DCL authorization, transaction control, conditional judgment

DQL (not W3C taxonomy) data tables record queries

  • Know the four categories of SQL statements, can identify which category SQL language belongs to

SQL statement learning process: Database operation statement —– Data table structure operation statement —- Data table record operation statement

Database operation statement:

Create database creates a separate database for each software system:

Syntax: create database Database name; (Create database using database server default character set)

Create database Database name character set Collate Comparison rule.

Create a database named mydb1. create database mydb1;

Create a MYDB2 database using the UTF8 character set. create database mydb2 character set utf8;

Create a myDB3 database with utF8 character set and collation rules. create database mydb3 character set utf8 collate utf8_bin;

Add: each time you create a database, create a folder in the data storage directory. Each folder contains db.opt to store the default character set and collation rules

Datadir =”C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/

2, query database

show databases; —– View all databases

Show create database Database name; —— View the data code set

Delete the database

Syntax: drop database Specifies the database name.

Show databases;

Show create database myDB2;

Drop database mydb1; drop database mydb1;

4. Modify the database code set

Alter DATABASE DATABASE name character set COLLate comparison rule;

Alter myDB2 character set to GBK; alter database mydb2 character set gbk;

To switch the current database: use the database name

To view the database in use: select database();

Note: All database-related action statements are DDL statements

[Learning Effect]

Master how to create database, delete database, view database, modify database character encoding set.

【 knowledge point transition 】

Next, learn about the data table structure manipulation statements.

[Stage 4]

[Target: table structure operation statement]

Data table structure SQL statement

1. Create a data table

Syntax: create table name (column name type (length), column name type (length)…) ;

A data table can have many columns, each of which has a type and length

  • No character set is specified when the table is created. The database default character set is used
  • You must specify the operation database using the use DB syntax before creating a table

Create database day10;

Switch to day10 database use day10;

Such as:

User {

id int

name string

password string

birthday date

}

A Java class corresponds to a data table in the database, and a Java object corresponds to a data record in the data table

Common data types of MySQL

Java String char —– Mysql String char varchar

  • For example: char(8) saves lisi, because lisi has only four characters, so it will add four Spaces to make eight characters in char(8). If there is a varchar(8), it will automatically change the length according to the contents stored in it

Byte short int long float double —– The value types in mysql are TINYINT, SMALLINT, int, BIGINT, float, double

Java Boolean —- mysql logical bit Holds a value 0 or 1

Date —– mysql Date type Date (only Date) time(only time) datetime timestamp(both Date and time)

  • Datetime is the same as timestamp, but timestamp can be updated automatically in the database (current time).

Java big data type inputStream binary file Reader text file ——- mysql Big data type BLOb (storing large binary data) text(storing large text files)

  • Tinyblob tinytext 255 bytes blob text 64KB mediumblob mediumtext 16MB longblob longtext 4GB

Such as:

User {

id int —— mysql int

name string —— mysql varchar

password string —– mysql varchar

birthday date —– mysql date

}

The employee forms a statement

create table employee (

id int,

name varchar(20),

gender varchar(20),

birthday date,

entry_date date,

job varchar(30),

salary double,

resume longtext

);

Select * from desc;

*** When creating a table, only string types must be written to length, while all other types have default lengths

2. Constraints on creating a single table

Constraints are used to ensure data validity and integrity

Primary key: a field that uniquely distinguishes other information records can be null.

Unique: The value of this field cannot be repeated as null

  • A table can have many unique constraints, and only one (or two) can be used as the primary key constraint not NULL: this field cannot be null

create table employee2 (

id int primary key auto_increment,

name varchar(20) unique not null,

gender varchar(20) not null,

birthday date not null,

entry_date date not null,

job varchar(30) not null,

salary double not null,

resume longtext

);

  • If the primary key constraint type is int bigINT, add auto_INCREMENT automatically

3. Data table structure modification

  1. Alter table name add column name type (length) constraint;

  2. Alter table table name modify column name type (length) constraint;

  3. Alter table table name change old column name new column name Type (length) constraint;

  4. Alter table table_name drop table_name;

  5. Rename table old table name to new table name;

  • Alter table student character set utf8; s

Add an image column to the basic employee table above. —- alter table employee add image varchar(100) ;

Modify the job column to be 60 in length. —-alter table employee modify job varchar(60) not null;

Delete the gender column. —-alter table employee drop gender ;

Change table name to user. —-rename table employee to user;

Alter table character set utf8 —- alter table user character set utf8;

—– alter table user change name username varchar(20) unique not null;

Run the show tables command to query all tables in the current database

Run the show create table user command to query the current character set of the tablespace.

4. Delete the data table

Syntax: drop table name;

5. View the data table structure

Desc table name; View table structure

show tables ; View all table names in the current database

Show create table name; View table builder sentences and character sets

Note: All data table structure manipulation statements are DDL

[Learning Effect]

Master how to create, delete, modify table structure, view table structure.

【 knowledge point transition 】

This section explains how to add or delete data records from a table (table record operation statement).

[Stage 5]

[Target: Table record operation statement]

Add, delete, change and check data records in a data table

Insert records into table

Grammar 1:

Insert into table name Values () ; Assign values to each column of the data table

Matters needing attention

  1. The insert value type must match the column type

  2. The value length cannot exceed the column definition length

  3. The order of values corresponds to the order of columns

  4. String and date values must be in single quotes

  5. Insert null values to write NULL

Create a new table employee and insert three employee information

insert into employee(id,name,gender,birthday,entry_date,job,salary,resume)

values(1,’zhangsan’,’male’,’1990-10-10′,’2010-01-01′,’sales’,4000,’good boy ! ‘);

Syntax 2:

Omit all column names, but the value must match all columns in the table, in the order in which they are listed

insert into employee values(2,’lisi’,’male’,’1988-10-01′,’2008-08-17′,’hr’,3500,’good hr ! ‘); s

Grammar:

The ellipsis can be empty, partial column names with default values, and the last value matches the previous column

insert into employee(id,name,job,salary) values(3,’wangwu’,’boss’,20000); s

  • After inserting the record, select * from employee; View all employee information

Insert a Chinese record

Insert into EMPLOYEE (id,name,job,salary) values(4,’ xiaoming ‘,’ cleaner ‘,1500);

Wrong:

ERROR 1366 (HY000): Incorrect string value: ‘\xC3\xF7’ for column ‘name’ at row 1 ;

The mysql client uses the default character set GBK

Show variables like ‘character%’;

Solution: Change the client character set to GBK

MYSQL has six local character sets: Client connetion Result and Database Server System

The first:

Current window temporarily modify set names GBK;

  • It is valid only for the current window and will become invalid after it is closed

The second:

Configure the mysql/my.ini file

[mysql] Client configuration

[mysqld] Server side configuration

Modify client character set [mysql] character set default-character-set= GBK

Mysql -u root -p mysql -u root -p mysql -u root -p mysql -u root -p mysql -u root -p mysql -u root -p

2. Data record change operation

Update table name set column name = value, column name = value…. Where condition statement;

  • If there is no WHERE condition statement, all row data is modified by default

Modify the salary of all employees to 5000 yuan. —– update employee set salary = 5000;

Example Change the salary of employee whose name is’ Zhangsan ‘to 3000 yuan. ——- update employee set salary = 3000 where name=’zhangsan’ ;

Change salary of employee with name ‘Lisi’ to 4000 yuan,job to CCC ——- update employee set salary=4000, job=’ccc’ where name=’lisi’;

Increase Wangwu’s salary by 1,000 yuan. ———— update employee set salary = salary+1000 where name =’wangwu’;

3. Delete data records

Delete from table name where condition statement;

  • If there is no WHERE statement, all records in the table are deleted

To delete all data records in a table, use truncate TABLE name. Equivalent to delete from table name;

Truncate TABLE deletes all records in a table.

Truncate Deletes data. The entire table is deleted and then recreated

Delete Deletes data and deletes records row by row

  • Truncate is more efficient than DELETE. Truncate belongs to DDL and DELETE belongs to DML ======== Transaction management is only effective for DML. SQL statements managed by transactions can be rolled back to the state before SQL execution

Delete the record ‘zhangsan’ from the table. —— delete from employee where name=’zhangsan’;

Delete all records from a table. —– delete from employee; (Transaction rollback is possible)

Use TRUNCATE to delete records in the table. —- truncate table employee;

Note: Insert, UPDATE, and DELETE are DML statements

4, data table record query (DQL statement)

A syntax: select [distinct] * | column names, column names… From the name of the table;

Select * from table_name; Query the information about all columns in this table

Select column name, column name… From the name of the table; Queries information for the specified column in the table

Distinct Indicates the weight assignment

create table exam(

id int primary key auto_increment,

name varchar(20) not null,

chinese double,

math double,

english double

);

Insert into exam values(null,’ score ‘, 80, 80);

Insert into exam values(null,’ zhangfei1 ‘,70, 70);

Insert into exam values(null,’ jh ‘,90, 95);

Query information about all students in the table. ——— select * from exam;

Query all students’ names and corresponding English scores in the table. —– select name,english from exam;

—- select distinct English from exam;

Select expression (column name) from table name;

Select name as alias from table name;

Add 10 special points to all students’ scores. —- select name,chinese+10,math+10,english+10 from exam;

Calculate the total score of each student. ——- select name,chinese+math+english from exam;

Use aliases to represent student scores. —– select name, Chinese +math+ English from exam;

Select name, Chinese +math+ English from exam; —— select name, Chinese +math+ English from exam;

select name,math from exam; Query the value of name and math columns

select name math from exam; Query the name column value and alias math

Select column name from table name where condition statement

——-select * from exam where name=’ exam ‘;

—– select * from exam where English > 90;

—– select * from exam where Chinese +math+ English > 200;

The operator

  1. Equal = unequal <>

  2. between … and… The value between 70 and 80 is equivalent to >=70 <=80 —– note that the previous number is smaller than the latter number

  3. In (value, value, value) Any of the specified values in(70,80,90) can be 70,80, or 90

  4. Like ‘fuzzy query pattern’ for fuzzy query, the expression has two placeholders % Any string _ Any single character for example: name like’ zhang %’ All surname Zhang student

Name like ‘zhang _’ All students with the surname of Zhang are given two characters

  1. Is NULL Checks that the value of the column is null

  2. And logic and OR or not logic is not

Select * from student where English score between 90 and 100; ——– select * from exam where english>=90 and english <= 100; select * from exam where english between 90 and 100;

Select * from student where math = 65,75,85; —- select * from exam where math in(65,75,85);

Select * from student where name = ‘zhao’; —- select * from exam where name like ‘zhao %’;

Select * from student where English >80, Chinese >80; —- select * from exam where english > 80 and chinese > 80;

Insert into exam values(null,’ liu ‘,null,55, 55);

Select * from exam where Chinese is null;

Select * from exam where Chinese is not null;

Grammar 4: select * from table name order by column asc | desc; —- ASC ascending desc descending order

The math scores are sorted and output. ———– select * from exam order by math; Default ASC ascending order

———— select * from exam order by math+ Chinese + English desc;

————- select * from exam order by English desc,math desc;

The aggregate function refers to the built-in function in the SQL statement ———- the grouping function (for statistics)

  1. Article count statistics query result record number select count (*) | count (column name) from the name of the table;

How many students are there in a class? ———— select count(*) from exam;

How many students have a score of more than 90 in English? ——- select count(*) from exam where english > 90;

How many people have a total score greater than 220? ——–select count(*) from exam where chinese+math+english > 220;

  1. Select sum from table name select sum from table name;

Counting the total score of a class in math? —– select sum(math) from exam;

—- select sum(Chinese),sum(math),sum(English) from exam;

Select sum(Chinese + English) from exam; select sum(chinese)+sum(math)+sum(english) from exam;

Liu Bei language null, null all operations are null

select sum(chinese)+sum(math)+sum(english) from exam; It contains Liu Bei’s scores in English and mathematics

select sum(chinese+math+english) from exam; Excluding Liu Bei’s score in English and mathematics

  • Select sum(ifnull(Chinese,0)+ifnull(math,0)+ifnull(English,0)) from exam; It contains Liu Bei’s scores in English and mathematics

—— select sum(Chinese)/count(*) from exam;

  1. Select avg from avg; select avg from avg;

Find a class math average score? —- select avg(math) from exam;

Find a class total average score? —- select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;

  1. Max Indicates the maximum value in a column. Min Indicates the minimum value in a column

Select Max (Chinese + Math + English),min(ifnull(Chinese,0)+ifnull(Math,0)+ifnull(English,0)) from select Max (Chinese + English + min(ifnull(Chinese,0)+ifnull(Math,0)+ifnull(English,0) from exam;

Select group function from exam group by column name; Group statistics according to a column

Grouping operation, is to have the same data records divided into a group, easy statistics

create table orders(

id int,

product varchar(20),

price float
Copy the code

);

Insert into orders(id,product,price) values(1,’ TV ‘,900);

Insert into orders(id,product,price) values(2,’ washer ‘,100); insert into orders(id,product,price) values(2,’ washer ‘,100);

Insert into orders(id,product,price) values(3,’ product ‘,90);

Insert into orders(id,product,price) values(4,’ orange ‘,9);

Insert into orders(id,product,price) values(5,’ product ‘,90);

Exercise: After grouping the items in the order table, display the total price of each item —- need to group by item name

select product,sum(price) from orders group by product;

Add the having condition —- after the group by statement to filter the group query results

Exercise: Query the number of items purchased and the total price of each item is greater than 100

select product,sum(price) from orders group by product having sum(price) > 100;

What is the difference between where and having conditional statements?

Where is conditional filtering before grouping, and HAVING is conditional filtering after grouping

Where can be replaced with HAVING, but having can be grouped instead of where

Select statement:

S-f-w-g-h-o select… from … where … group by… having… order by … ;

The order cannot be changed

From – where – group by – having – select – order by

Select name from exam where name= ‘group by having order by;

[Learning Effect]

1. Master how to insert records into a data table.

2. Master how to change data records.

3. Master how to delete data records.

4. Master the SELECT statement to query records, and operation records can be added to a variety of restrictions.

【 knowledge point transition 】

The next section explains how to back up and cache data in the mysql database.

[Stage 6]

MySQL database backup and restore

Backup and restore the MySQL database

1. Run mysql/bin/mysqldump to export SQL statements from the database

Mysqldump -u username -p database name > disk SQL file path

For example, back up the DAY12 database — c:\day10.sql

CMD > mysqldump -u root -p day10 > c:\day10.sql

INSERT INTO exam VALUES (1, “guan yu”, 85,76,70), (2, ‘zhang fei, 70,75,70), (3,’ zhaoyun, 90,65,95), (4, ‘liu bei, NULL, 55, 38).

2. Run mysql/bin/mysql to import SQL files to the database

Mysql -u user name -p database name < disk SQL file path

  • To import SQL, you must manually create a database. SQL does not create a database

For example, import c:\day10.sql into the DAY10 database

CMD > mysql -u root -p day10 < c:\day10.sql

Source C :\day10.sql can also be executed inside the database

[Learning Effect]

How to back up mysql database data.

【 knowledge point transition 】

Next, we will learn about constraints on creating multiple tables in mysql database.

[Stage 7]

Objective: Multi-table design (foreign key constraint)

create table emp (

id int primary key auto_increment,

name varchar(20),

job varchar(20),

salary double

);

Insert into EMp values(null,’ emp ‘,’ human resources ‘,4500);

Insert into EMp values(null,’ emp ‘,’ emp ‘,5000);

Insert into EMp values(null,’ l ‘,’ sc ‘,8000);

Insert into EMp values(null,’ xiaoliu ‘,’ project manager ‘,10000);

create table dept(

id int primary key auto_increment,

name varchar(20)

);

Insert into dept values(null,’ dept ‘);

Insert into dept values(null,’ dept ‘);

Insert into dept values(null,’ dept ‘);

Create a relationship between the employee table and the department table to know which department the employee belongs to. Add a department ID field to the employee table

alter table emp add dept_id int ;

Update emp set dept_id = 1 where dept_id = 1;

Update emp set dept_id = 2 where dept_id = 1;

Update emp set dept_id = 3 where dept_id = 1;

Update emp set dept_id = 3 where dept_id = 1;

Delete from dept where name =’ dept ‘; —– Xiao Zhang and Xiao Liu lost their organization

Select * from emp where dept_id = ——-;

Alter table EMp add Foreign key(dept_id) References dept(id) alter table EMp add Foreign key(dept_id) references dept(id);

Unable to delete tech r&d because Liu and Zhang depend on tech R&D records ！！！！！

Multiple table design principle: All relational data can have only three correspondences (one-to-one, one-to-many, many-to-many)

1. Many-to-many relationships: employee and project relationships

An employee can work on more than one project

Multiple employees can work on a project

Table building principle: you must create a third relational table that references two entity primary keys as foreign keys

Each entry in the relational table represents an employee’s participation in a project

2. One-to-many relationship: the relationship between users and bloggers

One user can publish multiple blogs

A blog can only have one author

Table construction principle: You do not need to create a third-party relational table. You only need to add a primary key as a foreign key in multiple parties

3. One-to-one relationships: This is a relationship where you rarely see principals and studios

A director runs a studio

A studio has only one head

Table building rules: Either party adds the primary key of the other party as a foreign key

[Learning Effect]

Figure out how to create tables when multiple tables are related.

You can think about it in real life, and you can understand why you want to build a table like this.

【 knowledge point transition 】

Now, cartesian products.

[Stage 8]

[Goal: Learning cartesian products]

Multi-table query – Cartesian product

Match each record in table A with each record in table B to obtain the Cartesian product

select * from emp;

select * from dept;

select * from emp,dept; The result is the Cartesian product

The cartesian product is the product of two tables such as table A with 3 tables and table B with 4 —- with 12 cartesian products

Cartesian product result is invalid, must select valid data result from cartesian product!!

Multi-table join query inner join query

Select A record from table A and search for the corresponding record in table B. —– The connection will be displayed only when the corresponding record exists in table A and table B

create table A(A_ID int primary key auto_increment,A_NAME varchar(20) not null);

insert into A values(1,’Apple’);

insert into A values(2,’Orange’);

insert into A values(3,’Peach’);

create table B(A_ID int primary key auto_increment,B_PRICE double);

Insert into B values,2.30 (1);

Insert into B values,3.50 (2);

insert into B values(4,null);

Select * from a,b where a.a_id = b.a_id;

  • The number of internal join query results must be smaller than the number of records in the two tables —– For example, the number of internal join results in table A 3 B 5 —- is less than = 5

select * from emp,dept where emp.dept_id = dept.id ; Inner join emP table and DEPT table

Add a condition when the inner link query, query human resources department which employees? Select * from emp,dept where dept_id = dept.id and dept.name =’ dept.id ‘;

Which department does the employee with a salary of more than 7000 come from? select * from emp,dept where emp.dept_id = dept.id and emp.salary > 7000;

select * from emp,dept where emp.dept_id = dept.id ; Writing a

select * from emp inner join dept on emp.dept_id = dept.id ; Write two

[Learning Effect]

Know the concept of cartesian product, know how to extract valid data from cartesian product.