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

First, operate on the database (DDL statement).

1. Create a database.

Create database [if not exists] Database name character set character encoding

Example: create a database with the name db_1 and the character encoding utf8

Create database if not exists db_1 character set utf8

two。 View the database.

2.1 View all databases: show databases

2.2 View how a database is created: show create database database name

Example:

Show create database db_1

3. Modify the database.

Alter database db_name [character set xxx]

4. Toggles the currently operated database.

The name of the database to which use will switch.

Example: use db_1

To see which database you are currently under:

Select database ()

Second, the data type in mysql.

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

Numeric type:

Date and time type:

String type:

CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They are also different in terms of maximum length and whether trailing spaces are retained. No case conversion occurs during storage or retrieval.

The BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings instead of character strings.

BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They just differ in the maximum length of the allowable value.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types with the same maximum length and storage requirements.

Third, the table operation in the database.

1. Create a table in the database.

Create table table name (fields and properties of each field, each separated by a comma)

Example:

Create table tb1 (id int primary key auto_increment, name char (20), gender bit default 1, birthday date,entry_date date,job char (20), salary double (4) unsigned, resume text)

!! The primary key property here is the primary key, so what is the primary key?

The primary key has two characteristics, that is, it is not empty and the value is unique.

The auto_increment attribute means self-incrementing and can be used when the primary key field is a numeric type. It allows the value to increase itself.

two。 View the structure and information of a table in the database.

2.1View the table structure of a table: desc tab_name

For example:

Desc tb1

2.2 View all the tables in the current database:

Show tables

2.3 View the sql statement used when creating the table:

Show create table table name

For example:

Show create table tb1\ G

3. Modify the table structure.

3.1 add a field to the table:

Format: alter table tab_name add [column] column name type [Integrity constraint] [first | after field name]

# where first and after are optional parameters (you don't have to use them. If you don't write them, the field added by default is at the last)

# first refers to the location added to the first field.

# after is added after a field.

Alter table user add addr varchar (20) not null unique first/after username

3.2 example of adding multiple fields:

Alter table users2

Add addr varchar (20)

Add age int first

Add birth varchar (20) after name

3.3 modify the type of a field in the table:

Alter table tab_name modify column name type [Integrity constraint] [first | after field name]

Example:

Alter table users2 modify age tinyint default 20

Alter table users2 modify age int after id

Modify the names of the fields in the table:

Alter table tab_name change [column] column name New column name Type [Integrity constraint] [first | after Field name]

Alter table users2 change age Age int default 28 first

3.4 Delete a field:

Alter table tab_name drop [column] column name

3.5 modify a table name:

Rename table table name to new table name

3.6 modify the table character set:

Alter table student character set utf8

3.7 Delete the entire table:

Drop table table name

3.8 add primary key constraints to the fields in the table:

Alter table tab_name add primary key (field name,...)

3.9 remove the primary key constraint in the field:

Delete the self-growing primary key first

Alter table tb1 change id id int (11); / / delete self-growth

Alter table tb1 drop primary key;// deletes the main building

3.10 add a unique index:

Format:

Alter table tab_name add unique [index | key] [Index name] (field name,...)

Example:

Alter table users add unique (name)

Alter table users add unique key user_name (name)

3.11 add a federated index:

Alter table users add unique index name_age (name,age); # show create table users

3.12 Delete a unique index:

Alter table tab_name drop {index | key} index_name

four。 Operate on the records in the table.

Insert inserts records in the table:

Format: insert [into] tab_name (field1,filed2,.) Values (value1,value2,.)

Example:

Insert into employee_new (id,name,birthday) values (1)

Insert multiple pieces of data at the same time:

Insert into employee_new values (4), (4), (4), (5), (5), (5), (5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 9, 5, 5, 9, 5, 5, 12)

Insert data as key-value pairs:

Format:

Insert [into] tab_name set field name = value

Example:

Insert into employee_new set id=130,name= "bitch jolin"

2. Update modify the records in the table:

Format:

Update tab_name set field1=value1,field2=value2,. [where statement]

Example:

Update employee_new set birthday= "1995-09-05" WHERE id=1

The # PDATE syntax can update columns in existing table rows with new values.

The # SET clause indicates which columns to modify and which values to give.

The # WHERE clause specifies which rows should be updated. If there is no WHERE clause, update all rows.

Another example of usage:

-increase suhaozhi's salary by 130 yuan on the original basis.

Update employee_new set salary=salary+130 where name='suhaozhi'

3.delete deletes a record from the table:

Format:

Delete from tab_name [where....]

# delete statement deletes the data in the entire table if it does not follow the where statement

# delete can only be used to delete a row of records

The # delete statement can only delete the contents of the table, not the table itself. If you want to delete the table, use drop.

# TRUNCATE TABLE can also delete all the data in the table. Words and sentences first destroy the table, and then create a new table. Data deleted in this way cannot be recovered in a transaction.

Example:

Delete the record in the tb1 table where the name field is suhaozhi.

Delete from tb1 where name='suhaozhi'

Delete all records in the table:

Delete from tb1

Use truncate to delete records in the table:

Truncate table tb1

# TRUNCATE TABLE can also delete all the data in the table. Words and sentences first destroy the table, and then create a new table. Data deleted in this way cannot be recovered in a transaction.

4.select query statement:

The basic format of the select query statement:

SELECT * | field1,filed2... FROM tab_name

WHERE condition

GROUP BY field

HAVING screening

ORDER BY field

Limit the number of LIMIT entries

The following is an example of an select query statement:

Create table tb2 (id int primary key auto_increment, name varchar (20), yuwen DOUBLE, shuxue DOUBLE, yingyu double)

Insert into tb2 values (1, "aaa", 98 insert into tb2 values 99), (2, "bbb", 35 record98 people 66), (3, "ccc", 59 miners 59 people 62), (4, "ddd", 88 ddd 88), (5, "eee", 100min65 people 88)

Now there is a score sheet with five records:

Select * from tb2

+-- +

| | id | name | yuwen | shuxue | yingyu | |

+-- +

| | 1 | aaa | 98 | 98 | 99 |

| | 2 | bbb | 35 | 98 | 66 | |

| | 3 | ccc | 59 | 59 | 62 | |

| | 4 | ddd | 88 | 89 | 87 | |

| | 5 | eee | 100 | 65 | 88 |

+-- +

5 rows in set (0.03 sec)

View the entire table:

Select * from tb2

Check the Chinese and math scores for which the name field is ddd:

Select yuwen,shuxue from tb2 where name= "ddd"

List the scores of all students in Chinese and English:

Select yuwen,yingyu from tb2

# where from specifies which table to filter from, * indicates to find all columns, or you can specify a column

# indicates that the column to be found is specified, and distinct is used to weed out duplicate rows.

Select and mathematical evaluation expressions and as aliases:

Select name,yuwen+shuxue+yingyu from tb2; # shows the sum of Chinese, mathematics and English for each student.

+-+ +

| | name | yuwen+shuxue+yingyu |

+-+ +

| | aaa | 295 |

| | bbb | 199 | |

| | ccc | 180 | |

| | ddd | 264 | |

| | eee | 253 |

+-+ +

After using field aliases:

Select name as' name', yuwen+shuxue+yingyu as' total grade 'from tb2

+-+ +

| | name | Total score | |

+-+ +

| | aaa | 295 |

| | bbb | 199 | |

| | ccc | 180 | |

| | ddd | 264 | |

| | eee | 253 |

+-+ +

Use the where statement to filter the query:

Example 1: find out the students whose English scores are greater than 90:

Select * from tb2 where yingyu > 90

Example 2: find out the names and scores of all students whose total score is greater than 200:

Select name as's name, yuwen+shuxue+yingyu as's total grade from tb2 where yuwen+shuxue+yingyu > 200,

+-+ +

| | name | Total score | |

+-+ +

| | aaa | 295 |

| | ddd | 264 | |

| | eee | 253 |

+-+ +

The comparison operator that can be used in the where statement complements:

>

< >

= 150

+-+ +

| | class | sum (price) |

+-+ +

| | Appliance | 540.00 |

+-+ +

#! Here is a special emphasis on one point! Although both having and where can filter query results! But there's a difference!

Where can only be used for filtering before grouping! And having can be used for filtering after grouping! This is because having can use aggregate functions! Not in where! Keep that in mind!

Common aggregate functions supplement:

In general, aggregate functions are used in conjunction with grouping queries.

# just look up the required content and package it with the aggregate function.

Count counts the number of rows.

Example 1: count how many rows there are in this table

Select count (*) from test_menu

Example 2: count the number of records with math scores greater than 70.

Select count (*) from tb2 where shuxue > 70

Example 3: how many records have a total statistical score of more than 200?

Select count (*) from tb2 where yuwen+shuxue+yingyu > 200

2.sum counts the sum of rows that meet the condition.

Example 1: count the total score of Chinese in this class.

Select sum (yuwen) as' Chinese Total score 'from tb2

+-+

| | Total language score |

+-+

| | 380 |

+-+

Example 2: count the total scores of each subject in this class.

Select sum (yuwen), sum (shuxue), sum (yingyu) from tb2

Example 3: seek the average of the Chinese scores of the class.

Select sum (yuwen) / count (*) from tb2

The average value was calculated by 3.avg.

Example 1: seek the average of the Chinese scores of the class.

Select avg (yuwen) from tb2

Example 2: seek the average score of the sum of the classes.

Select avg (yuwen+shuxue+yingyu) from tb2

4.max&min calculates the highest or lowest value.

Example:

Select max (yuwen+shuxue+yingyu) from tb2

Select min (yuwen+shuxue+yingyu) from tb2

5.ifnull converts a null value to the specified value.

Attention! Null and all numeric calculations are null, so you need to use ifnull to convert null to 0 numbers!

Example: ifnull (yuwen,0)

6.limit specifies the number of records found.

Select * from tb2 limit 1; # shows only the first line of the result.

Select * from tb2 limit 2 5; # skips the first two lines and displays the next five lines.

7.REGEXP uses regular expressions for queries.

Select * from tb2 where name regexp'^ a'

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.