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

This article mainly gives you a brief introduction to the basic process of operating the Mysql database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. Here, we will not dabble in, go straight to the topic, and hope to bring you some practical help.

1. MySql database table operation

Create database: create database database name

Modify database encoding format: alter database database name character set character set name to be modified

Query database: show databases; to view all database names

Select database: use database name

2. Operation table

Create table: create table table name (

Column 2 data type 2.

);

Data type of common column: int double char varchar (byte length) date:yyyy-MM-dd time: hh:mm:ss datetime: yyyy-MM-dd hh:mm:ss

Delete table: drop table table name

Modify the table:

Add column: alter table table name add column name data type

Modify column data type: alter table table name modify column name data type

Modify column name: alter table table name change old table name new table name data type

Delete column: alter table table name drop column name

Modify the table name: alter table old table name rename to new table name

Query table:

Desc table name; query the table structure of the table

3. Operate the data in the table

Add data:

Insert into table name (column 1, column 2, etc.) Values (value 1, value 2pm.)

Insert into table name values (give the corresponding values of all columns in order)

Delete data:

Delete from table name;-- deleting all cannot delete self-growing constraints and can be rolled back, while truncate table cannot be rolled back.

Truncate table table name;-- Delete all reset self-growing constraints. Only all table data can be deleted, not a single data.

Modify the data:

Update table name set column name 1 = value 1, column name 2 = value 2.

Basic query data:

Select column name 1, column name 2. From table name; view required columns

Select column name 1 as' alias 1, column name 2 as' alias 2 as. From table name; view the desired column and alias the column

Select (column name 1 + column 2) as' alias' from table name

-- when juxtaposing with, the fields of the two columns should be of the same type; if null participates in the operation, the result is null;ifnull field name, which will replace null with 0.

4. Table data query

=,! =, (not equal), =

IN (set)

Is null is empty

Is not null is not empty

And and

Or or

Not is not

Wildcard character

_ match a single arbitrary character

% match any character

Select * from table name where column name like 'wildcard combination'

3) remove duplicate records distinct

Select distinct column name from table name

4) sort order by ASC default ascending sort DESC descending sort

Select * from table name order by column name asc

Select * from table name order by column name 1 desc, column name 2 desc

5) aggregate function-vertical operation

Count (): select count (* or column names without null values) from table name

Max (): select max (column name) as' maximum 'from table name

Min (): select min (column name) as' minimum 'from table name

Sum (): select sum (column name) as' and 'from table name

Avg (): select avg (column name) as' average 'from table name

6) grouping query-it is meaningful to use the found data in conjunction with the aggregate function

Select column name 1 aggregate function from table name where qualification group by column name 1; grouped by column name 1

Select column name 1 aggregate function from table name group by column name 1 having qualification

Where: if the conditions are limited before grouping, if the conditions are not met, they will not participate in the grouping.

Having: filter the result set after grouping

7) pagination query

Select * the record index starting with the from table name limit, the number of entries displayed per page

Initial record index = (page number-1) * number of entries displayed per page

* oracle:rownum paging dialect

* sqlserver:top paging dialect

5. Constraint

1) Primary key constraint (primary key)

The decorated field is unique and not empty; a table can have only one primary key, which can contain multiple fields

Format 1:

Create table table name (

Field name Field Type primary key

);

Format 2:

Create table table name (

Primary key (field name)

);

Format 3:

Alter table table name add primary key (field name 1, field name 2.)

2) unique constraint (unique)-the modified field is unique and has no effect on null

3) non-null constraint (not null)-the decorated field is not empty

1) self-growing constraint auto_increment

1. The modified field type supports self-increment. General int

two。 The decorated field must be a key, usually primary key.

2) add a foreign key constraint on one side of the multi-table

Alter table exterior name add foreign key (foreign key field name) references primary table name (primary key field name)

6. Multi-table query

1) join table an and table b within

The connection conditions of select a. From a [inner] join b on ab

Format 2: implicit internal connection

The connection conditions of select a. From. B. * a.

2) external connection

Format 1: left outer connection

Select a. From a left [outer] join b on connection condition.

First show all the data of table (a) on the left of join, then associate the table (b) on the right of join according to the condition, and show it if it meets the condition, but it does not conform to the null value.

Format 2: right outer connection

Select a. From b right [outer] join an on connection condition.

First show all the data of table (a) on the right side of jion, associate the table (b) on the left side of join according to the condition, and show it if it meets the condition, but not with null value.

3) subquery-one query depends on another

For example: SELECT emp_fname,emp_lname FROM employee WHERE emp_no = (SELECT emp_no FROM works_on WHERE enter_date = '1998-01-04 00lv 0000')

Select column name from table name where column name = (select column name from table name where qualification)

2. Storage process-sql statements with logic

1. Characteristics

1) the execution efficiency is very fast, and the storage process is performed on the server side of the database.

2) the portability is very poor, and the storage process of different databases cannot be transplanted.

2. Grammar

Create a storage process

Delimiter $

Create procedure test (in input parameter name data type, out returns value name data type)

Begin

-- multiple sql statements to be executed

If input parameter name = value 1 then

Set returns value name = 'return value 1'

Set returns value name = 'return value 2'

Else

Set returns value name = 'return value 3'

End if

End $

Call the stored procedure:

Call tset (enter parameter value, @ return value name)

Select @ returns the value name

3. Variables in MySQL

1) Global variable (built-in variable): scope entire link

-- View all global variables; show variables

-- View a global variable: select @ @ variable name

-- modify global variable: set

Set @ @ character_set_client=gbk; sets the encoding received by the server port

Set @ @ character_set_results=utf8; sets the encoding of the display

2) session variable: it only exists in the current connection. If the link is broken this time, the session variable will disappear.

-- define session variable: set @ variable = value

-- View session variable: select @ variable

3) Local variables: for example, the scope of variables in stored procedures is between begin and end

4. Logical sql statements between begin end

1) conditional judgment

If.. then.. set..

Else set..

End if

2) cycle

Set..

Set variable name = variable name + 1;-- notice that there is no such thing as iTunes +.

End whlie

5. Delete the storage process

Drop procedure stored procedure name

Trigger-when one operation occurs, another behavior is triggered.

Create trigger

Create trigger has a trigger name after add / change / delete on the table name for each row to be added or deleted

The name of the table (field name) to be recorded by insert into values ('added a record')

/ / after insert on add

/ / after update on modification

/ / after delete on deletion

Example:

Delimiter $

Create trigger banji after insert on user for each row

Begin

Insert into loger values (NULL,' you added a record', NULL)

End $

Insert into user values (NULL,' Zhang San')

1. System function

Any function has a return value, and the function is called through select. Because there is a return value, it can only be called with select

1) intercept string

Substring (original string, starting position, truncated length); subscript in mysql is calculated from 1

2) character byte length

Set @ username=' Hello World

Char_length ('@ username') is 4 characters long

Length ('@ username') is 12 bytes long

3) find the index of the string in which the character belongs, and no 0 is returned

Select instr (@ username,' good'); return 2

4) fill in the specified string according to the specified length

Lpad (the character to be populated by @ username,10,'') is filled to the left

For example: select lpad (@ username,10,' Welcome'); note that the 10 lengths count the four lengths of the original Hello World, that is to say, the welcome fills in 6.

Rpad right fill

For example: select insert (@ username,2,2,' '); replace two characters from the second character with

6) strcmp (string 1, string 2) compare the size of two strings according to dictionary order

2. Custom function

Create function function name (argument list optional) returns data type-specifies the data type to be returned

Begin

Function body

Return value: return return type (the type we specified)

End

2) use functions

Create function show () returns int

Return 100

-- call the function

Select show ()

-- View all functions show functions status

-- View the creation function show create function test

-- delete the function drop function test

3) example:

Delimiter $

Create function delById (uid int) returns int

Begin

Delete form user where id = uid

Return (select count (*) from user)

End $

Select deById (7)

1. Overview: a structured virtual table. The structural source of the virtual table is not defined by itself, but generated from the corresponding base table.

Views can better control permissions, such as hiding the names of some of my base tables.

2. Create a view

Create view View name as select statement

Create view my_v1 as select * from student

Create view my_v2 as select a. Field name, b. Field name from aforme b where a.id=b.id

3. View the view

Statements about query tables are available for views except for show create view my_v1

View execution: in fact, the essence is to execute encapsulated select statements

5. Modifying the view is actually a modification of the original table, alter view view name, as, new select statement

6. Operation of view data

View insert data:

(1) Multi-table view cannot insert data

(2) data can be inserted in a single table view (if the fields in the view do not have fields that cannot be empty in the base table and have no default values, the insertion is not successful)

(3) the view can insert data into the base table (the operation of the view affects the base table)

View delete data

(1): multi-table view cannot delete data

(2): a single table view can delete data and affect the base table.

View update data

Both single table view and multiple table view can update data

Update limit: with check option

For example: create view my_v1 as select * from student where age > 30 with check option

Indicates that the sources of the view data are all older than 30. When with check option decides to update through the view, it cannot get the

Students with data age > 30 will be changed to age

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.