MySql-Day-01

Time:2021-10-20

MySql

  • Be able to understand the concept of database
  • Can install MySQL database
  • Be able to start, shut down and log in to MySQL
  • Be able to operate the database with SQL statements
  • Ability to manipulate table structures using SQL statements
  • SQL statements can be used to add, modify and delete data
  • Ability to add constraints using SQL statements

1、 Database introduction

1.1 database concept

  • What is a database

    ​ Database is the warehouse for storing data. Its essence is a file system. The data is stored in a specific format. Users can add, modify, delete and query the data in the database.

  • What is a database management system

    ​ Database management system (DBMS): refers to a large-scale software for operating and managing the database, which is used to establish, use and maintain the database, and uniformly manage and control the database to ensure the security and integrity of the database. Users access the data in the tables in the database through the database management system.

  • Relationship between database and database management system

1.2. Database table

​ Data is stored in the database in the form of tables.

​ Tables are similar to Java classes, and each field has a corresponding data type.

By comparing Java programs with relational data, you will find the following corresponding relationships.

  • Class ———— table
  • Attribute in class ————- field in table
  • Object ————- record

1.3 data sheet

​ According to the data type specified in the table field, we can fill it with data one by one, and each data in the table is similar to the instance object of the class. Row by row information in a table is called a record.

  • Table records the correspondence between Java class objects

1.4 common databases

​ Common database management system

  • MySQL: open source free database, small database. Has been acquired by Oracle. MySQL 6. X version also began to charge.
  • Oracle: a large database charged by Oracle company. Oracle acquired sun and mysql.
  • DB2: IBM’s database product, for a fee. It is often used in banking system.
  • SQL Server: a medium-sized database charged by Microsoft. C #,. Net and other languages are often used.
  • Sybase: it has faded out of the historical stage. PowerDesigner provides a very professional data modeling tool.
  • SQLite: a small embedded database, which is applied on the mobile phone.

​ Common databases: MySQL, Oracle.

​ Mysql database is used here. There can be multiple databases in mysql, and the database is the real place to store data.

2、 Mysql database

2.1. MySQL installation

  • Installation (refer to MySQL installation diagram. DOC)

​ After installation, MySQL will provide us with data storage function in the form of windows service. Opening and closing services:

​ Right click my computer → management → services → find MySQL service to start or stop.

​ You can also start and stop the MySQL service through commands in the DOS window (you must run the CMD command window as an administrator).

2.2. Log in to MySQL database

​ MySQL is a database that requires an account name and password to log in. It is used after logging in. It provides a default root account. You can log in using the password set during installation.

Format 1: CMD > MySQL – u user name – P password

For example: MySQL - uroot – proot
Format 2: CMD > MySQL -- host = IP address -- user = user name -- password = password

For example: MySQL -- host = 127.0.0.1 -- user = root -- password = root

3、 SQL statement

3.1 SQL overview

Introduction to SQL statements

​ The database does not know the Java language, but we also need to interact with the database. At this time, we need to use the SQL statement, which is the code of the database. Structured query language (SQL for short) is a standard that relational database management systems need to follow. Different database manufacturers support SQL statements, but they all have unique contents.

SQL statement classification

  • SQL classification:
    • Data definition language: referred to as DDL (data De “nition language) for short, which is used to define database objects: database, table, column, etc. Keywords: create, alter, drop, etc.
    • Data manipulation language: DML (data manipulation language) for short, which is used to update the records of tables in the database. Keywords: insert, delete, update, etc.
    • Data control language: referred to as DCL (data control language) for short, which is used to define the access rights and security levels of the database and create users.
    • Data query language: DQL (data query language) for short, which is used to query the records of tables in the database. Keywords: select, from, where, etc.

SQL general syntax

  • SQL statements can be written in one or more lines, ending with semicolons.
  • Spaces and indents can be used to enhance the readability of statements.
  • The SQL statements of MySQL database are not case sensitive. It is recommended to use uppercase for keywords.
    • For example: select * from user.
  • You can also use / * * / to complete the annotation.
  • The data types we often use in MySQL are as follows:
Type name explain
int(Integer) Integer type
double Decimal type
decimal(m,d) Specifies the decimal type of integer and decimal length
date Date type, format yyyy MM DD, including year, month and day, excluding hour, minute and second
datetime Date type, format yyyy-mm-dd HH: mm: SS, including hours, minutes and seconds
timestamp Date format, timestamp
varchar(M) Text type, M is an integer between 0 and 65535

3.2. Database operation of DDL: Database

Create database

Format:

Create database database name;
Create database database name character set character set;

For example:

#The code of the data in the database is the default code specified when installing the database utf8
CREATE DATABASE webdb_1;
#Create a database and specify the encoding of the data in the database
CREATE DATABASE webdb_2 CHARACTER SET utf8;

view the database

To view all databases in the MySQL server:

show databases;

To view the definition information of a database:

Show create database database name;

For example:

show create database webdb_1;

Delete database

Drop database database name;

For example:

drop database webdb_2;

Use database

View databases in use:

select database();

Other database operation commands switch databases:

Use database name;

For example:

use webdb_1;

3.3. DDL table operation: table

Create table

Format:

Create table table name(
	Field name type (length) [constraint],
	Field name type (length) [constraint],
	...
);
Type:
	varchar(n) 	 character string
	int 			 plastic
	double 		 floating-point
	date 		 time
	timestamp 	 time stamp
Constraints: (see Chapter 4 for details)
	Primary key is the primary key. The data in the field modified by the primary key cannot be duplicate or null.

For example: create a classification table

CREATE TABLE category(
	CID int primary key, # classification ID
	CNAME varchar (100) # classification name
);

View table

  • View all tables in the database:
Format: show tables;
  • View table structure:
Format: desc table name;
For example: desc category;

Delete table

  • Format: drop table name;
For example: drop table category;

Modify table structure format

  • Alter table name add column name type (length) [constraint];
    Function: modify the table and add columns.
For example: #1. Add a new field to the classification table to describe the classification varchar (20)
ALTER TABLE category ADD 'desc' VARCHAR(20);
  • Alter table name modify column name type (length) constraint;
    Function: modify the type, length and constraints of table columns.
For example: #2. Modify the description field of the classification table and add the constraint not null for the type varchar (50)
ALTER TABLE category MODIFY 'desc' VARCHAR(50) NOT NULL;
  • Alter table name change old column name new column name type (length) constraint;

    Function: modify table column names.

For example: #3. Replace the classification name field of the classification table with description varchar (30)
ALTER TABLE category CHANGE 'desc' description VARCHAR(30);
  • Alter table name DROP column name;
    Function: modify table and delete columns.
For example: #4, delete the Description column in the classification table
ALTER TABLE category DROP description;
  • Rename table name to new table name;
    Function: modify table name.
For example: #5. Rename the classification table category to category 2
RENAME TABLE category TO category2;
  • Alter table name character set character set (understand);
    Function: modify the character set of the table.
For example: #6. Modify the code table of the classification table category to GBK
ALTER TABLE category CHARACTER SET gbk;

3.4 DML data operation language

Insert table record: Insert

  • Syntax:
--Insert some fields into the table
Insert into table (field 1, field 2, field 3..) values (value 1, value 2, value 3..);
--Insert all fields into the table in the order in which the table was created.
Insert into table values (value 1, value 2, value 3..);
  • be careful:
  • Values and fields must correspond, with the same number and type
  • The data size of the value must be within the length of the field
  • Except for the numeric type, the values of other field types must be enclosed in quotation marks. (single quotation mark recommended)
  • If you want to insert a null value, you can not write the field or insert null.
  • For example:
Insert into category (CID, CNAME) values ('c001 ',' electrical ');
Insert into category (CID, CNAME) values ('c002 ',' clothing ');
Insert into category (CID, CNAME) values ('c003 ',' cosmetics');
Insert into category (CID, CNAME) values ('c004 ',' books');
INSERT INTO category (cid) VALUES ('c005');
Insert into category (CNAME, CID) values ('consumables', 'C006');

Update table record: update

​ It is used to modify the data of specified conditions, and modify the specified column of records that meet the conditions to the specified value.

  • Syntax:
    Updates the specified fields for all records
Update table name set field name = value, field name = value;

​ Update the specified field of the eligible record

Update table name set field name = value, field name = value,... Where condition;
  • be careful:
    • The type of column name should be consistent with the modified value
    • The maximum length cannot be exceeded when modifying the value
    • In addition to the numeric type, the values of other field types must be enclosed in quotation marks

Delete record: delete

  • Syntax:

    Delete from table name [where condition];

3.5. DOS operation data garbled code solution

When we operate Chinese on the DOS command line, we will report an error

Insert into category (CID, CNAME) values ('c010 ',' Chinese ');
ERROR1366 (HY000): Incorrect string value:'\xB7\xFE\xD7\xB0' for column 'cname' at row 1

Error reason: the MySQL client setting code is utf8, while the CMD window code of the system is GBK
1. View the codes set internally in MySQL

show variables like 'character%';  View all MySQL codes

2. The codes of client, connection and results need to be modified to be consistent (GBK code)
Solution 1: enter a command in the CMD command window. This operation is valid in the current window and is a temporary scheme.

set names gbk;

Solution 2: modify the my.ini file in the installation directory and restart the service to take effect everywhere.

4、 SQL constraint

4.1 primary key constraints

The primary key constraint uniquely identifies each record in the database table.

  • The primary key must contain unique values.
  • Primary key columns cannot contain null values.
  • Each table should have a primary key, and each table can only have one primary key.

Add primary key constraint

Method 1: when creating a table, declare the specified field as the primary key at the field description:

CREATE TABLE persons
(
	id_p int PRIMARY KEY,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
) ;

Method 2: when creating a table, declare the specified field as the primary key in the constraint constraint area:

  • Format: [constraint name] primary key (field list)
  • The keyword constraint can be omitted. If you need to name the primary key, the constraint cannot be omitted. The primary key name is generally useless.
  • The field list needs to be enclosed in parentheses. If there are multiple fields, it needs to be separated by commas. Declare more than two fields as primary keys, which we call joint primary keys.
CREATE TABLE persons
( 
    firstname varchar(255),
	lastname varchar(255),
	address varchar(255),
	city varchar(255),
	CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
) ;

Method 3: after creating a table, modify the table structure and declare the specified field as the primary key:

  • Format: alter table persons add [constraint name] primary key
CREATE TABLE persons
(
	firstname varchar(255),
	lastname varchar(255),
	address varchar(255),
	city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

Delete primary key constraint

To undo the primary key constraint, use the following SQL:

ALTER TABLE persons DROP PRIMARY KEY;

4.2 automatic growth column

We usually want the database to automatically generate the value of the field every time a new record is inserted.
We can use auto in the table_ Increment keyword. The automatic growth column type must be an integer, and the automatic growth column must be a key (usually a primary key).

  • The following SQL statement defines the “p_id” column in the “persons” table as auto_ Increment primary key
CREATE TABLE persons
(
	p_id int PRIMARY KEY AUTO_INCREMENT,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);
  • When adding data to persons, it can not be P_ The ID field can also be set to null. The database will automatically maintain the primary key value:
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates');
  • Extension: default Auto_ The start value of increment is 1. If you want to modify the start value, use the following SQL syntax:
ALTER TABLE persons AUTO_INCREMENT=100;
  • Interview questions

Q: for Auto_ Increment, delete all records in the table. What is the difference between using the delete from table name or using the truncate table table name?

Delete method:
Delete one by one, do not empty Auto_ Number of increment records.
Truncate directly deletes the table and recreates it. Auto_ Increment will be set to zero and start again.

4.3 non null constraints

The not NULL constraint forces columns not to accept null values.
The not NULL constraint forces fields to always contain values. This means that you cannot insert a new record or update a record without adding a value to the field.

  • The following SQL statement forces the “id_p” and “LastName” columns not to accept null values:
CREATE TABLE persons
(
	id_p int NOT NULL,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);

4.4 unique constraints

The unique constraint uniquely identifies each record in the database table. Both unique and primary key constraints guarantee the uniqueness of columns or column sets. Primary key has an automatically defined unique constraint. Note that each table can have multiple unique constraints, but each table can only
There is a primary key constraint.

Add unique constraint

There are three ways to add a primary key

  • Method 1: when creating a table, declare unique in the field description:
CREATE TABLE persons
(
	id_p int NOT NULL,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);
  • Method 2: when creating a table, declare uniqueness in the constraint area:
CREATE TABLE persons
( 
	id_p int,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255),
	Constraint name unique (id_p)
) ;
  • Method 3: after creating a table, modify the table structure and declare that the fields are unique:
Alter table persons add [constraint name] unique (id_p);

Delete unique constraint

  • To undo the unique constraint, use the following SQL:
Alter table persons drop index name;
  • If the constraint name is not set when adding a unique constraint, it defaults to the field name of the current field.

Chapter 5 MySQL database password reset (extension)

1. Stop the MySQL server. Enter services.msc to stop the MySQL service.

2. Under CMD, enter mysqld — console — skip grant tables to start the server. The following page appears. Do not close the window.

3. Open CMD newly and enter MySQL – uroot. No password is required.

use mysql;
update user set password=password('abc') WHERE user='root';

4. Close both CMD windows.

Recommended Today

SQL statement of three-level linkage of provinces, cities and counties

The first is the table creation statement Copy codeThe code is as follows: CREATE TABLE `t_address_province` ( `id` INT AUTO_ Increment primary key comment ‘primary key’,`Code ` char (6) not null comment ‘province code’,`Name ` varchar (40) not null comment ‘province name’)Engine = InnoDB default charset = utf8 comment = ‘province information table’; CREATE TABLE […]