Skills of using MySQL SQL 04/10 Update SLTechnology News&Howtos

Skills of using MySQL SQL

2025-04-10 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Note:

SQL language: structured query language, which is the standard of relational database query language. Although different databases have their own private extensions, key words are supported: (select, update, delete, insert, where)

SQL statement classification: things like Oracle and MSSQL are universal.

DDL: data definition language (create, alter, drop, rename)

DML: database maintenance language (select, insert, update, delete)

DCL: database control language, permissions (Grant, revoke)

TCL: transaction control language (commt, sarepqint): a new type of statement

I. addition, deletion, modification and query of the database

According to this table structure, you can do the following:

Mysql > select * from user

+-- +

+-- +

Basic operation

1. Create a database

Create database name

two。 Delete database

Drop database name

3. Create a user table

Create table user (id int,name varchar (30), pass varchar (30))

4. Insert data

Insert into user (id,name,pass) values ("1", "zhangsan", "123")

5. Delete a table

Drop table user

6. View table fields

Desc table user

7. View table data

Select * from user

8. Modify table name

Rename table user to tab1

9. Update data

Update user set pass= "newpass" where pass=123; # updates the password to newpass

Update user set id=10,name='lisi' where id=1; # updates the name recorded by id to id10 and name to lisi

10. Rename field name

Alter table user change pass newname varchar (30)

11. Add a field to the table

Alter table user add age int

twelve。 Modify field

Alter table user modify age int no null default 20; # when the age input is blank, the default is 20 years old

13. Delete a field from the table

Alter table user drop age

14. Delete a record

Delete from user where id=1

15. Find a record

Select id,name,pass from user where id=1

16. Delete ID record

Delete from user where id > = 3 id select id,name,pass,concat (id,'_',name) idname from user

+-- +

+-- +

| | 1 | zhangsan | 123 | 1_zhangsan |

| | 2 | lisi | 123 | 2_lisi |

| | 3 | zhaowu | 123 | 3_zhaowu |

+-- +

Random sorting of 7.rand functions, such as random selection of the top three

Select * from user order by rand () limit 3

8.count statistics, such as the number of id records

Select count (*) count from user

# count is the field name after custom query results are displayed. * it is the optimized query method of mysql, which is more efficient than writing id directly.

Select count (id) from user where name='zhangsan'; # counts the number of records in zhangsan

9.sum summation, such as counting the amount of money spent.

Select sum (id) from user where name='lisi'; # adds up all the id numbers

10.avg average, if class average is required.

Select avg (id) from user

Maximum value of 11.max, such as getting the highest score

Select max (id) from user

12.min minimum

Select min (id) from user

13.group by packet aggregation

Select name,count (id) from user group by name order by desc

# aggregate using count, grouping based on name, and then sorting under order by (grouping is generally used in rankings)

Select name,count (id) count from user group by name having count > = 3

# the top three queries. Having filters the grouped results. You cannot use where,count to represent the field names displayed after the query results.

14. Normal multi-table query (premise: two tables must be related)

First create two related tables:

Create table user (id int unsigned auto_increment primary key,name varchar (30), age int)

Create table post (id int unsigned auto_increment primary key,uid int,title varchar, content text)

Result 1: query all records sent by the user

Select user.name,post.title,post.content from user,post where user.id=post.uid

Result 2: count the number of records per user

Select user.name,post.title,post.content count (id) from user,post where user.id=post.uid group by user.name

15. Join table query (query results)

Mysql > select * from tb1

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | lisi |

+-+ +

Mysql > select * from tb2

+-+ +

| | id | score |

+-+ +

| | 1 | 80 |

| | 2 | 81 |

+-+ +

Mysql > select tb1.name,tb2.score from tb1,tb2 where tb1.id = tb2.id

+-+ +

| | name | score |

+-+ +

| | zhangsan | 80 | |

| | lisi | 81 | |

+-+ +

Table field type

1. Numerical value

Int (size): integer type, which can only store integer numbers and cannot be empty. Null is allowed by default, and not null can also be set.

Float: floating point type, which can be written to integers or floating point numbers

1.1 Field Properties

Unsigned: unsigned, all integers

Zerofill: it has nothing to do with the length. If it is less than 3 digits, it will be 0 in front of it. It cannot be seen by default.

Null and not null: allow input null and disallow input

Default: if null is not allowed, the default value is used when the input is blank.

Auto_increment: general self-increasing ID

1.2 exampl

For example, set id to self-incrementing:

Create table user (id int unsigned auto_increment primary key,name varchar (30), pass varchar (30))

Primary key: if you have auto_increment, you must set it as the primary key index to provide query speed. If you insert it, you don't have to write the id column. If you write it, you will continue to increase it.

For example, to create a table, when the input gender is empty, the default is male:

Create table user2 (id int unsigned auto_increment primary key,name varchar (30), sex varchar (5) not null default "nan")

View field information:

Mysql > desc user2

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

| | sex | varchar (5) | NO | | nan |

+-+ +

3 rows in set (0.00 sec)

two。 String

Char (size): occupies the bytes of size, but the query speed is fast, supporting a maximum of 255characters

Varchar (size): take up as much as you want, leave some space, and support up to 65535 characters.

Text: supports 65535 bytes

Longtext: supports 4.2 billion bytes

3. Date type (some use int type to store timestamps)

The following are the official bytes, but there will be discrepancies.

Date: year, month, day, occupies 3 bytes

Time: hour, minute, second, occupies 3 bytes

Datetime: year, month, day, hour, second, occupies 8 bytes

Year: year, occupies 1 byte

III. User management

1. Set Mysql password

Method 1: set password=password ('newpass')

Method 2: update user set password=password ('newpass') where user='root'

Method 3: grant all on *. * to 'root'@'localhost' identified by' newpass'

Refresh again: flush privileges

two。 Create users and authorize

Grant all privileges on *. * to 'user'@'localhost' identified by' pass'

*. *: corresponds to the permissions of the database / table

Localhost: only local access is allowed, or you can set% to all access

3. Revoke user rights

Revoke all privileges on *. * from 'root'@'localhost' identified by' pass'

IV. Database character set

1. View basic server information\ s

123456789101112131415161718192021mysql >\ s-mysql Ver 14.14 Distrib 5.5.34, for Linux (x86: 64) using EditLine wrapperConnection id: 141700Current database: labCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile:''Using delimiter:; Server version: 5.5.34-log Source distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: latin1Conn. Characterset: latin1UNIX socket: / tmp/mysql.sockUptime: 47 days 4 hours 54 min 1 secThreads: 4 Questions: 189434783 Slow queries: 1 Opens: 283512 Flush tables: 23 Open tables: 46 Queries per second avg: 46.447-

You can see that the four default character sets are latin1, modify the four default character sets, client and connection if the two are the same.

Set character_set_server=utf8

Set character_set_database=utf8

Set character_set_client=utf8

Set character_set_connection=utf8

You can also modify the my.cnf default character set

[mysqld]

Character_set_server=utf8

[mysql]

Default-character-set=utf8

Restart mysql!

two。 View the character set used when creating the database

Show create database mydb

Create a table field index

1. Primary key index

1.1 create a primary key index, which is generally set when you create a table

Alter table user add primary key (id)

1.2 Delete a primary key index

Alter table user drop primary key

two。 General index

2.1 create a normal index

Alter table user add index index_name (name)

2.2 Delete a normal index

Alter table user drop index index_name

2.3 View primary key index and normal index

Show index from user

Show keys from user

Note: changing the field name does not affect the index.

3. View the indexes created in the table

Mysql > desc user2

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

The PRI representation in the above key is the primary key index, and the MUL representation is the normal index.

4. View index information

Mysql > show index from user2

+ -+

+ -+

| | user2 | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE |

| | user2 | 1 | index_name | 1 | name | A | 1 | NULL | NULL | YES | BTREE |

+ -+

2 rows in set (0.00 sec)

You can see the user table and do the PRIMARY primary key index and the normal index of the index_name name.

5. Test whether to use an index

Mysql > desc select id,name from user2 where name='zhangsan'

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Using the desc detection statement, you can see that a record of Zhang San is queried, using the name of the general index index_name, and the record is found after searching one line (rows).

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.