Summary of the use of MySQL tools (Mac Version)

13.mysql Mac terminal operation

12.MYSQL on the MAC installation and configuration of the detailed;

11.mac under Install mysql5.7.18, connection appears access denied for user ' root ' @ ' localhost ' (using Password:yes)

=======

13.mysql Mac terminal operation

1. Start mysql:brew services start MySQL

2. Login Mysql:mysql-u root-p

MySQL command. -U followed by user name

Root Super Administrator, with the highest privileges.

-P followed by password

3. Quit Mysql:brew Services stop MySQL

4. Open Mysql:brew Services start MySQL

5. See what databases are on the database server

SHOW DATABASES; (can be uppercase and lowercase, rule is upper case)

6. Naming rules for databases

Keyword and function name all uppercase

Database name, table name, field name all lowercase

The SQL statement must end with a semicolon

7.MySQ exit

A.mysql >exit;

B.mysql >quit;

C.mysql > \q;

8. Remote server address notation

Mysql-u root-p-h127.0.0.0

9. Log on to the specified database on the server

Mysql-u root-p-h127.0.0.0-dj1702

10. Show all tables in the current database

Show tables;

11. Select the specified database in the database's server

Use MySQL;

12. Modify the Prompt

Mysql-u root-p--prompt \\h

localhost prompt mysql>

13. Select (query) What you want to display

Mysql>select version ();//version () indicates the revision number method

14. Time Method

Mysql>select now ();

15.user () User method

Mysql>select user ();

16. Create a Folder

Mysql>create database j1702 Character set ' GBK ';

17. Modify the Folder

Mysql>alter database j1702 character set=ut8;

18. Deleting a database

Drop database j1702;

19. Create a data table in the specified folder

Mysql> CREATE TABLE Tb_student (

->id INT,

->name CHAR (20),

->weight FLOAT

);

20. Display the data sheet in the database

Mysql> Show tables;

21. Two kinds of display table structure

A.desc tb_student;

B.show columns from tb_student;

22. Create a Constraint data table

Mysql> CREATE TABLE TB2 (

->id int UNSIGNED PRIMARY key,//unsigned unsigned integer PRIMARY key PRIMARY KEY constraint

->name CHAR (a) not null,//not null non-null constraint

->weight FLOAT (5,2)

);

23. Delete Data Sheet

Mysql>drop table TB2;

24. Insert Data Sheet

mysql > Insert tb2 VALUES (1001, ' Zhang San ', 50.02);

25. Display the creation process

Mysql> Show CREATE TABLE TB2;

26. Differences between table-level constraints and column-level constraints

A. The constraint created for a column becomes a column-level constraint.

B. For constraints created by two or more columns, we call table-level constraints.

C. column-level constraints when used, can be declared either at the time of the column definition or later in the column definition.

D. Table-level constraints can only be declared later in the column definition;

E. In all constraints, it is not said that each constraint has table-level or column-level constraints, they have column-level constraints, and for the other 3 kinds, such as primary key, unique, foreign keys they can have table-level constraints and column-level constraints.

27. Displaying Data structures

Mysql>\s

28. Definition of PRIMARY key

The primary key (primary key, primary key) is a candidate keyword that is selected to make a unique identifier for the row of the table. A table has only one keyword. The primary key is called the primary key.

A primary key can consist of multiple fields, called a single-segment primary key or a multi-word read primary key. It is called the main code. And it can uniquely determine a row of data in a table, or you can uniquely identify an entity.

29. Create a UNIQUE Constraint data table (parameter self-growth)

Mysql>create Table Tb3 (

->id INT UNSIGNED PRIMARY KEY auto_increment,//increment self-growth

->name CHAR (a) not NULL unique,//unique UNIQUE constraint

->weight FLOAT (5,2) default 50.3//default set defaults

);

30. Invalid null value for UNIQUE constraint

31. Add the Name field to the data table (name)

ALTER table TB5 Add name VARCHAR (a) unique;

32. Add a sex field to the data table (NAEM) before

ALTER table tb5 Add sex enum (' 1 ', ' 2 ') after Weight;//enum enumeration

32. Delete the specified column in the data table

ALTER table tb5 drop sex;

33. Add the class field to the front of the datasheet

ALTER table TB5 Add class int first;

34. Add the province and city Fields first in the datasheet

ALTER Table Tb5 Add (province char (), City char (20));

35. Add a PRIMARY KEY constraint

ALTER table Tb6 Add PRIMARY KEY (ID));

36. Add a UNIQUE constraint

ALTER table Tb6 Add UNIQUE (name);

37. Deleting multiple fields at the same time is not supported

ALTER table Tb5 Drop (province,city);

38. Display index-related information

Mysql>show index from Tb6\g;

39. Two ways to remove constraints

A.mysql >alter TABLE tb7 drop index name;

B.MySQL >alter TABLE tb7 drop key name;

12.MYSQL on the MAC installation and configuration of the detailed;

First, download the installation

Download the community version of DMG installation file: https://dev.mysql.com/downloads/mysql/

1, execute the installation files, follow the steps to complete the installation.

2, after the completion of installation terminal input:

?

1	mysql --version;

----Show the version number is normal, if the command not found, in the terminal input as follows, "/usr/local/mysql/bin/mysql" is the MySQL default installation path:

?

12	$ cd /usr/local/bin/$ sudo ln -fs /usr/local/mysql/bin/mysql mysql

3. Turn off the MySQL service:

?

1	sudo /usr/local/mysql/support-files/mysql.server stop

Second, the environment variable configuration

1, open the terminal, enter:

CD ~

Will enter the ~ folder

2. Then enter:

?

1	touch .bash_profile

After the carriage return is executed,

3. Re-enter:

?

1	open-e .bash_profile

This file is opened in TextEdit (this should be a blank document if the environment variable has not been previously configured). If you have content, enter it before the Terminator, and if there is no content, enter the following statement directly:

?

1	export PATH=${PATH}:/usr/local/mysql/bin

Then, save, Exit TextEdit (must be exited), close the terminal and exit.

Third, forget the root password reset

1. Turn off the MySQL service (click Stop MySQL server), System Preferences, Apple

2. Enter the terminal input:

?

1	cd /usr/local/mysql/bin/

Login Administrator rights after enter

?

1

sudo su

Enter the following command after carriage return to disable the MySQL authentication feature

?

1	./mysqld_safe --skip-grant-tables &

MySQL will restart automatically after carriage return (the MySQL status in preferences will become running)

3. Input command

./mysql

Enter the command after the carriage return

?

1	FLUSH PRIVILEGES;

Enter the command after the carriage return

?

1	SETPASSWORD FOR ‘root‘@‘localhost‘= PASSWORD(‘123‘);

At this end of the modification, after restarting the terminal, enter Mysql-u root-p return and type the password: 123 starts successfully.

Iv. deletion

Stop the MySQL process and console enter the command.

?

123456789

sudo rm/usr/ local / MySQL sudo rm-rf/usr/ local /mysql* sudo rm-rf/library/startupitems/mysqlcom sudo rm-rf/library/preferencepanes/my* vim/etc/hostconfig ( and Removed the line mysqlcom=-yes-) rm-rf ~/library/preferencepanes/my* sudo rm-rf/library/receipts/mysql* sudo rm-rf/library/receipts/ mysql* sudo rm-rf/var/db/receipts/com.mysql.*

The above is a small part of the introduction of MySQL on the MAC installation and configuration of the detailed, I hope that we have some help, if you have any questions please give me a message, small series will promptly reply to you. Thank you very much for the support of the Scripting House website!

11.mac under Install mysql5.7.18, connection appears access denied for user ' root ' @ ' localhost ' (using Password:yes)

mysql5.7.18 connection error under Mac, error message: Access denied for user ' root ' @ ' localhost ' (using Password:yes)

() inside the command entered for the shell, be sure to lose all including;& and other symbols

First step: Apple---system Preferences--bottom point MySQL, turn off MySQL service

Step Two: Enter terminal input (cd/usr/local/mysql/bin/) carriage return

Enter (sudo su) carriage return for administrator privileges

Input (./mysqld_safe--skip-grant-tables &) Enter to disable MySQL authentication, MySQL will restart automatically and MySQL status in preferences will change to running

Step three: Enter the command (./mysql) Carriage return

Input command (flush privileges;) semicolon don't forget to lose.

Enter the command (set password for ' root ' @ ' localhost ' = password (' root '), the root in password (' root ') is the new password, set it freely, semicolon do not forget to enter

At this point, the password changes successfully, you can log in normally.

Summary of the use of MySQL tools (Mac Version)