MYSQL Foundation One

I. Overview

1. What is a database?
　A: Data warehouses, such as: In the ATM example we created a DB directory called the database

2. What is MySQL, Oracle, SQLite, Access, MS SQL Server, etc.?
　A: They are all software and have two main functions:

• A. Saving data to a file or memory
• B. Receive a specific command, and then perform the appropriate action on the file
  
  PS: If you have the above software, do not have to create files and folders yourself, but instead of directly passing commands to the above software, let them for file operations, they collectively referred to as the database management system (Dbms,database Management Systems)

3. What is SQL?
A: The above mentioned MySQL and other software can accept the command, and do the appropriate action, because the command can contain delete files, get the contents of the file, and so many operations, for the written command is the SQL statement. SQL????????, is???????????????????????????? Structured language (structured Query Language???????? ) of the abbreviation, SQL???????????????????????????????? is a language specifically designed to communicate with a database.

Second, download the installation

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company. MySQL's most popular relational database management system, MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software in WEB applications.

To use MySQL to store and manipulate data, you need to do a few things:
A. Installing the MySQL server
B. Installing the MySQL Client
B. "Client" Connection "server Side"
C. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)

Download        http://dev.mysql.com/downloads/mysql/Install        windows:            dot dot        Linux:            yum Install Mysql-server        Mac:            dot Dot

Window version

1. Download

MySQL Community Server 5.7.16http://dev.mysql.com/downloads/mysql/

2. Decompression

If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-5.7.16-winx64

3. Initialization

After MySQL extracted the bin directory there is a lot of executable files, execute the following command to initialize the data:

CD C:\mysql-5.7.16-winx64\binmysqld--initialize-insecure

4. Start the MySQL service

Execute the command to start the MySQL service

# Go to executable directory CD c:\mysql-5.7.16-winx64\bin# start MySQL service mysqld

5. Start the MySQL client and connect to the MySQL service

The default password is not set to the root account due to the "mysqld--initialize-insecure" command used during initialization

# Enter the executable directory CD c:\mysql-5.7.16-winx64\bin# connect to MySQL server mysql-u root-p# prompt Please enter the password, direct return

Enter the entry to see that the installation was successful:

To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.

A. Adding environment variables

Add the MySQL executable to the environment variable to execute the command

"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"-"to append the MySQL bin directory path to the variable value, and split" such as: \ C Program Files (x86) \parallels\parallels tools\applications;%systemroot%\system32;%systemroot%;%systemroot%\ System32\wbem;%systemroot%\system32\windowspowershell\v1.0\; C:\Python27; C:\Python35; C:\mysql-5.7.16-winx64\bin

This way, when you start the service and connect later, you only need to:

# Start the MySQL service, connect the MySQL service in terminal input mysqld#, enter in Terminal: Mysql-u root-p

B. Making a MySQL service into a Windows service

The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:

# make MySQL Windows service, execute this command at Terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--install# remove the MySQL Windows service, execute this command at Terminal: "\ C Mysql-5.7.16-winx64\bin\mysqld "--remove

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:

# Start MySQL service net start mysql# shut down MySQL service net stop MySQL

Linux version

Installation:

Yum Install Mysql-server

Service-side startup

Mysql.server start

Client connections

    Connection:        mysql-h host-u user-p        Common errors: Error            2002 (HY000): Can ' t connect to local MySQL server through socket '/TM P/mysql.sock ' (2), it means that the MySQL server daemon (Unix) or service (Windows) was not running.    Quit:        quit or Control+d

III. database operations 1. Database related

Create a database

create database 数据库名称; create database 数据库名称 default charset utf8;创建一个数据库,以utf8作为编码格式.

View Database

2. User-related

Create a new user

create user "用户名城"@"ip或者域名"  

Delete User

drop user "用户名城"@"ip或者域名";

Modify User

rename "用户名城"@"ip或者域名" to "用户名城"@"ip或者域名";

Change Password

set password for "用户名城"@"ip或者域名" = password("新密码")

• PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

3. Permission-related

Add permissions to a user

grant 权限 on 数据库名称.表名称 to "用户名城"@"ip或者域名";

View authorizations

show grant for "用户名城"@"ip或者域名"

Cancel Authorization

revoke 权限 on 数据库.表 from "用户名城"@"ip或者域名"

• Of special

Read data into memory for immediate effect

flush privileges.

Permissions

All privileges except grant all permissions select only Check permissions Select,insert check and insert Permissions ... usage        No access permission ALTER uses ALTER TABLE ALTER ROUTINE using ALTER procedure and drop procedure Create using CREATE table create routine using CREATE PROCEDURE create temporary TA        Bles using the Create temporary tables create user using the Create user, drop user, rename user, and revoke all privileges Create view uses the CREATE view Delete to use the drop with delete drop        Table execute uses call and stored procedure file using SELECT INTO outfile and load data infile Grant option uses the index insert to use the INS with GRANT and REVOKE index                  ERT lock tables using the show full Processlist select using the lock table process    Using Select    show databases using show databases show view with show view update using U                   Pdate reload using flush shutdown using mysqladmin shutdown (off MySQL) super ???? Use change master, kill, logs, purge, master, and set global. also allows mysqladmin???????? Debug login Replication Client server location access replication slave used by replication slaves

Iv. List of related

View Table

Working with databases

Create a table

create table 表名称(    列名 类型 是否可为空 ,#加上auto_increment则会自增,primary key 设置为主键,不可重复,唯一标识,增加查找速度.    列名 类型 是否可为空 ,); engine=innodb default charset=utf8;        # innodb 支持事务，原子性操作        # myisam myisam

FOREIGN key

外键，一个特殊的索引，只能是指定内容        creat table color(            nid int not null primary key,            name char(16) not null        )        create table fruit(            nid int not null primary key,            smt char(32) null ,            color_id int not null,            constraint fk_cc foreign key (color_id) references color(nid)        )

Add data

增加一行数据:insert into 表名称(列名称,列名称,) values(对应的值,对应的值,);增加多行数据: insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)增加列:insert into 表 (列名,列名...) select (列名,列名...) from 表

Delete a table

不会清空ID:delete from 表名;清空ID:truncate table 表名;删除整个表:drop from 表名称;

View Table

直接用绝对路径查找:select 表名称.列名称 from 表名称;按一定条件查找: select 列名称 from 表名称 where id>1;查找某些属性:select nid,name,gender as gg from 表 where id > 1;

Modify Table

添加列：alter table 表名 add 列名 类型删除列：alter table 表名 drop column 列名修改列：        alter table 表名 modify column 列名 类型;  -- 类型        alter table 表名 change 原列名 新列名 类型; -- 列名，类型添加主键：        alter table 表名 add primary key(列名);删除主键：        alter table 表名 drop primary key;        alter table 表名  modify  列名 int, drop primary key;添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);删除外键：alter table 表名 drop foreign key 外键名称修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

• Conditional filtering

  A, conditions

  select * from 表 where id > 1 and name != ‘alex‘ and num = 12;select * from 表 where id between 5 and 16;select * from 表 where id in (11,22,33)select * from 表 where id not in (11,22,33)select * from 表 where id in (select nid from 表)

  B. Wildcard characters

  select * from 表 where name like ‘ale%‘  - ale开头的所有（多个字符串）select * from 表 where name like ‘ale_‘  - ale开头的所有（一个字符）

  C, restrictions

  select * from 表 limit 5;            - 前5行select * from 表 limit 4,5;          - 从第4行开始的5行select * from 表 limit 5 offset 4    - 从第4行开始的5行

  D, sort

  select * from 表 order by 列 asc              - 根据 “列” 从小到大排列select * from 表 order by 列 desc             - 根据 “列” 从大到小排列select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

  E, group

  select num from 表 group by numselect num,nid from 表 group by num,nidselect num,nid from 表  where nid > 10 group by num,nid order nid descselect num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nidselect num from 表 group by num having max(id) > 10特别的：group by 必须在where之后，order by之前

  F, even table

  无对应关系则不显示select A.num, A.name, B.namefrom A,BWhere A.nid = B.nid无对应关系则不显示select A.num, A.name, B.namefrom A inner join Bon A.nid = B.nidA表所有显示，如果B中无对应关系，则值为nullselect A.num, A.name, B.namefrom A left join Bon A.nid = B.nidB表所有显示，如果B中无对应关系，则值为nullselect A.num, A.name, B.namefrom A right join Bon A.nid = B.nid

  G, combination

  组合，自动处理重合select nicknamefrom Aunionselect namefrom B组合，不处理重合select nicknamefrom Aunion allselect namefrom B

  　

V. Basic data types

MySQL data types are broadly divided into: numeric, time, and string

Bit[(m)] bits (101001), M represents the length of bits (1-64), default M=1 tinyint[(m)] [unsigned] [zerofill] small integers, data types are used to hold some range of integer numeric ranges:        Signed:-128 ~ 127.    Unsigned: 0 ~ 255 Special: MySQL has no boolean value and is constructed using tinyint (1).            int[(M)][unsigned][zerofill] integer, data type is used to hold some range of integer numeric ranges: Signed: 2147483648 ~ 2147483647 Unsigned: 0 to 4294967295 Special: M in integer type is only for display and has no limit on storage range.                 For example: Int (5), when inserting data 2 o'clock, the data is displayed as: 00002 bigint[(m)][unsigned][zerofill] Large integer, data type is used to hold some range of integer numeric ranges: Signed: -9223372036854775808 ~ 9223372036854775807 unsigned: 0 ~ 18446744073709551615 deci mal[(M[,d])] [unsigned] [Zerofill] Accurate small value, M is the total number of digits (minus sign), D is the number of decimal points.        The M maximum value is 65,d maximum of 30.    Special: The reason why this type of decaimal is needed for accurate numerical calculations is that it stores the exact value internally as a string.            float[(m,d)] [UNSIGNED] [Zerofill] single-precision floating-point number (not accurate decimal value), M is the sum of numbers, D is the number after the decimal point. Unsigned: -3.402823466E+38 to-1.175494351e-38, 1.175494351E-38 to 3.402823466E+38 signed: 1.175494351E-38 to 3.402823466E+38 * * * * * * * * * * The higher the value, the less accurate * *            double[(m,d)] [UNSIGNED] [Zerofill] double-precision floating-point number (not accurate decimal value), M is the total number of numbers, D is the number of decimal places. Unsigned: -1.7976931348623157E+308 to-2.2250738585072014e-308 2.2250738585072014E-308 to 1.79769 31348623157E+308 signed: 2.2250738585072014E-308 to 1.7976931348623157E+308 * * * * The larger the number, the less accurate * CHAR (m) the char data type is used to represent a fixed-length string that can contain up to 255 characters.        where m represents the length of the string. PS: Even if the data is less than m length, the M-length varchar (m) Varchars data type is used for variable-length strings and can contain up to 255 characters.        where m represents the maximum length of a string that is allowed to be saved by the data type, as long as a string that is less than the maximum value can be saved in that data type. Note: Although varchar is more flexible to use, the char data type can be processed faster and sometimes more than 50% of the varchar processing speed from the overall system performance perspective.    Therefore, users in the design of the database should be comprehensive consideration of various factors, in order to achieve the best balance text text data type used to save the large string of variable length, you can group more than 65535 (2**16 1) characters.    Mediumtext a TEXT column with A maximum length of 16,777,215 (2**24? 1) characters. Longtext a TEXT column with A maximum length of 4,294,967,295 or 4GB (2**32 1) characters. Enum enum type, an enum column can has a maximum of 65,535 distinct elements.        (The practical limit is less than 3000.) Example: CREATE TABLE Shirts (name VARCHAR (+), size ENUM (' x-small ', ' small ', ' medium            ', ' large ', ' x-large ');    INSERT into shirts (name, size) VALUES (' Dress shirt ', ' large '), (' T-shirt ', ' Medium '), (' Polo shirt ', ' small ');        Set set Type a set column can have a maximum of distinct members.            Example: CREATE TABLE myset (Col SET (' A ', ' B ', ' C ', ' d '));    INSERT into MySet (col) VALUES (' A,d '), (' D,a '), (' A,d,a '), (' A,d,d '), (' d,a,d '); DATE Yyyy-mm-dd (1000-01-01/9999-12-31) Time HH:MM:SS (' -838:59:59 '/' 838:59:59 ') year YYYY (1901/2 155) DATETIME Yyyy-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP YYYYMMDD HHM MSS (1970-01-01 00:00:00/2037 years)

MYSQL Foundation One