MySQL Basic operation

Creating a library: Create database test1 charset UTF8;

View Gallery: show databases;
View Genesis statement: Show CREATE DATABASE test1;
Select Library: Use Test1
Modify Library: Alter DATABASE test1 CharSet UTF8MB4;
Delete Library: drop database test1;

Creating tables: Create TABLE stu (ID int,name varchar (10));
Create a table with the same table structure: CREATE table stu1 like Stu;
Create an alternate table of the same: CREATE TABLE STU2 as select * from Stu;
View table: Show tables;
View Genesis statement: show create TABLE Stu;
View table structure: desc stu;
Modify table name: ALTER TABLE Stu Rename to student;
Delete tables: drop table Stu;
Delete Large table: Truncate table stu; (for large tables with drop-down, you can use truncate and drop first)

Alter Modify TABLE:
1. Add a column at the end of the table: ALTER TABLE student add addr varchar (20);
2. Add a column to the table header: ALTER TABLE student add stu_id int first;
3. Add a column after the Name column: ALTER TABLE student add QQ int after name;
4. After the age of Tel_num, add email:alter table student add tel_num int after age,add email varchar (20) on the last line;
5. Delete a column: ALTER TABLE student drop ID;
6. Modify the column name: ALTER TABLE student change name stu_name varchar (20);
7. Modify the column data type: ALTER TABLE student modify gender varchar (20);

Insert data into the table:
1. Insert single line: Insert [into] student values (1, ' Beizi ', 111,20,110, ' Male ', ' bj ', ' [email protected] ');
2. Specify the column to insert: INSERT INTO student (STU_ID,STU_NAME,QQ) VALUES (2, ' li4 ', 456);
3. Multiline insert: INSERT into student values (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] '), (5, ' zz ', 12322,202,1102, ' Female ', ' bj ', ' [email protected] ';
4. Insert a table into another table: INSERT INTO stu0 select * from student; (table structure is the same)

Update modifies table data:
Modify one line: Update student set stu_name= ' Zhangsan ' where stu_id=1;

Delete Deletes the table data:
Delete from student where stu_name= ' Zhangsan ';

Note: Delete is dangerous and can be pseudo-deleted with an identifier with update:
ALTER TABLE STU0 add state int default 1;
Update stu0 set state=0 where stu_name= ' Zhangsan ';
SELECT * from stu0 where state=1;

Select query statement details next time continue

Permissions provided by MySQL:
Insert,select, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SU PER, create temporary TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, create VIEW, SHOW view, create ROUTINE, ALTER ROUTINE, create USER, EVENT, TRIGGER, create tablespace
Authorization rights: Grant Select,insert on test.* to [email protected] ' 192.168.1.% ' identified by ' 123456 ';
REVOKE permission: Revoke Select,insert on test.* from [email protected] ' 192.168.1.% ';
View permissions: Show grants for [email protected] ' 192.168.1.% ';
Remind:
If permissions are set at the library level and at the table level, permissions are superimposed on table operations.
Check out some information in the MySQL database user table:
Select User,host,password from Mysql.user;
Note: 5.7 password field is no longer password, replaced in order to authentication_string
5.7 Database:
Mysql> select user,host,authentication_string from Mysql.user;
+---------------+-----------+-------------------------------------------+
| user | Host | authentication_string |
+---------------+-----------+-------------------------------------------+
| Root | localhost | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+---------------+-----------+-------------------------------------------+
If the database password is forgotten, want to change the database password, in 5.6 The operation is:
A) Shutdown services: Service mysqld stop
b) Stop authorization start: Mysqld_safe--skip-grant-table--skip-networking &//--skip-grant-table Skip Authorization,--skip-grant-table prohibit remote connection
c) Change Password: Update mysql.user set Password=password (' 123 ') where user= ' root ' and host= ' localhost ';
(Replace password with authentication_string in 5.7)
d) Restart MySQL service

MySQL Basic operation