Mac terminal operation MySQL, and MySQL operation

Start the MySQL application manually first

Open terminal to enter the following command:/usr/local/mysql/bin/mysql-u root-p

(Note: Under Windows: Mysql-u root-p)

Where Root is the user name. The following command appears: Enter password:123456

This will allow you to access your database server.

Database operations

Here are some simple ways to create from a database to use;

Create a name for the MyDatabase database: Create databases mydatabase;

You can use the following command to see if the database you created was successful: show databases;

Change database name: alter databases hDataBase;

Change the character set of the database mydatabase: alter databases MyDatabase charset GBK;

Access database: Use mydatabase;

Use the following command to view the tables in the database: show tables;

Table Operations

Use the following command to create the table: Creation tableStudent (

Name varchar (10),

Gender varchar (10),

Sno Int primary KEY (ID)

) CharSet UTF8;

Use the following command to check whether the table was created successfully: show tables;

View the table structure, information for the fields in the table: desc table name;       describe table name; Show columns from table name;

Modify table name: The rename command is used to modify the table name. Rename command format: Rename table name to the new table name;

ALTER TABLE old table name rename new table name; ALTER TABLE student rename My_student;

Modify fields, Field operations Many: new (add), modify (Modify), duplicate name (change), delete (drop)

--Add the ID of the student table to the first position. ALTER TABLE name add field name data type [location];

ALTER TABLE my_student add ID int first;

---Change the number field in the student table to a fixed length and place it in the second place. ALTER TABLE name modify field name data type [position];

ALTER TABLE my_student Modify number char (ten) after ID;

---Modify the gender field in the student table to be sex.   ALTER TABLE name change old field name new field name data type; ALTER TABLE my_student change gender sex varchar (10);

---Delete the fields in the Student table age. ALTER TABLE name drop field name;

ALTER TABLE my_student drop age;    

Delete data table: drop table table name 1, table Name 2, ...; Delete multiple tables at once

Data Manipulation

---new data (insert full table field data) insert into my_student values (' Jim ', ' female ', 1106101);

---New data (specify field list) insert into my_student (number,sex) VALUES (1106, ' Male ');

---View all data select *

From table name

[Where condition];

---Update data alter is the operational database, table, field. Update updates data

--Update the gender of the name Jim. Update table name Set field = value [where condition];

Update my_student Set sex = ' female ' WHERE name = ' Jim ';

---delete data

--Delete from table name of gender male in table [where condition];

Delete from my_student where sex = ' male ';

The essence of Chinese data problem is character set problem, set names GBK; Be sure to set character sets.

FOREIGN key: If a table has one key segment (non-primary key) pointing to the other table's primary key, the field is called a foreign key.

Primary key issues

After the primary key is directly behind the field, you can have more than one field as the primary key, such that the primary key is called the composite primary key.

No primary key, append primary key ALTER TABLE My_database Modify Course char (TEN) primary key;

No primary key, append compound primary key ALTER TABLE My_student add primary key (SNO,CNO);

Update primary KEY & Delete primary key ALTER TABLE name drop PRIMARY key;

Paradigm: The ultimate goal in order to reduce the redundancy of data, all the data found through the relationship, resolutely no longer repeat storage.

First paradigm: Data is atomic when it is stored in a design table.

such as the table (name, gender, teaching time (start, end)) The teaching time of this watch can still be divided, not atomic.

Workaround (name, gender, start, end)

Second paradigm: The partial dependency of the table design is resolved, and part of the dependency is the existence of a field in the primary key of the field (the part of the primary key). As long as there is no composite primary key, the design of the table must meet 2NF

such as the table (instructor, gender, class, classroom, with class time, start time, end time) because the instructor can not be a primary key, need to combine the instructor class to be the main key (a teacher in a class with only one stage of the class, gender does not depend on the class, only rely on the lecturer; classrooms do not rely on lecturers, So there's a part of the gender and classroom dependency primary key)

Solution: You can remove the composite primary key by separate the gender and the instructor into a table, and the class and classroom as separate tables.

Some people also think that you can use logical primary keys, such as increasing the IDP.  Is that really okay? No, as follows, there will be transitive dependencies.

The third paradigm: Theoretically, all the fields in a table should be directly dependent on the primary key, if there is a field in the table design, does not directly depend on the primary key, but through a non-primary key field dependency, the final implementation depends on the primary key, such as the table has (primary key IDP, instructor, gender, class, classroom, with class time End time) gender is dependent on the instructor, the instructor relies on the primary key IDP, the classroom depends on the class, and the class relies on the primary key IDP. Gender and teachers all have transitive dependencies.

Solution: The fields that exist the transitive dependencies, and the dependent fields themselves, are taken out separately, forming a separate table, and then using the corresponding entity table's primary key when the corresponding information is needed. One sentence is that the entity builds the table separately.

Query data complete syntax

Select Field Name/*

From data source

[WHERE Condition clause]

[GROUP BY clause]

[HAVING clause]

[ORDER BY clause]

[LIMIT clause];

SELECT * from My_student;

---to heavy

SELECT DISTINCT * from My_student;

--Inserting data

INSERT into my_student values (null, ' itcast01 ', ' Zhang San ', ' Male '), (null, ' itcast02 ', ' John Doe ', ' Male '), (null, ' itcast03 ', ' Harry ', ' Female '), ( NULL, ' itcast03 ', ' Male ');

--Field aliases

Select Id,number as school number, name as name, sex gender

From My_student;

WHERE clause

Principle: Where is the only time to get the data directly from the disk when you start to judge the condition: Take a record from the disk, start to make where to judge, the results of the judgment if set to save to memory, and then give up.

---to find students with student ID 1,3,5

SELECT * from my_student where id = 1 | | id = 3 | | id = 5;

or select * from my_student where ID in (1,3,5);

Group BY field [Asc/desc]; Groups are grouped according to a field, with the same set of groups and different groups. Statistics are data, not records

--Grouped by sex

SELECT * from My_student GROUP by sex; This is an absolute error, grouping is for statistical purposes, data statistics by Group field

SQL provides a series of statistical functions

Count (); Count the number of records after grouping, and how many rows are recorded for each of the same groups

Max (); Count the maximum values per group

Min (); Count the minimum values for each group

AVG (); To count the average of each group

Sum (); Statistics for each group and

---height, average age, and total age

Select Sex,count (*), max (height), min (height), avg (age), Sun (age) from my_student Group by sex;

Note that using GROUP by does not use statistical functions, it doesn't make sense!

Multi-field grouping: First group by one field, then group the result again by another field.

HAVING clause

The same as WHERE clause to judge the condition, think: Why is group by......having ... Instead of group By......where???

Data statistics, is the data into memory, will be grouped-"statistics-" have the condition to judge. And where is to read the data from the disk to judge, at the moment is not grouped, statistics are judged. So wrong!!!

---Find out the number of students in all classes greater than or equal to 2

ORDER BY clause

Order by sorts a field in ascending or descending order, depending on the proofing set

Basic syntax: ORDER BY field name [Asc/desc]; ASC Ascending does not write desc descending

The limit clause is a statement that restricts the result

1) to limit the quantity

--Query the top two students select * from My_student limit 2;

2) limit start position of the limit start position, length

--Search for two students starting from number 4

SELECT * from My_student limit 4, 2;

Inner connection, outer connection, natural connection, cross connection

Inner joins, taking each record from the left table, and matching all the records in the right table, the match must be the same as a condition in the right table in the left table and will eventually retain the result, otherwise it is not preserved.

Basic syntax: Left table [inner] join right table on left table. field = Right table. field;

SELECT * FROM My_student inner joins My_class on My_student. c_id = mu_class.id;

Left outer connection: the left table is the main, then each record and the right table to connect, regardless of can match, the left table will be preserved. Can match, the right table a record is reserved, cannot match, a record is set to NULL, the final record number is at least not less than the left table already have records.

Basic syntax: The left table is the right table on the left side of the table. field = Right table. field;

A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
Relationship between a.ID and parent_id

--------------------------------------------------
1) Internal connection
Select a.*,b.* from a inner join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2

2) Left Connection
Select a.*,b.* from a LEFT join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null

 3)   right connection   
  Select   a.*,b.*   from   a   right   join   B & nbsp   on   a.id=b.parent_id       
  results     
  1   3                   1         1   
  2   John Doe                  2         2   < br>  null                        3     &NBSP ;   4   
    
 4)   full connection    
  Select   a.*,b.*   from   A   full   join   B     on   a.id=b.parent_id   

The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null

Mac terminal operation MySQL, and MySQL operation