Learning MySQL and MariaDB-云搜网

PART II

Database Structures

To start, let

’s create a database that will contain information

about birds and call it

rookery

. To do this, enter the following from within the

mysql

client:

CREATE DATABASE rookery;

DROP DATABASE

rookery

;

CREATE DATABASE

rookery

CHARACTER

SET

latin1

COLLATE

latin1_bin

;

Now that we

’ve created a database, let’s confirm that it’s there, on the MySQL server. To

get a list of databases, enter the following SQL statement:

SHOW DATABASES;

+——————–+

| Database |

+——————–+

|

information_schema

|

| rookery |

|

mysql

|

|

test

|

+——————–+

three other databases that were created

when MySQL was installed on the server.

Before beginning to add tables to the

rookery

database, enter the following command

into the

mysql

client:

USE

rookery

CREATE TABLE

birds

(

bird_id

INT

AUTO_INCREMENT

PRIMARY KEY

,

scientific_name

VARCHAR

(

255

)

UNIQUE

,

common_name

VARCHAR

(

50

),

family_id

INT

,

description

TEXT

);

The AUTO_INCREMENT option tells MySQL to automatically increment the value of this

field. It will start with the number 1, unless we specify a different number.

DESCRIBE birds;

+—————–+————–+——+—–+———+—————-+

+—————–+————–+——+—–+———+—————-+

| bird_id | int(11) | NO | PRI | NULL | auto_increment |

| family_id | int(11) | YES | | NULL | |

+—————–+————–+——+—–+———+—————-+

Inserting Data

INSERT INTO

birds

(

scientific_name

,

common_name

)

VALUES

(

'Charadrius vociferus'

,

'Killdeer'

),

(

'Gavia immer'

,

'Great Northern Loon'

),

(

'Aix sponsa'

,

'Wood Duck'

),

(

'Chordeiles minor'

,

'Common Nighthawk'

),

(

'Sitta carolinensis'

,

' White-breasted Nuthatch'

),

(

'Apteryx mantelli'

,

'North Island Brown Kiwi'

);

SELECT * FROM birds;

+———+———————-+——————-+———–+————-+

+———+———————-+——————-+———–+————-+

+———+———————-+——————-+———–+————-+

More Perspectives on Tables

SHOW CREATE TABLE birds \G

Table: birds

Create Table: CREATE TABLE `birds` (

`bird_id` int(11) NOT NULL AUTO_INCREMENT,

`scientific_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,

`common_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,

`family_id` int(11) DEFAULT NULL,

52 | Chapter 4: Creating Databases and Tables

`description` text COLLATE latin1_bin,

PRIMARY KEY (`bird_id`),

UNIQUE KEY `scientific_name` (`scientific_name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

CHAPTER 5

Altering Tables

use the

mysqldump

utility to make a backup of the tables you

’re altering or the whole

database.

备份表

mysqldump

—

user

=

'

russell

'

–

p

\

rookery birds

> /

tmp

/

birds

.

sql

备份数据库

mysqldump

—

user

=

'

russell

'

–

p

\

rookery

>

rookery

.

sql

恢复：

Later on, if you have a problem and need to restore the database back to where you were

at the end of a chapter, you would enter something like the following from the command

line:

mysql

—

user

=

'

russell

'

–

p

\

rookery

<

rookery

–

ch3

–

end

.

sql

The basic syntax for the ALTER TABLE is simple:

ALTER TABLE table_name changes;

ALTER TABLE

bird_families

ADD COLUMN

order_id

INT

;

To make a copy of the birds table, we

’

ll use the CREATE TABLE statement with the LIKE clause. This was covered in Chapter 4) In fact, let

’

s create the new table in the test database just to work separately on it (this isn

’

t necessary, but it

’

s a good practice to have a development database separate from the live one. To do this, enter the following in mysql on your server:

CREATE TABLE test.birds_new LIKE birds;

This DESCRIBE statement will show you the structure of the new table. Because we copied only the structure of the birds table when we created the new table, there is no data in this table. To do that, we could use an INSERT statement coupled with a SELECT like so:

INSERT INTO birds_new

SELECT * FROM rookery.birds;

This will work fine. However, there

’

s another method that creates a table based on another table and copies over the data in the process:

CREATE TABLE birds_new_alternative

SELECT * FROM rookery.birds;

This will create the table birds_new_alternative with the data stored in it. However,

if you execute a DESCRIBE statement for the table, you will see that it did not set

the bird_id column to a PRIMARY KEY and did not set it to AUTO_INCREMENT

. So in our

situation, the first method we used to create the table is preferred, followed by an INSERT INTO…SELECT statement. Enter the following to delete the alternative table:

DROP TABLE birds_new_alternative;

ALTER TABLE

birds_new

DROP COLUMN

wing_id

;

There

’s no

UNDO

statement in MySQL

ALTER TABLE

birds_new

ADD COLUMN

wing_id

CHAR

(

2

)

AFTER

family_id

;

This will put the

wing_id

column after the

family_id

in the table. Run the

DESCRIBE

statement again to see for yourself. By the way, to add a column to the first position, you

would use the keyword

FIRST

instead of

AFTER

.

FIRST

takes no column name.

ALTER TABLE

birds_new

ADD COLUMN

body_id

CHAR

(

2

)

AFTER wing_id

,

ADD COLUMN

bill_id

CHAR

(

2

)

AFTER body_id

,

ADD COLUMN

endangered

BIT

DEFAULT

b

'1'

AFTER bill_id

,

CHANGE COLUMN

common_name common_name

VARCHAR

(

255

);

ALTER TABLE

birds_new

ADD COLUMN

body_id

CHAR

(

2

)

AFTER wing_id

,

ADD COLUMN

bill_id

CHAR

(

2

)

AFTER body_id

,

ADD COLUMN

endangered

BIT

DEFAULT

b

'1'

AFTER bill_id

,

CHANGE COLUMN

common_name common_name

VARCHAR

(

255

);

In one of the columns added here, the

endangered

column, we

’re using a data type we

haven

’t used yet in this book:

BIT

.

This stores one bit, which takes a values of either set

or unset

—basically, 1 or 0.

UPDATE

birds_new

SET

endangered

=

0

WHERE

bird_id

IN

(

1

,

2

,

4

,

5

);

SELECT bird_id, scientific_name, common_name

FROM birds_new

WHERE endangered \G

*************************** 1. row ***************************

bird_id: 3

scientific_name: Aix sponsa

common_name: Wood Duck

Notice that in the

WHERE

clause of the

SELECT

statement we are selecting rows where the

endangered

column has a value. For the column data type of

BIT

, this is all that

’s needed

,

and it has the same effect as if we specified

WHERE endangered = 1

. To filter on the

reverse

—to select rows in which the bit for the

endangered

column is not set

—use the

NOT

operator like so:

SELECT

*

FROM

birds_new

WHERE NOT

endangered

\

G

如果希望endangered可以存0和1以为的值，下面语句修改endangered

列成枚举类型

。

ALTER TABLE

birds_new MODIFY

COLUMN

endangered

ENUM

(

'Extinct'

,

'Extinct in Wild'

,

'Threatened – Critically Endangered'

,

'Threatened – Endangered'

,

'Threatened – Vulnerable'

,

'Lower Risk – Conservation Dependent'

,

'Lower Risk – Near Threatened'

,

'Lower Risk – Least Concern'

)

AFTER family_id

;

Let

’s run the

SHOW COLUMNS

statement with the

LIKE

clause to see just the column settings

for the

endangered

column:

SHOW COLUMNS FROM birds_new LIKE 'endangered' \G

*************************** 1. row ***************************

Field: endangered

Type: enum('Extinct','Extinct in Wild',

'Threatened – Critically Endangered',

'Threatened – Endangered',

'Threatened – Vulnerable',

'Lower Risk – Conservation Dependent',

'Lower Risk – Near Threatened',

'Lower Risk – Least Concern')

Null: YES

Key:

Default: NULL

Extra:

Dynamic Columns

This is something that is available only in MariaDB, as of version 5.3. It

’s similar

to an

ENUM

column

。。。

Renaming a Table

RENAME TABLE

rookery

.

birds

TO

rookery

.

birds_old

,

test

.

birds_new

TO

rookery

.

birds

;

If there was a problem in doing any of these changes, an error message would be generated

and none of the changes would be made. If all of it went well, though, we should

have two tables in the

rookery

database that are designed to hold data on birds.

Let

’s run the

SHOW TABLES

statement to see the tables in the

rookery

database. We

’ll

request only tables starting with the word

birds

by using the

LIKE

clause with the wildcard,

%

. Enter the following in

mysql

:

SHOW TABLES IN rookery LIKE 'birds%';

PART III

Basics of Handling Data

CHAPTER 6

Inserting Data

The

INSERT

statement adds rows of data into a table. It can add a single row or multiple

rows at a time. The basic syntax of this SQL statement is:

INSERT INTO

table

[(

column

,

…)]

VALUES (

value

,

…), (…), …;

Replacing Data

REPLACE INTO

bird_families

(

scientific_name

,

brief_description

,

order_id

)

VALUES

(

'Viduidae'

,

'Indigobirds & Whydahs'

,

128

),

(

'Estrildidae'

,

'Waxbills, Weaver Finches, & Allies'

,

128

),

(

'Ploceidae'

,

'Weavers, Malimbe, & Bishops'

,

128

);

Query OK

,

6

rows

affected

(

.

39

sec

)

Records

:

3

Duplicates

:

3

Warnings

:

Actually, when a row is replaced using the

REPLACE

statement,

it

’s first deleted completely

and the new row is then inserted.

CHAPTER 7

Selecting Data

Limiting Results

SELECT common_name, scientific_name, family_id

FROM birds

WHERE family_id IN(103, 160, 162, 164)

AND common_name != ''

ORDER BY common_name

LIMIT 3, 2;

+————————+————————-+———–+

| common_name | scientific_name | family_id |

+————————+————————-+———–+

| American Avocet | Recurvirostra americana | 162 |

| American Golden-Plover | Pluvialis dominica | 103 |

+————————+————————-+———–+

This

LIMIT

clause has two values: the point where we want the results to begin, then the

number of rows to display. The result is to show rows 3 and 4. Incidentally,

LIMIT 3

used previously is the same as

LIMIT 0, 3

: the 0 tells MySQL not to skip any rows.

SELECT orders.scientific_name AS 'Order',

families.scientific_name AS 'Family',

COUNT(*) AS 'Number of Birds'

FROM birds, bird_families AS families, bird_orders AS orders

WHERE birds.family_id = families.family_id

AND families.order_id = orders.order_id

AND orders.scientific_name = 'Pelecaniformes'

GROUP BY Family;

+—————-+——————-+—————–+

| Order | Family | Number of Birds |

+—————-+——————-+—————–+

| Pelecaniformes | Ardeidae | 157 |

| Pelecaniformes | Balaenicipitidae | 1 |

| Pelecaniformes | Pelecanidae | 10 |

| Pelecaniformes | Scopidae | 3 |

| Pelecaniformes | Threskiornithidae | 53 |

+—————-+——————-+—————–+

We gave the

GROUP BY

clause the

Family

alias, which is the

scientific_name

column

from the

bird_families

table. MySQL returns one results set for all five families, for

one

SELECT

statement.

CHAPTER 8

Updating and Deleting Data

Updating Multiple Tables

UPDATE

prize_winners

,

humans

SET

winner_date

=

NULL

,

prize_chosen

=

NULL

,

prize_sent

=

NULL

WHERE

country_id

=

'uk'

AND

prize_winners

.

human_id

=

humans

.

human_id

;

This SQL statement checks rows in one table, associates those rows to the related rows

in another table, and changes those rows in that second table. Notice that we listed the

two tables involved in a comma-separated list. We then used the

SET

clause to set the

values of the columns related to winning a prize to NULL. In the

WHERE

clause, we give

the condition that the

country_id

Deleting Data

There

is no

UNDELETE

or

UNDO

statement for restoring rows that you delete.

Deleting in Multiple Tables

There are many situations where data in one table is dependent on data in another table.If you use

DELETE

to delete a row in one table on which a row in another table is dependent,you

’ll have orphaned data. You could execute another

DELETE

to remove that

other row, but it

’s usually better to delete rows in both tables in the same

DELETE

statement,especially when there may be many rows of data to delete.

The syntax for the

DELETE

that deletes rows in multiple tables is:

DELETE FROM

table

[,

table

]

USING

table

[, . . . ]

[WHERE

condition

];

DELETE FROM

humans

,

prize_winners

USING

humans

JOIN

prize_winners

WHERE

name_first

=

'Elena'

AND

name_last

=

'Bokova'

AND

email_address

LIKE

'%yahoo.com'

AND

humans

.

human_id

=

prize_winners

.

human_id

;

CHAPTER 9

Joining and Subquerying Data

Joining Tables

SELECT

book_id

,

title

,

status_name

FROM

books

JOIN

status_names

WHERE

status

=

status_id

;

PART IV

Built-In Functions

PART V

Administration and Beyond

CHAPTER 13

User Accounts and Privileges

GRANT ALL ON rookery.*

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@'localhost';

+———————————————————————+

| Grants for lena_stankoska@localhost |

+———————————————————————+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost' |

| GRANT ALL PRIVILEGES ON `rookery`.* TO 'lena_stankoska'@'localhost' |

+———————————————————————+

When we executed

the

CREATE USER

statement and didn

’t specify a host, we created one user account—one

with the wildcard for the host.

When we executed the

GRANT

statement to give privileges

to the same user, but with the host of localhost, a second user account was created.

SELECT User, Host

FROM mysql.user

WHERE User LIKE 'lena_stankoska';

+—————-+———–+

| User | Host |

+—————-+———–+

| lena_stankoska | % |

| lena_stankoska | localhost |

+—————-+———–+

To eliminate both of the user accounts that we created for Lena, we will have to execute

the

DROP USER

statement twice, like this:

DROP

USER

'lena_stankoska'

@

'localhost'

;

DROP

USER

'lena_stankoska'

@

'%'

;

Learning MySQL and MariaDB

相关推荐

热门推荐

分类目录