MYSQL学習ノート
19261 ワード
mysql
--------------
-- ， ：1. ， 。2. RUNOOB ， utf8
create database if not exists RUNOOB default charset utf8 collate utf8_general_ci;
--drop
drop database if exists RUNOOB;
--MySQL ， ： 、 / ( )
--- :int/integer,smallint,dec/decimal,numeric,bigint, DECIMAL(P，D) D P
--- :float,double,real,
--- / ：date,datetime,timestape,year,time
--- ：char,varchar,longtext,tinyblob,tinytext,blob,text,mediumbolb,mediumtext,longblob
-- MySql ， ` ， ， esc ~ ， 。 MySql ， ， 。
create table if not exists `RUNOOB`(
`runood_id` int unsigned auto_increment,--AUTO_INCREMENT ， ， 1
`runood_title` varchar(100) not null,-- NULL NOT NULL， NULL ， 。
`runood_author` varchar(40) not null,
`submission_date` date,
primary key (`runood_id`)--PRIMARY KEY 。 ，
)engine=InnoDB DEFAULT CHARSET=utf8;--ENGINE ，CHARSET
--MySQL
drop table if exists runoob;-- ，
-- ， delete
delete * from runoob;-- ， ， MyISAM ，InnoDB ;delete from ， ，
--- 。
delete * from runoob where name=' ';-- ， ， innodb MyISAM ;delete from ， ，
optimize table runoob;--delete ， optimize table table_name ， innodb myisam;
--- ， ， truncate
truncate table runoob;-- ， ，
/* ， drop;
2、 ， ， truncate;
3、 ， delete。
*/
--MySQL ，
INSERT INTO runoob_tbl
(runoob_title, runoob_author, submission_date)
VALUES
(" PHP", " ", NOW());-- runoob_id ， AUTO_INCREMENT( ) 。 ， 。 NOW() MySQL ，
---
INSERT INTO table_name (field1, field2,...fieldN)
VALUES (valueA1,valueA2,...valueAN),
(valueB1,valueB2,...valueBN),
(valueC1,valueC2,...valueCN)......;
--MySQL
SELECT column_name,column_name --SELECT 。
FROM table_name -- ， (,) ， WHERE
[WHERE Clause] -- WHERE 。
[LIMIT N] -- LIMIT
[ OFFSET M] -- OFFSET SELECT 。 0
--MySQL WHERE
SELECT field1, field2,...fieldN
FROM table_name1, table_name2... -- ， , ， WHERE 。
[WHERE condition1 [AND [OR]] condition2..... -- WHERE 。 AND OR ，WHERE SQL DELETE UPDATE 。
---where =,!=,>,=,<=
select * from runoob_tbl where runoob_author=" ";-- WHERE , BINARY WHERE
select * from runoob_tbl where binary runoob_author="runoob.com";-- BINARY ，
--MySQL UPDATE
UPDATE table_name SET field1=new-value1, field2=new-value2 -- 。
[WHERE Clause] -- WHERE ，
update runoob_tbl set runoob_title=" c++" where runoob_id=3;
update students set age=age+1;
update students set name=" ", age=19 wheretel="13288097888";
UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string')
[WHERE Clause] --
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where
runoob_id = 3;
--MySQL DELETE
DELETE FROM table_name
[WHERE Clause];-- WHERE ，MySQL 。 WHERE ,
--MySQL LIKE
---SQL LIKE % ， UNIX *。 %, LIKE = 。
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'--LIKE %
select * from runoob_tbl where runoob_author like "%com";-- runoob_author COM
/*
like / ， % _ 。
'%a' // a
'a%' // a
'%a%' // a
'_a_' // a
'_a' // a
'a_' // a
*/
--MySQL UNION
---MySQL UNION SELECT 。 SELECT 。
SELECT expression1, expression2, ... expression_n --expression1, expression2, ... expression_n:
FROM tables --tables: 。
[WHERE conditions] --WHERE conditions: ， 。
UNION [ALL | DISTINCT] --DISTINCT: ， 。 UNION ， DISTINCT 。ALL: ， ， 。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
--- "Websites" "apps" country（ ）
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
--- "Websites" "apps" country（ ）：
SELECT country FROM Websites
UNION all
SELECT country FROM apps
ORDER BY country;
--- "Websites" "apps" (CN) （ ）：
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
--MySQL
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]-- ASC DESC 。 ， 。 WHERE...LIKE
SELECT * from runoob_tbl ORDER BY submission_date ASC;
SELECT * from runoob_tbl ORDER BY submission_date DESC;
---MySQL
---- gbk( )， ORDER BY：
SELECT *
FROM runoob_tbl
ORDER BY runoob_title;
---- utf8( )， ：
SELECT *
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);
--MySQL GROUP BY
---GROUP BY 。 COUNT, SUM, AVG, 。
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' ',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee_tbl` VALUES ('1', ' ', '2016-04-22 15:25:33', '1'),
('2', ' ', '2016-04-20 15:25:47', '3'),
('3', ' ', '2016-04-19 15:26:02', '2'),
('4', ' ', '2016-04-07 15:26:14', '4'),
('5', ' ', '2016-04-11 15:26:40', '4'),
('6', ' ', '2016-04-04 15:26:54', '2');
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;-- ， ：
--- WITH ROLLUP,WITH ROLLUP （SUM,AVG,COUNT…）。
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
SELECT coalesce(name, ' '), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;--
--Mysql
---INNER JOIN（ , ）：
---LEFT JOIN（ ）： ， 。
---RIGHT JOIN（ ）： LEFT JOIN ， ， 。
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
--MySQL NULL
/* MySQL :
IS NULL: NULL, true。
IS NOT NULL: NULL, true。
<=>: （ = ）， NULL true。
NULL 。 = NULL != NULL NULL 。
MySQL ，NULL （ NULL） false， NULL = NULL false 。
MySQL NULL IS NULL IS NOT NULL 。
*/
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
--MySQL
---MySQL REGEXP 。
/*
^ 。 RegExp Multiline ，^ '
' '\r' 。
$ 。 RegExp Multiline ，$ '
' '\r' 。
. "
" 。 '
' ， '[.
]' 。
[...] 。 。 ， '[abc]' "plain" 'a'。
[^...] 。 。 ， '[^abc]' "plain" 'p'。
p1|p2|p3 p1 p2 p3。 ，'z|food' "z" "food"。'(z|f)ood' "zood" "food"。
* 。 ，zo* "z" "zoo"。* {0,}。
+ 。 ，'zo+' "zo" "zoo"， "z"。+ {1,}。
{n} n 。 n 。 ，'o{2}' "Bob" 'o'， "food" o。
{n,m} m n ， n <= m。 n m 。
*/
SELECT name FROM person_tbl WHERE name REGEXP '^st';
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
--MySQL
--- insert,update,delete ; ， SQL ， 。
--- ， 4 （ACID）： （Atomicity， ）、 （Consistency）、 （Isolation， ）、 （Durability）。
/* ： （transaction） ， ， ， 。 ， （Rollback） ， 。
： ， 。 ， 、 。
： ， 。 ， （Read uncommitted）、 （read committed）、 （repeatable read） （Serializable）。
： ， ， 。
*/
/*
MYSQL ：
1、 BEGIN, ROLLBACK, COMMIT
BEGIN
ROLLBACK
COMMIT
2、 SET MySQL :
SET AUTOCOMMIT=0
SET AUTOCOMMIT=1
*/
begin;
insert into runoob_transaction_test value(5);
commit;
begin;
insert into runoob_transaction_test values(7);
rollback;
--MySQL ALTER
create table testalter_tbl
(
i INT,
c CHAR(1)
);
alter table testalter_tbl drop i;-- ALTER DROP i
alter table testalter_tbl add i int;-- ADD ， testalter_tbl i ，
--- ， MySQL FIRST ( )， AFTER （ ）
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;--FIRST AFTER ADD ， DROP ADD 。
---
---- , ALTER MODIFY CHANGE 。
ALTER TABLE testalter_tbl MODIFY c CHAR(10);-- c CHAR(1) CHAR(10)
ALTER TABLE testalter_tbl CHANGE i j BIGINT;-- CHANGE , 。 CHANGE ， ，
ALTER TABLE testalter_tbl CHANGE j j INT;
----ALTER TABLE Null , ， 。
alter table testalter_tbl modify j bigint not null default 100;
---
alter table testalter_tbl alter i set default 1000;
----ALTER DROP ，
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
---- ， ALTER TYPE
ALTER TABLE testalter_tbl ENGINE = MYISAM;-- testalter_tbl MYISAM
----
alter table testalter_tbl rename to alter_tbl;
---alter ：
---- ： myisam
alter table tableName engine=myisam;
---- ：keyName
alter table tableName drop foreign key keyName;
---- ： name1 ，type1 ，first after ， ，first ，after name2
alter table tableName modify name1 type1 first|after name2;
--MySQL
--- 。 ， ， ， 。 ， 。
--- ， SQL ( WHERE )。
--- ， ， INSERT、UPDATE DELETE。 ，MySQL ， 。
--- 。
--
---
create index indexname on mytable(username(length));-- CHAR，VARCHAR ，length ； BLOB TEXT ， length。
--- ( )
ALTER table tableName ADD INDEX indexName(columnName);
---
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
---
DROP INDEX [indexName] ON mytable;
-- , ， ： ， 。 ， 。
---
CREATE UNIQUE INDEX indexName ON mytable(username(length));
---
ALTER table mytable ADD UNIQUE [indexName] (username(length));
---
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
-- ALTER
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);-- ， ， NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);-- （ NULL ，NULL ）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);-- ， 。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);-- FULLTEXT ， 。
ALTER TABLE testalter_tbl ADD INDEX (c);--
ALTER TABLE testalter_tbl DROP INDEX c;--DROP
-- ALTER
--- ， ， （NOT NULL）
ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
--- ALTER
ALTER TABLE testalter_tbl DROP PRIMARY KEY;-- PRIMARY KEY， ， 。
---
SHOW INDEX FROM table_name; \G -- \G 。
--MySQL
---MySQL 。 ， ，Mysql
CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL
, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES
('cucumber', 100.25, 90, 2);
DROP TABLE SalesSummary;
--MySQL
--- SHOW CREATE TABLE (CREATE TABLE) ， ， 。
--- SQL ， ， SQL ，
--- ， INSERT INTO ... SELECT 。
SHOW CREATE TABLE runoob_tbl \G;
CREATE TABLE `clone_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB;
INSERT INTO clone_tbl (runoob_id,
unoob_title,
runoob_author,
submission_date)
SELECT runoob_id,runoob_title,
runoob_author,submission_date
FROM runoob_tbl;
--- :
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
--- :
CREATE TABLE newadmin AS
(
SELECT username, password FROM admin
)
--- :
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin
)
--- :
CREATE TABLE newadmin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
--- :
CREATE TABLE newadmin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)
--MySQL
---MySQL ：1, 2, 3, ...， ， ， MySQL
CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);
INSERT INTO insect (id,name,date,origin) VALUES
(NULL,'housefly','2001-09-10','kitchen'),
(NULL,'millipede','2001-09-10','driveway'),
(NULL,'grasshopper','2001-09-10','front yard');
---
--- ， AUTO_INCREMENT ， ， 。
ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);
---
CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;-- 1， 100
--- ， ：
ALTER TABLE t AUTO_INCREMENT = 100;
--MySQL
---
---- MySQL PRIMARY KEY（ ） UNIQUE（ ） 。
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
----INSERT IGNORE INTO INSERT INTO INSERT IGNORE ， ， ， 。 ， 。
INSERT IGNORE INTO person_tbl (last_name, first_name)
VALUES( 'Jay', 'Thomas');
----INSERT IGNORE INTO ， ， ， ， 。 REPLACE INTO into primary unique ， 。 。
--- UNIQUE
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);
--
SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;
--
--- SELECT DISTINCT
SELECT DISTINCT last_name, first_name FROM person_tbl;
--- GROUP BY ：
SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name);
--
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
-- INDEX（ ） PRIMAY KEY（ ） 。 ：
ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (last_name, first_name);
--MySQL
---MySQL SELECT...INTO OUTFILE 。
SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt';
--- ， CSV ：
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r
';
--- ， ， 。 。
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
FROM test_table;
--MySQL
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
/* LOCAL ， 。 ， 。
LOAD DATA ， 。
FIELDS LINES 。 ， ，FIELDS LINES 。
FIELDS ， （TERMINATED BY、[OPTIONALLY] ENCLOSED BY ESCAPED BY) ， ， 。
*/
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r
';
LOAD DATA LOCAL INFILE 'dump.txt'
INTO TABLE mytbl (b, c, a);--LOAD DATA ， ， 。