MySQL database Getting Started notes

Introduction to 1.MySQL Related concepts

MySQL is a relational database (relational databases Management System), "relational" can be understood as the concept of "table", a relational database consists of one or more tables.

• header (header): The name of each column;
• column (Row): A collection of data of the same data type;
• Row (col): Each line is used to describe the specific information of a person/thing;
• value: The specific information for the row, each value must be the same as the data type of the column;
• Key: A method in a table that identifies a particular person/thing, and the value of the key is unique in the current column.

Basic composition of 2.MySQL scripts

MySQL executes the operation of the database by executing SQL script, which consists of one or more MySQL statements (SQL statement + extension statements), and the script file suffix is usually. SQL when saved. Under the console, the MySQL client can also execute a single sentence without saving the. sql file.

• identifiers : Identifiers are used to name objects, such as databases, tables, columns, variables, and so on, to be referenced elsewhere in the script. The MySQL identifier uses a universal naming convention: Identifiers consist of letters, numbers, or underscores (\_), and the first character must be a letter or an underscore. The case sensitivity of identifiers depends on the current operating system and is not sensitive under Windows, but for most linux\textbackslash Unix systems, these identifiers are case sensitive.
• Keywords: keywords have their own specific meaning, try to avoid as an identifier.

• statement : The MySQL statement is the basic unit that makes up the MySQL script, and each statement can accomplish a specific operation, which consists of the SQL standard statement + MySQL extension statement.

• functions : MySQL functions are used to implement some advanced functions of database operations, which are broadly divided into the following categories: String functions, mathematical functions, date-time functions, search functions, cryptographic functions, information functions.

Data types in 3.MySQL

MySQL has three major classes of data types, number, date \ Time, string, and more detailed sub-types in the three categories:

• Number type:

integers: tinyint, smallint, mediumint, int, bigint;
Floating point: float, double, real, decimal;

• Date and Time:

Date, time, DateTime, timestamp, year;

• String type:

String: char, varchar;
Text: Tinytext, text, Mediumtext, Longtext;
Binary (used to store pictures, music, etc.): Tinyblob, Blob, Mediumblob, Longblob.

For more information about data types, refer to the following pages: http://www.cnblogs.com/zbseoag/archive/2013/03/19/2970004.html

4. Using the MySQL database

4.1 Log in to MySQL

Start a command prompt as an administrator and start the MySQL service with net start MySQL or net start mysql57(the number later is the version number).

• Startup:net start MySQL
• STOP:net stop MySQL
• Uninstall:net delete mysql

After starting the service, enter the following format command to log in to the database:mysql-h hostname-u user name-P;

• -H: This command is used to specify the MySQL hostname that the client wants to log on, and the parameter can be omitted when logging on to the current machine;
• -U: The name of the user to log in;
• -P: Tells the server that a password will be used to log in, ignoring this option if the user name password you want to log in is blank.

To log in as an example of a MySQL database that has just been installed on this computer, enter mysql-u root-p at the command line to confirm that if the installation is correct and MySQL is running, you will get the following response:

Enter Password:

If the password exists, enter the password to login, does not exist then directly press ENTER to log in, the default root account is no password. Once you're signed in, you'll see Welecome to the MySQL monitor ... The prompt. Then the command prompt will always wait for the command input with mysql> plus a blinking cursor, enter exit or quit to sign out.

4.2 Creating a Database

Create DATABASE command format: CREATE databasename [other options];

To facilitate the display of Chinese at the command prompt, the database character encoding is specified as GBK at creation time by character set GBK . When the creation succeeds, it gets the response of Query OK, 1 row affected (0.02SEC).

Note: The MySQL statement is a semicolon (;) as the end of the statement, if you do not add a semicolon at the end of the statement, the command prompt prompts you to continue typing (there are individual exceptions, but the semicolon is definitely not wrong);

Tip: You can use the show databases command to see which databases have been created.

4.3 Select the database you want to manipulate

To operate on a database, you must first select the database, otherwise you will be prompted with an error: "Error 1046 (3d000): No Database selected

Two options for using the database:
One: Specified when logging into the database, command: mysql-d selected database name-h host name-u user name-P;
Second: After logging in using the USE statement to specify, command: Uses thedatabase name . The USE statement can be executed without a semicolon, and the "used + created database name" is chosen to create the database, and the selection will prompt after success: database changed.

4.4 Creating a database table

• Creating a database table: CREATE tabletable name (column declaration);
• View the name of the table you have created:show tables;
• To view details of a table that has been created:describe table name;

Create a database table as follows:

Create TableStudents (IDintUnsigned not NULLAuto\_incrementPrimary Key, nameChar(8) not NULL, SexChar(4) not NULL, AgetinyintUnsigned not NULL, TelChar( -)NULL default "_");

The script can be executed by any text editor after the statement is entered into a file that is saved as Createtable.sql, through the file redirection at the command prompt. Open a command prompt, enter:mysql-d samp_db-u root-p < Createtable.sql. (Hint: 1. If connecting to a remote host, add the-H command; 2. The Createtable.sql file must specify the full path of the file if it is not in the current working directory. )

CREATE TABLE tablename (columns); For the command to create a database table, the name of the column and the data type of the column will be completed in parentheses.
"ID int unsigned NOT NULL Auto_increment primary key" explanation:

• "id": The name of the column;
• "int": Specifies that the column has a data type of int and is then decorated with "unsigned" to indicate that the type is unsigned;
• "NOT NULL": Indicates that the column cannot be empty;
• "Auto_incremen": Used in an integer sequence to insert data if the column is null, a unique identifier that is larger than the existing value is automatically generated. Only one such value can be found in each table and the column must be an indexed column;
• "PRIMARY Key": Indicates that the column is the primary key of the table, and the value is unique.

4.5 Inserting data into a table

The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:

insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...);

Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:
INSERT into students (name, sex, age) VALUES ("Sun-hee", "female", +);

4.6 Querying the data in a table

SELECT statement:Select column name from table name [query condition]; You can use the wildcard character * to query all the contents of a table.
Specific criteria query:Select column name from table name where condition;

• where list = value
• =, \textgreater, \textless, \textless=, \textgreater=,! =
• is [not] null, in, like
• Or and and combination queries

4.7 Updating data in a table

The UPDATE statement can update the data in the table, command format: Updatetable name set column name = new value where update condition;

4.8 Deleting data from a table
The DELETE statement deletes the data in the table, deletes one or more rows of data that satisfy the condition, and the command format: Delete fromtable name \ where delete condition;

4.9 Modifications to the table after creation
The ALTER TABLE statement is used to modify the table after it is created.

• Add column:alter TABLE table name \ add column name \ column data type \ [after insertion position];
• Modify columns: ALTER TABLE name \ changecolumn name \ column new name \ new data type;
• Delete column:alter TABLE table name \ drop column name;
• Rename table: ALTER TABLE name\ rename new name;
• Delete entire table:drop table name;
• Delete Entire database:drop database name;

5. Appendix
To modify the root user password:
Open a command prompt and execute the command:mysqladmin-u root-p password new password .

MySQL database Getting Started notes