Mariadb学习笔记2，mariadb学习笔记

Mariadb学习笔记2，mariadb学习笔记

 创建数据库
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;