Mysql for Databases

Mysql for Databases
Preparation

Ubantu system, navicat for mysql software

Introduction

Mysql is designed based on the c/s communication architecture. Users interact with the server through the client, and the server operates on the database. Simply put, we send SQL statements to the client. The client translates SQL statements into commands that can be understood and executed by the server, and the server performs query operations on the database according to these commands, send the operation result to the client, and then translate the result into the user content that we can understand. One of the advantages of the c/s architecture is that users do not need to deal with the server, that is, users do not need to learn complex server-side commands, you only need to learn simple SQL statements and client commands. The client will help us translate commands. Mysql's c/s architecture is a server that can correspond to multiple clients. You do not need to know how to differentiate different mysql users.

Mysql is a type of relational database, so we must grasp the relationship between objects when designing the database. When designing a website in the future, different business sections should have a separate architecture or database. we can regard a separate section as a separate website.

Install mysql

Command: sudo apt install mysql-server

Configure mysql

Modify the/etc/mysql/my. cnf file and add the following statements ([] indicates the Group) to the end of the file)

[Client]

Default-character-set = utf8 # specify the client encoding. Because the default latin1 does not support Chinese characters, select utf8 here.

 

[Mysqld]

Character-set-server = utf8 # specify the server-side encoding, which is usually set to be consistent with the client-side encoding.

Collation-server = utf8_general_ci # The same encoding also has different sorting rules

Bind_address = 0.0.0.0 # The first layer daemon listens to all IP addresses

Backup the configuration file before modification. After modification, restart the configuration file to read the configuration file again.

Check the encoding and sorting information after setting.

Start mysql

Command: sudo service mysql restart or sudo systemctl restart mysql

Note: The mysql service process name is mysql, so the command is not sudo service mysqld restart. we generally do not directly use the mysqld program name to start the program, because this program may need some complex parameters, we can use systemctl and service to easily process related commands.

Navicat connects to mysql

Run the following three commands on the mysql client:

You can also use the following two commands:

Database creation

View existing DATABASES: show databases;

Create database: create database 'test' charset utf8 (character set utf8 );

View the DATABASE creation process: show create database 'test ';

Delete DATABASE: drop database 'test'

Modify DATABASE encoding: alter database 'test' charset utf8

View database version: status/select version ()

Back up and restore databases and create tables

The table in the database is equivalent to a two-dimensional excel table at the user layer.

View the TABLE creation process: show create table 'mysql'. 'user ';

Create table: create table 'test'. 'user' (column1 type1 option, colum2 type2 option );

View database TABLES (when selected): show tables;

Move the database TABLE with duplicate names: rename table 'python'. 'user' TO 'test'. 'user'

Drop table 'test'

Note:

• Do not add a comma to the last row of the table to be created. The sorting collate = utf8_general_ci is case insensitive. The query speed is high. The collate = utf8_genaral_bin is case sensitive and the sorting speed is slow; each table degree requires a primary key (the primary key is unique and not empty); char () is assigned a fixed length, varchar () is variable in maximum length, and the performance is relatively poor
• Database Engine: 1. myisam non-transactional, fast query 2. innodb Transaction, fast modification, default engine (powerful)
• Database tables, columns, and databases are the same. You can set character sets and sorting rules separately.
• If the column is not specified, the default value is null by default.
• If character set encoding is not specified, there will be inheritance: column search table encoding, table Search Library encoding, library search configuration file encoding
• Each field in the database must have a fixed type.
• One rule in mysql is that the name (the Field name is also the name) must be enclosed in quotation marks.
• \ C cancel command (cancle) \ G display column data row by row
• The primary key is unique and not empty, but unique and non-empty is not necessarily the primary key.
• A routine for table creation is to use an id without actual meaning as the primary key.
• Which fields can be empty during registration and which can be empty based on actual needs?
• AUTO_INCREMENT must be used in combination with INT. With AUTO_INCREMENT, you can insert data without specifying an id.
• Only keywords such as ALTER, RENAME, and CREATE must be connected to the DATABASE and TABLE.
• There are three relationships between entities: one-to-one, one-to-multiple, and multiple-to-many. The three relationships can be distinguished by drawing.
• One-to-multiple: UseForeign keyConstraints to achieve
• One-to-one: Using Foreign keys + UNIQUE keys
• Many-to-many: Using Foreign keys + third-party tables

 1 CREATE TABLE `users` ( 2     `id` int PRIMARY KEY AUTO_INCREMENT, 3     `email` char(50) UNIQUE KEY NOT NULL, 4     `user_name` varchar(20) NOT NULL, 5     `telphone` char(11)     6 ); 7  8 CREATE TABLE `sellers` ( 9     `id` int PRIMARY KEY AUTO_INCREMENT,10     `user_id` int UNIQUE KEY NOT NULL,11     FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)12 );13 14 CREATE TABLE `houses` (15     `id` int PRIMARY KEY AUTO_INCREMENT,16     `summary` varchar(100) NOT NULL,17     `seller_id` int NOT NULL,18     FOREIGN KEY (`seller_id`) REFERENCES `seller`(`id`)19 );20 21 CREATE TABLE `sellers_tags`(22     `sellers_id` int NOT NULL,23     `tag_id` int NOT NULL,24     FOREIGN KEY (`sellers_id`) REFERANCES sellers(`id`),25     FOREIGN KEY (`tag_id`) REFERANCES tags(`id`),26     UNIQOE KEY(`sellers_id`, `tag_id`)  # PRIMARY KEY(`sellers_id`, 27 28 `tag_id`)29 );30 31 CREATE TABLE `tags` (32     `id` int PRIMARY KEY AUTO_INCREMENT,33     `name` varchar(20) UNIQUE KEY NOT NULL34 );

View Code table information data type

MySQL supports multiple data types, including numeric, date/time, and string.

YEAR's DATE format is YYYY, TIME's DATE format is HH: MM: SS, DATE's DATE format is YYYY-MM-DD, DATETIME's DATE format is YYYY-MM-DD HH: MM: SS, the date format of TIMESTAMP is YYYY-MM-DD HH: MM: SS.

The parameter in string brackets indicates the number of bytes.

Key and Index

In mysql, keys can be interpreted as indexes, and indexes are some columns, which consume extra space to maintain an efficient data structure and speed up searching. Note: acceleration is only valid for specified columns.

Function key (constraint key): 1. Primary Key 2. Unique key 3. Foreign key

Non-function key (INDEX)

Add record (ROW) to delete record (ROW)

Note: before deletion, we usually need to pay attention to ourWHEREAt the same time, we recommend that you use the sameWHEREOfSELECTFirst Query

Record (ROW) Modification

UPDATE 'users' SET 'name' = 'jack' WHERE pattern

Note: before deletion, we usually need to pay attention to ourWHEREAt the same time, we recommend that you use the sameWHEREOfSELECTFirst Query

Query records (rows)

Note: grouping query is meaningful only when used in conjunction with Aggregate functions.

Multi-Table query

• Inner join (cross join)

SELECT's _ name', 'c _ flower 'FROM 'class' inner join 'students'ON 'students'. 'C _ id' = 'class'. 'C _ id' WHERE 'students'. 'C _ id' = 9;

Note: on is the connection condition, and where is the filtering condition. That is to say,ONConditions used to connect meaningful data rows,WHEREYes. After the connection is established, filter out the required data.

• Outer Join (left join and right join)
  

SELECT * FROM 'classs' left join 'students'ON 'students'. 'C _ id' = 'class'. 'C _ id'

Note: The left join is based on the left table, and the right join is based on the right table. What is different from the internal connection is that the external connection must use the ON connection condition.