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

[text]

Main contents:

1. Common commands on the cmd command line

2. Data definition language (DDL)

Data manipulation language (DML)

Data query language (DRL)

Transaction Control language (TCL)

1. Common commands on the cmd command line:

When we log in using the MySQL 5.5 Command Line Client client, we can only log in to root users. If you create another user in the future, it will be troublesome, so instead of MySQL 5.5 Command Line Client as a client, we will use cmd directly.

Log in to the MySQL database:

Because you have configured the environment variables when you installed MySQL before, you can log in directly by entering the following command in cmd:

Mysql-u root-p

Then just enter the password.

In the code above,-u stands for username, the user name. -p stands for password, or password.

View the database:

Show databases

Use database: (this command can be followed without a semicolon)

Use database name

View the current database table:

Show tables

Note: the semicolon after the command indicates the end.

Note: in the above figure, entering show databases; first shows four libraries, the first three represent the libraries of the system, and the fourth test is the test library of the system. Then we use the test database, which displays: Database changed, indicating that the current database is active. You can then make other related commands to it. Here we enter show tables, and we can see that the table of the test database is empty.

So it's time to start building the table, so the SQL statement is involved.

SQL full name is: structured query language (Structured Query Language). Since it is a language, grammar is involved. Let's take a look at the common grammar.

2. Data definition language (DDL)

Data definition language: Data Definition Language. Such as CREATE, DROP,ALTER and other statements. It is important to note that commands in the database are not case-sensitive.

Create a database (CREATE DATABASE statement):

[example] create a new database and name it mydb:

CREATE DATABASE mydb

Note: if you want to delete the database, change "create" to "drop".

Create a table (CREATE TABLE statement):

[example] create a table named teacher:

(

All the above code is actually the same line.

Define the format of the field: name of the field + type of the field + attribute

Note: after each field is defined, it is separated by a comma. The last field does not have a comma.

In general, each table should have a primary key.

The effect of running the command is as follows:

View the table structure:

Desc teacher

The effect is as follows:

It should be noted that the primary key is the only field used to represent a record (the primary key value must be unique).

Delete the table (DROP TABLE statement):

DROP TABLE teacher

Note: the drop table statement deletes all records and table structures.

Modify the table structure (ALTER TABLE statement): (assume the table name is test)

Alter table test add column job varchar (10);-- add table columns

Alter table test rename test1;-- modify the table name

Alter table test drop column name;-Delete table columns

Alter table test modify address char (10)-modify table column type (change type)

Alter table test change address address1 char (40)-- modify the table column type (rename and type to have the same effect as the following line)

Alter table test change column address address1 varchar (30)-change the name of the table column (rename and type)

Example: change the primary key to auto-grow: alter table tab_teacher change id id int auto_increment

Data manipulation language (DML):

Data manipulation language: Data Manipulation Language. Such as: INSERT (add), UPDATE (change), DELETE (delete) statements

Add data (INSERT INTO … Statement): (that is, add the record of the table)

INSERT INTO table name (field 1, field 2, field 3) values (value, value)

For example:

Insert into tab_teacher (name,gender,age,createDate) values ('smyh','b',22,now ())

Note: the now () function above can call up the time of the current system.

Then query all records of the table with the following command:

Select * from tab_teacher

Where the wildcard "*" indicates all fields, that is, the records of all fields are looked up from the tab_teacher table.

The display effect is as follows:

Modify the data (UPDATE … SET statement):

UPDATE table name SET field 1 name = value, field 2 name = value, field 3 name = value where field name = value

Note: the following section of the where indicates the conditions for modification. When modifying, modify it in the order of field 1, field 2, and field 3.

Delete data: (DELETE FROM … Statement)

Delete all records:

DELETE FROM table name

Delete a record with an ID of 1:

DELETE FROM table name where id=1

4. Data query language (DRL):

Data query language (Data Retrieval Language-DRL): SELECT statement.

In the actual development, the data query language is the most frequently used. Let's take the following table as an example:

Note: in actual development, we should avoid using the wildcard "*" (the wildcard represents all fields), because the system has to parse out all the field names and restore them to real names before operating. If you record a lot of content in the database, it will affect the efficiency.

Inquire about all teachers' information:

Select * from tab_teacher

Or: (recommended)

Select id,name,gender,age,job,createDate from tab_teacher

Allows you to specify DUAL as a false table name when no table is referenced:

DUAL is a virtual table. In other words, there is no such data in the table, but if you insist on using the SQL statement, the system will use this virtual table to satisfy you. The results of examples are as follows:

Query the information of teachers with ID 2:

Select * from tab_teacher where id=2

Query the information of teachers whose careers are empty:

Select * from tab_teacher where job is null

Notice that in the code above, the field is empty and is indicated by "job is null" instead of "job=null".

Query the name and gender of the teacher whose ID is 2:

Select name,gender from tab_teacher where id=2

Check for information about teachers whose gender is female and on a specified date:

Select $from tab_teacher where gender='w' and ceateDate='2014-10-14'

Note: and use "and" or "or".

Inquire about teachers whose gender is male or whose ID is less than 2:

Select * from tab_teacher where gender='m' or id

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.