Google's younger brother learning Backstage (--mysql) (2)

Explore the puzzles of Android soft keyboard
Delve into Android Async Essentials Handler
The essential cornerstone of Android's mainstream framework
Standing on the shoulder of the source code full solution scroller working mechanism

Android multi-resolution adaptation framework (1)-Core Foundation
Android multi-resolution adaptation framework (2)-Principle analysis
Android multi-resolution adaptation framework (3)-User Guide

Custom View Series Tutorial 00– Overthrow yourself and the past, re-learn custom view
Custom View Series Tutorials 01– Common Tools Introduction
Custom View Series Tutorial 02–onmeasure Source detailed analysis
Custom View Series Tutorial 03–onlayout Source detailed analysis
Custom View Series Tutorial 04–draw source code Analysis and its practice
Custom View Series Tutorial 05– Sample Analysis
Custom View Series Tutorial 06– Detailed View Touch event handling
Custom View Series Tutorial 07– ViewGroup distributing Touch events
Custom View Series Tutorial 08– the generation and processing of sliding conflicts

Copyright Notice

• This article original Google's younger brother
• Author Blog address: HTTP://BLOG.CSDN.NET/LFDFHL

SQL Brief

Structure Query Language (Structured Query language) is referred to as SQL, which is defined by the U.S. National Standards Office (ANSI) as the United States standard for relational database languages, and then adopted by the International Standards Organization (ISO) as the international standard for relational database languages. Database management system can manage database through SQL, define and manipulate data, maintain data integrity and security.

Benefits of SQL

• Easy to learn, with strong operability
• Most important database management systems support SQL
• Highly non-procedural; most of the work is done automatically by the DBMS when working with SQL database

Classification of SQL

• DDL (Data Definition Language)
  Data definition language for manipulating databases, tables, columns, etc.;
  Frequently used statements: CREATE, ALTER, DROP
• DML (Data manipulation Language)
  A data manipulation language used to manipulate data in tables in a database;
  Common statements: INSERT, UPDATE, DELETE
• DCL (Data Control Language)
  Data Control language, which is used to manipulate access rights and security levels;
  Common statements: GRANT, DENY
• DQL (Data Query Language)
  Data query language, used to query data
  Common statements: SELECT

Here, be aware of the distinction between DDL and DML:
They do not have the same object and level of action. The object of a DDL operation is a database, or a table, or a column in a table, in which DML operates the data in tables.

After understanding these basics, we'll learn more about DDL, DML, DCL, DQL

DDL

Create a database

Create Database db1;

Create a database named DB1

Show CREATE Database db1;

To view the database db1 you just created, the results are as follows:

| Database | Create Database
| DB1 | CREATE DATABASE db1 /!40100 DEFAULT CHARACTER SET UTF8 /**

As you can see from here, the database you just created is using UTF8 as the character set.

Of course, we can also specify a character set for the database when it is created

Create DATABASE DB2 character set GBK;

Querying the database

show databases;

Querying out all the databases

Deleting a database

Drop database DB2;

In fact, it can be seen from this to create a database and delete the database is very similar, but their key is different; The former uses the create the latter is the drop

Modify Database

ALTER DATABASE DB1 character set GBK;

Modify the character set of the database db1 to GBK

Switch database

Use DB1;

means to start using the database now DB1

View the database currently in use

Select Database ();

Note that the statement finally has a parenthesis ()

Create a table

The syntax is as follows:

CREATE TABLE Table name (
Field 1 field type,
Field 2 field type,
...............
field N field type
);

Example, create an employee table:

Create an employee table here that contains basic information about the employee.

View all tables in the current database

Show tables;

View the table you just created

Show create table employee;

View field information for a table

Describe employee;

can also be abbreviated as:

DESC employee;

Modifying the character set of a table

ALTER TABLE employee character set GBK;

Add columns to a table

ALTER TABLE employee add column photo blob;

A column with a data type of BLOB was added to the table photo

can also be abbreviated as:

ALTER TABLE employee add photo blob;

Modify the length of a column

ALTER TABLE employee Modify job varchar (170);

Modify the length of the job field in the table to 170

Modify the name of a column

ALTER TABLE employee change name username varchar (100);

Rename the table name to username

Delete Column

ALTER TABLE employee DROP photo;

Delete a photo column from a table

Modify the name of the table

Rename table employee to user;

Delete a table

drop table user;

DML

DML is used to add, delete, and change the data in a table. Common statements are insert, UPDATE, DELETE

Well, let's start by creating a table.

CREATE TABLE Employee (
ID int,
Username varchar (100),
Gender varchar (10),
Birthday date,
Salary float (10,2),
Entry_date date,
Resume text
);

Insert operation: Insert

Syntax for insert:

INSERT into table name (column name 1, column Name 2 ...) VALUES (column value 1, column value 2 ...);

Please note:

• Column name and column value of the type, number, order to correspond
• column values do not exceed the length of the column definition
• If you insert a null value, use NULL
• The inserted date and character are enclosed in quotation marks, for example: ' Sun ', ' 2016-09-04 '

Example of insert:

INSERT into employee
(Id,username,gender,birthday,salary,entry_date,resume)
VALUES (1, ' Grand Maria ', ' female ', ' 1990-09-12 ', 20000.00, ' 2010-11-22 ', ' Beauty ');

INSERT into employee
(Id,username,gender,birthday,salary,entry_date,resume)
VALUES (2, ' Enrique Sister ', ' female ', ' 1980-09-12 ', 30000.00, ' 2000-12-12 ', ' good ');

INSERT into employee
(Id,username,gender,birthday,salary,entry_date,resume) VALUES
(3, ' Well empty sister ', ' female ', ' 1987-07-28 ', 50000.00, ' 2001-08-09 ', ' great ');

Of course, you can also insert data in bulk:

INSERT into employee VALUES
(4, ' Cedar original apricot glass ', ' female ', ' 1992-04-01 ', 60000.00, ' 2014-09-03 ', ' very good '),
(5, ' Nozomi Sasaki ', ' female ', ' 1993-05-09 ', 70000.00, ' 2013-04-04 ', ' very nice '),
(6, ' Maiko Ito ', ' female ', ' 1995-11-07 ', 80000.00, ' 2012-03-05 ', ' very sexy ');

Modify Operation: UPDATE

Syntax for UPDATE:

UPDATE table Name set column Name 1 = column value 1, column Name 2 = column Value 2 .... WHERE Column name = value

Example of update:

UPDATE employee SET salary=88000;

Change the salary of all employees in the table to 88000

UPDATE employee SET salary=99000 WHERE username= ' great Jersey Maria ';

Change the salary of username in the table to 99000 for the employees of the Da Ze Maria

UPDATE employee SET salary=69000,birthday= ' 1993-02-28 ' WHERE username= ' well empty sister ';

Username in the table as well empty sister's staff salary modified to 69000, birthday changed to 1993-02-28

UPDATE employee SET salary=salary+1000 WHERE username= ' Nozomi Sasaki ';

Increase the salary of employees who are username to Nozomi Sasaki in the table by 1000

Delete operation: Delete

The syntax for delete:

DELETE from table name WHERE column name = value

Example of Delete:

DELETE from employee WHERE Username= ' great Jersey Maria ';

Delete the username in the table as a member of the DA Ze Maria

DELETE from employee;

Delete all the data in the table using Delete, after which the table structure still exists, the deleted data can be retrieved

TRUNCATE TABLE employee;

Use truncate to delete all data in the table, and after you do this, the system drops the table and then creates an identical new table, which executes much faster than delete, but note that the data deleted by this method cannot be retrieved.

DCL

As we said before: DCL (Data Control language) is used to define access rights and security levels;

Create user

The syntax for creating a user is:

Create user username @ip address identified by password;

Take a look at the following example:

Create user [email protected] identified by ' 123456 ';

Create a new user here, the user name xy password is 123456

Note that the Command Update permission table (grant tables) needs to be executed after creating a new user

FLUSH privileges;

Authorization to the user

The user-authorized syntax is:

Grant permission 1, permission 2,........, permission n on the database name. * To username @ip address identified by ' password ';

If you grant all permissions to the operation database to the user, the syntax is:

Grant all on the database name. * To User name @ip address identified by ' password ';

Take a look at the following example:

Grant all on db1.* to [e-mail protected] identified by ' 123456 ';

Revoke authorization

The syntax for revoking an authorization is:

Revoke permissions 1, permissions 2,........, permissions n on the database name. * From user name @ip address;

If you revoke all permissions for a database owned by a user, the syntax is:

Revoke all on db1.* from [email protected];

View User Permissions

The syntax for viewing user permissions is:

Show grants for user name @ip address;

Take a look at the following example:

Show grants for [email protected];

Delete User

The syntax for removing user rights is:

Drop user username @ip address;

Take a look at the following example:

drop user [email protected];

Google's younger brother learning Backstage (--mysql) (2)