MySQL common instructions

Time:2020-10-8

MySQL related instructions: database collation: commonly used utf8_ general_ Ci

1cmd connection database:

1. Version view: mysqladmin — version

2. Connect root: MySQL – u root – P, and enter the password/Exit Ctrl + C or quit

 2 query database: show databases;

3 query data table: show tables;

4. Create database: create database name;

5. Delete database: drop database database name;

6 select database: use database name;

7 display the current database and version information as well as the connection user name: select version(), database(),, user();

8 create data table: create table name;

Common example: create tableTable name (id int (4) not nullprimary key auto_increment, name char(20) not null, sex int(4) not null default ‘0’, degree double(16,2));

9. Delete data table: drop table name;

Insert data: insert into table name (class_ id, name, gender, score) VALUES

(1, ‘Dabao’,’m ‘, 87), (2,’ er Bao ‘,’m’, 81);

11 update data: set name =’calf ‘, gender = female where id > = 5 and ID < = 7;

12 delete data: delete from table name where id = 5;

13 add modified data: alter table table name add field name field name segment type;

Alter table name change old field name new field name segment type;

14 delete field: alter table name DROP field name;

15 query data:

a. Query all data in the table: select * from table name;

  b.Sort: select id, name / field name from table name order by score desc

  c.Sorting by conditions:

    SELECT id, name, gender, score

    FROM students

    WHERE class_id = 1

    ORDER BY score DESC;

e. Number of students in each class: select class_ id, COUNT(*) num FROM students GROUP BY class_ ID;

f. Multi table query:

    SELECT

    s.id sid,s.name,s.gender,s.score,c.id cid,c.name cname

    FROM students s, classes c

    WHERE s.gender = ‘M’ AND c.id = 1;

g. Linked table query:

    SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score

    FROM students s

    INNER JOIN/ RIGHT OUTER JOIN/ LEFT OUTER JOIN/ FULL OUTER JOIN classes c

    ON s.class_id = c.id;