MYSQ Database related Operations

(i) Common SQL statements

1.SELECT user () get logged in

2.SELECT version () get MySQL release information

3.SELECT now () get the current time

4.SELECT database () Gets the name of the open databases

(ii) database-related operations

1. Create a database (name does not include special characters or MySQL keywords)

(1) CREATE {database| SCHEMA} db_name;

Example: CREATE DATABASE Dou

(2) CREATE {database| SCHEMA} db_name [If not EXISTS] Db_name detects whether the database name exists, does not exist, creates

such as: CREATE DATABASE if not EXISTS dou;

(3)　　CREATE DATABASE [IF not EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset; Specifying encoding at the same time as the database is created

such as: CREATE DATABASE if not EXISTS dou DEFAULT CHARACTER SET ' GBK ';

2. View all databases under the current server

SHOW databases| SCHEMAS; (note s)

3. View the details of the specified database

SHOW CREATE DATABASE Dou;

4. Modify the encoding of the specified database

ALTER DATABASE Dou DEFAULT CHARACTER SET ' UTF8 ';

5. Open the specified database

Use Dou

6. Get the currently open database

SELECT DATABASE () | SCHEMA ();(note parentheses)

7. Delete the specified database

DROP DATABASE Dou;

Drop DATABASE if EXIST dou; Delete if present

(iii) Data sheet related operations

1. Create a table

CREATE TABLE [IF not EXISTS] Tbl_name (
Field Name field type [integrity constraint],
Field Name field type [integrity constraint],
...
) engine= Storage Engine charset= encoding method;

Such as:

CREATE TABLE IF not EXISTS Dou (
ID INT PRIMARY KEY,
Username VARCHAR (20),
Password CHAR (32),
Email VARCHAR (50),
Age TINYINT,
Card CHAR (18),
Tel CHAR (11),
Salary FLOAT (8,2),
Married TINYINT (1),
Addr VARCHAR (100),
Sex ENUM (' male ', ' female ', ' confidential ')
) Engine=innodb Charset=utf8;

Integrity constraints:

①unsigned unsigned, no negative number, starting from 0

②not NULL non-null constraint, value cannot be null, can not be filled out, is blank

③default Default value If you do not assign a value to a field when you insert it

Example: Age TINYINT UNSIGNED DEFAULT 18

④primary Key Primary key, the value cannot be duplicated, a table can have only one primary key, the field cannot be empty;

But a table can have a composite primary key

Such as:

--Composite PRIMARY key
CREATE TABLE Test_primary_key (
ID INT UNSIGNED,
CourseID VARCHAR (20),
Username VARCHAR (20),
Email VARCHAR (50),
PRIMARY KEY (Id,courseid)
);

This will only make an error if the ID and CourseID are the same at the same time as the previous, if only one repetition can be added normally

⑤auto_increment autogrow, can only be used for numeric columns, and is used with the index, the default initial value is 1, each increase of 1

Such as:

CREATE TABLE Test_auto_increment (
ID INT UNSIGNED KEY auto_increment,
Username VARCHAR (20)
);

INSERT test_auto_increment (username) VALUES (' A ');
INSERT test_auto_increment (username) VALUES (' B ');

INSERT test_auto_increment (id,username) VALUES (NULL, ' E ');
INSERT test_auto_increment (id,username) VALUES (DEFAULT, ' F ');
INSERT test_auto_increment (id,username) VALUES (' G ');

INSERT test_auto_increment (username) VALUES (' C ');

At this time the last ID is 16

⑥unique KEY Uniqueness, same value cannot be duplicated, except NUL

⑦zerofill 0 Fill, when the data display length is not enough, you can use the effect of the previous 0 to fill the specified length, the field will be automatically added unsigned

Such as:

CREATE TABLE Zero (
ID INT (4) Zerofill
);

INSERT Zero VALUES (1);

Automatic front 0 or 0001

⑧foreign key FOREIGN KEY constraint

2. View existing data tables under the current database

SHOW TABLES;

3. View the details of the specified data table

SHOW CREATE TABLE test_auto_increment

4. View table structure

desc| DESCRIBE test_auto_increment

SHOW COLUMNS from Test_auto_increment

5. Delete the specified data table

DROP TABLE [IF EXISTS] Test_auto_increment

(iv) Table structure-related operations

① Adding fields

ALTER TABLE Tbl_name
ADD Field Name field property [integrity constraint] [first| After field name]

Such as:

--Add Mailbox field email VARCHAR (+) not NULL UNIQUE, and add email to username back
ALTER TABLE User1
ADD Email VARCHAR (+) not NULL UNIQUE after username;

② Delete a field

--Delete the test field
ALTER TABLE User1
DROP username;

③ modifying field types, field properties

ALTER TABLE Tbl_name
MODIFY field Name field type [Field Properties] [First | After field name]

Such as:

-Change the email field to varchar (.) Not NULL first

ALTER TABLE User1
MODIFY Email VARCHAR (+) not NULL first;

④ modifying field names, field types, field properties

ALTER TABLE Tbl_name
Change original field name new Field Name field Type field Property [First | After field name]

Such as:

--Change the password name to PWD
ALTER TABLE User1
Change Password pwd CHAR (+) not NULL;

⑤ Adding a primary key

ALTER TABLE Tbl_name
ADD PRIMARY KEY (field name)

⑥ Add Unique

ALTER TABLE Tbl_name
ADD UNIQUE KEY (field name)

⑦ Delete Unique

ALTER TABLE Tbl_name
DROP (field name)

⑧ Modifying a data table name

ALTER TABLE Tbl_name

RENAME New_tbl_name

or directly RENAME TABLE tbl_name to New_tbl_name

MYSQ Database related Operations