This section describes concepts related to MySQL
A Relational Database consists of one or several tables, as shown in the following figure:
- Header: The name of each column;
- Row: a collection of data of the same data type;
- Col: Each line describes specific information about a person/thing;
- Value: Specific information about a row. Each value must be of the same data type as the column.
- Key: A method used in a table to identify a particular person/item, whose value is unique in the current column.
MySQL configuration in Windows
In MySQL 5.1 installation version, for example, download MySQL – noinstall – 5.1.69 – win32. Zip (official download page: dev.mysql.com/downloads/m…).
Configuration steps:
1. Decompress the downloaded mysql-noinstall-5.1.69-win32.zip file to the installation directory, for example, C:\Program Files;
2. Find the my-small-. ini configuration file in the installation folder, rename it to my.ini, open it for editing, and add default-character-set = GBK under [client] and [mysqld]
3. Open Windows environment variable Settings and create a variable named MYSQL_HOME. The value of the variable is the MySQL installation directory path, C:\Program Files\mysql-5.1.69-win32
4. Add it to the Path variable of the environment variable. %MYSQL_HOME%\bin;
5. Install the MySQL service, open the Windows command prompt, and run the following command: Mysqld –install MySQL –defaults-file=”my.ini”
Start, stop, and uninstall the MySQL service
Run at Windows command prompt:
Net start MySQL
Net stop MySQL
Uninstall: sc delete MySQL
Basic components of MySQL scripts
Similar to conventional scripting languages, MySQL has a set of rules for the use of characters, words, and special symbols. MySQL performs database operations by executing SQL scripts that consist of one or more MySQL statements (SQL statements + extension statements). The script file name extension is.sql. Under the console, the MySQL client can also execute statements singly without saving them as.sql files.
identifier
Identifiers are used to name objects such as databases, tables, columns, variables, and so on that can be referenced elsewhere in the script. The MySQL identifiers naming rules are a bit more verbose. Here we use the universal naming rules: Identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or underscore.
Whether or not identifiers are case-sensitive depends on the current operating system. They are insensitive under Windows, but they are case-sensitive on most Linux \ Unix systems.
Key words:
MySQL keywords are many, here is not a list, in learning to learn. These keywords have their own meanings and should be avoided as identifiers.
Statement:
MySQL statements are the basic unit of a MySQL script. Each statement can perform a specific operation. It is composed of SQL standard statements and MySQL extension statements.
Function:
MySQL functions are used to implement some advanced functions of database operations. These functions can be roughly divided into the following categories: string functions, mathematical functions, date and time functions, search functions, encryption functions, and information functions.
Data types in MySQL
1, the integer
MySQL data type
Meaning (signed)
tinyint(m)
1 byte range (-128 to 127)
smallint(m)
2 bytes range (-32768 to 32767)
mediumint(m)
3-byte range (-8388608 to 8388607)
int(m)
4-byte range (-2147483648 to 2147483647)
bigint(m)
8-byte range (+-9.22*10 ^ 18)
Value range If unsigned is added, the maximum value is doubled. For example, tinyint The value of unsigned ranges from 0 to 256. Int (m) specifies the width of the SELECT result set, and does not affect the display width.
2. Floating point (float and double)
MySQL data type
meaning
float(m,d)
Single precision floating point type 8 bit precision (4 bytes) m total, D decimal
double(m,d)
Double precision floating point type 16 bit precision (8 bytes) m total, D decimal
Let a field be defined as float(5,3). If you insert 123.45678, the actual database contains 123.457, but the total number is 6 bits.
3. Fixed point number
Floating-point types store approximate values in the database, while fixed-point types store exact values in the database. The decimal(m,d) argument m<65 is a total number,d <30 and d
String (char,varchar,_text)
MySQL data type
meaning
char(n)
A fixed length of up to 255 characters
varchar(n)
The value contains a maximum of 65535 characters
tinytext
The variable length contains a maximum of 255 characters
text
The value contains a maximum of 65535 characters
mediumtext
Variable length, up to 2 ^ 24 -1 characters
longtext
Variable length, up to 2 ^ 32 -1 characters
Char and varchar: 1. Char (n) If the number of stored characters is less than N, space is added after it. So char cannot store strings with Spaces at the end, and vARCHar is not limited to that. 2. Char (n) fixed length, char (4) into a few characters, will take up to 4 bytes, varchar is the actual number of characters of deposit + 1 byte (n < = 255) or 2 bytes (n > 255), so a varchar (4), in the 3 characters will take up to 4 bytes. 3. Char strings are retrieved faster than vARCHar strings.
Varchar and text: 1. Varchar can specify n, but text cannot specify. Internal storage vARCHar is the actual number of stored characters +1 byte (n <=255) or 2 bytes (n>255), text is the actual number of characters +2 bytes. 2. The text type cannot have a default value. 3. Varchar can create an index directly. Text specifies the number of characters before creating an index. Varchar queries are faster than text queries, and the text index does not seem to work when both indexes are created.
5. Binary data (_Blob)
1._BLOB and _text are stored in different ways. _text is stored as text and English is case sensitive, while _BLOB is stored as binary and case insensitive. 2._BLOB data can only be read as a whole. 3._TEXT can specify a character set. _BLO does not specify a character set.
6. Date and time type
MySQL data type
meaning
date
The date ‘2008-12-2’
time
Time ’12:25:36′
datetime
Date & Time ‘2008-12-2 22:06:44’
timestamp
Automatically store the modification time
If a field is defined as TIMESTAMP, the time data in this field will be automatically refreshed when other fields are modified, so the field of this data type can store the last modified time of this record.
Attributes of the data type
MySQL keyword
meaning
NULL
Data columns can contain NULL values
NOT NULL
Data columns are not allowed to contain NULL values
DEFAULT
The default value
PRIMARY KEY
A primary key
AUTO_INCREMENT
Auto-increment, for integer types
UNSIGNED
unsigned
CHARACTER SET name
Specify a character set
Using MySQL database
Log in to MySQL
When the MySQL service is running, you can log in to the MySQL database using the MySQL client tool. First, open the command prompt and enter the following name:
Mysql -h host name -u user name -p
- -h: This command is used to specify the host name of the MySQL client to log in to. This parameter can be omitted when you log in to the current host.
- -u: indicates the user name to log in to.
- -p: tells the server that a password will be used to log in. You can omit this option if the password is empty.
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 log in. If the password does not exist, press Enter to log in. By default, user root does not have a password. Welecome to the MySQL Monitor Welecome to the MySQL Monitor “.
Then the command prompt will wait for the command input with mysql> and a blinking cursor. Enter exit or quit to log out.
Create a database
To create a database, use the create DATABASE statement. The command is in the following format:
Create database Database name [other options];
For example, to create a database named samp_db, run the following command on the command line:
create database samp_db character set gbk;
To facilitate the display of Chinese at the command prompt, the database character encoding is specified as GBK through character Set GBK at creation time. Query OK, 1 row affected(0.02 SEC) will be responded upon successful creation.
Note: MySQL statements start with semicolons (;) At the end of the statement, if you do not add a semicolon to the end of the statement, the command prompt will prompt you to continue typing (there are some exceptions, but semicolons are always correct).
You can use show databases; Command to view which databases have been created.
Select the database to operate on
To operate on a database, you must select the database first, otherwise an error will be displayed:
ERROR 1046(3D000): No database selected
Choose between two ways to use the database:
Run the mysql -d command to select the database name -h host name -u user name -p
For example, run the mysql -d samp_db -u root -p command to log in to the newly created database
2. Use the use statement to specify the database name after login.
The use statement can be used without a semicolon (;). Run the use samp_db command to select the newly created Database
Create a database table
Use the create TABLE statement to create a table.
Create table table name (column declaration);
Select * from students; select * from students; select * from students; select * from students; select * from students;
Create table students (ID int unsigned not NULL auto_increment primary key, name char(8) not null, sex char(4) not null, age tinyint unsigned not null, tel char(13) null default "-" );Copy the codeLong statements can be mistyped at the command prompt, so you can use any text editor to enter the statement and save it to a file createTable.sql, and execute the script through file redirection at the command prompt.
Mysql -d samp_db -u root -p < createTable.sql
(Note: 1. If the remote host is connected, please add the -h command; 2. Specify a full path for the createTable. SQL file if it is not in the current working directory.
Statement explanation:
Create table tablename(columns) is the command to create a database table. The name of the column and the data type of the column are completed in parentheses.
Id, name, sex, age, and TEL are the names of each column, followed by a description of the data type, separated by commas (,).
Id int unsigned not NULL auto_increment primary key
- “Id” is the name of the column;
- “Int” specifies that the type of the column is int(the value ranges from -8388608 to 8388607). The value of the column ranges from 0 to 16777215.
- “Not NULL” indicates that the value of this column cannot be null and must be specified. If this attribute is not specified, the value can be null by default.
- The value of “auto_increment” is used in integer columns. If the column is NULL, MySQL will automatically generate a value with a unique identifier larger than the existing value. There can be only one such value in each table and the column must be an indexed column.
- “Primary key” indicates that this column is the primary key of the table. The value of this column must be unique. MySQL will automatically index this column.
The char(8) below indicates that the stored character length is 8, the tinyint value ranges from -127 to 128, and the default attribute specifies the default value when the column value is null.
MySQL > alter database
Insert data into a table
Insert statements can be used to insert one or more rows of data into a database table, using the following general form:
Insert [into] table name [(表名 1, 表名 2, 表名 3,…)] Values (1, 2, 3…) ;
Insert a record into the STUDENTS table in the SAMp_DB database, execute the following statement:
Insert into students values(NULL, “王刚”, “男”, 20, “13811371377”);
If Query Ok is displayed after press Enter, 1 row affected (0.05sec) indicates that the data was successfully inserted. If the database fails to be inserted, check whether the database you want to operate has been selected.
Sometimes we need to insert only part of the data, or not in column order, we can use the form:
Insert into students (name, sex, age) values(” 中 华”, “女”, 21);
Query the data in the table
Select statement is used to retrieve data from a database according to certain query rules.
Select table name from table name [query condition];
For example, to query the names and ages of all students in the STUDENTS table, enter the statement select name, age from students; The result is as follows:
mysql> select name, age from students; + + -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | age | + -- -- -- -- -- -- -- - + -- -- -- -- -- + | wang gang 20 | | | li-hua sun 21 | | | yong-heng wang 23 | | | chun-chieh cheng 19 | | | garrel 22 | | | Zhang Weipeng | 21 | + -- -- -- -- -- -- -- - + -- -- -- -- -- + 6 rows in the set (0.00 SEC) mysql >Copy the codeYou can also use the wildcard * to query all contents of the table, such as select * from students;
Query by specific criteria:
The where keyword is used to specify query conditions. The format is as follows: select column name from table name WHERE condition;
For example, enter the following query statement: select * from students where sex=” female “;
The WHERE clause supports not only the “where column name = value “query form, but also general comparison operators such as =, >, <, >=, <,! = and some extension operators are [not] null, in, like, and so on. You can also use A combination of OR and query criteria. You’ll learn more about more advanced conditional query methods in the future, which I won’t cover here.
Example:
Select * from students where age > 21; select * from students where age > 21;
Select * from students where name like “% wang %”;
Select * from students where id<5 and age>20; select * from students where id<5 and age>20;
Update the data in the table
Update statements can be used to modify data in a table. The basic form of use is:
Update table name set column name = new value WHERE update condition;
Example:
Update students set tel=default where id=5; update students set tel=default where id=5;
Update students set age=age+1;
Update students set name=” update students “, age=19 where tel=”13288097888″; update students set name=” update students “, age=19 where tel=”13288097888”;
Delete data from a table
The delete statement is used to delete data from a table.
Delete from table_name where table_name = 1;
Example:
Delete from students where id=2; delete from students where id=2;
Delete from students where age<20; delete from students where age<20;
Delete from students; delete from students;
Table changes after creation
The alter TABLE statement is used to alter a table after it has been created.
Add columns
Alter table name add column name [after insert position];
Example:
Alter table students add address char(60);
Alter table students add birthday date after age;
Modify the column
Alter table table name change column name new column name new data type;
Example:
Alter table students change tel telphone char(13) default “-“;
Alter table students change name name char(16) not null;
Delete the column
Alter table drop column name;
Example:
Alter table students drop birthday;
Rename table
Alter table name rename new table name;
Example:
Alter table students rename workmates; alter table students rename workmates;
Delete the entire table
Drop table Specifies the name of a table.
Example: Drop table workmates;
Delete the entire database
Drop database Specifies the database name.
Example: drop database samp_db;
The appendix
Change the password of user root
In this document, the root user does not have a password by default. There are many ways to reset the password of the root user.
Using mysqladmin:
Run the mysqladmin -u root -p password command to open the cli
Enter the old password. If the old password is empty, press Enter to confirm the password change.
Visual management tool MySQL Workbench
Although we can in the command prompt or through a line of input through the redirection file to execute the mysql statement, but the way efficiency is low, because there is no grammar automatically check, before the execution of the input errors caused by the possibility of some mistakes will increase greatly, then try some visual mysql database management tools, MySQL Workbench is a visual management tool for MySQL, you can directly manage the content of the database through a visual way. And the SQL script editor for MySQL Workbench supports syntax highlighting and syntax checking as you type, which is certainly more powerful than that.
MySQL Workbench official introduction: www.mysql.com/products/wo…
MySQL Workbench download page: dev.mysql.com/downloads/t…
= = =