个人学习Mysql中的一些笔记

cmd命令连接：（用户名root密码root）

mysql -u root -p

输入密码root

退出：exit

一条sql语句的结束标志是分号;

创数据库：create database NAME;

删除数据库：drop database NAME;

 

连接上mysql后选择使用哪个数据库：useNAME;

 

创建表：

create table if not exists student (

sid int unsigned primary key auto_increment,--无标志，主键，自增

sname varchar(20) not null,

sage int,

ssex int not null,

creat_date_time datetime

);engine=innodb default charset=utf-8;-- 设置存储引擎、字符集编码

删除表：drop table student;

 

插入数据insert into的使用：

insert into student (sname,sage,ssex) values (‘张三’,18,1,now());

now()是mysql的一个函数，返回时间和日期

插入多个数据：

insert into student (sname,sage,ssex,creat_date_time) values (‘张三’,18,0,now()),

(‘李四’,20,0,now()),(‘王老五’,17,0,now());

 

查询表信息select的使用：

select * from student;

select sname,sage,ssex from student where ssex = 1 order by creat_date_time asc/desc limit 10;

查询可以使用一个或者多个表，表间用逗号分割

可以使用limit设置返回的记录数

 

条件where的使用：

你可以在where子句中指定任何条件，你可以使用and或者or指定一个或多个条件，类似于if，mysql查询时，and or同时存在时，and的优先级高于or。

使用主键来作为where子句的条件查询是非常快速的,如果查询不到则不返回任何数据。

MySQL的where子句的字符串比较是不区分大小写的。 你可以使用binary关键字来设定where子句的字符串比较是区分大小写的

select * from student where binary sname = ‘AbcD’;--区分字符串的大小写

 

更新updete set的使用：

update student set sname=’张三’, sage=25,create_date_time=now() where sid=2;

不使用where则会更新整张表update student set sage=sage+1;

批量修改某个字符串为别的字符串：updete student set sname=replace(sname,’acb’,’abc’) where sid =1;--将名字中的acb字符串改为abc

 

删除delete的使用：

delete from student where sid=1；如果没有where条件将会整张表的数据删除

 

模糊查询like的使用：

like子句中使用百分号 %字符来表示任意字符，如果没有使用百分号%, LIKE子句与等号=的效果是一样的。

select * from student where sname like ‘%三%’;

 

union操作符的使用：

于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的数据。（union all会返回所有的数据包括重复的）

select * from student where sid = 1 union select * from teacher where tid = 1;

 

排序order by   desc/asc的使用：

select * from student order by sid desc;

 

分组语句group by的使用：

根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

select sname[as name使用as可以改列名],count(*) from studentgroup bysname;

with rollup可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

select coalesce(aname,'总和'),count(*) as '同名人数' from agroup by aname with rollup;

coalesce(a,b,c)如果a=null，则选择b，如果b=null，则选择c......

联合多表查询join的使用：

join大致分为三类

inner join on：内连接/等值连接，获取两个表中字段匹配关系的记录

left join on：左连接，获取左表所有记录，即使右表没有对应匹配的记录

right join on：右连接，获取右表所有记录，即使左表没有对应匹配的记录

MySQL的内连接使用inner join on,它的效果跟使用where是一样的,如果联结的是两个表,那么需要左右的条件或者说字段是需要完全匹配的。

select s.sid,s.sname,t.tid,t.tname from student s inner join teachert on s.sid=t.tid;

左连接的结果是除了匹配条件的数据还包含左表中的所有数据（右连接相反）

select s.sid,s.sname,t.tid,t.tname from student s left (out可省略) jointeacherons.sid=t.tid;

 

null值处理：

is null：为null则返回true

is not null：不为null则返回true

<=>：比较操作符，当比较的两个值为null时返回true（这个操作符只能用于mysql，便于移植其他数据库不建议使用）

 

正则表达式：正则表达式不区分大小写（可以在where后面加binary区分大小写）

点号“.”：匹配任意字符select * from student where sname regexp “.三疯”;

竖线”|”：或者的意思select * from student where sname regexp “张三疯|李三疯”;

中括号”[]”：字符集合，匹配所包含的任意一个字符。select * from student where sname regexp “[张李王]三疯”;（不想查张、李、王三疯[^张李王]三疯）

匹配数字1到9[1-9] 匹配字母a到z[a-z]

符号“^”：在[]里面表示取反，在外面表示以什么开头--> “^abc”以a开头

符号”$”：表示结束位--> “abc$”以c结束

如果匹配的字符串中含有特殊字符可以在前面加”\”转义  

特殊：点号”\\.”  反斜杠”\\\\\” 括号”\\(”

重复匹配：

* --> 0或多个匹配

+ --> 1个或多个  等价于{1,}

? --> 0或1个  等价于{0,1}

{n} --> 重复n次

{n,} --> 至少重复n次

{n,m} --> 重复n~m次（m不超过255）

select * from student where regexp “\\([0-9] sticks? go*d\\)”;

mysql的事务：

mysql中只有innodb数据库引擎的数据库或表才支持事务，该引擎在5.5后的MySQL数据库中为默认存储引擎

事务是必须满足4个条件（ACID）：Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、原子性：一组事务，要么成功；要么撤回。

2、稳定性 ：有非法数据（外键约束之类），事务撤回。

3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得，innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务务须使用命令 BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

开启事务：begin/start transaction

提交事务：commit;

回滚事务：rollback;

创建保存点：savepoint p1;

删除保存点：release savapoint p1;

事务回滚到保存点：rollback to p1;

设置事务隔离级别：set transaction;存储引擎innodb提供四个隔离级别

1、read uncommitted（未提交读）

2、read committed（提交读）

3、repeatable read（重复读）

4、serializable（序列化）

使用set改变mysql的自动提交模式：

set autocommit = 0 禁止自动提交

set autocommit = 1 开启自动提交

auto_increment（自增列）无论事务提交没，都不会回滚了

显示a表字段结构详细信息：show columns from a;

获取表信息：show table status;

当需要修改表名或者表字段时，要用到alter table命令

删除表上的字段ssex：

alter table student drop ssex;（如果表上只有sses一个字段则无法用drop删除）

向表添加列ssex：alter tablestudentaddssex int;（自动添加到表字段末尾）

可以使用first/after（这两个关键字只占用于add字句）指定位置，所以如果你想重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD来添加字段并设置位置。

 

alter table student add a int first;添加a字段在首列

alter table student add b int after a;在a字段后添加b字段

 

alter table student modify b char(10); --> 将b字段的类型改为char(10)

alter table student change m n bigint; --> 将m字段改名为n，类型改为bigint

alter table student modify b int not null default 100; --> 将b字段改为int型、非空、默认100，不设置值默认为null

修改表名：alter table student rename to students;表名student改为students

删除外键约束：alter table tableName drop foreign key keyName;

修改存储引擎：alter table tableName engine = myisam;

 

索引：show index from a;查表a的索引

建立索引会占用磁盘空间的索引文件

优点：mysql建立索引对其高效运行非常重要，索引大大提高检索速度。

缺点：虽然大大提高查询速度，但同时也会降低更新表的速度（增删改），mysql保存数据的同时要保存索引文件。

单列索引：一个索引只包含单个列，一个表可以有多个单列索引

组合索引：一个索引包含多个列

 

创建索引：

create index indexName on mytable(username(length));（char、varchar类型的length可以小于字段实际长度，如果blob和text必须指定length）

修改表结构（添加索引）：

alter table tableName add index indexName(columnName);

删除索引：

drop index [indexName] on mytable; --> drop index q on student;

创建唯一索引：

create unique index indexName on mytable(username(length));

alter table mytable add unique [indexName] (username(length));修改表结构

使用alter命令添加和删除索引：

alter table tableName add primary key (column_list); 添加一个主键，唯一且不为null

alter table tableName add unique indexName (column_list);创建索引的值唯一（除了null）

alter table tableName add index indexName (column_list);添加普通索引，索引值可多次出现

alter table tableName add fulltext indexName (column_list);指定索引为fulltext，用于全文索引

使用alter命令添加和删除主键：（主键只能作用于一个列）

alter table tableName modify id int not null;添加主键索引确保不能为空（not null）-->这句不必须

alter table tableName add primary key (id);添加主键

alter table tableName drop primary key;删除主键

删除索引时必须知道索引名，删主键只需要指定primary key（主键唯一，索引可以多个）

 

mysql的临时表:

临时表在需要保存一些临时数据的时候非常有用，只在当前连接可见，关闭连接mysql会自动删除临时表释放空间。

create temporary table tt(

tname varchar(30) not null,

tage int default 18,

tpwd varchar(30)

);

插入数据：insert into tt (tname,tage,tpwd) values (“张三”,17,”admin”);

手动删除临时表：drop table tt;

查看所有的表：show tables;