Database operation

Query all databases

mysql> show databases;

2. Create a database (cannot have the same name)

mysql> create database cgb2108;
mysql> create database cgb210801 default character set utf8;  #Specify the character set to avoid Chinese garbled code

3. Delete database

mysql> drop database cgb2108;  #Careful operation

Table operation

1. Query all tables

1,Query all tables
mysql> use cgb210801;  #Use the specified database
Database changed
mysql> show tables;  #Query all tables

2. Create table

grammar: create table Table name(Field name field type(Field length),Field 2,Field 3....)
mysql> create table tb_door(
    -> id int(11),
    -> door_name varchar(100),
    -> tel varchar(20)
    -> );

3. Modification table

Syntax: alter table  Table name    Add field name field type(Field length)
mysql> alter table student add column address varchar(100);

4. View table structure

mysql> desc student;

5. Delete table

mysql> drop table Table name

Actions recorded in table

1. Query all records in the table

*   In practice, it is represented by specific data records, not required*

Syntax: query all    Table name
mysql>select * from tb_door;

2. Insert a record into the table  

grammar:  insert data    Table name    value(Value of field 1,Value of field 2,Value of field 3)
be careful:There are several fields in the table values Several values are required+The order of values is consistent with the order of fields
mysql> insert into tb_door values(10,'test',"010-666");

3. Modify records

grammar:  to update    Table name   set up  Field name=New value for field

mysql> update tb_door set   tel='010-857';

4. Delete record

grammar: delete from Table name
mysql> delete from tb_door;  #Deleted all records in the table

Basic function

lower -- data to lowercase

SELECT 'ABC',LOWER('ABC') from dept;

upper -- convert data to uppercase

select upper(dname) from dept

Length -- length of data

select length(dname) from dept

substr -- intercept [1,3]

SELECT dname,SUBSTR(dname,1,3) FROM dept;

concat -- splice data

select dname,concat(dname,'123') X from dept

Replace -- replace the a character with 666

select dname,replace(dname,'a','666') X from dept

ifnull# judgment: if comm is null, replace it with 10

select ifnull(comm,10) comm from dept2

Key points: decimal places round & ceil & floor
round, ceil rounded up, floor rounded down

Direct rounding

select comm,round(comm) from emp
–Round to one decimal place

select comm,round(comm,1) from emp
–ceil Round up,floor Round down

select comm,ceil(comm) ,floor(comm) from emp

Check UUID

SELECT UUID()

time

now()

select now() -- Year, day, hour, minute and second
select curdate() --Year and day
select curtime() --Hour, minute and second

year & month & day
– hour() minute() minute() second() second

select now(),hour(now()),minute(now()),second(now()) from emp ;
–year()year month()month day()day
select now(),year(now()),month(now()),day(now()) from emp ;

Escape

Single quotation mark --- add \ escape

Condition query (7)

distinct ---- remove duplicate rows (only for single table)

SELECT DISTINCT loc FROM dept;

where -- with conditions

And --- and     Or -- or     in   --- Equal to multiple numbers

select * from emp where ename='tony' and deptno=2 --Equivalent to two conditions&relationship
select * from emp where ename='tony' or deptno=1 --Equivalent to two conditions|relationship
select name, sal from emp where sal in(1400,1600,1800);

like

The wildcard% represents 0 to n characters, and the wildcard is underlined_ Represents 1 character

1% -- beginning with 1    % 1 ends with a    % A% contains

NULL 

Use is not=

select * from emp where mgr is null --Filter fields with empty values
select * from emp where mgr is not null --The filter field value is not empty

Between and

Closed interval

select * from emp where sal<=3000 and sal>=10000--equivalent
select * from emp where sal between 3000 and 10000--equivalent

limit paging query

Pagination, limiting entries for data presentation

1. One parameter: list the first two --- limit 2

2. Two parameters: start with n+1 and display p pieces of information limit n,p

select * from emp limit 2 --List the first two
select * from emp limit 1,2 --Start with Article 2,Show 2 records

order by default ascending

Ascending asc

1. Number query: number size sorting

2. Letters: sort in alphabetical order

3. Date: the size of numbers

4. Chinese: code table of utf-8

Descending desc

aggregation (5)

According to a list of statistical results

Count -- use count(1)

max/main -- max min

sum/avg -- sum average

Grouping group

The group by group must be grouped according to when the grouping result has both aggregate columns and non aggregate columns

Grouping rules: grouping by non aggregation

having must be used after filtering  

What's the difference between char and varchar?

Char is a fixed length string, char(n), and the maximum value of n is 255

varchar is an indefinite length string. varchar(n) has a maximum length of 65535

char(10) and varchar(10) store abc. What's the difference between them?

char saves 10 characters, abc three, and others will be filled with spaces; varchar uses only three abc positions

What is the difference between primary key, foreign key and unique index?

Primary Key is a Primary Key constraint that automatically creates a unique index
Foreign Key constraint. The content of a Foreign Key field refers to the content of a field in another table and cannot be written blindly
Unique Index unique index, unique value but not primary key
For the benefits of constraints, the database will check. If the constraints are violated, an error will be reported and the operation fails. Database provides rich constraint checking and other constraints, but it is rarely used under the premise of weakening relational database. Just remember the above three.

What is the difference between drop, delete and truncate?

drop delete library or table, data and structure definitions

Delete and truncate just delete the data of the table

Delete can specify the where condition, delete the records that meet the condition, and tuncate can delete all records

For tables with self incremented fields, delete does not automatically increment and reset, while truncate deletes the table records and definitions, and then reconstructs the table definitions. Therefore, the self incremented primary key will start counting again
 

What is a transaction

Database transaction refers to a series of operations performed as a single logical unit of work, either completely or not.

Transaction 4 attributes ACID

Atomicity (or indivisibility)

Consistency

Isolation (also known as independence)

Durability.

Isolation level

Transaction isolation is divided into different levels, including

Read uncommitted has the worst security, concurrent data problems may occur, and the performance is the best
read committed is Oracle's default isolation level
repeatable read is the default isolation level of MySQL, with good security and average performance
Serializable table level lock, locking both read and write, low efficiency, high security and no concurrency

constraint