常用语法
登录数据库
mysql -h localhost -uroot -p
导出数据库
mysqldump -uroot -p db > db.sql
导入数据库
mysql -uroot -p db < db.sql
// or
mysql -uroot -p db -e “source /path/to/db.sql”
开启远程登录
grant all privileges on ss.* to ‘root’@’%’ indentified by ‘passoword’ with grant option;
// or
update user set Host=”%” and User=”root”
// 注意%是不包含localhost的
flush privileges;
创建用户
CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘password’;
grant all privileges on . to test@’localhost’ identified by ‘test’;
创建表
CREATE SCHEMA testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
赋予数据库权限
GRANT ALL ON testdb.* TO ‘test’@’localhost’;
查看表结构
desc 表名;
修改表名
alter table t_book rename to bbb;
添加列：
alter table 表名 add column 列名 varchar(30);
删除列：
alter table 表名 drop column 列名;
清空mysql表中的记录有以下两种方法：
delete from 表名; 不删除id?没实验
truncate table 表名; 删除主键id
复制表结构
create table b select * from users where 0;
create table a like users;
create table b select * from users limit 0;
复制表
create table c select * from users;
删除表名为MyClass 的表
drop table MyClass; //删除表名为MyClass 的表
SELECT
SELECT * FROM table LIMIT 5;
select * from issu_info limit 7,6; 从第8条开始取,取6条
SELECT name,LENGTH(name)
FROM world
WHERE LENGTH(name)=5 AND region=’Europe’
between
SELECT name, population
FROM world
WHERE population BETWEEN 1000000 AND 1250000
like
SELECT name FROM world
WHERE name LIKE ‘%a’ OR name LIKE ‘%l’
and
SELECT name, area, population
FROM world
WHERE area > 50000 AND population < 10000000
and not
SELECT name,population,area FROM world
WHERE (area>3000000 or population>250000000) and not (area>3000000 and population>250000000)
in
SELECT name, population/area
FROM world
WHERE name IN (‘China’, ‘Nigeria’, ‘France’, ‘Australia’)
case when … then …else…end
SELECT name,
CASE WHEN continent=’Oceania’ THEN ‘Australasia’
ELSE continent END
FROM world
WHERE name LIKE ‘N%’
多个when
SELECT name,
CASE WHEN continent in (“Europe”,”Asia”) THEN “Eurasia”
WHEN continent in (“North America”,”South America”,”Caribbean”) THEN “America” ELSE continent END
FROM world WHERE name LIKE “A%” OR name LIKE “b%”
ORDER BY .. DESC 由大到小排序
SELECT winner,yr,subject FROM nobel
where winner like ‘Sir%’ ORDER BY yr DESC, winner
复合查询
SELECT DISTINCT yr
FROM nobel
WHERE subject=’Medicine’
AND yr NOT IN(SELECT yr FROM nobel
WHERE subject=’Literature’)
AND yr NOT IN (SELECT yr FROM nobel
WHERE subject=’Peace’)
Round
ROUND(7253.86, 0) -> 7254
ROUND(7253.86, 1) -> 7253.9
ROUND(7253.86,-3) -> 7000
CONCAT 将字符连在一起
SELECT CONCAT(region,name)
FROM bbc
select name,CONCAT(ROUND(100*population/(select population from world where name = ‘Germany’),0),’%’) from world
where continent = ‘Europe’
ALL
SELECT name
FROM world
WHERE gdp >ALL(SELECT gdp
FROM world
WHERE gdp>0 and continent=’Europe’)
每一个地区中面积最大的国家，引进x,y做对比
SELECT continent, name, area FROM world x
WHERE x.area >= ALL
(SELECT y.area FROM world y
WHERE y.continent=x.continent
AND area>0)
列出每个大洲和国家的名字第一个字母顺序排列。
SELECT continent,name FROM world x
WHERE x.name = (SELECT y.name FROM world y
WHERE y.continent= x.continent order by name limit 1
)
计算函数
SUM, COUNT, MAX and AVG
distinct去重
SELECT DISTINCT region FROM bbc
ORDER BY
ASC or DESC for ascending (smallest first, largest last) //小，大
GROUP BY 相同分为一组
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
having
可以筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
SELECT continent, SUM(population)
FROM world
GROUP BY continent
HAVING SUM(population)>500000000
JOIN … ON 多表关联查询
SELECT games.yr, city.country
FROM games JOIN city
ON (games.city = city.name)
SELECT goal.player,goal.teamid,game.stadium,game.mdate
FROM game JOIN goal ON (game.id=goal.matchid) WHERE goal.teamid = ‘GER’
CASE WHEN
SELECT name, population
,CASE WHEN population<1000000
THEN ‘small’
WHEN population<10000000
THEN ‘medium’
ELSE ‘large’
END
FROM bbc
LEFT JOIN … ON … 可以包含左表空值
SELECT games.yr, city.country
FROM games LEFT JOIN city
ON (games.city = city.name)
INSERT
INSERT INTO games(yr,city)
VALUES (2012,’London’);
INSERT SELECT
INSERT INTO games(yr,city)
SELECT yr+12, city FROM games;
UPDATE 更新已经存在的
UPDATE games SET city=’Paris’ WHERE yr = 2004;
DELETE
DELETE FROM games WHERE yr=2000;
CREATE并存入
CREATE TABLE games
(yr INT NOT NULL PRIMARY KEY
,city VARCHAR(20)
);
INSERT INTO games(yr,city) VALUES (2004,’Athens’);
INSERT INTO games(yr,city) VALUES (2008,’Beijing’);
INSERT INTO games(yr,city) VALUES (2012,’London’);
SELECT * FROM games;
auto-increment 自动存入
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
PRIMARY KEY
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)
FOREIGN KEY
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
CREATE INDEX
CREATE INDEX index_name
ON table_name (column_name)
在表上创建一个唯一的索引。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
DROP INDEX
ALTER TABLE table_name DROP INDEX index_name
ALTER 修改，增加表的列
ALTER TABLE games ADD season VARCHAR(6);
UNIQUE约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)
update
清空表
清空mysql表中的记录有以下两种方法：
delete from 表名; 不删除id?没实验
truncate table 表名; 删除主键id
导出建表语句
show create table tablename;
复制表结构
create table b select * from users where 0; //复制表结构
create table a like users; //复制表结构
create table b select * from users limit 0; //复制表结构
create table c select * from users; //复制表的sql
排序
ORDER BY .. DESC 由大到小排序
SELECT winner,yr,subject FROM nobel
where winner like ‘Sir%’ ORDER BY yr DESC, winner
复合查询
SELECT DISTINCT yr
FROM nobel
WHERE subject=’Medicine’
AND yr NOT IN(SELECT yr FROM nobel
WHERE subject=’Literature’)
AND yr NOT IN (SELECT yr FROM nobel
WHERE subject=’Peace’)
Round
ROUND(7253.86, 0) -> 7254
ROUND(7253.86, 1) -> 7253.9
ROUND(7253.86,-3) -> 7000
CONCAT 将字符连在一起
SELECT CONCAT(region,name)
FROM bbc
select name,CONCAT(ROUND(100*population/(select population from world where name = ‘Germany’),0),’%’) from world
where continent = ‘Europe’
distinct去重
SELECT DISTINCT region FROM bbc
having
having 可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
SELECT continent, SUM(population)
FROM world
GROUP BY continent
HAVING SUM(population)>500000000
JOIN … ON 多表关联查询
SELECT games.yr, city.country
FROM games JOIN city
ON (games.city = city.name)
mysql设置权限命令
GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
flush privileges;刷新权限
创建聚合索引
CREATE INDEX index_name
ON table_name ( column1, column2,…);
查询索引
SHOW INDEX FROM table_name