MySQL mac terminal operation

1. Start MySQL:brew services start MySQL

2. Login to MySQL: mysql-u root-p

MySQL command. -U followed by user name

Root Super Administrator, with the highest privileges.

-P followed by password

3. Exit 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 both uppercase and lowercase, the 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 method of the revision number

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 () 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) is not NULL UNIQUE, //unique Unique Constraint

->weight FLOAT (5,2) DEFAULT 50.3 //default Setting default values

);

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;

MySQL mac terminal operation