mysql数据库基础总结

此博文中的内容是学习mysql的笔记，现在拿出来和大家分享。另外所有内容均是在centos下实验的，其中sampdb数据库可以在http://www.kitebird.com/mysql-book/中找到，第一次发博文欢迎大家批评指导，

数据备份

mysqldump -p -u root sampdb president > mytb.db

恢复mysql -p -u rootzheng < mytb.db

使用数据库步骤：

1.创建（初始化）一个数据库 

create database sampdb;

select database();

use sampdb;设置默认数据库

show databases;列出服务器上的数据库

2.在数据库里创建各种数据表

create table table_name(column_specs);

table_name是给数据表起的名字，column_specs是该数据表的各数据列以及各种索引（可以没有）

describe(desc) president;或explain president;

或show columns frompresident;或show fields frompresident;

 

show tables;列出当前数据库的数据表

 

mysqlshow sampdb -p（命令行#）

 

mysql> create table president

    -> (

    -> last_name VARCHAR(15)NOT NULL,

    -> first_name VARCHAR(15)NOT NULL,

    -> suffix VARCHAR(15)NULL,

    -> city VARCHAR(20) NOTNULL,

    -> state VARCHAR(2) NOTNULL,

    -> birth DATE NOT NULL ,

    -> death DATE NULL

    -> );

 

mysql> create table member(

    -> member_id INT UNSIGNEDNOT NULL AUTO_INCREMENT,

    -> PRIMARY KEY(member_id),

    -> last_name VARCHAR(20)NOT NULL,

    -> first_name VARCHAR(20)NOT NULL,

    -> suffix VARCHAR(5) NULL,

    -> expiration DATE NULL,

    -> email VARCHAR(100)NULL,

    -> street VARCHAR(50)NULL,

    -> city VARCHAR(50) NULL,

    -> state VARCHAR(2) NULL,

    -> zip VARCHAR(10) NULL,

    -> phone VARCHAR(20) NULL,

    -> interests VARCHAR(255)NULL

    -> );

3.对数据表里的数据进行各种操作

 

 

数据表 table

行     row

列     column

记录  record

mysql -p -u root(以root进入数据库)                 

mysql -h host_name(localhost) -p -u user_name  database_name

 

格式

VARCHAR(n) 长度可变的字符串，最多为n个

DATE 保存日期值 CCYY-MM-DD     CC世纪 如2002-07-18

member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 

PRIMARY KEY (member_id),

 

create table student(

 ->name varchar(20) Not null,

    -> sex enum('F', 'M') NOTnull,

    -> student_id int unsignednot null auto_increment,

    -> primary key(student_id)

    -> )engine = innodb;

InnoDB引擎通过引入“外键”概念而具备了保持“引用一致性”的特点

“外”在这里的含义是“在另一个数据表里”

“外键”的含义是一个给定的键值必须与另一个数据表里的某个键值相匹配

 

 create table score (

    -> student_id int unsignednot null,

    -> event_id int unsignednot null,

    -> score int not null,

    -> primary key(event_id,student_id),

    -> index(student_id),

    -> foreign key(event_id)references grade_event (event_id),

    -> foreign key(student_id)references student(student_id)

    -> )engine=innodb;

primary　key定义可以确保我们不会创建重复的考试成绩数据行，而foreign　key定义可以确保在我们的数据行不会有在grade_event或student数据表里并不存在的虚假ID值。

 

 

create table grade_event(

    -> date DATE not null,

    -> category enum('T', 'Q')not null,

    -> event_id int unsignednot null auto_increment,

    -> primary key(event_id)

    -> )engine = innodb;

 

mysql> create table absence(

    -> student_id int unsignednot null,

    -> date date not null,

    -> primary key(student_id,date),

    -> foreign key(student_id)references student (student_id)

    -> )engine = innodb;

 

插入：

1.一次性的列出全部数据列的值：

insert into tb1_name values(value1, value2,........);

2.将多个数据行插入到数据列里去：

insert into tb1_name values(value1,value2,........),(........),..........;

3.直接对数据列进行赋值，先给出数据列的名字，在列出他的值

insert into tb1_name(col_name, col_name,...) values(value1,value2,...);

4.用包含col_name = value(而非values()列表)的set子句对数据列赋值(不允许一次插入多个数据行)。

Insert into tb1_name set col_name1=value1, col_name2=value2,......

 

通过从文件中读取来添加新行（在sampdb文件夹下）

可能出现如下错误：ERROR 1148(42000): The used command is not allowed with this MySQL version

解决办法：加入--local-infile　　即sudo mysql -p -u zheng--local-infile

１.命令行下　mysql sampdb <insert_president.sql

2.已进入mysql 　source insert_presidnet.sql;

 

以下文件里的记录项不是以insert语句而是以纯数据值的形式来存放的可利用load data语句或mysqlimport工具程序来加载　

３.已进入mysql     load data local infile 'member.txt' intotable member;

4.在命令行下使用mysqlimport客户程序　　　mysqlimport -p -u zheng--local sampdb member.txt　(p58)

查看：（from,where,groupby,order by,limit等）

1.查看tb1_name的数据表里的全部内容：

select * from tb1_name;

2.查看某数据行的某个数据列

select birth from president where last_name = 'Bush';

或多个数据项(用逗号把他们分隔开)

select birth,first_name,sex from president where last_name = 'Bush';

3.查看某数据行的全部数据

select * from president where last_name = 'Bush';

select * from score where score > 95;　　数值

select last_name, first_name from president wherelast_name='roosevelt';  字符串

select last_name, first_name, birth from president where birth <'1750-1-1';日期

select last_name, first_name, birth, state from president where birth< '1750-1-1' and （state='VA' orstate='MA');组合

select last_name, first_name, birth, state from president wherestate='VA' or state='MA';

等效select last_name,first_name, birth, state from president where state in('VA','MA');

４.与NULL进行比较（用IS NULL或IS NOT NULL或<=>）

select last_name, first_name, birth, state from president where deathis null;

select last_name, first_name, birth, state from president where death<=> null;     (death == NULL)

select last_name, first_name, birth, state from president where not(death <=> null); (death!=NULL)

5.对查询结果进行排序(order by)

select last_name, first_name, birth, state from president order bylast_name;升序

默认order by为升序

用asc(升序)或desc(降序)可改变显示的排序结果

select last_name, first_name, birth, state from president order bylast_name desc;　降序

对查询结果按多个数据列进行排序

select last_name, first_name, state from president order by state desc,last_name asc;（按州的降序，若州相同则按姓氏的升序排列）

对于包含NULL的数据行，如果设定按升序排列，他们将出现在查询结果的开头；如果设定按降序进行排列，他们将出现在查询结果的末尾。

６.NULL在前但按降序排列

select last_name, first_name, death from president order by if(death isNULL,0,1), death desc;

７.限制查询结果中的数据个数(用limit)

select last_name, first_name, birth from president order by birth limit5;

select last_name, first_name, birth from president order by birth desclimit 10, 5;(跳过前十个，输出其后的５个)

８.随机抽取若干个（联合使用limit和order by和rand()子句）

select last_name, first_name, birth from president order by rand()limit 1;（随机抽取一个）

9.format格式控制

select format(sqrt(25), 3);        25的平方根，保留３位小数点

10.concat(str1, str2,........)函数

返回结果为连接参数产生的字符串。如果任一行参数为NULL，则此行返回值为NULL

select concat(first_name,' ',last_name,'  ',death),concat(city,', ',state) frompresident;

11.如果输出列的值是某个表达式的结算结果，这个表达式就会成为这个输出列的名字并用作它在输出结果中的标题，但用As name起别名（alias）

注：如果输出列的别名里包含空格符，就必须把它放到一组引号里

select concat(first_name,' ',last_name) as name,concat(city,', ',state)as birthplace from president;

select concat(first_name,' ',last_name) as 'presidentname',concat(city,', ',state) as 'place of birth' from president;

为数据列起别名时，关键字as可以省略：

select 1, 2 as two, 3 three;

select first_name last_name from president;

日期

１.select * fromgrade_event where date = '2008-10-01';等于某日期

2.select last_name,first_name,death from president where death>='1970-01-01'and death < '1980-01-01'; 　在某个日期范围

3.使用year(),month(),dayofmonth()分离年月日。

select last_name,first_name,birth from president where month(birth) =3;

select last_name,first_name,birth from president where month(birth)=3and dayofmonth(birth)=29;

4.用monthname()可以使用英文名称

select last_name,first_name,birth from president where monthname(birth)= 'march';

５.使用curdate()函数，得到今天的日期值

select last_name,first_name,birth from president where month(birth) =month(curdate()) and dayofmonth(birth) = dayofmonth(curdate());

6.timestampdate()函数用来计算两个日期值之间的时间间隔

select last_name,first_name,birth,death,timestampdiff(year,birth,death)as age from president where death is not null order by age desc limit 5;

7.to_days（）函数将日期转换为天数

select to_days('2015-01-01') – to_days(curdate());

也可以使用timestampdate()实现selecttimestampdiff(day,curdate(),'2015-01-01');

８.用date_add()或date_sub()函数计算输入日期值和时间间隔的新日期值

select date_add('1970-01-01', interval 10 month);　　interval间隔

模式匹配

模式匹配：在没有给出精确比较值的情况下把有关的数据查出来

要用到特殊的操作符(LIKE和NOT LIKE),还需要提供通配字符的字符串(“_”只能匹配一个字符,”%”能匹配任何多个字符序列)

１.selectlast_name,first_name from president where last_name like 'w%';列出姓氏以字母W和w开头的总统的名字

select last_name,first_name from president where last_name = 'w%';  列出姓氏是W%和w%的总统名字

select last_name,first_name from president where last_name like '%w%';列出名字中包含W和w的总统的名字

select last_name,first_name from president where last_name like '____';列出姓氏有且仅由４个字母构成的总统名字

 

 

 

 

×××××××××××××××××××××××××××××××××××××××××××××××××××××××××

×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

设置和使用sql变量             

变量的命名语法是“@变量名”，赋值语法是在SELECT语句里使用一个“＠变量名 := 值”形式的表达式。

select @birth := birth from president where last_name = 'Jackson';

select last_name,first_name,birth from president where birth <@birth order by birth;

可以用set语句来对变量赋值

set @today = curdate();

set @today := curdate();

set @one_week_ago := date_sub(@today,interval 7 day);

select @today,@one_week_ago;

生成统计信息

１.关键字distinct能把在查询结果中重复出现的数据行清除掉　即唯一不同的值

select distinct state from president order by state;

2.count(*)把查询到底选取了多少行显示出来

select count(*) from member;

３.count(*)的统计结果是被选中的数据行的总数，而count(数据列)值则只统计全体非NULL值的个数。

select count(distinct state) from president order by state;　　　查询总共有多少个州曾经有总统出生

select count(*),count(email),count(expiration) from member;

 

group by语句用于结合合计函数，根据一个或多个列对结果集进行分组

select state,count(*) from president group by state;　　有总统出生的各个州的总统个数统计

select sex,count(*) from student group by sex;     　　班上男女个数

能对group by的查询结果进行排序

select state,count(*) as count from president group by state order bycount desc;对输出列是某个汇总函数的计算结果时，不能直接在order by子句引用函数，而应该先给这个输出列取一个别名。

可以在order by子句里引用输出列在输出结果中的位置（最好别用）

select state,count(*) from president group by state order by 2 desc;

各个月份出生的总统的人数

select month(birth) as month,monthname(birth) as name,count(*) as countfrom president group by name order by month;

查询出生人数最多的四个州

select state,count(*) as count from president group by state order bycount desc limit 4;

 

查找在某个范围内的记录用having语句

select state, count(*) as count from president group by state havingcount > 1 and count < 5 order by count desc;

 

汇总函数count()  min() max()  sum() avg()

select event_id,min(score) as minimum, max(score) as maximum,max(score) - min(score) + 1 as span, sum(score) as total,  avg(score) as average, count(score) as countfrom score group by event_id;

在世的总统的平均年龄

select avg(timestampdiff(year,birth,curdate())) as 'average age' frompresident where death is null;

 

select last_name,first_name ,timestampdiff(year,birth,death) as agefrom president where death is not null and timestampdiff(year,birth,death) <60;  寿命小于60的总统　　另外where子句中不能使用alias其别名　官方解释Standard SQL disallowsreferences to column aliases in a WHERE clause. Thisrestriction is imposed because when the WHERE clause isevaluated, the column value may not yet have been determined.

 

 

 

 

从多个数据库中检索信息

方法：1.联结（join） 把一个数据表与另一个数据表中的信息结合起来才能得到查询结果　即联结操作是通过把两个或多个数据表里的同类数据进行匹配而完成的

2.将select语句嵌套在另一个select语句里,前者叫子查询。

select student_id,date,score,category from grade_event inner join scoreon  grade_event.event_id = score.event_idwhere date = '2012-09-23';

 

select  student.name,grade_event.date,score.score,grade_event.categoryfrom grade_event inner join score inner join student  on grade_event.event_id=score.event_id andscore.student_id=student.student_id where grade_event.date='2012-09-23';

 

LEFT JOIN左联结

 关键字会从左表 (student) 那里返回所有的行，即使在右表 (absence) 中没有匹配的行。

select student.student_id,student.name, count(absence.date) as absencesfrom student left join absence on student.student_id = absence.student_id groupby student.student_id;

left join将使MySQL为联结操作中第一个数据表（即名称出现在关键字LEFT JOIN左边的那个数据表）里的每一个中选数据行生成一个数据行。

将与考试日期与学生性别的每一种组合相对应的考生人数和平均分数统计出来

select grade_event.date,student.sex,count(score.score) as count,avg(score.score) as average from grade_event inner join score inner joinstudent on grade_event.event_id = score.event_id andscore.student_id=student.student_id group by grade_event.date,student.sex;

计算每位学生的期末成绩

select student.student_id,student.name, sum(score.score) astotal,count(score.score) as n from grade_event inner join score inner joinstudent on grade_event.event_id = score.event_id andscore.student_id=student.student_id group by score.student_id order by total;

出生在同一城市的总统

select p1.last_name,p1.first_name,p1.city,p1.state from president as p1inner join president as p2 on p1.city = p2.city and p1.state = p2.state where(p1.last_name <> p2.last_name or p1.first_name <> p2.first_name)order by state,city,last_name;

****************************************************************************************************************************************************

子查询

找出全勤的学生

select * from student where student_id not in(select student_id from absence);

找出哪些总统出生在Andrew Jackson之前

select last_name,first_name,birth from president where birth <(select birth from president where last_name = 'jackson' and first_name ='andrew');

 

 

删除或更新现有数据（delete和update）

delete语句格式 delete fromtb1_name where which rows to delete;

             delete from tb1_name;删掉整个数据表

如：delete frompresident where state='oh';

update语句基本格式：

update tb1_name set which columns to change where which rows to update;

where子句可选

update student set name='george';

 

update member set expiration='2009-7-20' where last_name='York' andfirst_name='Jerome';

一次设置多个

update member set email='jeromey@aol.com',street='123 Elm St',city='Anytown',state='NY',zip='01003' where last_name='York' andfirst_name='Jerome';

数据列允许使用NULL值时，可以把它设置为NULL

update member set expiration=null where last_name='York' andfirst_name='Jerome';

 

 

启动mysql的简便方法

1.把连接参数保存到一个选项文件里

2.利用shell的命令历史功能重复输入命令

3.利用shell别名或脚本为mysql命令行定义一个快捷键

shell是bash时　　alias sampdb='sudo mysql -p-u zheng'

查看每个用户所对应的shell       cut-d ":" -f 1,7 /etc/passwd

 

 

几种sql_mode:

strict_all_tables

strict_trans_tables

traditional

ansi_quotes

pipes_as_concat

ansi

设置方式：

命令行时

--sql-mode=”traditional”

--sql-mode=”ansi_quotes,pipes_as_concat”

运行时改变SQL模式可以使用SET语句来设置sql_mode系统变量

set sql_mode = 'traditional';

做全局性设置

set global　sql_mode='traditional';

查看当前的会话级或全局级sql模式当前值

select @@session.sql_mode;

select @@global.sql_mode;

如果当前没有启用任何模式，则返回一个空值

 

database plusplus

数据库级的语句

1. use用来选定一个默认数据库

2. create database　用来创建数据库

create database语句有好几种可选的子句，它的完整语法如下：

create database [if not exists] db_name

     [character set charset][collate collation];

可以加上if not exists子句来避免数据库已存在的错误:

create database if not exists db_name;

create database mydb character set utf8 collate utf8_icelandic_ci;

查看数据库的定义

show create database sampdb;

3. drop databases　用来删除数据库

drop database db_name;

4. alter database　用来改变数据库的全局特性

alter database [db_name] [character set charset] [collate collation];

 

 

 

table plus plus

show engines语句可以查看服务器都支持哪些存储引擎　show engines;

transaction 事务

用下列语句查看有那些支持事务处理的存储引擎可供使用

select engine from information_schema.engines where transactions ='yes';

mysql 允许创建、删除数据表或改变其结构相应的sql语句如下：

1.create table

create table if not exist tb_name(.........);

2.drop table

drop table tb1_name;

drop table if exists tb1_name;

3.alter table

 

查看数据表信息（存储引擎等）show create tablemydb;

改变mytb数据现在的存储引擎　　alter table mytb1 engine =innodb;

创建临时数据表:

create temporary table tb1_name.....;

服务器在客户会话结束时自动删除temporary数据表

删除使用drop temporarytable语句

 

1.创建数据表的一个空白副本(复制其所有结构)

create (temporary)  tabelnew_tb1_name like tb1_name;

然后插入数据：insert intonew_tb1_name select * from tb1_name;

实例（创建sampdb.student_f的临时副本，并将数据表中的女学生插入）

create temporary table student_f like student;

insert into student_f select * from student where sex = 'f';

2.如果不关心数据表是否保留了原始数据表里的数据列的精确定义

，可以使用create(temporary) table ….. select语句

 

用cast()函数强制在新的数据表使用特定属性

create table mytb1 select

    -> cast(1 as unsigned) asi,

    -> cast(curtime() as time)as t,

    -> cast(PI() asdecimal(10,5)) as d;

删除数据表

drop table tb1_name;

如果存在删除

drop table if exists tb1_name;  (不存在时产生warning，用show warning查看 )

删除临时数据表

drop temporary table tb1_name;

*********************************************************************************************************************************************************************************************************************************************************************************

添加索引

使用alter

alter table tb1_name add index_name (index_columns);

alter table tb1_name add unique index_name (index_columns);

alter table tb1_name add primary key (index_columns);

alter table tb1_name add fulltext index_name (index_columns);

alter table tb1_name add spatial index_name (index_columns);

其中tb1_name是想要添加索引的数据列的名字（索引本身的名字index_name为可选项），index_columns是想要添加索引的一个或多个数据列。

索引由多个数据列构成，要用逗号把他们的名字隔开。

使用cteate index语句添加索引：(除primary key)

create index index_name on tb1_name (index_columns);

create unique index index_name on tb1_name (index_columns);

create fulltext index index_name on tb1_name (index_columns);

create spatial index index_name on tb1_name (index_columns);

创建数据表的同时为它添加索引

create table tb1_name

{

     ….....

     index index_name(index_columns),

     unique index_name(index_columns),

     primary key　(index_columns),

     fulltext index_name (index_columns),

     spatial index_name(index_columns),

     …...

};

类似alter table语句

 

下面两条语句类似

create table mytb3

    -> (i int not null primarykey,

    -> j char(10) not nullunique

    -> );

create table mytb4

    -> (i int not null,

    -> j char(10) not null,

    -> primary key(i),

    -> unique(j)

    -> );

用memory数据表（默认索引类型是HASH）进行范围比较（如id < 100）时使用using btree子句创建一个btree索引

create table namelist

    -> (id int not null,

    -> name char(100),

    -> index using btree(id)

    -> )engine = memory;

如果只对某个字符串数据列的一个前缀编索引，在索引定义里命名数据列的语法是col_name(n)而不是简单的col_name.

Create table addresslist

{

     name char(30) not null,

     address binary(60) not null,

     index (name(10)),

     index(address(15))

};

删除索引

1.使用drop删除索引，必须给出要删除的索引的名字

drop index index_name on tb1_name;

删除primary key必须以一个带引号的标识符的形式给出primary

drop index 'primary' on tb1_name;(因为只有一个primary key)

2.用alter table

上述两条语句与下等价

alter table tb1_name drop index index_name;

alter table tb_name drop primary key;

 

ALTER TABLE语句的用途

改变存储引擎、创建和删除索引、重新命名数据表、添加或删除数据列、改变数据列的数据类型等

ALTER TABLE语句的语法：

ALTER TABLE　tb1_name action[, action] …..;

每个action代表一个你想对数据表进行的修改

注：如果需要在使用ALTER TABLE语句之前查看一下数据表的当前定义，可以执行一条SHOW CREATE TABLE语句。

用change或modify子句改变莫个数据列的数据类型

create table try_alter

(

     my_try smallint unsigned

);

改为mediumintunsigned    

1.alter table try_alter modify my_try mediumint unsigned;

2.alter table try_alter change my_try my_try smallint unsigned;

change子句能够（而modify子句不能）做到的事情是在改变其数据类型的同时重新命名一个数据列

如将my_try 改为m

alter table try_alter change my_try m smallint unsigned;

让数据表改用另一个存储引擎。

alter table tb1_name engine = engine_name;

重新命名一个数据表

1.用rename子句给数据表起一个新名

alter table tb1_name rename to new_tb1_name;

2.用rename table语句来重新命名数据表

rename table old_name to new_name;

用rename table可以一次命名多个，如下交换两个数据表的名字

rename table t1 to tmp, t2 to t1, tmp to t2;

将数据表t从mydb数据库移到mytest数据库

alter table mydb.t rename to mytest.t;

rename table mydb.t to mytest.t;

 

外键

可以建立数据表之间的依赖关系和维护它们的一致性

 

父表 包含原始键值的数据表

子表 引用父表中的键值的相关数据表

子表中定义一个外键的语法

[CONSTRAINT constraint_name]

FOREIGN  KEY  [fk_name] [index_columns]

        REFERENCES tb1_name  (index_columns)

        [ON DELETE action]               //若无则拒绝父表删除子表中有的数据

        [ON UPDATE action]             //若无 其默认行为是拒绝插入或更新其外键值在父表索引里没有任何匹配的子表数据行,并阻止仍有子表数据行在引用着它们的父表索引值被更新

        [MATCH  FULL  |MATCH PARTIAL | MATCH SIMPLE]

 

 

ON DELETE action可选的子句

ON DELETE NO ACTION 和 ON DELETERESTRICT 和省略ON DELETE效果一样

ON DELETE CASCADE  在删除父表数据行时,子表里与之相关联的数据行也将被删除

等等

 

ON UPDATE子句 用来设定当父表里的数据行更新时子表应该发生什么事

可供选择的action值及其效果与ON   DELETE子句相同

 

建立外键必须遵循的规则

1.子表数据列和父表数据列都必须有索引(innoDB存储引擎会自动为子表创建非唯一索引)

2.父表和子表索引

3.不能对外键关系里的字符串数据列编制索引

 

mysql> create table parent

    -> (

    -> par_id int not null,

    -> primary key(par_id)

-> )engine=innodb;

 

mysql> create table child

   -> (par_id int not null,

   -> child_id int not null,

   -> primary key(par_id, child_id),

   -> foreign key(par_id) references parent (par_id)

   -> on delete cascade

   -> on update cascade

   -> )engine=innodb;

 

这个例子在定义外键时使用了ON DELETE CASCADE子句,它指定当parent数据表里的某个数据行被删除时,MySQL将自动地从child数据表里把有匹配par_id值的数据行也删掉.

ON UPDATE CASCADE子句表明:如果parent数据表的某个数据行的par_id值被改变了,MySQL将自动地把child数据表里的所有匹配的par_id值也改成新值

 

primary key与unique的区别

primary key不允许为null

unique索引是也是独一无二的,但null除外

 

 

MySQL数据类型

MySQL能够识别和使用的数据值

1.数值

2.字符串值

3.日期/时间值

4.坐标值

5.空值(NULL)

 

数值包括 整数值 定点或浮点数值  位字段值

十六进制的表示方法  X’val’ -- val的数字的个数必须是偶数

     另一种表示方法0xval    若不为奇数个,则自动在前面加0

如oxa被视为ox0a

位字段值

位字段值(bit-field value)可以写成b’val’或0bval 比如 b’1001’和0b1001是十进制里的9

结果集里的BIT值将被显示为一个二进制字符串,可以采用加上一个零或使用CAST()函数来将结果转换为整数再输出

如: select  b'1001' + 0, cast(b'1001' as unsigned);

 

NUL表示零值字节(\0),而NULL代表的是”没有取值”

与字符串有关的操作

比较操作符 < <=<>  >= LIKE

排序操作  ORDER BY MIN()  MAX()

分组操作  GROUP BY   DISTINCT

 

可以使用CHARSET()或COLLATION()函数来查看字符串所使用的字符集和排序方式

 

MySQL的数值数据类型包括整数 定点数 浮点数和位值

数值数据类型

tinyint    smallint   mediumint  int    bigint   decimal float

double   bit

字符串数据类型

char         固定长度的非二进制(字符)字符串

varchar    可变长度的非二进制字符串

binary      固定长度的二进制字符串

varbinary 可变长度的二进制字符串

tinyblob    非常小的blob(二进制大对象)

blob           blob(binary largeobject)

mediumblob  中等大小的blob

longblob     大blob

tinytext      非常小的非二进制字符串

text             小文本字符串

mediumtext 中等大小的非二进制字符串

longtext       大的非二进制字符串

enum           枚举集合

set               集合

日期/时间数据类型

date       日期值,格式为’CCYY-MM-DD’

time       时间值,格式为’hh:mm:ss’

datetime 日期加时间值,格式为’CCYY-MM-DD hh:mm:ss’

timestamp 时间戳值,格式为’CCYY-MM-DD hh:mm:ss’

year          年分值,格式为CCYY或YY

 

属性

zerofill  可以在规定位数不足时自动前补零,自动unsigned

create table mytb1(my_zerofill int(5) zerofill);

insert into mytb1 values(1),(100),(1000),(10000),(1000000);

select my_zerofill from mytb1;

auto_increment

每个数据表最多只有一个auto_increment数据列,这个数据列还应该具备not null属性和索引,一般来说,最好把auto_increment数据列声明为一个primary key或一个unique索引,并且加上unsigned

mysql> create table ai1   

-> (i int unsigned not null auto_incrementprimary key);

mysql> create table ai2

   -> (i int unsigned not null auto_increment unique);

mysql> create table ai3

    -> (i int unsigned notnull auto increment,primary key(i));

mysql> create table ai4

(i int unsignednot null auto_increment,unique(i));

default属性

mysql> create table t

    -> (

    -> i1 int default -1,

    -> i2 int default 1,

    -> i3 int default null

    -> );

smallint(3)  显示宽度为3

char和varchar

char和varchar数据类型的主要区别在于它们的长度是固定的还是可变的,以及它们如何对待尾缀的空格

1.char是一种固定长度的类型,而varchar是一种长度可变的类型

2.char(M)数据类,如果某个值的长度小于M个字符,mysql在把它存入该数据列时将用空格不足到M个字符长,检索时忽略追加的空格,而对于varchar(M)数据列,尾缀空格在存储和检索时都会被保留

对varchar

一个长varchar数据列需要2个字节来存放字符串值的长度,这两个字节计算在数据行总长度之内

case语句

select case when wbc <= 100 then ‘0-100’

           when wbc <= 300 then ‘101-300’

           else ‘>300’  end as ‘wbc category’

from ...

enum类型

mysql> create table e_table

    -> ( eenum('jane','fred','will','marcia')

-> );

 

mysql> insert int e_table

   -> values('jane'),('fred'),('will'),('marcia'),(null);

 

mysql> select e,e+0,e+1,e*3 from e_table;

mysql> select e from e_table where e='will';

mysql> select e from e_table where e=3;

空字符串作为enum数据列的成员

mysql> create table t2 

    -> (

    -> e enum('a','','b')

    -> );

mysql> insert into t2 values('a'),(''),('b'),('x');

mysql>select e, e+0 from t;

set数据列对应set值里的一个位,第一个成员对应着第0位,第二个成员对应第1位,以此类推,换句话说,set成员对应数值都是2的幂,空字符串对应着数值为0的set成员(bin()函数)

mysql> create table s_table(s set('table','lamp','chair','stool'));

mysql> insert into s_tablevalues('table'),('lamp'),('chair'),('stool'),(''),(null);

mysql> select s,s+0,bin(s+0) from s_table;

一个值由多个set成员组成

mysql> insert into s_table values('lamp,chair');

排序

mysql> select s from s_table order by s;

mysql>select e,e+0 from e_table order by e;

将enum转换为字符串进行排序

mysql> select cast(e as char) as e_str from e_table order by e_str;

 

字符串特有的属性是character set(或charset)和collate,分别制定一种字符集和一种排序方式

show character set语句可以查出当前可用的字符集

show collation 语句可以把每一种字符集支持的排序方式列出来

mysql> create table mytb2

    -> (

    -> c1 char(10),

    -> c2 char(40) characterset latin2,

    -> c3 char(10) collatelatin1_german1_ci,

    -> c4 binary(40)

-> )character set utf8;

mysql将依次根据下列原则为它指定一个字符集

1.数据列中是否定义

2.数据表的定义里是否定义了数据表级的字符集选项

3.使用数据库的字符集作为数据表的默认字符集

除blob和text类型外,其他的字符串数据类型都可以用default子句设定一个默认值

改变数据表的默认字符集和排序方式

alter table mytb3 character set latin1collate latin1_general_ci;

mysql> create table t3

   -> (

   -> dt datetime,

   -> d date,

   -> t time

   -> );

mysql> insert  into t3(dt, d, t) values(now(),now(),now());