安装mysql-server包
sudo apt-get install mysql-server
安全设置
sudo mysql_secure_installation
创建用户
根用户连接mysql
mysql -u root -p
创建新用户
CREATE USER 'tester'@'localhost' IDENTIFIED BY 'password';
设置用户权限
GRANT ALL PRIVILEGES ON * . * TO 'tester'@'localhost';
重新加载权限
FLUSH PRIVILEGES;
新用户连接mysql
mysql -u tester -p
创建新数据库
CREATE DATABASE testdb;
删除数据库
DROP DATABASE testdb;
查看数据库
SHOW DATABASES;
连接数据库
USE testdb;
创建表
CREATE TABLE articles (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
查看表
SHOW TABLES;
查看表结构
DESCRIBE srticles;
表中增加数据
INSERT INTO articles (name) VALUES ("python");
增加列
ALTER TABLE articles ADD author VARCHAR(20);
增加列到指定位置
ALTER TABLE articles ADD content VARCHAR(10000) AFTER name;
更新表
UPDATE articles SET name = "java", author = "sam" WHERE name = "flask";
删除列
ALTER TABLE articles DROP created;
删除行
DELETE FROM articles WHERE name = "flask";
处理unicode文本
ALTER TABLE articles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE articles CHANGE content content VARCHAR(10000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE articles CHANGE author author VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
