Mysql常用文まとめ
15109 ワード
/** */
SHOW DATABASES;
CREATE DATABASE db;
SHOW DATABASES;
DROP DATABASE db;
/** */
USE db;
SHOW TABLES;
CREATE TABLE IF NOT EXISTS student(
stu_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(20) NOT NULL DEFAULT '',
stu_age INT NOT NULL DEFAULT 0,
stu_birthday DATE,
stu_salary FLOAT DEFAULT '0.1'
#
);
DESCRIBE student;
/** */
ALTER TABLE student ADD COLUMN stu_grade INT NOT NULL;
ALTER TABLE student DROP COLUMN stu_grade;
/** */
INSERT INTO student VALUES(1,' ',23,'1991-01-23','');
INSERT INTO student (stu_name,stu_age,stu_birthday) VALUES(' ',22,'1992-1-2');
INSERT INTO student (stu_name,stu_age) VALUES(' ',22);
UPDATE student SET stu_name=' ' WHERE stu_id=1;
UPDATE student SET stu_name=' ',stu_age=23 WHERE stu_id=1;
DELETE FROM student WHERE stu_id=1;
/** student teacher
：
*/
CREATE TABLE IF NOT EXISTS teacher(
tea_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
tea_name VARCHAR(20) NOT NULL DEFAULT '',
tea_age INT NOT NULL DEFAULT 0,
tea_birthday DATE,
tea_salary FLOAT DEFAULT '0.1'
#
);
INSERT INTO teacher SELECT * FROM student;
INSERT INTO teacher (tea_name,tea_age) SELECT stu_name,stu_age FROM student WHERE stu_age=22;
/** */
#Mysql ASC( ); （DESC）
/** ：
SELECT
FROM
[WHERE 1]
[GROUP BY 1] [HAVING 2]
[ORDER BY [ASC|DESC]]
*/
/**
: SQL :
WHERE HAVING SELECT ：
1、 WHERE
2、 GROUP BY
3、 WITH ROLLUP/CUBE
4、 HAVING
5、 ORDER BY
， GROUP/WHERE/HAVING/WITH... 。
*/
/**GROUP BY HAVING ：
GROUP BY ， 。
。 ， （GROUP BY ）
HAVING GROUP BY
*/
SELECT * FROM student;
SELECT * FROM student WHERE stu_age=22;
SELECT stu_name,stu_age,stu_birthday FROM student WHERE stu_age=22;
SELECT stu_name AS ' ',stu_age AS ' ' FROM student ORDER BY stu_age; # AS
SELECT * FROM student WHERE stu_name IN (' ',' '); # IN
SELECT * FROM student WHERE stu_age BETWEEN 0 AND 22; # BETWEEN AND
SELECT * FROM student WHERE stu_age>= 23; # ：（ =,<>,<,<=,>,>=）
/**( “%”， ， ， “ ” 。
“ ” ， ：'% %'; ， '_ %' '_ ' '_ _'。)*/
SELECT * FROM student WHERE stu_name like '% %';
SELECT * FROM student WHERE stu_name like '_ %';
SELECT * FROM student WHERE stu_name IS NOT NULL # IS[NOT] NULL
SELECT COUNT(*) FROM student;
SELECT avg(stu_age) FROM student;
SELECT max(stu_age) FROM student;
SELECT min(stu_age) FROM student;
SELECT * FROM student LIMIT X,Y; #X ，Y X ， Y
/** **/
/** : ，
。 。
， 。*/
CREATE TABLE employee(
num INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_id INT NULL, # （department）
name VARCHAR(20),
age INT ,
gender VARCHAR(20),
homeaddr VARCHAR(50)
)
INSERT INTO employee VALUES
(NULL, 1001, ' ', 26, ' ', ' '),
(NULL, 1001, ' ', 24, ' ', ' '),
(NULL, 1002, ' ', 25, ' ', ' '),
(NULL, 1004, 'Aric', 15, ' ', 'England');
CREATE TABLE department(
d_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
d_name VARCHAR(20) NOT NULL,
function VARCHAR(50),
address VARCHAR(50)
)
CREATE TABLE worker(
id INT PRIMARY KEY AUTO_INCREMENT,
num INT(10), #
d_id INT(50), # （ ）
name VARCHAR(20), #
gender VARCHAR(10), #
birthday DATE, #
address VARCHAR(50), #
FOREIGN KEY(d_id) REFERENCES department(d_id)
)
INSERT INTO department VALUES(1004, ' ', ' ', '2 3 ');
INSERT INTO employee VALUES(NULL, 1003, ' ', 28, ' ', ' ');
INSERT INTO employee VALUES(NULL, 1006, ' ', 22, ' ', ' ');
/** */
/** : 。 ，
。 ， */
SELECT num AS ' ID',name,age,gender,homeaddr,d_name,function,address FROM employee,department
WHERE employee.d_id=department.d_id;
/** */
/** : 。 。
， 。 ， 。
。*/
/** ：
SELECT
FROM 1( ) LEFT|RIGHT JOIN 2( )
ON 1. 1 = 2. 2;
*/
SELECT num, name, employee.d_id,age,gender, d_name, function
FROM employee LEFT JOIN department
ON employee.d_id=department.d_id;
SELECT num, name, employee.d_id,age,gender, d_name, function
FROM employee RIGHT JOIN department
ON employee.d_id=department.d_id;
CREATE TABLE performance(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
e_num INT(10) NOT NULL UNIQUE,
performance FLOAT NOT NULL DEFAULT 0
) DEFAULT CHARSET=utf8;
INSERT INTO performance VALUES
(NULL, 2, 2000),
(NULL, 1, 100),
(NULL, 3, 5000),
(NULL, 5, 8000),
(NULL, 6, 10000);
/** */
SELECT num, name, employee.d_id,age,gender, d_name, function, performance
FROM employee
LEFT JOIN department
ON employee.d_id=department.d_id
LEFT JOIN performance
ON employee.num=performance.id;
/** */
SELECT num,name,employee.d_id,age,gender,d_name,function
FROM employee,department
WHERE employee.d_id=department.d_id
AND age>=1
ORDER BY age DESC;
/** */
/** ： 。
， 。
IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS 。
， '='、'!='、'>' '<' 。 */
SELECT * FROM employee
WHERE d_id IN(SELECT d_id FROM department); /**IN */
CREATE TABLE computer_stu(
id INT PRIMARY KEY,
name VARCHAR(20),
score FLOAT
) DEFAULT CHARSET=utf8;
INSERT INTO computer_stu VALUES(1001, 'lILY', 85);
INSERT INTO computer_stu VALUES(1002, 'Tom', 91);
INSERT INTO computer_stu VALUES(1003, 'Jim', 87);
INSERT INTO computer_stu VALUES(1004, 'Aric', 77);
INSERT INTO computer_stu VALUES(1005, 'Lucy', 65);
INSERT INTO computer_stu VALUES(1006, 'Andy', 99);
INSERT INTO computer_stu VALUES(1007, 'Ada', 85);
INSERT INTO computer_stu VALUES(1008, 'jeck', 70);
CREATE TABLE scholarship(
level INT PRIMARY KEY,
score INT
) DEFAULT CHARSET=utf8;
INSERT INTO scholarship VALUES(1, 90);
INSERT INTO scholarship VALUES(2, 80);
INSERT INTO scholarship VALUES(3, 70);
/** computer_stu 、 */
SELECT com.id,com.name,com.score FROM computer_stu as com
WHERE score>=(SELECT score FROM scholarship WHERE level=1);
/** department 24 */
SELECT dep.d_name FROM department as dep
WHERE dep.d_id IN(SELECT emp.d_id FROM employee as emp WHERE emp.age!=24);
/**EXISTS */
/** :EXISTS 。 EXISTS ， 。
。 ， (TRUE)。
， (FALSE)。 ， 。 ，
*/
/** department d_id 1003 ， employee */
SELECT * FROM employee
WHERE EXISTS(SELECT * FROM department WHERE d_id=1003);
/** department d_id 1003 ， employee age 24 */
SELECT * FROM employee as emp
WHERE emp.age>0 AND EXISTS (SELECT * FROM department WHERE d_id=1003);
/**ANY */
/** ：ANY 。 ANY ， ，
*/
/** computer_stu 。 scholarship */
SELECT * FROM computer_stu as coms
WHERE coms.score>= ANY(SELECT score FROM scholarship) ORDER BY score DESC;
/**ALL */
/** :ALL 。 ALL ， ，
*/
/** */
SELECT * FROM computer_stu as coms
WHERE coms.score>=ALL(SELECT score FROM scholarship)
/**UNION */
/** SELECT 。
， SELECT */
/** ？？？？？？*/
/***REPLACE */
/*** : INSERT ， ( PRIMARYT KEY UNIQUE ) 。
REPLACE INTO */
/***ps: ？？？ UPDATE */
INSERT INTO product VALUES
(1005, ' 1 ', ' ', 'DD ', ' ');
REPLACE INTO product VALUES
(1005, ' 1 _replace', ' ', 'DD ', ' ');
/*** 1： (AUTO_INCREMENT) ？？*/
/*** ： INSERT 。 INSERT NULL/
/** Mysql( )*/
mysql -h(IP) -u( ) -p( )
/** mysql
： mysqlbin
*/
mysqladmin -u -p password
/** */
/*1. PC */
grant update,select,insert,delete on *.* to zyy1@"%" identified by "zyy1";
grant all on *.* to zyy2@"%" identified by "zyy2";
grant update,select,insert on *.* to zyy3@"%" identified by "zyy3";
/*2. PC db */
grant update,select,insert,delete on db.* to zyy4@localhost identified by "zyy4";
/**
： bin
*/
mysqldump -hlocalhost -uroot -p123456 db>C:\\db.sql
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
/* MySQL */
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
/* MySQL
MySQL ， 。
*/
mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
/* MySQL */
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
/* MySQL ( ) */
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
/* MySQL */
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
/* */
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
/* */
mysqldump –all-databases > allbackupfile.sql
/* MySQL */
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
/* MySQL */
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
/* */
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename
/*** : ， 。
*/
/***
CREATE TABLE index_tbl_1 (
id INT,
name VARCHAR(20),
gender BOOLEAN,
INDEX(id)
);
*/
/** */
/**
* 。 。
。 ， 。 。 ，
。 ， 。
， 。
*/
/** ：
1.
2.
3.
*/
/**
MySQL ， SQL CREATE VIEW 。 ：
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW [( )]
AS SELECT
[WITH[CASCADED|LOCAL] CHECK OPTION];
ALGORITHM:
UNDEFINED, MySQL ；
MERGE, ， ；
TEMPTABLE， ， 。
CASCADED ， ， ；"LOCAL"
。
*/
/**
MySQL 。 department ， department_view。
：
USE db;
CREATE VIEW department_view1
AS SELECT * FROM db.department;
DESC department_view;
：
CREATE VIEW department_view2(name, function,location)
AS SELECT d_name, function, address FROM db.department;
DESC department_view2;
*/
/**
MySQL ， CREATE VIEW 。
department worker worker_view1 。
：
CREATE ALGORITHM=MERGE VIEW
worker_view1(name, department, gender, age, address)
AS SELECT name, department.d_name, gender, 2011-birthday, worker.address
FROM worker,department WHERE worker.d_id=department.d_id
WITH LOCAL CHECK OPTION;
*/
/**
。 SHOW VIEW ,mysql user
。 DESCRIBE 、SHOW TABLE STATUS 、SHOW CREATE VIEW
information_schema views
*/
/** ： 。
1. sum()、count()、max()、min()
2. union、 union all、distinct、group by、having 。
3.
4. SELECT
5. 。
6. ，ALGORITHM TEMPTABLE 。
7. ，
： ， ， ，
。
*/
show TRIGGERS
/** */
/**
(TRIGGER) 。 INSERT 、UPDATE DELETE 。
， .
， .
*/
/** ： ： ， +1， ， */
/**
MySQL ， ：
CREATE TRIGGER BEFORE | AFTER
ON FOR EACH ROW
“ ” 。
“BEFORE” “AFTER” 。“BEFORE” 。“AFTER” 。
“ ” ， INERT、UPDATE DELETE。
“ ” 。
“FOR EACH ROW” 。
“ ” 。
*/
/** */