Mysqlベースシリーズ(二)
この章の内容:表示作成使用データベースの削除 ユーザー管理及び授権実戦 ローカルエリアネットワークリモート接続法 「作成」「使用」「削除」「クリア」「データベース表の変更」 .表内容の添削調査 where条件、ワイルドカード_%、制限limit、ソートdescasc、連表join、組合せunion 構築文の表示、テーブル構造の表示、インデックスの有無の表示 データ型 インデックス!
一、データベース操作
1、データベースの表示
2、データベースの作成
3、データベースの使用
4、ユーザー管理
5、授権管理
権限の詳細:
追加管理者の追加
簡単な例:
一般ユーザーの追加
6、ローカルエリアネットワーク内ホストのリモート接続データベースを許可する
二、表操作
1、テーブルの作成
2、テーブルの削除
3、テーブルを空にする
4、表の修正
三、表内容操作
1、増加
2、削除
3、変更
4、調べる
5、条件
6、ワイルドカード
7、制限
8、並べ替え
9、グループ化
10、連結表
11、組み合わせ
四、その他の命令
1、建表文の表示
2、表構造の表示
3、索引付けの有無を確認する
このコマンドを使用して、sql文に最適化の余地があるかどうかを確認します.
五、データ型
新しいことを学ぶにはデータ型が欠かせない.MySQLのデータ型は簡単だ.
大きく分けて数字、文字列、時間です.
では、詳しく見てみましょう.
六、索引
1、索引の概要
インデックスはテーブルのインデックスディレクトリであり、コンテンツを検索する前にディレクトリ内のインデックス位置を調べて、クエリーデータを迅速に位置決めします.
新華字典のインデックスと理解できる.
インデックスは追加のファイルに保存されます.
2、索引の種類
一般的なインデックスの種類と機能:一般インデックス:クエリー のみを高速化一意インデックス:クエリーの加速+カラム値一意(null可能) プライマリ・キー・インデックス:クエリーの加速+カラム値一意+テーブルにnullは1つのみ(nullは不可) コンポジットインデックス:複数のカラム値からなるインデックスで、コンポジット検索に特化しており、インデックスマージ よりも効率的です.全文インデックス:テキストの内容を分詞し、検索する
≪索引のマージ|Index Merge|emdw≫:複数の単一列の索引の組合せを使用して索引を検索します.selectのデータ列は索引からのみ取得できます.データ行を読み込む必要はありません.つまり、問合せ列は構築された索引で上書きされます.
a、普通索引
b、一意索引
c、主キーインデックス
d、コンビネーションインデックス
結合インデックスは、複数のカラムを1つのインデックスに結合してクエリーします.
適用シーン:where name='Mackei'and age=22など、頻繁に複数のカラムを使用してクエリーを行います.
前述のように、結合インデックスを作成した後、クエリは次の点に注意してください. name and email -->インデックスを使用するには、nameは必ず前の に配置します. name -->インデックス の使用 email -->インデックスを使用しない 注:複数の条件を同時に検索する場合、結合インデックスのパフォーマンスは、複数の単一インデックスのマージよりも優れています.
3、関連命令
4、インデックスを正しく使用する方法
5、注意事項
6、実行計画
一、データベース操作
1、データベースの表示
SHOW DATABASES;
# ：
mysql -
test -
information_schema - MySQL 2、データベースの作成
# utf-8
CREATE DATABASE DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# gbk
CREATE DATABASE DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;3、データベースの使用
USE db_name;
# 4、ユーザー管理
#
create user ' '@'IP ' identified by ' ';
#
drop user ' '@'IP ';
#
rename user ' '@'IP '; to ' '@'IP ';;
#
set password for ' '@'IP ' = Password(' ')
PS： mysql user ， （ ）#
select user();
#
select host,user from mysql.user;
#
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
#
show grants for 'nick'@'%';mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------+
| query |
+---------------------------+
| User: 'nick'@'%'; |
| User: 'root'@'localhost'; |
+---------------------------+
rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| % | nick |
| localhost | root |
+-----------+------+
rows in set (0.00 sec)
mysql> show grants for 'nick'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for nick@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nick'@'%' IDENTIFIED BY PASSWORD '*ECE7D02DCD7D4EF7CFE8E3B249FD1D5062A821F7' |
| GRANT ALL PRIVILEGES ON `kaoshi`.* TO 'nick'@'%' |
| GRANT ALL PRIVILEGES ON `xxxxx`.* TO 'nick'@'%' |
| GRANT ALL PRIVILEGES ON `xxxxxx`.`chouti` TO 'nick'@'%' |
+-----------------------------------------------------------------------------------------------------+
rows in set (0.00 sec)
mysql>5、授権管理
#
show grants for ' '@'IP '
#
grant on . to ' '@'IP '
#
revoke on . from ' '@'IP ' ：
all privileges grant
select
select,insert
usage
：
.*
.
.
*.*
IP：
@IP IP
@192.168.1.% IP ( % )
@% IP ( IP %)権限の詳細:
all privileges grant
select
select,insert
...
usage
alter alter table
alter routine alter procedure drop procedure
create create table
create routine create procedure
create temporary tables create temporary tables
create user create user、drop user、rename user revoke all privileges
create view create view
delete delete
drop drop table
execute call
file select into outfile load data infile
grant option grant revoke
index index
insert insert
lock tables lock table
process show full processlist
select select
show databases show databases
show view show view
update update
reload flush
shutdown mysqladmin shutdown( MySQL)
super change master、kill、logs、purge、master set global。 mysqladmin
replication client
replication slave 追加管理者の追加
mysql> delete from mysql.user;
Query OK, 2 rows affected (0.00 sec)
mysql> grant all privileges on *.* to system@'localhost' identified by 'mackei' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+--------+-----------+
| user | host |
+--------+-----------+
| system | localhost |
+--------+-----------+
row in set (0.00 sec)
mysql>簡単な例:
grant all privileges on db1.tb1 TO ' '@'IP'
grant select on db1.* TO ' '@'IP'
grant select,insert on *.* TO ' '@'IP'
revoke select on db1.t一般ユーザーの追加
create user mackei@localhost identified by 'admin';
GRANT ALL ON test.* TO 'mackei'@'localhost'; #
show grants for admin@localhost;
flush privileges;6、ローカルエリアネットワーク内ホストのリモート接続データベースを許可する
#
grant all on *.* to 'test'@'192.168.200.%' identified by 'test123';
#
grant all on *.* to 'test'@'192.168.200.0/255.255.255.0' identified by 'test123';
#
flush privileges;
#
mysql -utest -ptest123 -h 192.168.200.96二、表操作
1、テーブルの作成
# ：
create table (
，
)ENGINE=InnoDB DEFAULT CHARSET=utf8
not null #
default 1 # 1
auto_increment #
primary key #
constraint foreign key ( ’ ‘) references ( ) # ，
null ，
not null -
null - ， ， ，
create table tb1(
nid int not null defalut 2,
num int not null
) ， ， ， （ ）
create table tb1(
nid int not null auto_increment primary key,
num int null
)
create table tb1(
nid int not null auto_increment,
num int null,
index(nid)
)
：1、 ， （ ）。
2、
show session variables like 'auto_inc%';
set session auto_increment_increment=2;
set session auto_increment_offset=10;
shwo global variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10; ， ， ， ， ， ， 。
create table tb1(
nid int not null auto_increment primary key,
num int null
)
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
) ， ，
creat table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)2、テーブルの削除
drop table 3、テーブルを空にする
# ， delete from
truncate table 4、表の修正
# ：
alter table add
# ：
alter table drop column
# ：
alter table modify column ; --
alter table change ; -- ， # ： alter table add primary key( );
# ： alter table drop primary key;
alter table modify int, drop primary key;# ：
alter table add constraint （ ：FK_ _ ） foreign key ( ) references ( );
# ：
alter table drop foreign key # ：
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;# ：
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;#
rename table to ;
# ，altertable 。
1> ： altertable add ；
2> ， sex。
mysql> alter table student add sex char(4);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+----------+-----+------+------+
| id | name | age | dept | sex |
+----+----------+-----+------+------+
| 2 | oldsuo | 0 | NULL | NULL |
| 3 | kangknag | 0 | NULL | NULL |
| 4 | kangkang | 0 | NULL | NULL |
+----+----------+-----+------+------+
rows in set (0.00 sec)
3> suo name 。
mysql> alter table student add suo int(4) after name;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
4> qq 。
mysql> alter table student add qq varchar(15) first;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
# ，rename 。
1> : rename table to ；
2> oldsuo oldning。
mysql> rename table oldsuo to oldning;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------------------+
| Tables_in_nick_defailt |
+--------------------------+
| oldning |
| student |
+--------------------------+
rows in set (0.00 sec)
#
1> ：drop table ；
2> test 。
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)三、表内容操作
1、増加
：insert into ( , ...) values ( , , ...) #
insert into ( , ...) values ( , , ...)
#
insert into ( , ...) values ( , , ...),( , , ...)
#
insert into ( , ...) select , ... from 2、削除
：delete from delete from ;
delete from where id＝1;3、変更
：update set name ＝ 'mackei' where id>1 update set name ＝ 'nick' where id>14、調べる
：select * from select * from
select * from where id > 1
select nid,name,gender as gg from where id > 1
# as 5、条件
：select * from where id > 1 select * from where id > 1 and name != 'nick' and num = 12; #
select * from where id between 5 and 16; # id 5 16
select * from where id in (11,22,33); # id
select * from where id not in (11,22,33); # id
select * from where id in (select nid from ); # id 6、ワイルドカード
：select * from where name like '_n%' select * from where name like 'ni%' # ni （ ）
select * from where name like 's_' # s （ ）7、制限
：select * from limit 9,5; select * from limit 5; # 5
select * from limit 9,5; # 9 5
select * from limit 5 offset 9 # 9 5 8、並べ替え
：select * from order by 1 desc, 2 asc select * from order by asc # “ ”
select * from order by desc # “ ”
select * from order by 1 desc, 2 asc # “ 1” ， 2 9、グループ化
：select num from group by num select num from group by num # num
select num,nid from group by num,nid # num nid
select num,nid from where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from group by num,nid #
select num from group by num having max(id) > 10 #
：group by where ，order by count(*)、count(1) #
sum(score) #
max(score) #
min(score) #
having # having。10、連結表
：inner join . on 、 left join . on 、 right join . on
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A ， B ， null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B ， B ， null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid11、組み合わせ
：union 、 union all ，
select nickname
from A
union
select name
from B
，
select nickname
from A
union all
select name
from B四、その他の命令
1、建表文の表示
show create table \G mysql> use mackei_defailt
Database changed
mysql> create table student (
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
);
Query OK, 0 rows affected (0.05 sec)
mysql> show create table student\G #
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` ( #CREATE TABLE ，student 。
`id` int(4) NOT NULL, # ， ， 4， 。
`name` char(20) NOT NULL, # ， ， 20， 。
`age` tinyint(2) NOT NULL DEFAULT '0', # ， ， 2 ， 0。
`dept` varchar(16) DEFAULT NULL # ， ， 16， 。
) ENGINE=MyISAM DEFAULT CHARSET=latin1 # ， InnoDB， lantin1。
row in set (0.00 sec)2、表構造の表示
desc ; mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.04 sec)3、索引付けの有無を確認する
explain select * from where name ='nick' \G このコマンドを使用して、sql文に最適化の余地があるかどうかを確認します.
mysql> explain select * from student where name ='student' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ref #
possible_keys: index_name
key: index_name # ,
key_len: 20
ref: const
rows: 1 #
Extra: Using where
row in set (0.00 sec)五、データ型
新しいことを学ぶにはデータ型が欠かせない.MySQLのデータ型は簡単だ.
大きく分けて数字、文字列、時間です.
では、詳しく見てみましょう.
bit[(M)]
（101001），m （1-64）， m＝1
tinyint[(m)] [unsigned] [zerofill]
， ：
：
-128 ～ 127.
：
～ 255
： MySQL ， tinyint(1) 。
int[(m)][unsigned][zerofill]
， ：
：
-2147483648 ～ 2147483647
：
～ 4294967295
： m ， 。 ： int(5), 2 ，select ： 00002
bigint[(m)][unsigned][zerofill]
， ：
：
-9223372036854775808 ～ 9223372036854775807
：
～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
，m （ ），d 。 m 65，d 30。
：
decaimal 。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
（ ），m ，d 。
：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
：
1.175494351E-38 to 3.402823466E+38
**** ， ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
（ ），m ，d 。
：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
：
2.2250738585072014E-308 to 1.7976931348623157E+308
**** ， ****
char (m)
char ， 255 。 m 。
PS: m ， m
varchar(m)
varchars ， 255 。 m ， 。
： varchar ， ，char ， varchar 50%。 ， ，
text
text ， 65535 (2**16 1) 。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 1) characters.
enum
，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
A SET column can have a maximum of 64 distinct members.
：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 ）六、索引
1、索引の概要
インデックスはテーブルのインデックスディレクトリであり、コンテンツを検索する前にディレクトリ内のインデックス位置を調べて、クエリーデータを迅速に位置決めします.
新華字典のインデックスと理解できる.
インデックスは追加のファイルに保存されます.
2、索引の種類
一般的なインデックスの種類と機能:
≪索引のマージ|Index Merge|emdw≫:複数の単一列の索引の組合せを使用して索引を検索します.selectのデータ列は索引からのみ取得できます.データ行を読み込む必要はありません.つまり、問合せ列は構築された索引で上書きされます.
a、普通索引
# +
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)#
create index index_name on table_name(column_name)#
drop index_name on table_name;#
show index from table_name;# ： BLOB TEXT ， length。
create index ix_extra on in1(extra(32));b、一意索引
# +
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
unique ix_name (name)
)#
create unique index on ( )#
drop unique index on c、主キーインデックス
# +
create table in1(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
OR
create table in1(
nid int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
extra text,
primary key(ni1),
index ix_name (name)
)#
alter table add primary key( );#
alter table drop primary key;
alter table modify int, drop primary key;d、コンビネーションインデックス
結合インデックスは、複数のカラムを1つのインデックスに結合してクエリーします.
適用シーン:where name='Mackei'and age=22など、頻繁に複数のカラムを使用してクエリーを行います.
#
create table mess(
nid int not null auto_increment primary key,
name varchar(32) not null,
age int not null
)
#
create index ix_name_age on mess(name,age);前述のように、結合インデックスを作成した後、クエリは次の点に注意してください.
3、関連命令
#
show index from
#
set profiling = 1; # profiling
SQL... # SQL
show profiles; # 4、インデックスを正しく使用する方法
# like '%xx'， %_
select * from tb1 where name like '%n';
#
select * from tb1 where reverse(name) = 'nick';
# or
select * from tb1 where nid = 1 or email = '[email protected]';
： or ，
#
， 。
select * from tb1 where name = 999;
# !=，
select * from tb1 where name != 'nick'
： ，
select * from tb1 where nid != 123
# >，
select * from tb1 where name > 'nick'
： ，
select * from tb1 where nid > 123
select * from tb1 where num > 123
# order by
select email from tb1 order by name desc;
， ，
： ， ：
select * from tb1 order by nid desc;
#
：(name,email)， ：
name and email --
name --
email -- 5、注意事項
# select *
# count(1) count( ) count(*)
# char varchar
#
# （ ）
#
# （JOIN） (Sub-Queries)
#
# （ ） ， ： 6、実行計画
explain + SQL SQL実行情報パラメータを表示するために使用され、参照情報に基づいてSQL最適化が可能mysql> explain select * from testdb;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | testdb | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (1.67 sec)id
：mysql> explain select * from (select nid,name from tb1 where nid | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
| 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
： union null
select_type
SIMPLE
PRIMARY
SUBQUERY
DERIVED
UNION
UNION RESULT
...
table
type
， ：all >=
INDEX_MERGE ，
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF
select * from tb1 where name = 'seven';
EQ_REF primary key unique
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST
, , ,const , 。
select nid from tb1 where nid = 2 ;
SYSTEM
(= )。 const 。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys
key
key_len
MySQL
rows
mysql ------
extra
MySQL
“Using index”
mysql ， 。 index 。
“Using where”
mysql ， where ， （ ） ， ， where “Using where”。 “Using where” ： 。
“Using temporary”
mysql 。
“Using filesort”
mysql ， 。mysql ， ，explain mysql ， 。
“Range checked for each record(index map: N)”
， ，N possible_keys ， 。