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

1. SQL grammar

1. Database table

A database usually contains one or more tables. Each table is identified by a name and the table contains rows with data.

2. Case

SQL is not case-sensitive.

3. Semicolon after SQL statement

Some databases require a semicolon at the end of each SQL command, such as mysql.

4. DML and DDL of SQL

SQL can be divided into two parts: DML (data manipulation language data manipulation language) and DDL (data definition language data definition language)

The instructions to query and update data make up the DML part of SQL:

Select-read data from database tables

Update-updates the data in the database table

Delete-removes data from the database table

Insert into-inserts data into a database table

The DDL part of SQL refers to the commands that create or delete tables, update table structure, define indexes, specify links between tables, and impose constraints between tables.

Create database-create a new database

Alter database-modify the database

Create table-create a new table

Alter table-- update the table structure

Drop table-Delete the table

Create index-create an index

Drop index-Delete the index

Second, create an instance

Take MySQL as an example:

Mysql > create database study;mysql > use study;mysql > create table students (id int auto_increment primary key,name varchar, sex char (10); mysql > insert into students (name,sex) values ('zhangsan','man'), (' lisi','woman'), ('wangwu','man'), (' zhaoliu','woman'); mysql > select * from students

Note: in MySQL, all values except numbers need to be included with''.

III. Basic SQL statement

1. Select: used to get data from a table.

Format: select column name from table name

Example:

① selects all student names from the students table:

Mysql > select name from students

② selects all student names and genders from the students table:

Mysql > select name,sex from students

Multiple column names are separated by commas.

③ looks at all the data in the students table:

Mysql > select * from students

"*" represents all column names.

2. Distinct: the table may contain duplicate data, but sometimes you want to list only different values. At this point, use distinct to return a unique different value.

Format: select distinct column name from table name

Example:

① lists all genders from the students table (... ):

Mysql > select distinct sex from students;+-+ | sex | +-+ | man | | woman | +-+ man and woman are listed once each.

3. Where: used to conditionally get data from a table, usually in conjunction with select.

Format: select column name from table name where column name operator value

The operator here contains = (equal to), (or! =, not equal to), > = (greater than or equal to), (greater than), select name from students where sex = 'man'

This lists the two values of 'zhangsan','wangwu'.

② lists all students with ID no more than 3 from the students table:

Mysql > select name from students where id select name from students where id between 2 and 3

This lists the two values of 'lisi','wangwu'. Different databases treat the two boundary values (2 and 3) differently. Mysql lists columns that contain 2 and 3, but some databases do not.

4. And and or: used to filter more than one record. You can combine two or more conditions in the where clause.

Example:

① lists the names of students whose ID is not more than 3 and whose gender is female from the students table:

Mysql > select name from students where id select name from students order by name

② displays the names in the students table from largest to smallest in terms of id:

Mysql > select id,name from students order by id desc

6. Insert into: used to insert new rows (data) into the table.

Format: insert into table name values (value 1, value 2,... )

Insert data into the specified column: insert into table name (column 1, column 2,... ) values (value 1, value 2,... )

Example:

① inserts a row into the students table, id 5, name qianqi, gender woman:

Mysql > insert into students values (5 recordings Qianqiqi pageant women')

② inserts two rows into the students table:

Mysql > insert into students (name,sex) values ('sunba','man'), (' wujiu','man')

There is a line in a (), and multiple lines are separated by commas.

7. Update: used to modify the data in the table.

Format: update table name set column name = new value where column name = some value; two columns are different.

Example:

① changed the gender of zhangsan in the students table to woman:

Mysql > update students set sex='woman' where name='zhangsan'

8. Delete: used to delete rows (data) in the table.

Format: delete from table name where column name = value

Example:

① removes the row named wujiu from the students table:

Mysql > delete from students where name='wujiu'

② deletes all rows in the students table:

Mysql > delete from students; or mysql > delete * from students

This only deletes all data in the table, not the table structure, indexes, and attributes.

9. Alter: used to add, modify, delete columns and modify table names in the data table.

① adds a column to the table:

Format: alter table table name add column name data type

Example: add a column of 'age', data type' int': to the students table

Mysql > alter table students add age int

② modifies the column name of a column in the table:

Format: alter table table name change current column name modified column name data type

Example: change 'name' to' perons': in students table

Mysql > alter table students change name persons varchar (255)

③ modifies the data type of a column in the table:

Format: alter table table name modify column name modified data type

Example: change the data type of persons in the students table to char:

Mysql > alter table students modify persons char

④ modifies the table name

Format: alter table table name rename modified table name

Example: change the name of the students table to learners:

Mysql > alter table students rename learners

⑤ deletes a column from the table

Format: alter table table name drop column name

Example: delete the age column in the learners table:

Mysql > alter table learners drop age

10. Drop: used to delete database tables and indexes.

① deletes the database:

Format: drop database database name

② deletes the data table:

Format: drop table datasheet name

③ delete index, syntax for MySQL:

Format: alter table table name drop index name

11. Primary key: the primary key that uniquely identifies each record in the database table.

The primary key must be unique and cannot contain null values. Each table should have one primary key, and each table can have only one primary key.

In the example, id is set as the primary key, and primary key is added after id when the table is created.

12. Auto_increment: set the primary key to grow itself.

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.