MySQL Library and table-related operations

I. Introduction of the Database

1, the origin of the database

What we've learned before is that data is stored in a file for permanent preservation, and there is no doubt that a file can only exist on a single machine, which poses many problems:

(1) All components of the program cannot be run on a single machine

(2) Data security issues

(3) Concurrency issues

Summary: Before we write any program, we need to write a web-based operation of the file on a host program (Socket server and client program), so someone will write such a program as a specialized processing software, this is the origin of MySQL and other database management software, MySQL not only solves the problem of data sharing, as well as query efficiency, security and a series of problems, in short, the programmer from the data management to free up, focus on their own program logic to write.

2. Database description

Database server-: Running database management software

Database management software: Management-Database

Database: A folder for organizing files/tables

Table: A file that is used to hold multiple lines of content/Multiple records

3. mysql Introduction

MySQL is a relational database management system

(1) divided into two major categories:

Relationship type: As Sqllite,db2,oracle,access,sql server,mysql, NOTE: SQL statements are common

Non-relational: Mongodb,redis,memcache

(2) can be simply understood as:

relational databases require a table structure

The non-relational database is key-value stored, without a table structure

Second, the operation

1. MySQL Database environment preparation

Environment: window7_x86_64

Version: Mysql-5.6.38-winx64.zip

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

File path: E:\mysql-5.6.38-winx64

Add to Environment variables: computer----> Properties----> Advanced system settings----> Environment variables----> System variables---->path---->E:\mysql-5.6.38-winx64\bin\;

2. Login and Set Password

c:\users\administrator> mysqld--installc:\users\administrator> net start MySQL #启动mysql数据库C: \user                               s\administrator> mysql #默认用户不用密码可以直接登录mysql > select User (); #查看当前登录用户, the ODBC user c:\users\administrator> mysqladmin-uroot-p password "123" is displayed #设置root账号的密码是123

3. Reset Password

c:\users\administrator> net stop mysqlc:\users\administrator> mysqld -- Skip-grant-tables new Open terminal Operation:c:\users\administrator> mysql -uroot -pmysql >  Update mysql.user set password=password ("")  where user= ' root '  and host= ' localhost ";mysql > flush privileges;mysql> exit; c:\users\administrator>tasklist |findstr mysql               #查看mysql服务的进程号mysqld .exe          6316 console          1    454,544  KC:\Users\Administrator>taskkill /F /PID 6316                 #结束mysql服务C:\users\administrator>net start  Mysql                   #启动mysql服务C: \users\administrator> mysql -uroot -p                   # Login does not require a password

4, the same character encoding

#mysql5. configuration file configuration of more than 5

[Mysqld]

Character-set-server=utf8

Collation-server=utf8_general_ci

[Client]

Default-character-set=utf8

[MySQL]

Default-character-set=utf8

Mysql> Show variables like '%char% '; #查看字符编码

5. Introduction to SQL language

The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL languages:

#1, DDL Statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER

#2, DML statement database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select

#3, DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

(1) Operation folder

Add: Create Database db1 charset UTF8;

Check: show databases;

Change: Alter DATABASE DB1 CharSet latin1;

Delete: Drop database db1;

(2) Operation file

Switch to the folder first: Use DB1

Add: CREATE TABLE t1 (id int, name char);

Check: Show tables

Change: ALTER TABLE t1 modify name char (3); ALTER TABLE T1 change name name1 char (2);

Delete: drop table T1;

(3) Contents/records in the operation file

Add: INSERT INTO T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');

Check: SELECT * from T1;

Change: Update t1 set name = ' SB ' where id = 2;

Delete: delete from t1 where id = 1;

Clear the table:

Delete from T1; # If there is a self-increment ID, the new data is still the same as the beginning of the last one before deletion.

TRUNCATE TABLE T1; Large amount of data, deleted faster than the previous one, and directly from zero,

Auto_increment says: self-increment

Primary key means: constraint (cannot be repeated and cannot be empty); Accelerated Lookup

6. Library operation

(1) View database

show databases;

Show CREATE Database db1;

Select Database ();

(2) Select database

Use database name

(3) Deleting a database

DROP database name;

(4) Modify the database

ALTER DATABASE DB1 CharSet UTF8;

7. Table operation

(1) Create a table

#注意:

1. Field names cannot be the same in the same table

2. Width and constraints are optional

3. Field names and types are required

mysql> show engines;                        #显示所有的存储引擎mysql > use db1mysql>  CREATE TABLE T1 (Id int) engine=innodb;mysql> create table t2 (id int ) Engine=myisam;mysql> create table t3 (Id int) engine=memory;mysql> create  TABLE T4 (Id int) engine=blackhole;mysql> show tables;                             #查看db1库下的所有的表名mysql > desc t1;                                 #查看t1表的表结构 #memory Storage engine, after restarting MySQL or restarting the machine, the data in the table empties the #blackhole storage engine, inserting any data into the table, is equivalent to throw into the black hole, There is never a record in the table Mysql> create table db1.f2 select user,host from mysql.user;            #复制表mysql > create table db1.f3 select user,host from  mysql.user where 1=2;     #只复制表结构 (The result of the query does not exist, there is no value) mysql> create table  db1.f4 like mysql.user;                        #只复制表结构

(2) Date type

CREATE TABLE student (id int, name char (+), Born_year year, birth_date date, class_time time, Reg_time                     datetime); INSERT into student values (1, ' Wang ', now (), now (), now (), now ());  #插入当前时间insert into student values (2, ' Wang ', ' 1999 ', ' 1999-11-11 ', ' 11:11:11 ', "1990-11-11 11:11:11"); #插入指定时间

(3) Character type

#char类型: Fixed length, simple rough, wasted space, fast access speed

Character Length range: 0-255 (one Chinese is a character, is a UTF8 encoded 3 bytes)

#varchar类型: variable length, precision, space saving, slow access speed

Character Length range: 0-65535 (if greater than 21845 prompts for other types.) MySQL line maximum limit is 65535 bytes, character encoding is utf-8)

#常用字符串系列: char and varchar

Note: Although varchar is more flexible to use, the char data type is processed faster and sometimes can exceed 50% of the varchar processing speed. Therefore, users should consider various factors in designing the database in order to achieve the best balance

#宽度代表的是字符的个数

CREATE table T6 (name char (5));

CREATE TABLE T7 (name varchar (5));

(4) Modify table ALTER TABLE

Grammar:

#1. modifying table names

ALTER table name RENAME new table name;

#2. Adding fields

ALTER Table Table Name

ADD field name data type [integrity constraint ...],

ADD field name data type [integrity constraint ...];

ALTER Table Table Name

ADD field name data type [integrity constraint ...] First;

ALTER Table Table Name

ADD field name data type [integrity constraint ...] After field name;

#3. Deleting a field

ALTER Table Table Name

DROP field name;

#4. modifying fields

ALTER Table Table Name

MODIFY field name data type [integrity constraint ...];

ALTER Table Table Name

Change old field name new field name old data type [integrity constraint ...];

ALTER Table Table Name

Change old field name new field name new data type [integrity constraint ...];

#5. Deleting a table

DROP table name;

Example:

#1. Modifying the storage Engine

mysql > ALTER TABLE service engine = InnoDB;

#2. Adding fields

mysql > ALTER TABLE student10, add name varchar (a) Not NULL, add age int (3) is not NULL default        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; Add to the front

#3. Deleting a 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. Increasing constraints (increasing auto_increment for existing primary keys)

mysql > ALTER TABLE student10 modify ID int (one) not NULL auto_increment;

#6. Add a composite primary key to a table that already exists

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 auto grow

mysql > ALTER TABLE student1 modify ID int not NULL primary key auto_increment;

#9. Deleting a primary key

A. Deleting a self-increment constraint

mysql > ALTER TABLE student10 modify ID int (one) not null;

B. Deleting a primary key

mysql > ALTER TABLE student10 drop PRIMARY key;

(5) Enum type and collection type

The value of a field can be selected only in a given range, such as a radio box, a multi-marquee

Enum Radio can only select a value within a given range

Set multiple selection One or more values can be selected within a given range

CREATE TABLE employee (ID int, name char (ten), Sex enum (' Male ', ' female ', ' other '), hobbies set (' play ', ' eat ', ' mus IC ', ' read '); INSERT into employee values (1, ' Li ', ' Male ', ' music,read '); INSERT into employee values (2, ' Wang ', ' xxxx ', ' Music,read ');

MySQL Library and table-related operations