Common operations statements in a database

1. Display the list of databases:
show databases; --For a view of how many databases you have in your current total!
2. Make it the current operational database
Use MySQL;    --Open the database. Choose to enter the database you want to enter
Show tables; --Displays the data tables in the MySQL database. All the tables in the database you entered are displayed.
3. Display the table structure of the data table:
Descrip student; --for displaying the structure of the student table

4. Create a database, build a table

Create DATABASE MyDatabase; --Create a database
Use MyDatabase; --Open this database
create table table name; --Create a table
5. Delete the database, except the table
drop database name;
drop table name;
6. Enquiry
SELECT * from table name;
7. Add a field:
ALTER TABLE name add column < field name >< field Options >
8. Modify the fields:
ALTER TABLE name change < old field name > < new field name > < options >--option refers to whether the type of the new field is empty
9. Delete a field:
ALTER TABLE Name drop column < field name >
10 Summarize the above actions
Create DATABASE office;
Use Office;
CREATE TABLE ' newtable ' (
' Member_no ' char (5) Not NULL,
' Name ' char (TEN) NULL,
' Birthday ' date NULL,
' Exam_score ' tinyint (ten) NULL,
PRIMARY KEY (' member_no ')
)
;
To modify a database table:
Requirements: After birthday this, add a field that is height and the data type is tinyint.
Rename field Exam_score to scores, data type unchanged
ALTER TABLE personal
Add column height tinyint after birthday,
Change column exam_score scores tinyint;
To insert data into a table:

Update personal set scores=95 where name= ' Netseek ';

Search by Score

Select scores from personal where name= ' netseek ';
Delete all of the information in the table named ' Gogo ':
Delete from personal where name= ' Gogo ';
Tables in the database except for:
drop table if exists personal;
--View table structure
EXEC sp_help ' tabname ' DESC tabname
--Change the table name
ALTER TABLE tabname RENAME newtabname
--Change the field type
ALTER TABLE MODIFY ID BIGINT
--Add Field
ALTER TABLE t_stuinfo ADD sname varchar ($) NULL
--delete Field
ALTER TABLE t_stuinfo DROP SID
--Add primary key/foreign key
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column name);
ALTER TABLE subtabname ADD CONSTRAINT fk_subtabname_tabname FOREIGN KEY subtabname (FID) REFERENCES tabname (ID);
--Delete primary key/foreign key
ALTER TABLE tabname DROP PRIMARY KEY pk_tabname
ALTER TABLE subtabname DROP FOREIGN KEY fk_subtabname_tabname
--Delete Constraint
ALTER TABLE tabname DROP CONSTRAINT constaintname
--Delete Table
DROP TABLE IF EXISTS tabname,subtabname;
--View
DESC view_name;
SHOW CREATE VIEW view_name;

Common operations statements in a database