Basic Mysql database knowledge and basic mysql knowledge

Basic Mysql database knowledge and basic mysql knowledge

E-R model (Entity Relationship Model)

• Currently, physical databases are designed according to the E-R model.
• E Indicates entry, Entity
• R indicates relationship, Relation
• Convert an object to a table in the database
• Describes the corresponding rules between two entities, including
  • One-to-one
  • One-to-multiple
  • Many-to-many
• Converting a link to a column in a database table * a row in a relational database is an object

Data Engine

Differences between MyISAM and InnoDB

Tri-paradigm

• After research and summarization of problems in use, some specifications have been put forward for database design. These specifications are called paradigms.
• 1NF: The Column cannot be split.
• 2NF: Unique Identifier
• 3NF: reference the primary key
• Note: The last paradigm is based on the previous one.

Field Type

• Mysql contains many data types, which are mainly listed below
• Number: int, decimal
• String:
  • Char (8): immutable. If not 8 characters are left blank on the right,
  • Varchar (8): variable. If there are not eight, it should be a few,
  • Text
• Date: datetime
• Boolean:
  • Bit (1): 0, 1
  • Bit (2): 00, 01, 10, 11
  • Bit (3): 001,010,100 ,......

Constraints

• Primary key
• Not null
• Unique
• Default
• Foreign key

Remote connection

• Generally, in the development of a company, the database may be set up on a single server. All developers share a database instead of configuring a database on their own computers.
• Run commands
• Mysql-hip address-uroot-p

Database

• Create a database
  • Create database test character utf8 collate utf8_general_ci;
• Delete Database
  • Drop database name;
• Switch Database
  • Use Database Name;

Data Table

• Create a data table
  • Create table create_test (
  • Id int (11) auto_increment primary key not null,
  • Name varchar (20) not null,
  • Birthday datetime,
  • Gender bit default 0,
  • IsDelete bit default 0
  • ) Engine = InnoDB default charset = utf8;

• Modify Table

  Alter table name add | change | drop column name type;
  For example:
  Alter table students add birthday datetime;

• Delete table

  Drop table name;

• View table structure

  Desc table name;

• Change table name

  Rename table original table name to new table name;

• View the table creation statement

  Show create table 'table name ';

Data Operations

• Query

  Select * from Table Name

• Add

  Insert all columns: insert into table name values (...)
  Default insert: insert into Table Name (column 1,...) values (value 1 ,...)
  Insert multiple data records at the same time: insert into table name values (...),(...)...;
  Or insert into Table Name (column 1,...) values (value 1,...), (value 1 ,...)...;

• Primary Key columns are automatically increased, but the placeholder is required during full column insertion. Generally, 0 is used. After successful insertion, the actual data prevails.
• Modify

  Update table name set column 1 = value 1,... where Condition

• Delete

  Delete from table name where Condition

• Logical deletion. The essence is to modify the update operation.

  Alter table students add isdelete bit default 0;
  If you want to delete
  Update students isdelete = 1 where ...;

Clear table

-- Clear all data, do not write logs, cannot be recovered, fast

Truncate TableTable Name;

-- Clears all data, writes logs, and restores data, which is slow.

Delete FromTable Name

View Character Set

Show variables like '% char % ';

Data backup

• Go to super Administrator

  Sudo-s

• Go to the mysql database directory

  Cd/var/lib/mysql

• Run the mysqldump command

  Mysqldump-uroot-p Database Name> ~ /Desktop/backup file. SQL;
  Enter the mysql password as prompted

Data Recovery

• Connect to mysqk and create a database
• Exit the connection and execute the following command:

  Mysql-uroot-p database name <~ /Desktop/backup file. SQL
  Enter the mysql password as prompted

Data Query (priority in where)

• Parentheses, not, comparison operators, logical operators
• And is equal to or. If you want to calculate or at the same time, you need to use

Data Query (difference between where and having)

• Where is used to filter data from specified tables.
• Having is a filtering of group by results and belongs to grouping data.

Data Aggregation

• Select count (*) from students;
• Select max (id) from students where gender = 0;
• Select min (id) from students where isdelete = 0;
• Select sum (id) from students where gender = 1;
• Select avg (id) from students where isdelete = 0 and gender = 0;

Data Group

• Select gender as gender, count (*) from students group by gender having gender = 1;

Sort

• Select * from table name order by column 1 asc | desc, column 2 asc | desc ,...

Remove duplicate rows

• Select distinct * from...

Foreign key settings

• You can directly create constraints when creating a table.

  Create table scores (
  Id int primary key auto_increment,
  Stuid int,
  Subid int,
  Score decimal (5, 2 ),
  Foreign key (stuid) references students (id) on delete cascade,
  Foreign key (subid) references subjects (id) on delete cascade
  );

• You can also create a foreign key constraint after creating a data table.

  Alter table scores add constraint stu_sco foreign key (stuid) references students (id) on delete cascade;

• Cascade operations include:
  • Restrict: Default Value, throwing an exception
  • Cascade: If records in the master table are deleted, all associated records in the table will be deleted.
  • Set null: set the foreign key to null.
  • No action: do nothing

• Generally, an enterprise creates a logical deletion field (isDelete) in the master table ).

Join Table query

Select students. sname, subjects. stitle, scores. score
From scoresinner join students on scores. stuid = students. id;

Note: inner join... on... indicates that there is no order to read all the fields of the two tables.

Left join... on... and right join... query all the data fields of one of them, and none of them exist. Fill in null.