MySQL: database entry, mysql database entry

MySQL: database entry, mysql database entry

1. What is a database? -- Data storage warehouse

Database technology is a very important technology in the computer application field. It was developed at the end of 1960s and is the latest technology of data management and an important branch of software technology.

To put it simply,DatabaseA data warehouse is a data warehouse that organizes and stores data according to a certain data structure (the data structure refers to the organization form of data or the relationship between data, we can manage the data in the database through multiple methods provided by the database. In a simpler image, databases are the same as warehouses that store debris in our daily lives. The difference is that they store different things.

2. What is data? -- All you know is data.

The information stored in the table is called data.

3. What is a database table?

A data table is a very important object in a relational database. It is the basis of other objects and a set of two-dimensional arrays used to store and operate the logical structure of data. Based on the information classification.
A database may contain several data tables. Each table is composed of rows and columns. When a data record is set, the data table adds a row. Each column is composed of a field name and a field data set, A column is called a field,
Each column also has its own attributes, such as whether to allow null, default value, length, type, storage encoding, comment, etc.

4. Database composition:

1) Database Server

2) Database Management System

3) Database

4) Table

5) Record

6) data

　　　The database system has three main components:

1. Database System: the place where data is stored.

2. Database Management System (DBMS): software used to manage databases.

3. Database Application: the software used to manage databases to improve the processing capability of the Database system.

5. Environment Installation

MySQL: https://dev.mysql.com/downloads/installer/

A: Download the mysql installation program from the official website. B: Install the mysql server. c: Install the mysql client. d: connect the client to the server. e: send a command to the server through the client, perform the addition, deletion, modification, and subtraction operation on database files. #1. download: MySQL Community Server 5.7.20 https://dev.mysql.com/downloads/mysql/4102.decompress the decompress package downloaded from the official network to the specified directory (such as D: // mysql-5.7.20-winx64) #3. add the environment variable [Right-click the computer]> [properties]> advanced system Settings> advanced> environment variable> Find the variable in the second content box A row named Path, double-click to append the bin directory path of MySQL to the variable value, and separate it with;. C: \ Program Files (x86) \ Parallels Tools \ Applications; d: \ mysql-5.7.16-winx64 \ bin #4. initialize mysqld -- initialize-insecure #5. start mysql service mysqld #6. connect to mysql service mysql-uroot-p and press Enter. If you have a password, enter the password.

Install

 

6. Database Operations

1) Create a database

Create database DB2 charset utf8;

2) Delete

Drop database db2;

3) Use (enter)

Use db1

4) view the database

Show databases; -- View All Database names of the current user
Select DATABASE (); -- view the currently used
Show create database db1; -- view the information of the created DATABASE

7. Table operations

1) create a table

Create table info1 (
Id int not null auto_increment PRIMARY key,
Name VARCHAR (50) not null,
Sex CHAR (2) NULL
)

2) view the table

Select * from info1; -- View table data

DESC info1; -- view the table structure

Show create table info1; -- View table creation Information

3) delete a table

Drop table info;

4) modify a table

-- ALTER table info1 add age int not null; -- add table fields

-- Alter table info1 drop age; -- delete table fields

-- Alter table info1 modify name char (100) null; -- modify table field attributes

-- Alter table info1 CHANGE names name varchar (50) not null; -- modify the table name

Additional information
Alter table info1 add PRIMARY key (ID, names); -- add a PRIMARY key

Alter table info1 drop PRIMARY key; -- delete a PRIMARY key

Alter table info1 alter sex set default 'male'; -- sets the default value.

Alter table info1 alter sex drop default; -- delete default value

5) modify the table name

Rename table info1 to info; -- modify the table name

6) copy a table

1. CREATE table info3 select * from info where id = 1;
Ps: Primary Key auto-increment/index/trigger/foreign key will not be copied
2. Copy the table structure
CREATE table info5 SELECT * FROM info where 1! = 1

CREATE table info4 LIKE info;
Ps: Data/trigger/foreign key will not be copied

8. Data Type

MySQL supports multiple types, which can be roughly divided into four types: numerical value, string type, date/time, and other types.

Binary: bit [(M)] binary (101001). m indicates the length of the binary (1-64). The default value is m = 1. Integer: tinyint [(m)]. [unsigned] [zerofill] small integer. The data type is used to save the range of integer values:-128 ~ 127. Unsigned: 255 Special: MySQL has no Boolean value and is constructed using tinyint (1. Int [(m)] [unsigned] [zerofill] integer. The data type is used to save the range of integer values:-2147483648 ~ 2147483647 unsigned: 4294967295 bigint [(m)] [unsigned] [zerofill] Big integer. The data type is used to save the range of integer values:-9223372036854775808 ~ 9223372036854775807 unsigned: 18446744073709551615 function: storage age, level, id, various numbers, etc. Note: When specifying the width for this type, only the display width of the query result is specified, which is irrelevant to the storage range, therefore, we can use the default value. The maximum display width required by the signed and unsigned numbers is 10, and the minimum value for the signed number must be 11 characters to display completely, so the default display width of int type is 11, which is a very reasonable decimal type: decimal [(m [, d])] [unsigned] [zerofill, m is the total number of digits (not counted as the negative number), and d is the number after the decimal point. The maximum m value is 65, and the maximum d value is 30. Specifically, this type of decaimal is required for exact value calculation because it is stored internally by string. FLOAT [(M, D)] [UNSIGNED] [ZEROFILL] Single-precision floating point number (non-accurate and small value), m is the total number of digits, and d is the number of decimal places. Signed :(-3.402823466E + 38 to-1.175494351E-38), 0, (1.175494351E-38 to 3.402823466E + 38) unsigned: 0, (1.175 494 351 E-38, 3.402 823 466 E + 38) the greater the *** value, the less accurate the *** DOUBLE [(M, D)] [UNSIGNED] [ZEROFILL] double-precision floating point number (non-accurate and small value). m indicates the total number of digits, and d indicates the number after the decimal point. Signed :(-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 7 E + 315) unsigned: 0, (308 073 2.225 858 507 4 E-308, 201 1.797 693 134 862 7 E + 315) * *** the greater the value, the more inaccurate it is. *** role: store complex types such as salary, height, weight, and physical parameters: char (m) the char data type is a string of a fixed length and can contain up to 255 characters. M indicates the length of the string. PS: even if the data length is smaller than m, it will occupy m length. However, during the query, the result will automatically delete the space at the end. Features: Fixed Length, simple and rude, wasting space, fast access speed varchar (m) varchar data types are used for variable-length strings and can contain up to 65535 characters (theoretically acceptable, but after actually exceeding the length of 21845 characters, mysql automatically converts the data type to text type ). M indicates the maximum length of the string that can be saved by the data type. Strings whose length is smaller than the maximum value can be saved in the data type. PS: Specifies the actual content of the varchar data. For example, if 'AB' is used, spaces at the end of the backend are stored. Note that the varchar type will add a prefix of 1-2 bytes before the actual data, this prefix is used to indicate the number of bytes of real data (1-2 bytes represents a maximum of 65535 numbers, which is in line with mysql's maximum row byte limit, that is, enough to use) if the actual data is <255bytes, the prefix of 1Bytes is required (1 Bytes = 8bit 2 ** 8 indicates the maximum number is 255) if the actual data is larger than 255bytes, the prefix of 2Bytes is required (2 Bytes = 16bit 2 ** 16 indicates the maximum number is 65535). Features: Variable Length, precision, and space saving, slow access speed SQL optimization: when creating a table, the type of the fixed length is put forward, and the type of the variable length is put back, such as gender, such as address or description information PS: Although varchar is more flexible to use, however, from the perspective of the performance of the entire system, the processing speed of the char data type is faster, and sometimes it can go beyond the varchar Processing speed: 50%. Therefore, when designing a database, users should consider various factors in order to achieve the best balance between the text and text data types for saving long strings, it can contain a maximum of 65535 (2 ** 16-1) characters. Enumeration type (knowledge): enum An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000 .) example: create table user (name VARCHAR (40), sex ENUM ('male', 'female ', 'unknown'); insert into user (name, sex) VALUES ('Man 1', 'male'), ('Man 2', 'female '), ('Man 3', 'unknown'), ('Man 4 ', 'shill'); PS: Will a person 4 be inserted successfully ?? Set a set column can have a maximum of 64 distinct members. example: create table myset (col SET ('A', 'B', 'C', 'D'); INSERT INTO myset (col) VALUES ('a, d'), ('d, A'), ('a, d, A'), ('a, d, D'), ('d,, d '); DATE/TIME type: DATE value YYYY-MM-DD (1000-01-01/9999-12-31) TIME value or duration HH: MM: SS ('-838: 59: 59 '/'2014: 59: 59') YEAR Value YYYY (838) DATETIME mixed Date and Time Value YYYY-MM-DD HH: MM: SS (1000-01-01 00:00:00/9999-12-31 23:59:59) TIMESTAMP: yyyymmdd hhmmss (2037 00:00:00/) function: stores user registration time, Article release time, employee entry time, birth time, expiration time, and other data types

9. Data Operations
1. Add
-- INSERT into info values (1, 'Korean refer', 'female ');

-- INSERT into info (id, NAMES) values (2, 'Korean refer ');

-- INSERT into info (id, NAMES) values (3, 'Korean refer'), (4, 'Korean refer'), (5, 'Korean refer ');

-- INSERT into info (names, sex) select nameS, sex from info;

2. Delete
-- DELETE from info where id = '11'; -- DELETE specified data

-- Delete from info; -- delete all data in the entire table

TRUNCATE info; -- clear the entire table
3. Change
Update info set sex = 'shill', names = 'Han Xiaoqiang 'where id = 12;

4. Query

4.1 simple query
Select * from person; -- Query all

Select name, SEX from person; -- query by specified field

Select name, SEX as 'gender' from person; -- as indicates that the field alias is used.

Select salary + 200 from person; -- you can perform data column operations.

Select DISTINCT age, name FROM person; -- repeated Query

4.2 conditional Query
1. Operators
Select * FROM person WHERE age> 20;
Select * FROM person WHERE age <= 20;

Select * FROM person WHERE age <> 20;
Select * FROM person WHERE age! = 20;

2. null keyword
Select * FROM person where dept_id is null;

Select * FROM person where dept_id is not null;

Select * FROM person where name = '';

3. logical operators and or

Select * from person where age = 28 and salary = 53000;

Select * from person where age = 23 or salary = 2000;

Select * from person where not (age = 28 and salary = 53000 );

4.3 interval Query

Select * from person where age BETWEEN 18 and 20;

Ps: between... and contains the specified value.
Equivalent to select * from person where salary> = 4000 and salary <= 8000;

4.4 set Query

Select * from person where id = 1 or id = 3 or id = 5;

Select * from person where id not in (1, 3, 5 );

4.5 fuzzy search

Select * from person where name like '% e %'; -- contains the specified parameter
Select * from person where name like '% E'; -- end
Select * from person where name like 'e % '; -- start

Select * from person where name like '_ e %'; -- _ indicates a single character

Select * from person where name like '__';

4.6 sorting Query
Select * from person where age> 30 order by salary desc; -- ASC forward order desc Reverse ORDER

Select * from person order by convert (name USING gbk); -- Chinese sorting