1.mysql基础笔记
1.1常用语句
DROP TABLE [IF EXISTS] tbl_name
DELETE FROM pet;
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
mysql --help
mysql -h host -u user -p
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT USER(); SELECT NOW();
SELECT USER()\c # 不想执行
SHOW DATABASES;
USE test;
SHOW TABLES;
DESCRIBE pet;
SHOW WARNINGS;
# 配置私有数据库权限
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
[ LINES TERMINATED BY '\r\n' ];
# tab键和null
Whistler Gwen bird \N 1997-12-09 \N
INSERT INTO pet
VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
OR (species = 'dog' AND sex = 'f');
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet
ORDER BY species, birth DESC;
# calculate age
SELECT name, birth, death,
TIMESTAMPDIFF(YEAR,birth,death) AS age
FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT '2018-10-31' + INTERVAL 1 DAY;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%y';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
# 8.0可用REGEXP_LIKE
SELECT species, sex, COUNT(*) FROM pet
WHERE sex IS NOT NULL
GROUP BY species, sex;
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = 'f' AND p1.death IS NULL
AND p2.sex = 'm' AND p2.death IS NULL;
1.2mysql与sql关系
1.3 crud
1 新增字段：
ALTER TABLE `dashboards` ADD COLUMN `sort` int(11) NULL DEFAULT '' COMMENT '界面上显示顺序';
ALTER TABLE dashboards ADD COLUMN type int(11) DEFAULT NULL COMMENT '0：新建文件夹；1新建看板';
1.4 engine
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
engine = create_engine('mysql://root:8319jwjw!@localhost/local_test_superset?charset=utf8')
session = Session(engine)
1.5 mysql忘记账号密码
mac版本
关闭mysql, 苹果 --》系统偏好设置 --》 mysql --》Stop Mysql Server
进入mysql安装路径 cd /usr/local/mysql/bin/
登录管理员权限 sudo su
./mysqld_safe --skip-grant-tables & (又重启了)
进入mysql 输入命令 ./mysql
flush privileges;
update user set authentication_string='' where user='root';
alter user 'root'@'localhost' identified by 'xxxxxxx';
