深入浅出MySQL数据库开发、优化于管理维护

MySQL数据库物理文件默认存放位置:C:\ProgramData\MySQL\MySQL Server 5.5\data\

MySQL通过配置my.int的datadir属性来指定数据库的物理存放位置。

一、DDL语句:
1.创建数据库:create database test;

2.删除数据库:drop database test;

3.描述表:desc emp;

4.删除表:drop table emp;

5.修改表:
(1)修改表类型:alter table emp modify ename varchar(20);
(2)增加表字段:alter table emp add column age int(3);
(3)删除表字段:alter table emp drop column age;
(4)字段改名:alter table emp change age age1 int(4);
【备注】:change和modify都可以修改表的定义,不同的是change后面需要写两次列名,但change的优点是可以修改列名称。
(5)修改字段排列顺序:alter table emp add birth date after ename;
alter table emp modify age int(3) first;
(6)更改表名:alter table emp rename emp1;

二、DML语句:
1.插入记录:insert into emp(ename,hiredate,sal,deptno) values('zzx','2000-01-01','2000',1);
【备注】:MySQL特有一次插入多条记录insert into dept values(5,'dept5'),(6,'dept6');

2.更新记录:update emp set sal=4000 where ename='Lisa';

3.删除记录:delete from emp where ename='dony';
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

4.查询记录:select * from emp;
(1)查询不重复的记录:select distinct deptno from emp;
(2)条件查询:select * from emp where deptno=1;
(3)排序和限制:select * from emp order by deptno,sal desc limit 1,10;
【备注】:limit为MySQL特有
(4)聚合:select deptno,count(1) from emp group by deptno with rollup;
select deptno,count(1) from emp group by deptno having count(1)>1;
select sum(sal),max(sal),min(sal) from emp;
(5)表连接:
【备注】:表连接分为内连接和外连接,内连接仅选出两张表中互相匹配的记录,而外连接会选出其他不匹配的记录。外连接又分为左连接和右连接。
1>内连接:select ename,deptname  from emp,dept where emp.deptno=dept.deptno;
2>左连接:select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
3>右连接:select ename,deptname from dept right join emp on dept.deptno=emp.deptno;
(6)子查询:select * from emp where deptno in(select deptno from dept);
如果子查询记录数唯一,还可以用=代替in:select * from emp where deptno=(select deptno from dept limit 1);
【备注】:表连接在很多情况下用于优化子查询。
(7)记录联合:
包含重复:
select deptno from emp
union all
select deptno from dept;
不包含重复:
select deptno from emp
union
select deptno from dept;

三、DCL语句:
创建一个数据库用户xavier,给予test数据库中所有表的select和insert权限:
grant select,insert on test.* to 'xavier'@'localhost' identified by '123456';

收回insert权限:
revoke insert on test.* from 'xavier'@'localhost'.

【备注】:? contents用来显示所有可供查询的分类。例如使用? Data Types来查看MySQL支持哪些数据类型。

四、杂乱知识:
1.删除数据库test下所有前缀为tmp的表:
select concat('drop table ',table_schema,'.',table_name,';') from information_schema.tables where table_schema='test' and table_name like 'tmp%';

2.将数据库下所有存储引擎为myisam的表改成innodb:
select concat('alter table ','table_schema,'.',table_name,' engine=innodb;') from information_schema.tables where engine='MyISAM';

3.加入zerofill参数, 在int位数不够时,补充0:create table t1(id int(5) zerofill);但是,如果int类型的数超过补充位但是没有超过int的表示范围,仍然会显示。同时,MySQL会在有zerofill的列中自动 加入unsigned属性。

4.MySQL中使用auto_increment设置自增主键,自增主键要求为primary key/unique键,且为not null

5.浮点数如果不写精度和标度,则会按实际精度值显示,如果有精度和标度,则会自动将四舍五入后的结果插入,系统不会报错。定点数如果不写精度和标度,则会按照默认值decimal(10,0)来进行操作,如果数据超过了精度和标度值,系统就会报错。

6.对于bit类型,不能直接使用select id from table查询,需要使用bin(id),hex(id)来查看。

7. 如果要用来表示年月日,需要用DATE表示;如果要表示年月日时分秒,需要用DATETIME表示;如果要用来表示时分秒,需要用TIME表示。如果需要 经常插入或者更新日期为当前系统时间,则通常使用TIMESTAMP来表示。TIMESTAMP返回显示为"YYYY-MM-DD HH:MM:SS".TIMESTAMP与时区相关。
【备注】:
create table t1(d date,t time,dt datetime);
insert into t1 values(now());

create table t2(ts timestamp);
inset into t2 values(null);
注意,MySQL只给表中第一个TIMESTAMP字段设置默认值为系统时间,如果有第二个TIMESTAMP类型,则默认值设置为0。

8.使用enum类型:create table t(gender enum('M','F'));
insert into t values('M'),('1'),('f'),(NULL);

9.NULL 不能用于“=”比较,select null=null;的结果为null,而不是1.如果要使用NULL 的安全比较,需要使用"<=>",或者"is null"。select null<=>null,null is null;。

10.字符串函数:
mysql> select concat('aaa','bbb'),concat('aaa',null);
+---------------------+--------------------+
| concat('aaa','bbb') | concat('aaa',null) |
+---------------------+--------------------+
| aaabbb              | NULL               |
+---------------------+--------------------+
1 row in set (0.05 sec)
任何字符串与NULL连接的结果都是NULL

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.02 sec)

mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);

+-----------------------+----------------------+------------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2008                   |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)

11.数值函数:
mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.02 sec)
floor()返回小于x的最大整数

mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)
ceil()返回大于x的最小整数

mysql> select rand(),rand();
+--------------------+--------------------+
| rand()             | rand()             |
+--------------------+--------------------+
| 0.1481672824809023 | 0.5434224144960031 |
+--------------------+--------------------+
1 row in set (0.02 sec)
rand()返回0-1内的随机值

mysql> select round(1.1),round(1.1,2),round(1,2);
+------------+--------------+------------+
| round(1.1) | round(1.1,2) | round(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1.00 |
+------------+--------------+------------+
1 row in set (0.00 sec)
round(x,y)返回参数x的四舍五入的有y位小数的值

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
truncate(x,y)为返回数字x截断为y位小数的结果

12.日期和时间函数:
curdate()返回当前日期,只包含年月日;curtime()返回当前时间,只包含时分秒;now()返回当前的日期和时间,年月日时分秒

mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| May,8th,2015                  |
+-------------------------------+
1 row in set (0.00 sec)
date_format(date,fmt)按字符串fmt格式化日期date

mysql> select date_add(now(),INTERVAL 31 day);
+---------------------------------+
| date_add(now(),INTERVAL 31 day) |
+---------------------------------+
| 2015-06-08 18:53:24             |
+---------------------------------+
1 row in set (0.01 sec)
date_add(date,INTERVAL expr type)返回与所给日期date相差INTERVAL时间段的日期。

mysql> select datediff('2020-01-01',now());
+------------------------------+
| datediff('2020-01-01',now()) |
+------------------------------+
|                         1699 |
+------------------------------+
1 row in set (0.00 sec)
datediff(date1,date2)用来计算两个日期之间相差的天数。

13.流程函数:
if(value,t,f)函数:满足value则使用t,否则使用f替换;
ifnull(value1,value2)函数:用来替换NULL值。

case when [value1] then [result]...else [default] end:用when...then。

14.常用函数:
database(),version(),user(),inet_aton(IP)返回IP地址的数字表示,inet_ntoa(num)返回数字表示的IP地址,password(str)返回字符串str的加密版本,md5()。

四、MySQL数据库引擎
MySQL中仅有InnoDB和BDB支持事务,MyISAM等引擎不支持事务。
1.查看MySQL支持的引擎:
show engine;支持当前数据库支持的存储引擎。
show variables like 'have%';也可以查看。

2.创建表时指定引擎:
create table ai(
    i bigint(20) not null auto_increment,
    primary key(i)
)engine=MyIsam default charset=utf8;

3.修改表的引擎:
alter table ai engine=innodb;
修改表的引擎

4.查看表的引擎:
show create table ai;

5.MyISAM 引擎:不支持事务,也不支持外键。其优势是访问速度快,对事务完整性没有要求,或者以select,insert为主的引用可以使用此引擎。每个 MyISAM引擎在磁盘上存储成.frm(存储表定义),.MYD(存储数据),.MYI(存储引擎)3个文件。数据文件和索引文件可以放置在不同的目 录,平均分布IO,以求获得更快的速度。
MyISAM的表具有三种存储格式:静态表,动态表,压缩表。
【备注】:可以使用check table语句来检查MyISAM表的将抗,并且用repair table语句来修复一个损坏的MyISAM表。使用optimize table语句可以优化表提高性能。
在cmd下使用myisampack工具可以创建压缩表。

6.InnoDB 引擎:具有提交,回滚和崩溃回复能力的事务安全,但是处理效率相对与MyISAM引擎差,并且会占用更多的磁盘空间以保留数据和索引。InnoDB引擎在 磁盘上表结构存储在.frm(存储表定义)文件中,数据和索引保存在innodb_data_home_dir和 innodb_data_file_path。
(1)自动增长列 auto_increment:
alter table autoincre_demo auto_increment=7;来设置自动增长列的初始值。
select last_insert_id();来查询当前线程最后插入记录所使用的主键。(当使用insert一次插入多条数据时,在最前面的数据最后插入,在最后面的数据最先插入,但是在表中顺序仍然为从前向后插入)。
mysql> insert into autoincre_demo values ('11'),('12'),('13');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into autoincre_demo(name) values ('11'),('12'),('13');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+----+------+
| i  | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  7 | 7    |
|  8 | 11   |
|  9 | 12   |
| 10 | 13   |
+----+------+
7 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)
此处可以看出,一次插入多条数据11,12,13.表中顺序仍然为8-11,9-12,10-13.但是,使用last_insert_demo后显示的为最后插入的11的主键:8.

对于InnoDB表,自动增长列必须为索引(主键就是一种索引)。如果是组合索引,也必须是组合索引的第一列。但是对于MyISAM表,自动增长列可以是组合索引的其他列。

(2)外键约束:
restrict 和no action相同(restrict是在企图删除父表中的一行的时候,如果子表中有记录则阻止。no action是先删除父表中的记录,再去查看子表中的记录,如果有记录,则回滚父表的删除),是指限制在子表有关联记录的情况下父表不能更 新;cascade表示父表在更新或者删除时,更新或者删除子表对应的记录;set null则表示父表在更新或者删除的时候,子表的对应字段被设置成NULL。

6.MEMORY引擎:使用存在于内存中的内容来创建表。每个MEMORY表值实际对应一个磁盘文件,存储格式是.frm。MEMORY类型的表访问非常快,但是一旦服务关闭,表中的数据就会丢失掉。
create table t1 engine=memory select * from t;创建
show table status like 't1';查看表信息
create index t1_index_hash using hash on t1(id);创建hash索引
show index from t1;查看t1表的索引
drop index t1_index_hash on t1;删除t1表上的hash索引
create index t1_index_btree using btree on t1(id);创建btree索引
【备注】:MySQL中还有其他的存储引擎,例如:MERGE引擎(一组MyISAM表的组合),Infobirght引擎(列式存储引擎),TokuDB(高写性能高压缩引擎)

五、MySQL中选择合适的数据类型
1.char和varchar类型:
char 属于固定长度的字符类型,而varchar属于可变长度的字符类型。由于char是固定长度,所以他的处理速度比varchar快得多,但缺点是浪费时 间,程序需要对行尾空格进行处理。varchar对于'abcd'四个字符,需要5个字节(多一个),而char只需要4个字节。使用MyISAM或 MEMORY引擎时,推荐使用char类型;对于使用InnoDB引擎,建议使用varchar类型。

2.text和blob类型:
blob 用来保存二进制数据,比如图片;而Text只能保存字符数据,比如文章和日记。text中又分为text,mediumtext,longtext。 blob分为blob,mediumblob,longblob,它们的主要区别是存储文本长度不同和存储字节不同。由于blob和text值可能会引起 一些性能文件,建议使用optimize table进行碎片整理功能。
应该使用合成索引来提高大文本字段(blob或text)的查询性能。(使用md5()函数,或者sha1(),crc32()等函数,对大文本字段生成索引,提高查询性能)
在不必要的时候,避免检索大型的blob或text值。将blob或text列最好分离到单独的表中,提高性能。
【 备注】:repeat("hello",2)函数可以将字符串"hello"重复2次。结果为:"hellohello"

3.浮点数与定点数:
MySQL 中float,double,real用来表示浮点数,使用decimal或number来表示定点数。浮点数一般用于表示含有小数部分的数值,如果插入 数据的精度超过了该列定义的实际精度,则会四舍五入。定点数实际上是按照字符串形式存放的,如果插入数据超过了实际定义的精度,同时SQLMode处于 traditional传统模式下,则会报错,导致数据无法插入;如果插入数据超过了实际定义的精度,同时处于默认SQL Mode下,则会进行警告,但是数据按四舍五入存入。
对于精度要求比较高的应用(例如货币)要使用定点数而不是浮点数来保存数据。
同时,在编程时候要注意浮点数的误差问题,尽量避免浮点数的比较。同时要注意浮点数中的一些特殊值处理。

4.日期类型选择:
按 照实际需要选择能够满足引用的最小存储的日期类型。如果要记录年月日时分秒,并且记录的年份比较救援,则最好使用datetime,而不要使用 timestamp。如果记录的日期需要让不同时区的用户使用,那么最好使用timestamp,因为日期类型中只有它能够和实际时区相对应。

【备 注】:MySQL中使用show character set来查看所有可用的字符集。使用desc information_schema.character_sets显示所有字符集和该字符集默认的校对规则。使用show collation like 'gbk%'查看相关字符表的校对规则。(ci表示大小写不敏感,cs表示大小写敏感,bin表示二元)。MySQL的字符集和校对规则有4个级别的默认 设置:服务器级,数据库级,表级和字段级。

【备注】:使用索引的原则:搜索的索引列不一定是所要选择列;使用唯一索引,索引的列的基数越大,索引的效果越好,索引选择性别列最多也就有一半,效果不好;使用短索引;利用最左前索引;不要过度使用索引;InnoDB尽量自己制定主键。
在对索引字段进行范围查找是,只有btree索引可以通过索引访问。而hash索引实际上是全表扫描

【备注】:视图相对于普通的表的优势主要体现在:简单,安全,数据独立。
创建视图:create or replace view staff_list_view as select s.staff_id,s.first_name from staff;
MySQL的视图定义有一些限制,from关键字后不能包含子查询。
视图的更新性和视图中的查询定义有关。以下视图是不可更新的:
包含以下关键字的SQL语句:聚合函数,distinct,group by,having,union,union all;常量视图;select中包含子查询;join;from一个不能更新的视图;where字句的子查询引用了from字句中的表。

删除视图:drop view staff_list_view;
查看视图信息的操作:show table status like 'staff_list';
查询某个视图的定义:show create view staff_list_view;
查看系统表information_schema.views也可以查看视图的相关信息。
PS:貌似没有更改视图的!!!

六、存储过程和函数
存储过程和函数是事先经过编译并且存储在数据库中的一段SQL语句集合。函数必须要有返回值,而存储过程可以没有。
MySQL的存储过程和函数中允许包含DDL语句,也允许在存储过程中执行提交,但是不允许执行load data infile语句。
delimiter $$ 将语句结束符改成其他符号$$
delimiter ; 将语句结束符改成;

1.创建存储过程
create procedure film_in_stock(IN p_film_id INT,IN p_store_id INT,OUT p_film_count INT)
begin
  select inverntory_id
  from inventory
  where film_id=p_film_id
  and store_id=p_store_id
  and inventory_in_stock(inventory_id);

  select FOUND_ROWS() into p_film_count;
end;

2.删除存储过程
drop procedure film_in_stock;

3.查看存储过程的状态
show procedure status like 'film_in_stock';

4.查看存储过程的定义
show create procedure film_in_stock;

5.通过information_schema.Routines了解存储过程和函数的信息
select * from routines where ROUTINE_NAME='film_in_stock';

6.光标的使用:
create procedure payment_stat()
begin
  declare i_staff_id int;
  declare d_amount decimal(5,2);
  declare cur_payment cursor for select staff_id,amount from payment;
  declare exit handler for nou found close cur_payment;

  set @x1=0;
  set @x2=0;

  open cur_payment;

  repeat
    fetch cur_payment into i_staff_id,d_amount;
      if i_staff_id=2 then
        set @x1=@x1+d_amount;
      else
        set @x2=@x2+d_amount;
  until 0 end repeat;

  close cur_payment;

end;

7.流程语句的使用:if,case,loop,leave,iterate,repeat,while.
(1)if:
      if i_staff_id=2 then
        set @x1=@x1+d_amount;
      else
        set @x2=@x2+d_amount;

(2)case:
     case i_staff_id
         when 2 then
            set @x1=@x1+d_amount;
         else
            set @x2=@x2+d_amount;
     end case;

(3)loop,leave,iterate:
create procedure actor_insert()
begin
  set @x=0;
  ins:Loop
    set @x=@x+1;
    if @x=10 then
      leave this;
    elseif mod(@x,2)=0 then
      iterate ins;
    end if;
    insert into actor(actor_id,first_name,last_name) values(@x+200,'Test',@x);
  end loop ins;
end;

8.使用事件调度器
(1)创建事件调度器,每5s往表中插入数据:
create event test_event _t
on schedule
every 5 second
do
insert into test.t(id,time) values('test',now());

(2)打开调度器:
set global event_scheduler=1;

(3)创建事件调度器,每分钟清空表:
create event test_event_trunc
on schedule
every 1 minute
do
truncate table t;

(3)禁用调度器:
alter event test_event_t disable;

(4)删除调度器:
drop event test_event_t;

七、触发器:
1.创建触发器:
create trigger ins_film_bef
before insert on film for each row begin
   insert insto tri_demo(note) value("before insret");
end;

2.删除触发器:
drop trigger ins_film_bef;

3.查看触发器:
show triggers;
desc triggers;
select * from triggers where trigger_name='inf_film_bef';

八、事务控制和锁定语句
1.表锁:
lock table t read;
unlock tables;

2.事务控制:
start transaction
commit
rollback

【备注】:MySQL解决SQL Injection的方法:PrepareStatement和变量绑定,使用引用程序提供的转换函数,自己定义函数进行校验和过滤。

【备注】:SQL Mode:
select @@sql_mode;查看SQL所有的模式。
set sessopm sql_mode='ANSI'; 设置SQL模式。

九、MySQL分区:
【备注】:MySQL分区都要求分区键必须是INT类型。同时分区的名字是不区分大小写的。
MySQL分区类型:
RANGE分区:基于一个给定连续区间范围,将数据分配到不同的分区
LIST分区:累死RANGE分区,区别在LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区
HASH分区:基于给定的分区个数,把数据分配大不同的分区
KEY分区:类似于HASH分区

1.RANGE分区:
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  spearated date not null default '9999-12-31',
  job varchar(30) not null,
  store_id int nou null
)
partition by range(store_id)(
  pattition p0 values less than (10),
  partition p1 values less than (20),
  partition p2 values less than MAXVALUE
);

RANGE分区中,NULL值是最小值。

2.LIST分区:
create table expenses(
  expense_date date not null,
  category int,
  amount decimal(10,3)
)
partiton by list(category)(
  partition p0 values in(3,5),
  partition p1 values in (1,10),
  partition p2 values in (6)
)
LIST分区中,需要任何值都必须在值列表中找到。

3.Columns分区:
Columns分区相比与RANGE分区和LIST分区,支持更多的数据类型,同时也支持多列分区。
create table t(
  a int,
  b int
)
partiton by range columns(a,b)(
  partition p01 values less than(0,10),
  partition p02 values less than(10,10),
  partition p03 values less than(maxvalue,maxvalue)
);

4.HASH分区:
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  separated date nou null default '9999-12-31',
  job varchar(30) not null,
  store_id int not null
)
partition by hash(store_id) partitions 4;

5.KEY分区:
KEY分区不允许使用用户自定义的表达式,需要使用MySQl服务器提供的HASH函数。HASH分区只支持整数分区,但是KEY分区还支持BLOB or TEXT类型作为分区列,也可以不制定分区键,默认会首先选择使用主键作为分区将。
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  separated date nou null default '9999-12-31',
  job varchar(30) not null,
  store_id int not null
)
partition by hash( ) partitions 4;

6.删除分区:
alter table emp_date drop partition p2;

7.增加分区:
alter table epenses add parittion(partition p5 values in(7,8));

8.拆开分区:
alter table emp_date reorganize partition p3 into(
  partition p2 values less than(2005),
  partition p3 values less than(2015)
);

转载于:https://www.cnblogs.com/xavierjzhang/p/4501492.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值