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

Summary of SQL sentences SQL language is an abbreviation for Structured Query Language, that is, structured query language is a standard language for relational databases to maintain and manage databases, such as data query, data update, access control, object management and other functions SQL classification

(1) DDL: data definition language

(2) DML: data manipulation language

(3) DQL: data query language

(4) DCL: data control language

DDL operation command 1, DDL statement is used to create database objects, such as libraries, tables, indexes, etc. 2, use DDL statements to create new libraries, tables

(1) create a database: creste databaes database name

(2) create data table: create table table name (field definition …)

3. Delete libraries and tables using DDL statement

(1) Delete the specified data table: drop table [database name] table name

(2) Delete the specified database: drop database database name

DML operation commands 1 and DML statements are used to manage the data in the table 2, including the following operations

(1) insert: insert new data

(2) update: update the original data

(3) delete: delete unwanted data

3. Insert a new data record into the data table

Insert into table name (field 1, field 2,.) Values (the value of field 1, the value of field,.)

4. Modify and update the data records of data table P F

Update table name set field name 1 = value 1 [, field name 2 = value 2] where conditional expression

5. Delete the specified data record in the data table

(1) delete from table name where conditional expression

(2) statements without where conditions delete all records in the table (proceed with caution)

DQL operation command 1, DQL is a data query statement, there is only one: SELECT2, used to find qualified data records from the data table 3, query conditions can not be specified

Selext field name 1, field name 2. From table name

4. Specify conditions when querying

Select field name 1, field name 2. From table name where conditional expression

DCL statement operation 1. Set user rights (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']

2. View the permissions of the user

SHOW GRANTS FOR user name @ Source address

3. Revoke the user's rights

REVOKE permission list ON database name. Table name FROM user name @ Source address

Operation example 1. View database list information mysql > show databases / / View database list information +-+ | Database | +-+ | information_schema | | mysql | / / where mysql is the system database | performance_schema | | sys | +- -+ 4 rows in set (0.02 sec) 2, Create database mysql > create database school / / create database schoolQuery OK, 1 row affected (0.02 sec) mysql > show databases / / View database list information +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | / / Database created successfully | sys | +- -+ 5 rows in set (0.00 sec) mysql > 3, Create the table mysql > mysql > use school / / use database schoolDatabase changedmysql > show tables;Empty set (0.00 sec) mysql > create table info (- > id int (4) not null,-> name char (10) not null,-> address varchar (50) default 'beijing',-> score decimal,-> primary key (id)); / / create table infoQuery OK, 0 rows affected (0.01 sec) mysql > describe info / / View the table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +-- -- + | id | int (4) | NO | PRI | NULL | name | char (10) | NO | | NULL | | address | varchar (50) | YES | | beijing | | score | decimal (10L0) | YES | | NULL | +-+- -+ 4 rows in set (0.03 sec) 4, Add data to the table and view the data in the table mysql > mysql > insert into info (id Name,address,score) values. (1. Shanghaifang) / / add data Query OK, 1 row affected (0.03 sec) mysql > insert into info (id,name,address,score) values. / / add data Query OK, 1 row affected (0. 01 sec) mysql > insert into info (id,name,address,score) values (3) / / add data Query OK, 1 row affected (0.00 sec) mysql > insert into info (id,name,address,score) values; / / add data Query OK, 1 row affected (0.00 sec) mysql > select * from info / / View data in info table +-+ | id | name | address | score | +-+ | 1 | stu01 | shanghai | 88 | | 2 | stu02 | nanjing | 79 | 3 | stu03 | beijing | 90 | 4 | stu04 | 60 | +-+ 4 rows in set (0.00 sec) 5. Modify and delete data in the table mysql > update info set address='hangzhou' where id=4 and name='stu04' / / modify the address with id 4 to "hangzhou" Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from info / / View the data in the table +-+ | id | name | address | score | 1 | stu01 | shanghai | 88 | | 2 | stu02 | nanjing | 79 | 3 | stu03 | beijing | 90 | 4 | stu04 | hangzhou | 60 | +- -+ 4 rows in set (0.01sec) mysql > delete from info where name='stu04' / / Delete the data Query OK with name "stu04" in the table, 1 row affected (0.02 sec) mysql > select * from info / / View the data in the table +-+ | id | name | address | score | +-+ | 1 | stu01 | shanghai | 88 | | 2 | stu02 | nanjing | 79 | | 3 | stu03 | beijing | 90 | + -+ 3 rows in set (0.00 sec) 6. Delete tables and databases mysql > drop table info / / delete the table infoQuery OK, 0 rows affected (0.06 sec) mysql > show tables; / / View the table, delete successfully Empty set (0.00 sec) mysql > drop database school; / / delete database Query OK, 0 rows affected (0.04 sec) mysql > show databases / / View the database Deleted successfully +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec) 7, settings, View and revoke user permissions mysql > show grants for 'root'@'%' / / View permission +-- + | Grants for root@% | +-- -- + | GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' WITH GRANT OPTION | +-- + 1 row in set (0.00 sec) mysql > revoke all on *. * from 'root'@'%' / / remove permission Query OK, 0 rows affected (0.03 sec) mysql > show grants for 'root'@'%' / / View permission +-- + | Grants for root@% | +-- -+ | GRANT USAGE ON *. * TO 'root'@'%' WITH GRANT OPTION | +-- + 1 row in set (0.00 sec) mysql > grant all on *. * to root@'%' identified by' abc123' / / add permission Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > show grants for 'root'@'%' / / View permission +-- + | Grants for root@% | +-- -- + | GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' WITH GRANT OPTION | +-+ 1 row in set (0.00 sec) mysql >

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.