MySQL学习笔记2——MySQL基础操作

MySQL基础操作

创建/查看/删除数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification [,...ect]]];    IF NOT EXISTS   //可选如果此名的数据库不存在则创建,存在则不创建但继续执行语句,不报错,也不覆盖原数据库    create_specification: [DEFAULT] CHARACTER SET characte_name     //设置编码格式                            [DEFAULT] COLLATE collate_name        //设置排序方式(即比较时使用的字符集)SHOW DATABASES;     //查看所有数据库SHOW CREATE DATABASE db_name;   //查看数据库db_name创建时的语句DROP DATABASE [IF EXISTS] db_name;      //删除数据库db_name

创建/查看/删除表

CREATE TABLE tb_name (    fild1 datatype([size]) [comment '描述'],      //内部不能用等号    fild2 datatype([size]),    fild3 datatype([size]),    fild4 datatype([size]),    fild5 datatype([size]),    ...    fildn datatype([size])    )[engine [=] MyISAM/other engine_name]      //设置表引擎    [CHARACTER SET [=] utf8/other charset_name] //设置字符集    [comment [=]'描述'];                        //添加描述SHOW TABLES;            //显示所有表DESC tb_name;           //显示表结构SHOW CREATE TABLE tb_name; //显示表创建语句DROP TABLE tb_name;      //删除表SELECT * FROM tb_name;   //查询表所有记录信息SELECT col1,col2,...etc FROM tb_name;   //查询指定col的记录信息INSERT INTO tb_name [(col1,col2,...etc)] VALUES(val1,val2,...etc) //按指定的col插入记录***当[(col1,col2,...etc)]省略时,VALUES插入值必须是按顺序的对应的所有col的值,不能有遗漏

MySQL数据类型

数值型,文本型(字符串),日期时间型

数值型

整型: tinyint(1bt) samllint(2bt) mediumint(3bt) int(4bt) bigint(8bt)   //一般用int类型      默认是有符号的,有正负,unsigned可设置为无符号的只有整数,数值范围翻倍      zerofill标识,在数值位数不够时加前导零,默认加上unsigned属性浮点型:float(M,D)(4bt) double(M,D)(8bt)      //M,数总长度,不算小数点;D,精度,即小数位数定点型:decimal(M,D)(M>D?(M+2)bt:(D+2)bt)     //M,D同上,decimal的小数点不可移动,是精确值

文本型

CHAR 定长字符,定义多少放多少,不足以空格补齐,select查询去空格;范围,0~255个字符(无论中英文符号等)VARCHAR 变长字符,是多少放多少,select查询不会去空格;范围,0~65535个字节,具体可存字符数量由字段使用的字符集决定,        从第二个开始存放,后面1到2个字节存字符个数,所以最多也就存65532个字符text 文本类型,不能长度,不限字符数量,也没有默认长度enum 枚举,值限定在枚举列表中,只能选一个值;范围,最大65535个值set 枚举,值限定在枚举列表中,值可多选;范围,最大64个值    a,字符串尽量使用单引号    b,如果字符数量超过255个,请尽量使用text

日期时间型

0000-00-00 00:00:000000-00-00 00:00:00date1000-01-01~9999-12-3130000-00-00time-838:59:59~838:59:59300:00:00datetime1000-01-01 00:00:00~9999-12-31 23:59:598 timestamp1970-01-01 00:00:00~2038-01-19 03:14:174  year1901~21551000

实际开发中一般使用int保存时间戳,或用date保存生日等日期select NOW();返回当前系统时间DATE_FORMAT(NOW(), '%Y' 返回当前年份

MySQL字段属性

有无符号(unsigned),是否为空(is/not null),默认值(default ''),索引(index),额外(extra)

NULL和空字符的区别:空字符不占用空间,而NULL不占用空间                  空字符可以插入声明NOT NULL 的列,而NULL不能                  空字符在select语句中没结果,NULL有结果                  空字符在count计行时有数值,而NULL则不计入行数条件表达式中不能用=NULL/!=NULL,得用is NULL /is NOT NULL 一般字段名设置NOT NULL,因为NULL要占用空间,而NOT NULL可插入''(空字符)eg: create table tb_name(id int(10) unsigned not null primary key auto_increment , d timestamp);    insert into tb_name(null,null);主键(primary key),用于独一无二的识别每条记录,它是表中的某一列的属性,可以让每一条记录成为唯一的        一张表只能定义一个主键,可以指定一列或多列;指定多列的称为组合主键,创建时要使用表定义;创建方式:行级定义,表级定义eg: create table tb_name(id int(10) not null unsigned primary key auto_increment,name char(10) not null)auto_increment=1001;    create table tb_name(id int(10) not null unsigned name char(10) not null,primary key ('id','name'));//id-name组合主键auto_increment,自加,一般用在id 列上    1,列类型必须为整数,且有索引    2,可以有重复值,但唯一索引不能有重复值    3,列必须设为NOT NULL ,若无 auto_increment 会自动加上 not null     4,一张表只有一列可以设置为 auto_increment    5,在创建表时,列名后面加 auto_increment标记,字段名括号外可用 auto_increment=n赋初始值,    6,列中的值一经使用,就不会再次被用,除非用truncate清空数据(delete不行),或是主键列插入式指定

MySQL字段类型选择

1,布尔型用tinyint表示,例如0代表false,1代表true2,时间一般用int保留时间戳3,整数能用小的就不用大的4,小数,根据需要使用(近似值float,精确值,decimal)5,字符串,若字符数固定,如身份证/MD5加密后的数据,手机号等用char比较好        若字符数不固定,则使用varchar6,字符数超过255个,推荐使用text7,字符串的结果是预定义值,并且值较少,可以使用enum/set,十几个就可以另建表8,IP保存:使用char,15或16位字符         使用inet_aton和inet_ntoa,前者存储室将其转为整型,后者读取时转为字符(ip)mysql> create table tb1(ip int(11) unsigned ,c char(2));mysql> insert into tb1 values (inet_aton('127.0.0.1'),'a'),(inet_aton('192.168.0.1'),'b');mysql> select * from tb1;+------------+------+| ip         | c    |+------------+------+| 2130706433 | a    || 3232235521 | b    |+------------+------+mysql> select inet_ntoa(ip),c from tb1;+---------------+------+| inet_ntoa(ip) | c    |+---------------+------+| 127.0.0.1     | a    || 192.168.0.1   | b    |+---------------+------+

CURD操作

SQL

SQL(Structured Query Language),结构化查询语言,在关系型数据库上执行数据操作/检索/维护的标准语言SQL 功能:改变数据库结构         更改系统安全设置         维护用户对数据库或表的许可权限         在数据库中检索需要的信息         对数据库的信息进行更新SQL 分类:DDL(Data Defeinition Language),数据定义语言,定义对数据库对象(库/表/列/索引)的操作            CREATE DROP ALTER RENAME TRUNCATE       //库/表级操作        DML(Data Manipulation Language),数据操作语言,定义对数据记录的操作            INSERT DELETE UODATE SELECT等           //行级操作        DCL(Data Control Language),数据控制语言,定义对数据库,表,字段,用户的访问权限和安全级别            GRANT REVOKE 等        Transaction Control ,事务控制   other COMMIT ROLLBACK SAVEPOINT ...SQL书写规范    在数据库系统中,SQL语句不分大小写(建议用大写)    字符串常量区分大小写    SQL语句可单行或多行书写,以;(英文下)结尾    关键词不能跨多行或简写    用空格,缩进来提高语句可读性    字句通常位于独立行,便于编辑和可读性    注释,SQL /**/多行注释 --单行注释;MySQL #注释    MySQL中单引号起来的数值,字符...处理

CRUD--表,创建/读/更新/删除表

create database db_name;create table tb_name;insert into tb_name(col1,col2,col3,...etc) values (val1,val2,val3,...etc);insert into tb_name values(val1,val2,...etc);insert into tb_name(col1,col2,col3,...etc) select col1,col2,...etc from tb2;查询语句:select *|{col1,col2,col3...}            from tb_name             [where where_definition]where 常用的条件    比较运算符:= != > <      //大小              between ... and ...   //某一区间的值,闭区间              in(set)       //显示in列表中的值,eg:in(100,200)              like(_ %)     //模糊查询,%表示多个或0个字符,_表示一个字符              is null       //判断是否为空    逻辑运算符:and   //都成立则成立              or    //一个成立则成立              not   //非更新语句:update tb_name        set col1=val1 [,col2=val2,...]        [where where_definition]    where 子句一般不可少,没有where,则会更新所有对应列的记录    update成功的两条件,语句OK,数据行也更新成功    update和insert连用,数据有符合条件的行存在则更新,无则插入新行    insert into tb_name(colmun_list) values(val_list) on duplicate key update col1=val1,col2=val2,...etc;删除语句:delete from tb_name where where_definition;    一定得记得加where条件,不加则删表中除所有记录    delete 语句删除以行为单位,不能删除某列某行的特定值    delete 删表只删记录, auto_increment自增的值还没清    truncate清空表是把表所有记录痕迹删除,auto_increment自增清除,从头再来    从表中删记录时要考虑表间的约束关系(其他表的参照完整性),如外键约束,唯一约束等,update,insert时也是要考虑

修改表结构(ALTER)

RENAME(改名) alter table tb_name rename new_tb_name;CHANGE(改列名) alter table tb_name change old_col_name new_col_name datatyepe[(len)];MODIFY(改列名的类型) alter table tb_name modify col_name new_datatype;DROP(删除列) alter table tb_name drop col_name;ADD(增加列) alter table tb_name add col_name datatype [fisrt|after col1]; //加在第一列,或col1之后删除表DROP TABLE tb_name; 

SELECT语句其它常用关键字

DISTINCT( ),select查询结果去重    select distinct col_list from tb_name where ...;ORDER BY (排序) ,查询结果排序(可对多列进行排序)    select col1,col2,col3,...etc | col_name as as_name             from tb_name             order by coln[,colm] |as_name  [asc | desc];LIMIT(限制),限制查询结果的输出行数,一般用于查询结果分页      select col1,col2,col3,...etc             from tb_name             limit [offset,] rows | rows OFFSET offset; //offset偏移量从0计

GROUP BY 分组

将列中记录相同的值分到一组,一般和count的聚合函数一起用,统计组的相关信息,如统计同班同组的人数等    select col1,col2,...coln |count(colm),coln           from tb_name wehre ...           group by coln;    查询结果中col1,col2只显示组中的第一个值    函数group_concat(col1)可把其他列的结果横列(一个记录格里放多个数据记录)在同组的那一行    select查询的列,只限于select的聚合函数列和分组条件的列(coln),如 count(colm),coln    group by 可对多列进行分组:select col1,col2 from tb_name group by col1,col2;having 子句,having同where写法类似,但having在结果中进行筛选,而where对原始记录进行删选,having一般跟在group by后面      select col1,col2,...coln |count(colm),coln           from tb_name wehre ...           group by coln having ...;常用的聚合函数(常于group by搭配)count(col|*),计数,返回某一列记录的行数sum(column),求和,返回column所有质的和,配合group by,可分类汇总avg(column),求平均数,返回column所有质的平均值,同上max/min(),求最大/最小值,同上eg: select count(*)| sum(column) | avg(column) |max(column) |min(column)        from tb_name        where  where_definition                                           

子句总结

书写顺序:where -->> group by -->> having -->>order by -->>limit;执行顺序:from子句组装来自不同表(库)的数据        where子句基于指定的条件对记录行进行筛选        group by 子句将数据划分多个分组        使用聚合函数进行计算        使用having子句对结果进行筛选        计算所有表达式        使用order 不要对结果进行排序        limit 语句调整输出的数量