MySQL First day

Database

Additions and deletions to the crud

E-r model

• Entry (entity), relationship (relationship)
• Relationship describes the corresponding rules between two entities

Three paradigms

Design database specification, called paradigm

• First normal form (1NF): Column non-split
• Second paradigm (2NF): Uniquely identifying
• Third normal form (3NF): Reference primary key
• Building on the basis of the previous paradigm

Field type

• Numbers: Int,decimal
• String: Char,varchar,text
• Date: datetime
• Boolean: Bit constraint
• Primary KEY Primary Key
• Uniquely unique
• Foreign key foreign Keygui:navicat tombstone (ISDELETE:0/1)

Command line Operation Connection

[[email protected]]# mysql -u root -p

Help

[[email protected]]# mysql --help

Remote connection

[[email protected]]# mysql -h ipaddress -u root -p

Database operations Create a database

create database 数据库名 charset=utf8;

Deleting a database

drop database 数据库名;

Switch database

use 数据库名;

Show all databases

show databases;

Table Operation Display Table

show tables；

Create a Table!!

create table 表名(列，类型等)；

• Example:
  create table students(id int auto_increment primary key,sname varchar(10) not null);
• Auto_increment for automatic growth modification table

  ALTER TABLE table name add|change|drop column name type;

• Example:
  alter table students add birthday datetime;

  Delete a table

  drop table name;

  View Table

  DESC table name;

  Change table name

  Rename table name to new table name;

  View creation statements for a table

  Show create table ' name ';

  Data manipulation
  1. Inquire
     select * from 表名
  2. Increase
     Full column insert: INSERT into table name values (...)

  Default insert: INSERT into table name (column 1,...) VALUES (value 1,...)

  Insert multiple data at the same time: INSERT into table name values (...), (...) ...;

  or INSERT into table name (column 1,...) VALUES (value 1,...), (value 1,...) ...;

  3. Modify
     Update table name set column 1= value 1,... Where condition
  4. Delete
     Delete from table name where condition backup and restore
• Go to MySQL library directory
  cd /var/lib/mysql
• Run the mysqldump command
  mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
• Recovery

  mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql

Summarize

1. Creating database: Create Datebase database name Charset=utf8;
2. CREATE TABLE: Create table table name (field type constraint);
3. ID int auto_increment PRIMARY key NOT NULL,
4. Insert data: INSERT into table name () values ();
5. Modify data: Update table name set field name = Value,......
6. Delete data: Delete from table name

5/10/2018 7:43:31 PM

MySQL First day