Ubuntu12.04下使用mysql, 默认版本5.5.34
kitt
posted @ 2013年11月12日 14:24
in 技术类 Tech
, 1661 阅读
看着http://dev.mysql.com/doc/refman/5.5/en/tutorial.html做的
安装：http://wiki.ubuntu.org.cn/MySQL，首先sudo start mysql, 让mysqld跑起来
MySQL命令不区分大小写，混用也没有问题。
3.1. Connecting to and Disconnecting from the Server
mysql -h host -u user -p 然后输入密码，如果MySQL跑在本机上，省略-h host即可
QUIT退出，quit, quiT都可以退出，这条命令不需要分号
3.2. Entering Queries
查询版本, 当前日期和时间: SELECT VERSION(), CURRENT_DATE, NOW();
查询语句要用分号结尾，一个命令没有输入分号结尾时会显示 ->, 此时要么输入一个分号来执行命令，要么输入 \c 取消命令。其他类似的有'>等待单引号 ">等待双引号 `>等待撇号 /*>等待/*
MySQL把命令发给server执行，然后显示结果。它显示的执行时间不是CPU time或machine time，而是wall clock time，就是一个任务的结束时间减开始时间，人的感知时间。
简单的计算: SELECT SIN(PI()/4), (4+1)*5;
查询user: SELECT USER();
3.3. Creating and Using a Database
查看server上当前有什么数据库: SHOW DATABASES;
test数据库一般用来让用户做测试。
访问数据库: use test 这条命令不需要分号，必须写成一行，不可多行。
在test数据库做实验有风险，别人也可以改你的数据。最好用自己的数据库，比如menagerie，让管理员这样做：
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
或者自己创建一个：mysql> CREATE DATABASE menagerie; 关键字大小写不敏感，但数据库名字是大小写敏感的，表名也大小写敏感，最好一律大写或一律小写。
删除数据库: drop database database_name; 慎用！所有表将被删除！
然后选择它：mysql> USE menagerie 每次要先使用这条命令，再开始后续的MySQL操作
一个省事的方法是shell> mysql -h host -u user -p menagerie 注意menagerie虽然跟在-p后面，但它不是密码。
查看当前哪个数据库被选择了: select database();
查看当前的表: SHOW TABLES;
现在想建一个pet table，每条记录代表一个宠物。不要把age记入表中因为它是变化的，birth date是OK的。
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
删除一张表: drop table table_name;
VARCHAR的值可取1～65535, 使用show tables;可看到menagerie数据库下有了表pet，使用explain pet; 或 describe pet; 可查看表的结构。
往表里填数据可以用load data或者insert，比如有个文件pet.txt：
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
注意MySQL日期的格式是’YYYY-MM-DD’,排列要按照表pet的列的顺序，不同列之间用tab分隔，NULL用\N表示。
把文件pet.txt内容载入table pet：LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
若在windows编辑的文件本命令末尾要加上lines terminated by '\r\n';
若是OS X则末尾加lines terminated by '\r';
出于安全性考虑，MySQL默认是不能读文件的。所以先quit，再用mysql -u user -p --local-infile=1 启动就OK了。
载入成功后可用select * from pet; 来查看table内容。
使用insert往table pet里面插入数据, string用单引号或者双引号都行：
insert into pet values ('Puffball','Diane','hamster','f','1999-03-30',NULL);
当然load data比insert省事喽，打字少。
SELECT语句：SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
比如select * from pet; 可查看整张表的内容。
发现Bowser的生日比较离谱，应该是1989，使用UPDATE语句来修改：
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
UPDATE语句好处是只修改有问题的record，并不用reload整张表。
只看Bowser的记录：
SELECT * FROM pet WHERE name = 'Bowser';
查看生日是1998年以后的：
SELECT * FROM pet WHERE birth >= '1998-1-1';
查看female dogs：
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
AND的优先级高于OR，最好用括号：
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
只看name，birth两列：
SELECT name, birth FROM pet;
只看owner：
SELECT owner FROM pet;
发现有一些重复的名字，去重：
mysql> SELECT DISTINCT owner FROM pet;
对输出结果排序用ORDER BY：
mysql> SELECT name, birth FROM pet ORDER BY birth;
对于只有大小写不同的列，若强制按大小写排序：
ORDER BY BINARY col_name
安species生序排列，相同species按birth降序排列：
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
用timestampdiff函数计算每个宠物的年龄，age这个名字用于使结果更清楚：
select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet order by age;
已死去的宠物：
select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet where death is not null order by age;
提取日期的某一部分 year(), month(), dayofmonth():
SELECT name, birth, MONTH(birth) FROM pet;
谁出生在5月份：
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
看谁在下个月过生日，注意12月：
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
NULL表示a missing unknown value，用IS NULL或IS NOT NULL来测试，不能用=, <, <>
SELECT 1 IS NULL, 1 IS NOT NULL; 结果使0和1
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; 结果全是NULL
MySQL中0或NULL是false，其他东西都是true，布尔操作默认的真值是1
GROUP BY语句中两个NULL被认为是一样的
ORDER BY … ASC语句执行时NULL第一个出现，DESC时NULL最后一个出现
0不是NULL，空字符串不是NULL
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
下划线_匹配单个字符，%匹配多个字符，包括0个。比较操作符LIKE，NOT LIKE
以b开头的name：
SELECT * FROM pet WHERE name LIKE 'b%';
以fy结尾的name：
SELECT * FROM pet WHERE name LIKE '%fy';
包含w的name：
SELECT * FROM pet WHERE name LIKE '%w%';
长度为5的name：
SELECT * FROM pet WHERE name LIKE '_____';
MySQL也支持正则表达式，操作符REGEXP，NOT REGEXP，RLIKE，NOT RLIKE
点.匹配单个字符，中括号表示或，[abc]匹配a或b或c, [a-z]任意字母, [0-9]任意数字
x*匹配任意多个x, 包括0, [0-9]匹配任意多个数字，.*匹配任意多个字符
REGEXP任意一部分匹配上就成功，LIKE要value的全部都匹配上才成功
要求value的开头或结尾必须匹配上，用^或$，比如找以b开头的name：
SELECT * FROM pet WHERE name REGEXP '^b';
要求大小写也匹配:
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
找以fy结尾的name:
SELECT * FROM pet WHERE name REGEXP 'fy$';
找还有w的name, 可以看到REGEXP是匹配一部分的:
SELECT * FROM pet WHERE name REGEXP 'w';
找长度为5的name:
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$'; 花括号表示重复5次
计算行数，table pet中有多少行？
SELECT COUNT(*) FROM pet;
每个人有多少宠物？
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
每个性别有多少宠物？
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
具体到某物种某性别，这一类有多少？
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
多个表的情况，比如再来一个event表：
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
然后数据文件是event.txt
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel \N
Fang 1991-10-12 kennel \N
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday
填表：LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
找出发生litter的宠物的name，age：
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark
FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
INNER JOIN允许两个表内容出现在一起，当且仅当ON条件被满足时。
仅仅出现在一张表中的name是不会出现在结果中的。
有时当需要比较同一张表中的内容时，可以把两张相同的表join到一起，比如将宠物中的male和female搭配：
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 p2.sex = 'm';
3.4. Getting Information About Databases and Tables
列出server管理的数据库：show databases;
列出当前正在使用的数据库用database()函数: SELECT DATABASE();
访问数据库: use database_name
列出表的名字: show tables;
列出表的结构: describe table_name; 或者 desc table_name; 或者 explain table_name;
看已存在的表是怎么建的: show create table table_name;
看表的索引: show index from table_name;
评论 (0)