MySQL FAQs and commands

Problem:

1, CentOS MySQL startup failure: Turn off SELinux, Vi/etc/selinux/config, set selinux=disabled, restart the computer;

Command:

Stop, start MySQL server:/etc/init.d/mysqld {start|stop|status|restart|condrestart|try-restart|reload| Force-reload}

DDL (Data Definition Languages)

First, database-related

Creating a Database : create databases Test1;

View database: show Databases;

Using the database : Use test1;

Delete databases: drop database test1;

Second, the table related

Creating tables: CREATE TABLE student (name varchar) primary key, birthday date, weigh T decimal(10,2), age int(3));

View all tables of the database: show tables;

View table definition: desc student;

View the SQL statement that created the table:show CREATE table student \g; (\g, indicates a column display)

Delete tables: drop table student;

To modify a table:

1. Modify the field type: ALTER TABLE student Modify column name varchar (+ ) first; First means that the column is set to column one, and the same applies to other modify table commands

2. Modify field Name: ALTER TABLE student change column age age1 varchar (20); Also specify the field type

3. Add table field: ALTER TABLE student add column score int (3) after name; After the inserted column is in the Name column, the same keyword applies to the other modify Table command 4, delete the table field: ALTER TABLE student drop column score;

5, modify the table name: alter table student rename student1;

DML (Data manipulation Language)

Insert Record: insert into student (name, birthday) values (' Pape ', 19880522);

insert into student (name, birthday, weight, age) values (' A ', 19880522, $), (' B ', 19880523, 70, 25                                  ), (' C ', 19890312, 55, 22); Inserting multiple lines

Updated Record: update student set weight=65, age=26 where name= ' a ';

Delete Record: Delete from student where name= ' a ';

Query record:

1, the query does not re-record: SELECT distinct age from student;

2. Condition query: SELECT * from student where age=23 and weight>60;

3. Sorting and restriction: SELECT * from student order by age desc limit 1, 3;

4. Aggregation: Select name, count(1) from student group by age;

Select sum (name), Max(age), min(weight) from student;

5. Table Connection: Select name,age from Student,student1 where student.number=student1.num ber

6. Subquery: SELECT * from student where number in (select number from student1);

7. Record union: Select name from student UNION ALL select name from Student1; If you want to go heavy, change union all to Union

DCL (Data Control Language)

Authorization: Grant Select,insert on test1.* to ' pape ' @ ' localhost ' identifie D by ' 1234 ';

REVOKE authorization: revoke insert on test1.* from ' pape ' @ ' localhost ';

Can pass ? int; ? Show, wait for help to query.

MySQL FAQs and commands