Mariadb学习笔记2

  创建数据库

MariaDB [(none)]>CREATE DATABASES;

删除数据库

MariaDB [(none)]> DROP DATABASE rookery;

创建数据库并指定数据库使用的字符集和存储方式

MariaDB [(none)]> CREATE DATABASE rookery

    -> CHARACTER SET latin1

    -> COLLATE latin1_bin;

把rookery设置为默认数据库

MariaDB [(none)]> USE rookery;

创建birds表

MariaDB [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);

查询表结构

建立6行数据

Database changed

MariaDB [rookery]> 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');

查看表的内容

新建另一个数据库

MariaDB [(none)]> CREATE TABLE birdwathers.humans

    -> (human_id INT AUTO_INCRENT PRIMARY KEY,

    -> formal_title VARCHAR(25),

    -> name_first VARCHAR(25),

    -> name_last VAARCHAR(25),

    -> email_address VARCHAR(255));

新建表

MariaDB [(none)]> CREATE TABLE birdwatchers.humans 

(human_id INT AUTO_INCREMENT PRIMARY KEY,

  formal_title VARCHAR(25), 

name_first VARCHAR(25), 

name_last VARCHAR(25), 

email_address VARCHAR(255));

插入4条数据

MariaDB [(none)]> INSERT INTO birdwatchers.humans (formal_title,name_first,name_last,email_address) VALUES ('Mr.','Russell','Dyer','ressell@mysqlresources.com'), ('Mr.','Richard','Stringer','richard@mysqlsources.com'), 

('Ms.','Rusty','Osborne', 'resty@mysqlsources.com'), 

('Ms.','Lexi','Hollar','alexandra@mysqlresources.com');

使用SHOW CREATE TABLE查看表结构

MariaDB [rookery]> SHOW CREATE TABLE birds \G

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

        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,

  `description` text COLLATE latin1_bin,

  PRIMARY KEY (`bird_id`),

  UNIQUE KEY `scientific_name` (`scientific_name`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_bin

建表bird_families它用于存放鸟的科信息

MariaDB [rookery]> CREATE TABLE bird_families

  ( family_id INT AUTO_INCREMENT PRIMARY KEY, 

scientific_name VARCHAr(255) UNIQUE, 

brief_descritpion VARCHAR(255) );

建立鸟目表

MariaDB [rookery]> CREATE TABLE bird_orders ( 

order_id INT AUTO_INCREMENT PRIMARY KEY, 

scientific_name VARCHAR(255) UNIQUE, 

brief_description VARCHAR(255), order_image BLOB

  ) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;