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

MySQL basic operation mysql command mysql-h host_name-u user_name-ppasswordh: when connecting to the MySQL server is not on the same host, fill in the hostname or IP address u: login MySQL user name p: login MySQL password

-

Common commands:

Selected default database: use dbname; displays all databases: show databases; displays all tables in the default database: show tables; discards the command being entered:\ c display command list:\ h exit mysql program:\ Q View MySQL server status information:\ s

-

SQL syntax composition

DML (Data Manipulation Language data manipulation language)-- query, insert, delete and modify data in the database;-- SELECT, INSERT, UPDATE, DELETE, etc.; DCL (Data Control Language data Control language)-- used to control access permissions, access permissions, etc.;-- GRANT, REVOKE, etc. DDL (Data Definition Language data definition language)-- used to create databases, database objects and define their columns-- functional functions such as CREATE TABLE, DROP TABLE, ALTER TABLE, etc.-- date functions, mathematical functions, character functions, system functions, etc.

-

Several instructions on using SQL language in MySQL:

Belongs to a SQL statement and ends with a semicolon (;), otherwise mysql thinks the statement is not finished. The arrow (- >) indicates that the SQL statement has not been typed to cancel the SQL statement using (\ c) the keyword of the SQL statement and the function name are case-insensitive (Linux-sensitive, Windows-insensitive). When using a function, there can be no space between the function name and the following parentheses

-

Create database operation:

Syntax: create database database name description: create a database with the specified name. This statement fails if the database you want to create already exists or does not have the appropriate permissions to create it. Example: set up a student library. Mysql > create database student

-

Create table operation:

Syntax: create table table name (column name 1 column type [], column name 2 column type [],...); description: create a new data table under the current database. Column type: represents the data type of the column. Example: create a table school, which consists of two columns, the first column attribute is non-empty, and as the primary key, and add create table school (school_id int (10) not null auto_increment primary key, school_name varchar (20))

-

Common integrity constraints:

PRIMARY KEY primary code constraint (primary key) UNIQUE uniqueness constraint NOT NULL non-null constraint AUTO_INCREMENT is used for integer columns default increment 1UNSIGNED unsigned integer DEFAULT default_value default value constraint DEFAULT cur_timestamp default save current time when creating new records (timestamp data columns only) ON UPDATE cur_timestamp modify records default save current time (timestamp data columns only) CHARACTER SET name specified character set (strings only)

-

When creating a new MySQL data table

You can set a type for it, of which the three most important types are MyISAM: mature, stable and easy to manage InnoDB: add things, data row-level locking mechanism, foreign key constraints, crash recovery and other new functions HEAP: only exist in memory, can be used as a temporary table create table tmp (…) ENGINE=MyISAM

-

The association / reference relationship between data tables is established based on specific primary keys (primary key) and foreign keys (foreign key). Primary key: helps MySQL determine the location of a characteristic data record as quickly as possible. The primary key must be unique and the primary key should be compact, so the integer type is more suitable for foreign keys: referencing a record in another data table. The foreign key column type should be consistent with the primary key column type as much as possible, and the foreign key column should be added with NOT NULL

-

Primary key create table student (sid int not null auto_increment, name varchar (20) not null, primary key (sid)); Foreign key (automatically check whether the foreign key matches, only applicable to InnoDB) create table score (cid int not null auto_increment primary key, score int, sid int, foreign key (sid) references student (sid))

-

Master table and slave table 1. When there is no corresponding record in the master table, the record cannot be added to the subtable-the student number in the score table cannot appear in the student information table; 2. The value in the master table cannot be changed, resulting in the isolation of the records in the subtable.

The student number in the student information table should be changed, and the student number in the student transcript should also be changed accordingly.

3. There are records corresponding to the main table in the sub-table, and the row cannot be deleted from the main table-- students with achievements cannot be deleted. 4. Delete the sub-table before deleting the main table-delete the student score table first, and then delete the student information table.

-

Syntax: describe table name; desc table name

Description: used to display the creation structure of the table.

-

Delete table operation syntax: drop table [if exists] tab_name [, tab_name]... Description: deletes a given table from the database. If the if exists clause is given, there is no error in deleting a table that does not exist. Delete database operation syntax: drop database [if exists] db_name description: delete a given database. After you delete a database, it will never be there, so be very careful. If the if exists clause is given, there will be no error in deleting a database that does not exist.

-

Syntax: alter table table name action Description: action can be the following statement: add column name establishment statement [first | after column name] can add a column to the table. If first or after is not specified, add a column at the end of the column, otherwise add a new column add primary key (column name) to the table, if the primary key already exists Then there is an error add foreign key (column name) references table name (column name) add a foreign key alter column name set default default value to change the specified column default value

-

Change old column name new column name [first | after column name] column type and column name can be changed If the name of the original column is the same as the name of the new column, modify column name [first | after column name] and change serve the same purpose. Drop column name / / can delete a column drop primary key / / can delete the primary key engine type name / / can change the table type rename as new table name / / can change the table name

-

Example 1:

Add the field address2 to the people table with type varchar and maximum length 100alter table people add address2 varchar

Example 2:

Change the default value of the name column in the people table to 100:alter table people alter name set default 100

Example 3:

Add the "enrollment time" column to the student table, whose data type is date type. Alter table student add scome date; note: the newly added columns are null regardless of whether there is already data in the base table.

Example 4:

Change the data type of age to a half-word integer. Alter table student modify sage smallint

Note: modifying the original column definition may destroy the existing data

-

Query syntax: SELECT [DISTINCT | DISTINCTROW | ALL] select_expression,... [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC],...] [LIMIT [offset,] rows] [PROCEDURE procedure_name]]

-

From clause: specify table where clause of query data: filter condition group by clause of query data: group query results matching where clause having clause: conditional restriction order by clause: sort query results, followed by desc descending order or asc ascending order (default). Limit clause: limit the number of results displayed by a query procedure clause: query the result set data returned by the stored procedure

-

Use function: the statistical function provided by SQL is called set function. Main set function: count function: count (column name) to calculate the number of elements summation function: sum (column name) to sum the values of a column, but the attribute must be the integer calculation average: avg (column name) to calculate the average value of a column: max (column name) to find the maximum and minimum value of a column: min (column name) to find the minimum value of a column.

-

In the WHERE clause, use the predicate: BETWEEN AND: between two numbers NOT BETWEEN AND: not between two numbers IN: whether in a specific set (enumeration) NOT IN: contrary to the above LIKE: whether it matches a pattern IS NULL (empty) or IS NOT NULL (not empty) REGEXP: check whether a value matches a regular expression.

-

Subquery: a query that nests a query block within the condition of the WHERE clause or HAVING phrase of another query block is called a subquery. A SELECT-FROM-WHERE statement is called a query block

For example:

Select sname from student where sno in (select studentid from sc where courseid=2)

-

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.