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;

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;

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