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

One. First acquaintance with SQL language

SQL (Structured Quqry Language): structured query language, mainly used to access data, query data, update data and manage relational database systems

There are three types of SQL languages, which are:

1. DDL statement Database definition language (Data Definition Language) is generally used to create databases, tables, views, indexes, stored procedures, etc.

2. DML statement Database manipulation language (Data Manipulation Language): insert data INSERT, delete data DELETE, update data UPDATE, query data SELECT

3. DCL statement database control language (Data Control Language): for example, control user's access rights GRANT (grant permissions), REVOKE (recall permissions)

Q: what is the default save path for mysql library files and table files? What types of files are stored on the hard disk?

Answer: save path / var/lib/mysql

Library file type: stored as a directory in the / var/lib/mysql directory

Table file: in the directory where the font is stored as a normal file

Two. Basic operation of library

The operation of database table can be divided into four operations: insert data INSERT, delete data DELETE, update data UPDATE, query data SELECT.

1. Create a database

Syntax: create database database name

Naming rules: case-sensitive, unique, cannot be named with keywords such as create select, and numbers cannot be used alone

Example: create a database named db1

2. View and use the database

View the database: show databases

Use database: use database name

Elect database (); # returns the name of the current database

From the figure above, you can see that the listed database has the newly created db1 database. Using the database, you can see the hint: the Database changed database has changed.

3. Delete database

Syntax: Drop database database name

Example: delete the created db1 library and check whether the db1 library exists

Three. Operation of the table

1. Create a tabl

Syntax: create table table name (field name type (range of values)) [storage engine character set]

Note: field names cannot be the same in the same table, and fields and types must be defined to create a table.

Example: create a table named tb1 in the test library

Desc tb1; # View the structure of tb1 table

2. Insert data into a table

Syntax: insert into table name [field name] values (data to be inserted) / / the inserted data should match the fields of the table

Example: insert 3 pieces of data into the tb1 table

(insert a value into the specified field-for example: insert only the number and name insert into tb1 (id, name) values (8, 'qwer'))

3. View the data in the table

Syntax: select [fields in table] from table name where [query criteria]

Example: view the tb1 table (* indicates query all)

4. Table modification operation (alter)

Add the specified column field at the end of the table: alter table table name add field name data type

Add the specified column field after the first column or field name A column in the table: alter table table name add field name data type [first | after field name A]

Delete a column field from the table: alter table table name drop field name

Modify the properties of a column [i.e. field name and data type]:

Modify the data type of a column: alter table table name modify field name new data type [first | after field A]

Modify the field name and data type of a column: alter table table name change old field name new field name new data type [first | after field name]

Example: add a sex field after the name column, and specify the type as enum ('massively recoveryingf'). After the addition, check the structure of the table / / enum (' massively recoveryingf') means that the value inserted in the sex field can only be m or f, not these two letters, and an error will be reported when inserting data.

5. Delete operation of table

Format: drop table [library name]. Table name

Example: delete the tb1 table in the test library

Delete the tb1 table and look at it, you can see that the tb1 table has been deleted (if you are no longer in the test data, you will use drop table test.tb1 to delete it, remember to see what the current path to the database is)

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.