Basic operation of database

database

Basic concepts of database

  • It is more convenient to manage the data in development
  • Previously, data was managed through IO flow
    1. Use the character stream to read one line of data at a time
    2. Encapsulate the read data as an object
    3. Add multiple objects to the collection
    4. Traverse the collection to determine whether the object is the object you are looking for
    5. Find the object and modify it
    6. send
    7. Write set data back to file with character stream

Database introduction

  • Warehouse for storing and managing data

  • The English word is DataBase. DB for short

  • Large storage space, which can store millions, tens of millions and hundreds of millions of data

  • Use a unified way to operate the database - SQL

  • MySQL is one of the most popular relational database management systems. It was launched by Swedish MySQL AB company and later acquired by Oracle company

  • Relational database is to save data in different data tables, instead of putting all data into a large warehouse, and there can be correlation between tables. This improves access speed and flexibility

  • The SQL statements used by MySQL are the most commonly used standardized language for accessing databases

  • Free (before version 6)

Database installation

  1. Connect to Linux system through secureCRT tool

  2. Upload mysql installation package

alt + p -------> put d:/setup/mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
  1. Unzip the mysql installation package
mkdir mysql
tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C mysql/
  1. Install client
cd mysql/
rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm --force --nodeps
  1. Install server
rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm --force --nodeps
  1. Modify mysql default character set
vi /etc/my.cnf

Add the following:
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

-- It needs to be filled in at the bottom
[client]
default-character-set=utf8
  1. service mysql start 
service mysqld start
  1. Login to mysql
mysql -u root -p  Press enter and enter the password
 Initial password view: cat /var/log/mysqld.log
 stay root@localhost:   The following is the initial password
  1. Modify mysql login password
set global validate_password_policy=0;

set global validate_password_length=1;

set password=password('password');
  1. Grant remote connection permission
//to grant authorization
grant all privileges on *.* to 'root' @'%' identified by 'password';
//Refresh
flush privileges;
  1. Turn off the Linux system firewall
systemctl stop firewalld.service

DDL operation database and data table

Database, data table and data relation

  • Multiple databases can be created in MySQL server
  • Each database can contain multiple data tables
  • Multiple data records can be stored in each data table
  • The client operates the MySQL database through the database management system

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xokkpv7t-164417489265) (/ users / Herma / library / Application Support / typora user images / image-20220129165410540. PNG)]

Introduction to SQL

  • SQL (Structured Query Language): structured query language actually defines the rules for operating all relational databases
  • General grammatical principles
    • 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.
    • Comments for the database:
      • Single line comment: - comment content # comment content (unique to mysql)
      • Multiline comment: / * comment content*/
  • SQL classification
    • DDL(Data Definition Language)
      • Used to define database objects: database, table, column, etc. Keywords: create, drop,alter, etc
    • DML(Data Manipulation Language)
      • It is used to add, delete and modify the data of tables in the database. Keywords: insert, delete, update, etc
    • DQL(Data Query Language)
      • Used to query the records (data) of tables in the database. Keywords: select, where, etc
    • DCL(Data Control Language)
      • It is used to define the access rights and security levels of the database and create users. Keywords: GRANT, REVOKE, etc

DDL query and create database

  • Query all databases

    -- Query all databases
SHOW DATABASES;

  • Create statement to query a database

    -- Standard grammar
SHOW CREATE DATABASE Database name;

-- see mysql Database creation format
SHOW CREATE DATABASE mysql;

  • Create database

    -- Standard grammar
CREATE DATABASE Database name;

-- establish db1 database
CREATE DATABASE db1;

-- Creating an existing database will report an error
-- Error code: 1007  Can't create database 'db1'; database exists
CREATE DATABASE db1;

  • Create database (judge, create if it does not exist)

    -- Standard grammar
CREATE DATABASE IF NOT EXISTS Database name;

-- Create database db2(Judge, if not, create)
CREATE DATABASE IF NOT EXISTS db2;

  • Create a database and specify a character set

    -- Standard grammar
CREATE DATABASE Database name CHARACTER SET Character set name;

-- Create database db3,And specify the character set utf8
CREATE DATABASE db3 CHARACTER SET utf8;

-- see db3 Character set of database
SHOW CREATE DATABASE db3;

  • Exercise: create a db4 database, create if it does not exist, and specify the character set as gbk

    -- establish db4 The database is created if it does not exist, and the specified character set is gbk
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;

-- see db4 Character set of database
SHOW CREATE DATABASE db4;

DDL modify, delete and use database

  • U(Update): modify

    • Modify the character set of the database
    -- Standard grammar
ALTER DATABASE Database name CHARACTER SET Character set name;

-- modify the database db4 The character set of is utf8
ALTER DATABASE db4 CHARACTER SET utf8;

-- see db4 Character set of database
SHOW CREATE DATABASE db4;

  • D(Delete): delete

    • Delete database
    -- Standard grammar
DROP DATABASE Database name;

-- delete db1 database
DROP DATABASE db1;

-- Deleting a nonexistent database will report an error
-- Error code: 1008  Can't drop database 'db1'; database doesn't exist
DROP DATABASE db1;
    • Delete the database (judge and delete if it exists)
    -- Standard grammar
DROP DATABASE IF EXISTS Database name;

-- Delete database db2，If it exists
DROP DATABASE IF EXISTS db2;

  • Use database

    • Query the name of the database currently in use
    -- Query the database currently in use
SELECT DATABASE();
    • Use database
    -- Standard grammar
USE Database name;

-- use db4 database
USE db4;

DDL operation data sheet

  • R(Retrieve): query

    • Query all data tables in the database
    -- use mysql database
USE mysql;

-- Query all tables in the library
SHOW TABLES;
    • Query table structure
    -- Standard grammar
DESC Table name;

-- query user Table structure
DESC user;
    • Query table character set
    -- Standard grammar
SHOW TABLE STATUS FROM Library name LIKE 'Table name';

-- see mysql In database user Table character set
SHOW TABLE STATUS FROM mysql LIKE 'user';

  • C(Create): create

    • Create data table

      • Standard grammar
      CREATE TABLE Table name(
    Column name 1 data type 1,
    Column name 2 data type 2,
    ....
    Listing n data type n
);
-- Note: the last column does not need a comma
      • data type
      1. int: Integer type
	* age int
2. double:Decimal type
	* score double(5,2)
	* price double
3. date:Date, only including mm / DD / yy     yyyy-MM-dd
4. datetime:Date, including month, day, hour, minute and second	 yyyy-MM-dd HH:mm:ss
5. timestamp:Timestamp type	Including month, day, hour, minute and second	 yyyy-MM-dd HH:mm:ss	
	* If you do not assign a value to this field in the future, or assign a value of null，The current system time is used by default to assign values automatically
6. varchar: character string
	* name varchar(20):Name up to 20 characters
	* zhangsan 8 Two characters for three characters
      • Create data table
      -- use db3 database
USE db3;

-- Create a product Commodity list
CREATE TABLE product(
	id INT,				-- Item number
	NAME VARCHAR(30),	-- Trade name
	price DOUBLE,		-- commodity price
	stock INT,			-- Commodity inventory
	insert_time DATE    -- Shelf time
);
      • Copy table
      -- Standard grammar
CREATE TABLE Table name LIKE Table name to be copied;

-- copy product Table to product2 surface
CREATE TABLE product2 LIKE product;

  • U(Update): modify

    • Modify table name
    -- Standard grammar
ALTER TABLE Table name RENAME TO New table name;

-- modify product2 Table name is product3
ALTER TABLE product2 RENAME TO product3;
    • Modify the character set of the table
    -- Standard grammar
ALTER TABLE Table name CHARACTER SET Character set name;

-- see db3 In database product3 Data table character set
SHOW TABLE STATUS FROM db3 LIKE 'product3';
-- modify product3 The data table character set is gbk
ALTER TABLE product3 CHARACTER SET gbk;
-- see db3 In database product3 Data table character set
SHOW TABLE STATUS FROM db3 LIKE 'product3';
    • Add a column
    -- Standard grammar
ALTER TABLE Table name ADD Column name data type;

-- to product3 Add a column to the table color
ALTER TABLE product3 ADD color VARCHAR(10);
    • Modify column name and data type
    -- Modify data type standard syntax
ALTER TABLE Table name MODIFY Column name new data type;

-- take color The data type is modified to int
ALTER TABLE product3 MODIFY color INT;
-- see product3 Table details
DESC product3;


-- Modify the standard syntax for column names and data types
ALTER TABLE Table name CHANGE Column name new column name new data type;

-- take color Change to address,Data type is varchar
ALTER TABLE product3 CHANGE color address VARCHAR(30);
-- see product3 Table details
DESC product3;
    • Delete column
    -- Standard grammar
ALTER TABLE Table name DROP Listing;

-- delete address column
ALTER TABLE product3 DROP address;

  • D(Delete): delete

    • Delete data table
    -- Standard grammar
DROP TABLE Table name;

-- delete product3 surface
DROP TABLE product3;

-- Deleting a nonexistent table will report an error
-- Error code: 1051  Unknown table 'product3'
DROP TABLE product3;
    • Delete data table (judge and delete if it exists)
    -- Standard grammar
DROP TABLE IF EXISTS Table name;

-- delete product3 Table, delete if it exists
DROP TABLE IF EXISTS product3;

DML-INSERT statement

  • New syntax table

    • New format 1: add data to the specified column
    -- Standard grammar
INSERT INTO Table name(Column name 1,Column name 2,...) VALUES (Value 1,Value 2,...);

-- towards product Add a piece of data to the table
INSERT INTO product(id,NAME,price,stock,insert_time) VALUES (1,'mobile phone',1999,22,'2099-09-09');

-- towards product Add specified column data to table
INSERT INTO product (id,NAME,price) VALUES (2,'computer',4999);

-- View all data in the table
SELECT * FROM product;
    • New format 2: add data to all columns by default
    -- Standard grammar
INSERT INTO Table name VALUES (Value 1,Value 2,Value 3,...);

-- Add data to all columns by default
INSERT INTO product VALUES (3,'television',2999,18,'2099-06-06');

-- View all data in the table
SELECT * FROM product;
    • New format 3: batch add data
    -- Add all column data standard syntax by default
INSERT INTO Table name VALUES (Value 1,Value 2,Value 3,...),(Value 1,Value 2,Value 3,...),(Value 1,Value 2,Value 3,...);

-- Batch add data
INSERT INTO product VALUES (4,'Refrigerator',999,26,'2099-08-08'),(5,'Washing machine',1999,32,'2099-05-10');
-- View all data in the table
SELECT * FROM product;


-- Adds data standard syntax to the specified column
INSERT INTO Table name(Column name 1,Column name 2,...) VALUES (Value 1,Value 2,...),(Value 1,Value 2,...),(Value 1,Value 2,...);

-- Batch add specified column data
INSERT INTO product (id,NAME,price) VALUES (6,'Microwave Oven',499),(7,'Electromagnetic furnace',899);
-- View all data in the table
SELECT * FROM product;

  • matters needing attention

    • The number of column names and values and the data type should correspond
    • Except for the number type, data of other data types need quotation marks (single quotation and double quotation are OK, single quotation is recommended)

DML-UPDATE statement

  • Modify table data syntax
-- Standard grammar
UPDATE Table name SET Column name 1 = Value 1,Column name 2 = Value 2,... [where condition];

-- Modify the price of the mobile phone to 3500
UPDATE product SET price=3500 WHERE NAME='mobile phone';

-- View all data
SELECT * FROM product;

-- Modify the price of the TV to 1800 and the inventory to 36
UPDATE product SET price=1800,stock=36 WHERE NAME='television';

-- Modify the inventory of induction cooker to 10
UPDATE product SET stock=10 WHERE id=7;
  • matters needing attention
    • Conditions must be added in the modification statement
    • If no conditions are added, all data will be modified

DML-DELETE statement

  • Delete table data syntax
-- Standard grammar
DELETE FROM Table name [WHERE condition];

-- delete product Microwave oven information in table
DELETE FROM product WHERE NAME='Microwave Oven';

-- delete product Item information with inventory of 10 in the table
DELETE FROM product WHERE stock=10;

-- View all product information
SELECT * FROM product;
  • matters needing attention
    • Conditions must be added in the delete statement
    • If there are no conditions, all data will be deleted

DQL single table query

  • Data preparation (just copy directly)
-- establish db1 database
CREATE DATABASE db1;

-- use db1 database
USE db1;

-- Create data table
CREATE TABLE product(
	id INT,				-- Item number
	NAME VARCHAR(20),	-- Trade name
	price DOUBLE,		-- commodity price
	brand VARCHAR(10),	-- Commodity brand
	stock INT,			-- Commodity inventory
	insert_time DATE    -- Add time
);

-- Add data
INSERT INTO product VALUES (1,'Huawei Mobile',3999,'Huawei',23,'2088-03-10'),
(2,'Mi phones',2999,'millet',30,'2088-05-15'),
(3,'iPhone',5999,'Apple',18,'2088-08-20'),
(4,'Huawei computer',6999,'Huawei',14,'2088-06-16'),
(5,'Xiaomi computer',4999,'millet',26,'2088-07-08'),
(6,'Apple Computer',8999,'Apple',15,'2088-10-25'),
(7,'Lenovo computer',7999,'association',NULL,'2088-11-11');
  • Query syntax
select
	Field list
from
	Table name list
where
	Condition list
group by
	Grouping field
having
	Conditions after grouping
order by
	sort
limit
	Paging qualification
  • Query all
-- Standard grammar
SELECT * FROM Table name;

-- query product Table all data
SELECT * FROM product;

  • Query part

    • Multiple field query
    -- Standard grammar
SELECT Column name 1,Column name 2,... FROM Table name;

-- Query name, price and brand
SELECT NAME,price,brand FROM product;
    • Remove duplicate queries
      • Note: only all duplicates can be removed
    -- Standard grammar
SELECT DISTINCT Column name 1,Column name 2,... FROM Table name;

-- Query brand
SELECT brand FROM product;
-- Query brands and remove duplicates
SELECT DISTINCT brand FROM product;
    • Calculate the value of the column (four operations)
    -- Standard grammar
SELECT Column name 1 operator(+ - * /) Column name 2 FROM Table name;

/*
	Calculate the value of the column
	Standard syntax:
		SELECT Column name 1 operator (+ - * /) column name 2 FROM table name;
		
	If a column is null, it can be replaced
	ifnull(Expression 1, expression 2)
	Expression 1: the column you want to replace
	Expression 2: value to replace
*/
-- Query the commodity name and inventory, and add 10 to the original inventory quantity
SELECT NAME,stock+10 FROM product;

-- Query the commodity name and inventory, and add 10 to the original inventory quantity. conduct null Value judgment
SELECT NAME,IFNULL(stock,0)+10 FROM product;
    • Alias
    -- Standard grammar
SELECT Column name 1,Column name 2,... AS alias FROM Table name;

-- Query the commodity name and inventory, and add 10 to the original inventory quantity. conduct null Value judgment. Alias is getSum
SELECT NAME,IFNULL(stock,0)+10 AS getsum FROM product;
SELECT NAME,IFNULL(stock,0)+10 getsum FROM product;

  • Condition query

    • Condition classification
    Symbolfunction
    >greater than
    <less than
    >=Greater than or equal to
    <=Less than or equal to
    =be equal to
    < > or=Not equal to
    BETWEEN ... AND ...Within a certain range (all included)
    IN(...)Choose one more
    LIKE placeholderFuzzy query_ Single arbitrary character% multiple arbitrary characters
    IS NULLIs NULL
    IS NOT NULLNot NULL
    AND or&&also
    OR or||perhaps
    NOT or!No, No
    • Conditional query syntax
    -- Standard grammar
SELECT Listing FROM Table name WHERE condition;

-- Query commodity information with inventory greater than 20
SELECT * FROM product WHERE stock > 20;

-- Query the product information of Huawei brand
SELECT * FROM product WHERE brand='Huawei';

-- The inquiry amount is 4000 ~ 6000 Commodity information between
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;

-- Query commodity information with inventory of 14, 30 and 23
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);

-- Query inventory as null Product information for
SELECT * FROM product WHERE stock IS NULL;
-- Query inventory is not null Product information for
SELECT * FROM product WHERE stock IS NOT NULL;

-- Query the product information whose name starts with Xiaomi
SELECT * FROM product WHERE NAME LIKE 'millet%';

-- The second word of the query name is the product information of
SELECT * FROM product WHERE NAME LIKE '_by%';

-- Query the product information with a name of four characters
SELECT * FROM product WHERE NAME LIKE '____';

-- The query name contains the product information of the computer
SELECT * FROM product WHERE NAME LIKE '%computer%';

  • Aggregate function

    • Take a column of data as a whole for vertical calculation
    • Aggregate function classification
    Function namefunction
    Count (column name)Statistical quantity (generally non null columns)
    Max (column name)Maximum
    Min (column name)minimum value
    Sum (column name)Sum
    AVG (column name)average value
    • Aggregate function syntax
    -- Standard grammar
SELECT Function name(Listing) FROM Table name [WHERE condition];

-- calculation product Total records in the table
SELECT COUNT(*) FROM product;

-- Get the highest price
SELECT MAX(price) FROM product;
-- Get the product name with the highest price
SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);

-- Get minimum stock
SELECT MIN(stock) FROM product;
-- Get the item name of the lowest stock
SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);

-- Get total inventory quantity
SELECT SUM(stock) FROM product;
-- Get the total inventory of Apple brand
SELECT SUM(stock) FROM product WHERE brand='Apple';

-- Obtain the average commodity price of Xiaomi brand
SELECT AVG(price) FROM product WHERE brand='millet';

  • Sort query

    • Sorting and classification
      • Note: for multiple sorting conditions, the second condition will be judged only when the condition value of the current edge is the same
    key wordfunction
    ORDER BY column name 1, sort method 1, column name 2, sort method 2Sort the specified column, ASC ascending (default) DESC descending
    • Sorting syntax
    -- Standard grammar
SELECT Listing FROM Table name [WHERE condition] ORDER BY Column name 1 sort by 1,Column name 2 sort by 2;

-- Sort in ascending order of inventory
SELECT * FROM product ORDER BY stock ASC;

-- Query the product information of the mobile phone contained in the name. Sort by amount in descending order
SELECT * FROM product WHERE NAME LIKE '%mobile phone%' ORDER BY price DESC;

-- Sort by amount in ascending order. If the amount is the same, sort by inventory in descending order
SELECT * FROM product ORDER BY price ASC,stock DESC;

  • Grouping query

-- Standard grammar
SELECT Listing FROM Table name [WHERE condition] GROUP BY Group column names [HAVING Conditional filtering after grouping] [ORDER BY Sort column names sort by];

-- Group by brand and get the total amount of each group of goods
SELECT brand,SUM(price) FROM product GROUP BY brand;

-- Commodities with an amount greater than 4000 yuan shall be grouped according to brand,Get the total amount of each group of goods
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

-- For goods with an amount greater than 4000 yuan, group them according to the brand and obtain the total amount of each group of goods. Only those with a total amount greater than 7000 yuan are displayed
SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;

-- For goods with an amount greater than 4000 yuan, group them by brand and obtain the total amount of each group of goods. Only those with a total amount greater than 7000 yuan are displayed and arranged in descending order of the total amount
SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000 ORDER BY getSum DESC;
  • Paging query
-- Standard grammar
SELECT Listing FROM Table name [WHERE condition] GROUP BY Group column names [HAVING Conditional filtering after grouping] [ORDER BY Sort column names sort by] LIMIT Start indexing,Number of queries;
-- Formulas: start indexing = (Current page number-1) * Number of items displayed per page

-- Display 2 pieces of data per page
SELECT * FROM product LIMIT 0,2;  -- First page start index=(1-1) * 2
SELECT * FROM product LIMIT 2,2;  -- Start index on page 2=(2-1) * 2
SELECT * FROM product LIMIT 4,2;  -- Start index on page 3=(3-1) * 2
SELECT * FROM product LIMIT 6,2;  -- Start index on page 4=(4-1) * 2

constraint

1. Concept and classification of constraints

  • Concept of constraint
    • Limit the data in the table to ensure the correctness, effectiveness and integrity of the data!
  • Classification of constraints
constraintexplain
PRIMARY KEYPrimary key constraint
PRIMARY KEY AUTO_INCREMENTPrimary key, auto growth
UNIQUEUnique constraint
NOT NULLNon NULL constraint
FOREIGN KEYForeign key constraint
FOREIGN KEY ON UPDATE CASCADEForeign key cascade update
FOREIGN KEY ON DELETE CASCADEForeign key cascade deletion

2. Primary key constraint

  • Primary key constraint characteristics
    • The primary key constraint contains two functions: non empty and unique
    • Only one table can have a primary key column
    • Primary keys are generally used to uniquely identify data in a table
  • Add primary key constraint when creating table
-- Standard grammar
CREATE TABLE Table name(
	Column name data type PRIMARY KEY,
    Column name data type,
    ...
);

-- establish student surface
CREATE TABLE student(
	id INT PRIMARY KEY  -- to id Add primary key constraint
);

-- Add data
INSERT INTO student VALUES (1),(2);
-- The primary key is unique by default. If duplicate data is added, an error will be reported
INSERT INTO student VALUES (2);
-- The primary key is not empty by default and cannot be added null Data
INSERT INTO student VALUES (NULL);

-- query student surface
SELECT * FROM student;
-- query student Table details
DESC student;
  • Delete primary key
-- Standard grammar
ALTER TABLE Table name DROP PRIMARY KEY;

-- Delete primary key
ALTER TABLE student DROP PRIMARY KEY;
  • Add a primary key separately after creating a table
-- Standard grammar
ALTER TABLE Table name MODIFY Column name data type PRIMARY KEY;

-- Add primary key
ALTER TABLE student MODIFY id INT PRIMARY KEY;

3. Primary key auto growth constraint

  • Add primary key self increment constraint when creating a table
-- Standard grammar
CREATE TABLE Table name(
	Column name data type PRIMARY KEY AUTO_INCREMENT,
    Column name data type,
    ...
);

-- establish student2 surface
CREATE TABLE student2(
	id INT PRIMARY KEY AUTO_INCREMENT    -- to id Add primary key auto increment constraint
);

-- Add data
INSERT INTO student2 VALUES (1),(2);
-- add to null Value, it will increase automatically
INSERT INTO student2 VALUES (NULL),(NULL);

-- query student2 surface
SELECT * FROM student2;
-- student2 Table details
DESC student2;
  • Delete automatic growth
-- Standard grammar
ALTER TABLE Table name MODIFY Column name data type;

-- Delete automatic growth
ALTER TABLE student2 MODIFY id INT;
  • Add automatic growth separately after creating a table
-- Standard grammar
ALTER TABLE Table name MODIFY Column name data type AUTO_INCREMENT;

-- Add auto growth
ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

4. Unique constraints

  • Add unique constraint when creating table
-- Standard grammar
CREATE TABLE Table name(
	Column name data type UNIQUE,
    Column name data type,
    ...
);

-- establish student3 surface
CREATE TABLE student3(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tel VARCHAR(20) UNIQUE    -- to tel Add unique constraint to column
);

-- Add data
INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666');
-- If duplicate data is added, an error will be reported
INSERT INTO student3 VALUES (NULL,'18666666666');

-- query student3 data sheet
SELECT * FROM student3;
-- student3 Table details
DESC student3;
  • Delete unique constraint
-- Standard grammar
ALTER TABLE Table name DROP INDEX Listing;

-- Delete unique constraint
ALTER TABLE student3 DROP INDEX tel;
  • Add unique constraints separately after creating a table
-- Standard grammar
ALTER TABLE Table name MODIFY Column name data type UNIQUE;

-- Add unique constraint
ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

5. Non NULL constraint

  • Add non empty constraint when creating table
-- Standard grammar
CREATE TABLE Table name(
	Column name data type NOT NULL,
    Column name data type,
    ...
);

-- establish student4 surface
CREATE TABLE student4(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL    -- to name Add non empty constraint
);

-- Add data
INSERT INTO student4 VALUES (NULL,'Zhang San'),(NULL,'Li Si');
-- add to null Value, an error will be reported
INSERT INTO student4 VALUES (NULL,NULL);
  • Delete non empty constraints
-- Standard grammar
ALTER TABLE Table name MODIFY Column name data type;

-- Delete non empty constraints
ALTER TABLE student4 MODIFY NAME VARCHAR(20);

  • Add non empty constraints separately after creating a table

    -- Standard grammar
ALTER TABLE Table name MODIFY Column name data type NOT NULL;

-- Add non empty constraint
ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;

Keywords: Database MySQL

Added by T_Hayden on Thu, 10 Feb 2022 18:20:03 +0200

Popular Keywords

©2024 Programming VIP