Database operation Mysql

Database operation Mysql 1. create a user:

> Create user name identified by 'ssapdrow ';

2. change the password:

> Set password for name = PASSWORD ('fdddfd ');

3. permission management

> Show grants for name; // view the name user permission

> Grant select on db_name. * TO name; // GRANT all permissions TO the name user db_name database

> Revoke select on db_name. * TO name; // GRANT the permission TO be removed;

I. database operations:

1. view the database:

> Show databases;

2. create a database:

> Create database db_name; // db_name indicates the DATABASE name.

3. database usage:

> USE db_name;

4. delete a database:

> Drop database db_name;

II. create a table:

1. create a table:

> Create table table_name (

> Id tinyint unsigned not null AUTO_INCREMENT, // id value, UNSIGNED, non-NULL, incrementing -- unique, can be used as the primary key.

> Name VARCHAR (60) NOT NULL

> Score tinyint unsigned not null default 0, // you can specify the DEFAULT column value.

> Primary key (id)

>) ENGINE = InnoDB // specifies the storage ENGINE for tables. generally, InnoDB and MyISAM are used. InnoDB is reliable and supports transactions. MyISAM does not support full-text retrieval efficiently.

> DEFAULT charset = utf8; // sets the DEFAULT encoding to prevent Chinese garbled characters in the database.

IF you have conditions to CREATE a data TABLE, you can also use> create table if not exists tb_name (........

2. copy a table:

> Create table tb_name2 SELECT * FROM tb_name;

Or partial replication:

> Create table tb_name2 SELECT id, name FROM tb_name;

3. create a temporary table:

> Create temporary table tb_name (the same as creating a common TABLE );

4. View available tables in the database:

> Show tables;

5. view the table structure:

> DESCRIBE tb_name;

You can also use:

> Show columns in tb_name; // from is optional.

6. delete a table:

> DROP [TEMPORARY] TABLE [if exists] tb_name [, tb_name2 ......];

Instance:

> Drop table if exists tb_name;

7. rename a table:

> Rename table name_old TO name_new;

You can also use:

> Alter table name_old RENAME name_new;

3. modify a table:

1. change the table structure:

> Alter table tb_name ADD [CHANGE, RENAME, DROP]... content to be changed...

Instance:

> Alter table tb_name add column address varchar (80) not null;

> Alter table tb_name DROP address;

> Alter table tb_name CHANGE score SMALLINT (4) not null;

4. Insert data:

1. Insert data:

> Insert into tb_name (id, name, score) VALUES (NULL, 'zhangsan', 140), (NULL, 'zhangsi', 178), (NULL, 'zhangwu ', 134 );

Insert multiple data entries with commas (,) to the backend and directly write the inserted data. the primary key id is an auto-incrementing column and does not need to be written.

2. Insert the retrieved data:

> Insert into tb_name (name, score) SELECT name, score FROM tb_name2;

5. update data:

1. specify to update data:

> UPDATE tb_name SET score = 189 WHERE id = 2;

> UPDATE tablename SET columnName = NewValue [WHERE condition]

6. delete data:

1. delete data:

> Delete from tb_name WHERE id = 3;

VII. condition control:

1. WHERE statement:

> SELECT * FROM tb_name WHERE id = 3;

2. HAVING statement:

> SELECT * FROM tb_name group by score HAVING count (*)> 2

3. related condition controllers:

=,>, <, <>, IN (1, 2, 3 ......), BETWEEN a AND B, NOT

AND, OR

In Linke () usage, % matches any character and _ matches a character (can be Chinese characters)

Is null value detection

8. MySQL regular expression:

1. Mysql supports REGEXP regular expressions:

> SELECT * FROM tb_name WHERE name REGEXP '^ [A-D]' // find the name starting with the A-D

2. escape special characters.

9. MySQL functions:

1. string link -- CONCAT ()

> Select concat (name, '=>', score) FROM tb_name

2. mathematical functions:

AVG, SUM, MAX, MIN, COUNT;

3. text processing functions:

TRIM, LOCATE, UPPER, LOWER, SUBSTRING

4. operators:

+ ,-,*,\

5. time functions:

DATE (), CURTIME (), DAY (), YEAR (), NOW ().....

10. group query:

1. grouping queries can be grouped by specified columns:

> Select count (*) FROM tb_name group by score having count (*)> 1;

2. Conditional use of Having;

3. order by sorting:

Order by desc | ASC => sort data in descending and ascending ORDER

11. UNION rule -- two statements can be executed (repeated rows can be removed)

12. full-text search-MATCH and AGAINST

1. select match (note_text) AGAINST ('picaso') FROM tb_name;

2. the InnoDB engine does not support full-text retrieval. MyISAM is fine;

XIII. View

1. create a view

> Create view name as select * FROM tb_name WHERE ~~ Order ~~;

2. Special functions of a view:

A. simplify the join between tables (write the join in select );

B. reformat the output data (TRIM, CONCAT, and other functions );

C. filter unwanted data (select part)

D. use the view to calculate the field value, such as the sum.

14. use stored procedures:

In my personal understanding, stored procedures are a user-defined function with local variable parameters that can be passed in and can return values. However, this syntax is not enough ~~~

1. create a stored procedure:

> Create procedure pro (

> IN num INT, OUT total INT)

> BEGIN

> Select sum (score) INTO total FROM tb_name WHERE id = num;

> END;

* ** Here, the IN (pass a value to the stored procedure), OUT (transfer a value from the stored procedure), INOUT (pass IN and OUT the stored procedure), and INTO (save the variable)

2. call the stored procedure:

> CALL pro (13, @ total) // The stored procedure contains two variables: IN and OUT, which also needs to be written. if you do not write, an error will occur.

> SELECT @ total // you can see the result here;

3. other operations in the stored procedure:

> Show procedure status; // displays the current stored PROCEDURE

> Drop procedure pro; // delete a specified stored PROCEDURE

15. Use a cursor:

I don't understand this very well. I hope you can give me some advice ~~~

1. cursor operations

> Create procedure pro ()

> BEGIN

> DECLARE ordername CURSOR

> SELECT order_num FROM orders;

> END;

　　　　

> OPEN ordername; // OPEN the cursor

> CLOSE ordername; // CLOSE the cursor

16. trigger:

A trigger triggers a specified action in a trigger;

1. the statements supporting triggers include DELETE, INSERT, and UPDATE. None of the other statements are supported.

2. create a trigger:

> Create trigger trig after insert on orders for each row select new. orser_name;

> INSERT statement, triggering statement, returns a value

3. delete a trigger

> Drop trigger trig;

17. syntax arrangement:

1. ALTER TABLE)

Alter table table_name

(ADD column datatype [NULL | not null] [CONSTRAINTS]

CHANGE column datatype COLUMNS [NULL | not null] [CONSTRAINTS]

DROP column,

....

)

2. COMMIT (transaction processing)

> COMMIT;

3. create index (CREATE an INDEX on one or more columns)

Create index index_name ON tb_name (column [ASC | DESC],...);

4. create procedure (CREATE a stored PROCEDURE)

Create procedure pro ([parameters])

BEGIN

........

END

5. CREATE TABLE)

Create table tb_name (

Column_name datetype [NULL | not null] [condtraints],

Column_name datetype [NULL | not null] [condtraints],

.......

Primary key (column_name)

) ENGINE = [InnoDB | MyiSAM] default charset = utf8 AUTO_INCREMENT = 1;

6. CREATE USER)

Create user user_name [@ hostname] [identified by [PASSWORD] 'pass _ word'];

7. create view (create view on one or more tables)

CREATE [or replace] VIEW view_name as select ......

8. DELETE (DELETE one or more rows from the table)

Delete from table_name [WHERE...]

9. DROP (permanently delete databases and objects, such as views and indexes)

Drop datebase | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW name

10. INSERT (add rows to the table)

Insert into tb_name [(columns,...)] VALUES (value1 ,............);

Insert with SELECT value:

Insert into tb_name [(columns,...)]

SELECT columns,... FROM tb_name [WHERE...];

11. ROLLBACK (Undo a transaction processing block)

ROLLBACK [TO savapointname];

12. SAVEPOINT (set retention point for ROLLBACK)

SAVEPOINT sp1;

13. SELECT (retrieve data and display information)

SELECT column_name,... FROM tb_name [WHERE] [UNION] [rroup by] [HAVING] [order by]

14. start transaction (START of a new TRANSACTION processing block)

START TRANSACTION

15. UPDATE (UPDATE one or more rows in a table)

UPDATE tb_name SET column = value,... [where]

[Note] MySQL database is a very lightweight database management system, compared to large database management systems such as Oracle, MS-SQL, MySQL has more light, flexible, fast development characteristics, it is more suitable for the storage and architecture of small and medium-sized data. This is why MySQL can be used by tens of thousands of websites.

I did not learn very well in the past. I have learned a little about it today. I feel that open-source and lightweight databases also have a lot of content. from version 5 and later, it gradually supports advanced applications such as cursors, triggers, transactions, and stored procedures, which also adds an important weight to the ease of use of MySQL and the development of enterprise services.

There is very little Foundation for the database, but the performance optimization of the database is the most important, so a lot of optimization will be helpful.