Chapter 3 Tutorial

Chapter 3 Tutorial

mysql --help
mysql -?

3.1 Connecting to and Disconnecting from the server

mysql -h host -u user -p

disconnect

quit
\q
Ctrl+D

3.2 Entering Quries

select version(), current_date;

illustrates several things:

• statement followed by a semicolon. (some exceptions , e.g. quit, use)
• mysql sends it to the server
• mysql labels the column using the expression itself
• shows how many rows were returned and how long the query took to execute.

keywords may be entered in any lettercase.

use mysql as a simple calculator:

select sin(pi()/4), (4+1) * 5;

enter multiple statements on a single line. end each one with a semicolon:

select version(); select now();

mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.

select
user()
,
current_date;

cancel query by typing \c:

select
user()
\c

prompt	meaning
mysql>	ready for new query
->	waiting for next line
'>	waiting for completion of a string that began with a single quote (’)
">	同上
`>	同上
/*>	同上

3.3 creating and using a database

show databases;
use exercise;

use statement must be given on a single line.

grant all on menagerie.* to 'your_mysql_name'@'your_client_host';

设置权限我也不熟，先往下面看，后面有讲

3.3.1 creating and selecting a database

create database menagerie;

Under Unix, database names are case-sensitive, this is also true for table names.

use menagerie
or
mysql -h host -u user -p menagerie
select database();

3.3.2 creating table

show tables;

The hard part is deciding what the structure of your databases should be: what tables you need and what columns should be in each of them.

存储日期，不要存数字

create table pet(
name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1),
birth date,
death date
);

describe pet;
system clear;

3.3.3 Loading Data into a Table

load data local infile '/path/pet.txt' into table pet;

if you created the file on windows:

load data local infile '/path/pet.txt' into table pet lines terminated by '\r\n';

从文件里面加载数据，我实验失败，我也不想用这种办法.实验失败的解决办法在第六章有，不管了，先往下看

insert into pet values ('Puffball','Diane','hamster','f','1999-03-30',null);

3.3.4 Retrieving Information from a Table

select what_to_select
from which_table
where conditions_to_satisfy;

3.3.4.1 Selecting All Data

select * from pet;

fix error

delete from pet;
load data local infile 'pet.txt' into table pet;

or

update pet set birth = '1989-08-31' where name = 'Bowser';

3.3.4.2 Selecting Particular Rows

select * from pet where name = 'Bowser';

String comparisons are case-insensitive.
AND has higher precedence than OR.

3.3.4.3 Selecting Particular Columns

select name, birth from pet;

select distinct owner from pet;

3.3.4.4 Sorting Rows

select name, birth from pet order by birth;

On character type columns, sorting in a case-insensitive. force a case-sensitive sort by using binary like so: order by binary col_name.
Default sort order is ascending

select name, birth from pet order by birth desc;
select name, species, birth from pet order by species, birth desc;

3.3.4.5 Date Calculations

select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet;
select name, birth, death, timestampdiff(year, birth, death) as age from pet where death is not null order by age;

null is a special value

select name, birth, month(birth) from pet;
select name, birth from pet where month(birth) = 5;
select name, birth from pet where month(birth) = month(date_add(curdate(), interval 1 month));
select name, birth from pet where month(birth) = mod(month(curdate()), 12) + 1;

if a calculation uses invalid dates, the calculation fails and produces warnings:

select '2018-10-31' + interval 1 day;
select '2018-10-32' + interval 1 day;
show warnings;

3.3.4.6 Working with NULL Values

null means “a missing unknown value”.

select 1 is null, 1 is not null;

Cannot use arithmetic comparison operators such as =, <, <> to test for null.

select 1 = null, 1<>null, 1< null, 1> null;

• In MySQL, 0 or null means false and anything else means true.
• Two null values are regarded as equal in a group by.
• When doing an order by, null values are presented first asc.

select 0 is null, 0 is not null, '' is null, '' is not null;

It is possible to insert a zero or empty string into a not null column.

3.3.4.7 Pattern Matching

_ match any single character
% match an arbitrary number of characters (including zero characters).
Do not use = or <> when you use SQL patterns. Use the like or not like comparison operators

select * from pet where name like 'b%';
select * from pet where name like '%fy';
select * from pet where name like '%w%';
select * from pet where name like '_____';

Use extended regular expressions, use the regexp_like(), or regexp or rlike operators,

some characteristics of extended regular expressions:

• . matches any single character.
• A character class […] matches any character within the brackets. To name a range of characters, use a dash.
• • matches zero or more instances of the thing preceding it.
• succeeds if the pattern matches anywhere in the value being tested.
• Use ^ at the beginning or $ at the end of the pattern.

select * from pet where regexp_like(name, '^b');
select * from pet where regexp_like(name, '^b' collate utf8mb4_0900_as_cs);
select * from pet where regexp_like(name, binary '^b');
select * from pet where regexp_like(name, '^b', 'c');

不太清楚跟个’c’ 为什么就能大小写敏感了？ c match-control character 是个什么鬼我也不知道

select * from pet where regexp_like(name, 'fy$');
select * from pet where regexp_like(name, 'w');
select * from pet where regexp_like(name, '^.....$');
select * from pet where regexp_like(name, '^.{5}$');

3.3.4.8 Counting Rows

select count(*) from pet;
select owner, count(*) from pet group by owner;
select species, count(*) from pet group by species;
select sex, count(*) from pet group by sex;
select species, sex, count(*) from pet group by species, sex;
select species, sex, count(*) from pet where species = 'dog' or species = 'cat' group by species, sex;
select species, sex, count(*) from pet where sex is not null group by species, sex;

set sql_mode = 'only_full_group_by';
select owner, count(*) from pet;
Error 1140 (42000): owner is nonaggregated column.

If only_full_group_by is not enabled, the server is free to select the value from any row:

set sql_mode = '';
select owner, count(*) from pet;

sql_mode 我也不清楚是什么模式， 十二章讲 group by 的知识点，往下面走

3.3.4.9 Using More Than one Table

create table event (
name varchar(20),
date date,
type varchar(15),
remark varchar(255));

load data local infile 'event.txt' into table event;

I use insert into:

nsert into event (name, date, type, remark) values (Puffball, 2000-01-01, litter, '4
kittens, 3 female, 1 male');

calculate ages at which each pet had its litters. (有宝宝时多大了)

select pet.name, timestampdiff(year, birth, date) as age, remark from pet inner join event on pet.name = event.name where event.type = 'litter';

Sometimes it is useful to join a table to itself.
比如你想给你的宠物配种。

select p1.name, p1.sex, p2.name, p2.sex, p1.species from pet as p1 inner join pet as p2 on p1.species = p2.species and p1.sex = 'f' and p1.death is null and p2.sex = 'm' and p2.death is null;

3.4 Getting Information About Databases and Tables

To find out which database is currently selected.

select database();
show tables;
describe pet;

我感觉 show full columns from pet 更好用。

show create table pet;
show index from pet;

3.5 Using mysql in Batch Mode

To do this, put the statements you want to run in a file, then tell mysql to read its input from the file;

mysql < batch-file
mysql -e "source batch-file"

If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line option.

Why use a script?

• If you run a query repeatedly
• You can generate new queries from existing ones that are similar by copying and editing script files.
• Batch mode can also be useful while you’re developing a query, if you make a mistake, you do not have to retype everything.
• if you hava a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen:

mysql < batch-file | more

• You can catch the output in a file for further processing:

mysql < batch-file > mysql.out

• You can distribute your script to other people so that they can also run the statements.
• Some situations do not allow for interactive use, for example, when you run a query from a cron job.

The default output format is different when you run mysql in batch mode than when you use it interactively.

select distinct species from pet;

If you want to get the interactive output format in batch mode, use mysql -t.
-t ===> --table display output in table format.
-v ===> --verbose produce more output about what the program does.

source filename
\. filename

3.6 Examples of Common Queries

create table shop (
article int unsigned default '0000' not null,
dealer char(20) default '' not null,
price decimal(16, 2) default '0.00' not null,
primary key (article, dealer));

insert into shop (article, dealer, price) values
(1, 'A', 3.45), (1, 'B', 3.99),(2, 'A', 10.99), (3, 'B', 1.45), (3, 'C', 1.69), (3, 'D', 1.25), (4, 'D', 19.95);

3.6.1 The Maximum Value for a Column

select max(article) as article from shop;

3.6.2 The Row Holding the Maximum of a Certain Column

select article, dealer, price from shop where price=(select max(price) from shop);

or

select s1.article, s1.dealer, s1.price from shop s1 left join shop s2 on s1.price < s2.price where s2.article is null;

or

select article, dealer, price from shop order by price desc limit 1;

3.6.3 Maximum of Column per Group

select article, max(price) as price from shop group by article order by article;

3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

select article, dealer, price from shop s1 where price = (select max(s2.price) from shop s2 where s1.article = s2.article) order by article;

关联子查询低效

select s1.article, dealer, s1.price from shop s1 join (select article, max(price) as price from shop group by article) as s2 on s1.article = s2.article and s1.price = s2.price order by article;

or

select s1.article, s1.dealer, s1.price from shop s1 left join shop s2 on s1.article = s2.article and s1.price < s2.price where s2.article is null order by s1.article;

or

with s1 as (
    select article, dealer, price,
        rank() over (partition by article
                        order by price desc
                        ) as `rank`
    from shop
 )
select article, dealer, price from s1 where `rank` = 1 order by article;

这个我是一点不懂， with 可能类似 view， rank() over, partition by 怎么这么多不懂

3.6.5 Using User-Defined Variables

select @min_price:=min(price), @max_price:=max(price) from shop;
select * from shop where price = @min_price or price = @max_price;

3.6.6 Using Foreign Keys

create table person (
    id smallint unsigned not null auto_increment,
    name char(60) not null,
    primary key (id)
);
create table shirt (
    id smallint unsigned not null auto_increment,
    style enum('t-shirt', 'polo', 'dress') not null,
    color enum('red', 'blue', 'orange', 'white', 'black') not null,
    owner smallint unsigned not null references person(id),
    primary key (id)
);
insert into person values (null, 'Antonio Paz');
select @last:=last_insert_id();
insert into shirt values
(null, 'polo', 'blue', @last),
(null, 'dress', 'white', @last),
(null, 't-shirt', 'blue', @last);
insert into person values (null, 'Lilliana Angelovska');
insert into shirt values
(null, 'dress', 'orange', @last),
(null, 'polo', 'red', @last),
(null, 'dress', 'blue', @last),
(null, 't-shirt', 'white', @last);

select s.* from person p inner join shirt s on s.owner = p.id where p.name like 'Lilliana%' and s.color <> 'white';

show create table shirt\G

3.6.7 Searching on Two Keys

select field1_index, field2_index from test_table where field1_index = '1' or field2_index = '1'

select field1_index, field2_index from test_table where field1_index = '1' union
select field1_index, field2_index from test_table where field2_index = '1';

3.6.8 Calculating Visits Per Day

create table t1 (
year YEAR,
month int unsigned,
day int unsigned);
insert into t1 values(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
select year, month, bit_count(bit_or(1<<day)) as days from t1 group by year, month;

3.6.9 Using auto_increment

create table animals (
    id mediumint not null auto_increment,
    name char(30) not null,
    primary key (id)
);
insert into animals (name) values
('dog'),('cat'), ('penguin'),('lax'),('whale'),('ostrich');
select * from animals;
insert into animals (id,name) values (0, 'groundhog');
insert into animals (id, name) values (null, 'squirrel');
insert into animals (id, name) values (100, 'rabbit');
insert into animals (id, name) values (null, 'mouse');

last_insert_id() function are connection-specific.

For a multiple-row insert, last_insert_id() return the auto_increment key from the first of the inserted rows.

alter table tbl auto_increment = 100;

create table animals2 (
    grp enum('fish', 'mammal', 'bird') not null,
    id mediumint not null auto_increment,
    name char(30) not null,
    primary key(grp,id)
) engine=MyISAM;
insert into animals2 (grp, name) values
('mammal', 'dog'),
('mammal', 'cat'),
('bird', 'penguin'),
('fish', 'lax'),
('mammal', 'whale'),
('bird', 'ostrich');
select * from animals2 order by grp, id;

3.7 Using MySQL with Apache

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

我也不知道是什么意思，直接复制过来的

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'