mysql使用（二）---常用命令

create table student(id int PRIMARY key auto_increment,name varchar(20),classid int);
create table class(id int PRIMARY key auto_increment,name varchar(20));
insert into student (name,classid) VALUES('aaa',1),('bbb',1),('ccc',2);
insert into class(name) VALUES('one class'),('two class');

使用上面的数据

select * from student,class;
select * from student join class;

这两种方式得到的都是笛卡尔积的结果。

alter添加约束：alter table *** add constraint *** 用法_小封子_新浪博客

alter table student add CONSTRAINT student_class FOREIGN key student(classid) REFERENCES class(id);

添加了约束使用

select * from student join class

也只会得到笛卡尔积，必须使用on来指定关联字段。

join默认是inner join。

不存在outer join以及full outer join，可以使用的是left outer join，right outer join。

full outer join 可以使用union来实现：

select * from student s left join class c on s.classid = c.id
UNION
select * from student s right join class c on s.classid = c.id;

union要求查询到的两个结果集列数相同且对应的列的类型相容，对于varchar(20)与varchar(50)是可以union的。

在含有join的查询语句中，on与where的区别：

on后面为关联条件，而where后面为筛选条件。即使用left join时，即使关联不到其他关联表的数据，也会将左侧表的数据全部显示出来，若不满足on后面的条件会使左侧表的该条数据无法关联到其他表的数据，但是仍然会显示，但是若不满足where后的条件会使该条数据无法显示，可以参考：MySQL关联left join 条件on与where不同 - 51CTO.COM

不小心将主键的自增长属性删了以后，通过modify来添加自增长可能会报重复主键1的异常，此时为了能正常添加，需要先将主键为1的主键值置为其他值，然后执行modify，尽量不要通过modify来更改主键属性，其实字段属性一般是不应变动的。

使用建表语句如下：

create table timeabout2(id int primary key auto_increment,mdate date,mtime time,mdatetime datetime,mtimestamp timestamp,myear year);

再使用show create table查看结果为：

CREATE TABLE `timeabout2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mdate` date DEFAULT NULL,
  `mtime` time DEFAULT NULL,
  `mdatetime` datetime DEFAULT NULL,
  `mtimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `myear` year(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可见timestamp类型默认了自动插入当前值，自动更新当前值，使用时需要注意。

-- create database basic;
-- grant insert,delete,update,select on basic.* to tornado IDENTIFIED by 'tornado123';
-- use mysql;
-- 通过insert添加用户的方法会报错，早期版本不会报错，添加用户使用grant命令 
-- INSERT INTO user (host,user,password,select_priv, insert_priv, update_priv)VALUES ('localhost','guest',PASSWORD('guest123'),'Y','Y','Y');
use basic;
-- create table simple(id int primary key auto_increment,name varchar(50));
-- insert into simple(name) values('aaa'),('bbb');
-- show index from simple;		-- 查看存在的索引
-- show table status from basic like 'sim%';
-- select * from student where name REGEXP 'a+';	-- mysql查询中使用的正则表达式
-- -- -- -时间相关类型- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create table timeabout(id int primary key auto_increment,mdate date,mtime time,mdatetime datetime,mtimestamp timestamp,myear year);
-- insert into timeabout(mtime) values('12:36:14');
-- update timeabout set myear=2033 where id=6;
-- show create table timeabout;
 
-- insert into timeabout(mtime,mdate,mdatetime,mtimestamp,myear) values('12:36:14','2016-12-13','2016-12-13 12:36:14',20161213123614,2016);	-- 有中文字符竟然提示列数与值数不相等
-- desc timeabout;
-- -- -笛卡尔积查询- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create table temp(id integer(11));
-- create table temp2(name varchar(20));
-- insert into temp values(1),(2),(3);
-- insert into temp2 values('aaa'),('bbb'),('ccc');
-- select * from temp,temp2;		-- 笛卡尔积
-- -- -- -JOIN的用法- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create table student(id int PRIMARY key auto_increment,name varchar(20),classid int);
-- create table class(id int PRIMARY key auto_increment,name varchar(20));
-- insert into student (name,classid) VALUES('aaa',1),('bbb',1),('ccc',2);
-- insert into class(name) VALUES('one class'),('two class');
-- select * from student,class;
-- select * from student join class;
-- alter table student add CONSTRAINT student_class FOREIGN key student(classid) REFERENCES class(id);
-- select * from student s INNER join class c on s.classid = c.id;
-- select * from student s left join class c on s.classid = c.id	-- 用union实现full outer join
-- UNION
-- select * from student s right join class c on s.classid = c.id;
-- -- -- -UNION的使用- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create table union1(name varchar(20));
-- create table union2(name varchar(50));
-- insert into union1 values('aaa');
-- insert into union2 values('bbb');
-- select * from union1
-- UNION 
-- select * from union2;
 
-- -- -- -NULL的判断- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- select * from student where classid <=> null;
-- select * from student where classid is null;
 
 
-- -- -事务相关 - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- BEGIN;
-- insert into student(name,classid) values('ddd',1);
-- insert into student(name,classid) values('eee',2);
-- insert into student(name,classid) values('fff',2);
-- insert into student(name,classid) values('ggg',4);
-- COMMIT;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- CREATE TABLE `products` (  
-- `pid` int(3) NOT NULL auto_increment,  
-- `pname` varchar(20) NOT NULL,  
-- `pcode` varchar(20) NOT NULL,  
-- PRIMARY KEY (`pid`)  
-- );
-- INSERT INTO `products` (`pid`, `pname`, `pcode`) VALUES (1, '商品1', 'AC90'),  
-- (2, '商品2', 'DE78'),  
-- (3, '商品3', 'XXXX');
-- CREATE TABLE `sales_detail` (  
-- `aid` int(3) NOT NULL auto_increment,  
-- `pcode` varchar(20) NOT NULL,  
-- `saletime` date NOT NULL,  
-- PRIMARY KEY (`aid`)  
-- );
-- INSERT INTO `sales_detail` (`aid`, `pcode`, `saletime`) VALUES 
-- (1, 'AC90', '2008-09-22'),  
-- (2, 'DE78', '2008-09-22'),  
-- (3, 'AC90', '2008-09-23'),  
-- (4, 'AC90', '2008-09-24');
-- 在join中on与where的区别
-- select * from sales_detail s left JOIN products p on s.pcode=p.pcode and s.saletime in('2008-09-23','2008-09-24');
-- select * from sales_detail s left JOIN products p on s.pcode=p.pcode where s.saletime in('2008-09-23','2008-09-24');
-- select * from sales_detail s LEFT JOIN products p on s.pcode=p.pcode;
-- -- -使用alter来添加或删除列- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -还可以使用alter添加约束- =- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- alter table student add column score int after classid;	-- 添加字段，并指定添加位置
-- alter table student drop column score;		-- 删除字段
-- alter table student add column score int first;	-- 添加字段，并指定新添加字段位于第一个位置
-- alter table student modify id int(11) auto_increment;	-- 修改字段类型1,ps.主键类型尽量不要修改
-- alter table student modify name varchar(255) not null default '';	-- 修改字段类型1
-- alter table student change sname `name` varchar(50);		-- 修改字段名
-- alter table student change name `name` varchar(255);		-- 修改字段类型2
-- delete from student where 1=1;
-- insert into student (classid)values(1);
 
-- -- -临时表,退出客户端自动删除- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create temporary table tempo(name varchar(20));
-- insert into tempo values('aaa');
-- select * from tempo;
-- -- -复制表的方法- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -除此之外还有mysqldump命令来导出数据库中的表，然后执行来复制的方法- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- show create table student;
-- CREATE TABLE `student_clone` (
--   `id` int(11) NOT NULL AUTO_INCREMENT,
--   `name` varchar(255) NOT NULL DEFAULT '',
--   `classid` int(11) DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `student_class` (`classid`),
--   CONSTRAINT `student_class_2` FOREIGN KEY (`classid`) REFERENCES `class` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8;		-- 复制表结构，需要修改表名和外键名
-- insert into student_clone (name,classid) select t.name,t.classid from student t;		-- 复制数据
-- -- -- -获取服务器的元数据- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- select version();		-- mysql版本
-- SELECT DATABASE();		-- 当前数据库
-- select USER();				-- 当前用户
-- show STATUS;					-- 当前状态
-- show VARIABLES;			-- 所有的变量
-- SELECT LAST_INSERT_ID();	-- 上一次插入数据，自动生成的id值
-- create table seq(id int PRIMARY KEY auto_increment,name varchar(20)); -- [Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- alter table seq auto_increment=33;			-- 直接通过id int PRIMARY KEY auto_increment=33指定会报错。
-- -- -- -insert的其他使用方式- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- insert into seq(name) values('ddd');
-- insert IGNORE into seq(id,name) values(33,'cccc');		-- 若出现重复数据，就忽略本条数据的插入
-- REPLACE into seq(id,name) values(33,'cccc');		-- 若出现重复数据，用本条数据进行替换
-- -- -- -唯一性约束- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- create table users(var1 varchar(20),var2 varchar(20),UNIQUE(var1,var2));	-- 这种形式定义的单一性约束，只要不同时相同就能够插入
-- insert into users(var1,var2)values('a','a');
-- insert into users(var1,var2)values('a','b');
-- create table users2(var1 varchar(20),var2 varchar(20),UNIQUE(var1),UNIQUE(var2));	-- 这种形式定义的单一性约束，只要不同时相同就能够插入
-- insert into users2(var1,var2)values('a','a');
-- insert into users2(var1,var2)values('a','b');		-- 这种是单独检查唯一性约束
-- 写的顺序：select ... from... where.... group by... having... order by..
-- 执行顺序：from... where...group by... having.... select ... order by...
-- create table student2 select s.id,s.name,s.classid from student s;	-- 这种复制表数据的方式的到的表结构不完整，详见下面的信息。
-- show create table student2;
-- CREATE TABLE `student2` (
--   `id` int(11) NOT NULL DEFAULT '0',
--   `name` varchar(255) NOT NULL DEFAULT '',
--   `classid` int(11) DEFAULT NULL
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- show create table student;
-- CREATE TABLE `student` (
--   `id` int(11) NOT NULL AUTO_INCREMENT,
--   `name` varchar(255) NOT NULL DEFAULT '',
--   `classid` int(11) DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `student_class` (`classid`),
--   CONSTRAINT `student_class` FOREIGN KEY (`classid`) REFERENCES `class` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=1005 DEFAULT CHARSET=utf8
-- -- -导出表数据到文件，得到的数据用notepad的格式可能不方便看，用ultraedit，notepad++，editplus打开- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -- -参考网址：http://hunan.iteye.com/blog/752606- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- select * from student into OUTFILE "E:/student.txt";	-- 若已存在该文件，会报错
-- select * from student into OUTFILE "E:/student2.csv";
-- select * from student into OUTFILE "E:/student2.csv" FIELDS TERMINATED by ',' ENCLOSED by '"' LINES TERMINATED by '\r\n'; -- 这样生成的文件内容更加直观
-- load data INFILE "E:/student.csv" into table student (id,name,classid);
-- load data INFILE "E:/student2.csv" replace into table student FIELDS TERMINATED by ',' ENCLOSED by '"' LINES TERMINATED by '\r\n' (id,name,classid);	-- 若使用分隔符导出，也要使用同样的分隔符导入
 
 
-- mysqldump -uroot -p  basic student>student.sql		-- 将建表语句，插入数据语句输出到一个sql文件中，加上--tab='e:/tmp'选项会输出两个文件，一个建表的sql文件，一个txt的通过outfile输出的数据文件，通过制定--no-data与--no-create-info可以指定只输出建表或数据文件。
-- mysqldump -u root -p database_name | mysql -h other-host.com database_name -- 通过管道命令将本机数据库导入到与本机相连的其他主机的数据库中，未测试
-- mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。
-- 对于将建表语句与插入语句在同一个sql文件中的类型可以登录mysql，切换到要导入的数据库，然后使用source命令导入
 
 
-- -- -日期函数- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- select DATE_ADD(mdate,INTERVAL 45 WEEK) from timeabout where id=5; -- https://www.w3cschool.cn/mysql/func-date-add.html
-- select  
-- DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'),
-- DATE_FORMAT(NOW(),'%m-%d-%Y'),
-- DATE_FORMAT(NOW(),'%d %b %y'),
-- DATE_FORMAT(NOW(),'%d %b %Y %T:%f');	-- 日期格式化
-- SELECT NOW(),CURDATE(),CURTIME();
-- select EXTRACT(YEAR FROM mtimestamp) as year from timeabout where id=6;
-- select concat('11','22','33');
-- select concat('11','22',null); -- 所要连接的字段有一个为空，则连接后的结果为空
-- select concat_ws('+','11','22','33'); -- concat with separator
-- group_concat  -- group_concat函数很有用 -- https://www.w3cschool.cn/mysql/5nab1ptx.html
-- select repeat('ab',2);
-- select left('abcdefg',2),right('abcdefg',2),SUBSTR('abcdefg',2,2),substring_index("www.w3cschool.cn",".",2),substring_index("www.w3cschool.cn",".",-2);-- substring_index为关键字截取
-- 数学函数：https://www.w3cschool.cn/mysql/b7g51pun.html
 
-- -- -mysql中的if-else，存储过程- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- SELECT if(id>500,'大于500','小于500'),id as des from student;	-- if作为三元表达式使用
-- SELECT case id when 1 then '等于1' else '不等于1' end,id from student; -- case用于多可能判断
-- SELECT IFNULL(classid,'is null'),id from student;	-- 假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2
 
-- drop PROCEDURE if EXISTS stupro;
-- 
-- create PROCEDURE stupro(in sid int)
-- BEGIN
-- DECLARE sname varchar(20);
-- select name into sname from student where id=sid;
-- 
-- if sname is null THEN
-- 	select 'name is null' as name;
-- ELSEIF sname='' then		-- 注意此处是elseif而不是else if
-- 	select 'name is ' as name;
-- else 
-- 	select sname as name;
-- end if;
-- END
 
-- set @sid=2;
-- call stupro(@sid);
 
-- create procedure sum2(in top int,out sum int)
-- BEGIN
-- DECLARE i int;
-- DECLARE s int;	-- 不能使用DECLARE i int，s int的格式
-- set i=0;
-- set s=0;
-- while i <= top do		-- mysql有三种循环：while，repeat，loop
-- 	set s = s+i;
-- 	set i = i+1;
-- end while;
-- set sum = s;
-- END
-- set @top=100;
-- call sum2(@top,@s);
-- select @s;

-- 游标的使用
-- drop PROCEDURE if EXISTS stupro;
-- create PROCEDURE stupro()
-- BEGIN
-- DECLARE sname varchar(20);
-- DECLARE done int;
-- declare mycursor cursor for (select name from aaa);
-- declare mycursor2 cursor for (select name from aaa);
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 设置标志位
-- 	set done=0;
-- 	open mycursor;  -- 打开游标
-- 	fetch  next from mycursor into sname;
-- 	while done = 0 do
-- 		select sname;
-- 		fetch  next from mycursor into sname; -- 遍历游标
-- 	end while;
-- 	close mycursor;	-- 关闭游标
-- 
-- 	set done=0; -- 同一个存储过程中使用多个游标，需要重置标志位
-- 	open mycursor2;
-- 	fetch  next from mycursor2 into sname;
-- 	while done = 0 do
-- 		select sname;
-- 		fetch  next from mycursor2 into sname; 
-- 	end while;
-- 	close mycursor2;	-- 关闭游标
-- 
-- END
-- 
-- call stupro();


 
-- -- -- -测试索引的作用- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- drop PROCEDURE if EXISTS multiInsert;
-- create procedure multiInsert(in nam varchar(20), in count int)
-- BEGIN
-- DECLARE i int;
-- set i=0;
-- IF count <=0 THEN
-- 	SELECT 'count必须大于0' as outmsg;
-- ELSE
-- 	create table tab(id int PRIMARY KEY auto_increment,name varchar(20),score int default 0);
-- 	WHILE i<count DO
-- 		insert into tab(name,score)values(CONCAT(nam,i),i);
-- 		set i = i+1;
-- 	END WHILE;
-- END IF;
-- END;
 
-- set @nam='hurricane';
-- set @count = 10000;
-- call  multiInsert(@nam,@count);
 
-- SELECT count(*) from tab;
-- select * from tab where name='hurricane5098';	-- 不加索引前大致需要0.005秒，添加了索引需要0.001秒
-- alter table tab add index nameindex (name);
-- 
-- mysqladmin -uroot -p -h192.168.0.1 processlist -- 通过state可以查看对应连接状态
-- mysqladmin -uroot -p -h192.168.0.1 status -- Thread数量即为当前连接数
-- 登录到mysql后可以通过
-- show processlist	-- 显示前100条数据
-- show full processlist	-- 显示全部数据
-- show variables like '%max_connection%'; -- 查看当前允许的最大连接数
-- 修改my.ini中max_connections=xxx可以修改默认连接数
 
 
-- 给表及列添加注释 
-- drop table if exists aaa;
-- create table aaa(id int comment "this is id comlumn")comment "this is table aaa" engine=myisam charset=utf8;
-- alter table aaa modify id int comment 'this is id comment modified';
-- alter table aaa comment 'this is table aaa comment modified';
-- show create table aaa;
-- desc information_schema.tables;
-- 查看表注释 
-- select t.table_name,t.table_comment from information_schema.tables t where t.table_schema='BASIC' and table_name='aaa';
-- 查看列注释
-- show full columns from aaa;
 
 
 
 
 
 
 
 
 

关于事务：

不考虑事务会引发的问题：1.脏读；2.不可重复读；3. 虚读（幻读）。

mysql的事务隔离级别有：1.Serializable；2.Repeatable read；3.Read committed；4.Read uncommitted。

查看当前隔离级别：select @@tx_isolation.

设置事务隔离级别：set transaction isolation level 隔离级别.

mysql默认隔离级别为Repeatable read，效果为在一个事务中，前后读到的数据一致（可以读到其他事务提交的数据，只要在这个事务开始之后没读过，但是读过之后，其他事务提交的数据就不会再被读到）.

数据库建表的三大范式：数据库建表规则（三大范式）_hins-CSDN博客_数据库建表三大规范

1.确保每列保持原子性。

2.确保每列都与主键相关。

3.确保每列都和主键直接相关而不是间接相关。

参考网址：MySQL 教程_w3cschool

---