MySQL Basic knowledge Review

Markdownpad Documentmysql

MySQL: is a software for managing files-server-side software-Socket server-local file operations-parsing Instructions "SQL statements"-client software (various)-socket client -Send instruction-parse instruction "SQL statement" PS:-DBMS: Database management System-SQL statement requires skills:-Install server and client-connect-Learn SQL To instruct the server to do anything else similar software: relational database: Sqllite,db2,oracle,access,sql server MySQL non-relational database: Mongodb,redis

MySQL Installation

windows:     executable File Installation:        while true:              Next      Compress package           Place any directory          initialize               Service side: E:\somewhere\mysql-5.7.16-winx64\bin\mysqld  --initialize-insecure                     #  Default User name  root  password: empty           start service side:             e:\somewhere\ mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld         Client Connection:             e:\somewhere\mysql-5.7.16-winx64 \mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p          Send command:                 show databases; #查看数据库                  create database  DB1; Create a configuration for database          environment variables:             E:\somewhere\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin             mysqld    add to System environment variable path         windows Service: (made into Windows service, can be powered on automatically)              E:\somewhere\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install             net start MySQL              e:\somewhere\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove             net start MySQL             net stop mysql

About connecting to a database

Connection:     Default: User Root    show databases;    use   Database name;     #使用数据库     show tables;        #查看数据表     select * from  table name;      #查询     select name,age,id from  table name;     mysql Database User table       #系统自带 (storing MySQL user account information)     use mysql;    select  user,host from user;     Create user:           create user  ' Alex ' @ ' 192.168.1.1 '  identified by  ' 123123 ';           create user  ' Alex ' @ ' 192.168.1.% '   identified by  ' 123123 ';           create user   ' Alex ' @ '% '  identified by  ' 123123 ';     authorized:           rights     People           grant select,insert,update   on db1.t1 to  ' Alex ' @ '% ';          grant  all privileges  on db1.t1 to  ' Alex ' @ '% ';           revoke all privileges on db1.t1 from  ' Alex ' @ '% ';

Learning SQL statement Rules

Actions folder     create database db2;    create database  db2 default charset utf8; *****    show databases;     drop database db2; Operation Files     show tables;     Create table t1 (Id int,name char (Ten))  default charset=utf8;     create table t1 (Id int,name char (Ten)) engine=innodb default charset=utf8;     create table t3 (Id int auto_increment,name char (Ten)) engine= INNODB&NBSP;DEFAULT&NBSP;CHARSET=UTF8;&NBSP;&NBSP;*****&NBSP;&NBSP;&NBSP;&NBSP;CREATE&NBSP;TABLE&NBSP;T1 (          column name   type  null,          column name   type  not null,         column name   type  not  null auto_increment primary key,        id int,         name char (Ten)     ) engine=innodb default charset=utf8;         # innodb  support transactions, atomic operations (Error rollback)          # myisam myisam        auto_ increment : Self-increment         primary key:   representation   constraints ( Cannot be repeated and cannot be empty);  accelerated find         not null:  is empty           Data Type:              Numbers: (note distinguishing between unsigned, unsigned,signed)                  tinyint                 int                bigint                 FLOAT                      0.00000100000123000123001230123                 DOUBLE                     0.00000000000000000000100000123000123001230123                      0.00000100000123000000000000000                 decimal  (exact decimal notation, underlying string implementation, recommended)                      0.1             string:                 char (Ten)         Fast speed ()                  varchar     space-saving                  PS:  Create a data table fixed-length column forward                  text type: Maximum character 65535                  Uploading Files:                       File Storage Drive         &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;DB Storage Path               Time Type                  DATETIME            enum             set         CREATE&NBSP;TABLE&NBSP;T1 (            id int  signed not null auto_increment primary key,             num decimal (10,5),             name char (Ten)         ) engine=innodb  default charset=utf8;     Empty table:         delete  from t1;   #自动列信息保留         truncate table  t1;   #自动列信息Clear      Delete Table:        drop table t1; contents in action file      Insert Data:         insert into t1 (Id,name)  values (1, ' Alex ');     Delete:        delete  from t1 where id<6     modifying:         update t1 set age=18;        update t1  set age=18 where age=17;     View Data:         select * from t1;

FOREIGN key:

CREATE TABLE UserInfo (UID bigint auto_increment primary key, name varchar (+), department_id int, Constraint Fk_user_depar foreign KEY (department_id) references Department (ID), engine=innodb default Charset=utf    8; CREATE TABLE department (ID bigint auto_increment primary key, Title char ()) Engine=innodb default Char Set=utf8;

MySQL Basic knowledge Review