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