1. Introduction to the Database 1.1What is a database, a file system that operates on a database using standard SQL 1.2a common databaseOracle is a database of Oracle companies, a large database for a fee
DB2, IBM's database, is a large database of charges;
Sqlsever, Microsoft's database, is a medium-sized database.
The MySQL database was acquired by Oracle,
SQLite database, embedded small database, application in client development;
1.3relational databaseA relational database stores relationships between entities.
Using er diagrams to represent relationships between entities
2. Installation and uninstallation of MySQL database
3. Storage node for MySQL database2.1 There are multiple databases
There are multiple database tables in each database
There are multiple records in each table
2.2 Learning is about using standard SQL to record operations on databases, database tables, and tables
4. Introduction to SQL4.1 Structured Query Language, structured querying language
4.2 Non-procedural language, run directly, do not need to rely on other things
4.3 SQL operations on databases, database tables, table records
4.4 Classification of SQL DDL Data Definition Language
Creating a database, creating a database table operation
Commonly used statements Create
DML Data Manipulation language
Operations on database table records
Frequently used statements Insert Update Delete
DCL Data Control Language
DQL Data Query Language
Querying operations on records in a database table
Using the statement Select
5. Using SQL to manipulate a database5.1 Starting MySQL---mysql-u root-p
5.2 Enter the installation database password
5.3 Creating a Database---CREATE DATABASE mydatabase;
5.4 Get the list of all current databases---show databases;
5.5 using the database created above MyDatabase//---use MyDatabase
5.5 Delete database drop Databaese MyDatabase;
5.6 Switch to the database used by use MyDatabase;
6. Working with SQL on database tables
6.1 Creating a database table CREATE TABLE MyTable (
ID int,
Name varchar (40),
Sex varchar (40),
)
6.2 Get current list of all database tables show tables;
6.3 Gets the structure of the specified database table desc mytable;
6.4 Adding data to the specified database table insert INTO mytable values (1, ' Lishi ', ' man ');
6.5 Getting the content data for a specified database table
6.5.1 gets all of the data contents of the SELECT * from MyTable;
6.5.2 Gets the data contents of the specified field in the Select Name,sex from MyTable;
6.5.3 gets the data contents of the specified field in the select * from mydatabase where id = 1;
6.6 MySQL has three constraints
6.6.1 NOT NULL non-null constraint
6.6.2 auto_increment in the primary key, let the primary key be autogrow
When auto-grow is used, the type of the field must be the Int class
6.6.3 unique only Wang Se constraint
6.7 Creating a table with constraints create TABLE MyTable (
ID int PRIMARY KEY,
Name varchar (NOT NULL)
)
CREATE TABLE Stu (
ID int primary KEY auto_increment,
sname varchar (40),
Sex varchar (40)
)
6.8 Deleting tables drop table mytable;
6.9 action to modify data in a table update mytable name = ' Abd ' where id=2;
6.10 Deleting data from a table delete from mytable where id=3;
6.11 query to remove duplicate data
SELECT DISTINCT * FROM MyTable;
6.12 Setting aliases when querying
Select name as KKK from MyTable;
6.13 Possible write operators in query statements
CREATE TABLE MyTable (
ID int,
Name varchar;
che int,
math int,
ength int
)
6.13.1 query a person with math score greater than 40 select * from Mytables where math>40;
6.13.2 a student with math score 10 and 40 in the enquiry form.
select* from mytables where math int (10,40);
6.13.3 Fuzzy Query
Select*from mytables where name like '%lili% ';
6.13.4 viewing the currently running database
Select Databaese ();
6.14 order BY to sort the records of a query
6.14.1 SELECT * from Mytables order by math ASC; Ascending
6.14.2 SELECT * from Mytables order by math desc; Descending
6.15 count () How many records are in the statistics table
Select COUNT (*) from Mytables;
6.16 Sum summation function
Select sum (math) from Mytables;
6.17 avg Averaging function
Select AVG (math) from Mydatables;
6.18 Max min
Select Max (math), Min (Nath) from Mytables;
6.19 Grouping of operations
CREATE TABLE Orders (
ID int,
Product varchar (20),
Price float
);
INSERT into orders values (1, ' TV ', 900);
INSERT into orders values (2, ' washing machine ', 100);
INSERT into orders values (3, ' washing machine ', 100);
INSERT into orders values (4, ' oranges ', 9);
INSERT into orders values (5, ' oranges ', 9);
INSERT into orders values (6, ' flashlight ', 20);
INSERT into orders values (7, ' flashlight ', 20);
Inquire about the purchase of several kinds of goods, and each kind of total price more than 100 of goods
SELECT * FROM Mytables GROUP BY product have sum (price) >100;
Writing specification for 6.20 SELECT statements
Select...from ... where ... grout by. Having. ORDER BY:
7. Data types in MySQL7.1 String Type
varchar Char
The difference between the two is that the length of the varchar is variable and must be set at the time of use.
The length of char is immutable and can be used without setting its length;
7.2 Big Data types
Blob text
7.3 Numerical Type
tinyint smallint int Bigin float dooble
7.4 Logical bit
7.5 Date Type
Date format for dates
Time represents the format
DateTime can represent a date or time
Timestamp automatic generation of the current time of the system, do not need to manually add
the Limit keyword in 8.mysql(1) Implementation of a certain number of records in the query table, used in the system inside the operation of paging
(2) The Limit keyword is not a standard SQL keyword and can only be used inside MySQL
* There are also special keywords in other databases.
For example, in Oracle to implement paging using the keyword RowNum
The keyword top for paging in SQL Server
(3) Limit keyword query before several records limit 2
* Exercise: Query the top three records in the Orders table
SELECT * from Orders limit 3;
SELECT * FROM Orders limit 0, 3;
(4) Limit keyword can query the first to the number of records limit plus two parameters, separated by a comma
* Exercise: Query the Orders table for the second to fourth record
SELECT * FROM Orders limit 1,3
* There are two parameters in limit 2,4
* * The first parameter represents the starting position of the record, but the start position starts at 0
* * The second parameter gets a few records from the starting position
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Getting started with MySQL basic operations
