mysql一些入门

 mysql 执行大量insert语句性能分析
http://blog.csdn.net/huangjing_whlg/article/details/39933131
-----------------------------------------------------------------------------------------------------
mysql insert 忽略主键冲突
http://www.cppblog.com/amazon/archive/2011/11/02/159489.html
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
初涉mysql
启动，登录，退出，修改提示符等简单的数据库操作
---------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
net start mysql
net stop myssql
mysql -V(version)
mysql -uroot -pxiaoruirui -P3306 -h127.0.0.1 -Dtest;
--delimiter 指定分隔符
exit；
quit；
q；
--prompt 设置提示符
mysql -uroot -pxiaoruirui --prompt root>;
进入修改：
prompt \h;(服务器名称)
prompt \u@\h@\d>;(用户名@服务器名称@数据库名称)
创建数据库：
create {database | schemas} [if not exists] db_name [default] character set [=] charset_name;
create database tb1;
show {databases | schemas} [like 'pattern'| where expr]
create database if not exists t1;
查看警告信息：
show warnings;
查看数据库创建信息：
show create database t1;
设置数据库编码：
create database if not exists t2 character set gdk;
修改数据库：
alter {database|schema} [db_name] [default] character set[=] charset_nam;
alter database t2 character set = utf8;
删除数据库：
drop {database|schema} [if exists] db_name
show databases;
-----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
数据类型与操作数据表
-----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
数据类型：http://www.runoob.com/mysql/mysql-data-types.html
打开使用数据库：
use db_name;
use test;
select database();
创建表：
create table [if not exists] table_name(column_name data_type,...);
create table tb1(
user_name varchar(20),
age tinyint unsigned,
salary float(8,2) unsigned
);
查看表：
show tables [from db_name] [like 'pattern'|where expr]
show tables from mysql ;
show tables;
查看数据表的结构：
show columns from tal_name;
show columns from tb1; 
插入记录：
inser [into] tbl_name [(col_name,...)] value(val,...)
insert tb1 value('Tom',25,43534.23);
insert tb1(username,salary) value('John',4500.67);
记录查询：
select expr,... from tbl_name;
select * from tb1;
空值和非空：
create table tb2(
username varchar(20)not null,
age tinyint unsigned null
);
show columns from tb2;
insert tb2 value('Tom',NULL);
自动编码：auto_increment(如果插入错误，则id会增加一次)
主键约束：
create table tb3(
id smallint unsigned auto_increment primary key,
username varchar(30) not null
);
create table tb4(
id smallint unsigned primary key,
username varchar(30)not null
);
自增的一定是主键，主键可以不自增。
唯一约束：
create table tb5(
id smallint unsigned auto_increment primary key ,
username varchar(30)not null
);
主键约束只有一个，唯一可以有多个。
默认值：default
create table tb6 (
id smallint unsigned auto_increment primary key,
username varchar(20) not null,
age enum('1','2','3') default '3'
); 
总结：
数据类型：字符型、整型、浮点型、日期时间型
数据表操作：插入记录、查找记录
记录操作：创建数据表、约束使用
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
约束以及修改数据库
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
外键约束：
修改数据库默认存储引擎：default-storage-engine=innodb


create table provices(
id samllint unsigned primary key auto_increment,
pname varchar(20) not null
);
create table users(
id smallint unsigned primary key auto_increment,
username varchar(10) not null,
pid smallint unsigned,
foreign key (pid) references provinces (id)
);
show indexes from provices\G;\\查看索引


create table users1(
id smallint unsigned primary key auto_increment,
username varchar(10) not null,
pid smallint unsigned,
foreign key (pid) references provinces (id) on delete cascade
);
修改数据表：
增加单列：
alter table tbl_name add [column] col_name column_definition [first|afger col_name]
alter table users1 add age tinyint unsigned not null default 10;
alter table users1 add password varchar(32) not null after username;
alter table users1 add truename varchar(20) not null first;
删除列：
alter table tbl_name drop col_name1,drop col_name2;
alter table users1 drop password,drop age;


添加主键约束：
alter table tbl_name add [constraint[symbol]] primary key [index_type](index_col_name,...)
create table users2(
username varchar(20) not null);
alter table users2 add id smallint unsigned;
alter table users2 add constraint PK_users2_id primary key (id);
添加唯一约束：
alter table tbl_name add [constraint[symbol]] unique [index|key] [index_name] [index_type] (index_col_name,...)
alter table users2 add unique (username);
添加外键约束：
alter table tol_name add [constraint[symbol]] foreign key [index_name] (index_col_name) referende_definition
alter table users2 add pid smallint(5) unsigned default null;
alter table users2 add foreign key (pid) references provinces (id) ;
添加/删除默认约束：
alter table tbl_name alter[column] col_name {set default literal | drop default}
alter table users2 add age tinyint unsigned not null;
alter table users2 alter age set default 15;
alter table users2 alter age drop default;


删除主键约束：
alter table tbl_name drop primary key
alter table users2 drop primary key;// 不加name，仅有一个主键
show columns table users2;
desc users2;
删除索引和唯一约束：
alter table tab_name drop {index|key} index_name
show index from users2\G;
alter table users2 drop index username;
show columns from users2;
show index from uers2;
删除外键：
alter table tbl_name drop foreign key fk_sysmbok(外键name)
show create table users2;
alter table users2 drop foreign key users2_ibfk_1;(系统指定外键名称，通过上述找到)


修改列定义：
alter table tbl_name modify [columns] col_name column_definition [first|after colname]
alter table users2 modify id smallint unsigned not null first;//改变位置为首位
alter table users2 modify id tinyint unsigned not null;//改变定义数据类型
修改字段名称和定义：
alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]
alter table users2 chang pid p_id tinyint unsigned not null;
修改数据表名：
alter table tbl_name rename [to|as] new_tab_name
alter table uers2 reanme to|as(或者省了) users3；
show tables;
rename table tbl_name to new_tab_name [tab_name1 to new_tbl_name1,tab_name2 to new_tbl_name2]
rename table users3 to users2;
show tables;
约束：
按功能划分：not null ,primary key,unique key, default,foreign key
按数据列的数目划分：表级约束，列级约束
修改数据表：
针对字段的操作：添加/删除字段、修改列定义，修改列名称等
针对约束的操作：添加/删除各种约束
针对数据表的操作：数据表更名（俩中方式）
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
操作数据表中的记录
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
insert：
create table users(
id smallint unsigned primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null,
age tinyint unsigned not null default 10,
sex boolean
);
insert users value(NULL,'Tom','123',25,1);
insert users value(default,'Tom','123',25,1);
insert users value(default,'Tom','123',default,1);
insert users value(defualt ,'Bolo','234',2+3,1);
insert users value(null,'Tom','123',default,1),(null,'A',md5('123'),234,1);
insert users set username='Bem',password = '456';


update:
update users set age = age+5;
update users set age = age-id,sex = 0;
update users set age = age+10 where id%2 =0;


delete:
delete from users where id = 5;


select:
select id,username from users;
select users.id ,users.username from users limit 3;
select id as userId,username as uname from users;// as可省略，下面的问题
select id username from users;//id的别名为username
where：


group by：
[group by{col_name|position}] [asc|desc,...]
select * from users group by password;
select * from users group by password having age<29;


order by:
select * from users order by username,id desc;


limit:
select * from users limit 3;// 返回3条
select * from users limit 3,2;//出现第4，5俩条记录




Insert:没有完成任务
insert test select username from users where age>30;
insert test(username) select username from users where age>30;
总结：
insert:insert ,insert set,insert select 
update:单标，多表
delete：单标，多表
select：where,group by,having ,order by,limit
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
子查询和连接
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
子查询.txt
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
运算符合函数
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
字符函数
-----------------------------------------------------------------------------------------------------
连接：concat()
select concat('mysql','+','php');
select concat(first_name,last_name) as fullname from tbl_info;
使用指定的分隔符进行连接：concat_ws()
select concat_ws('+','mysql','i','like');
数字格式化：format()返回字符
select format(125760.75,2);//2表示保留小数点位数
小写:lower()
select lower('Mysql');
大写:upper()
select upper('Mysql');
获取左值：left()
select left('mysql',3);
获取右值：right()
select right('mysql',3);
获取长度：length()
select length('mysql');
删除左空格：rtrim
select rtrim('  mysql  ');
select '  mysql  ';
select length(rtrim('  mysql  '));
删除右空格：rtrim
删除指定字符串（前面和后边，不能删除中间）trim()
select length(trim('  mysql  '));
select trim(leading '?' from '??msy??ql??');
select trim(trailing '?' from '??msy??ql??');
select trim(both '?' from '??msy??ql??');
字符串替换：replace()
select replace('??my??sql??','?','');
select replace('??my??sql??','?','  ');
字符串截取：substring()
select substring('mysql',1,2);//第一位为1
select substring('mysql',1);
select substring('mysql',-1);
select substring('mysql',-4,3);
select substring('mysql',-2,-3);// 不支持，（'',开始位置（-）表示倒叙截取，长度，不写从位置开始到结束）
类似，像：like
select 'mssql' like 'm%';//返回为1表示true
select * from test where first_name like '%o%';
%：表示任意多个字符
_:任意一个字符串
select * from test where first_name like '%%%'; // 通配符，全部为
select * from test where first_name like '%somastr%%' escape 'somastr';// 表示somestr后的字符串不需要解析
-----------------------------------------------------------------------------------------------------
数值运算符和函数
-----------------------------------------------------------------------------------------------------
向上取整：ceil()
select ceil(4.44);
向下取整：floor()
select floor(4.44);
整数除法：div()
select 3/4;
select 3 div 4;
取余数：mod()
select 5.3 mod 2;
select 5 mod 2;
幂运算：power()
select power(3,3);
四舍五入：round()
select round(4.4);
select round(4.5);
select round(4.9);
数字截取：truncate()
select truncate(123.456,2);
select truncate(123.456,1);
select truncate(123.456,0);
select truncate(123.456,-1);
比较运算符和函数：
[not] between ...and ...
select 33 not between 1 and 22;
select 33 between 1 and 22;
[not] in()
select 33 in(1,2,33,4);
is [not] null
select * from test where firstname is null;
日期时间函数：
select now();
select curdate();
select curtime();

select date_add('2014-3-12',interval 265 day);
select date_add('2014-3-12',interval -2 week);
select date_add('2014-3-12',interval 2 year);

select datediff('2014-2-23','2015-2-3');

select date_format('2014-3-3','%m/%d/%y');
信息函数：
select connection_id();
select database();
select last_insert_id();
select user();
select version();
聚合函数：
select avg(id) from test;
select round (avg(good_price),2) as agv_price from tdb_goods;
select count(goods_id) as counts from tdb_goods;
select min(goods_price) from tdb_goods;
select max(goods_price) from tdb_goods;
select sum(goods_price) from tdb_goods;
加密函数：
md5();
password();//修改客户端密码
select md5('admin');


总结：
内置函数库：
字符函数：
数值函数：
日期时间函数：
加密函数：

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
自定义函数udf(user defined function)
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
select date_format(now(),'%Y年%m月%d日  %H时：%i分：%s秒');


create function f1() returns varchar(30)
return date_format(now(),'%Y年%m月%d日  %H时：%i分：%s秒');

select f1();
drop function if exists f1;

create function f2(num1 smallint unsigned,num2 smallint unsigned ) 
returns float (10,2) unsigned
return (num1+num2)/2;
select f2(2,3);

delimiter //
create function adduser(username varchar(20))
returns int unsigned
begin
insert test(username) value (username);
return last_insert_id();
end
//
select adduser('Rose');//
delimiter ;
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
存储过程
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
create procedure sp1() select version();

call sp1();
call sp1();

delimiter //
create procedure removeUserById(in id int unsigned)
begin
delete from users where id = id;
end
//
delimiter ;
//上述会删除所有内容
drop procedure removeUserById;

delimiter //
create procedure removeUserById(in p_id int unsigned)
begin
delete from users where id = p_id;
end
//
delimiter ;

delimiter //
create procedure removeUserAndReturnUserNums(in p_id int unsigned,out userNums int unsigned)
begin
delete from users where id = p_id;
select count(id) from users into userNums;
end
//
delimiter ;
call removeUserAndReturnUserNums(12,@nums);

set @i = 4;
select @i;

select row_count();//获得插入表，删除表，更新表的记录数

delimiter //
create procedure removeUserByAgeAndReturnInfos(in p_age smallint unsigned,out deleteUsers smallint unsigned ,out userCouts smallint unsigned)
begin
delete from users where age = p_age;
select row_count() into deleteUsers;
select count(id) from users into userCouts;
end
//
delimiter ;

call removeUserByAgeAndReturnInfos(10,@deleteusers,@usercounts);
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
create table user2(
id smallint unsigned auto_increment primary key,
username varchar(30) not null
);
insert user2(username) value('孙悟空'),('牛魔王'),('蛟魔王'),('鹏魔王'）,('狮驼王');
---------------------------------------------------------------------------------------------------