MySQL安装使用(ubuntu)

安装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;