MySQLベースから上級編3
22107 ワード
MySQLベースから上級編3DML(Data Modern language)言語データ操作言語:①挿入操作②修正操作③削除操作 DDL言語定義:データ定義言語の役割:データベースとデータテーブル構造の管理 一、データベースの管理作成(create)、修正(alter)、削除(drop)一般的なデータ型 一般的な制約の意味:表のデータを制限するために使用される制限です.表のデータの正確さと信頼性を保証するために分類されます.6つの制約not null:空ではありません.このフィールドを包装する値は空ではありません(名前、学号など)default:デフォルトで、このフィールドにデフォルト値(性別など)primary key:プライマリ・キーがあることを保証します.このフィールドの値が一意であることを保証するために使用され、空ではない(たとえば、学号、従業員番号)unique:一意であり、このフィールドの値が一意であることを保証するために使用されます.空(座席番号など)check:検査制約【mysqlでサポートされていない】(例えば、年齢、性別)foreign key:外部キーであり、2つのテーブルの関係を制限するために使用されます.このフィールドの値は、プライマリ・テーブルの関連するカラムの値に由来する必要があります.プライマリ・テーブルのカラムの値(たとえば、学生の専門番号) を参照するために、テーブルから外部キー制約を追加します.
制約を追加するタイミング:1、テーブルの作成時2、テーブルの変更時
制約の追加分類:1、列レベル制約:6つの制約は構文的にサポートされていますが、外部キー制約は効果がありません2、表レベル制約:空でない、デフォルト以外はサポートされていません
プライマリ・キーと一意の比較:
一意性の保証
空の許可
1つのテーブルの数
グループ化を許可するかどうか
プライマリキー
√
×
only
√,推薦しない
ユニーク
√
√
some
√,推薦しない
外部キー:1、テーブルから外部キー関係を設定する2、テーブルからの外部キー列のタイプとプライマリ・テーブルの関連列のタイプは一致または互換性が必要で、フィールド名は要求なし3、プライマリ・テーブルの関連列はキー(一般的にプライマリ・キーまたは一意)でなければならない4、データを挿入するときは、プライマリ・テーブルを挿入し、セカンダリ・テーブルを挿入します.データを削除するときは、スレーブテーブルを削除してからメインテーブルを削除します.TCL(Transaction Controller language)トランザクション制御言語トランザクション:sql文コンポーネントの1つまたは複数の実行ユニット.この実行ユニットはすべて実行するか、すべて実行しないかのいずれかです.トランザクションは、個々のユニットの1つまたは複数のSQL文で構成されます.このユニットでは、各MySQL文は互いに依存し、個々のユニット全体が分割できない全体として機能します.ユニットのSQL文が失敗したり、エラーが発生したりすると、ユニット全体がロールバックされます.影響を受けたすべてのデータは、トランザクションが開始される前の状態に戻ります.セル内のすべてのSQL文が正常に実行された場合、トランザクションは正常に実行されます.
データベースのトランザクションに対する4つの独立性レベル:
独立性レベル
説明
read uncommitted(コミットされていないデータを読む)
トランザクションが他のトランザクションによってコミットされた変更、ダーティリード、繰返し不可リード、幻リードとして読み込まれることを許可する問題が発生します.
read commit(コミットされたデータを読む)
トランザクションが他のトランザクションによってコミットされた変更のみを読み取り、汚れた読み取りを避けることができますが、重複しない読み取りと幻の読み取りの問題は依然として存在します.
repeatable read(繰り返し可能)
トランザクションが同じ値を1つのフィールドから複数回読み込むことができることを確認します.このトランザクションが継続している間、他のトランザクションがこのフィールドを更新することを禁止します.汚れた読み取りと繰り返し不可能な読み取りを避けることができますが、幻の読み取りの問題は依然として存在します.
serializable(シリアル化)
トランザクションが同じローを1つのテーブルから読み出すことができることを確認します.このトランザクションが継続している間、他のトランザクションがテーブルの更新、挿入、削除を禁止します.同時性の問題はすべて回避できますが、パフォーマンスは次のとおりです.
汚読
繰り返し不可
まぼろし読み
READ UNCOMMITTED
あります
あります
あります
READ COMMITTED
なし
あります
あります
REPEATABLE READ
なし
なし
あります
SERIALIZABLE
なし
なし
なし
トランザクション・アイソレーション・レベルに対するさまざまなデータベース・プロダクトのサポート
oracle
mysql
READ UNCOMMITTED
×
√
READ COMMITTED
√(デフォルト)
√
REPEATABLE READ
×
√(デフォルト)
SERIALIZABLE
√
√ビューの意味:仮想テーブルは、通常のテーブルと同様に使用されます.仮想的に存在するテーブルで、行と列のデータを使用してビューのクエリー内のトランザクションのテーブルをカスタマイズし、ビューを使用するときに動的に生成され、「sqlロジック」のみを保存し、クエリー結果の適用シーンを間違えない:1)、複数の場所で同じクエリー結果を使用する2)、このクエリー結果に使用されるsql文は比較的複雑
構文のキーワードの作成
物理スペースを実際に使用するかどうか
使用
表示
create view
SQLロジックを保存しただけ
添削・改ざんは一般的にはできない
表
create table
データを保存
添削して改める.
、
（ ）：
：
insert into ( 1, 2, ......)
values( 1, 2,........)
# 1：
——insert into student(last_name, sex, tech_id, score) values(' ', ' ', 3, 99);
# 2： null ， null
——insert into student(last_name, sex, tech_id, score) values(' ', null, null,null);
——insert into student(last_name) values(' ');
# 3：
——insert into student(last_name, score, sex, tech_id) values(' ', 99, ' ‘’, 3);
# 4：
——insert into student(last_name, sex) values(' ', ' ');
# 5： ， ，
——insert into student values(' ', ' ', 3, 99);
：
：
insert into
set = , = , = ,.......
# 1： student
——insert into student
set ast_name=' ', sex=' ', tech_id=3, score=99;
insert ：
① ，
——insert into student(last_name, sex, tech_id, score)
values(' ', ' ', 3, 99), (' ', ' ', 2, 89), (' ', ' ', 4, 94);
② ，
——insert into student(last_name, sex, tech_id, score)
select ' ', sex, tech_id, score from student where score=97;
、
1、
：
update
set = , = , = ,...........
where ；
# 1：
——update student set tech_id=3, score=89 where id=34;
# 2： student id 45 tech_id=3，score=92
——update student set tech_id=3, score=92 where id=45
2、 【 】
：
sql92 ：
update 1 , 2
set = , = ,...........
where
and ;
sql99 ：
update 1
inner | left | right join 2
on
set = , = , ........
where ;
# 1： ‘ ’ score=89
# 2： student tech_id=5
、
：delete
：
1、
delete from where
# 1： 9
——delete from beauty where phone='%9';
2、
sql92 ：
delete 1 , 2
from 1 , 2
where
and ；
sql99 ：
delete 1 , 2
from 1
inner | left | right join 2
on
where ;
# 1：
——delete b
from beauty as b
inner join boys as bo
on b.boyfriend_id=bo.id
where bo.boyName=' ';
# 2：
——delete b, bo
from beauty as b
inner join boys as bo
on b.boyfriend_id=bo.id
where bo.boyName=' ';
：truncate
：truncate table ；
：
①delete where ，truncate
②truncate ，
③
delete ， ，
truncate ， ， 1
④truncate ，delete
⑤truncate ，delete
# ： >100
——truncate table boys（ ）
、
1、
：
create database ;
# 1： Books
——create database if not existts Books;
2、
# 1：
——alter database Books character set gbk;
# 2：
——alter database Books rename to books;
3、
——drop database if exists Books;
、
1、
：
create table (
【( ) 】,
【( ) 】,
【( ) 】,
.....................................,
【( ) 】
);
# 1： book
——create table book(
id int, #
book_name varchar(20), #
price double, #
author_id int, #
publish_date datetime #
);
# 2： author
——create table author(
id int, #
author_name varchar(20), #
nation varchar(10) #
);
2、
：
——alter table add| drop| modify| change| column 【 】 【first/ after 】( );
：
① （alter table change column ;）
——alter table book change column publish_date pub_date datetime;
② （alter table modify column 【 】）
——alter table book modify column pub_date timestamp ## datetime timestamp
③ （alter table add column 【first| after 】）
——alter table author add column annual double;
④ （alter table drop column ）
——alter table author drop column annual;
⑤ （alter table rename 【to】 ）
——alter table author rename to book_author;
3、
drop table if exists book_author;
4、
# 1：
——create table c_author1 like book_author;
# 2： +
——create table c_author2
select * from book_author;
# 3： + （ ）
——create table c_author3
select * from book_author where id>2;
# 4： + （ ）
——create table c_author4
select id, author_name from book_author;
# 5：
——create table c_author5
select id, author_name from book_author where 1=2;
# 1： dept1
——create table dept1(
id int(10),
name varchar(20)
);
# 2： departments dept2
——create table dept2
select department_id, department_name
from departments;
# 3： emp5
——create table emp5(
id int(10),
first_name varchar(25),
last_name varchar(25),
dept_id int(10)
);
# 4： Last_name 50
——alter table emp5 modify column last_name varchar(50);
# 5： employee employee2
——create table employee2 like employee;
# 6： emp5
——drop table if exists emp5;
# 7： employee2 emp5
——alter table employee2 rename to emp5;
# 8： dept emp5 test_column,
——alter table dept add column test_column int;
——alter table emp5 add column test_column int;
# 9： emp5 dept_id
——alter table emp5 drop column test_column;
1、 ：
：
2、
：char、varchar
：text、blob（ ）
3、 ：
、
：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
：
① ， ， ， unsigned
② ， out of range ，
③ ， ， ， 0 ， zerofill
# 1：
——create table if exists tab_int(
t1 int,
t2 int unsigned ## unsigned
);
# 2： ti 5000000000
——insert into tab_int
set t1=5000000000; ## out of range ， t1 4294967295
# 3： t1 ， 0
——alter table tab_int modify column t1 int(7) zerofill;
——insert into tab_int
set t1=1234;
、
1、 ：
float（M，D）
double（M，D）
2、
dec（M，D）
decimal（M，D）
：
①M： + ，D： ， ，
②M D ， decimal， M 10，D 0， float double， 。
③ ， （ ： ， ）
： ， （ ）
# 1： tab_float ， f1、f2、f3
——create table tab_float(
f1 float(5, 2),
f2 float(5, 2),
f3 float(5, 2),
);
# 2： f1、f2、f3 132.456、123.2、1234.56
——insert into tab_float(f1, f2, f3) values(132.456, 123.2, 1234.56);
# 3： tab_float f1、f2、f3 ， MD， 456.12、123456.131456、123.54652137
——alter table tab_float modify column f1 float;
——alter table tab_float modify column f2 float;
——alter table tab_float modify column f3 float;
——insert into tab_float(f1, f2, f3) values(456.12, 123456.131454, 123.54652137);
# 4： tab_float f1 float，f2 double，f3 decimal， M、D，
——alter table tab_float modify column f1 float;
——alter table tab_float modify column f2 double;
——alter table tab_float modify column f3 decimal;
——insert into tab_float(f1, f2, f3)
values(23456.123456, 23456.123456, 23456.123456), (456.56, 456.56, 456.56), (12.3, 12.3, 12.3);
3、
①char(M)
②varchar(M)
③ ：
binary varbinary
enum
set
：
M
char char(M)
varcahr varcahr(M)
①text
②blob ## （ 、 ）
、
——create table tab_date(
t1 datetime,
t2 datestamp
);
：
date
time
year
datetime +
datestamp +
：
datetime 8 1000-9999
datestamp 4 1970-2038
# 1：
——insert into tab_date(t1, t2) values(now(), now());
# 2： ，
——show variables like 'time_zone' ##
——set time_zone='+9:00' ## 9
——insert into tab_date(t1, t2) values(now(), now());
制約を追加するタイミング:1、テーブルの作成時2、テーブルの変更時
制約の追加分類:1、列レベル制約:6つの制約は構文的にサポートされていますが、外部キー制約は効果がありません2、表レベル制約:空でない、デフォルト以外はサポートされていません
プライマリ・キーと一意の比較:
一意性の保証
空の許可
1つのテーブルの数
グループ化を許可するかどうか
プライマリキー
√
×
only
√,推薦しない
ユニーク
√
√
some
√,推薦しない
外部キー:1、テーブルから外部キー関係を設定する2、テーブルからの外部キー列のタイプとプライマリ・テーブルの関連列のタイプは一致または互換性が必要で、フィールド名は要求なし3、プライマリ・テーブルの関連列はキー(一般的にプライマリ・キーまたは一意)でなければならない4、データを挿入するときは、プライマリ・テーブルを挿入し、セカンダリ・テーブルを挿入します.データを削除するときは、スレーブテーブルを削除してからメインテーブルを削除します.
、
1、
：
create table (
（ ， ）,
.....................，
);
# 1： stu_test ， id( )，stu_name( )，gender( )，seat( )，age( )，major_id( )
——create table stu_test (
id int primary key, ##
stu_name varchar(20) not null, ##
gender char(1) check(gender=' ' or gender=' '), ##
seat int union, ##
age int default 18, ##
major_id int references major(id) ##
);
# 2： major ， id( )，major_name( )
——create table major(
id int primary key,
major_name varchar(20) not null
);
# 3： stu_test ， 、 、
——show index from stu_test ;
2、
：
【constraint 】 ( )
# 1： stu_test
——drop table stu_test; ## stu_test
# 2： stu_test
——CREATE TABLE IF NOT EXISTS stu_test(
id INT,
stu_name VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
major_id INT,
CONSTRAINT pk PRIMARY KEY(id), ## （id）
CONSTRAINT uq UNIQUE(seat), ## （seat）
CONSTRAINT ck CHECK(gender=' ' OR gender=' '), ##
CONSTRAINT fk_stu_test_major FOREIGN KEY(major_id) REFERENCES major(id) #
);
3、
# 2： stu_test
——CREATE TABLE IF NOT EXISTS stu_test(
id int primary key, ##
stu_name varchar(20) not null, ##
gender char(1) check(gender=' ' or gender=' '), ##
seat int union, ##
age int default 18, ##
major_id INT,
CONSTRAINT fk_stu_test_major FOREIGN KEY(major_id) REFERENCES major(id) #
);
、
：
①、
——alter table modify column ;
②、
——alter table add 【constraint 】 ( ) 【 】;
# 1： stu_test
——drop table stu_test; ## stu_test
# 2： stu_test
——CREATE TABLE IF NOT EXISTS stu_test(
id INT,
stu_name VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
major_id INT
);
# 3： stu_name （not null）
——alter table stu_test modify column stu_name varchar(20) not null;
# 4： age （default）
——alter table stu_test modify column age int default;
# 5： id （primary key）
——alter table stu_test modify column id int primary key;
# 6： seat （unique）
——alter table stu_test modify column seat int unique;
——alter table stu_test add unique(seat);
# 7： major_id （foreign key）
——alter table stu_test add foreign key(major_id) references major(id);
、
1、
# 1： stu_name （not null）
——alter table stu_test modify column stu_name varchar(20) null;
# 2： age （default）
——alter table stu_test modify column age int;
# 3： seat （unique）
——alter table stu_test modify column seat int;
——alter table stu_test drop index seat;
# 4： id （primary key）
——alter table stu_test drop primary key
# 5： major_id （foreign key）
——alter table stu_test drop foreign key major_id;
、
# ： （ 、 ）
——alter table stu_test add constraint fk_stu_major foreign key(major_id) references major(id) on delete cascade;
# ： （ 、 ）
——alter table stu_test add constraint fk_stu_major foreign key(major_id) references major(id) on delete set null;
：
： ，
： ， （ ）
、 （ ）
： ，
：
① ， key
②
③
④ set auto_increment_increment=3；
：
——create table (
(key：primary key、unique) auto_increment,
.................,
);
# 1： stu_test ， id
——drop table stu_test; ## stu_test
# 2： stu_test
——CREATE TABLE IF NOT EXISTS stu_test(
id int primary key auto_increment, ## ， key
stu_name VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
major_id INT
);
、
# 1： stu_test
——alter table stu_test drop primary key；
# 2： stu_test id
——ALTER TABLE stu_test MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
、
ALTER TABLE stu_test MODIFY COLUMN id INT PRIMARY KEY;
、
# 1：
1000
1000
update set =1000-500 where name=' ';
/ /
update set =1000+500 where name=' ';
1、
—— ， ， ，
① ： A、B，A B ， B ， A （ ）。
② ： A、B，A ， B ，A ， （ —— ）。
③ ： A、B，A ， B ，A （ —— ）。
2、 ACID
① （atomicity）—— ， ， 。
② （consistency）—— 。
③ （isolation）—— ， ， 。
④ （durability）—— ， ， 。
3、
① ： （ ：insert、update、delete ）
② ： （ ：autocommit=0/1）
1：
set autocommit=0;
start transaction; ##
2： SQL （select、insert、update、delete）
1;
2;
........,
3：
commit; ## （ ）
rollback; ## （ ， ）
savepoint a; ## （ rollback ，rollback to a—— ）
4、
①
——select @@tx_isolation;
② MySQL
——set transaction isolation level (read uncommitted、read commit、repeatable read、serializable);
③
——set global transaction isolation level (read uncommitted、read commit、repeatable read、serializable);
5、delete truncate
① delete： delete rollback， delete
② truncate： truncate rollback， delete
データベースのトランザクションに対する4つの独立性レベル:
独立性レベル
説明
read uncommitted(コミットされていないデータを読む)
トランザクションが他のトランザクションによってコミットされた変更、ダーティリード、繰返し不可リード、幻リードとして読み込まれることを許可する問題が発生します.
read commit(コミットされたデータを読む)
トランザクションが他のトランザクションによってコミットされた変更のみを読み取り、汚れた読み取りを避けることができますが、重複しない読み取りと幻の読み取りの問題は依然として存在します.
repeatable read(繰り返し可能)
トランザクションが同じ値を1つのフィールドから複数回読み込むことができることを確認します.このトランザクションが継続している間、他のトランザクションがこのフィールドを更新することを禁止します.汚れた読み取りと繰り返し不可能な読み取りを避けることができますが、幻の読み取りの問題は依然として存在します.
serializable(シリアル化)
トランザクションが同じローを1つのテーブルから読み出すことができることを確認します.このトランザクションが継続している間、他のトランザクションがテーブルの更新、挿入、削除を禁止します.同時性の問題はすべて回避できますが、パフォーマンスは次のとおりです.
汚読
繰り返し不可
まぼろし読み
READ UNCOMMITTED
あります
あります
あります
READ COMMITTED
なし
あります
あります
REPEATABLE READ
なし
なし
あります
SERIALIZABLE
なし
なし
なし
トランザクション・アイソレーション・レベルに対するさまざまなデータベース・プロダクトのサポート
oracle
mysql
READ UNCOMMITTED
×
√
READ COMMITTED
√(デフォルト)
√
REPEATABLE READ
×
√(デフォルト)
SERIALIZABLE
√
√
1、teacher
CREATE TABLE `teacher` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(255) DEFAULT NULL COMMENT ' ',
`age` varchar(255) DEFAULT NULL COMMENT ' ',
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=' ';
2、student
CREATE TABLE `student` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(255) DEFAULT NULL COMMENT ' ',
`sex` varchar(255) DEFAULT NULL COMMENT ' ',
`tech_id` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT=' ';
3、 teacher student （ ）
：
create view
as ;
：
① SQL （ ）
② SQL ， （ ）
③ ， （ ）
：
——create view v1
as select s.last_name as stu_name, t.last_name as tech_name
from student as s
inner join teacher as t
on s.tech_id=t.id;
# 1：
——SELECT * FROM v1;
# 2： tech_name=' '
——SELECT stu_name FROM v1 WHERE tech_name=' ';
# 3： 、
①
——create view v
as select s.last_name as stu_name, score, t.*
from student as s
inner join teacher as t
on s.tech_id=t.id;
②
——select * from v where stu_name like '% %';
# 4：
①
——create view v
as select avg(salary) as ag, department_id
from employees
group by department_id;
②
——select v.ag, v.department_id, grade_level
from v
inner join job_grade as g
on v.ag between g.lowest_sal and hignest_sal;
# 5：
①
——select min(ag) as , d.*
from v
inner join departments as d
on v.department_id=d.department_id;
# 6：
①
——select min(ag) as , d.department_name
from v
inner join departments as d
on v.department_id=d.department_id;
4、
：
——create or replace view
as ;
：
——CREATE OR REPLACE VIEW v1
AS SELECT s.last_name AS stu_name, t.*
FROM student AS s
INNER JOIN teacher AS t
ON s.tech_id=t.id;
：
——alter view
as ;
：
——ALTER VIEW v1
AS SELECT s.last_name AS stu_name, score AS , t.*
FROM student AS s
INNER JOIN teacher AS t
ON s.tech_id=t.id;
5、
：drop view , , ....;
：drop view v1;
：
# 1： emp_v1， ‘011’ 、
①
——create or replace view emp_v1
as select e.last_name as emp_name, salary, email
from employees
where phone_number like='011%';
②
——select * from emp_v1;
# 2： emp_v2， 12000
①
——create or replace view emp_v2
as select max(salary) as max_sal, d.*
from employees
group by department_id
having max_sal>12000;
②
——select * from emp_v2;
6、
： ， 。
① SQL ： 、distinct、group by、having、union/union all
——create or replace stu_tech
as select last_name, score
from student
group by tech_id;
②
——create or replace stu_tech
as select name=' '
③select
——create or replace stu_tech
as select(select * from teacher);
④join
——create or replace stu_tech
as select s.last_name, t.*
from student as s
inner join teacher as t
on s.tech_id=t.id;
⑤from
——create or replace stu_tech2
as select * from stu_tech;
⑥where from
——create or replace stu_tech3
as select last_name, score
from student
where tech_id in(
select id from teacher course=' '
);
# 1： student teacher stu_tech
——create or replace view stu_tech
as select s.last_name, score
from student
# 2： stu_tech
——INSERT INTO stu_tech(last_name, score) VALUES(' ', 89);
# 3： stu_tech
——update stu_tech set score=90 where last_name=' '
# 4： stu_tech
——delete from stu_tech where last_name=' ';
構文のキーワードの作成
物理スペースを実際に使用するかどうか
使用
表示
create view
SQLロジックを保存しただけ
添削・改ざんは一般的にはできない
表
create table
データを保存
添削して改める.
：
# 1： Book ， ：
b_id ，
b_name ， ，
price ， 10
b_type_id ， bookType id
bookType （id、name）
——create table Book(
b_id int primary key,
b_name varchar(20) unique not null,
price float default 20,
b_type_id int,
foreign key(b_type_id) references bookType(id)
);
# 2： ， ，
——set autocommit=0；
——start transaction；
——insert into Book(b_name, price, b_type_id) values('JavaWeb', 25, 3);
——commit;
# 3： ， 100
——create view b_v
as select b_name, tb.name as b_type_name
from Book as b
inner join bookType as bt
on b.b_type_id=bt.id
where b.price>100;
# 4： ， 90~120
——create or replace view b_v2
as select b_name, price
from Book
where price between 90 and 120;
# 5：
——drop view b_v2;