MySQL Learning note 01_ Database basics

01_1 MySQL database start and stop

• With < admin privileges > start cmd;
• Enter the net stop MySQL service to stop MySQL;
• Enter the net start MySQL service;
• Enter Mysql-u root–p carriage return and enter the password into the MySQL operating system and have root privileges;

• In the Command Prompt window, use services.msc to view the status of the MySQL service.

A brief introduction to the 01_2 database

According to the development time Order of database, the following types of database system are mainly appeared:

• Web-based database
• Hierarchical database
• relational database
• Object-oriented database

In the above 4 database system, the relational database is the most widely used. Object-oriented database is a new type of database which is spawned by object-oriented language, and some database systems such as SQL Server 2005, oracle10g and so on are beginning to increase the object-oriented features.

01_3 basic theory of SQL statements

SQL is the standard language for manipulating and retrieving relational databases, and standard SQL statements can be used to manipulate relational databases, divided into the following types:

• DDL(data definition Language) statement, manipulating the language of the data object, has create, alter, drop;
• DMLData munipulation Language, data manipulation language) statements, this set of DML statements will maintain a good consistency, with INSERT, UPDATE, delete;
• DQL(data query Language), the query language is mainly used for querying data, mainly by the Select keyword to complete, query statements are the most complex SQL statements, the most functional statement;
• The DCL(Data Control Language) statement consists of the grant, REVOKE, commit, rollback, savepoint statements.

01_4 common database objects and data types

(1) Common database objects

Object Name	corresponding keywords	Describe
Table	Table	A table is a logical unit of database storage that exists in the form of rows and columns; A column is a field, and a row is a data record
Data dictionary		Is the system table, stores the database related information table, the system table data usually has the database system maintenance. system table structure and data, developers should not manually modify, can only query the data in it
View	View	The logical display of data in one or more tables, a view is a virtual table and does not actually store the data
Constraints	Constraint	Enforce data validation rules for rules that guarantee data integrity
Index	Index	Used to improve query performance, equivalent to the book's Directory
Function	function	Used to complete a specific calculation with return values and parameters
Stored Procedures	Procedure	Complete business processing, no return value, but multiple values can be passed to the calling environment via an outgoing parameter
Trigger	Trigger	Equivalent to the listener of an event, when a specific event occurs in the database, the trigger is triggered and the response processing is completed

(2) Common data types

Column type	Description
tinyint/smallint/mediumint int (integer)/bigint	1-byte, 2-byte, 3-byte, 4-byte, 8-byte integer, and can be divided into two symbols and unsigned. These integer types differ only in scope
Float/double	Single-precision, double-precision floating-point types
Decimal (DEC)	Exact decimal type, equivalent to float and double without loss of precision
Date	Date type, the time cannot be saved. When a Date object in Java is saved to that type, the time part is lost
Time	Time type, cannot save date. When the date object of Java is saved in that type, the dates are partially lost
Datetime	Date, Time type
Timestamp	Time Stamp Type
Year	Year type, save year only
Char	Fixed-length string type
varchar	Variable-length string type
Binary	Fixed-length binary string type, which holds the string in binary form
varbinary	Variable-length binary string type, binary form holds string
Tingblob/blob Mediumblob/longblob	1-byte, 2-byte, 3-byte, 4-byte binary large object, can be stored in the image, music and other binary data, respectively, can be stored: 255/64k/16m/4g size
Tingtext/text Mediumtext/longtext	1-byte, 2-byte, 3-byte, 4-byte text object that can store long-length strings that can be stored: 255/64k/16m/4g-sized text
Enum (' Val1 ', ' val2 ', ...)	Enumeration type, which can only be one of the values that appear in the enum parentheses
Set (' value1 ', ' value2 ', ...)	collection type, the value of which can be one or more values in set

01_5 log in to MySQL database

(1) DOS command:

Mysql–h hostname–p Port–u username-p

Typically in native testing:

Mysql–h localhost–p 3306–u root–pmysql–u root-p

You will then be prompted to enter your password.

(2) graphical interface SQLyog

01_6 MySQL Related commands

List of all MySQL commands:

Note that all text commands must is first on line and end with '; '? (\?)   Synonym for "help". Clear (\c) Clear the current input statement. Explicitly connect (\ r) to the server, optional parameters are database and host delimiter (\d) Set statement delimiter. Sets the statement delimiter ego (\g) Send command to MySQL server, display result vertically. Send commands to MySQL server and show results ex It (\q) Exit MySQL. Same as quit. Exit Mysqlgo (\g) Send command to MySQL server. Send the command to the MySQL server Help (\h) display this assist. Display assistance Notee (\ t) don ' t write into outfile. Do not write output file print (\p) print CU rrent command. Print current commands prompt (\ r) Change your MySQL prompt. Changing the message for MySQL quit (\q) quit MySQL. Exit Mysqlrehash (\#) Rebuild completion hash. Rebuild completes hash source (\.) Executes a SQL script file with a file name as parameter status (\s) Get status information from the server. Get MySQL status information from the server tee (\ t) sets the output file [To_outfile], and adds the information to all given output files using the other database, the database name switches to another word as a parameter charset (\c) \u Descriptor Warnings (\w) show warnings after every statement. Show warning after each statement nowarning (\w) Don ' t show Warnings after every statement. Do not display a warning after each statement 

01_7 re-configuring MySQL

(1) Reconfigure with DOS command

Modify character Set encoding: Set Character_set_client=utf8; Set CHARACTER_SET_CLIENT=GBK;

(2) Modifying the INI configuration file

Note: The MySQL service needs to be restarted after modification to take effect.

01_8 troubleshooting How to set a password when MySQL is initialized

Problem:

Solution:

(1) First start the CMD console as an administrator

Stop MySQL Service

>net stop MySQL

(2) Skip the MySQL permission check, because MySQL did not set a user name and password at the beginning of the default to log on as the root user will automatically generate random passwords, need to skip this step

In the initialization configuration, My.ini the [mysqld] field join: Skip-grant-tables

(3) Restart MySQL service, when MySQL does not need a password to log into the database

CMD into MySQL database

Mysql>use mysql;mysql>update User Set Password=password (' q335836929 ') WHERE user= ' root '; mysql>flush privileges;

(4) After the operation and finally remove the My.ini in the Skip-grant-tables, restart Mysqld can

You can also do this:

→ First jump to the MySQL bin directory, and then enter mysqld--skip-grant-tables. It is possible that there will be no response at this time, and a new line of input is not generated.

In fact it has stopped the permission check and we can do other things.

→ Reopen a cmd command Prompt window, enter into MySQL, and then enter use MySQL, you will find the success

The last line above indicates that the Database changed!!

If you enter show tables you will see a table under this database called user, which logs the login account name and password.

So you can modify the root account password as follows:

UPDATE user SET Password=password (' q335836929 ') WHERE user= ' root ';

When you use MySQL Command line client login, you will be asked to enter the password 123! You have not logged in anonymously, so you can do a variety of operations!!!

This article link: http://www.cnblogs.com/homewch/p/6015151.html

MySQL Learning note 01_ Database basics