Operation of MySQL

Log in to MySQL mysql-h hostname-u username-p password

Start MySQL under Mac

Added to the. bash_profile file in the terminal's own user name.

Export path=/usr/local/mysql/support-files/: $PATH path

Then terminal input sudo mysql.server (start start restart restart stop stop)

\s Viewing database information

Create a database

Create database name with no character encoding;

Create database name with character encoding charset= ' UTF8 ';

View Database

show databases;

View a database information that has been created

Show create database name;

Modify Database Encoding

ALTER DATABASE name default character set encoding method collate encoding _bin;

Deleting a database

drop database name;

Log in to Database
Use database name;

-----------------------------------------

View columns in a table
SHOW COLUMNS from table name;
DESCRIBE table name;
DESC table name;

View tables in a database

Show tables;

Build table Syntax:
CREATE Table Table name (
Field 1 data type constraints,
Field 2 data type constraints,
...
field N Data type constraints
);

View information for a built table
Show create table table name;
Delete a table
drop table name;

Constraints:

Constraints on tables
Primary key, foreign key, non-null, unique, default

If the primary key is defined, it is also a bit unique and non-empty
Single Field primary key
Field name data type primary key
Multi-field Primary key
Field name data type (field name 1, field name 2, field name 3, field name 4, field name 5,.... Field name N)
Non-null constraint
Field name data type NOT NULL
Unique constraint
Field name data type unique;
Default Constraints
Field name data type default defaults;
Automatic growth of table fields
Field name data type auto_increment;
FOREIGN KEY constraints
FOREIGN KEY
Establish association relationships in two tables or tables to ensure data integrity
Note: 1. The value of the child table reference must be within the range of the value of the reference field of the primary table
2. If a value is referenced in the primary table, the corresponding record in the primary table cannot be deleted
3. The column of the child table's foreign key reference can only be the primary key column of the primary table or the column of a unique constraint

CREATE TABLE Class (
CID INT PRIMARY KEY auto_increment,
CNAME VARCHAR (20)

) Engine=innodb;

CREATE TABLE Student (
Sid INT PRIMARY KEY auto_increment,
Sname VARCHAR (20),
SCID INT,

--FOREIGN KEY (SCID) REFERENCES class (CID)

CONSTRAINT fk_scid FOREIGN Key (SCID) REFERENCES class (CID)

) Engine=innodb;

DROP TABLE student;
DROP TABLE class;

Syntax 1:foreign key (foreign key field) REFERENCES Main Table (primary key field);
Syntax 2:constraint foreign key name FOREIGN key (foreign key field) REFERENCES Main Table (primary key field);

(Foreign Key Name: Fk_ field name)

Operation after the table is built:
Modify Table Name:
ALTER TABLE old table name rename to new table name;
Modify table field names
ALTER TABLE name change old field name new data type for new field name;
Modify the data type of a field
ALTER TABLE name modify field name data type;
Add Field
ALTER TABLE name add new field name data type;-----constraint
Delete a field
ALTER TABLE name drop field name;
Modify the arrangement position of a field
ALTER TABLE name modify field name 1 data type first|after field Name 2;

Tabular

SELECT * from table name;

1.DELETE
Syntax: delete from table name [where condition]

Note: Deleting refers to deleting the whole piece of data
Delete deleted data is not completely deleted, preserving the structure of the table and data structure
If the condition is not specified when deleting, the entire table data is deleted

--removal of all students aged 18 years
DELETE from student where sage = 18;

--Delete all students who are 17 years of age and learn English software major
DELETE from student where sage = Szhuanye = ' English software ';
DELETE from student where sage = OR Szhuanye = ' English software ';

CREATE Table Test (
Tid int PRIMARY KEY auto_increment,
TTT VARCHAR (10)

);

Like '% character '% ' character% ' word '

INSERT into Test (TTT) VALUES ("a");
INSERT into Test (TTT) VALUES ("B");
INSERT into Test (TTT) VALUES ("C");

DELETE from Test WHERE tid = 5;

2.TRUNCATE
Syntax: TRUNCATE TABLE name;

Note: Truncate means that the entire contents are deleted, the table structure is preserved, and the data space is freed
TRUNCATE TABLE test;

3.drop
Syntax: drop table name;
Note: Use caution to remove the entire table

UPDATE

Syntax: Update table name set column 1 = value 1, column 2 = value 2 ... [WHERE condition]

Note: If the Where condition is not followed, all data in the table is modified

--The age of students in the student list is 20 years old.
UPDATE Student SET sage = 20;

--The name of the student who modified the number 5 was ' Lucy '
UPDATE Student Set sname = ' Lucy ' WHERE sid = 5;

--Revision of student number 2 for the age of 18 years, class ID changed to 1001
UPDATE Student SET Sage =18,cid = 1001 WHERE sid = 2;

INSERT

CREATE TABLE Student (
Sid INT PRIMARY KEY auto_increment,
Sname VARCHAR (10),
Sage INT

);

Add data without specifying a field, you must add data for each field in the table
Note: The order of values must match the order of the fields in the table

INSERT into table name values (value 1, value 2,... Value n);

INSERT into student VALUES (101, ' Lily ', 20);

Specify fields to add data
Note: When specifying a field, the field order must be the same as when the table was built
INSERT into student (sname,sage) VALUES (' Lucy ', 20);

DROP TABLE student;

CREATE TABLE Class (
CID INT PRIMARY KEY,
CNAME VARCHAR (10)

) Engine=innodb;

CREATE DATABASE TT Engine=innodb;

CREATE TABLE Student (
Sid INT PRIMARY KEY auto_increment,
Sname VARCHAR (10),
Sage Int,
CID INT,
FOREIGN KEY (CID) REFERENCES class (CID)

) Engine=innodb;

DROP TABLE student;
DROP table class;

INSERT into Class (Cid,cname) VALUES (1, "Class1");
INSERT into Class (Cid,cname) VALUES (2, "Class2");
INSERT into Class (Cid,cname) VALUES (1001, "CLASS3");

INSERT into student (sname,sage,cid) VALUES (' Lily ', 19,2);
INSERT into student (sname,sage,cid) VALUES ("Lucy", 20,10001);

CREATE TABLE TTT (
TTT int
);

Operation of MySQL