MySQL 基本操作

数值类型

MySQL中，每个库是一个文件夹，每张表是一个文件分为（

.FM 存储表结构 .MYD存储记录   .MYI存储索引 ）

1 数值型

    整型

        1B TINYINT

        2B SMALLINT

        3B MEDIUMINT

        4B INT

        8B BIGINT

    浮点型(M为一共有多少位，D 为保留多少位小数)

        4B FLOAT(M,D)

        8B DOUBLE(M,D)

         (M+2)B   DECIMAL(M,D) 

2 字符型(可以表示任何类型)

        CHAR  最大为255B 每个字符为一个B 固定长度字符串

        VARCHAR 同上 可变长度字符串

            char(4)        varchar(4)

            ''  4B               '' 1B

            'ab' 4B             'ab' 3B

            处理速度快，存储内容大小变化不大的用char

            处理速度慢，存储内容变化大的用varchar

         用于保存文本数据 

            TEXT         2^16-1 个字符

            MEDIUMTEXT    2^24-1个字符

            LONGTEXT    2^32-1 个字符

        用于保存二进制数据

            BLOB

            MEDIUMBLOB

            LONGBLOB 2^32-1个字符

        枚举类型

            ENUM 1~2B 一次只能用一个值

        集合类型

            SET    1,2,3,4,8B 一次可以用一个集合的多个值

      

3 日期型

            DATE            YYYY-MM-DD

            TIME               HH:MM:SS

            DATETIME        YYYY-MM-DD HH:MM:SS

            TIMESTAMP      YYYYMMDDHHMMSS

            YEAR                 YYYY

数据字段属性

UNSIGNED   无符号 只能用在数值型字段

ZEROFILL        前导0  只能用在数值型字段

AUTO_INCREMENT 只能是整数，数据每增加一条就会自动增加 1，字段的值不允许重复

NULL ,NOT NULL   空值和非空值

DEFAULT 默认值

示例：CREATE TABLE users(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(30) NOT NULL DEFAULT '',

    height DOUBLE(10,2) NOT NULL DEFAULT 0.00,

);

创建索引

1 主键索引

        主要作用确定数据库表里一条特定数据记录的位置

        最好为每一张表定义一个主键，一个表只能指定一个主键，主键值不                  能为空

2 唯一索引

    都可以防止创建重复的值

    每一个表可以有多个唯一索引

    unique

3 常规索引

    最重要的技术

    提升数据库性能

    可以提高查找的速度，减慢数据列的插入，删除，修改的速度

    

数据表类型和存储位置

mysql有存储引擎的概念，可以针对不同的存储引擎需求选择最优的存储引擎

引擎  ===》数据表类型

            InnoDB

create table () type -  InnoDB;

create table() type - MyISAM;

MyISAM 为默认的引擎

同一个库中的每一个表都可以有自己的表类型

MyISAM   表类型

容易产生碎片 所以常用OPMIMIZE TABLE 表名来执行回收碎片

强调快速读取操作

缺点是有一些功能不支持

InnoDB 表类型

支持一些MyISAM不支持的功能

也有缺点，占用空间大，不支持快速读取

 

功能            MyISAM              InnoDB

事务处理       no                          yes

数据行锁定    no                          yes

外键约束        no                          yes

表空间占用     相对小                    相对大

全文索引         yes                         no

默认字符集

ACSII

ISO-8859-1/latin1

gb2312-80

gb13000

GBK     定长 用于大量的比较 排序之类的数据操作

GB18030

UTF-32

UTF-16

UTF-8   1-4B 非定长

USC-2

name varchar（12） 6个汉字  GBK   其中的12是12B

name varchar（12） 4个汉字   UTF8

数据库中的UTF-8对应为UTF8

字符集：用来定义MySQL存储字符串的方式

校对规则：对规则定义了比较字符串的方式

一对多的关系，一个字符集对应多个校对规则

create database db default character set utf8 collate GBK；

客户端与服务器交互时的字符集

character_set_client

character_set_connection

character_set_results

set names 字符集 同时修改上面三个的值

修改表

alter table

alter table user add name varchar(21) not null;

alter table user add age int not null  default '0' after name;

alter table user add height double not null default '0' first;增加表中的字段

alter table user modify sex char(3);修改表中某一字段的属性

alter table user change name username varchar(30);修改表中某一字段的名称

alter table user rename as users;重命名表

alter table user drop name;删除表中的某一字段

DDL 数据定义语言

create drop alter

创建库

create database 库名；

 use 库名;

创建表

CREATE TABLE[IF NOT EXISTS]表名(字段名1 列类型[属性][索引]，

字段名2 列类型[属性][索引],

字段名3 列类型[属性][索引])[表类型][表字符集];

表名称和字段名 自己定义 如users articles 

SQL不区分大小写 表名是一个文件名 

SQL语句最好都大写

属性：UNSIGNED 无符号

drop database 库名；

drop table 表名；

DML 数据操作语言

insert into 表名[字段名] values（值列表1），（值列表2）

insert into users（id，name） 

values（‘1’，‘zhangyizheng’）；

特点：

1 如果在表名后没有给出字段列表，则值列表必须列出所有的字段的值

2 所有需要写入字段名的地方都不加单双引号，在值列表中则必须要加单双引号

3 建议在插入数据时，最好添加上字段名，这样就可以按照字段的顺序插入

update 表名 set 字段1=‘值’，字段2=‘值’  [条件] 

条件可以指定一个条件也可以是多个

update users set name='hello',age='12' where id='1';

delete from 表名 [条件]

delete from users where id='2';

DQL 数据查询语言

SELECT  [ALL|DISTINCT] {*|table.*|[table.]field1[as alias1],[table.]field2[as alias2],[......]}

FROM 表名

[WHERE ]

[group by]

[having]

[order by]

[limit] 

1 字段 要列出要查询的字段

2 可以为每个字段起别名 

3 使用distinct作用整个查询列表，取消重复的数据，只返回一个，而不是单独的一列

4 在SQL语句中使用表达式的列（算术运算符号，可以使用条件，逻辑运算符）

5 WHERE 可以在SELECT UPDATE DELETE中使用

逻辑运算符号（多个条件组合）

&& || ！

比较运算符号

=

<=> 和=作用一样，但可以用于null比较

!= <>

<

<=

>

>=

iS NULL

select * from users where name is null;

IS NOT NULL

BETWEEN AND   （between 10 and 20 ）

select * from users where age between 10 and 20;

NOT BETWEEN AND

LIKE   其中_指任意一个字符 %指任意个字符

select * from users where name like ' java__';

NOT LIKE

IN

select * from users where age in (10,15,20,25);

REGEXP

select * from users where name repexp '^hello';

多表查询（连接查询）

select c.name cname,d.name dname,c.id cid,d.id did from users as c,product as d;

嵌套查询

select * from product where id in(select id from users where name = 'zhang');

顺序查询

order by 字段 [asc]正序 [desc]倒序

select * from users order by id asc;

限定查询的条数

select * from users limit 5

select * from users where id<15 order by id asc limit 1;

分组查询 group by

count()

sum()

avg()

max()

min()

select count(*),sum(price),avg(price),max(price ),min(price) from users group by id having avg(price)>500;

having 未分组后再进行限定条件

内置函数

原函数可以将字段名作为变量来使用，变量的值就是这个列对应的每一行字段

1字符串函数

    1 concat (str1,str2....);连接字符串

    2 insert(str,x,y,insert);讲字符串x位置开始，y位置结束的长度字符串替换为insert

    3 lower(str) ,upper(str);

    4 left(str,x),right(str,x);返回最左边或最右边的第二个参数个的字符串，若第二个参数为null，则什么也不返回

    5 lpad(str,n,pad),rpad(str,n,pad)； 在str的左边或右边增添n个pad

    6 trim(str),ltrim(str),rtrim(str);去掉str的全部，左边，右边的空格

    7 replace(str,a,b);用字符串b替换str从a开始的全部字符串部分

    8 strcmp(a,b)；比较字符串a，b的ascii码

    9 substring(str,x,y);返回str的从x到y的子字符串

2数值函数

     1 abs(a)返回绝对值

     2 ceil(x) 返回大于x的最小整数

     3 floor(x) 返回小雨x的最大整数

     4 mod(x,y) 返回x/y的模

     5 rand() 返回0~1之间的随机数

     6 round(x,y);返回参数x的y位四舍五入的值

     7 truncate(x,y);返回参数x的y位截断的值

 3日期函数

    

     curtime();返回当前的时间

     curdate();返回当前的日期

     now();

     week();

     year();

     hour();

     minute();

     from_unixtime

     unix_timestamp(date)

4流程控制函数

if(value,t,f);如果value成功则返回t，如果不成立则返回f

ifnull(value1,value2);如果为不为空则返回value1，如果为空则返回value2

case when [value1] then [result1]... else [result2]

 5 其它函数

 前面都要加上select;

database();

version();

user();

inet_aton(ip);

inet_ntoa();

password();

md5();

DCL 数据控制语言

\s 查询状态

show database;

show tables;

desc 表名;显示表结构

show variables;