Introduction to SQL Statements

.DDL statement

Data Definition Language. These statements define different data segments, databases, tables, columns, indexes and other data objects. Commonly used statement keywords mainly include create drop alter, etc.;
In which database to create a table, you first need to switch to this database

One, create create table

use data storage name;    //use a specified database

2. Create a table under this database (keywords are not case-sensitive)

 
create table table name(Field 1 Type, Field 2 Type);   //Create a table in a database
create table hero_table ( id int,name varchar(10));    //create table
 
//Create table and add constraints
CREATE TABLE table name (
      Field 1 Type 1 Constraint 1 Constraint 1,
      Field 2 Type 2 Constraint 2 Constraint 2 
)；
//Create table and add constraints
CREATE TABLE hero_table4 (
      id INT(11) NOT NULL PRIMARY KEY,
      NAME VARCHAR(10) NOT NULL UNIQUE KEY 
)

3. Use create to copy the table

//Only the table structure and constraints are copied, but the data is not copied
CREATE TABLE copied table LIKE original watch
//Copy table structure and data, but not constraints
CREATE TABLE copied table AS SELECT * FROM original watch

2. alter keyword

1. Add fields

ALTER TABLE table name ADD field name field constraint
ALTER TABLE table name ADD COLUMN field name field constraint

2. Add index

    ALTER TABLE table name ADD INDEX index name (field name)

3. Add primary key

    ALTER TABLE table name ADD PRIMARY KEY (field name)
    ALTER TABLE table name ADD UNIQUE KEY (field name)

4. Add foreign key constraints

 ALTER TABLE Need to add foreign key table name ADD FOREIGN KEY foreign key name(Fields set as foreign keys) REFERENCES The foreign key table name is added(Set as the field to be added as a foreign key)

5. Pay attention to the compatibility and precision of the field type when modifying the field type, unless all the data in the column is null

ALTER TABLE table name CHANGE COLUMN NAME Modify field Modify to field type
ALTER TABLE table name MODIFY COLUMN Modify field Modify to field type

6. Modify and delete default in the table

//set default
ALTER TABLE table name ALTER COLUMN field name SET DEFAULT  'modified value'
//delete default
ALTER TABLE table name ALTER COLUMN field name DROP DEFAULT

7. Disable and enable constraints

//delete table field
ALTER TABLE table name DROP COLUMN field name
//delete primary key
ALTER TABLE table name DROP PRIMARY KEY
//drop index
ALTER TABLE table name DROP INDEX indexed field name
//Remove foreign key constraints
ALTER TABLE table name DROP FOREIGN KEY Field name of foreign key

8. Delete table fields, primary keys, indexes, foreign key constraints

//delete table field
ALTER TABLE table name DROP COLUMN field name
//delete primary key
ALTER TABLE table name DROP PRIMARY KEY
//drop index
ALTER TABLE table name DROP INDEX indexed field name
//Remove foreign key constraints
ALTER TABLE table name DROP FOREIGN KEY Field name of foreign key

3. drop keywords

1. Delete the database

DROP DATABASE data storage name

2, delete the table

DROP TABLE table name

Four, truncate keyword

The difference from drop: truncate only deletes data but does not delete the structure (definition) of the table, freeing up space
delete table

truncate table table name;

.DML statement:

A data manipulation statement is a programming statement used for database operations, running access to objects and data in the database, and is usually a subset of database-specific programming languages. , UPDATE, DELETE three instructions as the core

1. insert statement

Delete from table name [where condition];
DELETE FROM project WHERE id>11
DELETE FROM project WHERE id=5

1. Insert a whole record:

Insert into table name(field) values(data)；
INSERT INTO student(id,NAME) VALUES(1,'student 1');
INSERT INTO student(id,NAME) VALUES(2,'student 2');

2. Insert multiple records at one time:

insert into table name(Field 1, Field 2, Field 3) values(data 1, data 2, data 3)，(data 1, data 2, data 3)；
INSERT INTO project(pid,pname,tijiaoren) VALUES(12,'item 53',5),(13,'item 55',5);

Second, the update statement

Update table name set field name=new value[,column name 2=new value,...][where condition];
//Change the status of all even item id s to yes
UPDATE project SET zt='Yes' WHERE MOD(id,2)=0

Three, delete statement

The delete statement is a database operation language (dml). This operation will be placed in the rollback segment and will take effect after the transaction is committed; it will be triggered when the corresponding trigger is executed.

Delete from Table Name [where condition];
DELETE FROM project WHERE id>11
DELETE FROM project WHERE id=5

Fourth, the select statement

Select *|distinct field 1,field 2,...... expression from table name[where condition]
SELECT Company FROM Orders             //If there are the same values in the table, they will be listed
SELECT DISTINCT Company FROM Orders    //This will only show one if there are identical values in the table
SELECT * FROM Orders                   //Display all values in the table
 
//subqueries as operands
SELECT (SELECT id FROM student WHERE id=1) FROM DUAL
//Subquery as condition
SELECT * FROM project WHERE tijiaoren>(SELECT 2 FROM DUAL)
//Subqueries are used as data tables
SELECT * FROM (SELECT * FROM project , student WHERE project.tijiaoren=student.id)  a WHERE a.name LIKE '%3%'
//Multi-column comparison
SELECT (3,2)<(3,4) FROM DUAL
//Cartesian Product
SELECT * FROM table name 1,table name 2
//Inner join (retain matching values of on in the two tables)
SELECT * FROM table name 1 INNER JOIN table name 2 ON table name 1.field=table name 2.field;
//Left outer join (retain the values in table 1 and fill in null for table 2)
SELECT * FROM table name 1 LEFT JOIN table name 2 ON table name 1.field=table name 2.field
//Right outer join (retain the values in table 2 and fill in null for table 1)
SELECT * FROM table name 1 RIGHT JOIN table name 2 ON table name 1.field=table name 2.field
//Full outer join (retain the values of the two tables and fill in null respectively)
SELECT * FROM table name 1 LEFT JOIN table name 2 ON table name 1.field=table name 2.field

.DCL statement

is a database control language. Is a statement used to set or change database user or role permissions, including (grant,deny, revoke, etc.) statements

1. Create a user

- CREATE USER 'username'@'CPU name' IDENTIFIED BY 'password';

.2 Authorized Users

- GRANT permission 1, permission 2... ON data storage name.Table Name TO 'username'@'CPU name';

3 Revocation of authorization

- REVOKE permission 1, permission 2... ON data storage name.Table Name TO 'username'@'CPU name';

4 View permissions

- SHOW GRANTS FOR 'username'@'CPU name';

.5 delete user

- DROP USER 'username'@'CPU name';

6. Modify the administrator password

mysqladmin -uroot -p password new password
#Enter the old password after entering
 Note: need to be logged in MySQL operation in the case of

Also this is done from the command line

6.2 Modify common user password

- set password for 'username'@'CPU name' = password('new password');

Tags: Database MySQL SQL

Posted by TFD3 on Fri, 03 Jun 2022 02:05:34 +0530

Programs Team