《深入浅出mysql数据库开发、优化与管理维护》sql基础(DDL、DML、DCL语句)

链接数据库 mysql -uroot -hlocalhost-p
密码

一、DDL语句(对数据库内部对象进行创建、删除、修改等操作的语言)
1、创建数据库 test1
语法: CREATE DATABASE dbname
例子:create database test1

2、查看所有库
show databases;

3、使用库
use dbname;

4、查看库中的表
show tables;

5、删除库
drop database dbname;
例子:drop database test1;

6、创建表
语法：create table tablename(column_name1 column_type1 constraints1, column_name2 column_type2 constraints2);
(column_name1(名字) column_type1 (数据类型) constraints1(约束条件))
例子：create table emp(ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2));
create table u_c_t_ab(id int(11) primary key auto_increment, uin int(11) not null , tid int(11) not null , addtime int(11) not null, c_t_status tinyint(1) not null default 1) ;

7、查看表的定义(列、数据类型、约束条件等)
desc tablename;
更详细的可用:show create table emp \G;
其中\G代表按字段竖向排列
engine 代表迎请 charset代表字符集

8、删除表
drop table tablename；

9、修改表
column 可以不写
a、修改表类型
语法:alter table tablename modify [column] column_definition[first|after col_name]
例子:alter table emp modify ename varchar(20); 将表emp中的ename 的类型改为varchar(20)

b、增加字段
语法:alter table tablename add[column] column_definition[first|after col_name]
例子:alter table emp add column age int(3); 在表emp中增加age，类型为int(3)
例子:ALTER TABLE market_goods_list ADD limit_buy TINYINT( 2 ) NOT NULL DEFAULT ‘0’ COMMENT ‘0不限购,1限购’ AFTER remain_count

c.添加自增字段
ALTER TABLE user_relation_aa ADD id INT( 11 ) PRIMARY KEY AUTO_INCREMENT FIRST ;
ALTER TABLE test_lz ADD unickname VARCHAR( 32 ) UNIQUE ;
ALTER TABLE test_lz ADD UNIQUE (unickname);

一次添加多个字段
alter table tinfo_aa
add t_flower_count int(11) not null default 0 COMMENT ‘鲜花数量’,
add t_official_mark int(11) not null default 0 COMMENT ‘1官方收录’,
add t_reward_flower int(11) not null default 0 COMMENT ‘0 无，正数为悬赏数量’,
add t_is_rewarded tinyint(2) not null default 0 COMMENT ‘1 已悬赏’,
add t_popular_buy int(11) not null default 0 COMMENT ‘推广位’

c、删除表字段
语法:alter table tablename drop[column] col_name
例子:alter table emp drop column age; 删除表emp中的 age字段

d、字段改名
语法:alter table tablename change [column] old_column_name column_definition[first|after col_name]
例子:alter table emp change age age1 int(4); 将表emp中的age改名为age并修改字段为int(4)

e、修改字段排列顺序
[first|after col_name]就是用来排序的
两种排序方式 first最前面
after ename 在ename后面
例子:alter table emp modify age1 varchar(20) first; 将age1排序到最前
alter table emp modify age1 varchar(20) after ename;将age1排序到ename后面

f、修改表名
语法:alter table tablename rename[to] new_name
例子:alter table emp rename emp1; 将emp改名为emp1

二、DDL语句(对表记录的操作语句)
1、插入记录
语法:insert into tablename(field1, field2, field3,…,fieldn) values(value1,value2,value3,…,valuen);
例子:insert into emp(ename,hiredate,sal,deptno) values(‘lizuhi’,’1013-05-15’,’2000’,2);
可以只对部分字段插入值：
insert into emp(ename,sal) values(‘lizuhi’,’2000’);
可以一次插入多个值(以逗号隔开)：insert into emp(ename,hiredate,sal,deptno) values(‘lizuhi’,’1013-05-15’,’2000’,2),(‘lizuhi2’,’1013-05-15’,’2000’,2) ,(‘lizuhi3’,’1013-05-15’,’2000’,2);

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。例如，如果列a被定义为UNIQUE，并且包含值1，则以下 两个语句具有相同的效果：
//先执行插入，如果对应的行存在，就改为更新
INSERT INTO TABLE (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

2、更新记录
语法:update tablename set file1=value1,file2=value2,…,filen=valuen [where condition]
例子:update emp set ename=’haofei888’ where sal=’2000’; 将sal=’2000’的行对应的ename改名为 haofei888

同时更新多个表 update tablename1, tablename2,…,tablenamen set tablename1.file1=value1, tablename2.file1=value1,.. [where condition]
例子:update emp,dept set emp.ename=’liuzhi’,emp.sal=100,dept.depton=3;
or update emp a,dept b set a.ename=’liu’,a.sal=99,b.depton=7;

3、删除：
语法:delete from tablename [where condition]
例子:delete from dept where deptname=’aa’; 删除dept表中deptname=’aa’的记录

可以一次删除多个表中的多个记录
语法:delete tablename1,tablename2 from tablename1,tablename2 [where condition]
delete emp,dept from emp,dept where deptname=’bb’ and depton=2; 删除表emp 和 dept表中符合 deptname=’bb’ and depton=2 条件的记录

4、查询
语法:select * from tablename [where condition]
或者 select column1,column2..,columnn from tablename [where condition]

a、查询不重复的记录
select distinct ename from emp;
select distinct ename,deptno from emp;

b、条件查询
where condition
例如 select * from emp where deptno=6;
select * from emp where deptno=6 or ename=’liuzhi’;
支持 < > <= >= !=
or and 多个条件

c、排序和限制
select * from tablename [where condition] [order by field1 [DESC | ASC],…, fieldn [DESC | ASC]]
DESC 表示降序排列
ASC 表示升序排列(默认)
部分显示 limit
select * from tablename [where condition] [order by field1 [DESC | ASC],…, fieldn [DESC | ASC]] [linit offset_start,row_count]
offset_start 其实偏移量
row_count 显示行数
例如:select * from emp order by sal limit 3; 按sal 升序排序后 显示前3行；
select * from emp order by sal limit 3,3;按sal 升序排序后 从第三条起，显示3行也就是 4~6行

d、聚合操作
①统计
select [field1, field2..,fieldn] fun_name from tablename [where where_contition] [group by field1, field2..,fieldn [with rollup] ] [ having where_condition]
解释:
fun_name 表示要做的聚合操作，也就是聚合函数 常用的有 sum (求和)、 count(*) (记录数) 、max(最大值)、 min (最小值)
group by 要分类聚合的字段
with rollup 对分类后的结果在汇总(相当于没用group by而得到的结果)
having 对分类后的结果再过滤
例如:select count(1) from emp;统计emp的总行数数量
select deptno,count(1) from emp group by deptno; 统计deptno值相同的数量，并显示deptno
select deptno,count(1) from emp group by deptno with rollup;统计deptno值相同的数量，并统计deptno总的数量
select deptno,count(1) from emp group by deptno having count(1)>1;统计deptno值相同的数量，并筛选出deptno值大于1的项

②求和 最大值 最小值
select sum(sal),max(sal),min(sal) from emp; 计算sal的总和，得出最大值 最小值

e、表连接
内连接:内连接仅仅选出两张表中互相匹配的记录； 43页
例如：select ename, deptname from emp , dept , where emp.deptno=dept.deptmo;

外连接:外连接会选出其他不匹配的记录。
常用的是内连接
外连接又分为：
左连接:包含左边表中的记录（右边表中没有的也包含）
例子:select ename,deptname from emp left join dept on emp.deptno=dept.deptno; (emp是左表)会将emp中的所有项列出，就算dept中没有对应的，也会列出
右连接:包含右边表中的记录（左边表中没有的也包含）
例子:select ename,deptname from dept right join emp on emp.deptno=dept.deptno; (emp是右表)会将emp中的所有项列出，就算dept中没有对应的，也会列出

f、子查询(一个select的结果是另一个select的条件时使用)
子查询关键字 in、not in、 =、 !=、 exists、not exists
例如
select * from emp where deptno in (select depton from dept where depton=1);

g、记录联合
语法:select * from tablename1 union|union all select * from tablename2 union|union all … select * from tablenamen ;
例子:select deptno from emp union all select depton from dept;
union all 把所有结果输出
union 把结果去掉重复之后输出

三、DCL语句(用户权限管理语句)
添加权限grant(给权限的同时可以创建用户)
移除权限revoke

给用户添加select/insert权限
grant select,insert on testdb.* to ‘rick’@’localhost’ identified by ‘123’; 给用户 rick 添加数据库testdb 的查询和插入权限，密码是123
移除insert的权限
revoke insert on testdb.* from ‘rick’@’localhost’ ; 移除用户rick在数据库testdb中的insert权限

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql创建本地登陆用户 rick 密码rick
GRANT ALL ON . TO rick@localhost IDENTIFIED BY ‘rick’;
(第一个是库，第二个是表)

创建一个能通过 wiley.com登陆的用户
GRANT ALL ON . TO rick@’%.wiley.com’ IDENTIFIED BY ‘root’;

创建ip都能登陆的用户
GRANT ALL ON . TO rick@’192.168.0.0/255.255.255.0’ identified by ‘root’;
GRANT权限(select/insert之类的) ON 库名.表名 TO 用户名@允许的ip或者用户名 identified by ‘密码’;
例如：GRANT SELECT, INSERT, UPDATE, DELETE ON GPAnalytics.* TO gpanalytics@’192.168.2.244’ identified by ‘gpanalytics123!’;

GRANT ALL ON .创建的用户权限很大，慎用

flush privileges; 添加用户之后，刷新权限表

//给某个用户添加某个库的权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON TaskList.* TO ‘gpbbs’@’localhost’;
四、
1、删除数据库test1下所有前缀为 tmp的表
select concat(‘drop table test.’, table_name, ‘;’) from tables where table_schema=’test1’ and table_name like ‘tmp%’;

2、将数据库test1 下所有存储引擎为myisam 的表改为innodb。
alter table table_name engine=innodb;

五、将txt文件导入数据库
use database;
load data local infile ‘/tmp/user_base.txt’ into table user_base;
批量将txt文件导入数据库
ls |awk -F ‘.’ ‘{print $1}’| xargs -i -t mysql -uroot -p0ad1b3ab9e6c164b -D GPBbs -e “load data local infile ‘/tmp/2016-02-19_GPBbs/{}.txt’ into table {}”

六、REPLACE INTO
REPLACE INTO table_name(col_name, …) VALUES (…);
REPLACE INTO table_name (col_name, …) SELECT …;
REPLACE INTO table_name SET col_name=’value’,
…算法说明：
REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除，即：
尝试把新行插入到表中
当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：
从表中删除含有重复关键字值的冲突行
再次尝试把新行插入到表中
旧记录与新记录有相同的值的判断标准就是：
表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。
返回值：
REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和
受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。

第三章：mysql支持的数据类型
1、定点数类型：如果小数部分长度超出范围，直接截断如1.234如果小数后只能存两位，就直接截断为1.23
DEC(M,D)
DECIMAL(M,D)

2、浮点数类型：如果指点了小数位数，超出部分会按实际的保存下来。1.234如果小数后只能保存2位，实际会存1.234

3、char和varchar char会把前后的空格去掉后保存，varchar会把空格保存下来

第四章 mysql中的运算符
1、<=> NULL安全的等于
2、regexp或rlike 正则表达式匹配
3、XOR逻辑异或 比如 0 XOR 1则为真

第五章 字符串函数
1、concat(s1, s2, s3, …) 字符串拼接
2、rand() 返回0-1随机数
3、IF(value, t, f) 如果value为真，就返回t，否则返回f
4、IFNULL(value1, value2 ) 如果value1不为空，返回value1，否则返回value2
5、case when [value1] then [result1] … else [default] end 如果value1是真，返回result，否则赶回default
或者：case [expr] when [value1] then [result1] …else [default] end 如果expr等于value1，返回result，否则返回default

第七章 表类型
1、查看数据库支持的存储引擎
show engines；
show variables like ‘have%’; //DISABLED的记录表示支持但在数据库启动的时候被禁止了。

3、各常用存储引擎比较

4、myisam介绍
myisam是mysql默认的存储引擎;
myisam不支持事务、不支持外键，其优势是访问速度快;
数据文件和索引文件可以放置在不同的目录，平均分配IO，获得更快的速度(创建表的时候用DATA DIRECTORY和INDEX DIRECTORY指定目录);
可以使用check table语句来检查myisam表的健康;
可以用repair table语句修复一个损坏的myisam表;
myisam表支持3中不同的存储格式：
静态(固定长度)表：默认，各个字段为非变长字段，存储非常迅速、容易缓存、出现故障容易恢复，保存的内容后面的空格会被去掉。
动态表：占用空间比较少，频繁地更新和删除会产生碎片，故障恢复比较困难。
压缩表：占用非常小的磁盘空间、访问开支非常小。

5、innodb
自动增长列：innodb的自动增长列可以手动插入，但如果插入的是0或者空，就为自动增长；自动增长值保存在内存中，重启数据库会丢失；
外键约束：创建外键的时候，夫表必须有对应的索引，子表在创建外键的时候也自动创建对应的索引。
存储方式：
使用共享表空间存储，表结构保存于.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件；
使用多表空间存储，这种方式创建的表的结构保存在.frm中，但每个表的数据和索引单独保存在.ibd中，如果是分区表，则每个分区表对应单独的.ibd文件。多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的大小限制和扩张大小等参数。

第30章 mysql常见问题和应用技巧
1.忘记mysql root密码
a. kill掉mysql进程 kill -9 mysql的pid
b.用–skip-grant-tables选项来重启mysql服务（–skip-grant-tables是跳过权限表认证的意思）
mysqld_safe –skip-grant-tables –user=mysql
c.用空密码的root用户连接mysql，并修改密码
mysql -uroot #登陆
update user set password=password(‘123’) where user=’root’ and host=’localhost’; #修改密码
flush privileges #刷新权限

2.如何处理myisam存储引擎的表损坏
a. mysql表损坏的表现：
tbl_name.frm 被锁定不能更改
不能找到文件 tbl_name.MYI（Errcode：nnn）
文件意外结束
记录文件被损坏
从表处理器得到错误 nnn

b. 处理方法1
myisamchk -r tablename
-r参数的含义是recover，这个命令几乎可以解决所有问题，如果不行就使用
myisamchk -o tablename
-o 参数的含义是–safe-recover，可以进行更安全的修复

c. 处理方法2
使用CHECK TABLE(检查表是否损坏) 和 REPAIR TABLE(修复顺坏的表)命令一起进行修复
CHECK TABLE tbl_name1 [, tal_name2]… [option]…
option = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
REPAIR [LOCAL|NO_WRITE_TI_BINLOG] TABLE
tbl_name [,tbl_name]… [QUICK][EXTENDED][USE_FRM]

3.数据目录磁盘空间不足的问题(也就是将数据和索引分开)
a.myisam表的处理方法
create table t1( id int, name varchar(11) ) data directory=’目录路径’,index directory=’目录路径’;
这种方法的实际处理方式是在操作系统上创建软连接。

b.innodb表的处理方法
innodb的表，其数据和索引是在同个文件的，索引不能分离了，但可以增加一个新的数据文件。
innodb_data_file_path = /home/ibdata1:2000M; /home1/ibdata2:2000M:autoextend;
需要重启后才会生效。

4.mysql.sock丢失后连接数据库
使用localhost作为主机名来链接数据库时，默认使用unix套接字来链接，我们可以使用–protocol=TCP|SOCKET|PIPE|MEMORY来指定连接方式
mysql –protocal=TCP -uroot -p -hlocalhost