Basic operation of MySQL database 1

MySQL Database management day01
Related Conceptual issues
Database introduction? Warehouse where data is stored

Database services are those companies in use?
Shopping website Game website Financial website

What data does the data service store?
Data information corresponding to the account information

What are the software that provides database services?
Open source software MySQL, MongoDB, Redis
Business software Oracle, DB2, SQL SERVER

Is the software cross-platform? Linux Unix Windows

The source of the package: Download your own package using the operating system installation CD

MySQL software introduction? MySQL mariadb
Relational data-based software: to store data in a certain organizational structure, and to correlate data and data with each other.
Cross-platform Linux Unix Windows
Portability is strong
Supports multiple languages python/java/perl/php

In a production environment, data services and Web services work together to build a Web site to run the platform
Lnmp LAMP Wnmp WAMP

Package type of MySQL package: RPM package source package can customize installation information

Non-relational database software (NoSQL) MongoDB,
Redis, memcached

1 build MySQL database server
2 MySQL Service Basic usage
3 MySQL data type

First, build MySQL database server 192.168.4.51
Loading package
Modifying a configuration file
Start the service
View service processes and port numbers
Service Name Mysqld
Main configuration file for the service/etc/my.cnf
Data Catalog/var/lib/mysql
Log file/var/log/mysqld.log

Second, the basic use of database services
2.1 Using the initial password in the native Connection database service
MySQL [-H database Server IP address-u username-P ' password '

2.2 Resetting the native connection password mysql> alter user

[email protected] "localhost" identified by "password";

2.3 The process of storing data on a database server?
2.4 SQL command classification? DDL DML DTL DCL
2.5 SQL command usage rules?
2.6 Naming rules for SQL commands and library names for managing databases
2.7 SQL commands for managing tables
What is the syntax format for the build table?
CREATE table library name. Table Name (
Field Name Type (width) constraints,
Field Name Type (width) constraints,
.....
）;

2.8 SQL commands for managing records
View
Insert
Update
Delete
+++++++++++++++++++++++++
Third, MySQL data type
3.1 Value type (score age wage)
The range of storage data for each type is fixed
Integer type (only integers are stored)
Large integer type maximum integer type for small integral type of tiny integer
tinyint smallint mediumint INT bigint
Unsigned uses a range of numeric types that are signed.

Floating point type (storing decimals)
Float (m,n)
Double (m,n)

M set total number of digits
N set the number of decimal digits

Positive number. Decimal total digits integer digits decimal places
18088.88 7 5 2

3.2 Character type (product name, hometown name manufacturer)
char (255) fixed-length character type
varchar (65532) variable-length character type

Large text type (audio file video file picture file)
Blob
Text

3.3 Date and Time type
(Registration time Appointment time meeting time date of entry time birthday)

Year YYYY 2018
Date YYYYMMDD 20180423
Time HHMMSS 161958
Date Time Datetime/timestamp
YYYYMMDDHHMMSS 20180423161958

Get datetime assigns a value to a field of the corresponding datetime type
Get Date Time function
Now () Gets the time of the current system
Year (datetime) Gets the years in the specified time
Month (datetime) Gets the months in the specified time
Date (datetime) Gets the day of the specified time
Day (datetime) Gets the number in the specified time (in days)
Time (datetime) Gets the times in the specified time

You can use 2-bit numbers to assign values to a field of year, as follows:
01-69 20XX
70-99 19XX

3.4 Enumeration type (the value recorded when the record is inserted is selected within the enumerated range)
Gender Hobby Majors

Enum (value list) radio
Set (value list) multiple selection

Data type keyword Storage Range assignment method reasonable use data types

* * Focus
The width of a numeric type is the width of the display and cannot be assigned the size of the field. The value of the field is determined by the type

Availability

Mysql-day02
First, field constraint conditions
1.1 Function: Limit How to assign a value to a field
1.2 Includes content: NULL Key Default Extra
NULL allows null values to be assigned to the field
Null defaults allow null values to be assigned
Not NULL does not allow null values to be assigned
Key value type: Normal index unique index full-text index primary key foreign key

Default defaults: Assign values to a field using default values when not assigned to a field
Do not set default value is System-defined is NULL
Default value
Extra additional settings, whether the field is set to auto-increment, no auto-grow function by default

Second, modify the table structure
2.1 Commands to modify table structure
mysql> ALTER TABLE Library. Perform the action;

Add new field Add field Name type (width) [constraint]

Delete an existing field drop field name

Modify the type width and constraints of an existing field
* * * * * * * * The changes cannot be changed with the data stored in contradiction, not allowed to modify
Modify field Name type (width) [constraint]

Modify field names
Change Source field name new field name Type (width) [constraint]

Modify Table Name
ALTER TABLE source table name Rename [to] new table name;

Third, MySQL key value
Set on a field in a table, the effect is to constrain how to assign a value to a field. The field is also indexed.

Index Description: Tree-like directory structure similar to the book "Directory"
Advantages: Speed up the query table record
Disadvantage: slows down the editing of table records and consumes the physical storage space of the disk
(Delete insert update)

Number of dictionaries total pages 1000 pages

Directory information
1------100 page Record catalog information
101---1000 page body

How to look up a dictionary
Stroke
Radical
Pinyin

Modify content, add content to delete content when modifying content

Stuinfo/var/lib/mysql/db2/stuinfo.*
Name Age Home Class
Dba

3.1 Key value: Normal index unique index full-text index primary key foreign key

3.1.1 Use of normal indexes (index)
Use rules?

View
DESC table name;
Show index from table name;
Table:t2
Key_name:aaa
Column_name:age
Index_type:btree (binary tree)

Create
Created in an existing table
Create index index name on table name (field name);

Build table is created when
CREATE TABLE Table name (
Field List,
Index (field name),
Index (field name),
）;

Delete
Drop index index name on table name;

+++++++++++++++++++++++++++++++
3.1.2 Primary Key Primary key
(Common primary key composite primary key +auto_increment)

Use rules?

View desc table; key----> PRI

Create
Create an ALTER TABLE table in an existing table Add primary key (field name);

Create a table when you build it
CREATE TABLE Table name (
Field List,
Primary key (field name)
）；

Create composite primary Key usage: Multiple fields together with the master key, when inserting a record, you can insert a record as long as the value of the main key field is not duplicated at the same time. (You must create a delete together, the values of multiple fields are not allowed to be the same at the same time)
Desc mysql.db;
Desc Mysql.user;

The primary key primary key is usually used with auto_increment.
Let the value of the field grow automatically i++
Numeric Type i=i+1

Delete primary key mysql> ALTER TABLE table drop PRIMARY key;
++++++++++++++++++++++++++++++++++++++
3.1.3 Foreign keys (how limits are assigned to a field)
When assigning a value to a field in the current table, the value can only be selected in the range of the specified field value for the specified (other) table.

Use rules?

Create the command format for the foreign key foreign key:
CREATE TABLE Table (
List of field names,
Foreign key (field name) References table name (field name)
On UPDATE cascade on DELETE CASCADE
) Engine=innodb;

Payment Form
Use STUDB;
CREATE TABLE JFB (
jfb_id Int (2) Zerofill primary key auto_increment,
Name Char (15),
Pay float (7,2)
) Engine=innodb;

Insert into JFB (Name,pay) VALUES ("Bob", 26800);
Insert into JFB (Name,pay) VALUES ("Tom", 26000);

SELECT * from JFB;

Class table
CREATE TABLE BJB (
bjb_id Int (2) Zerofill,
Name Char (15),
Foreign KEY (bjb_id) references JFB (jfb_id)
On UPDATE cascade on DELETE CASCADE
) Engine=innodb;

INSERT into BJB values (3, "Lucy");
INSERT into BJB values (1, "BZENGJIAOB");
INSERT into BJB values (2, "Tom");
SELECT * from BJB;

mysql> Update JFB set jfb_id=9 where name= "Bob";
Mysql> Delete from jfb where jfb_id=2;
Select from jfb;
Select from BJB;

View mysql> show create table table name;

Delete foreign key
ALTER TABLE name drop FOREIGN key foreign key name;
ALTER TABLE BJB drop foreign key bjb_ibfk_1;
Mysql> Delete from jfb where jfb_id=02; He has a synchronized role, and the class table doesn't have this one.
mysql> Update JFB set name= "Xixi" where jfb_id=05; Modify the Payment form ID is 05 change his name to Xixi.

Created in an existing table
Mysql> Delete from BJB;
Mysql> ALTER TABLE BJB add foreign key (bjb_id) references JFB
(jfb_id) on the update cascade on DELETE cascade;

Basic operation of the MySQL database 1