Database: add, delete, modify and query commands for databases and tables

Connect to mysql
To connect to the MySQL server: mysql -u root -p (Note: my user name is root), and then enter the password to connect again

show database

show databases;

Create a database and set the encoding format to utf8

create database if not exists Library name character set utf8;

Displays the encoding format of a library

show create database Library name

Use a library

use Library name;

Modify the encoding format of a library

alter database Library name character set Coding format

Delete a library

drop database if exists Library name;

Data table operation: add, delete, modify and query

Create table

create table if not exists Table name(
    Field name data type constraint
)

data type
Integer tinyint / int()
Floating point float (total number of digits, number of digits after decimal point) / double
String char (fixed length 5) varchar (variable length 5)
Boolean 1 0
time

constraint
Primary key: primary key. It cannot be empty by default. auto_increment
unique key, mobile number, ID card, student number
Non empty: not null
Default constraint: default ''
Foreign key constraint: foreign key

View table structure
Return sql statement

show create table Table name

Return to table form:

describe Table name

Modify table
Add field

  alter table indicate add Field name data type constraint

Modify field name

alter table name change uname username data type constraint

Change table name

alter table stu rename student

Delete field

alter table name drop field name

Delete table
drop table name

Add constraint
alter table indicates the data type constraint of the add field name
alter table name change uname uname data type constraint
alter table name alter field name set constraint
Delete constraint
Delete unique constraint

Table records in the database: addition, deletion, modification and query

Create table

create table member5(
    id int(5) unsigned key auto_increment,
    name varchar(24),
    sex enum("man","women") default "man",
    age tinyint,
    province varchar(30),
    salary float(8,2)
);

increase
Insert a single piece of data

Insert [into] < table name > values < value list >
Eg: insert into table name values(null, 'Zhang San', default,45, 'Henan', 6666.66);

 insert into member5 values(null,'Zhang San',default,45,'Henan',6666.66);
    insert into member5 values(null,'Zhang Sanfeng',default,65,'Henan',7500);
    insert into member5 values(null,'Zhang Ziyi','women',39,'Los Angeles',6666.66);
    insert into member5 values(null,'Fool Zhang','women',26,'Beijing',3000);
    insert into member5 values(null,'Zhang Wei',default,33,'Beijing',9999.99);
    insert into member5 values(null,'Xiao Zhang Ning',default,19,'Henan',99999.99);
    insert into member5 values(null,'Li Si','women',15,'Hebei',2000);

Insert multiple pieces of data

insert into table name (field name) values (corresponding field value), (corresponding field value); Field name: can be omitted

insert into member5(name,age,province,salary) values('Wang Xiaoer',17,'Hebei',1800),('Erlang God',98,'Baolian lamp',8900),('Li Erniu',19,'Beijing',3200);
insert into member5 values(null,'JACK',default,45,'Los Angeles',3000);

lookup

select field 1, field 2,... from table name [where condition clause] [group by field] [having condition clause] [order by field] [limit [starting value] number]

Find all data in a table
select * from table name;

The following are query examples of the table created above

Fields in lookup table name，age，salary
select name,age,salary from member5;      member5 Is the table name;
Fields in lookup table name，age，salary also age Greater than 30
select name,age,salary from member5 where age > 30;

Fields in lookup table province yes null of
select * from member5 where province is null;

Fields in lookup table province no null of

select * from Table name
select Field name from Table name where [condition]
select * from member5 where province is not null;

    select * from member5 where age >= 30 and age<=40;
    select * from member5 where age between 30 and 40;
    select * from member5 where salary not between 5000 and 10000;

    select * from member5 where name like 'Zhang%';
    select * from member5 where name like '_Zhang_';
    select * from member5 where name like '_Zhang%';
    select * from member5 where name like '%Zhang%';
    select * from member5 where binary name = 'JAck';
    select * from member5 where sex='women' and age > 30;
    select * from member5 where age > 80 or salary > 10000;

    select * from member5  order by  age desc limit 0,10;

    select province,count(province) from member5 group by province; Query all provinces and the corresponding number of people
select province,count(province) from member5 group by province having count(province) >=3;

    where: The content of the condition must be the field existing in your table; Cannot match the corresponding function

like : Fuzzy query
% 	: 0 One or more		((similar to regular expression)
_	: There must be one

binary :Case sensitive

delete

Delete from < table name > [where delete condition]: the automatically incremented id will not be completely cleared;
Trunck < table name > is deleted in this way: the automatically incremented id will be completely cleared; (uprooting)
This statement deletes the rows in the table. If there is no where clause, the records in the whole table will be deleted, but the table will not be deleted.

Delete people older than 80 or with a salary greater than 1000
delete from member5 where age>80 or salary>10000;

Delete the oldest person   
delete from member5 order by age desc limit 1; 

modify
Update < table name > set < field name = value > where [condition]
Give a salary increase of 500 to women aged 30
update member5 set salary = salary + 500 where sex='women' and age >30;

Multi table operation of database

Multi table operation example exercise

Multi table operation
Create student and score tables

CREATE  TABLE student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4) ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);
establish score Watch. SQL The code is as follows:
CREATE  TABLE score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);

Add records for student table and score table

INSERT INTO student VALUES( 901,'Boss Zhang', 'male',1985,'Computer Department', 'Haidian District, Beijing');
INSERT INTO student VALUES( 902,'Zhang Laoer', 'male',1986,'Chinese Department', 'Changping District, Beijing');
INSERT INTO student VALUES( 903,'Zhang San', 'female',1990,'Chinese Department', 'yongzhou city ');
INSERT INTO student VALUES( 904,'Li Si', 'male',1990,'English Department', 'Fuxin City, Liaoning Province');
INSERT INTO student VALUES( 905,'Wang Wu', 'female',1991,'English Department', 'Xiamen City, Fujian Province');
INSERT INTO student VALUES( 906,'Wang Liu', 'male',1988,'Computer Department', 'Hengyang City, Hunan Province');

INSERT INTO score VALUES(NULL,901, 'computer',98);
INSERT INTO score VALUES(NULL,901, 'English', 80);
INSERT INTO score VALUES(NULL,902, 'computer',65);
INSERT INTO score VALUES(NULL,902, 'chinese',88);
INSERT INTO score VALUES(NULL,903, 'chinese',95);
INSERT INTO score VALUES(NULL,904, 'computer',70);
INSERT INTO score VALUES(NULL,904, 'English',92);
INSERT INTO score VALUES(NULL,905, 'English',94);
INSERT INTO score VALUES(NULL,906, 'computer',90);
INSERT INTO score VALUES(NULL,906, 'English',85);

1. Query all records of student table

select * from student;

1. Query the second to fourth records in the student table

select * from student  limit 1,3;	

1. Query the student id, name and department information of all students from the student table

select id,name,department from student;

1. Query the information of students in computer department and English department from the student table

select * from student where department = 'Computer Department' or department = 'English Department';

1. Query the information of students aged 18 ~ 22 from the student table

According to the time here, it is 2022

select * from student where birth>=2000 and birth<=2004;

1. Query the number of students in each department from the student table

select department,count(department) from student group by department;

1. Query the highest score of each subject from the score table

select * from score group by c_name  order by grade desc limit 3;

1. Query Li Si's test subjects (c_name) and test scores (grade)

select c_name,grade  from student inner join score on student.id = score.stu_id where name = 'Li Si';

1. Query the information of all students and examination information by connecting

select stu_id,name,sex,birth,department,address,c_name,grade from student as st inner join score as sc on st.id = sc.stu_id;

Or:

select stu_id,name,sex,birth,department,address,c_name,grade from student,score where student.id = score.stu_id;

1. Calculate the total score of each student

select name,sum(grade) from student inner join score on student.id = score.stu_id group by stu_id;

1. Calculate the average score of each examination subject

select name,sum(grade)/count(stu_id) from student inner join score on student.id = score.stu_id group by stu_id;

1. Query the information of students whose computer scores are lower than 95

select stu_id,name,sex,birth,department,address,c_name,grade from student as st inner join score as sc on st.id = sc.stu_id where c_name = 'computer' and grade < 95;

1. Query the information of students who take computer and English exams at the same time

 select name,birth,department,c_name,address,grade from student as st inner join score as sc on st.id = sc.stu_id where c_name like "computer%" or  c_name like "English%" group by name having count(name) = 2;

select name,birth,department,c_name,address,grade from student as st inner join score as sc on st.id = sc.stu_id where c_name = "computer" or  c_name = "English" group by name having count(name) = 2;

1. Sort the computer test scores from high to low

select stu_id,name,sex,birth,department,address,c_name,grade from student as st inner join score as sc on st.id = sc.stu_id  where c_name = 'computer' order by grade desc;

1. Query the student number from the student table and score table, and then merge the query results

select stu_id,name from student as st inner join score as sc on st.id = sc.stu_id;

1. Check the names, departments, examination subjects and scores of students surnamed Zhang or Wang

select stu_id,name,department,c_name,grade from student as st inner join score as sc on st.id = sc.stu_id where name like "Zhang%" or name like "king%";

1. Inquiries are the names, ages, departments, examination subjects and scores of students in Hunan

select name,birth,department,c_name,grade from student as st inner join score as sc on st.id = sc.stu_id where address like "Hunan%";