mysql学习笔记之一
来源：互联网 发布：微软雅黑blod mac 编辑：程序博客网 时间：2024/06/05 08:23
shell> mysql --help3.1连接和断开服务器---------------------连接服务器 shell> mysql -h host -u user -p断开服务器 1、mysql> QUIT 2、mysql> \q 3、Ctrl + D (unix)---------------------3.2输入查询语句---------------------查询MySQL版本和当前日期 mysql> SELECT VERSION(),CURRENT_DATE; mysql> select version(),current_date; mysql> SeLeCt vErSion(),current_DATE; MySQL关键字不区分大小写简单计算 mysql> SELECT SIN(PI()/4),(1+2)*3;一行里边写多条查询语句 mysql> SELECT VERSION();SELECT NOW();取消查询 mysql> SELECT USER()\c \c 取消编辑---------------------3.3创建和使用数据库---------------------mysql> SHOW DATABASES;mysql> USE test (USE 和 QUIT 一样，不需要分号结束，但是只能写在一行上面) ++++++++++++++++++++ 3.3.1创建和选择数据库 ++++++++++++++++++++ mysql> CREATE DATABASE menagerie; 在unix下，数据库名字和表名是大小写敏感的 mysql> USE menagerie 选择数据库 另外一种方式： shell> mysql -h host -u user -p menagerie (选择menagerie这个数据库) mysql> SELECT DATABASE(); 查看现在正在使用的数据库的名字 ++++++++++++++++++++ 3.3.2创建表 ++++++++++++++++++++ mysql> SHOW TABLES; 查看当前数据库中有哪些表 mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); 创建表 mysql> DESCRIBE pet; 或者 mysql> DESC pet; 查看表结构 ++++++++++++++++++++ 3.3.3向表里边装载数据 ++++++++++++++++++++ pet.txt Fluffy Horold cat f 1993-02-04 \N Claws Gwen cat m 1994-03-17 \N Buffy Horold 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 空值NULL用\N表示 mysql> LOAD DATA LOCAL INFILE 'D:\\qs\\sql\\mysql\\pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n'; Windows系统下的装载方式 mysql> LOAD DATA LOCAL INFILE '/home/qs/pet.txt' INTO TABLE pet; UNIX系统下的装载方式 mysql> LOAD DATA LOCAL INFILE '/home/qs/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r'; MACOS下的装载方式 mysql> INSERT INTO pet -> VALUES('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL); 一条插入语句 ++++++++++++++++++++ 3.3.4从表里边获取数据 ++++++++++++++++++++ 3.3.4.1选择所有数据 ==================== mysql> SELECT * FROM pet; 3.3.4.2选择特定的行 ==================== mysql> SELECT * FROM pet WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE name = 'bowser'; 大小寫不敏感 mysql> SELECT * FROM pet WHERE birth >= '1998-1-1'; mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f'); 3.3.4.3選擇特定的列 ==================== mysql> SELECT name, birth FROM pet; mysql> SELECT owner FROM pet; mysql> SELECT DISTINCT owner FROM pet; mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat'; 3.3.4.4對行進行排序 ==================== mysql> SELECT name, birth FROM pet ORDER BY name; mysql> SELECT name, birth FROM pet ORDER BY birth; mysql> SELECT * FROM t_dept ORDER BY BINARY dname; 如果需要明確區分大小寫，使用這個 mysql> SELECT name, birth FROM pet ORDER BY birth DESC; 明確指定降序排列，默認是升序排列 mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; 3.3.4.5日期計算 ==================== mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet; 計算生日 mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet ORDER BY name; 最好給排個序 mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death IS NOT NULL ORDER BY name; 下面這兩個不會報錯，但是查詢結果是錯的，不能用 mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death <> NULL ORDER BY name; mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet WHERE death != NULL ORDER BY name; mysql> SELECT name, birth, MONTH(birth) FROM pet; mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; 從日期裡邊抽取月份 mysql> SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE()) FROM DUAL; 提取當前的年份、月份和日子 mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); 找出下個月出生的寵物 3.3.4.6关于NULL ==================== mysql> SELECT 1 IS NULL, 1 IS NOT NULL; mysql> SELECT 1 > NULL, 1 < NULL, 1 = NULL, 1 <> NULL; 任何邏輯運算符和NULL操作的結果都是NULL mysql> SELECT * FROM pet ORDER BY death; 升序排列的時候，NULL在前 mysql> SELECT * FROM pet ORDER BY death DESC; 降序排列的時候，NULL在后 mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL, NULL IS NULL; 定義為NOT NULL的列是可以插入0或者空字符串的（0 IS NOT NULL | '' IS NOT NULL） 3.3.4.7模式匹配 ==================== 標準 LIKE | NOT LIKE | _ | % mysql> SELECT * FROM pet WHERE name LIKE 'b%'; mysql> SELECT * FROM pet WHERE name LIKE '%FY'; mysql> SELECT * FROM pet WHERE name LIKE '%w%'; mysql> SELECT * FROM pet WHERE name LIKE '_____'; 擴展的正則表達式 REGEXP | NOT REGEXP | RLIKE | NOT RLIKE | . | [] | * | ^ | $ mysql> SELECT * FROM pet WHERE name REGEXP '^b'; 以b開頭，大小寫不敏感 mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b'; 以b開頭，大小寫敏感 mysql> SELECT * FROM pet WHERE name REGEXP BINARY 'fy$'; 以fy結尾 mysql> SELECT * FROM pet WHERE name REGEXP BINARY 'w'; 包含w mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^.....$'; mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^.{5}$'; 3.3.4.8行計數 ==================== mysql> SELECT COUNT(*) FROM pet; mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex; // mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT name, COUNT(*) FROM pet; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT name, COUNT(*) FROM pet; +--------+----------+ | name | COUNT(*) | +--------+----------+ | Fluffy | 9 | +--------+----------+ 1 row in set (0.00 sec) mysql> // 3.3.4.9使用多表 ==================== 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 need beak straightened Slim 1997-08-03 vet broken rib Bowser 1991-10-12 kennel Fang 1991-10-12 kennel Fang 1998-08-28 birthday Gave him a new chew toy Claws 1998-03-17 brithday Gave him a new flea collar Whistler 1998-12-09 birthday First birthday mysql> LOAD DATA LOCAL INFILE 'D:/教程/其他/mysql-study/mysql5.1-doc/event.txt' INTO TABLE event LINES TERMINATED BY '\r\n'; mysql> 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'; mysql> 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獲取數據庫和表的信息 ---------------------mysql> SELECT DATABASE(); 查看當前正在使用的數據庫mysql> SHOW TABLES; 查看當前使用的數據庫中的表mysql> DESCRIBE pet; 查看表结构3.5使用批量模式-----------------------force 使用選項shell> mysql < batch-file C:\> mysql -e "source batch-file"shell> mysql -h host -u user -p < batch-fileshell> mysql < batch-file | moreshell> mysql < batch-file > mysql.out 批量模式輸出非常簡潔C:\Users\qs>mysql < F:/mysql.txtname owner species sex birth deathFluffy Horold cat f 1993-02-04 NULLClaws Gwen cat m 1994-03-17 NULLBuffy Horold dog f 1989-05-13 NULLFang Benny dog m 1990-08-27 NULLBowser Diane dog m 1989-08-31 1995-07-29Chirpy Gwen bird f 1998-09-11 NULLWhistler Gwen bird NULL 1997-12-09 NULLSlim Benny snake m 1996-04-29 NULLPuffball Diane hamster f 1999-03-30 NULLC:\Users\qs>如果想讓輸出和交互模式一樣，可以使用-t 選項C:\Users\qs>mysql -t < F:/mysql.txt+----------+--------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+----------+--------+---------+------+------------+------------+| Fluffy | Horold | cat | f | 1993-02-04 | NULL || Claws | Gwen | cat | m | 1994-03-17 | NULL || Buffy | Horold | dog | f | 1989-05-13 | NULL || Fang | Benny | dog | m | 1990-08-27 | NULL || Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 || Chirpy | Gwen | bird | f | 1998-09-11 | NULL || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || Slim | Benny | snake | m | 1996-04-29 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+--------+---------+------+------------+------------+C:\Users\qs>如果想讓輸出顯示操作語句，可以使用-v選項C:\Users\qs>mysql -v < F:/mysql.txt--------------SELECT * FROM pet--------------name owner species sex birth deathFluffy Horold cat f 1993-02-04 NULLClaws Gwen cat m 1994-03-17 NULLBuffy Horold dog f 1989-05-13 NULLFang Benny dog m 1990-08-27 NULLBowser Diane dog m 1989-08-31 1995-07-29Chirpy Gwen bird f 1998-09-11 NULLWhistler Gwen bird NULL 1997-12-09 NULLSlim Benny snake m 1996-04-29 NULLPuffball Diane hamster f 1999-03-30 NULLC:\Users\qs>3.6普通查詢舉例---------------------準備實驗環境CREATE TABLE shop (article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,dealer CHAR(20) DEFAULT '' NOT NULL,price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,PRIMARY KEY(article, dealer));INSERT INTO shop VALUES(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),(3,'D',1.25),(4,'D',19.95); 3.6.1查詢列的最大值 ++++++++++++++++++++ mysql> SELECT MAX(article) FROM shop; 3.6.2查詢指定列的最大值所對應的記錄 ++++++++++++++++++++ mysql> SELECT article, dealer, price FROM shop WHERE price = (SELECT MAX(price) FROM shop); mysql> SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; 另一種方法 另一種方法（左外連接） mysql> SELECT s1.article, s1.dealer, s1.price -> FROM shop s1 -> LEFT JOIN shop s2 ON s1.price < s2.price -> WHERE s2.article IS NULL; 3.6.3每一組的最大值 ++++++++++++++++++++ mysql> SELECT article, MAX(price) FROM shop GROUP BY article; 3.6.4 ++++++++++++++++++++ 使用關聯子查詢的方式-效率低下 mysql> SELECT article, dealer, price -> FROM shop s1 -> WHERE price=(SELECT MAX(s2.price) -> FROM shop s2 -> WHERE s1.article = s2.article); 使用非關聯子查詢 mysql> SELECT s1.article, dealer, s1.price -> FROM shop s1 -> JOIN ( -> SELECT article, MAX(price) AS price -> FROM shop -> GROUP BY article) AS s2 -> ON s1.article = s2.article AND s1.price = s2.price; 使用左外連接 mysql> SELECT s1.article, s1.dealer, s1.price -> FROM shop s1 -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price -> WHERE s2.article IS NULL; 3.6.5使用用戶自定義變量 ++++++++++++++++++++ mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; 3.6.6使用外鍵 ++++++++++++++++++++ 3.6.7 ++++++++++++++++++++ 3.6.8 ++++++++++++++++++++ 3.6.9 ++++++++++++++++++++3.7mysql和Apache一起使用--------------------- 0 0
- Mysql学习笔记之一
- MySQL学习笔记之一
- mysql学习笔记之一
- MySQL学习笔记之一 MySQL入门
- 初试MySQL笔记之一
- MySQL笔记之一
- MySQL学习笔记之一：安装与配置(Windows 7环境)
- mysql学习之一
- mysql的学习 之一
- MySQL数据库学习之一
- mysql学习之一：mysql安装
- 学习笔记之一
- C#学习笔记之一
- Jabber学习笔记 之一
- C++学习笔记之一
- ABAP学习笔记之一
- 学习笔记之一
- java学习笔记之一
- html form表单提交后不刷新不跳转的实现方法------
- 怎么根据程序读到的ADC值，推算实际电压值？
- C++输入流状态变化的一点小细节
- 树莓派(raspberry pi)初观之虚拟机qemu
- 2016.11.5 NOIP提高组模拟赛 总结
- mysql学习笔记之一
- JAVA多线程实现模拟卖票
- mysql：查询排名
- 操作系统（1）：发展过程
- iwebshop 首页位置
- linux下判断文件和目录是否存在[总结]
- 拼命加载中...
- 数据结构实验 建树+线索化二叉树+线索二叉树的遍历
- 调整路由器天线角度真的能增强信号？看看砖家怎么说！
