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

The following together to understand the entry MySQL database needs to master the basic knowledge, I believe you will benefit a lot after reading, the text in the essence is not much, hope to enter the MySQL database need to master the basic knowledge of this short content is what you want.

1. Introduction to MySQL 2. MySQL installation 1. Linux installation mysql

Install using the yum package

Yum install mysql-server mysql- y

2. Windows install mysql

Download the windows version of mysql (MySQL Community Server) installation package on MySQL official website

Download address: https://dev.mysql.com/downloads/mysql/

After downloading, you need to add the environment variable of mysql.

A) initialize:

Mysqld-- initialise-insecure (--user=mysql)

B) start the server:

Mysqld

C) Connect the client:

Mysql-u root-p

D) create a mysql database:

Create database sjingx

E) create the windows service of MySQL:

Mysql install (create mysql service)

Mysql remove (delete mysql service)

F) create the opening and closing of the MySQL service

Net start mysql (enable mysql service)

Net stop mysql (turn off the mysql service)

3. OS X installs mysql4. Basic management

A) create a user

B) authorization

C) Import and export of databases

Mysqldump-u user name-p password database name > export file path

Third, library operation 1. System database

Information_schema: virtual library, which does not take up disk space, and stores some parameters after database startup, such as user table information, column information, permission information, character information, etc.

Performance_schema: MySQL 5.5.The new database is mainly used to collect database server performance parameters and record various events, locks and other phenomena that occur when processing query requests.

Mysql: authorization library, which mainly stores the permission information of system users.

Test: test database automatically created by MySQL database system

two。 Create a database

Syntax:

Create database database name charset utf8

3. Database related operations

Create database db1 charset utf8; (create database db1, encoding format is UTF8)

Show create database sjingx; (view database sjingx information)

Show databases; (view all databases)

Alter database db1 charset gbk; (modify the encoding format of data db1 to GBK)

Drop database db1; (delete database)

Fourth, table operation 1. Storage engine 2. Addition, deletion, modification and query of the table

1) create a table

Syntax:

Create table table name (field name 1 type [(width) constraint], field name 2 type [(width) constraint], field name 3 type [(width) constraint]); # Note: 1. In the same table, the field name cannot be the same 2. Width and constraints are optional. Field name and type are required

Example:

Create table T1 (id int, name varchar (50), sex enum ('male','female'), age int (3)); # View all the table names under the db1 library show tables; select id,name,sex,age from T1 select * from T1 select * from T1 select # insert data insert into T1 values into the table (1) insert into T1 (id) values (2), (3)

2) View the table structure

# View the table structure, which can be abbreviated as desc table name mysql > desc T1 +-+-- + | Field | Type | Null | Key | Default | Extra | + -- + | id | int (11) | YES | | NULL | | name | varchar (50) | YES | | NULL | | sex | enum ('male' 'female') | YES | | NULL | age | int (3) | YES | | NULL | | +-+-- # View the detailed structure of the table You can add\ Gmysql > show create table T1\ G * * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`id` int (11) DEFAULT NULL, `name` varchar (50) DEFAULT NULL, `sex` enum ('male','female') DEFAULT NULL, `age` int (3) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

3) modify the structure table

Syntax:

1. Modify table name: alter table table name rename new table name; 2. Added fields: alter table table name add field name data type [integrity constraint condition], add field name data type [integrity constraint condition]; alter table table name add field name data type [integrity constraint condition.] FIRST; alter table table name add field name data type [integrity constraints …] AFTER field name; 3. Delete field alter table table name drop field name; 4. Modify field alter table table name modify field name data type [integrity constraint condition]; alter table table name change old field name new field name old data type [integrity constraint condition.] ; alter table table name change old field name new field name new data type [integrity constraints.]

Example:

1. Modify the storage engine mysql > alter table service-> engine=innodb;2. Add the fields mysql > alter table student10-> add name varchar (20) not null,-> add age int (3) not null default 22; mysql > alter table student10-> add stu_num varchar (10) not null after name; / / add the name field after mysql > alter table student10-> add sex enum ('male','female') default' male' first / / add to the first 3. Delete the fields mysql > alter table student10-> drop sex; mysql > alter table service-> drop mac;4. Modify the field type modify mysql > alter table student10-> modify age int (3); mysql > alter table student10-> modify id int (11) not null primary key auto_increment; / / change to primary key 5. Add constraints (auto_increment for existing primary keys) mysql > alter table student10 modify id int (11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql > alter table student10 modify id int (11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 06. Add the compound primary key mysql > alter table service2-> add primary key (host_ip,port) to the existing table; 7. Add primary key mysql > alter table student1-> modify name varchar (10) not null primary key;8. Add primary key and auto-grow mysql > alter table student1-> modify id int not null primary key auto_increment;9. Delete the primary key a. Delete the self-increasing constraint mysql > alter table student10 modify id int (11) not null; b. Delete the primary key mysql > alter table student10-> drop primary key

4) copy the table

Replicate table structure + records (key does not copy: primary keys, foreign keys and indexes) mysql > create table new_service select * from service; only replicate table structure mysql > select * from service where 1 records 2; / / if the condition is false, no records can be found: Empty set (0.00 sec) mysql > create table new1_service select * from service where 1 records 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > create table T4 like employees

5) delete the table

Drop table table name; # clear table, auto_increment = 0truncate table name; 3. Data type

1) numeric type

Integer function: store age, grade, id, various numbers

Floating point function: store salary, height, weight, physique parameters

2) date type

Function: store user registration time, article release time, employee entry time, birth time, expiration time

3) string type

Char and varchar (char)

4) enumerations and collection types

4. Complete constraint

1) constraints not null and default

Not null is not empty

Default default value

Zerofill: if you insert int (4), you insert 1 and display 0001

Unsigned: cannot be negative. When you insert a negative number, it displays as 0.

Create table T1 (id int (11) unsigned zerofill); create table T1 (id int, name char (6), sex enum ('male','female') not null default' male')

2) constraint unique

The unique constraint uniquely identifies each record in the database

Both UNIQUE and PRIMARY KEY constraints provide a guarantee of uniqueness for columns or sets of columns.

PRIMARY KEY constraints have automatically defined UNIQUE constraints.

Note that each table can have multiple UNIQUE constraints, but each table can have only one PRIMARY KEY constraint.

A single column is unique:

Mode 1: create table department (id int, name char (10) unique); Mode 2: create table department (id int, name char (10), unique (name))

United only:

Create table services (id int, name char (15), port int, unique (id), unique (name,port))

3) constraint primary key

Constraints: not null unique is not empty and unique

Storage engine innodb: for the innodb storage engine, there must be a primary key in a table

# single column primary key create table T1 (id int primary key, name char (16)); # will automatically find a non-unique and empty field as primary key create table T1 (id int not null unique, name char (16)); # compound primary key create table T1 (id char (15), port int, primary key (id,port))

4) constraint auto_increment

Create table T1 (id int primary key auto_crement, name char (15)); # understand show variables like 'auto_inc%'; viewing and auto_inc fuzzy query # step size auto_increment_increment defaults to "start offset auto_increment_offset defaults to" set step size set session auto_increment_increment=5;set global auto_increment_increment=5;# to set start offset (start offset

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.