Features
Features:

Product Tour >

Edraw AI >

Paid Plans:

Individuals >

Business >

Eduaction >
Resources
Blog

History

How-tos & Tips

Discovery

Biography

Business Analysis

Examples

AI concept Map

Free AI Mind Map Generator

Onenote Mind Map

Bcg Matrix Examples

Nike Marketing Strategy

Unilever SWOT Analysis

Make Mind Maps in Google Docs

Guide

FAQs

What's New

Resource Center
Templates
All Templates

Brain Storming Templates

Strategy and Planning Templates

Project Management Templates

Product Management Templates

Human Resources Templates

Agile Workflow Templates

Marketing Templates

Education Templates

Fun and Games Templates

User Gallery
Download
Pricing
Enterprise

mysql core operations

Contains creating database, Create a table, insert data, update table, Delete operations, etc. After learning this article, you can get started with MySQL!

Edited at 2024-03-16 21:33:03

PlotWizard

Recent works View more works>>

mysql core operations

PlotWizard

Recent works View more works>>

Recommended to you
Outline

Basic operations of MySQL

Create database

create database database name

Use database

use database name

Create table

create table table name( Column name 1 data type, Column name 2 data type, Column name 3 data type );

eg: create table stu( sno int, sname char(10), birthday date );

You can also add some default conditions when creating

For example: the value of a certain column cannot be empty when input, you can add not null The value of a column can be empty or null can be added If the value of a certain column is increasing (such as id), auto_increment can be added

Set a primary key

This value must be unique. For example, the student ID of a classmate in a class can be set as the primary key. primary key must not be empty

Insert data

insert into database name. table name (column name 1, column name 2, column name 3) values(value 1, value 2, value 3) (database name can be omitted, column name can also be omitted)

eg: insert into stu values(1,'Xiaohong','2007-09-09')

Update table

Add a new column

alter table database name.table name add column name data type default condition

update data

update database name.table name set value (write the number you want to change) where condition (such as: sno=3, sno is the primary key, sno=3 can uniquely determine a row)

Delete operation

delete certain data

delete from database name.table name where condition (specify location)

Delete a table

drop table database name.table name

Delete a database

drop database database name

View content

View all contents of the table

select *from table name

View the contents of a column

select column name 1, column name 2 from table name

View the data after deduplication (for example, if you want to see what systems are in the table) select distinct column name from table name

Sort

select * from table name order by column name sort condition

If it is from low to high, add ASC from small to large (the default is ASC)

If it is from high to low, add DESC from large to small.

filter

select * from table name where condition (order by column name)

For example: a table has a column named grades and a column named gender. I want to view the information of girls with scores above 90% select * from students_grade where grade>=90 and ssex != 'male' (if I want to read letters from girls below 90 information, just add not in front of grade)

For example: if you want to see students with scores between 80 and 90, the condition after where can be written between 80 and 90

For example: to see the student information of two majors, you can write sdept in ('CS', 'IS') after where If you accidentally forget how to spell the professional name, just remember that it starts with C and you can write: where sdept like 'C%', similarly if it ends with C you can write: where sdept like '%C') Underscores can replace any letters. If you remember that the third letter is the A condition, you can write it like this: Column names like '__A%'

some operations

intersection

inner join

select *from table one inner join table two on table one. same column name = table two. same column name

For example: sc table (student course information table) and c table (course information table) both have the word cno Value, merge the two tables together with cno (a bit like the join operation in relational operations) The code can be written like this: select * from sc inner join c on sc.cno = c.cno In the final table, the content of sc is on the left and the content of c is on the right.

union

Splice the contents of the two tables (or the contents of the columns you specify) up and down. If you take the complete union of the two tables The row of column names of the second table will not be displayed, and two tables with different numbers of columns cannot be combined.

eg1: select cno from sc union select cno from c; (duplication will be removed, the same content will only be displayed once) select cno from sc union all select cno from c; (adding all after union will not remove duplication) eg2:sc(sno,cno,tno,grade) c(cno,cname,cpon,credit) Both the sc and c tables have four rows select * from sc union select * from c; (splicing of two tables)

left join

select *from Table 1 left join Table 2 on Table 1. Same column name = Table 2. Same column name

Table 1 will be completely retained, while Table 2 will retain only the part you selected.

For example: there is an item cno in Table 1 but only 01 and 02 Then Table 2 will only retain the two rows 0102 in the final table.

right join

Keep the right side intact

-- Adding a statement is to annotate the sentence