MySQL common statements

mysql login

 mysql -uroot -p password
 mysql -hip -uroot -p Password for connection target
 mysql --host=ip --user=root --password=Password for connection target

MySQL exit

exit
quit

DDL: operation database and table

Create database

create database Database name;

Create a database, judge that it does not exist, and then create it

create database if not exists Database name;

Create a database and specify a character set

create database Database name character set Character set name;

Create a db4 database, judge whether it exists, and set the character set as gbk

create database if not exists db4 character set gbk;

Query the names of all databases

show databases;

Query the character set of a database: query the creation statement of a database

show create database Database name;

Modify the character set of the database

alter database Database name character set Character set name;

Delete database

drop database Database name;

Judge whether the database exists, and then delete it

drop database if exists Database name;

Query the name of the database currently in use

select database();

Use database

use Database name

Create table

create table Table name(
					Column name 1 data type 1,
					Column name 2 data type 2,
					....
					Listing n data type n
				);
create table student(
					id int,
					name varchar(32),
					age int ,
					score double(4,1),
					birthday date,
					insert_time timestamp
				);

Copy table

create table Table name like Replicated table name;

Query all table names in a database

show tables;

Query table structure

desc Table name;

Modify table name

alter table Table name rename to New table name;

Modify the character set of the table

alter table Table name character set Character set name;

Add a column

alter table Table name add Column name data type;

Modify column name type

alter table Table name change Column name new column type new data type;
alter table Table name modify Column name new data type;

Delete column

alter table Table name drop Listing;

Delete table

drop table Table name;
drop table  if exists Table name ;

DML: adding, deleting and modifying data in the table

Add data

insert into Table name(Column name 1,Column name 2,...Listing n) values(Value 1,Value 2,...value n);
If no column name is defined after the table name, values are added to all columns by default
insert into Table name values(Value 1,Value 2,...value n);

Delete data

delete from Table name [where condition]

Delete all records

1. delete from Table name;
-- Not recommended. How many records will be deleted
2. TRUNCATE TABLE Table name; 
-- Recommended. It is more efficient. First delete the table, and then create the same table.

Modify data

update Table name set Column name 1 = Value 1, Column name 2 = Value 2,... [where condition];

DQL: querying records in a table

Query age greater than 20 years old

SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;

The query age is equal to 20 years old

SELECT * FROM student WHERE age = 20;

Query age is not equal to 20 years old

SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

Query age is greater than or equal to 20 and less than or equal to 30

SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

Query the information of age 22, 18 and 25

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

Query English score is null

SELECT * FROM student WHERE english IS NULL;

Query English score is not null

SELECT * FROM student WHERE english  IS NOT NULL;

What are the names of horses? like

SELECT * FROM student WHERE NAME LIKE 'horse%';

The second word of the query name is the humanized person

SELECT * FROM student WHERE NAME LIKE "_turn%";

A person whose name is three words

SELECT * FROM student WHERE NAME LIKE '___';

Query the person whose name contains De

SELECT * FROM student WHERE NAME LIKE '%virtue%';

Grouped by gender. Query the average scores of male and female students respectively

SELECT sex , AVG(math) FROM student GROUP BY sex;

Grouped by gender. Query the average score and number of male and female students respectively

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

Grouped by gender. Query the average scores of male and female students respectively. The number requirements: those with a score less than 70 will not participate in the grouping, and will not participate in the grouping. The number of people should be more than 2

SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

3 records per page

SELECT * FROM student LIMIT 0,3; -- Page 1
SELECT * FROM student LIMIT 3,3; -- Page 2
SELECT * FROM student LIMIT 6,3; -- Page 3

constraint

Add constraints when creating tables

CREATE TABLE stu(
				id INT,
				NAME VARCHAR(20) NOT NULL -- name Is not empty
			);

After creating the table, add a non empty constraint

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

Delete non empty constraint for name

ALTER TABLE stu MODIFY NAME VARCHAR(20);

When creating a table, add unique constraints

CREATE TABLE stu(
				id INT,
				phone_number VARCHAR(20) UNIQUE -- cell-phone number
			);

Delete unique constraint

ALTER TABLE stu DROP INDEX phone_number;

After the table is created, add a unique constraint

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

When creating a table, add a primary key constraint

create table stu(
				id int primary key,-- to id Add primary key constraint
				name varchar(20)
			);

Delete primary key

ALTER TABLE stu DROP PRIMARY KEY;

After creating the table, add the primary key

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

When creating a table, add a primary key constraint and complete the primary key self growth

create table stu(
				id int primary key auto_increment,-- to id Add primary key constraint
				name varchar(20)
			);

Delete auto growth

ALTER TABLE stu MODIFY id INT;

Add auto growth

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

When you create a table, you can add foreign keys

create table Table name(
					....
					Foreign key column
					constraint Foreign key name foreign key (Foreign key column name) references Main table name(Main table column name)
				);

Delete foreign key

ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

After creating the table, add the foreign key

ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);

Add cascading operation

ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) 
REFERENCES Main table name(Main table column name) 
ON UPDATE CASCADE 
ON DELETE CASCADE  ;

Keywords: Database MySQL SQL

Added by Design on Wed, 29 Sep 2021 21:03:30 +0300

Programming VIP

MySQL common statements

DDL: operation database and table

DML: adding, deleting and modifying data in the table

DQL: querying records in a table

constraint

Popular Keywords