Kang Changyuan's Personal Notes

select version(), database(), user();

-- Create a table with primary key, auto-increment, and constraints
create table tableName (
  id int primary key auto_increment,
  nickName varchar(36) not null comment '昵称',
  status int default 0 comment '状态',
  address varchar(120) null comment '地址',
  age int not null comment '年龄'
);

-- Delete a table
drop table tableName;

-- Delete a table only if it exists to prevent errors
drop table if exists tableName;

-- Copy table structure only
create table newTable like oldTable;

-- Copy both table structure and data
create table newTable select * from oldTable;

# Stash current changes
git stash

# Restore the most recent stash
git stash pop

# List all stashes
git stash list

# Initialize repository in current directory
git init

# Initialize a new directory as a repository
git init [projectName]

# Clone a remote project
git clone [url]

# Back up the original sources list
sudo cp /etc/sources.list /etc/sources.list.backup

# Edit the sources list to replace URLs with https://mirrors.aliyun.com/ubuntu/
sudo vim /etc/sources.list

# Update the package list and upgrade installed packages
sudo apt update
sudo apt upgrade

# List local branches
git branch

# List all local and remote branches
git branch -a

# Create and switch to a new branch
git checkout -b [newBranchName]

# Delete a branch (use -D for forced deletion)
git branch -d [branchName]
git branch -D [branchName]

# Rename current branch
git checkout [branch]
git branch -m [newName]

# Rename remote branch: delete old and push new
git push -d origin branch
git push origin newName
git branch --set-upstream-to origin/newName

# Associate local branch with a remote branch
git branch --set-upstream-to localBranch origin/remoteBranch

# Clean up deleted remote branches from local tracking
git remote prune origin

# Stage all changes
git add .

# Commit changes
git commit -m 'message'

# Standard push
git push

# Force push to overwrite remote
git push -f

# Push a new branch and set upstream tracking
git push --set-upstream origin [branchName]

# Navigate to the SSH directory
cd ~/.ssh/

# View the public key content
cat xxx.pub

# Create a new SSH key pair with an email label
ssh-keygen -m PEM -t ed25519 -C "your.email@example.com"

# When prompted:
# Enter file in which to save the key (/Users/you/.ssh/id_rsa): [Press enter]
# Enter passphrase (empty for no passphrase): [Press enter for no password]

-- Select all columns
select * from tableName;

-- Select specific columns and rename them using AS
select nickname as cname, status as '状态' from tableName;

-- Filter by multiple conditions
select * from tableName where status = 0 and nickName = '小米';

-- Filter using IN (matches any value in the list)
select * from tableName where status in (1,2);

-- Filter using NOT IN
select * from tableName where status not in (1,2);

-- Filter within a range (inclusive [a, b])
select * from tableName where status between 1 and 3;

-- Limit results: limit start, count (e.g., start at 0, return 5 records)
select * from tableName limit 0,5;

git remote set-url origin newremote

-- Insert a single record using SET syntax
insert into tableName set nickName = '昵称小胖', status = 1, age = 60, address = '长白山';

-- Insert multiple records using VALUES syntax
insert into tableName (nickName, address, status, age) values 
('小米', '长白山', 1, 20),
('小明', '太行山', 2, 22);

-- Update specific records
update tableName set nickName = '昵称大胖', status = 2 where id = 1;

-- Delete specific records
delete from tableName where id = 1;