Mysql 安装 Docker 安装
docker volume create mysql-volume docker run -d --name mysql \ -e MYSQL_ROOT_PASSWORD=1234 \ -v mysql-volume:/var/lib/mysql \ -p 3306:3306 \ mysql:5.7.35 # client mysql -h 127.0.0.1 -P 3306 -u root -p 1234 # 注意，docker启动的mysql如果用localhost连会报找不到socket的错误 # 可以把host换成127.0.0.1，或者用tcp连接 mysql -h localhost --protocol=tcp -P 3306 -u root -p 1234 DockerHub - mysql
mycli pip install mycli mycli -h 127.0.0.1 -P 3306 -u root -p 1234 基础操作 CREATE DATABASE /* 创建新数据库 */ ALTER DATABASE /* 修改数据库 */ CREATE TABLE /* 创建新表 */ ALTER TABLE /* 变更（改变）数据库表 */ ALTER TABLE table ADD COLUMN /* 添加列 */ ALTER TABLE table MODIFY /* 修改列 */ DROP TABLE /* 删除表 */ CREATE INDEX /* 创建索引（搜索键） */ DROP INDEX /* 删除索引 */ mysql -u yourusername -p yourpassword yourdatabase < text_file # 执行sql文件 Learning SQL 启动 # MacOS sudo mysqld_safe mysql -u root -p # login as root create database bank #create a new database create user 'user'@'localhost' identified by 'password'; /* 创建本地访问用户 */ create user 'user'@'%' identified by 'password'; /* 创建远程访问用户 */ grant all privileges on bank.* to 'ychen'@'localhost' identified by 'password'; /* 配置权限 */ grant all privileges on bank.* to 'ychen'@'%' identified by 'password'; flush privileges; quit; mysql -u ychen -p use bank; 创建表 CREATE TABLE person (person_id SMALLINT UNSIGNED, fname VARCHAR(20), lname VARCHAR(20), gender ENUM('M','F'), birth_date DATE, street VARCHAR(30), city VARCHAR(20), state VARCHAR(20), country VARCHAR(20), postal_code VARCHAR(20), CONSTRAINT pk_person PRIMARY KEY (person_id) ); # describe table person DESC person; # create another tabel with foreign key CREATE TABLE favorite_food (person_id SMALLINT UNSIGNED, food VARCHAR(20), CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food), CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id) ); 修改定义 ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; ALTER TABLE person ADD occupation VARCHAR(20); 插入行 INSERT INTO person (person_id, fname, lname, gender, birth_date) VALUES (null, 'William','Turner', 'M', '1972-05-27'); 查找行 SELECT person_id, fname, lname, birth_date FROM person; # 按列显示 SELECT person_id, fname, lname, birth_date FROM person \G SELECT person_id, fname, lname, birth_date FROM person WHERE person_id = 1 ORDER BY lname; 更新行 UPDATE person SET street = '1225 Tremont St.', city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138' WHERE person_id = 1; 删除行 DELETE FROM person WHERE person_id = 2; 查看tables SHOW TABLES; Query Primer Clause name Purpose Select Determines which columns to include in the query’s result set From Identifies the tables from which to draw data and how the tables should be joined Where Filters out unwanted data Group by Used to group rows together by common column values Having Filters out unwanted groups Order by Sorts the rows of the final result set by one or more columns The SELECT Clause things can be included in the SELECT clause
...