MySQL復習
80675 ワード
一、データベースの基本概念
MySQLデータベースソフトウェア
SQL
DDL:操作データベース、テーブルクライアントグラフィックスツール:SQLYog DML:テーブルのデータを削除する
DQL:クエリー・テーブルのレコード
条件クエリー練習のsqlコード
練習する
クエリー、制約、マルチテーブル関係
DQL:クエリー文
拘束
データベースのバックアップとリストア
三、マルチテーブルクエリー:
取引
DCL:
1. ： DataBase ： DB
2. ？
* 。
3. ：
1. 。
2.
3. -- SQL
MySQLデータベースソフトウェア
1.
1. mysql my.ini
* datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2. MySQL
3. C:/ProgramData MySQL 。
2.
* MySQL
1. 。
2. cmd--> services.msc
3. cmd
* net start mysql : mysql
* net stop mysql: mysql
* MySQL
1. mysql -uroot -p
2. mysql -hip -uroot -p
3. mysql --host=ip --user=root --password=
* MySQL
1. exit
2. quit
* MySQL
1. MySQL ：basedir="D:/develop/MySQL/"
* my.ini
2. MySQL ：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
*
* ：
* ：
* ：
SQL
1. SQL？
Structured Query Language：
。 ， “ ”。
2.SQL
1. SQL ， 。
2. 。
3. MySQL SQL ， 。
4. 3
* : -- # (mysql )
* : /* */
3. SQL
1) DDL(Data Definition Language)
： ， ， 。 ：create, drop,alter
2) DML(Data Manipulation Language)
。 ：insert, delete, update
3) DQL(Data Query Language)
( )。 ：select, where
4) DCL(Data Control Language) ( )
， 。 ：GRANT， REVOKE
DDL:操作データベース、テーブル
1. ：CRUD
1. C(Create):
* ：
* create database ;
* ， ， ：
* create database if not exists ;
* ，
* create database character set ;
* ： db4 ， ， gbk
* create database if not exists db4 character set gbk;
2. R(Retrieve)：
* :
* show databases;
* :
* show create database ;
3. U(Update):
*
* alter database character set ;
4. D(Delete):
*
* drop database ;
* ，
* drop database if exists ;
5.
*
* select database();
*
* use ;
2.
1. C(Create):
1. ：
create table (
1 1,
2 2,
....
n n
);
* ： ， （,）
* ：
1. int：
* age int,
2. double:
* score double(5,2)
3. date: ， ，yyyy-MM-dd
4. datetime: ， yyyy-MM-dd HH:mm:ss
5. timestamp: yyyy-MM-dd HH:mm:ss
* ， null， ，
6. varchar：
* name varchar(20): 20
* zhangsan 8 2
*
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* ：
* create table like ;
2. R(Retrieve)：
*
* show tables;
*
* desc ;
3. U(Update):
1.
alter table rename to ;
2.
alter table character set ;
3.
alter table add ;
4.
alter table change ;
alter table modify ;
5.
alter table drop ;
4. D(Delete):
* drop table ;
* drop table if exists ;
1. ：
* ：
* insert into ( 1, 2,... n) values( 1, 2,... n);
* ：
1. 。
2. ， ，
insert into values( 1, 2,... n);
3. ， ( )
2. ：
* ：
* delete from [where ]
* ：
1. ， 。
2.
1. delete from ; -- 。
2. TRUNCATE TABLE ; -- ， ， 。
3. ：
* ：
* update set 1 = 1, 2 = 2,... [where ];
* ：
1. ， 。
DQL:クエリー・テーブルのレコード
* select * from ;
1. ：
select
from
where
group by
having
order by
limit
2.
1.
select 1， 2... from ；
* ：
* ， * 。
2. ：
* distinct
3.
* 。（ ）
* ifnull( 1, 2)：null ， null
* 1： null
* null 。
4. ：
* as：as
3.
1. where
2.
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( )
* LIKE：
* ：
* _:
* %：
* IS NULL
* and &&
* or ||
* not !
条件クエリー練習のsqlコード
id int, --
name varchar(20), --
age int, --
sex varchar(5), --
address varchar(100), --
math int, --
english int --
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,' ',55,' ','
',66,78),(2,' ',45,' ',' ',98,87),(3,' ',55,' ',' ',56,77),(4,'
',20,' ',' ',76,65),(5,' ',20,' ',' ',86,NULL),(6,' ',57,' ','
',99,99),(7,' ',22,' ',' ',99,99),(8,' ',18,' ',' ',56,65);
練習する
-- 20
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
-- 20
SELECT * FROM student WHERE age = 20;
-- 20
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
-- 20 30
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
-- 22 ，18 ，25
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);
-- null
SELECT * FROM student WHERE english = NULL; -- 。null = （!=）
SELECT * FROM student WHERE english IS NULL;
-- null
SELECT * FROM student WHERE english IS NOT NULL;
-- ？ like
SELECT * FROM student WHERE NAME LIKE ' %';
--
SELECT * FROM student WHERE NAME LIKE "_ %";
-- 3
SELECT * FROM student WHERE NAME LIKE '___';
--
SELECT * FROM student WHERE NAME LIKE '% %';
クエリー、制約、マルチテーブル関係
1. DQL:
1.
2.
3.
4.
2.
3.
4.
DQL:クエリー文
1.
* ：order by
* order by 1 1 ， 2 2...
* ：
* ASC： ， 。
* DESC： 。
* ：
* ， ， 。
2. ： ， 。
1. count：
1. ：
2. count(*)
2. max：
3. min：
4. sum：
5. avg：
* ： ， null 。
：
1.
2. IFNULL
3. :
1. ：group by ；
2. ：
1. ： 、
2. where having ？
1. where ， ， 。having ， ，
2. where ，having 。
-- 。 、
SELECT sex , AVG(math) FROM student GROUP BY sex;
-- 。 、 ,
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
-- 。 、 , ： 70 ，
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
-- 。 、 , ： 70 ， , 。 2
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING > 2;
4.
1. ：limit , ;
2. ： = （ - 1） *
3. limit MySQL" "
-- 3
SELECT * FROM student LIMIT 0,3; -- 1
SELECT * FROM student LIMIT 3,3; -- 2
SELECT * FROM student LIMIT 6,3; -- 3
拘束
* ： ， 、 。
* ：
1. ：primary key
2. ：not null
3. ：unique
4. ：foreign key
* ：not null， null
1.
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name
);
2. ，
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. name
ALTER TABLE stu MODIFY NAME VARCHAR(20);
* ：unique，
1. ，
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE --
);
* mysql ， null
2.
ALTER TABLE stu DROP INDEX phone_number;
3. ，
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
* ：primary key。
1. ：
1. ：
2.
3.
2. ，
create table stu(
id int primary key,-- id
name varchar(20)
);
3.
-- alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
4. ，
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5. ：
1. ： ， auto_increment
2. ， ，
create table stu(
id int primary key auto_increment,-- id
name varchar(20)
);
3.
ALTER TABLE stu MODIFY id INT;
4.
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
* ：foreign key, ， 。
1. ，
* ：
create table (
....
constraint foreign key ( ) references ( )
);
2.
ALTER TABLE DROP FOREIGN KEY ;
3. ，
ALTER TABLE ADD CONSTRAINT FOREIGN KEY ( ) REFERENCES ( );
4.
1.
：ALTER TABLE ADD CONSTRAINT
FOREIGN KEY ( ) REFERENCES ( ) ON UPDATE CASCADE ON DELETE CASCADE ;
2. ：
1. ：ON UPDATE CASCADE
2. ：ON DELETE CASCADE
データベースのバックアップとリストア
1. ：
* ：
* ： mysqldump -u -p >
* ：
1.
2.
3.
4. 。source
2. ：
三、マルチテーブルクエリー:
* ：
select
from
where....
#
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES (' '),(' '),(' ');
#
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), --
salary DOUBLE, --
join_date DATE, --
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) -- ， ( )
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(' ',' ',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(' ',' ',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(' ',' ',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(' ',' ',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(' ',' ',4500,'2011-03-14',1);
* ：
* A,B . 。
* ，
* ：
1. ：
1. ： where
* ：
--
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
-- ， 。
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT
t1.name, --
t1.gender,--
t2.name --
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;
2. ：
* ： select from 1 [inner] join 2 on
* ：
* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
3. ：
1.
2.
3.
2. ：
1. ：
* ：select from 1 left [outer] join 2 on ；
* 。
* ：
-- ， ， ， ，
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
2. ：
* ：select from 1 right [outer] join 2 on ；
* 。
* ：
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
3. ：
* ： ， 。
--
-- 1 9000
SELECT MAX(salary) FROM emp;
-- 2 ， 9000
SELECT * FROM emp WHERE emp.`salary` = 9000;
-- sql 。
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
*
1. ：
* ， 。 ： > >= < <= =
*
--
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. ：
* ， in
-- ' ' ' '
SELECT id FROM dept WHERE NAME = ' ' OR NAME = ' ';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
--
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ' ' OR NAME = ' ');
3. ：
*
-- 2011-11-11
--
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
--
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
*
--
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- id
dname VARCHAR(50), --
loc VARCHAR(50) --
);
-- 4
INSERT INTO dept(id,dname,loc) VALUES
(10,' ',' '),
(20,' ',' '),
(30,' ',' '),
(40,' ',' ');
-- ， ，
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 4
INSERT INTO job (id, jname, description) VALUES
(1, ' ', ' ， '),
(2, ' ', ' '),
(3, ' ', ' '),
(4, ' ', ' ');
--
CREATE TABLE emp (
id INT PRIMARY KEY, -- id
ename VARCHAR(50), --
job_id INT, -- id
mgr INT , --
joindate DATE, --
salary DECIMAL(7,2), --
bonus DECIMAL(7,2), --
dept_id INT, --
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
--
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,' ',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,' ',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,' ',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,' ',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,' ',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,' ',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,' ',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,' ',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,' ',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,' ',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,' ',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,' ',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,' ',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,' ',4,1007,'2002-01-23','13000.00',NULL,10);
--
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, --
losalary INT, --
hisalary INT --
);
-- 5
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- ：
-- 1. 。 ， ， ， ，
/*
:
1. , , . emp
2. , . job
3. : emp.job_id = job.id
*/
SELECT
t1.`id`,
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`
FROM
emp t1, job t2
WHERE
t1.`job_id` = t2.`id`;
-- 2. ， ， ， ， ， ，
/*
:
1. , , . emp
2. , . job
3. , . dept
4. :emp.job_id = job.id AND emp.dept_id = dept.id
*/
SELECT
t1.`id`,
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`
FROM
emp t1, job t2, dept t3
WHERE
t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
-- 3. ， ，
/*
:
1. , . emp
2. . salarygrade
3. : emp.salary >= salarygrade.losalary AND emp.salary <= salarygrade.hisalary
emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary
*/
SELECT
t1.`ename`,
t1.`salary`,
t2.`grade`
FROM
emp t1, salarygrade t2
WHERE
t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
-- 4. ， ， ， ， ， ，
/*
:
1. 4
3. :
emp.job_id = job.id
AND
emp.dept_id = dept.id
emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary
*/
SELECT
t1.`ename`,
t1.`salary`,
t2.jname,
t2.description,
t3.`dname`,
t3.`loc`,
t4.`grade`
FROM
emp t1, job t2, dept t3, salarygrade t4
WHERE
t1.`job_id` = t2.`id`
AND
t1.`dept_id` = t3.`id`
AND
t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
-- 5. 、 、 、
/*
:
1. , , . dept
2. . emp
3. . emp.dept_id . count(id)
4. 3 dept
*/
SELECT
t1.`id`,
t1.`dname`,
t1.`loc`,
t2.totalNum
FROM
dept t1,
(
SELECT
dept_id, COUNT(dept_id) totalNum
FROM
emp
GROUP BY
dept_id
) t2
WHERE
t1.`id` = t2.dept_id;
-- 6. ,
/*
:
1. emp, emp
- emp id mgr
2. emp.id = emp.mgr
3.
-
*/
/*
--
select
t1.`ename`,
t2.`id`,
t2.`ename`
from
emp t1, emp t2
where
t1.`mgr` = t2.`id`
*/
SELECT
t1.`ename`,
t2.`id`,
t2.`ename`
FROM
emp t1
LEFT JOIN
emp t2
ON
t1.`mgr` = t2.`id`;
取引
1.
1. ：
* ， ， ， 。
2. ：
1. ： start transaction;
2. ：rollback;
3. ：commit;
3. ：
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
--
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 500
-- 0.
START TRANSACTION;
-- 1. -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. +500
-- ...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- ，
COMMIT;
-- ，
ROLLBACK;
4. MySQL
* ：
* ：
* mysql
* DML( ) 。
* ：
* Oracle
* ，
* ：
* ：SELECT @@autocommit; -- 1 0
* ： set @@autocommit = 0;
2. ：
1. ： ， ， 。
2. ： ， 。
3. ： 。 。
4. ： ，
3. （ ）
* ： ， 。 ， ， 。
* ：
1. ： ，
2. ( )： ， 。
3. ： (DML) ， ， 。
* ：
1. read uncommitted：
* ： 、 、
2. read committed： （Oracle）
* ： 、
3. repeatable read： （MySQL ）
* ：
4. serializable：
*
* ： ，
* ：
* select @@tx_isolation;
* ：
* set global transaction isolation level ;
* ：
set global transaction isolation level read uncommitted;
start transaction;
--
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
1： ： ， ; ， ；
2： ： ，
： A B 5000; AB ， 5000, 。
3： ： ， ， ， ；
4： ： ， ， 。
： ， ， 。 ， ：
01：
， ；
： A B 100 ，A B ，B ， SQL ， ， ， B 。
02：
， ， ， ， ， ， ； 。 ： ， ;
03： ：
， ， ， ， ， / ， ， ， 。
: ， ， 。
：
01：Read uncommitted( )： ， 。
02：Read Committed( )： 。
03：Repeatable read( )： 、 。
04：Serializable( )： 、 ， 。
： ：Seralizable ， Read uncommitted ; ， ; ， JDBC ， Connection ， Connection connection ， connection 。
01：Mysql ： ：Repeatable read;
02：oracle ， seralizable( ) Read committed(); Read committed ；
：
01: Read uncommitted ; ， 5000 singo ， ， singo ， ， 5000 ， 。 ， singo ， 2000 ， ， ， ， singo 2000 ，singo 。
02:Read committed ; singo ， 2000 ， ， singo 2000 ， singo ， singo ， singo ， ，singo ， ， ......
03:Repeatable read singo ， （ ），singo ， singo 。
04： ： singo ， singo 。 ， singo （select sum(amount) from transaction where month = ） 80 ， singo ， 1000 ， 1000 （insert transaction ... ）， ， singo singo A4 ， 1080 ，singo ， ， 。
Serializabale: ， ， ， ， ， ， 。
： ， 。
， ， 。
： , （ ） 。 A X， ， A ， A 。 ， B X (A )，A ， 。 B X “ ” ， 。
， ， ， ， ， ， 。 ， ， 。
： ， 。
。 T1 ， T2 ，T1 ， 。
： ， 。 ， 。 ， 。 ， ， ， ， 。
： ， ， 。 ， ， 。 ， ， 。
DCL:
* SQL ：
1. DDL：
2. DML：
3. DQL：
4. DCL： ，
* DBA：
* DCL： ，
1.
1. ：
* ：CREATE USER ' '@' ' IDENTIFIED BY ' ';
2. ：
* ：DROP USER ' '@' ';
3. ：
UPDATE USER SET PASSWORD = PASSWORD(' ') WHERE USER = ' ';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR ' '@' ' = PASSWORD(' ');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
* mysql root ？
1. cmd -- > net stop mysql mysql
* cmd
2. mysql ： mysqld --skip-grant-tables
3. cmd , mysql ， 。
4. use mysql;
5. update user set password = password(' ') where user = 'root';
6.
7. ， mysqld.exe
8. mysql
9. 。
4. ：
-- 1. mysql
USE myql;
-- 2. user
SELECT * FROM USER;
* ： %
2. ：
1. ：
--
SHOW GRANTS FOR ' '@' ';
SHOW GRANTS FOR 'lisi'@'%';
2. ：
--
grant on . to ' '@' ';
-- ，
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. ：
-- ：
revoke on . from ' '@' ';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';