Database learning 7: MySQL client tools and SQL introduction

7, MySQL client tools and SQL introduction

1. Syllabus:

mysql client command introduction;

How to get help from mysql;

Management database of DDL statements;

DDL statement management table and case introduction;

Data in management table of DML statement;

SELECT to retrieve data;

2. mysql interface program and SQL introduction

mysql client command introduction:
• mysql: – for database connection management
 -Send user SQL statement to server
 • mysqladmin: - command line administration tool
 • mysqldump: - backs up the contents of databases and tables

-For managing databases:
	Command interface with command
	DDL: Data Definition Language (create)
	DCL: data control language (grant,revoke)
	DML: data operation language (update,delete,insert)
mysql interface program:
mysql -uroot -poldboy123 -e "show variables like '%server_id%'"
mysql>:
1. Functions of the interface
 mysql command:
1.\h or help or?
Display the interface command help command.
2.\G
 Output the displayed content format.
3.\T or tee
 For logging, you need to first: tee /tmp/test.log
 All mysql operations and output are recorded in this file.
4.\c or CTRL+c
 Statement followed by \ c, the previous command is not executing. ctrl+c exit
 5.\s or status
 View the basic status of the current database.
6. \. Or source
 Used to execute external SQL scripts: binary log interception, backed up SQL scripts
7.\ use
 use enters a database.
2. Server side commands (SQL structured query language, mysql interface program is only responsible for receiving SQL)
show series command.

2. Server side commands (SQL)

(1) SQL: a structured query language. mysql interface program is only responsible for receiving and transferring SQL

(2) SQL type:

DDL: database object definition language (create)

DCL: database control language (grant, revoke)

DML: data row operation language (update,delete,insert)

DQL: Data Query Language (show, select)

DDL operation:

Object: f

Library:

What is the definition?

1. Library name

2. Basic properties of the library

How to define?

create database lufei;

create shema lf;

show databases;

mysql> help create database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
Create properties:
create_specification:
Character set: [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] 
  Collation: collate [=] collation [name
mysql> create database llf character set utf-8;
MySQL > show create database LLF; view the database creation statement
 drop database llf; delete database

Help is followed by a command to help unfamiliar commands.
Modify character set:
ALTER DATABASE [db_name] CHARACTER SET charset_name collation_name
mysql> alter database lf charset utf8mb4;
                          Abbreviation
mysql> show create database lf;

Table:

Table data

Table attribute (metadata): indicates the column name, column definition (data type, constraint, special column attribute), and table index information.

What is the definition?

Define table properties?

use lufei;
create table t1(id int,name varchar(20));
mysql> use lufei;
mysql> create table t1(id int ,name varchar(20));
mysql> show tables;
mysql> show create table t1;
mysql> desc t1;
mysql> drop table t1;

//Modify the definition of the table:
		//Modification:
		(1)Add a column to the table
		alter table t1 add age int;
		(2)Add multiple columns
		alter table t1 add bridate datetime, add gender enum('M','F');
		(3)Add a column after the specified column
		alter table t1 add stu_id int after id;
		(4)Add a column at the top of the table
		alter table t1 add sid int first;
		(5)Delete column
		 alter table t1 drop sid;
		(6)Modify column name
		alter table t1 change name stu_name varchar(20);
		(7)Modify column properties
		alter table t1 modify stu_id varchar(20);
		(8)Modify table name
		rename table t1 to student;
		alter table student rename  as stu;

DML statement: database operation language

insert

update

delete

DML Statements: Data Manipulation Language
	insert 
	use  lufei
	create table t1 (id int ,name varchar(20));
	insert into t1 values(1,'zhang3');
	select * from t1;
	insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6');
	insert into t1(name) values ('xyz');
	update
	update  t1  set name='zhang33' ;   ----The name Field, more dangerous.
	update  t1  set name='zhang55' where id=1;   ----update Generally, there are where Conditions to limit.
	delete
	delete from t1 ;  --It is dangerous to delete all rows in the table. Delete the data in the table row by row.
	delete from t1   where  id=2;
	DDL
	truncate table t1;  ---It is faster to delete table data physically.

DQL statement: (database query statement)

DQL:
	select sentence:
SELECT USER,PASSWORD ,HOST  FROM mysql.user;

-- select Basic query

DESC world.city
SELECT  id ,NAME   FROM  world.city;
SELECT * FROM world.`city`;
-- select Condition query where
---- 1,Query China( CHN)All city information
SELECT * FROM world.`city` WHERE countrycode='CHN';
---- 2,Query China( CHN)All city information of Anhui Province.
SELECT * FROM world.`city` 
WHERE countrycode='CHN'
AND
district='anhui';

---- 3,Query the population of the world at 10 w-20w City Information
SELECT * FROM world.`city` 
WHERE 
population BETWEEN 100000 AND 200000 ;

---- 4,All cities in China or Japan
where In words and sentences IN
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');

---- 5,Fuzzy query
SELECT * FROM world.city
WHERE countrycode LIKE 'ch%';

select sort and limit

Output the urban information of China by population (asc (default ascending order), desc (descending order))

-- select Sort and limit
---- Output China's urban information in order of population( ASC\DESC)

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC;

---- Sort population by columns+Province ranking
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY id DESC  ;
//Sort in descending order by column 5:
SELECT * FROM city
ORDER BY 5 DESC ;

1-20
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 20;

//Display lines 11-20
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10,10 ;

SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

Table join query (use where)

Traditional connection writing (using where)
---- All cities in China+Use language
		
SELECT NAME ,countrycode ,population FROM city 
WHERE countrycode ='CHN'	
	
SELECT countrycode ,LANGUAGE FROM countrylanguage;

SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language 
FROM 
city AS ci , countrylanguage AS cl
WHERE ci.countrycode ='CHN' 
AND
ci.CountryCode=cl.CountryCode;

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM  city ci , countrylanguage cl
WHERE 
ci.countrycode='chn' AND
ci.`CountryCode`=cl.countrycode;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city NATURAL  JOIN  countrylanguage 
WHERE population > 10000000
ORDER BY population;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city JOIN  countrylanguage 
USING(countrycode);

---- What's the name of the country where Qingdao is located
DESC city
DESC country

SELECT NAME,countrycode FROM city WHERE NAME='qingdao';

SELECT NAME FROM country WHERE CODE='CHN';
--------------------------------
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci 
JOIN 
country AS co
ON ci.countrycode=co.code
AND
ci.name='qingdao';

group by + aggregate function (avg(),max(),min(),sum())

group by +Aggregate function( avg(),max(),min(),sum())

SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = 'chn'
GROUP BY countrycode;

union 

//Used to replace or, in()

SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
//Overwrite with:

SELECT * FROM world.city
WHERE countrycode ='CHN'
union
SELECT * FROM world.city
WHERE countrycode ='JPN';

character set

Character set:

charset: character set
UTF8
UTF8mb4

gbk

Collation: collation

A-Z, A-Z case sensitive

aA-zZ lowercase insensitive

show charset;
show collation;

Database:

Server character set:

The control is that when it is saved in mysql, the character set control

Client character set

It controls the user's input and display
 System character set

It controls system related displays and some applications that depend on the operating system
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

Note: when changing the character set, make sure to change from small to large. The latter must be a strict superset of the former. Don't change it in production.

Data type and column properties:

Number type
 Character type
 Time type
 Column properties
create table student(id int not null primary key  AUTO_INCREMENT)；
create table student1(id int not null primary key  AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");

primary key: non empty and unique
 Unique: unique

Get metadata:

information_schema :

Outside data row

Metadata (define data column property, column name, etc., status of data)

Act as the central system information base of database metadata:

Patterns and pattern objects
Server statistics (status variables, settings, connections)

Table format for flexible access

Use any select statement

Is "virtual database"

Table is not a "real" table (base table), but a "system view"
Dynamically populate the table based on the privileges of the current user

mysql> use information_schema 

mysql> show tables;

mysql> desc tables;

mysql> select table_name ,table_schema,engine from world;

//Displays information about the columns of the tables in the database world:

mysql> select * from columns where table_schema='world'\G;

mysql> select table_schema,table_name from information_schema.tables where table_schema='world';
+--------------+-----------------+
| table_schema | table_name      |
+--------------+-----------------+
| world        | city            |
| world        | country         |
| world        | countrylanguage |
+--------------+-----------------+
//Batch splicing statement:
----
mysql> select concat('hellow');
+------------------+
| concat('hellow') |
+------------------+
| hellow           |
+------------------+
1 row in set (0.01 sec)
----
//example:
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table__name,".bak.sql") from information_schema.tables where table_schema='world';
+-----------------------------------------------------------------------------------------------------------------------------+
| concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table_name,".bak.sql") |
+-----------------------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -poldboy123 world city >>/backup/world-city.bak.sql                                                        |
| mysqldump -uroot -poldboy123 world country >>/backup/world-country.bak.sql                                                  |
| mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world-countrylanguage.bak.sql                                  |
+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
//Example 2:
SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
			 TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
			 TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
			 WHERE TABLE_SCHEMA = 'world';

linux Commands used on the command line:
[root@centos6-kvm3 data]# mysqlshow -uroot -poldboy123 world
//Metadata general query statement:
show
show databases
show create database oldboy
show tables
show create table t1

SOHW databases: List all databases
SHOW TABLES: List tables in the default database
SHOW TABLES FROM <database_name>: Lists the tables in the specified database
SHOW COLUMNS FROM <table_name>: Display the column structure of the table
SHOW INDEX FROM <table_name>: Displays information about indexes and index columns in a table
SHOW CHARACTER SET: Display available character sets and their default collation
SHOW COLLATION: Show collation of each character set
SHOW STATUS: List current database status
SHOW VARIABLES: List parameter definition values in the database

Keywords: MySQL Database SQL mysqldump

Added by jdimino on Fri, 08 May 2020 16:47:04 +0300

Programming VIP

Database learning 7: MySQL client tools and SQL introduction

7, MySQL client tools and SQL introduction

1. Syllabus:

2. mysql interface program and SQL introduction

2. Server side commands (SQL)

Popular Keywords