Shulou(Shulou.com)06/01 Report--

Write at the front

In the daily management and application of the database, no matter whether the administrator is suitable to access a database, SQL language is almost used. therefore, it is very important to master the SQL language and its script. SQL (structured query language) is not only the most popular relational database query language, but also the standard language of database.

Database language classification

According to different functions, data languages can be divided into four categories: database definition language (DDL), database operation language (DML), database control language (DCL), database query language (DSL).

DDL: the database definition language consists of a set of SQL commands for creating and defining database objects. For example, you can create databases, create tables, modify views, and so on. Database objects include libraries, users, views, tables, triggers, stored procedures, and so on. The DDL statement and its functions are as follows:

Create

Alter

Drop

Rename

Truncate

Delete database objects

Modify database objects

Create a database object

Modify database object name

Delete all the contents of the table

DML: database manipulation language is mainly used to deal with the content of data in the database, additions, deletions, changes and other operations about the content of data.

Insert

Update

Delete

Call

Merge

Commit

Rollback

Insert data into a table or view

Update

Delete

Calling procedure

Merge operation

Submit the current changes and write them to the database

Rollback to cancel all actions since the last submission

DCL: database control language is generally used to modify database operation permissions.

Grant

Revoke

Grant authority

Reclaim permission

DSL: database query language for querying the contents of the database. Its syntax is the most complex and rich in the whole SQL language. It's also very powerful.

Select

Retrieve data from a table or view

Note: for database management software such as MySQL, it is not case-sensitive. However, in order to develop good programming habits, we still have to follow certain rules. It is recommended that SQL keywords should be capitalized or written in a consistent way. Because the SQL keywords executed by SQL are saved in the cache, a consistent way of writing is easier to hit by the cache and improves execution efficiency.

Simple example of SQL statement

Help how to use

Learning anything and learning to use help usage is the most effective way to improve yourself. Mysql uses help keywords to view help information.

For example:

MariaDB [(none)] > help drop database delete the correct pose of the library

Name: 'DROP DATABASE'

Description:

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Simple example

[root@zachary] # mysql-u root-p

MariaDB [(none)] > show databases; # shows all databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

Create a database

MariaDB [(none)] > create database if not exists zachary; create if the data does not exist

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)] > use Zachary switches the currently used database to Zachary

Database changed

Create a tabl

MariaDB [zachary] > create table person (

-> id tinyint primary key

-> name varchar (8) not null

-> age tinyint)

Query OK, 0 rows affected (0.02 sec)

MariaDB [zachary] > show tables

+-+

| | Tables_in_zachary |

+-+

| | person |

+-+

1 row in set (0.01 sec)

Modify the table

MariaDB [zachary] > desc person; # View table structure

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | tinyint (4) | NO | PRI | NULL |

| | name | varchar (8) | NO | | NULL |

| | age | tinyint (4) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

MariaDB [zachary] > alter table person modify id int; modifies the data type of the id field

MariaDB [zachary] > alter table person add email varchar (20); add column email

MariaDB [zachary] > desc person

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | name | varchar (8) | NO | | NULL |

| | age | tinyint (4) | YES | | NULL |

| | email | varchar (20) | YES | | NULL |

+-+ +

4 rows in set (0.01sec)

MariaDB [zachary] > alter table person drop email; # Delete columns mysql does not support multiple column deletions

MariaDB [zachary] > alter table person change age sex varchar (2); modify column name

MariaDB [zachary] > alter table person rename to student; rename table

MariaDB [zachary] > rename table person to student; rename table

Modify constraint condition

MariaDB [zachary] > alter table student add unique key (name); add unique key

MariaDB [zachary] > alter table student add check (sex in); add check constraint

MariaDB [zachary] > desc student

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | name | varchar (8) | NO | UNI | NULL |

| | sex | varchar (2 | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

Delete tabl

MariaDB [zachary] > drop table student

Insert data

MariaDB [(zachary)] > create table if not exists test (

-> id tinyint unsigned primary key

-> name varchar (20) not null

-> sex enum ('masking menagerie f') default' m'. The default value is'm'if it is the specified value when inserted.

-> email varchar (30)

MariaDB [zachary] > insert into test values (1) insert data in the order in which the database fields were created

MariaDB [zachary] > insert into test (id,sex,name,email) values (2) insert data in custom field order

MariaDB [zachary] > insert into test (id,sex,name,email) values. Insert multiple rows of data at one time.

Query and modify data in the table

MariaDB [zachary] > select * from test; queries all data in the table

+-- +

| | id | name | sex | email | |

+-- +

| | 1 | zachary | m | zachary_yzh@126.com |

| | 2 | marry | f | marry@163.com |

| | 3 | jack | m | jack@qq.com |

| | 4 | mali | NULL | mali@foxmail.com | |

+-- +

MariaDB [zachary] > update test set sex='f' where id=4; modifies the gender of mali

MariaDB [zachary] > select name from test where sex='m'; to see who the men are in the table.

MariaDB [zachary] > select name from test where id > = 3; people whose id is greater than 3

MariaDB [zachary] > select name from test where id > = 2 and id select name from test where id between 2 and 4; use the between and keyword to determine the range. Between and is a closed interval

MariaDB [zachary] > insert into test (id,name) value (5) insert tom information.

MariaDB [zachary] > select * from test where name='tom'; to view the information of tom. There is no gender when inserting. Use the default values used when creating the table.

+-- +

| | id | name | sex | email | |

+-- +

| | 5 | tom | m | NULL |

+-- +

1 row in set (0.01 sec)

MariaDB [zachary] > select name from test where email rlike'. *. Check who uses NetEase's mailbox. Rlike is the regular expression matching method.

MariaDB [zachary] > select * from test where name like'_ r% query; the wildcard matching query method uses the like keyword to query the user information with the third letter r in the name. _ represents any single character, and% represents any character of any length.

MariaDB [zachary] > select * from test where email is null; queries users who do not use mailboxes. You cannot use the = = sign to match when the query keyword is NULL. Instead, use is null or is not null.

Use select statements to create data in a table

MariaDB [zachary] > create table emp select * from test

MariaDB [zachary] > select * from emp

+-- +

| | id | name | sex | email | |

+-- +

| | 1 | zachary | m | zachary_yzh@126.com |

| | 2 | marry | f | marry@163.com |

| | 3 | jack | m | jack@qq.com |

| | 4 | mali | f | mali@foxmail.com |

| | 5 | tom | m | NULL |

+-- +

5 rows in set (0.00 sec)

Delete data from the table

MariaDB [zachary] > delete from emp where email is null; deletes user data with empty email

MariaDB [zachary] > truncate emp; clears all data in the table (no logging)

Create users and authorizations

MariaDB [zachary] > create user 'yzh'@'172.18.%.%' identified by' yzh01'; to create users and change passwords

In mysql, a user account consists of a user name and a user host name. Hosts can use network segments, wildcards, or hostnames that correspond to the parsed ip.

MariaDB [zachary] > set password for 'yzh'@'172.18.%.%'=password (' 123456'); modify the correct posture of the user's password. It is not recommended to change the mysql.user table directly.

MariaDB [zachary] > grant select, insert on Zachary.test to 'yzh'@'172.18.%.%'; grants user yzh query and insert permissions on the test table.

MariaDB [zachary] > grant ALL on zachary.* to 'tony'@'localhost' identified by' zacharyadmin' with grant option;; creates users directly when granting permissions. The tony user is granted all permissions on the Zachary database, and the user can grant permissions to other users, and the with grant option option can be used with caution when granting other users their own permissions.

MariaDB [zachary] > revoke insert on zachary.test from 'yzh'@'172.18.%.%'; reclaims the yzh user's insert rights in the Zachary.test table

MariaDB [(none)] > create user 'test'@'localhost' identified by' 123456users; create a test user

[root@zachary ~] # mysql-u tony-p uses tony user connection

Enter password:

MariaDB [zachary] > select user ()

+-+

| | user () |

+-+

| | tony@localhost |

+-+

1 row in set (0.00 sec)

MariaDB [zachary] > grant select on zachary.* to 'test'@'localhost'

Use tony users to grant test users query operations against the Zachary database.

[root@zachary] # mysql-u root-p

MariaDB [(none)] > revoke all on zachary.* from 'tony'@'localhost'; reclaims all permissions of the tony user, but the permissions of other users he authorizes are not affected. The permissions of Revoke are not cascaded and reclaimed.

[root@zachary ~] # mysql-u test-p # users using test can still query

MariaDB [(none)] > select * from zachary.test

+-- +

| | id | name | sex | email | |

+-- +

| | 1 | zachary | m | zachary_yzh@126.com |

| | 2 | marry | f | marry@163.com |

| | 3 | jack | m | jack@qq.com |

.. ..

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.