First acquaintance of MySQL Ⅰ

MySQL

What is MySQL?

MySQL database
Database: a server for storing data

installation

Note: encoding problem
Select path: C:\ProgramData\MySQL\MySQL Server 5.7
open this edit file

Insert picture description here

Modify as shown in the figure below:

default-character-set=utf8mb4
Insert picture description here
Insert picture description here

Database VS data structure

Data structure is a subject. The
database is a piece of software. The bottom layer of the database uses a data structure and is very dependent on this data structure.

Advantages of the database

(Disadvantages of saving data in files)

  • File security issues
  • Files are not conducive to data query and management
  • Files are not conducive to storing massive amounts of data
  • Inconvenient file control in the program

Database storage medium

Disk, memory

Classification of the database

Relational database (two-dimensional table): MySQL, Oracle, SQL server
Non-relational database (key-value): Redis, hbase

MySQL is not suitable for large and complex problems

Link to the database

mysql -u root -p密码
//注意,p与密码间没有空格
Insert picture description here

SQL language

Is a programming language used to manipulate database data

DDL (used at the beginning of the project)
DML (the most frequently used, CURD)
DCL (operation and maintenance, not frequently used by developers)

What is a storage engine

How the database management system stores data, how to build an index for the stored data structure , how to update and query data and other technical implementation methods

show engines;
Insert picture description here
What is the difference between InnoDB MyISAM engine?
  • InnoDB supports transactions, MyISAM does not support transactions.
  • InnoDB supports foreign keys, but MyISAM does not.
  • InnoDB is a clustered index, MyISAM is a non-clustered index.
  • InnoDB does not save the specific number of rows in the table, and a full table scan is required when executing select count(*) from table.
  • InnoDB's smallest lock granularity is row locks, and MyISAM's smallest lock granularity is table locks.

Transactions: Affairs

Library operations

  1. Show database
show databases;
  1. Select database
use 数据库名;
  1. View the tables in the selected database
show tables;
  1. Delete database
drop database if exists 数据库名;
//删除数据库 如果存在
  1. Create database
create database if not exists 数据库名;
//创建数据库 如果不存在

Commonly used data types

When creating the table, the data type of the fields in the table

  • Numerical type
  1. Integer
    BIT (M) —> boolean
    eg:bit BIT(3) represents the current storage of three
    INT —> Integer
  2. Floating point
    DECIMAL(M,D) : The decimal
    M represents the effective length, D represents the number of digits after the decimal point
    eg: 13.45 —> M=4, D=2
    eg: DECIMAL(4, 2) cannot store 123.4, because 123.4 will Is parsed as 123.40 (M=5)
  • String type
    VARCHAR(SIZE) —>String
    eg: varchar(4) means that the current field stores up to 4 bytes
    TEXT 65535
  • Date type
    DATATIME : 8 bytes
    . Time range: 1000-9999 years
    . Time zone search and conversion will not be performed.
    TIMESTAMP : 4 bytes
    . Time range: 1970-2038
    . Time zone search and conversion are possible.

Note: fixed time format:'2021-06-02' 00:00:00'

Date function

date_format(date,format)
%Y: year, 4 digits
%m: month, 00~12
%M: month name
%k: hour (0~23)

eg: date_format(date,'%Y-%m')indicates the year and month

Splicing function

concat(列,列) 别名 from 表名
eg:

concat(first_name, ,last_name) name from student;

Table operations

create

drop table if exists 表名;
create table 表名(
	//表的字段 类型
	ID INT,
	NAME VARCHAR(10),
	password VARCHAR(10),
	age INT,
	sex VARCHAR(1),
	birthday timestamp,
	amout decimal(6,2),
	resume TEXT //注意这里无逗号
 	);

display

View table

show tables;

View the field type of the table

desc 表名;

delete

drop table if exists 表名

modify

alter table 原表名 rename as 修改后表名;