Basic MySQL commands and basic mysql commands

Basic MySQL commands and basic mysql commands

 

SQL Statement

Mysql version: For mysql-5.6.36 version

1.1 Common commands

# View database mysql> show databases; show databases like '% pres %'; # fuzzy query # View table mysql> use xzymysql> show tables; # view permissions show grants for oldboy @ '10. 0.0.% '; # view the column mysql> use xzymysql> desc test;

 

1.2 log on to mysql

Common Client-specific connection options-u <user_name> or -- host = <user_name>-p <password>-h 
 
1.2.1 Change Password
[root@db02 ~]# mysqladmin -uroot -p123 password 123456[root@db02 ~]# mysql -uroot -p123456mysql>
 
1.2.2 forgot password-what should I do?
# Enable mysql (skip the authorization table) # -- skip-grant-tables skip authorization table -- skip-networking disable network logon-that is, local logon/application/mysql/bin/mysqld_safe -- skip-grant-tables -- skip-networking & # Go to MySQL to modify mysql. user table content mysql # directly log on (no password required) # modify mysql. user table content mysql> update mysql. user set password = PASSWORD ('000000') where user = 'root' and host = 'localhost'; mysql> flush privileges; # quit and use the new password 666666 for Logon. Note: different update mysql versions 5.7. user set authentication_string = PASSWORD ('000000') where user = 'sys 'and host = 'localhost' flush privileges;
 
1.3 grant grant1.3.1 grant
Grant permission on permission range (object) to user identified by ''; permission (role): select, update, delete, insert, drop, createALL permission range :*. * All database objects oldboy. * oldboy: all objects in a single oldboy database. test single-Table users: repl @ localhostrepl @ '10. 0.0.53 'repl @ '10. 0.0.% 'repl @ '10. 0.0.5% '----- requirement: 1. the user can only access through 10 CIDR blocks, and the user name is oldboy, the password is 1232. Only objects in the oldboy database can be added with insert create, update, and select. # create grant select, insert, update, and create on oldboy. * to oldboy @ '10. 0.0.% 'identified by '20140901'; # view permissions: show grants for oldboy @ '10. 0.0.% '; # revoke permissions revoke drop on oldboy. * from 'oldboy '@ '10. 0.0.% '; # delete user drop user oldboy @' 10. 0.0.% 'Note: The created permission information is not in the database name directory under/data /.
 
1.4 easy-to-use functions and built-in functions of mysql client interface
1. \ h or help or? Get help 2. \ G format output (Row-to-column) 3. \ T or tee record operation log tee/tmp/mysql. log4, \ c, or CTRL + c Exit mysql5, \ s, or status to view database status information 6 ,\. or source mysql> source/tmp/world. sql7, \ u, or use world show databases view the names of all databases currently show tables view all the tables in the database currently in use show tables from world View tables in the target database 8, ctrl + L clear screen
 
1.5 add, delete, modify, and query-detailed description of Database SQL commands 1.5.1 Database
# Create database mysql> create database xzy character set utf8; # Or charset utf8 (recommended) mysql> show create database xzy; + ---------- + databases + | Database | Create Database | + ---------- + -------------------------------------------------------------- + | xzy | create database 'xzy '/*! 40100 default character set utf8 */| + ---------- + -------------------------------------------------------------- + # modify database mysql> alter database xzy charset gbk; # delete database mysql> drop database xzy;
 
1.5.2 table-table1.5.2.1 View table
# Help: mysql> help create table; # view the column mysql> use xzymysql> desc test; + ------- + ---------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ---------- + ------ + ----- + --------- + ------- + | id | int (11) | YES | NULL | name | char (30) | YES | NULL | + ------- + ---------- + ------ + ----- + --------- + ------- +
1.5.2.2 operation table
# Create a table mysql> use xzymysql> create table test (id int, name char (30); # complete: create table 'test' ('id' int (4) not null AUTO_INCREMENT, 'name' char (20) not null, primary key ('id') ENGINE = InnoDB default charset = UTF8; # modify the table name (two methods) mysql> rename table test to test1; or mysql> alter table test1 rename to test; # add a column structure mysql> alter table people add addr char (40) not null; # Add to the end by default # specify the position after adding the age column to the name column, for example: alter Table people add age int (4) after name; # use the following command to add the qq field to the first column. Alter table test add telnum int first; # add multiple column definitions simultaneously: alter table people add id int first, add sex char (4) after name; # Delete table structure: alter table people drop sex; # alter table definition alter table people modify name char (20); # alter column name: alter table people change name people_name char (30 );
 
 
1.5.3 insert and update data
 
# Insert statement: create table test (id int, name varchar (20); # create table testinsert into test values (1, 'test '); # insert a single insert into test values (2, 'yougboy '), (3, 'youggilr'); # insert multiple insert into test (name) values ('xiaoming '); # insert the specified column select * from test; # view # create the same table ‑ create table test like oldboy; insert into oldboy select * from oldboy; -------------------------------- # modify (update) data updateupdate (where condition is required) update test set name = 'oldboy1 'WHERE id = 1; # delete data deletedelete (where condition must exist) delete from oldboy where id = 1;
 
1.5.4 View data-select
select user,password ,host from mysql.user where user='sys';select user,password ,host from mysql.user where user like 'sy%';select * from oldboy.test;select id,name from test where id=2;select id,name from test where id>2 and id<4;
 
 
1.6 complex query 1.6.1 order by clause
The order by clause is used to sort the row Syntax: SELECT expr FROM table [WHERE condition (s)] [order by {column, expr, numeric_position} [Asc | DEsc]; WHERE: Asc: sort in ascending order. Default Value: DEsc: run the order by clause in descending ORDER. Generally, this clause is at the end of the SELECT statement # SELECT * FROM city order by population, countrycode;
 
1.6.2 LIMIT clause
The LIMIT clause is the last clause in the SELECT statement (after order ). It is used to select the first or last rows from the result set. Syntax: limit <Number of retrieved rows> [OFFSET <Number of skipped rows>] or limit [<Number of skipped rows>,] <Number of retrieved rows>SELECT * FROM city order by 5 DEsc LIMIT 4;
1.6.3 subquery
Obtain the number of players in the same city and gender as player 100. Select playerno from players where (sex, town) = (select sex, town from players where playerno = 100 );
 
1.6.4 connection Query
# Basic usage SELECT * FROM Student, Grade WHERE Student. sID = Grade. gID; # AS alias SELECT ci. name AS city_name, co. name AS country_name, co. 'surfaceregion' AS mianji FROM city AS ci, country AS co WHERE ci. name = 'qingdao 'AND ci. 'countrycode' = co. 'countrycode'; # The natural join clause automatically searches for all columns of the same NAME in the two tables for JOIN columns, and performs the same connection for select name, CountryCode, LANGUAGE, populationFROM city natural join countrylanguageWHERE population> 1000000 order by population; # Use the using clause (specifying column JOIN) select name, countrycode, LANGUAGE, populationFROM city JOIN countrylanguageUSING (countrycode );