Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the common commands on the basis of MySQL", the content is simple and clear, and I hope it can help you solve your doubts. Let the editor lead you to study and learn what are the common commands in MySQL.

First, the connection MySQL format: mysql-h host address-u user name-p user password 1, connect to the MySQ on the machine: first open the cmd window, enter mysql-uroot-p, and then enter the space into the MySQL console. The prompt for MySQL is: mysql >. Mysql-uroot-p / * if you just installed MySQL,root, you don't have a password * /

2. Connect to the MySQL on the remote host: suppose the IP of the remote host is 192.168.206.100, the user name is root, and the password is 12345678. Mysql > mysql-h292.168.206.100-uroot-p12345678; / * u and root can not add spaces * / 3, exit MySQL command: exit or quit (enter): mysql > exit/quit; II, MySQL-- modify password format: mysqladmin-u username-p old password password new password 1, when root password is empty, set root password: mysql > mysqladmin-uroot newpassword -- because root does not have a password at the beginning, the old password can be omitted. 2. Use the sqladmin command to change the password: mysql > mysqladmin-uroot-p123456 password 12345678

3. Use the set passsword command to change the password:

Mysql > set password for username @ localhost = password ('new password')

4. If you forget the root password:

① shuts down the running MySQL service.

② opens the DOS window and changes to the mysql\ bin directory.

Enter mysqld for ③-- enter enter skip-grant-tables. Skip-grant-tables means to skip authorization table authentication when starting the MySQL service.

④ opens another DOS window (because the DOS window just now cannot be moved), type mysql to enter, and if successful, the MySQL prompt will appear.

⑤ connection permissions database: use mysql;.

⑥ change password: update user set password=password ("new password") where user= "root"; (don't forget to add the semicolon at the end).

⑦ refresh permissions (required): flush privileges;.

⑧ exits mysql quit;. Mysqld-- skip-grant-tables mysql > use mysql;-- Connect permissions database mysql > update user set password=password ("new password") where user= "root";-- change password mysql > flush privileges;-- Refresh permissions mysql > quit;-- exit mysql III. MySQL-- commands

1. Create a database (Create)

Mysql > create database db_name;-- create database mysql > show databases;-- Show all databases mysql > drop database db_name;-- Delete database mysql > use db_name;-- Select database mysql > create table tb_name (field name varchar (20), field name char (1));-- create data table template mysql > show tables -- display data table mysql > desc tb_name -- display table structure mysql > drop table tb_name;-- Delete table

For example:

/ * create a student table * / create table Student (Sno char (10) primary key, Sname char (20) unique, Ssex char (2), Sage smallint, Sdept char (20))

2. Insert data (Insert)

The insert into statement is used to insert a new row into the table: / * the first form does not need to specify the column name to insert data, just provide the inserted value: * / mysql > insert into tb_name values (value1,value2,value3,...); / * the second form needs to specify the column name and the inserted value: * / mysql > insert into tb_name (column1,column2,column3,...) Values (value1,value2,value3,...); for example: / * insert data * / mysql > insert into Student values (20180001, Zhang San, male, 20 CS); mysql > insert into Student values (20180002, Li Si, male, 19 CS); mysql > insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (20180003, Wang Wu, male, 18 Ma); mysql > insert into Student (Sno,Sname,Ssex,Sage,Sdept) values (20180004, Zhao Liu, male, 20pm is)

3. Query data (Select)

Select statements can not only view the information of tables and views in the database, but also view the system information of SQL Server, copy, and create data tables. Its query function is powerful, and it is not only the soul statement of SQL language, but also the most frequently used statement in SQL.

Basic select statement:

A basic select statement can be broken down into three parts: what data to look for (select), where to find it (from), and what conditions to find (where).

The general format of the select statement is as follows: select

[new into table name]

From table or view name

[where]

[group by]

[having]

[order by [ASC | DESC]] (1) query the specified column

1. All columns in the query table represent all columns of the query table when the * sign is used at the position specified by the select statement.

Template: select * from tb_name

two。 When querying multiple columns for the columns specified in the query table, the column names are separated by commas.

Template: select tb_name.,... From tb_name

3. Specify the column headings in the query results to make the output more understandable by specifying the column headings (also called column aliases).

When you specify a column header, you can use the AS clause after the column name, or you can specify the column header in the form of: column alias =.

The format of the AS clause is: column name or evaluation expression [AS] column header

Template: select as column heading 1, as column heading 2, as column heading 3 from bt_name

4. Query the calculated column (that is, the value of the expression)

When using select to query a column, we can not only directly take the original value of the column as the result, but also take the calculated value of the column value as the query result, that is, the select clause can query the value of the expression, and the expression can be composed of column name, constant and arithmetic operator.

The query result calculation column shows "no column name". It is generally necessary to add a column title to the calculation column.

Among them: the operators that can be used in the expression are: add +, subtract -, multiply *, divide /, take the remainder%.

Template: select, column header = * n from tb_name

(2) Select rows: select some or all of the rows in the table as the result of the query

Format: select [all | distinct] [top n] from table name

1. Eliminate duplicate rows in query results for a relational database, each row in the table must be different (that is, no duplicate rows). However, if only some of the columns are selected when querying the table, duplicate rows may appear in the query results.

Use the distinct keyword in a select statement to eliminate duplicate rows in the result set

Template: select distinct [,...] From tb_name

two。 Limit the number of rows returned in the query results

Use the top option to limit the number of rows returned by the query result, that is, the number of records that you specify.

Where: n is a positive integer that represents the first n rows of the query result set; with the percent keyword, the first n% rows of the query result set are returned.

Template: celect top n from tb_name; / * query the data of the first n * /

Template: celect top n percent from tb_name; / * query the data of n% tb_name * /

(3) query rows that meet the condition: use where clause to implement conditional query

Implemented through the where clause, which must immediately follow the From clause.

Format: select [all | distinct] [top n]] from table name where

Description: the following operators or expressions can be used in query conditions:

Operator identification

The comparison operator =,!

< 范围运算符 between... and，not between... and 列举运算符 in，not in 模糊匹配运算符 like，not like 空值运算符 is null，is not null 逻辑运算符 and，or，not 1.使用比较运算符： 模板：select * from tb_name where >

= n

two。 Specify range: there are two keywords for the specified range: between...and and not between...and.

Format: select * from tb_name where [not] between and

Among them: the between keyword is followed by the lower limit of the range (that is, the low value), and the and keyword is followed by the upper limit of the range (i.e. the high value)

Used to find rows whose field values are (or are not) in the specified range. 3. Use enumeration:

Use the in keyword to specify a collection of values that lists all possible values, returning true when the value of the expression matches any element in the collection, or false otherwise.

Template: select * from tb_name where [not] in (value 1, value 2, value n)

4. Use wildcards for fuzzy queries:

You can use the like clause to make a fuzzy matching query for a string, and the like clause returns a logical value (true or False).

Format of like clause: select * from tb_name where [not] like

The meaning is to find a record whose specified field value matches the matching string. The matching string usually contains wildcards% and _ (underscore).

Where:%: a string representing any length (including 0)

5. Queries using null

Use the is null keyword when you need to determine whether the value of an expression is null.

When not is not used, true is returned if the value of the expression is null, otherwise false; is returned. When not is used, the result is just the opposite.

Template: select * from tb_name where is [not] null

6. Multiple conditional queries: using logical operators

The logical operators and (and: both conditions must be met) and or (or: one of the conditions can be satisfied) can be used to join multiple query conditions. And takes precedence over or, but you can change the priority if you use parentheses.

Template: select * from tb_name where = 'volues' and > n

(4) sort the query results

The order by clause can be used to sort query results in ascending (ASC) or descending (DESC) order by the value of one or more fields (or the value of an expression). The default is ascending order.

Format: order by {sort expression [ASC | DESC]} [,... n]

The sort expression can be either a single field, an expression composed of fields, functions, constants, etc., or a positive integer.

Template: select * from tb_name order by

(5) use statistical functions: also known as set functions, aggregate functions

When retrieving a table, it is often necessary to calculate or count the results. T-SQL provides some statistical functions (also known as set functions or aggregate functions) to enhance the retrieval function. Statistical functions are used to calculate the data in a table, that is, these functions are used to calculate a set of data and return a single value.

Common statistical function table

Function name function

AVG to calculate the average value

Count calculates the number of records and returns an integer of type int.

Max to find the maximum

Min to find the minimum

Sum summation

1. SUM and AVG

Function: find the sum or average of the specified numerical expression.

Template: average number of select avg () as, total number of sum () as from tb_name where = 'string'

2. Max and Min

Function: find the maximum or minimum value of the specified expression.

Template: select max () as maximum, min () as minimum from tb_name

3. Count

This function has two formats: count (*) and count ([all] | [distinct] field name). To avoid errors, count (*) is used to query the number of records, and count (distinct field name) is used to query several values of a field.

(1) count (*):

Function: count the total number of records.

Template: total select count (*) as from tb_name

(2) .count ([all] | [distinct] field name)

Function: count the number of records in which the specified field value is not empty. The data type of the field can be any type other than text, image, ntext or uniqueidentifier.

Template: select count () as Total from tb_name

(6) grouping query results

The group by clause is used to group query result tables according to one or more column values. Those with equal column values are grouped together, and each group statistics a result. This clause is often used with statistical functions for grouping statistics.

Format: group by grouping field [,... n] [having]

1. After using the group by clause

The select list can only contain the grouping fields and statistical functions specified in the group by clause.

The usage of 2.having clause

The having clause must be used in conjunction with the group by clause to filter the grouped results (statistical functions are often included in the filter conditions).

3. The condition of not including statistical function in grouping query

The where clause is usually used; for conditions that contain statistical functions, you can only use the having clause.

Template: select, count (*) as column header from tb_name where = 'string' group by

4. Modify data (Update)

The Update statement is used to modify the data in the table.

Format: update tb_name set column name = new value where column name = a value

5. Delete data (Delete)

Delete a single Lin

Format: delete from tb_name where column name = a value

Delete all rows

You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:

Format: delete * from tb_name or delete from tb_name; IV, MySQL-- alter command the alter add command is used to add fields to the table.

Alter add command format: alter table table name add field type other

For example, a field passtest is added to the table MyClass, with a type of int (4) and a default value of 0:

Mysql > alter table MyClass add passtest int (4) default '0students; add two fields mysql > alter table Person add age int,add address varchar (11); delete two fields mysql > alter table Person drop column age,drop column address; modified field comments mysql > alter table `student`modify column `id`student'; 1) add indexing

Mysql > alter table table name add index index name (field name 1 [, field name 2 …])

Mysql > alter table employee add index emp_name (name); 2) add the index of the primary key

Mysql > alter table table name add primary key (field name)

Mysql > alter table employee add primary key (id); 3) Index with unique constraints

Mysql > alter table table name add unique index name (field name)

Mysql > alter table employee add unique emp_name2 (cardnumber); 4) Delete an index

Mysql > alter table table name drop index index name

Mysql > alter table employee drop index emp_name;5) add field

Mysql > ALTER TABLE table_name ADD field_name field_type;6) modify the original field name and type mysql > ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;7) delete the field

MySQL ALTER TABLE table_name DROP field_name; V. MySQL-Applied Student-course Database Student Table: Student (Sno,Sname,Ssex,Sage,Sdept)

Course schedule: Course (Cno,Cname,Cpno,Ccredit)

Student elective schedule: SC (Sno,Cno,Grade)

The master code of the relationship is underlined. Examples of data in each table are shown in the figure: Student table

School number

Sno

Name

Sname

Gender

Ssex

Age

Sage

The department where it belongs

Sdept

20180001 Zhang Sannan 20CS20180002 Li Sinan 19CS20180003 Wang Wunan 18MA20180004 Zhao Liunan 20ISCourse watch

Course number

Cno

Course name

Cname

Take a course first

Cpno

Credit

Ccredit

1 database 542 mathematics

23 Information system 144 operating system 635 data structure 746 data processing

27PASCAL language 64SC table

School number

Sno

Course number

Cno

Achievement

Grade

20180001

19220180001285201800013882018000229020180002380

Create a "students" table Student:

Create table Student (Sno char (9) peimary key, / * column-level integrity constraints, Sno is the master code * / Sname char (20) unique, / * Sname takes a unique value * / Ssex char (2), Sage smallint, Sdept char (20)) Create a "course" table Course:create table Course (Sno char (4) primary key, / * column-level integrity constraints, Cname cannot take null values * / Sname char (40) not null, / * Cpno means prior courses * / Cpno char (4) Ccredit smallint, foreign key (Cpnoo) references Course (Cno) / * table-level integrity constraints, Cpno is foreign code, referenced table is Course, referenced column is Cno*/) Establish the student course selection table SC:create table SC (Sno char (9), Cno char (4), Grade smallint, frimary key (Sno,Cno), / * the master code is composed of two attributes, which must be defined as table-level integrity * / foreign key (Sno) references Student (Sno), / * table-level integrity constraints, Sno is foreign code, and the referenced table is Student*/ foreign key (Cno) references Course (Cno) / * table-level integrity constraints. Cno is an external code, and the referenced table is Course * /) These are all the contents of this article entitled "what are the common commands in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.