Common commands of MySQL database

Time:2020-9-22

The basic SQL statements include select, insert, update, delete, create, drop, grant, revoke, etc

 

 

Library operations

Create database: create database shujuku;
Create database with character set of database;
Create database with verification: create database mydb3 character set = utf8 collate utf8_ general_ CI;
Display databases: show databases;
Delete database: drop database shujuku;
Modify the database code: alter databasese shujuku character set GB2312;

Table operation

Create a database table (create a table named employee, which contains fields of ID, name, sex, birthday and job);

create table employee
			(
			id int,
			name varchar(40),
			sex char(4),
			birthday date,
			job varchar(40),
	);

Add the image field to the table: alter table employee add image blob;
Modify the job value to 60 (the original length is 1000): alter table employee modify job varchar (60);
Delete the sex column: alter table employee drop sex;
Change the table name to user (formerly employee): rename table employee to user;
Change the character set of the table to UTF_ 8:alter table user character set utf8;
Change the column name to Username: alter table change colum name username varchar (100);
Drop table user;

Examples of adding, deleting, modifying and checking

Preparation table:

create table employee
			(
			id int,
			name varchar(40),
			sex varchar(4),
			birthday date,
			entry_date date,
			salary decimal(8,2),
			resume text
			);
	

Insert table data:

insert into employee(id ,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1999-08-22','2020-08-22,'1000','i am a developer');

Specify some columns to insert data:insert into employee(id) values(6);
Insert Chinese characters:insert into employee(id,name) values(6,'ZhangSan');

Modify table data

Change the salary of all employees to 5000 yuan:update employee set salary=5000;

Change the salary of the employee whose name is’ ZS’ to 3000 yuan: update employee set salary = 3000 where name =’zhangsan ‘;

The salary of the employee whose name is’ AAA ‘is changed to RMB 4000 and the job is changed to CCCupdate employee set salary = 4000,job=‘ccc’ where name=‘ZhangSan’;

Increase Wu’s salary by 1000 yuan on the original basis:update employee set salary = salary+1000 where name=‘ZhangSan’;

Delete table data

Delete the record named “ZS” in the table:delete from employee where job='ccc';
Delete all records in the table:delete from employee;
Use truncate to delete the record in the table: truncate table employee;

Query table data

Query the information of all students in the table:select id,name,chinese,english,math from student;
The names of all students and their corresponding English scores are as follows:select name,english from student;
For students with the name Wu:select * from student where name='ZhangSan';
For students with English scores above 90:select * from student where english>‘90’;
For students with English scores between 80 and 90:select * from student where english>=80 and english=<90;

Common MySQL statement commands

Enter the MySQL command line: MySQL – uroot – p;
View all databases: show databases;
Create database: create database Niu charset utf8;
Delete database:drop database niu;
Select database: use databases;
View all tables: show tables;
To view the statement that created the database:show create database databasename;
To view the statement that created the table:show create table tablename;
View the table structure: desc tablenmae;

Common meanings of MySQL fields

Self growth: Auto_ increment
Non null: not null
Default value: default
Unique: unique
Specified character set: charset
Primary key: primary key

 

See: https://www.shujukuba.com/mysql/99.html