首页 > 代码库 > Mysql笔记

Mysql笔记

Mysql笔记

 

  1. PHPMySQL合作的方式:

a) PHP是作为MySQL的客户端

b) PHPMySQL服务器发送一些sql语句,来操作mysql服务器当中的数据

  1. SQL语言分为四个部分:

a) DDL(定义语言):

  1. 建库、建表、修改表等操作

b) DML(操作语句):

  1. 增加数据、删除数据、修改数据

c) DQL(查询语句):

  1. 查询数据

d) DCL(控制语句):

  1. GRANTREVOKECOMMITROLLBACK
  2. Mysql客户端登陆:

a) Mysql –u用户名 –p密码 [数据库名称–h192.168.170.250

  1. 查看当前数据库服务器下的所有数据库:

a) Show databases;

  1. 选择到某一个数据库下:

a) Use test;

  1. 查看当前所在的数据库:

a) Select database();

  1. 查看当前数据库下的所有的数据表:

a) Show tables;

  1. 查看帮助信息:

a) Help

  1. \c 取消当前输入的未完成的命令
  2. \d 临时修改执行符
  3. \G 将查询出的数据格式化(立起来)
  4. \q 退出  exit  ctrl+c  quit
  5. \t 取消tee
  6. \T 将命令记录到外部文件当中
  7. \s 获取服务器的信息
  8. SQL语言需要注意的问题:

a) 需要使用分号执行

b) 可以使用折行,最后加分号才执行

  1. DDL语句:

a) 建立数据库:

  1. CREATE DATABASE [IF NOT EXISTS] 数据库名称
    1. 注意:数据库名称尽量小写(一个数据库会产生一个文件夹,对于Linux,文件夹和文件是严格区分大小写的)
    2. 命令不区分大小写
    3. 建立数据表:
      1. 建立数据表之前,一定要选择数据库
      2. 注意:

a) 数据表名称小写

  1. Create table bbs_user(
  2. Id int unsigned not null auto_increment primary key,
  3. Username varchar(32)
  4. )engine=myisam default charset=utf8;
  5. 删除数据表:
    1. Drop table 数据表名称
    2. 删除数据库:
      1. Drop database [IF EXISTS] 数据库名称
      2. 注意:

a) 删除结构化的内容,我们使用drop语句

b) 删除数据,我们使用delete语句

  1. 修改表:
    1. Alter table 表名称 具体的操作
  2. 数据表的列类型:

a) 数值类:

  1. Tinyint
  2. Smallint
  3. Mediumint
  4. Int        IP地址  时间戳  主键ID   年龄   性别等
  5. Bigint
  6. Float
  7. Double
  8. Decimal
  9. 注意:
    1. 整型后面的数字可以省略,默认情况下为10
    2. 如果指定了整型的长度,该长度不会截取,不影响取值范围,只会影响现实范围(只配合零填充的时候有效)
    3. Floatdouble后面的数字会影响精度,float(3,1)整体长度最大为3位,超出3位,会以最大值99.9来存储,1代表的是小数点后保留一位小数

 

  1. 登陆MySQL客户端:

a) Mysql –uroot –p123 [数据库名称–hlocalhost

  1. 查看当前数据库服务器下的所有数据库:

a) Show databases;

  1. 选择数据库:

a) Use 数据库名称;

  1. 查看所有数据表:

a) Show tables;

  1. 查看当前所在数据库:

a) Select database();

  1. 查看帮助信息:

a) Help

  1. \c   取消之前输入的命令
  2. \q   退出
  3. \G   将输出的结果格式化,将表立起来
  4. \t    取消tee
  5. \T    将以下的所有记录到外部文件当中
  6. \s    获取服务器相关信息
  7. \d    更改执行符
  8. 创建数据库:

a) Create database [IF NOT EXISTS] 数据库名称;

  1. 删除数据库:

a) Drop database [IF EXISTS] 数据库名称;

  1. 创建数据表:

a) Create table 数据表名称(

b) Id int unsigned not null auto_increment primary key,

c) Username varchar(32) not null default ‘admin’,

d) Password char(32) not null default ‘123’

e) )engine=myisam default charset=utf8;

  1. 修改数据表:

a) Alter table 数据表名称 操作

  1. 删除数据表:

a) Drop table 数据表名称

  1. 数据字段的类型:

a) 数值类型:

  1. 整型
    1. Tinyint
    2. Smallint
    3. Mediumint
    4. Int
    5. Bigint
    6. 注意:

a) Int(10) 后面的数字不会影响精度,只配合zerofill零填充来使用才有效,如果使用了零填充,而我们存储的数字的显示位数不够10位,用0填充到十位,如果插入的值超过10位,不会影响精度

  1. 浮点类型
    1. Float
    2. Double
    3. Decimal
    4. 注意:

a) Float(3,1)  整体会截取到3位,99.91代表保留一位小数

b) 字符串类型:

  1. Char  字符串类型  定长   2000左右
  2. Varchar  字符串类型 可变长度(只能缩短,不能扩大)    65533
  3. Charvarchar的区别:
    1. Char的最大取值范围较varchar小一些
    2. Char是定长,varchar是可变长度,如果char当中存入的实际字符串的长度不到指定长度,会自动以空格补全至指定长度,如果varchar当中存入的实际字符串的长度不到指定长度,不会自动补全,反之,会缩小至实际长度
    3. Charvarchar存入的实际字符串的长度如果超出指定长度,同样都会被截取
    4. Char的效率更高于varchar,但是占用空间更大
    5. Tinyblob
    6. Tinytext
    7. Blob  text
    8. Mediumblob, mediumtext
    9. Longblob  longtext
    10. 注意:
      1. Blob重点存储数据对象,一些文件
      2. Text重点存储文本内容
      3. Enum(‘val1’,’val2’,’val3’…..)   最大成员 65535
      4. Set(‘val1’,’val2’…..)   最大成员64
      5. 注意:
        1. 效率较高,占用空间较大
        2. Enum只能选一个,set可以选多个
  4. 时间和日期类型:

a) Date

b) Time

c) Datetime

d) Timestamp

e) Year

  1. NULL值:

a) 什么都没有

  1. MySQL的字段属性:

a) Unsigned

  1. 存入的数值类型不能为负数,取值范围会跟着改变
  2. Tinyint -128-127    0-255

b) Zerofill

  1. 零填充,配合整型后面的数字使用

c) Auto_increment

  1. 自增列,需要注意的是,使用此属性,必须要给该字段设置索引

d) Nullnot null

  1. Not null 该字段不能存入空值,只有配合default属性使用才有意义

e) Default

  1. 默认值,如果没有存入值,以默认值存储,通常配合not null使用
  2. MySQL的索引:

a) 什么是索引?

  1. 快速定位的工具,相当于是书的目录
  2. 索引是创建在字段上的

b) 索引有几种?

  1. 主键索引
  2. 唯一索引
  3. 常规索引
  4. 全文索引
  5. 共同点:都可以提高查询速度
  6. 区别:
    1. 主键索引:

a) 一张表只允许有一个主键

b) 唯一性

  1. 唯一索引:

a) 唯一性

  1. 常规索引:

a) 只是简单的定位

  1. 全文索引:

a) 针对text类型创建,不使用全文索引,将来会用sphinx(全文检索引擎)替代

  1. 索引创建的方式:
    1. 建表的时候在字段后直接加索引

a) Create table t1(

b) Id int unsigned not null auto_increment primary key,

c) Username varchar(32) unique,

d) Password char(32) index

e) )engine=myisam default charset=utf8;

  1. 在建表的时候,在所有字段后加索引

a) Create table t2(

b) Id int unsigned not null auto_increment,

c) Username varchar(32),

d) Password char(32),

e) Primary key(id),

f) Unique(username)

g) Index/key(password)

h) )engine=myisam default charset=utf8;

  1. 创建表完成以后,对数据表进行修改:

a) Alter table t3 add index 索引名称(字段)

b) Alter table t3 add unique 索引名称(字段)

c) Alter table t3 add primary key(字段)

  1. ALTER语句:

a) 修改表:

  1. 修改表名称:
    1. Alter table t5 rename to t5_new
    2. 修改字段:
      1. 增加字段

a) Alter table t4 add column rtime int unsigned not null default 0;

  1. 修改字段属性

a) Alter table t4 modify rtime int not null default 0;

b) Alter table t4 change rtime ptime int not null default 0;

c) 注意:

  1. Modify无法更改字段的名称
  2. change可以更改字段名称
  3. 删除字段

a) Alter table t4 drop column 字段名称

  1. 修改索引:
    1. 增加索引:

a) Alter table t6 add index 索引名称(字段名)

b) Alter table t6 add unique 索引名称(字段名称)

c) Alter table t6 add primary key(字段名称)

  1. 删除索引:

a) Alter table t6 drop index 普通索引的名称

b) Alter table t6 drop index 唯一索引的名称

c) Alter table t6 drop primary key;

d) 注意:

  1. 删除主键之前,需要将auto_increment属性删除掉,因为auto_increment属性需要依附在一个索引上
  2. 查看索引:

a) Show indexes from 表名称

  1. 数据表类型:

a) 在建表的最后使用engine来指定表类型

b) 数据表类型有很多,我们经常用的只有myisaminnodb

c) Myisaminnodb的区别:

  1. Myisam更成熟,更稳定,更易于管理
  2. Myisam会产生碎片空间,innodb不会产生,myisam需要经常性的使用optimize来优化表
  3. Myisam不支持事务处理,innodb支持
  4. Myisam不支持外键索引,innodb支持
  5. Myisam效率更高
  6. Myisam会产生三个文件,表名称.frm(存储表结构),表名称.MYD(存储表数据),表名称.MYI(存储表索引)innodb只会产生一个文件表名称.frm(存储表结构),innodb的表数据在ibdata这个文件当中存储(共享表空间,所有的innodb表的数据和索引全部存在这里)
  7. 字符集:

a) 数据库服务器字符集

b) 数据库字符集

c) 数据表字符集

d) 数据字段字符集

e) 客户端字符集

  1. Php作为客户端
    1. php文件设置utf-8bom的字符集
    2. Header(‘content-type:text/html;charset=utf-8’)
    3. Html页面也必须是utf-8字符集
    4. <meta http-equiv=”content-type” content=”text/html;charset=utf-8” />
    5. Set names utf8

f) 连接字符集

g) 校验字符集

  1. SQL语句分为四种:

a) DDL 定义语句

  1. Create
  2. Alter
  3. Drop

b) DML语句(操作语句,增删改)

  1. Insert
    1. Insert into t1(id,username,password) values(1,’zhangsan’,’123’);

a) 把所有的字段列出来,values对应的值的顺序要与前面字段的一致,类型也要一致

  1. Insert into t1(id,username,password) values(null,’lisi’,’234’);

a) Id如果是自增列,values对应的值可以使用null

  1. Insert into t1(username,id,password) values(‘wangwu’,null,’345’);

a) 前面字段的顺序可以打乱,但是后面的values对应的值得顺序依然要与前面字段的顺序一致

  1. Insert into t1(username,password) values(‘zhaoliu’,’456’);

a) Id列可以省略,此时id列依然会自增

  1. Insert into t1(username) values(‘tianqi’);

a) Password列可以省略,但是会插入null值,如果password列有not null属性,会插入空字符串,通常情况下,not null会配合default属性来使用,当没有插入password时,会按照默认值插入

  1. Insert into t1 values(null,’zhaoba’,’678’);

a) 前面的字段可以不写,但是values后面需要把所有字段对应的值都写上,并且按照字段的顺序来插入值

  1. Insert into t1(username,password) values(‘zhangsan’,’123’),(‘lisi’,’345’),(‘wangmazi’,’678’);

a) 可以一次性插入多个值,这样效率较高,mysql只会解析一次sql语句的语法

  1. Delete
    1. Delete from t1;

a) 删除语句,后面如果没有where子句,会将所有的记录全部删除掉,强烈要求大家把where条件加上

  1. Delete from t1 where id=14;

a) 可以使用where子句来对删除的记录进行筛选

  1. Delete from t1 order by id desc limit 2;

a) 删除id最大的两条记录

  1. Update
    1. Update t1 set username=’xiaoqingxin’;

a) 如果没有where子句,会将当前表当中的所有记录全部更新掉,非常不安全,强烈要求大家把where子句加上

  1. Update t1 set username=’wangwu’ where id=3;

a) 根据where条件筛选后的记录进行更改(找到id=3的记录,将username进行更改)

  1. Update t1 set username=’pijiu’ where id>8;

a) Where条件可以有多样性

  1. Update t1 set username=’wangmazi’,password=’222’ where id>8;

a) 可以同时更改多个字段,用逗号(,)隔开

  1. Update t1 set username=’xiaoqingxin’,password=’444’ order by id desc limit 3;

a) Order by代表排序,limit代表限制条数,这句话的意思是:以id降序排序,只将最大的3条进行修改。

c) DQL语句(查询语句)

  1. Select
    1. Select [all|distinct] [表名.]字段名称 [as 字段的别名]|* from 表名 [where子句][group by][having][order by][limit];

a) Select * from user;

  1. 没有where子句,代表显示所有记录,*代表显示所有字段

b) Select username,age,sex from user;

  1. 只显示usernameagesex字段

c) Select username as u from user;

  1. 可以给username别名为u,使用as来给别名

d) Select username u from user;

  1. 定义别名的as可以省略,用一个空格隔开也是可以的

e) Select user.username from user;

  1. 可以使用表名.字段名来显示字段

f) Select u.username from user as u;

  1. 同样可以使用as给表名加别名

g) Select u.username from user u;

  1. As同样可以省略

h) Select all age from user;

  1. 查询出所有记录的age字段的内容

i) Select distinct age from user;

  1. Where
    1. 查询出不重复的age内容

a) Select * from user where age>18;

  1. 查询age大于18的记录

b) Select * from user where age>18 and username=’wangwu’;

  1. 查询age大于18并且username为王五的用户

c) Select * from user where age=18 or age=35;

  1. 查询age等于18的或者是age等于35的用户的记录

d) Select * from user where age!=20;

  1. 查询age不等于20的用户

e) Select * from user where age is null;

  1. 查询agenull的记录

f) Select * from user where age is not null;

  1. 查询age不为null的记录

g) Select * from user where age between 20 and 30;

  1. 查询age2030之间的记录,等价于age>=20 and age<=30

h) Select * from user where username like ‘zhang%’;

  1. 查询usernamezhang开始的记录,这里的%是通配符,通配符还是_,%代表任意个任意字符,_代表一个任意字符

i) Select * from user where id in(1,3,5,7);

  1. 查询id1,3,5,7的用户,等价于id=1 or id=3 or id=5 or id=7

j) Select * from user where id not in(1,3,5,7);

  1. 查询id不为1,3,5,7的用户
  2. Group by

a) Select age,count(*) from user group by age;

  1. age的值进行分类,每种值分为不同的类,配合count(*),统计每种类的记录的个数,这样我们就可以完成对不同年龄段的会员的个数的统计,group by在配合聚合函数的时候,才有更大的意义

b) Select age,count(*) from user where age>18 group by age;

  1. 查询出age大于18的每个年龄段的记录的个数

c) Select age,count(*) as c from user where age>18 group by age having c>1; 

  1. 对分组后的数据进行二次筛选,having的用法和where的用法一模一样,只是进行二次筛选
  2. Order by

a) 排序

  1. Select * from user order by age desc;
    1. Desc代表降序排序,asc代表升序排序,默认情况下即为升序
    2. Select * from user order by age desc , id asc;
      1. 先对age进行降序排序,如果age有相同的,再对id进行升序排序。
      2. Select * from user order by rand();
        1. 随机排序
        2. Limit

a) 限制

  1. Select * from user Limit 1;
    1. 只显示查询出来的一条记录
    2. Select * from user limit 3,2;
      1. 跳过3条显示两条

d) DCL语句(控制语句)

  1. Grant
  2. Revoke
  3. Commit
  4. Rollback

e) 两表关联查询的三种方式:

  1. 普通关联查询
    1. Select u.id,u.username,u.password,d.age,d.sex from bbs_user as u,bbs_user_detail as d where u.id=d.uid;
    2. 连接关联查询
      1. 左连接

a) Select u.id,u.username,u.password,d.age,d.sex from bbs_user as u left join bbs_user_detail as d on u.id=d.uid;

  1. 以左边的表为主,先输出左边表的内容,再按照on后的条件去右边表进行匹配输出
  2. 右连接

a) Select u.id,u.username,u.password,d.age,d.sex from bbs_user as u right join bbs_user_detail as d on u.id=d.uid;

  1. 以右边的表为主,先输出右边的表的内容,然后再按照on后的条件去左边的表进行关联匹配输出
  2. 内连接

a) Select u.id,u.username,u.password,d.age,d.sex from bbs_user as u inner join bbs_user_detail as d on u.id=d.uid;

  1. 不以任何表为主,以on后的条件来进行关联输出,类似于普通关联查询,只输出满足on的条件的内容
  2. 嵌套关联查询
    1. Select username from bbs_user where id in(select uid from bbs_user_detail);

a) 一个查询的结果作为另一个查询的条件,但是不推荐大家使用,因为效率很低

  1. 聚合函数:
  2. Count(*) 统计个数
  3. Sum(id) 统计总和
  4. Avg(age) 统计平均值
  5. Max(age) 统计最大值
  6. Min(age) 统计最小值

 

导入导出语句

mysqldump -uroot -p123456 lamp72 >H:/lamp72.sql

mysql -uroot -p123456 < H:/lamp72.sql导入

mysql -uroot -p123456 > H:/lamp72.sql导出

 

 

Mysql笔记