MYSQL database concepts and sql statements

What is data

Number: digital information

Data: attribute or some kind of credential

Data: A collection of information describing specific attributes of an object.

What is a database

Database: It is a warehouse used to organize (organized according to rules), store and manage (add, delete, modify and query data) data.

Databases are important information assets for enterprises. Be careful when using the database (It doesn’t matter if you check or add, but be careful when deleting and modifying!)

Database management system (DBMS): system software that implements effective organization, management and access of data

mysql working process and data flow diagram:

Database composition

A database is a system, which is a human-machine system, hardware, os operating system, database, DBMS management system and database user.

Users operate the database through DBMS (various database software)

Classification of databases

mysql: relational database

redis: non-relational database es

Relational database

The structure that stores data is a two-dimensional table

Table: row column

Line: record, used to display information about an object

Column: Field, used to describe all attributes of the object

mysql (concurrent requests for small and medium-sized databases, free)

oracle (use it for large amounts of data)

sql-server (obsolete)

maria DB

postgresql

In relational database. The data in the table is related, and the data between tables is also related.

Non-relational database

Saving data is not a table. It is saved in the form of key-value pairs. There is no correlation between key—->value ky32=12 itself

redis: caching database

es: index database

MongDB: document database

Since there are related queries, the speed is relatively speaking:

Relational database queries are slower

Non-relational databases can support high concurrent reading and writing. It still maintains efficient storage and access to massive data.

Database architecture commonly used by enterprises:

Storage engine of MYSQL database

Before 5.5: MYSAM, a storage engine that does not support transaction foreign keys. Suitable for reading more and writing well.

The default storage engine after 2.6: inodb, a storage engine that can support transactions and can support foreign keys and row-level locking. Support applications with high concurrent performance.

Transactions

Transaction: In a database, a sequence of data operations consisting of one or more operations. Either all of these operations succeed, or none of them are executed if one fails. Ensure data consistency and integrity.

Characteristics of transactions

Atomicity: The smallest unit of work in the database, either all of them are executed successfully or none of them are executed. As long as one operation fails, the entire execution sequence will be rolled back. Even completed operations will be undone.

Consistency: The integrity of the database cannot be destroyed before and after transaction execution. A transaction can be committed only if all constraints are met.

Isolation: Things are executed in isolation from each other. The execution of a transaction cannot be interfered with by other execution transactions. Isolate concurrent transactions from each other. Prevent data inconsistencies from occurring.

Persistence: Once a transaction is committed, all modifications made by it will be permanently saved in the database. Even if the system crashes, the submitted database will not be lost.

Ensure that the database is always in a consistent state and the data is complete and consistent

mysql noun

1. Database: database

2. Table: tab row: row column: column

3. Index: index

4. View: view

5. Stored procedure: procedure

6. Trigger: trigger

7. User: user

8. Permission: privilege

mysql statement specifications

In the database system, sql is not case-sensitive, but uppercase letters are recommended

Statements are not case-sensitive, table names are strictly case-sensitive

SQL statements can be written in a single line or in multiple lines, but by default they all end with;

Rules for line breaks

Keywords cannot span lines or be abbreviated

Sub-statements are usually placed in the path to facilitate editing and improve readability.

Database naming rules

It must start with a letter and can contain data afterwards. Special characters: #, _ and $

Do not use MySQL reserved words to name: table, select, etc.

Database names, table names, and user names are all strictly case-sensitive.

Character type of database

Commonly used character types:

1. init: occupies 4 bytes. Used to store integers.

2. char: fixed-length string. Used to store fixed-length strings.

3. varchar: variable-length character type. Used to store variable length strings. (You can write whatever you want without any restrictions, and it also depends on the length of the type)

4. float (m, d): single-precision floating point, storing floating point numbers, m represents the total number of digits, d represents the decimal place

5. duble (m, d): double-precision floating-point number, which stores floating-point numbers. m represents the total number of digits and d represents the decimal place.

6. text: used to store large text data. document or long string.

7. image: binary storage image. Pictures, multimedia, etc.

8. decimal (5, 2): used to store fixed-precision decimals, where 5 represents the total number of digits and 2 represents the decimal place.

9. date: storage date: yyyy-mm-dd

10. datetime: storage log YYY-MM-DD HH:MM:SS

11. Timestamp: The format is consistent with datetime and can be automatically updated to the current timestamp.

Key points:

char: ‘space’ is fixed to 4 bytes. ‘ab’ ‘ab ‘ 4 bytes

varchar: ‘space’ takes up only 1 byte. ‘ab’ 3 bytes

Char: Whether you define a value or not, it will occupy a fixed length of bytes. If you set 8, even if you only write 4, it will be counted as full 8

varchar: When saving, the actual length of varchar 14 is 5, so it takes up 5, plus a hidden character, a total of 6

varchar saves disk space than char, but varchae reads and writes slower than char

Classification of sql statements

DDL statement

DDL: Data Definition Language, used to create database objects. Create libraries, tables, and indexes. All belong to data definition language. CREATE DROP ALTER

CREATE: create

DROP: delete

ALTER: Modify

show global variables like 'port';
#View the port currently used by the database

show databases;
#Check how many libraries there are in the current database

use mysql; #Use library

show tables; #View the tables in mysql

describe user;
#View table structure
desc user;
#abbreviation

describe user\G;
#viewvertically
desc user\G;
#abbreviation

create database yy;
#Create a library named yy

show databses;
#Check how many libraries there are

use yy;
#View library yy

create table yy (id int(4) not null,name varchar(10) not null,sroce decimal(5,2),passwd char(48),primary key(id));
#Create table

show databases;

drop tables yy;
#Delete table

drop database yy;
#delete database

desc yy;
#View table structure

The difference between null and space

null means nothing, the object does not have any description information.

Space: also a character

insert into yy values(1,’zyg’,null,’ ‘);

select * from yy where sroce is not null;

DML statement

Data operation statements to manage data in the table

select: View table format
select * from table name;
#View table format

update: update
update zyg set passwd=password('000') where id =4 ;

insert：Add

insert into zyg (id,name,sroce,passwd) values(1,'Squidward',90,'123');

insert into zyg values(2,'Squidward 2',95,'456');

insert into zyg values(4,'Squidward 4',95,password('666'));
#Encryption processing password

delete: Delete the table

delete from zyg where id = 5;

DQL statement (asked in interview)

Query data records

select

select * from zyg where id = 8;

select * from zyg limit 4,7;
#Start from the fourth row and go down 3 rows

select * from zyg limit 2;
#Display the first 2 lines

select distinct sroce from zyg;
#Remove duplicate queries. Compress duplicate behavior to 1 line

select distinct id,name,sroce from zyg;
#idduplication

select distinct name,sroce from zyg;
#Remove all conditions

select distinct name from zyg;
#nameremoval

alter table original table name rename new table name;
#Change table name

alter table zyg add birth date;
#Add new information

update zyg set birth=date('2000-8-24') where id = 3;
#Update information

alter table zyg change birth birthday datetime;
#Change table information

alter table zyg drop birthday;
#Delete table information

alter table zyg modify column passwd varchar(52);
#Modify field type

DCL statement

Data control language, set or change database user permissions

GRANT: Grant permissions

REVOKE: cancel permission

TCL statement

Transaction control statements manage transactions in the database.

commit: confirm the commit transaction

ROLLBACK: Cannot rollback after committing transaction

savepoint: Save point, can be rolled back.

MYSQL extended statements and constraints, user management

MYSQL extended statement

create table if not exists bqb (
id int(4) zerofill primary key auto_inc rement,
name varchar(10) not null,
cradid int(10) not null unique key,
hobby varchar (50)
);

id int(4) zerofill primary key auto_inc rement，
 #Indicates that the field can grow automatically. By default, each record starts from 1 and will automatically increase by 1.

if not exists bqb: The bqb table will be created only if it does not exist.

zerofill: automatically fill position 1 0001

Primary key: The primary key of the current table. There can be only one primary key, which is unique and cannot be empty.

auto_increment: Indicates that the field can grow automatically. It starts from 1 by default, and each record will automatically increase by 1.

upique key: unique constraint. It has nothing to do with the primary key and can be empty.

create table test like bqb;
#Copy table results. Directly copy the table structure of bqb through the like syntax. Just copying the table structure cannot copy the data in the table

insert into test select * from bqb;
#Copy table data. Copy the data in the bqb table to the test table. The data structure of the two tables must be consistent.

create table test1 (select * from bqb);
#Copy directly. Create a table, test1, the data comes from bqb, and the table results also come from bqb;

create table test like bqb;
#Copy table results. Directly copy the table structure of bqb through the like syntax. Just copying the table structure cannot copy the data in the table

delete or truncate or drop: clear the table. Delete all data in the table

delete from test;
#delete deletes rows one by one. If there are sub-increment columns in the table, after clearing all records and adding content again, the original records will continue to be written after the original records

truncate table test;
#Generally use truncate to clear at work. Clears the table data and rewrites the table structure, which is faster than delete. recommend.

drop table test1;
? #Delete the table directly (not recommended)

Create temporary table

Temporary tables are generally used for debugging, and the temporary tables are not displayed in the table directory after they are created. After the connection exits, the temporary table will be destroyed, and the temporary table cannot create foreign keys.

create temporary table test1 (
id int(4) primary key,
name char(10),
sex char(2)
);
#Create temporary table

MYSQL constraint methods

6 commonly used constraints:

1. Primary key constraints. For each row used to uniquely identify a table type, the value of the primary key column must be unique and cannot be empty. There can only be one primary key for a total table type.

2. Foreign key constraints. Used to establish a relationship between tables, usually associated with the primary key of another table. Ensure the integrity of data references, a table can have multiple foreign keys

3. Non-null constraint. not null must have a value.

4. Uniqueness constraint. unique ensures that all values in the column are unique. Similar to primary key, but can be empty. And a table can have multiple unique constraints.

5. Default value constraints. default When inserting table data, if no value is defined, a default value will be provided.

6. Self-increasing constraints. A unique identifier is automatically generated for each row. Usually used with primary keys.

Primary key constraints

Primary table:

create table student (
card_id int(18) primary key,
stud_name varchar(12) not null,
stud_email varchar(255) unique
);

Table:

create table class (
stud_id int(11) auto_increment primary key,
address varchar(50) default 'unknown address',
card_id int(18) not null,
foreign key(card_id) references student(card_id)
);

The primary key of the primary table must be consistent with the foreign key of the secondary table, otherwise an error will be reported

show create table class;
#View related relationships

Master table and slave table:

If you want to insert data. Insert into the master table first, then insert into the slave table.

delete data. Delete the master table first, then delete the slave table

alter table class drop for foreign key class_ibfk_1;
?#Delete the foreign key from the table

MUL: Indicates that this is an index.

alter table class drop index card_id;
?#Delete the index from the table

alter table class drop primary key;
#Delete primary key

alter table class modify stud_id int(12);
#Delete self-increasing constraints

alter table class drop primary key;
#Delete primary key

Exercise questions

Define a requirement:

1. Two tables: school

de_id int(4) must be completed if it is less than four digits, and it will grow automatically. Primary key
name VARCHAR(15) not NULL email varchar(45) cannot be empty and cannot be repeated
money int cannot be empty or repeated

2. cloud_ky32

id auto-increment primary key int class_name cannot be empty.
de_id foreign key, the foreign key is associated with the primary key of the main table. adress can be empty, the default is ‘unknown address’ phone int cannot be empty, and cannot be repeated.
3. Insert 10 pieces of data into two tables respectively
4. Add a column to the second table, hobby.
5. Modify the name type of the main table to char(15)
6. Change the table name of cloud_ky32 to ky2
7. Modify the description of hobby to hob
8. Through the command line, change the initial value of money in the first row of the main table from 1000 to 900.

Main table: school

create table school (
de_id int(4) zerofill auto_increment primary key,
name varchar(15) not null,
email varchar(45) not null unique,
money int(255) not null unique
);

desc school;

From table: cloud_ky32

create table cloud_ky32 (
id int(4) auto_increment primary key,
class_name varchar(5) not null,
de_id int(4) zerofill,
adress varchar(50) unique,
phone int(20) not null unique,
foreign key(de_id) references school(de_id)
);

desc cloud_ky32;

Insert 10 pieces of data into two tables respectively
Main table: school
insert into school values(1,'yy1','1','1');
insert into school values(2,'yy2','2','2');
insert into school values(3,'yy3','3','3');
insert into school values(4,'yy4','4','4');
insert into school values(5,'yy5','5','5');
insert into school values(6,'yy6','6','6');
insert into school values(7,'yy7','7','7');
insert into school values(8,'yy8','8','8');
insert into school values(9,'yy9','9','9');
insert into school values(10,'yy10','10','10');

From table: cloud_ky32
insert into cloud_ky32 values(1,'yy1','1','1','123');
insert into cloud_ky32 values(2,'yy2','2','2','1234');
insert into cloud_ky32 values(3,'yy3','3','3','12345');
insert into cloud_ky32 values(4,'yy4','4','4','123456');
insert into cloud_ky32 values(5,'yy5','5','5','1234567');
insert into cloud_ky32 values(6,'yy6','6','6','12345678');
insert into cloud_ky32 values(7,'yy7','7','7','123456789');
insert into cloud_ky32 values(8,'yy8','8','8','1234567890');
insert into cloud_ky32 values(9,'yy9','9','9','1234567891');
insert into cloud_ky32 values(10,'yy10','10','10','1234567892');

Add a column hobby to the second table
alter table cloud_ky32 add hobby varchar(50);

desc cloud_ky32;

Modify the name type of the main table to char(15)
alter table school modify column name char(15);

desc school;

Change the table name of cloud_ky32 to ky2

alter table cloud_ky32 rename ky2;

desc ky2;

Modify the listing of hobby to hob
alter table ky2 change hobby hob varchar(51);

desc ky2;

Through the command line, change the initial value of money in the first row of the main table from 1000 to 900.
update school set money='1000' where de_id = 1;
update school set money='900' where de_id = 1;


desc school;

Create table