MySQL database overview
It is used to store and manage data, and finally form a table like appearance with rows and columns (fields)
Classification: relational database and non relational database
There are many products of relational database: MySQL (free) Oracle SqlServer
There are many non relational database products: Redis
Database ranking
Database classification
- DML (Data Manipulation Language) is a Data Manipulation Language that CRUD data
For example: insert, delete, update, select (insert, delete, modify, retrieve)
CRUD operations for short add Create, query Retrieve, modify Update, Delete - DDL (Data Definition Language) database definition language, creating libraries and tables
- DCL (Data Control Language) is a database control language, which manages the permissions of operating the database in a fine-grained manner
- DQL database query language initiates query requirements for data
MySQL basic usage
Login database
Mysql -u root -p root; #-u user name - p password
exit #sign out
query data base
show databases ;# Show all databases
Create database
create database Library name ; # Create database name
Delete database
drop database Library name ;# Delete database
use Library name ;# Use database
MySQL database table related operations
- To operate a table or create a table, use the database (use database name) and operate the table under a database
Create table
create table Table name(Field 1 name field 1 type,Field 2 name field 2 type); # Example: create table person(name varchar(10),age int);
Query all tables in the current database:
show tables;
Delete table:
drop table Table name;
Modify table:
alter table Table name add column Field name field type(length);
View the details of the description table (fields contained in the created table, type constraints)
desc Table name;
To delete a table field:
alter table Table name drop Field name;
Modify field name and type
alter table Table name change Original name new name new type;
Modify field type and location
alter table Table name modify Field name new type first /after xxx #Example: alter table student modify id int first; #Location example: alter table student modify id int after name;
Insert content into table
insert into Table name values(Value of field 1,Value of field 2,Value of field 3,Value of field 4);
#Example:
insert into person(name) values('Jerry');
``
Batch insert format:
```bash
#Example:
insert into person values('aa',10),('bb',20),('cc',30);
insert into person(name) values('Jerry'),('yyy');`
Query table content
Select * from Table name;
Solution to query and display Chinese garbled code
set names gbk/utf8;#Solve Chinese garbled code (Chinese can be inserted)
Modify data:
update Table name set Field name=New value; update Table name set Field name=value where condition; #Example: update person set age=18 where name='Lau Andy';
Delete data
#Format: delete from Table name where condition; example: delete from person where name='Jerry';
MySQL field constraints
Primary key constraint primary key
summary:
Primary key: fields that represent data uniqueness are called primary keys
Constraints: creating a table is to add constraints to fields
Primary key constraint: the limit value is unique and non empty
#Format: create table t1(id int primary key,name varchar(10)); #Primary key auto increment constraint #Format: create table t2(id int primary key auto_increment,name varchar(10));
unique constraint
#Example: create table test( id int primary key auto_increment, username varchar(50) unique--Unique constraint );
Default constraint default
#Default constraint: sets the default value for the specified field
CREATE TABLE f( id INT PRIMARY KEY AUTO_INCREMENT,#Primary key, auto increment sex VARCHAR(10) DEFAULT 'male' #Set default)
foreign key constraint
Foreign key – primary key of the associated table (primary table)
When adding a record to a sub table, the ID must be taken from the main table. When deleting a record in the main table, it must not be used by the sub table. Determine whether it is the foreign key of the sub table
Check constraint check
#Check constraints: check rules that set legal values for specified fields CREATE TABLE g( id INT PRIMARY KEY AUTO_INCREMENT,#Primary key, auto increment age INT, CHECK(age>0 AND age<200)#Setup check)
MySQL data type
character
char is fixed in length and cannot be filled with spaces. It can hold up to 2000 characters. char(11) stores abc, accounting for 11 bits. Query speed is very fast, but it wastes space
varchar variable length string, which can hold up to 4000 characters. varchar(11) stores abc, accounting for only 3 bits. Query is slightly slow, but saves space. Oracle is varchar2
Large text: large amount of text (not recommended, try to use varchar instead)
Calculated by utf8 coding, a Chinese character occupies 3 bytes under u8
Note: different database versions may have different length limits
number
tinyint,int integer type
float,double decimal type
numberic(5,2) decimal(5,2) - can also represent decimals, representing a total of 5 digits, of which there can be two decimals
decimal and numeric represent exact integer numbers
date
date includes mm / DD / yy
time hour minute second
datetime includes month, day, hour, minute and second
Timestamp timestamp is not a date, but the number of milliseconds from January 1, 1970 to the specified date
picture
blob binary data, can store pictures and sounds, with a capacity of 4g. There was such a design in the early days. However, its disadvantages are very obvious. The database is huge and the backup is slow. It is of little value to back up multiple copies of these contents. At the same time, the database migration is too large and the migration time is too long. Therefore, at present, the mainstream will not directly store such data, but only store its access path, and the files are stored on disk.
Basic function
lower
SELECT 'ABC',LOWER('ABC') from dept; #--Data to lowercase
upper
select upper(dname) from dept; #--Data to uppercase
length
select length(dname) from dept; #--Length of data
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; #--Intercept [1,3]
concat
select dname,concat(dname,'123') X from dept #--Splice data
replace
select dname,replace(dname,'a','666') X from dept #--Replace the a character with 666
ifnull
select ifnull(comm,10) comm from dept2 #Judge that if comm is null, replace it with 10
round & ceil & floor
round rounding, ceil Round up, floor Round down
Direct rounding
select comm,round(comm) from emp ;
Round to one decimal place
select comm,round(comm,1) from emp ;
ceil rounded up and floor rounded down
select comm,ceil(comm) ,floor(comm) from emp
Uuid (implement unique value)
SELECT UUID(); return uuid: a08528ca-741c-11ea-a9a1-005056c00001
now
select now() #--Year, day, hour, minute and second select curdate() #--Year and day select curtime() #--Hour, minute and second year & month & day #specific date hour()Time minute()branch second()second
select now(),hour(now()),minute(now()),second(now()) from emp ;
– year month day
select now(),year(now()),month(now()),day(now()) from emp ;
Escape character
'as a sql statement symbol, single apostrophe in the content will be disorderly, and can be escaped
select 'ab' cd '– single quotation marks are special characters of an SQL statement
select 'ab' cd '-- when there is a single quotation mark in the data, use a \ escape to become a normal character