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

Basic operation command 1. View database list information show databases;mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.11 sec) 2. View the data table information in the database (1) enter the database use mysql Mysql > use mysql;Database changed (2) View data table show tables;mysql > show tables +-- + | Tables_in_mysql | +-+ | columns_priv | | db | | engine_cost | | event. 3. Display table structure information (field) describe user (1) where PRI is the primary key (cannot be empty) definition-- determine the identification characteristics of the unique entity object in the table-- uniqueness, non-emptiness (2) where Extra is the constraint condition mysql > describe user. +-- +-- +-+ | Field | Type | | Null | Key | Default | Extra | + -+-+ | Host | char (60) | NO | PRI | User | char (30) | NO | PRI | Select_priv | enum ('N') | 'Y') | NO | | N | | .4. Create database create database named Mysql > create database auth;Query OK, 1 row affected (0.00 sec) SQL statement

QL language

● is the abbreviation of Structured Query Language, that is, structured query language ● is the standard language of relational database ● is used to maintain and manage database, such as data query, data update, access control, object management and other functions.

SQL classification

● DDL: data definition language ● DML: data manipulation language ● DQL: data query language ● DCL: data control language typical database index algorithm-binary search definition: take a data as a reference, those smaller than him on the left, larger than him on the right. DDL operation command

DDL statements are used to create database objects, such as libraries, tables, indexes, etc.

1. Use DDL statement to create new database and table: creste databae database name; mysql > create database auth;Query OK, 1 row affected (0.00 sec) to create data table: create table table name (field definition …) ; mysql > create table info (- > ID int (4) not null,-> name varchar (8) not null,-> address varchar (10) not null,-> grade decimal default 0,-> primary key (ID); Query OK, 0 rows affected (0.01 sec) mysql > desc info +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | ID | int (4) | NO | PRI | NULL | | name | varchar (8) | NO | | NULL | | address | varchar (10) | NO | | NULL | | Grade | decimal (10Force 0) | YES | 0 | | +-+-| -+ 4 rows in set (0.00 sec) 2. Delete the specified data table using the DDL statement: drop table [database name.] Table name delete the specified database: drop database database name mysql > drop database auth;Query OK, 0 rows affected (0. 05 sec) DML operation command

The DML statement is used to manage the data in the table

This includes the following operations.

● insert: inserting new data

● update: update existing data

● delete: delete unwanted data

1. Insert a new data record insert into table name into the data table (field 1, field 2,.) Values (value of field 1, value of field,.); mysql > insert into info values (1 row affected 'Zhou Mei', 'Nanjing', 80); Query OK, 1 row affected (0. 00 sec) mysql > insert into info values (2 sec 'Zhang Qiandi', 'Nanjing', 66); Query OK, 1 row affected (0. 00 sec) mysql > insert into info values (3 # Li Xiangyang, 'Shanghai', default); Query OK, 1 row affected (0. 02 sec) 2. Modify and update the data record update table name of data table P F: set field name 1 = value 1 [, field name 2 = value 2] where conditional expression; mysql > update info set address = 'Nanjing' where ID=3;Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 03. Delete the specified data records delete from table name where conditional expression in the data table; statements without where conditions delete all records in the table (with caution); # delete the specified data records in the table mysql > delete from info where ID=2; Query OK, 1 row affected (0.02 sec) # delete table mysql > drop table info; Query OK, 0 rows affected (0.00 sec) # delete library mysql > drop database test; Query OK, 0 rows affected (0 sec) DQL operation command

DQL is a data query statement with only one item: SELECT

Used to find qualified data records from the data table

1. When querying, you can not specify the conditional selext field name 1 and field name 2. From table name; mysql > select * from info +-+ | ID | name | address | score | +-+ | 1 | Zhou Mei'er | Nanjing | 80 | | 3 | Li Xiangyang | Nanjing | 0 | +- -+ 2 rows in set (0.00 sec) 2. Specify conditional select field name 1 and field name 2 when querying. From table name where conditional expression; mysql > select address from info where address = 'Nanjing'; +-+ | address | +-+ | Nanjing | | Nanjing | +-+ 2 rows in set (0.04 sec) DCL operation command 1. Set user permissions (create a new user if the user does not exist) GRANT permission list ON database name. Table name TO username @ source address [IDENTIFIED BY 'password'] mysql > grant all privileges on *. * to 'root'@'%' identified by' abc123';Query OK, 0 rows affected, 1 warning (0.08 sec) 2. View the user's permissions SHOW GRANTS FOR username @ source address mysql > show grants for 'root'@'%' +-- + | Grants for root@% | +-- -- + | GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' WITH GRANT OPTION | +-+ 1 row in set (0.00 sec) 3. Revoke the user's permissions REVOKE permission list ON database name. Table name FROM username @ source address mysql > revoke all on *. * from 'root'@'%';Query OK, 0 rows affected (0.00 sec)

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.