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

1. Introduction to mysql database:

Mysql database belongs to relational database. Relational database refers to the database that uses relational model to organize data, which is similar to Excel functional relational table. Maintaining the consistency of data is the greatest advantage of relational database

Relational database bottlenecks:

1. High concurrency read and write hard disk Istroke O bottleneck

two。 For relational data, the query efficiency in a massive data table is very low.

3. Scale-out is difficult, when the number of users of an application system is increasing day by day, there is no way to expand and provide load capacity as simple as web server by adding more hardware or nodes.

Mysql still has a great advantage for the application system with low concurrency and small amount of data, and at present mysql belongs to open source software, and mariadb, the derivative database of mysql, is also developing rapidly.

2. Introduction to mysql:

SQL

Sql: user and database delivery language, known as sql,sql is the abbreviation of Structured Query Language structure language query language, and it is the database application language of relational number.

Sql classification

DDL: data definition statement

These statements define different data segments, databases, columns, tables, indexes, and other database objects. Common keywords include create, drop, alter

DML: data manipulation statement

Used to add, delete, update, and query database records, common statement keywords include: insert, delete, update, select

DCL: data control language

Used to control permission and access level statements before different data segments, which define databases, tables, fields, user access, permissions, and security levels. The main statement keywords include grant and revoke.

DDL statement

DDL is the abbreviation of data definition, simply speaking, it is the language for creating, deleting, modifying and other operations on the internal objects of the database. The difference with DML is that DML only operates on the internal data of the table, and does not involve the modification of the definition structure, let alone other objects. DDL is used by database administrators.

Library operation

1. Database creation

Start the database to connect to the mysql server to use

[root@localhost] # mysql-u root-p Enter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

When you enter a command to add after its execution, or\ g ends, if you do not add the default newline to continue to enter the command.

Use the help; or\ h command for help and clear the command line buffer with the\ c command.

The database creation syntax is as follows:

CREATE DATABASE DBNAME

For example:

Mysql > CREATE DATABASE liaodb;Query OK, 1 row affected (0.00 sec)

Query OK: created successfully. (all operations of DML and DDL, except select, will display Query OK after successful operation)

1 row affected indicates that the operation affects only one row of records

(0.00 sec) indicates the time the operation was executed

two。 View the database

Syntax:

SHOW DATABASES

Mysql > show DATABASES;+-+ | Database | +-+ | information_schema | | liaodb | | mysql | | test | +-+ 4 rows in set (0.00 sec)

The default data exists:

Information_schema data object information storage, such as user table information.

Mysql stores information such as user permissions of the system.

Test database created automatically by test system, which can be used by all users

3. Select a database

Syntax:

USE DBNAME

Mysql > USE mysql;Database changed

4. Delete database

Delete data syntax

Drop database DBNAME

Delete the database liaodb we just created

Mysql > drop database liaodb;Query OK, 0 rows affected (0.00 sec)

Note:

Delete the database carefully, after deleting the database by default, the following tables will be deleted together.

Table operation

1. Create a tabl

Grammar

CREATE TABLE TABLENAME (column_name_1 colemn_type_1 constraints,olumn_name_2 colemn_type_2 constraintsolumn_name_3 colemn_type_3 constraints)

Column_name_1 is the name of the column; column_type is the data type of the column; and constraints is the constraint of the column.

Let's create one of the most common general class statistics

The table includes name,age,sex, with field types using varchar (10), date, varchar (1)

Mysql > create table class_one (name varchar (10), age INT (3), sex varchar (1)); Query OK, 0 rows affected (0.02 sec) mysql > show tables;+-+ | Tables_in_test | +-+ | class_one | +-+ 1 row in set (0.00 sec)

two。 View the definition of the table

Syntax:

DESC TABLENAME

Mysql > DESC class_one +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | name | varchar (10) | YES | | NULL | age | int (3) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | +-+-+ 4 rows in set (0.00 sec)

View more detailed table definition information

Show create table tablename\ Gsmith MySQL > show create table class_one\ G * * 1. Row * * Table: class_oneCreate Table: CREATE TABLE `class_ one` (`name` varchar (10) DEFAULT NULL, `age` int (3) DEFAULT NULL, `sex` varchar (1) DEFAULT NULL,ENGINE=MyISAM DEFAULT CHARSET=latin11 row in set (0.00 sec) ERROR: No query specified

The\ G option means to enable records to be arranged vertically according to fields.

3. Delete tabl

DROP TABLE TABLENAME;mysql > DROP TABLE class_one;Query OK, 0 rows affected (0.00 sec)

4. Modify the table

1) the syntax for modifying table types is as follows:

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

For example: change the name field type in the previously made class_ one table to verchar (20)

Mysql > alter table class_one modify name varchar (20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table class_one\ G * * 1. Row * * Table: class_oneCreate Table: CREATE TABLE `class_ one` (`name` varchar (20) DEFAULT NULL, `age` int (3) DEFAULT NULL, `sex` varchar (1) DEFAULT NULL, `deptno` DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin11 row in set (0.00 sec) ERROR: No query specified

2) the syntax for adding table fields is as follows

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]

FIRST | AFTER

Define a new field to be placed before FIRST or after AFTER in that field

For example, add a fraction to the class_one table

Mysql > alter table class_one add column fraction int (3); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | name | varchar (15) | YES | | NULL | | age | int (3) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | fraction | int (3) | YES | | NULL | | +-+- -+ 4 rows in set (0.00 sec)

3) delete the fields in the table as follows

ALTER TABLE tablename drop [COLUMN] column_definition [FIRST | col_name]

For example, delete the fraction and deptno fields in class

Mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (6) | YES | | NULL | age | int (3) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | fraction | int (3) | YES | | NULL | | +-- -+ 4 rows in set (0.00 sec) mysql > alter table class_one drop column fraction Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table class_one drop column deptno;Query OK, 0 rows affected (0 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Name | varchar (6) | YES | | NULL | | age | int (3) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec)

4) the syntax for renaming the field is as follows

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST\ AFTER col_name]

For example, change the age field to age1 and the field type to int (2)

Mysql > alter table class_one change column age age1 int (2); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Name | varchar (6) | YES | | NULL | | age1 | int (2) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec)

5) change the sorting of fields

Grammatical keywords

FIRST | AFTER

FIRST means to put in front of a field

AFTER means to put after a field.

Example: add a field named age and put it after the name field

Mysql > alter table class_one add column age int (4) after name;Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc class_one-> +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Name | varchar (6) | YES | | NULL | | age | int (4) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec)

Modify the field name to place it in the sex field

Mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Name | varchar (6) | YES | | NULL | | age | int (4) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | +-+-+ 3 rows in set. Sec) mysql > alter table class_one modify name varchar (6) after sex Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc class_one +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Age | int (4) | YES | | NULL | | sex | varchar (1) | YES | | NULL | | name | varchar (6) | YES | | NULL | | +-+-+ 3 rows in set (0.01sec)

6) change the syntax of the table name

ALTER TABLE tablename RENAME [TO] new_tablename

For example, change class_one to class_1

Mysql > show tables;+-+ | Tables_in_test | +-+ | class_one | +-+ 1 row in set (0.00 sec) mysql > alter table class_one rename class_1;Query OK, 0 rows affected (0.00 sec) mysql > show tables +-+ | Tables_in_test | +-+ | class_1 | +-+ 1 row in set

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.