2018-03-30 SQL Language Learning notes

Introduction and classification of SQL language what is SQL

Structured Query Language, a language method for defining and manipulating data in a relational database by structured querying the voice.
Structured Query language is a kind of database query and programming language

Classification of SQL

The SQL Structured Query language consists of 6 parts:

1. Data Query Language DQL
   • The role is to get data from the table to determine how the data is given in the application
   • Select,where,order By,group by and having
     • Select User,host,password from Mysql.user;
2. Data Manipulation Language DML
   • Insert,update,delete
   • Example: Delete from Mysql.user where user= ' DC ';
3. Transaction-Processing Language TPL
   • His statement ensures that all rows of the table affected by the DML statement are updated in a timely manner.
   • Begintransaction,commit,rollback
4. Data Control Language DCL
   • His statement is licensed through GRANT or revoke
5. Data Definition Language DDL
   • Create,alter,drop
   • Creating new Tables Creat table
   • Delete Table drop tables
6. Pointer Control Language CCL
   • Declare,cursor,fetch Into,update WHERE current

Summary: The most common classification of SQL statements is generally Class 3
ddl--Data Definition language Create,alter,drop<---operations
dml--Data Manipulation language Select,insert,delete,update<---Development
dcl--Data Control Language Grant,revoke,commit,rollback<---operation and maintenance

MySQL database common management application CREATE database

• Create databases < database name >← Note that the library name cannot begin with a number
• mysql> CREATE DATABASE oldboy;<---Creating default databases, using default character encoding
• Mysql> Show CREATE Database Oldboy \g
• Mysql> CREATE DATABASE OLDBOY_GBK default character set GBK collate gbk_chinese_ci;<---Creating GBK character Set databases
• Mysql> CREATE DATABASE Oldboy_utf8 default character set UTF8 collate utf8_general_ci;<---Creating UTF8 character set databases
• Hint: The inconsistency of the character set is the culprit of the Chinese garbled database.
• Tip: If you compile a specific character set encoding, you will not need to specify a character set for subsequent databases that create the corresponding character set.

Enterprise Creation Database

• Determine the character set according to the developed program (recommended UTF8)!
• Specifying a character set when compiling
  • -ddefault_charset=utf8 \
  • -DDEFAULT_COLLATION=UTF8_GENERAL_CI \
• Build the library when the default creation can, create database Oldboy;
• Compile without specifying a character set or specifying a different character set for the program, specifying the character set to create the database
  • Mysql> CREATE DATABASE OLDBOY_GBK default character set GBK collate gbk_chinese_ci;<---Creating GBK character Set databases
  • Mysql> CREATE DATABASE Oldboy_utf8 default character set UTF8 collate utf8_general_ci;<---Creating UTF8 character set databases

Database to support the creation of the library's character set

Display Database

• show databases;
• Show databases lick ' old% ';
• Select Database ();<---show current databases
• Use Oldboy;
• Select Database ();
• Select version ();
• Select User ();
• Select Now ();

Deleting a database

-drop database Oldboy;

Learn the subconscious to see Help:
mysql> Help DROP database

Connecting to a database

• Use < database name >;
• Use Oldboy;

Table information contained in the current database

• Cut it into the database and check it out.
• Use Oldboy;
• Show tables;
• Show tables like ' user ';
• Show tables in OLDBOY_GBK;

Delete MySQL system more than account

• Drop user ' user ' @ ' host domain ';
• Drop user ' oldboy ' @ ' localhost ';

Note: The deleted post is sent as the result returned above
If the drop cannot be deleted (typically a special character or uppercase), it can be removed in the following way (as the root user, Oldboy host, for example)
Delete from Mysql.user where user= ' root ' and host= ' Oldboy ';
Fluch privileges;<---Handle user-related, it is best to refresh the permissions after the operation is completed

Create a MySQL user and give the user permission to view the Grant command help

1. View the grant command help by helping
   • 。。。 Omitted
   • CREATE USER ' Jeffrey ' @ ' localhost ' identified by ' mypass ';
   • GRANT all on db1.* to ' Jeffrey ' @ ' localhost ';
   • GRANT SELECT on Db2.invoice to ' Jeffrey ' @ ' localhost ';
   • GRANT USAGE on . To ' Jeffrey ' @ ' localhost ' with max_queries_per_hour 90;
   • 。。。 Omitted
2. Operators are more commonly used to create users by using the grant command to create a user's colleague permission authorization, the specific authorization example is:
   • Grant all on db1.* to ' Jeffrey ' @ ' localhost ' identified by ' mypass ';
3. The grant command help above also provides a way to create a user with the creation command before using grant authorization, and create user and authorization permissions separately, for example:
   • Create user ' jeffrey ' @ ' localhost ' identified by ' mypass ';
   • Grant all on db1.* to ' Jeffrey ' @ ' localhost ';
   • The above two commands are equivalent to one of the following commands:
     • Grant all on db1.* to ' Jeffrey ' @ ' localhost ' identified by ' mypass ';

With grant command you to create a user and authorize

• The simple syntax for the grant command is as follows:
  • Grant all privileges the dbname.* to [e-mail protected] identified by ' passwd ';
• The list is described below:

All On to

Grant	Privileges	dbname.*	[email protected]	identified by ' passwd '
Authorization commands	corresponding permissions	Target: Libraries and tables	User name and client host	User password

Description: The above command is to authorize all permissions on the localhost host to manage dbname database through user username, password is passwd. Where username,dbname,passwd can be modified according to the business situation.

• Operation Case 1:
  • Create Oldboy user, have all permissions to test library, allow login from localhost host to manage database, password is oldboy123
  • The specific commands for implementing the above actions are:
    • Grant all privileges the test.* to ' oldboy ' @ ' localhost ' identified by ' oldboy123 ';

Mysqlselect User,host from mysql.user;+------+-----------+| user | Host |+------+-----------+| Root | 127.0.0.1 | | Root | localhost |+------+-----------+2 rows in Set (0.00 sec) Mysqlgrant all privileges on test.* to [email protected] ' Local Host ' identified by ' oldboy123 '; Query OK, 0 rows Affected (0.00 sec) Mysqlflush privileges; Query OK, 0 rows Affected (0.00 sec) Mysqlselect User,host from mysql.user;+--------+-----------+| user | Host |+--------+-----------+| Root | 127.0.0.1 | | Oldboy | localhost | | Root | localhost |+--------+-----------+3 rows in Set (0.00 sec) Mysqlshow grants for [email protected];+----------------- ----------------------------------------------------------------------------------------------+| Grants for [email protected] |+----------------------------------------------------------------------------- ----------------------------------+| GRANT USAGE on *. oldboy ' @ ' localhost ' identified by PASSWORD ' *fe28814b4a8b3309dac6ed7d3237aded6da1e515 ' | | GRANT all privileges the ' test '. * to ' oldboy ' @ ' localhost ' |+---------------------------------------------------------- -----------------------------------------------------+2 rows in Set (0.00 sec) MySQL

Create and Grant mate methods

• First difference function key user username and password passwd, authorized host Loaclhost.
  • Create user ' username ' @ ' localhost ' identified by ' passwd ';
• Then authorize all permissions on the localhost host to manage the dbname database via user username, without a password
  • Grant all on dbanme.* to ' username ' @ ' localhost ';
• Operation Case 2
  • Create Oldgirl user, the test library has all permissions, jade virtual from the localhost host login management database, password is oldgirl123.
  • View the current database user situation, and then execute the corresponding command to create the user as follows:

    Mysql> select User,host from Mysql.user; +--------+-----------+    | user |    Host | +--------+-----------+    | Root |    127.0.0.1 | | Oldboy |    localhost | | Root |    localhost | +--------+-----------+ 3 rows in Set (0.00 sec) mysql> Create user ' oldgirl ' @ ' localhost ' identified by ' oldgirl12    3 ';    Query OK, 0 rows Affected (0.00 sec) Mysql> Grant all on test.* to ' oldgirl ' @ ' localhost ';   Query OK, 0 rows Affected (0.00 sec) mysql> Show grants for [email protected]; +-------------------------------------------------------------------------------------------------------------- --+    |    Grants for [email protected] | +-------------------------------------------------------------------------------------------------------------- --+    |    GRANT USAGE on *. oldgirl ' @ ' localhost ' identified by PASSWORD ' *2cadadd54086d5eb4c9f10e0430084d7f179885c ' | |    GRANT all privileges in ' test '. * to ' oldgirl ' @ ' localhost ' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set  (0.00 sec) mysql>

Authorized host remote link database in LAN:

• According to the grant command syntax, we know that [email protected] ' localhost ' location is a host for authorized access to the database, localhost can be replaced with a domain name, IP address or IP segment, so to authorize the host in the LAN can be implemented by the following methods:
  • Percent-semicolon matching method
    • Mysql> grant all in to [e - Mail protected] ' 10.0.0.% ' identified by ' test123 ';
  • Subnet Mask Configuration method
    • Mysql> grant all in to [e - Mail protected] ' 10.0.0.0/255.255.255.0 ' identified by ' test123 ';

mysql> grant all on *.* to [email protected]‘192.168.206.%‘ identified by ‘test123‘;Query OK, 0 rows affected (0.00 sec)mysql> select user,host from mysql.user;+---------+---------------+| user    | host          |+---------+---------------+| root    | 127.0.0.1     || test    | 192.168.206.% || oldboy  | localhost     || oldgirl | localhost     || root    | localhost     |+---------+---------------+5 rows in set (0.00 sec)mysql> fluch privileges;

• Connect offsite database services via MySQL client:
  • The local mysql-uroot-poldboy123 connection database is equivalent to Mysql-uroot-poldboy123-hlocalhost
  • To remotely connect to the 10.0.0.7 database, the command is Mysql-utest-p-h10.0.0.7, and if you want to connect successfully, you need to pass the following authorization on the 10.0.0.7 database server
  • Mysql> grant all in to [e - Mail protected] ' 10.0.0.% ' identified by ' test123 ';

2018-03-30 SQL Language Learning notes