MySQL keyword is case-insensitive, BUT
MySQL databases, tables, and columns names IS case sensitive
Login to MySQL
mysql -h localhost -u root -p
-h &＃43; 主机地址;
-u &＃43; Username;
-p: prompt for password;
-p &＃43; Password (明文输入无引号无空格);
Clear terminal
\c //不加; return you to the mysql> prompt.
Linear display (instead of tabular display)
\G // 放在statement末尾
Create Users
create user &＃39;username&＃39;&＃64;&＃39;hostname&＃39; identified by &＃39;password&＃39;; // 创建名为 &＃39;username&＃39; 的用户&＃xff0c;并设置密码为 &＃39;password&＃39;&＃xff0c;须加引号
grant all on *.* to &＃39;username&＃39;&＃64;&＃39;hostname&＃39;; // grant 所有权限 on 所有 database&＃xff0c;须加引号
List all Users
select user from mysql.user;
Create a new DB
CREATE DATABASE dbname; // OR
CREATE SCHEMA dbname; // OR
CREATE DATABASE IF NOT EXISTS dbname;
Adding options when creating DB
CREATE DATABASE db_name
CHARACTER SET latin1 //Set the default characters to be Latin letters
COLLATE latin1_bin; //Set sorting in tables to be based on binary Latin characters
It is optional to add options when creating DB
Delete a DB
Create a new table
create table database.table (col_1 type, col_2 type, ...);
column definition
name type [NOT NULL | NULL] [DEFAULT value]
- name: column name
- type: how and what is stored in the column
- null/not null: whether the column can be empty
- default : set default value
Adding options when creating tables
create table database.table (col_1 type PRIMARY KEY,col_2 type AUTO_INCREMENT, ...)
List all DB
- Use LIKE to add query conditions
- e.g. LIKE &＃39;%s&＃39; -- list db endiing with s
Default DB
- information_schema: stores information about the server
- mysql: stores usernames, passwords, and user privileges (Don’t try to change the mysql database directly)
- test: a database for testing (initially empty)
Set current/active database
USE dbname // This allows you to specify table name without preceding dbname.
Check which is the active database
SELECT DATABASE();
View all tables in active db
SHOW TABLES;
View table schema
DESCRIBE table_name; //OR
DESC table_name
Or alternatively --
SHOW COLUMNS FROM table_name;
- Field: the fields or columns of the table created.
- Type: the data type for each field.
- Null: whether each field may contain NULL values.
- Key: whether a field is a key field — an indexed column. (e.g. PRI &＃61; Primary key, UNI &＃61; Unique, 空白 &＃61; not a key field)
- Default: default value for a field.
- For time field: CURRENT_TIMESTAMP
- Extra: any extra information
Display all entries from a table
SELECT * FROM table_name;
Show db-creation/table-creation command
SHOW CREATE DATABASE [db_name];
SHOW CREATE TABLE [table_name];
京公网安备 11010802041100号