MySQL Database basics

First, MySQL introduction two, MySQL installation 1. Linux installation MySQL

Installing with 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 the MySQL official website

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

Add MySQL environment variables after download

A) Initialize:

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

b) Start the service side:

Mysqld

c) Connect the client:

Mysql-u root-p

d) Create MySQL database:

Create Database Sjingx;

e) Create a Windows service for MySQL:

MySQL Install (create MySQL service)

MySQL Remove (delete mysql service)

f) Create a good MySQL service to open and close

net start MySQL (turn on MySQL service)

net stop MySQL (turn off MySQL service)

3. OS x installation 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

Three, the library operation 1. System Database

INFORMATION_SCHEMA: Virtual library, do not occupy disk space, storage is the database startup parameters, such as user table information, column information, permission information, character information, etc.

Performance_schema:mysql 5.5 started a new database: Mainly used to collect database server performance parameters, record the processing of query requests occurred in various events, locks and other phenomena

MySQL: Authorization library, access information for primary storage system users

Test:mysql Database System Auto-created test database

2. Create a database

Grammar:

Create database name CharSet UTF8;

3. Database-related Operations

Create Database db1 charset UTF8; (creation of DB DB1, encoding format UTF8)

Show Create database sjingx; (view databases Sjingx information)

show databases; (View all databases)

ALTER DATABASE DB1 CharSet GBK; (The encoding format of the modified data db1 is GBK)

Drop database db1; (delete databases)

Four, table Operation 1. Storage Engine 2. Table additions and deletions to change

1) Create a table

Grammar:

create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]);#注意：1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

Example:

create table t1(     id int,    name varchar(50),    sex enum(‘male‘,‘female‘),    age int(3));# 查看db1库下所有表名show tables; select id,name,sex,age from t1;select * from t1;select * from t1;# 往表中插入数据insert into t1 values(1,‘alex‘,‘male‘,20);# 往id字段插入数据insert into t1(id) values(2),(3);

2) View table structure

# 查看表结构，可简写为desc 表名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    |       |+-------+-----------------------+------+-----+---------+-------+# 查看表详细结构，可加\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

Grammar:

1. 修改表名：    alter table 表名 rename 新表名;2. 增加字段：    alter table 表名 add 字段名 数据类型[完整性约束条件...],                     add 字段名 数据类型[完整性约束条件...];    alter table 表名 add 字段名  数据类型 [完整性约束条件…]  FIRST;    alter table 表名 add 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;3. 删除字段    alter table 表名 drop 字段名;4. 修改字段    alter table 表名 modify 字段名 数据类型 [完整性约束条件...];    alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];    alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];

Example:

1. Modify the storage Engine mysql> ALTER TABLE service-ENGINE=INNODB;2. Add Field mysql> ALTER TABLE student10, add name varchar (3) Not NULL, and add age int    Efault 22;                Mysql> ALTER TABLE student10, add stu_num varchar (TEN) not NULL after name;  After adding the Name field mysql> ALTER TABLE student10, add sex enum (' Male ', ' female ') default          ' Male ' first; Added to the front 3.    Delete field 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 (one) NOT null primary key auto_increment; Modify the primary key 5.    Add constraint (Increase auto_increment for existing primary key) mysql> ALTER TABLE student10 modify ID int (one) NOT null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> ALTER TABLE student10 ModiFY ID int (one) not NULL auto_increment; Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:06.        Add a composite primary key to an existing table mysql> ALTER TABLE SERVICE2, add primary key (Host_ip,port); 7. Increase the primary key mysql> ALTER TABLE STUDENT1, modify name varchar (TEN) NOT null primary key;8. Increase primary KEY and autogrow mysql> ALTER TABLE STUDENT1-Modify ID int not NULL PRIMARY key auto_increment;9.     Remove primary key A. Remove the self-increment constraint mysql> ALTER TABLE student10 modify ID int (one) is not null; B. Delete the primary key mysql> ALTER TABLE student10, drop primary key;

4) Copy Table

复制表结构＋记录 （key不会复制: 主键、外键和索引）mysql> create table new_service select * from service;只复制表结构mysql> select * from service where 1=2;        //条件为假，查不到任何记录Empty set (0.00 sec)mysql> create table new1_service select * from service where 1=2;  Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> create table t4 like employees;

5) Delete Table

drop table 表名;# 清空表，auto_increment = 0truncate 表名;

3. Data type

1) Numeric type

Integral type effect: Store age, class, ID, various numbers

Floating-point function: Store salary, height, weight, physical parameters

2) Date Type

Role: Store user registration time, article release time, employee entry time, birth time, expiration time

3) String type

char and varchar (char)

4) enumeration and collection types

4. Complete constraints

1) constraints not NULL vs. default

Not NULL non-null
Default Defaults
Zerofill: If you insert int (4), you insert 1, display 0001
Unsigned: cannot be negative when you insert a negative number to display 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) Unique constraint conditions

Unique constraint uniquely identifies each record in the database

Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection.

The PRIMARY KEY constraint has a UNIQUE constraint that is automatically defined.

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

Single Column only:

方式一：create table department(    id int,    name char(10) unique);方式二：create table department(    id int,    name char(10),    unique(name));

Union unique:

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

3) Constraint conditions primary key

Constraint: NOT null unique is not empty and unique

Storage Engine InnoDB: There must be a primary key in a table for the InnoDB storage engine

# 单列主键create table t1(    id int primary key,    name char(16));# 会自动呢找一个不唯一且为空的字段为主键create table t1(    id int not null unique,    name char(16));# 复合主键create table t1(    id char(15),    port int,    primary key(id,port))

4) Constraint conditions auto_increment

create table t1(    id int primary key auto_crement,    name char(15));# 了解show variables like ‘auto_inc%‘; 查看与auto_inc模糊查询# 步长auto_increment_increment默认为1# 起始偏移量auto_increment_offset默认为1# 设置步长set session auto_increment_increment=5;set global auto_increment_increment=5;# 设置起始偏移量（起始偏移量<=步长）set global auto_increment_offset=3;

5) Constraints Foregin key to establish a relationship between tables

You can see that the Department and department information in the following table is duplicated ID	name	sex	dep_name	Comment
1	Alex	Male	Technical Department	Technology
2	Sjingx	Male	Technical Department	Technology
3	Wuxx	Female	Finance Department	Money
4	Liunx	Male	Generation Department	Production
5	Python	Male	Technical Department	Technology

Forrgin key role: Easy to manage, save disk space

Example:

User_info table ID	name	sex	dep_id
1	Alex	Male	1
2	Sjingx	Male	1
3	Wuxx	Female	2
4	Liunx	Male	3
5	Python	Male	1

DEP table ID	name	Comment
1	Technical Department	Technology
2	Finance Department	Money
3	Generation Department	Production

# 先建被关联的表create table dep(    id int,    name char(16),    comment char(50));# 再建关联的表create table user_info( id int primary key, name char(10), sex enum(‘male‘,‘female‘), dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade);on delete cascade删除同步：被关联一删除，关联的表也删除了on update cascade更新同步：同上# 先往被关联表插入记录# 在往关联表插入记录# 删除

5. Relationship Between Tables v. data manipulation

MySQL Database basics