4, Eighth week-network programming advanced-MySQL database operation

A database is a warehouse that organizes, stores, and manages data according to its structure, and each database has one or more different APIs for creating, accessing, managing, searching, and replicating the saved data. Here are some terminology about databases:

• Database: The database is a collection of some associated tables.
• Data table: A table is a matrix of data. Tables in a database look like a simple spreadsheet.
• Columns: A column (the data element) contains the same data, such as the postal code.
• Row: a row (= tuple, or record) is a set of related data, such as a user's subscribed data.
• Redundancy: Storage of two copies of data, redundancy can make the system faster. For example, the same fields in different tables (the more normalized the table, the more relationships between the tables and the table), and the query may often require connection queries between multiple tables, while a connection operation can slow down the query.
• Primary key: The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data.
• FOREIGN key: A foreign key is used to correlate two tables.
• Composite key: Composite key (key combination) Multiple columns are used as an index key, and are typically applied to composite indexes.
• Index: Use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.
• Referential integrity: Referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.

Common commands for Mysql management:

Use database name: Select the MySQL database you want to manipulate, and all MySQL commands are only for that database after using this command. SHOW DATABASES: Lists a list of databases for the MySQL database management system. SHOW TABLES: #显示指定数据库的所有表, you need to use the use command to select the database you want to manipulate before using this command. SHOW COLUMNS from data table: #显示数据表的属性, property type, primary key information, whether null, default value, and other information. Create DATABASE TestDB charset "UTF8"; #创建一个叫testdb的数据库, and let it support the Chinese drop database testdb; #删除数据库SHOW index from data table: Displays detailed index information for the data table, including primary key (primary key).

MySQL Database type

The type of data field defined in MySQL is important for optimizing your database.
MySQL supports multiple types and can be broadly divided into three categories : numeric, date/time, and string (character) types.

Numeric type
MySQL supports all standard SQL numeric data types.
These types include strict numeric data types (INTEGER, SMALLINT, Decimal, and numeric), as well as approximate numeric data types (FLOAT, real, and double PRECISION).
The keyword int is a synonym for integer, and the keyword Dec is a synonym for decimal.
The bit data type holds the bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension of the SQL standard, MySQL also supports integer types tinyint, Mediumint, and bigint. The following table shows the storage and scope of each integer type that is required.

type	size	Range (signed)	Range (unsigned)	Use
TINYINT	1 bytes	(-128,127)	(0,255)	Small integer value
SMALLINT	2 bytes	(-32 768,32 767)	(0,65 535)	Large integer value
Mediumint	3 bytes	(-8 388 608,8 388 607)	(0,16 777 215)	Large integer value
int or integer	4 bytes	(-2 147 483 648,2 147 483 647)	(0,4 294 967 295)	Large integer value
BIGINT	8 bytes	(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)	(0,18 446 744 073 709 551 615)	Maximum integer value
FLOAT	4 bytes	( -3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38)	0, (1.175 494 351 e-38,3.402 823 466 e+38)	Single precision Floating point value
DOUBLE	8 bytes	(1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+3 08)	0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)	Double precision Floating point value
DECIMAL	For decimal (m,d), if m>d, is m+2 otherwise d+2	Values that depend on M and D	Values that depend on M and D	Decimal value

Date and Time type
The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.
Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.
The timestamp type has a proprietary Automatic Update feature, which is described later.

type	size (bytes)	Range	format	Use
DATE	3	1000-01-01/9999-12-31	Yyyy-mm-dd	Date value
Time	3	' -838:59:59 '/' 838:59:59 '	HH:MM:SS	Time Value or duration
Year	1	1901/2155	YYYY	Year value
Datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Blend date and time values
TIMESTAMP	4	1970-01-01 00:00:00/2037, sometime	YYYYMMDD HHMMSS	Mixed date and time values, timestamp

　　

The

String type
String type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

type	size	purpose
CHAR	0-255 bytes	fixed length string
VARCHAR	0-65535 words Section	variable length string
tinyblob	0-255 bytes	No more than 255 characters of binary string
tinytext	0-255 bytes	short text string
BLOB	0-65 535 bytes	long text data in binary form
text	0-65 535 bytes	long text data
mediumblob	0-16 777 215 bytes	binary Form medium-length text data
mediumtext	0-16 777 215 bytes	medium-length text data
longblob	0-4 294 967 295 bytes	binary form of large text data
longtext	0-4 294 967 295 bytes	Extreme Text data

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.
Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string. This means that they do not have a character set, and sort and compare numeric values based on column-valued bytes.
A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.
There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

MySQL CREATE TABLE, query, modify Operation

Create a table syntax:

Create Table name (Field 1, Field 2)

Such as:

CREATE TABLE student (   stu_id int not null auto_increment,   name CHAR (+) NOT NULL,   age  int. NOT NULL,   R Egister_date date,   PRIMARY KEY (stu_id));

Annotations:

• If you do not want the fields to be null, you can set the field's property to NOT NULL, and you will get an error if the data entered in the field is null when you manipulate the database.
• Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.
• The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.

Insert Table Syntax:

INSERT into table_name (field1, Field2,... fieldn)                       VALUES                       (value1, value2,... Valuen);

Mysql Select query

Grammar

SELECT column_name,column_name from table_name [WHERE Clause] [OFFSET M][limit N]

MySQL WHERE clause condition query

Grammar

SELECT field1, Field2,... fieldn from table_name1, table_name2 ... [WHERE condition1 [and [OR]] condition2 .....

Where to query common operators:

operator	Description	Example
=	Equals, detects if two values are equal, returns true if equal	(A = B) returns FALSE.
<>! =	does not equal, detects whether two values are equal if not equal returns True	(A! = B) returns TRUE.
>	Greater than sign, detects if the left value is greater than the right value, and returns True if the left value is greater than the right value	(A > B) returns FALSE.
<	Less than sign, detects if the left value is less than the right value, and returns True if the left value is less than the right value	(A < B) returns TRUE.
>=	Greater than equals sign, detects if the left value is greater than or equal to the right value, if the left value is greater than or equal to the right value returns True	(A >= B) returns false.
<=	Less than equals sign, detects if the left value is less than or equal to the right value, if the left value is less than or equal to the right value returns True	(A <= B) returns True.

Mysql UPDATE Query

Grammar:

UPDATE table_name SET field1=new-value1, Field2=new-value2[where Clause]

MySQL DELETE Statement
Grammar:

MySQL like clause (Fuzzy matching query)

Grammar:

SELECT field1, Field2,... fieldn table_name1, table_name2 ... where field1 like Condition1 [and [OR]] filed2 = ' somevalue ' such as: Select *from student where name binary as "%li"; Select *f Rom student where name binary like  binary "%li"; #只匹配大写

Mysql sort (default to primary key ID number Ascending)

Grammar:

SELECT field1, Field2,... fieldn table_name1, table_name2 ... ORDER by field1, [field2 ...] [ASC [Desc]]select *from student where name like binary '%li ' ORDER by stu_id DESC; using the ASC or DESC keyword to set the query result is sorted in ascending or descending order. By default, it is sorted in ascending order.

MySQL GROUP by statement

Grammar:

SELECT column_name, Function (column_name) from Table_namewhere column_name operator Valuegroup by column_name such as: Using GROUP The By statement groups the data table by name and counts how many records each person has:mysql> SELECT name, COUNT (*) from   employee_tbl Group by name;+--------+---------- +| Name   | COUNT (*) |+--------+----------+| Xiao Li |        1 | | Xiao Ming |        3 | | Xiao Wang |        2 |+--------+----------+3 rows in Set (0.01 sec)

MySQL alter command

You need to use the MySQL alter command when you modify the data table name or modify the field of the datasheet.

Delete, add, or modify a table field

ALTER TABLE student drop register_date; #从student表删除register_date字段alter table Student Add phone int (one) not null; # Add Phone field

Modify field type and name

If you need to modify the field type and name, you can use the MODIFY or change clause in the ALTER command. For example, to change the type of field C from char (1) to char (10), you can execute the following command:mysql> ALTER TABLE testalter_tbl MODIFY C CHAR (10);

Syntax differs greatly by using the change clause

After the Change keyword, follow the name of the field you want to modify, and then specify the new field name and type. Try the following example:mysql> ALTER TABLE TESTALTER_TBL change i j bigint;mysql> ALTER TABLE TESTALTER_TBL Change J J INT;

4, Eighth week-network programming advanced-MySQL database operation