Mac_MySQL installation, mysql

Mac_MySQL installation, mysql

1. Download and install

Go to the MySQL official website at http://dev.mysql.com/downloads/mysql/, and download about 164 MB of Community Server.

On the following page, select No thanks, just start my download]

The downloaded file is: mysql-5.6.20-osx10.8-x86_64.dmg

1. Click to install the pkg file in the installation package.

After installation, a MySQL folder is generated in usr/local/Under the root directory.

After the installation, the system preference settings, such as the following, will appear:

3. Click this icon to pop up the dialog box.

Click Start MySQL Server to Start mysql

2. Open the terminal

Enter at the terminal:

sudo chmod +w bashrcsudo vi /etc/bashrc

Add the following two command aliases at the end of bashrc to facilitate quick use of mysql

#mysqlalias mysql='/usr/local/mysql/bin/mysql'alias mysqladmin='/usr/local/mysql/bin/mysqladmin'

Tip: after adding the command alias to bashrc, you must restart the terminal to use the Quick Command.

3. Set the password of the mysql root Account

mysqladmin -u root password root

2. If you need to modify the password, run the following command:

Mysqladmin-u root-p password latest password

Enter the password. Enter the old password and press Enter.

4. Connect to the database

mysql -u root -p

Then, enter the password and the initial password set in step 3.

2. If you log on to the mysql database on the remote host

Mysql-h host address-u user name-p User Password

5. Configure PHPAdmin(Not required)

1. Download PHPAdmin and decompress it ~ /Sites directory, and rename the directory to phpmyadmin;

2. Enter:

http://localhost/~apple/phpmyadmin/setup/

Add a server configuration.

6. Set the default Character Set of the database(Required)

Enter at the terminal:

mysql -u root -p root# Create a database named mydbcreate database mydb;# Set the default Character Set of mydb to utf8alter database mydb default characterset = utf8;

Note: The default Character Set of mysql is latin1, which does not support Chinese characters. You need to set it.

5. Perform common mysql database operations

First, use root permission to connect to mysql

mysql -u root -proot

Then, enter the root password.

1,Authorize a new user

The format is as follows:

Grant operation permission on database. * to username @ login host address identified by 'Password ';

Grant a user (with the user's login password) on a host the permission to perform certain operations on a database.

(1) For example, on any host ("%"), the user (username: test1, password: adc) has the permission to perform any operation on all databases.(Dangerous)

grant all privileges on *.* to test1@"%" identified by "abc";

All privileges indicates the query, insert, modify, and delete permissions: select, insert, update, and delete.

The preceding commands are equivalent:

grant select,insert,update,delete on *.* to test1@"%" identified by "abc";

Refresh permission

flush privileges;

(2) For example, authorize the user on the local host to operate on the database

Create a database (for example, openfire)

create database openfire;

Grant the operation permission of the local host user (username: test2, password: 123) to access the database (Database Name: openfire)

grant all privileges on openfire.* to test2@localhost identified by "123";

flush privileges;

Then, you can use a new user to access the openfire database.

2. Update the password of the specified account (username: test1, new password: 1234)

update mysql.user set password=password('1234') where User="test1" and Host="localhost";

3. delete a user

Use the mysql database first

use mysql;

Delete a local user (test7) from the user table in the mysql database)

delete from user where User="test7" and Host="localhost";

4. Display commands

(1) display the list of all databases

show databases;

Only two databases are initialized, mysql and test.

Note: MYSQL system information is stored in the mysql database. For example, you can use this database to modify the password and add new users.

(2) open a database (such as the database: openfire)

use openfire;

(3) display all tables in this database

show tables;

(4) display the structure of a table (table1)

describe table1;

(5) database creation

Create database name;

(6) create a table

Use Database Name; create table Name (field setting list );

(7) delete a database

Drop database name;

(8) delete a table

Drop table name;

(9) clear records in the table

Delete from table name;

(10) display records in the table

Select * from table name;

6. log out of mysql

exit

7. start, stop, and restart MySQL(In the bash command, the password is the system logon password)

Start Mysql Service

Sudo/Library/StartupItems/MySQLCOM start stop Mysql service sudo/Library/StartupItems/MySQLCOM stop restart Mysql service sudo/Library/StartupItems/MySQLCOM restart

8. Other common table operations

In bash, type mysql-uroot-p.
Press enter, enter the password, and then press Enter.
Mysql-uroot-proot

Temporary change of window character encoding: input and Result
Set character_set_client = gb2312;
Set character_set_results = gb2312;

User Library:
Use beyond;

Table query:
Show tables;




Mysql> \ s


========================================================== ====================
Create a database using the specified utf8 File
Mysql> create database mydb2 character set utf8;
Mysql> create database mydb3 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.03 sec)


Mysql> drop database beyond;
Mysql> show character set;
+ ---------- + ---------------- + -------------- + --------------- +
| Charset | Description | Default collation | Maxlen |
+ ---------- + ---------------- + --------------------- + -------- +
Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

Mysql> show create database mydb2;
Create database 'mydbdb '/*! 40100 default character set utf8 */
Mysql> create database mydb3 character set utf8 collate utf8_general_ci;
> Alter database mydb2 character set gb2312;
> Show create database mydb2;

Demo recovery and backup
Create database mydb4;
Use mydb4;
Create table user
(
Name varchar (20)
);
Insert into user (name) values ('beyond ');
Select * from user;
----- Data in the user table is displayed.
Mysql administrator username and password: root


Create a database named mydb1
Create database mydb1;
Show databases;


Create a mydb2 database using the UTF-8 character set.
Create database mydb2 character set utf8;


Create a mydb3 database that uses the UTF-8 character set and has verification rules.
Create database mydb3 character set utf8 collate utf8_general_ci;


View the definition information of the mydb2 database created earlier
Show create database mydb2;


Delete the mydb1 database created earlier
Drop database mydb1;


View the database on the server and change the character set of a database to gb2312;
Alter database mydb2 character set gb2312;
Show create database mydb2;


Demo recovery and backup
Create database mydb4;
Use mydb4;
Create table user
(
Name varchar (20)
);
Insert into user (name) values ('beyond ');
Select * from user;
----- Data in the user table is displayed.


Back up mydb4, start a bash command line window, and execute the following command
Mysqldump-uroot-p tt> c: \ tt. SQL






Demo recovery
1. Delete the database first
Drop database tt;


2. Restore the tt Library (1)
2.1 to restore the database, create database tt first;
2.2 restore the tt Database
Use tt;
Source c: \ tt. SQL (source: You can execute an SQL script)



3. Restore the tt Library (2)
2.1 to restore the database, create database tt first;
2.2 Restore database mysql-uroot-proot tt <c: \ 1. SQL; (window command)
 


Create an employee table
Use mydb2;
Create table employee
(
Id int,
Name varchar (40 ),
Sex varchar (4 ),
Birthday date,
Entry_date date,
Job varchar (40 ),
Salary decimal (8, 2 ),
Resume text
);


Show tables; view all tables in the database
Show create table employee; view table creation details
Desc employee; view the table structure




Add an image column to the employee table above.
Alter table employee add image blob;


Modify the job column to 60 in length.
Alter table employee modify job varchar (60 );


Delete sex columns
Alter table employee drop sex;


Change the table name to user.
Rename table employee to user;


Modify the table character set to UTF-8
Alter table user character set utf8;


Change column name to username
Alter table user change column name username varchar (40 );


Delete table
Drop table user;




Use the insert statement to insert information about three employees into the table.
Rename table user to employee;
Insert into employee (id, username, birthday, entry_date, job, salary, resume) values (1, 'aaa', '2017-09-09 ', '2017-09-09 ', 'bbb ', 90, 'aaaaa ');
Select * from employee;


Insert data details 1
Insert into employee values (1, 'aaa', '2017-09-09 ', '2017-09-09', 'bbb ', 90, 'aaaaa ');


Insert data details 2
Insert into employee values ('1', 'aaa', '2017-09-09 ', '2017-09-09', 'bbb ', '90', 'aaaaa ');


Insert data details 3 (insert Chinese)
Tell mysql users to use gb2312 Encoding
Show variables like 'chara % ';
Set character_set_client = gb2312;
Insert into employee (id, username) values ('3', 'zhang san ');

No garbled characters are required for viewing
Show variables like 'chara % ';
Set character_set_results = gb2312;
Select * from employee;


Change the salary of all employees to 5000 yuan.
Update maid set salary = 5000;


Change the salary of an employee whose name is 'bbb' to 3000 yuan.
Update employee set salary = 3000 where username = 'bbb ';


Change the salary of an employee whose name is 'bbb to 4000 yuan, and change job to ccc.
Update employee set salary = 4000, job = 'ccc 'where username = 'bbb ';


Increase the salary of bbb by RMB 1000 based on the original salary.
Update employee set salary = salary + 1000 where username = 'bbb ';


Notes for updates
Update employee set username = 'ccc ', salary = 9000, birthday = '2017-09-09 ',.....................
Update where id = 1;




Delete the record named 'zs' in the table.
Delete from employee where username = 'bbb ';


Delete all records in the table.
Delete from employee;


Use truncate to delete records in a table.
Truncate table employee;




Query the information of all students in the table.
Select * from student;


Query the names and English scores of all students in the table.
Select name, english from student;


Filter duplicate English data in the table.
Select distinct english from student;


Add 10 points of expertise to the total score of all students.
Select name, (chinese + english + math) + 10 from student;


Calculate the total score of each student.
Select name, (chinese + english + math) from student;


Use aliases to indicate student scores.
Select name as name, (chinese + english + math) + 10 as total score from student;
Select name, (chinese + english + math) + 10 Total from student;


Query the scores of students whose names are wu.
Select * from student where name = 'wang wu ';


Query students whose English score is greater than 90
Select * from student where english> '90 ';


Query all students whose total score is greater than 200
Select name from student where (chinese + english + math)> 200;


Query the students whose English scores are between 80 and 90.
Select name from student where english> 80 and english <90;
Select name from student where english between 80 and 90; = select name from student where english> = 80 and english <= 90;


Students whose mathematical scores are, 90, and 91 are queried.
Select * from student where math in (89,90, 91 );


Query the scores of all students surnamed Li.
Select * from student where name like 'Li % ';
Select * from student where name like 'Lee _';




The number of students with a query score greater than 80 in mathematics and a Chinese score greater than 80.
Select * from student where math> 80 and chinese> 80;


Sorts mathematical scores and outputs them.
Select name, math from student order by math;


Output after sorting the total score, and then output in the order from high to low
Select name, (chinese + english + math) total score from student order by (chinese + english + math) desc;
Select name, (chinese + english + math) total score from student order by total score desc;


Sort and output the scores of students surnamed Li
Select * from student where name like 'Lee % 'order by (chinese + english + math) desc;


How many students are counted in a class?
Select count (name) from student;
Select count (*) from student;


How many students have scored more than 90 in mathematics?
Select count (*) from student where math> 80;


How many people are there with a total statistical score greater than 250?
Select count (*) from student where (chinese + english + math)> 250;


Details about the count function (count only contains rows with values)




Count the total score of a class's mathematics?
Select sum (math) from student;


Measure the total scores of each class in Chinese, English, and mathematics.
Select sum (chinese), sum (english), sum (math) from student;


Total scores of Chinese, English, and mathematics in a class
Select sum (chinese + english + math) from student;


Average score of each class
Select sum (chinese)/count (*) from student;


Average score of each class
Select avg (chinese) from student;


Calculate the average score of a class total score
Select avg (chinese + math + english) from student;


Calculate the highest score and lowest score of the class.
Select max (chinese + math + english), min (chinese + math + english) from student;


After the items in the order table are classified, the total price of each category of items is displayed.
Select product, sum (price) from orders group by product;


Query the purchased items with a total price of more than 100.
Select product from orders group by product having sum (price)> 100;




Define primary key constraints (each table must have a primary key column)
Create table student
(
Id int primary key,
Name varchar (40)
);


Define automatic growth of primary keys
Create table student
(
Id int primary key auto_increment,
Name varchar (40)
);


Define unique constraints
Drop table student;
Create table student
(
Id int primary key auto_increment,
Name varchar (40) unique
);


Define non-empty Constraints
Drop table student;
Create table student
(
Id int primary key auto_increment,
Name varchar (40) unique not null
);


Define foreign key constraints
Create table husband
(
Id int primary key,
Name varchar (40)
);


Create table wife
(
Id int primary key,
Name varchar (40 ),
Husband_id int,
Constraint husband_id_FK foreign key (husband_id) references husband (id)
);




Table design scheme when one-to-many or multiple-to-one objects are stored in the database
Departments and employees
Create table department
(
Id int primary key,
Name varchar (40)
);


Create table employee
(
Id int primary key,
Name varchar (40 ),
Salary decimal (8, 2 ),
Department_id int,
Constraint department_id_FK foreign key (department_id) references department (id)
);




Multi-to-multi-object table design (teachers and students)
Create table teacher
(
Id int primary key,
Name varchar (40 ),
Salary decimal (8, 2)
);


Create table student
(
Id int primary key,
Name varchar (40)
);


Create table teacher_student
(
Teacher_id int,
Student_id int,
Primary key (teacher_id, student_id ),
Constraint teacher_id_FK foreign key (teacher_id) references teacher (id ),
Constraint student_id_FK foreign key (student_id) references student (id)
);






One-to-one object database design
Create table person
(
Id int primary key,
Name varchar (40)
);


Create table idcard
(
Id int primary key,
City varchar (40 ),
Constraint id_FK foreign key (id) references person (id)
);




Self-join table
Create table person
(
Id int primary key,
Name varchar (40 ),
Parent_id int,
Constraint parent_id_FK foreign key (parent_id) references person (id)
);

How to install mysql on a mac Machine

Install an mamp pro Kit.
 
How to install mysql on mac

I went there and went directly to the next dmg on the official website. It is best not to go to version 10.5 for version 10.6 x64.