mysql记录
图形化界面工具:navicat、hedisql、sqlyag、phpAdmin等
1. 创建数据库:create database xxx charset=utf8;(指定编码格式,避免以后插入中文数据时,出现乱码)
2. 显示数据库结构:show create database dbxxx;
3. 删除数据库:drop database dbxxx;
数据库授权:
小总结:
mysql,所有用户的权限都是在user表中进行设置的,想要远程连接,需要在mysql数据库的user表中加个host为%的记录
当你对mysql的user表进行了操作后,需要执行下面的命令,使条件生效
数据库存储引擎:
1. 存储引擎简介:
用户可以根据自己的不同要求,选择不同的存储方式、是否进行事务处理等。存储引擎是基于表的,同一个数据库,不同的表,存储引擎可以不同。甚至同一个数据库表,在不同的场合可以应用不同的存储引擎。
2. 常见的存储引擎分类:
1) InnoDB存储引擎:
InnoDB给Mysql的表提供了事务、回滚、崩溃修复能力、多版本并发控制的事务安全。
InnoDB支持外键,支持事务(如果某张表主要提供OLTP支持,需要执行大量的增删改查操作,出于事务安全方面的考虑,InnoDB是更好的选择)
2) MyISAM存储引擎:
MyISAM是Mysql默认的存储引擎,采用操作系统文件缓存机制
MyISAM不支持事务、不支持外键
InnoDB与MyISAM的区别:
a. InnoDB是行级锁,myisam是表级锁,所以现在数据库优化默认存储引擎直接改为了InnoDB
b. 当数据库中有大量的写入、更新操作而查询较少或数据完整性要求较高时,选择InnoDB表;
当数据库主要以查询为主,而更新、写入较少,且业务完整性要求不那么严格时,选择MyISAM表,因为MyISAM的查询操作效率和速度都比InnoDB快
3) Memory存储引擎:
其使用存储在内存中的内容来创建表,且所有数据也是放着内存中,读写速度较前两者要快,因为数据都是放在内存中,一般可以用来做一些临时表
3. 有关存储引擎的命令:
1) 查看mysql服务支持的存储引擎:show engines
2) 指定表的存储引擎:create table tmp(xxx)Engine=MyISAM
3) 设置默认存储引擎:set default_storage_engine=MyISAM (该命令只是临时把存储引擎改了,mysql重启后会恢复默认的存储引擎,如果要永久修改,需要修改mysql的配置文件)
DDL语句:
1. 建表语句:
create table 表名(
列名1 列类型[<列的完整性约束>],
列名2 列类型[<列的完整性约束>],
.......
)
例如:create table students(
id int(10) not null auto_increment primary key,
name varchar(20) not null,
phone int(10) unique
)engine=InnoDB default charset=utf8;
2. 常见的约束:
primary key --- 主键
unique --- 唯一性约束
not null --- 非空
auto_increment --- 自增长
default default_value --- 默认值约束
default cur_timestamp --- 创建新纪录时默认保存当前时间(仅适用timestamp数据列)
on update cur_timestamp --- 修改纪录时默认保存当前时间(仅适用timestamp数据列)
character set name 指定字符集 --- 仅适用于字符串
3. 修改表:
1. 修改表名:alter table 旧表名 rename [to] 新表名 (eg:alter table school rename school2)
2. 修改字段的数据类型:alter table 表名 modify 属性名 数据类型 (eg: alter table school modify name char(30))
3. 修改字段名及其数据类型:alter table 表名 change 旧属性名 新属性名 新数据类型 (eg: alter table school change name name_new char(25))
4. 增加字段:alter table 表名 add 属性名1 数据类型[完整性约束条件][FIRST|AFTER 属性名2]
参数解释:a 增加无完整性约束条件的字段;b 增加有完整性约束条件的字段; c 在表的第一个位置增加字段; d 在属性名2之后增加字段
eg: alter table school add address varchar(50) not null first;
5. 修改字段的排列位置:alter table 表名 modify 属性名1 数据类型 FIRST|AFTER 属性名2 (字段修改到第一个位置,字段修改到指定位置)
eg:alter table school modify address varchar(50) after name;
6. 删除字段:alter table 表名 drop 字段名 (eg: alter table school drop addr)
7. 更改表的存储引擎:alter table 表名 engine=存储引擎名 (eg: alter table school engine=MyISAM)
插入语句:
1. 如果插入的values为该表中所有字段值,insert into tablexxx values(col1Val,col2Val,col3Val,col4Val);
2. 如果插入的values为该表中的部分字段值,insert into tablexxx(col1,col2,col3) values(col1Val,col2Val,col3Val);
3. 同时插入多条数据,insert into tablexxx values
(col1Val,col2Val,col3Val,col4Val),
(col1Val,col2Val,col3Val,col4Val),
(col1Val,col2Val,col3Val,col4Val),
(col1Val,col2Val,col3Val,col4Val);
4. 将查询结果插入到另一张表中,insert into 表名1(属性列表1) select 属性列表2 from 表名2 where 条件表达式
eg:create table new_stu like students;
insert into new_stu select * from students;
select * from new_stu;
5. 使用replace插入新纪录,3种语法格式
format1:replace into 表名 [(字段列表)] values (值列表)
format2:replace [into] 目标表名[(字段列表1)] select (字段列表2) from 源表 where 条件表达式
format3:replace into 表名 set 字段1=值1, 字段2=值2
使用replace语句向表中插入新纪录时,如果新记录的主键值或唯一性约束的字段值与已有记录相同,则已有记录先被删除,然后再插入新纪录。
使用replace最大的好处就是可以将delete和insert合二为一,形成一个原子操作,这样就无需将delete和insert操作置于事务中了
eg1: replace into student values('hello', 'name', 19);
更新语句:
format: update 表名 set 属性名1=value1, 属性名2=value2,... , 属性名n=valueN where 条件表达式
eg1:update students set name='helloWorld' where id=10001;
eg2:update 多表修改(主要把多张表关联起来就可以了)
update stu, score set stu.age=18, score.grade=1000 where stu.stu_id=score.st_id and stu.stu_name='haha';
删除语句:
format: delete from 表名 where 表达式
1) delete from students(如果不加后面的where条件表达式,则证明是删除表中所有数据)
2) delete from students where id=10001;
查询语句:
select 属性列表 from 表名或视图列表
where 条件表达式1
group by 属性名1 having 条件表达式2
order by 属性名2 asc/desc
聚合函数:count(col1), avg(col1), max(col1), min(col1)
子查询(反复用到的东西--子查询和表连接)
1. 关联子查询:这两个表里的记录之间有关系(员工表里的部门号和部门表里的部门号代表的是相同的列,通过列来表达之间的关系):
两张表的列写成一个条件表达式,叫给这个表建关联 如:emp.deptno=dept.deptno
2. 非关联子查询 :没建立关联 单列的
子查询:可用于select、update语句
eg1: 一个select语句嵌套另一个select语句
select * from score where stu_id=(select stu_id from stu where stu_name='haha')
update score set grade=100 where stu_id=(select stu_id from stu where stu_name='haha')
eg2: 还可以把子查询的结果作为一张表,对其进行查询
select aa.stu_name, aa.sex, b.score from score a, (select stu_id,stu_name,sex from stu where stu_name='haha') as aa where a.st_id=aa.stu_id;
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
select * from employee limit 3, 7; // 返回4-11行
select * from employee limit 3,1; // 返回第4行
一个参数
单引号和双引号是有完全区别的
双引号用在(别名上) 如:列别名 (严格按照双引号里面的内容来执行)别名中包含空格活希望大小写敏感时用双引号
单引号用在字符串
like 模糊查询
它的通配符%和_
%表示0或任意多个字符
_表示任意一个字符
's' 's%'(1到多个字符) 's_'(2个字符)
eg1:哪些员工的名字的第二个字符是a?
SQL> select ename from emp_liushu where ename like '_a%';
eg2:哪个员工的名字是以“yan_开头的?
SQL> select ename from emp_liushu where ename like 'yan\_%' escape '\'; // \_表示转义字符,escape \ 表示加\的东西进行转义
其他:
1) between xx and xx (select * from students where age between 18 and 28;)
2) 去重:distinct (select distinct age from students where class_name='wuhh' and address like '北京市%')
3) in: select * from students where class_name in('class1', 'class2', 'class3', 'class4');
4) 查询class1的男女生人数(用到了表的别名,以及group by后面加having,而having后面没加聚合函数):
a: select a.sex, count(a.id), a.class_name from students a where a.class_name='class1' group by a.sex;
b: select a.sex, count(a.id), a.class_name from students a group by a.sex having a.class_name='class1';
5) 比较运算符:= != <> > >= < <= 等
合并结果集:
1. union [all]:使用union [all]可以将多个select语句的查询结果及组合为一个结果集
format:select 字段列表1 from table1 union [all] select 字段列表2 from table2;
注意:字段列表1与字段列表2的字段个数必须相同,且具有相同的数据类型,才能使用union或union all合并结果集;合并长生的新结果集的字段名与字段列表1中的字段名对应
eg1: select id, name from students union select id, name from new_stu;
2. union与union all的区别:
union会对结果集自动去重,即在结果集合并后会对新产生的结果集进行排序运算,效率稍低;而是用union all,会直接合并2个结果集不去重,效率高于union。
所以如果可以确定合并前的2个结果集中不包含重复的记录,建议使用union all
操作符的一些肯定及否定形式的理解
肯定形式 否定形式
is null is not null
like not like (not like '_a%';第二个字符不是a的)
between and(包含) not between and(不包含)
in(=or)=any(只要随便找个相等的就行) not in (<> and) <>all(和集合中所有每个元素都不能相等) (job<> 'Manager'and <> 'clerk' and <> 'salesman')
where和having的区别
where过滤的是记录 where后面可以跟单行函数(原封不动的对应到记录上的),不能跟组函数,可以跟所有的列
having过滤的是组 having后面可以跟组函数,不能跟单行函数,可以跟group by后面的列(即组标识)
(单行函数跟记录是对应的)(门当户对)(组函数和组对应)
外连接 outer join(最核心的是谁是驱动表)(外连接的基础是内连接)(如何判断谁作驱动表?你希望哪张表的记录全都出现在结果集里,就把谁作驱动表)
from ti left outer join t2 (左外连接,通常把outer省略,所以叫左连接) on t1.c1=t2.c2 左边的表作驱动表 结果集=内连接的结果集+t1表中匹配不上的记录和t2的一条null记录的组合
from ti right outer join t2 (右外连接,通常把outer省略,所以叫右连接) on t1.c1=t2.c2 右边的表作驱动表 结果集=内连接的结果集+t2表中匹配不上的记录和t1的一条null记录的组合
from ti full join t2 (全连接) on t1.c1=t2.c2 结果集=内连接的结果集+t1表中匹配不上的记录,t2表中匹配不上的记录和t1的一条null记录的组合+t2的一条null记录的组合(full连接用的较少)
在匹配不上时,内连接和外连接最大的不同是处理方式不同,内连接
外连接时:驱动表的所有记录都会出现在结果集里,一个都不能少
当驱动表里的记录与匹配表里的记录匹配时,内外连接是完全一样的
当驱动表里的记录与匹配表里的记录在匹配表里找不到记录时,内连接和外连接是不一样的:具体如下
对于内连接来说,当驱动表里的记录在匹配表里找不到记录时,会自动过滤掉
对于外连接类说,当驱动表里的记录在匹配表里找不到记录时,这条记录依然会出现在结果集里,系统会模拟造成一个空记录
表连接from(有相同的属性)
内连接表示这两张表之间的记录应如何匹配来解决
同一张表之间的列(记录)之间是有关系的,想都不想就用“自连接“
根据结果集的产生不一样,可以分为内连接和自连接
from emp_ls e join emp_ls m
根据结果集的产生方法不一样,将连接分为内连接和外连接(它们对匹配不上的记录的处理方式不同)
inner join :匹配不上的就过滤掉
有 等值连接(员工名字和部门名字 结果集中不包括40)、自连接的形式(列出员工名字和领导名字,结果集中不包含张三丰)
outer join :匹配不上的还要留下(一个都不能少)
有等值连接(员工名字和部门名字 结果集中包括部门40)、自连接的形式(列出员工名字和领导名字,结果集中包含张三丰)
等值连接(当你连接的这两张不同的表里有描述共同属性的列)
自连接(同一张表的记录直接有关系,用自连接)
outer join 有两种用法
1 让某一张表的记录都出现在结果集里,用外连接 ---- 除了匹配的记录出现在结果集里,不匹配的记录也要出现在结果集中。如:员工名字和领导名字找出来,把张三丰也找出来
2 结果集中只是所有不匹配的记录,用outer join + where 匹配表.主键列 is null
执行顺序:
mysql中,select基本语法形式如下:
select 属性列表 from 表名或视图列表
where 条件表达式1
group by 属性名1 having 条件表达式2
order by 属性名2 asc/desc
执行顺序:from--where--select--group by--having--order by(order by一定是最后执行)
发生连接之前对e表做过滤,用and(执行顺序:from 执行and 然后发生外连接outer join,再执行where --->select)
SQL> select e.empno,e.ename,d.dname,d.deptno
2 from emp_liushu e right join dept_ls d
3 on e.deptno=d.deptno
4 and e.ename='zhangwuji' //and发生在外连接之前(在外连接之前对匹配表的数据进行过滤的话,需要用and实现)
5 where e.empno is null;//where 发生在外连接之后(实际上是对结果集的过滤)(如果需要在outer join之后对结果集进行过滤,该过滤是通过对匹配表的主键列来实现的话,用where子句)
数据库的备份与恢复:
1. 备份:mysqldump -uUserName -pPwd dbname table [option] > xx.sql ;
2. mysqldump常用参数:
--all-databases, -A 导出全部数据库
--add-drop-database, 每个数据库创建之前添加drop数据库语句
--no-data, -d 不导出任何数据,只导出数据库表结构
--no-create-db, -n 只导出数据,而不添加create database语句
--no-create-info, -t 只导出数据,而不添加create table语句
eg1:导出所有数据库的所有表数据--- mysqldump -uroot -p123456 -A > /tmp/all.sql
eg2:导出prac数据库中的所有数据,不包括表结构--- mysqldump -uroot -p123456 prac -n -t > /tmp/allWithNoStructure.sql
eg3:导出prac数据库中的所有表结构,不包括数据--- mysqldump -uroot -p123456 prac -d > /tmp/allWithNoData.sql
eg4:导出prac数据库中所有的学生信息--- mysqldump -uroot -p123456 prac students -n -t > /tmp/students.sql
3. 恢复数据:mysql -uUsername -pPwd < xx.sql;
eg1: 恢复刚才备份的prac数据库中的数据--- mysql -uroot -p123456 prac < prac.sql
eg2:恢复刚才备份的所有数据库的数据----- mysql -uroot -p123456 < all.sql
存储过程:
1. 实例:
delimiter $$;
create procedure test_p11(count int)
begin
declare name varchar(20);
declare phone int(20);
declare i int;
set i = 0;
while i<count do
set name=CONCAT('安大叔',i);
set phone=18612545+i;
insert into stu (stu_name,phone,salary)values (name,phone,i);
set i=i+1;
end while;
end
$$;
delimiter;
2. 调用存储过程: call test_p11(100)
索引:
1. 索引定义:
索引是有数据库表中一列或多列组合而成,其作用是提高对表中数据的查询速度。索引是创建在表上的,是对数据库表中一列或多列进行排序的一种结构。
2. 索引的优缺点:
优点:a) 增加查询速度 b) 利用索引的唯一性来控制记录的唯一性 c) 降低查询中分组和排序的时间 d) 可以加速表与表之间的连接
缺点:a) 存储索引占用磁盘空间,所以不能创建太多的索引 b) 执行数据修改操作(insert update delete)产生索引维护 c) 每次修改表结构都需要重新创建索引
3. 索引的分类:
1) 普通索引:没有任何限制
2) 唯一性索引:索引列的值必须唯一,但允许有空值(注意和主键不同)
3) 全文索引:FULLTEXT索引仅可用于MyISAM表
4) 单列索引、多列索引
4. 创建索引:
1) 创建普通索引:create index 索引名 on 表名(列) ; alter table 表名 add index 索引名 (列)
2) 创建唯一索引:create unique index 索引名 on 表名(列) ; alter table 表名 add unique index 索引名 (列)
5. 删除索引:drop index 索引名 on 表名 (eg: drop index xx_index on students;)
6. 索引设计原则:
1) 选择唯一性索引
2) 为经常需要排序、分组和联合操作的字段建立索引
3) 为常作为查询条件的字段建立索引
4) 限制索引的数目
5) 尽量使用数据量少的索引
6) 尽量使用前缀来索引
7) 删除不再使用或很少使用的索引
视图:
1. 定义:
视图时从一个或多个表中导出的表,是一种虚拟存在的表,就像一个窗口,通过这个窗口可以看到系统专门提供的数据。视图可以使用户的操作更方便,而且可以保障数据库系统的安全性。视图中的数据时依赖于原来的表中的数据的,一旦表中数据发生改变,显示在视图中的数据也会发生改变。
2. 作用:
1) 使操作简单化
2) 增加数据的安全性
3) 提高表的逻辑独立性
3. 创建视图:
create view 视图名称(视图列1,视图列2) as select 语句
eg:create view AAView as select * from students;
4. 查看视图:
1) desc 视图名称
2) show table 视图名称
3) show create view 视图名称
4) information_schema数据库中查看所有数据库中的视图:select * from information_schema.views
5. 修改视图:
1) create or replace view 视图名称(列1,列2) as select语句
2) alter view 视图名称(列1,列2) as select语句 (eg: alter view view1(id,name) as select id,name from students limit 10;)
6. 删除视图:
drop view [if exists] 视图名列表 (eg: drop view if exists view1, view2 ===> 同时删除创建的2个视图)
7. 注意:视图中后面的select语句不能有子查询,如果非要有的话,可以先把子查询作为一个视图,然后将该视图作为一个表再行查询