MySQL Basic Operation brief

1 databases: Warehouses for managing and storing data

2 Advantages of the database:

① can store a large amount of data ② easy to retrieve ③ maintain data consistency, integrity ④ security, can be shared ⑤ through combinatorial analysis, can generate new data

3 Common databases:

Mysql:

MySQL is the most popular open source SQL database management system, developed, distributed, and supported by MySQL AB. MySQL AB is a MySQL developer-based business company, a second-generation open source company that uses a successful business model to combine open source values and methodologies. MySQL is a registered trademark of MySQL AB.

MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. MySQL server supports mission-critical, heavy-duty production system use, or it can be embedded in a large configuration (mass-deployed) software.

Compared to other database management systems, MySQL has the following advantages:

(1) MySQL is a relational database management system.

(2) MySQL is open source.

(3) MySQL server is a fast, reliable and easy-to-use database server.

(4) The MySQL server works in a client/server or embedded system.

(5) A large number of MySQL software can be used.

SQL Server:

SQL Server, a database management system developed by Microsoft, is the most popular database for storing data on the web, and it has been widely used in the fields of e-commerce, banking, insurance, electricity and other related databases.

Currently the latest version is SQL Server 2005, which can only be run on Windows, and the system stability of the operating system is important for the database. The parallel implementation and coexistence model is immature, and it is difficult to handle the increasing number of users and data volumes with limited scalability.

SQL Server provides numerous web and e-commerce features, such as rich support for XML and Internet standards, easy and secure access to data through the Web, and powerful, flexible, web-based, and secure application management. Moreover, due to its ease of operation and its friendly operating interface, the user's favorite

Oracle:

When you bring up a database, the first company you think of is Oracle (Oracle). The company was founded in 1977 and was originally a company specializing in database development. Oracle has been a leader in the database industry. In 1984, the relational database was first transferred to the desktop computer. Then, Oracle5 pioneered the new concepts of distributed databases, client/server architectures, and more. Oracle 6 first-line lockdown mode and support for Chenduo processing computers ... The latest Oracle 8 increases the object technology and becomes the relational-object database system. Oracle is now one of the most widely used relational data systems in the world, covering dozens of models, including large, medium and small machines.

Oracle database products have the following excellent features.

(1) Compatibility

Oracle products are standard SQL and tested by the U.S. National Institute of Standards and Technology (NIST). Compatible with IBM Sql/ds, DB2, INGRES, IDMS/R, and more.

(2) Portability

Oracle's products can run on a wide range of hardware and operating system platforms. Can be installed in more than 70 different large, medium and small machines, can be in VMs, DOS, UNIX, Windows and other operating systems under the work.

(3) Possible connectivity

Oracle can connect to a variety of communication networks, supporting a variety of protocols (TCP/IP, DECnet, LU6.2, etc.).

(4) High productivity

Oracle products provide a variety of development tools that can greatly facilitate further development of the user.

(5) Openness

Oracle's good compatibility, portability, connectivity, and high productivity make Oracle RDBMS a good open-ended.

Sybase:

In 1984, Mark B. Hiffman and Robert Epstern created Sybase and launched the Sybase database product in 1987. Sybase has three main versions: one is the version running under the UNIX operating system, the other is the version running under Novell NetWare environment, and the third is the version running under Windows NT. For UNIX operating systems, the most widely used are Sybase 10 and syabse for SCO Unix.

Features of the Sybase database:

(1) It is a database based on the client/server architecture.

(2) It is a truly open database.

(3) It is a high-performance database.

DB2:

DB2 is a database management system embedded in IBM's AS/400 system, supported directly by the hardware. It supports the standard SQL language and has a gateway connected to heterogeneous databases. Therefore, it has the advantages of fast speed and good reliability. However, only the hardware platform chooses IBM's as/400 to choose to use the DB2 database management system.

DB2 can be run on all major platforms (including Windows), and is best suited for massive amounts of data.

DB2 is the most widely used enterprise-class, with nearly 85% of the world's 500 largest enterprises using DB2 database servers, and the country accounts for about 5% in 1997.

In addition, there are Microsoft's Access database, FoxPro database and so on. Now that there are so many database systems, what kind of database should be chosen in game programming? The first principle is to consider the game development budget on the other hand, depending on the actual needs. Databases that are commonly used now are SQL Server, My sql, Oracle, FoxPro. MySQL is a completely free database system, its function also has the function of standard database, therefore, in the independent production, it is recommended to use. Oracle is powerful, but it is for commercial use, and is rarely used in games.

Note: MySQL: small-to-medium database, relational applications

4 Installation Database: Installation details (with blog URL);

5 Turn on MySQL service: net start MySQL

Turn off MySQL service: net stop MySQL

6 start Mysql:mysql–u root–p exit in DOS environment: exit;

7 MySQL is a multi-user, multi-database relational application database

Databases: Database, which stores various types of data in a two-dimensional table, and places the corresponding table in the database.

8 Data type:

Character: char (number of character data size) immutable character/varchar (data size) variable character/text

Number: Float/double int

Date: Date/time/datetime/timestamp

9 Constraint conditions

PRIMARY KEY constraint: Primary key-a flag that identifies all record uniqueness, the corresponding data cannot be null, and cannot be duplicated

nonempty constraint: NOT null--the field information must be filled in and cannot be populated with null

Unique constraint: Unique--The field information cannot have duplicate information

FOREIGN KEY constraint: foreign key--reference the data information in the primary table from the table

Check constraint: Check-checks the current input input validity

Default value: Default-fills with defaults when the user does not fill in the data

10 View all current databases

show databases;

11 Creating a Database

Create database name; (create DB day_01;)

12 Deleting a database

drop database name; (drop DB day_01;)

13 Selecting a Database

Use database name;

MySQL Syntax requirements

The ①sql statement can be written one or more lines, ending with a semicolon;

② can be used to enhance the readability of sentences with spaces and indents;

③ keyword is not case-sensitive, the proposed capitalization;

SQL classification

DDL: Data definition Language For example: Create a table, create a library, delete a table, delete a library, modify a table structure

DML: Data manipulation Language For example: adding information to a table, deleting information, modifying information

DCL: Data Control Language For example: authorizing users

DQL: Data Query Language For example: Filter the information in the table, left outer connection, right outer join, sub-query, aggregate query ....

+ DDL Classification

Creating database: Create database name;

Query database: show databases;

Delete the database: drop database name;

Switch database: use database name;

Querying the table in the current database: show tables;

To create a table:

CREATE TABLE Table name (

Field Name 1 data type constraints,

Field Name 2 data type constraints,

);

Example:

#学生表中: Number of names age sex date of birth Social security number place of birth scholarshipCreate TableStudent (IDint, namevarchar(Ten), ageint(3), sex enum ('male','female'), Birthday date, IdcardChar( -), Addressvarchar( -), Bonuesdecimal(7,2));

View table structure: DESC table name;

Add Data:

INSERT into student (id,name,age,sex,birthday,idcard,address,bonues) VALUES (2, ' Zhang San ', 23, ' female ', ' 1999-2-2 ', ' 1111111111111 ', ' Beijing ', 500);

To view table data:

SELECT * from database name; (View all tabular data)

Select Field Name 1, field Name 2, ...

Select Id,name from student; (View partial data)

17 Common basic operations

Modify the table structure to add columns to the table:

Grammar:

ALTER TABLE name the type of the Add column list;

Example: Adding a class column to a student table

ALTER TABLE student add classes int;

To modify the types of fields in a table:

Syntax: ALTER TABLE name modify new type of column list;

Example: Change the type of the date of birth field in the student table to a timestamp

ALTER TABLE student modify birthday timestamp;

Modify table structure, change column name

Syntax: ALTER TABLE name change the type of new column name of the old column name new column name;

Example: Change the name of the sex column in the student table to gender

ALTER TABLE student change sex gender enum (' Male ', ' female ');

Modify the structure of a table, delete a column

Syntax: ALTER TABLE name drop column name;

Example: Deleting an address column in a student table

ALTER TABLE student drop address;

Modify the name of the table:

Grammar:

ALTER TABLE name rename to new table name;

Or

Rename table name to new table name;

Example: Change the name of the student's table to Stu

ALTER TABLE student Rename to Stu;

Or

Rename table Stu to Stus;

To delete a table:

drop table name;

Example:

drop table tab1;

MySQL Basic Operation brief