MySQL Basics

Connect to Database#

mysql -u user -p
// For example: mysql -u root -p

Common errors:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

Exit Connection#

QUIT or Ctrl + D

View Databases, Create Database, Use Database to View Databases#

show databases;

Default databases:

mysql - User privilege related data
test - For user test data
Information_schema - MySQL internal schema related data

Create Database:

create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; #utf8 encoding
create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #gbk encoding

Use Database:

use db1;

Show all tables in the current database:

SHOW TABLES;

User Management#

Create User:

create user 'username'@'IP address' identified by 'password';

Delete User:

drop user 'username'@'IP address';

Modify User:

rename user 'username'@'IP address'
to  'new_username'@'IP address';

Change Password:

set password for 'username'@'IP address' = Password('new_password');

Note: User privilege related data is stored in the user table of the mysql database, so it can also be operated directly (not recommended).

Permission Management#

MySQL has the following restrictions on permissions:

all privileges: All privileges except grant
select: Only select permission
select,insert: Select and insert permissions
...
usage: No access permission
alter: Use alter table
alter routine: Use alter procedure and drop procedure
create: Use create table
create routine: Use create procedure
create temporary tables: Use create temporary tables
create user: Use create user, drop user, rename user and revoke all privileges
create view: Use create view
delete: Use delete
drop: Use drop table
execute: Use call and stored procedures
file: Use select into outfile and load data infile
grant option: Use grant and revoke
index: Use index
insert: Use insert
lock tables: Use lock table
process: Use show full processlist
select: Use select
show databases: Use show databases
show view: Use show view
update: Use update
reload: Use flush
shutdown: Use mysqladmin shutdown (shut down MySQL)
super: Use change master, kill, logs, purge, master and set global. Also allows mysqladmin debug login
replication client: Access from server location
replication slave: Used by replication slave

For database and internal other permissions as follows:

database_name.* // All in the database
database_name.table // A specific table in the specified database
database_name.procedure // A specific stored procedure in the specified database
*.* // All databases

For user and IP permissions as follows:

username@IP address // User can only access under this IP
[email protected].% // User can only access under this IP segment (wildcard % means any)
username@% // User can access under any IP (default IP address is %)

View Permissions:

show grants for 'user'@'IP address'

Grant Permissions:

grant permission on database.table to 'user'@'IP address'

Revoke Permissions:

revoke permission on database.table from 'username'@'IP address'

Grant Examples:

grant all privileges on db1.tb1 TO 'username'@'IP'

grant select on db1.* TO 'username'@'IP'

grant select,insert on *.* TO 'username'@'IP'

revoke select on db1.tb1 from 'username'@'IP'

MySQL Table Operations#

View Tables:

show tables; // View all tables in the database
select * from table_name; // View all contents of the table

Create Table:

create table table_name(
    column_name type nullability,
    column_name type nullability
)ENGINE=InnoDB DEFAULT CHARSET=utf8

Example:

CREATE TABLE 'tab1'(
    'nid' int(11) NOT NULL auto_increment,
    'name' varchar(255) DEFAULT zhangyanlin,
    'email' varchar(255),
    PRIMARY KEY('nid')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note:

Default value: When creating a column, a default value can be specified. If no value is set during data insertion, the default value will be added.
Auto-increment: If a column is set as auto-increment, there is no need to set this column during data insertion; it will automatically increment (only one auto-increment column is allowed in a table). Note: 1. For auto-increment columns, they must be indexed (including primary keys). 2. The auto-increment can set step size and starting value.
Primary Key: A special unique index that cannot have duplicate values. If the primary key uses a single column, its value must be unique; if it is multiple columns, their combination must be unique.

Delete Table:

drop table table_name

Clear Table Contents:

delete from table_name
truncate table table_name

Modify Table:

Add Column:   

alter table table_name add column_name type

Delete Column:   

alter table table_name drop column column_name

Modify Column:
          
alter table table_name modify column column_name type;  -- type
alter table table_name change old_column_name new_column_name type; -- column name, type

Add Primary Key:
          
alter table table_name add primary key(column_name);

Delete Primary Key:
          
alter table table_name drop primary key;
alter table table_name modify column_name int, drop primary key;

Add Foreign Key: 

alter table child_table add constraint foreign_key_name (like: FK_child_table_parent_table) foreign key child_table(foreign_key_field) references parent_table(primary_key_field);

Delete Foreign Key: 

alter table table_name drop foreign key foreign_key_name

Modify Default Value:

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

Delete Default Value:

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

Basic Data Types#

MySQL data types are roughly divided into: Numeric, Time, and String

bit[(M)]
Binary bit (101001), m represents the length of the binary bit (1-64), default m=1

tinyint[(m)] [unsigned] [zerofill]
Small integer, data type used to save some range of integer values
Signed: -128 ~ 127
Unsigned: 0 ~ 255
Special: MySQL does not have a boolean value, using tinyint(1) instead.

int[(m)] [unsigned] [zerofill]
Integer, data type used to save some range of integer values
Signed: -2147483648 ~ 2147483647
Unsigned: 0 ~ 4294967295
Special: The m in integer types is only for display, with no storage range limit. For example: int(5), when inserting data 2, the select display will show: 00002

bigint[(m)] [unsigned] [zerofill]
Large integer, data type used to save some range of integer values
Signed: -9223372036854775808 ~ 9223372036854775807
Unsigned: 0 ~ 18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]
Accurate decimal value, m is the total number of digits (excluding the sign), d is the number of digits after the decimal point. The maximum value of m is 65, and the maximum value of d is 30.
Special: This type is needed for precise numerical calculations.
Decimal can store precise values because it is stored internally as a string.

Float[(M,D)] [UNSIGNED] [ZEROFILL]
Single precision floating point (non-accurate decimal value), m is the total number of digits, d is the number of digits after the decimal point
Unsigned: -3.402823466E+38 to -1.175494351E-38, 0
Signed: 0, 1.175494351E-38 to 3.402823466E+38
// The larger the number, the less accurate

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Double precision floating point (non-accurate decimal value), m is the total number of digits, d is the number of digits after the decimal point
Unsigned: -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, 2.2250738585072014E-308 to 1.7976931348623157E+308
Signed: 0, 2.2250738585072014E-308 to 1.7976931348623157E+308
// The larger the number, the less accurate

char(m)
The char data type is used to represent fixed-length strings, which can contain up to 255 characters. Here, m represents the length of the string.
Note: Even if the data is less than m length, it will still occupy m length.

varchar(m)
The varchar data type is used for variable-length strings, which can contain up to 255 characters. Here, m represents the maximum length of the string that this data type can store. Any string with a length less than this maximum value can be stored in this data type.
Note: Although varchar is more flexible to use, from the overall system performance perspective, the char data type is processed faster, sometimes even exceeding the processing speed of varchar by 50%. Therefore, users should consider various factors when designing databases to achieve the best balance.

text
The text data type is used to store variable-length large strings, which can contain up to 65,535 (2**16 − 1) characters.

mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

enum
Enumeration type
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
Example:
CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts(name, size) VALUES('dress shirt', 'large'),('t-shirt','medium'),('polo shirt','small');

set
Set type
A SET can have a maximum of 64 distinct members.
Example:
CREATE TABLE myset(col SET('a', 'b','c', 'd'));
INSERT INTO myset(col)VALUES('a,d'),('d,a'),("a,d,a"),('a,d,d'), ('d,a,d');

DATE
YYYY-MM-DD (1000-01-01/9999-12-31)

TIME
HH:MM:SS ('-838:59:59'/'838:59:59')

YEAR
YYYY (1901/2155)

DATETIME
YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)

TIMESTAMP
YYYYMMDD HHMMSS (1970-01-01 00:00:00/2037 certain time)

MySQL Table Content Operations#

Insert

insert into table(column_name,column_name...) values(value,value...)
insert into table(column_name,column_name...) values(value,value...),(value,value,value...)
insert into table(column_name,column_name...) select(column_name,column_name...) from table
Example:
insert into tab1(name,email) values('zhangsan','[email protected]')

Delete

delete from table // Delete all contents in the table
delete from table where id=1 and name='zhangsan' // Delete the row where ID = 1 and name='zhangsan'

Update

update table set name='zhangsan' where id>1

Select

select * from table
select * from table where id > 1
select nid,name,gender as gg from table where id > 1

Conditional Judgment where

select * from table where id > 1 and name != 'zhangsan' and num=12;
select * from table where id between 5 and 16;
select * from table where id in (11,22,33)
select * from table where id not in (11,22,33)
select * from table where id in (select nid from table)

Wildcard like

select * from table where name like 'zhang%'    // All starting with zhang (multiple strings)
select * from table where name like 'zhang_'    // All starting with zhang (one character)

Limit

select * from table limit 5;   // First 5 rows
select * from table limit 4,5  // From the 4th row to the 5th row
select * from table limit 5 offset 4   // From the 4th row to the 5th row

Sort asc, desc

select * from table order by column asc    // Sort by "column" from smallest to largest
select * from table order by column desc   // Sort by "column" from largest to smallest
select * from table order by column1 desc,column2 asc    // Sort by "column1" from largest to smallest, if equal, then sort by column2 from smallest to largest

Group by

select num from table group by num
select num,nid from table group by num,nid
select num,nid from table where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from table group by num,nid
select num from table group by num having max(id) > 10

Special: group by must be after where and before order by