Shulou(Shulou.com)06/01 Report--

The following mainly brings you some basic knowledge of MySQL. I hope these words can bring you practical use, which is also the main purpose of this article that I edit some basic knowledge of MySQL. All right, don't talk too much nonsense, let's just read the following.

A brief introduction to the database

What is a database?

* A database is a file system, but you need to use standard SQL statements to access the data.

Relational database

* what is stored is the relationship between entities.

Common databases

* Oracle: Oracle's database products, large fee-based databases.

* SQLServer: Microsoft's database products, medium-sized fee-based databases.

* MySQL: an open source, free database product that is free in version 5.x and charged in version 6.x.

* DB2:IBM database products, large fee-based databases.

* SyBASE: has retired from history, Powerdesigner (database modeling tool). II. Uninstall and install MySQL

Uninstall MySQL:

1. First go to the installation path of MySQL, find the configuration file of my.ini, and find two paths, basedir and datadir.

two。 Then uninstall the database and find the above two paths to delete the folder.

Installation of MySQL:

Next step, next step.

III. Reset of root password

3.1 stop the mysql service

3.2 enter a command in cmd to open a MySQL service that does not require authentication when logging in

The window above appears at this time, indicating that the MySQL service has been started. Do not close this window at this time. Or the service will fail.

Open a new cmd window, and then type mysql-u root-p. In the above service, you can enter directly without a password.

Enter the command show databases; to view the database.

Enter the command use mysql; to use the mysql database.

3.5 statements to change passwords

Update user set password = password ('root') where user=' root'

3.6 close both windows

3.7 the process of ending mysqld in Task Manager

3.8 restart the MySQL service

4. The relationship between MySQL CVM, data and tables

5. Introduction to SQL

* SQL:Structured Query Language, structured query language.

* Features: non-procedural language

* procedural language: the current execution of this statement depends on the previous statement or statements.

* non-procedural language: can be a statement, a result will be executed.

* in order to enhance the language ability of SQL, various manufacturers have enhanced the features of process language.

* such as Oracle's PL/SQL procedural processing capability.

* T-SQL of SQL Server and Sybase.

* SQL is a language used to access relational databases, with four functions of querying, manipulating, defining and controlling relational data.

VI. SQL classification

* DDL (data definition language)

* data definition language Data Definition Language

* used to define objects in the database, such as data tables, views, indexes, and so on.

* DML (data manipulation language)

* data manipulation language Data Manipulation Language

* update, add, and delete records in database tables

* such as update, insert, delete.

* DCL (data control language)

* data control language Data Control Language

* used to set user permissions and control transaction language

* grant, revoke, if...else, while, begin...transaction

* DQL (data query language)

* data query language Data Query Language

* select

VII. Database creation

Create database database name character set utf8

Case: create a database named mydb1.

Create database mydb1

Case: create a database of mydb2 that uses the utf8 character set.

Create database mydb2 character set utf8

VIII. The database-- viewing the database

-- display database statements; show databases

IX. Database-- displaying database definition information

-- display database definition information show create database database name

X. database deletion

Drop database database name

11. database-- modifying the database

Alter database if not exists database name [default] character set utf8

XII. Database switching

Use database name

XIII. Database-View the database currently in use

Select database ()

XIV. Datasheets-- creation of datasheets

Craete table table name (field 1 type (length) constraint, field 2 type (length) constraint, field 3 type (length) constraint)

Note:

The table name is in parentheses followed by a semicolon.

Each line of fields is followed by a comma, but the last line does not have a comma.

The data type is followed by a length, and if it is a string type, the length must be added. If other types can not be added. Default length.

Data type:

String type:

The varchar length is variable. The char length is immutable.

Big data type:

Bolb binary file text character

Numerical type:

Tinyintsmallintintbigintfloatdouble

Logical type:

Bit

Date type:

Date contains date only time contains hour minute second only datetime contains date and hour minute second timestap contains date and hour minute second, if no data is passed, the current system time is selected by default

Case: create a tabl

Create table employee (id int, name varchar (20), gender varchar (10), birthday date, entry_date date, job varchar (100), salary double, resume text)

15. One of the datasheets-showing all datasheets

Show tables

XVI. Data tables-constraints of a single table

Primary key constraint: identifies and marks the record, and the primary key can be declared through primary. (default unique and non-empty)

Unique constraint: the value is unique, declared using unique.

Non-null constraint: value cannot be empty, not null

XVII. Datasheets-- creating tables using constraints

Create table employee2 (id int primary key auto_increment, name varchar (20) unique not null, gender varchar (10) not null, birthday date not null, entry_date date not null, job varchar (100) not null, salary double not null, resume text not null)

XVIII. Data sheet-- View the information of the table.

Desc table name

XIX. Datasheets-- View table statements and character sets

Show create table table name

20. One of the data tables-deleting tables

Drop table data Table name

21. Datasheets-- modifying tables

Add a field:

Alter table table name add field type (length) constraint

Delete a field

Alter table table name drop field

Modify a type or constraint

Alter table table name modify field type (length) constraint

Modify the name of the field

Alter table table name change old field new field type (length) constraint

Modify table name

Rename table table name to new table name

Modify character set

Alter table old table name character set utf8

22. Increase in data

Add data

Insert into table name (field, field) values (value 1, value 2, value 3); insert into table name values (value 1, value 2, value 3)

Note:

The data is of the same type as the field.

The field length needs to be controlled.

String or date type requires the use of''.

Insert data into the user table.

Insert into user values (null,'xiaofeng','1994-10-10); insert into user values (null,' Mei Mei, 1994-10-10); insert into user values (null,' Xiaofeng, 1994-10-10); insert into user values (2011-10-10). Insert into user values (null,' hibiscus, '1994-10-10); insert into user values (null,' monitor, 1994-10-10); insert into user values (2011-10-10)

23. Modification of data

Update table name set field = value, field = value [where]

If there is no where condition, all records are updated by default.

If there is a where, update one or more.

Case study: change the salary of all employees to 5000 yuan.

Case study: change the salary of an employee whose name is monitor to 3000 yuan.

24. Deletion of data

Delete from table name [where]

Case: delete the data named 'monitor' in the table

25. Query of data

Select * from table name; select field 1, field 2 from table name

Case: query all user information

Case: query the user's name and birthday information

Use where conditional filtering

Select * from table name [where]; select field 1, field 2 from table name [where]

Case: query Furong's information

Sort using order by

Case: sort the user's birthday (descending)

Grouping function

Count (), max (), min (), avg (), sum ()

Case: ask for information about how many people there are, what is the total salary, what is the maximum and minimum wage, and what is the average wage.

For the above basic knowledge about MySQL, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.