mysql庫的操作

show databases//查看所有數(shù)據(jù)庫
information_schema
mysql
performance_schema//這三個(gè)庫不能隨便刪
create database mydbl(數(shù)據(jù)庫的名)//創(chuàng)建數(shù)據(jù)庫
show create database mydb1//查看mydb1的創(chuàng)建方式
//創(chuàng)建mydb2庫丈牢，指定字符集為gbk;   兩種寫法
create database mydb2 character set gbk;
create database mydb2 charset gbk;
//修改mydb1的字符集為utf8
alter database mydb1 character set utf8;
//要求對輸入數(shù)據(jù)進(jìn)行檢查，要求為utf8格式
create database mydb3 character set utf8 collate utf8_general_ci; 
//刪除數(shù)據(jù)庫mydb2
drop database mydb2

mysql表的操作

//創(chuàng)建表
//建表需要先選庫  //兩種寫法
use mydb1;
create table tl(id int ,name varchar(30));
create table mydb1.tl(id int ,name varchar(30));
//增加一個(gè)字段
alter table tl add sal int;
也可以寫成 alter table tl add column sal int;
//顯示表
desc tl;
修改字段屬性
alter table tl modify column name varchar(40);//column可加可不加
更改字段名字同時(shí)改類型
alter table tl change comm comm2 double;//將comm改為comm2  并且類型改了
//刪除表字段
alter table tl drop column comm2;
//查看表是怎么創(chuàng)建的  創(chuàng)建方式
show create table tl;
顯示
CREATE TABLE `tl` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(40) DEFAULT NULL,
  `sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
engine= InonoDB是默認(rèn)引擎，高速模式，支持事務(wù)，默認(rèn)情況下不開啟阻桅。
表名大小寫敏感 字段不區(qū)分大小寫 庫名大小寫敏感
//重命名表  需要加table 關(guān)鍵字
rename table tl to t2;
//刪除表  mysql 不支持回收站，直接刪除，不能加purge
drop table t2;

mysql數(shù)據(jù)的操作

先創(chuàng)建一個(gè)表 然后往里面插數(shù)據(jù)
create table employee(id int ,name varchar(20),sex int,birthday date,salary double ,entry_date date,resume text);
插數(shù)據(jù)兩種方式
insert into employee values(1,"葉井",1,'1983-04-27',15000,'2012-06-24','一個(gè)大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,"傅紅雷",1,'1984-02-22',10000,'2012-07-24','一個(gè)中牛');
查詢表中的數(shù)據(jù)
select * from employee;
修改表中的數(shù)據(jù)
update employee set resume='一個(gè)小牛'where id=3;
刪除表中的數(shù)據(jù)
delete from employee where name ='陸小佳';

mysql組函數(shù)相關(guān)

練習(xí)題
create table student(id int ,name varchar(20),chinese int ,english int ,math int);
-- insert into student(id,name,chinese,english,math) values(1,'黃真',80,85,90);
-- insert into student(id,name,chinese,english,math) values(2,'歸辛樹',90,95,95);
-- insert into student(id,name,chinese,english,math) values(3,'李尋歡',80,96,96);
-- insert into student(id,name,chinese,english,math) values(4,'葉芽',81,97,85);
-- insert into student(id,name,chinese,english,math) values(5,'袁承志',85,84,90);
-- insert into student(id,name,chinese,english,math) values(6,'何紅藥',92,85,87);
-- insert into student(id,name,chinese,english,math) values(7,'何鐵手',75,81,80);
-- insert into student(id,name,chinese,english,math) values(8,'夏雪宜',77,80,79);
-- insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);
-- insert into student(id,name,chinese,english,math) values(10,'岳不群',94,85,84);
-- alter table student add class_id int;
--修改表中的數(shù)據(jù) 
--比如把id號小于5的class_id 改成1
update  student set class_id=1 where id<=5;
或者update student set class_id=ceil(id/5);//ceil取整函數(shù);
--求各班平均成績
select avg(english),class_id from student group by class_id; //avg求平均灭将，group by 以什么為分組
--求各班總成績
select sum(chinese+math+english),class_id from student group by class_id;
--求總成績大于1300的班級
select sum(chinese+math+english),class_id from student group by class_id having sum(chinese+math+english)>1300;

mysql日期函數(shù)_數(shù)字函數(shù)_字符函數(shù)
select 3+5*20 from dual;//dual   確實(shí)是一張表.是一張只有一個(gè)字段,一行記錄的表. 
                                 2.習(xí)慣上,我們稱之為'偽表'.因?yàn)樗淮鎯χ黝}數(shù)據(jù).
                                 3. 他的存在,是為了操作上的方便.因?yàn)閟elect 都是要有特定對象的.
--查看當(dāng)前時(shí)間
select now() from dual;
函數(shù)原型 DATE_ADD(date2,INTERVAL d_value d_type)在date2中加上日期或時(shí)間
         DATE_SUB(date2,INTERVAL d_value d_type)在date2上減去一個(gè)時(shí)間
昨天 今天 明天
select date_add(now(),interval -1 day),now(),date_add(now(),interval 1 day)from dual;
ADDTIME(date2,time_interval)  將time_interval加到date2
加一分鐘
select addtime(now(),'0:1:0'),now()from dual;
字符串相關(guān)函數(shù)
select concat('hello','mysql','haha','hehe')from dual;//concat將字符串連接起來
utf字符串，漢子占3個(gè)字節(jié)
select length('hello中國') from dual;
數(shù)字相關(guān)函數(shù)
CONV(number 2,from_base,to_base);進(jìn)制轉(zhuǎn)換
select conv(10,10,2),conv(10,10,16)from dual;

mysql轉(zhuǎn)換函數(shù)

在mysql中沒有to_date函數(shù)后控，進(jìn)行日期轉(zhuǎn)換需要使用date_format()來代替庙曙。
select  date_format('1982-11-17','%Y-%m-%d')from dual;//%Y大寫的Y是四位1982 %y小寫的是兩位82  %m是兩位比如07 %c是一位比如7
字符串轉(zhuǎn)日期
select str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;
select addtime(str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s'),'0:1:0') from dual;

mysql多表查詢

練習(xí)題
create database if not exists scott character set utf8;//if not exists防止出錯(cuò)的 如果存在就不創(chuàng)建不存在就創(chuàng)建
use scott;
create table bonus//創(chuàng)建bonus表
(
  ename VARCHAR(10),
  job   VARCHAR(9),
  sal   int,
  comm  int
);
create table dept//創(chuàng)建dept表
(
  deptno int not null,
  dname  VARCHAR(14),
  loc    VARCHAR(13)
);
alter table dept add constraint PK_DEPT primary key (deptno);修改表時(shí)添加主鍵  ALTER TABLE <數(shù)據(jù)表名> ADD PRIMARY KEY(<列名>);如果不指定CONSTRAINT symbol，MYSQL會自動生成一個(gè)名字
create table emp//創(chuàng)建emp表
(
  empno    int not null,
  ename    VARCHAR(10),
  job      VARCHAR(9),
  mgr      int,
  hiredate DATE,
  sal      int,
  comm     int,
  deptno   int
);
alter table emp add constraint PK_EMP primary key (empno);//添加主鍵
alter table emp add constraint FK_DEPTNO foreign key (deptno)  references dept (deptno);//添加外鍵
create table salgrade//創(chuàng)建salgrade表
(
  grade int,
  losal int,
  hisal int
);
insert into dept (deptno, dname, loc)//往dept表中插入數(shù)據(jù)
values (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)//往emp表中插入數(shù)據(jù)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into salgrade (grade, losal, hisal)//往salgrade表中插入數(shù)據(jù)
values (1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
顯示:員工號浩淘，員工姓名捌朴，部門編號，部門名稱
select e.empno,e.ename,e.deptno,d.dname from dept d,emp e where e.deptno=d.deptno;//多表查詢
select e.empno,e.ename,e.deptno,d.dname from dept d inner join emp e on e.deptno=d.deptno;//sql99的標(biāo)準(zhǔn) 內(nèi)連接寫法
顯示：員工號张抄，姓名砂蔽，薪水，薪水級別
select e.empno,e.ename,e.sal,s.grade 
  from emp e,salgrade s 
  where e.sal>=s.losal
  and e.sal<=s.hisal;
顯示：部門編號署惯，部門名稱左驾，人數(shù)
select d.deptno,d.dname,count(e.empno)
  from emp e,dept d
  where e.deptno=d.deptno
  group by d.deptno,d.dname;//上述操作仍然缺少40部門 因?yàn)榻y(tǒng)計(jì)的人數(shù)沒有
 //sql99不支持（+）連接寫法
select d.deptno,d.dname,count(e.empno)//這么寫全 外連接
  from emp e right outer join dept d//這個(gè)寫right是看dept d在逗號右面//from dept d left outer join emp e
  on e.deptno=d.deptno
  group by d.deptno,d.dname;
//內(nèi)連接  外連接 inner outer可以省略
顯示xx'boss is xx
select concat(e.ename,'''s boss is',b.ename)//自連接
  from emp e,emp b
  where e.mqr=b.empno//上述結(jié)果會缺少最大的老板
select concat(e.ename,'''s boss is',b.ename)
  from emp e left join emp b
  on e.mqr=b.empno;//改完之后大老板那行顯示NULL;
select concat(e.ename,'''s boss is',ifnull(b.ename,'his wife'))//ifnull 如果大老板的老板為空顯示hiswife
  from emp e left join emp b
  on e.mqr=b.empno; 
select * from emp order by sal ;按工資排序正著排 從小往大
select * from emp order by sal desc;倒序排  從大往小
select * from emp order by sal desc limit 3;//取工資前三名
select * from emp order by sal desc limit m,n;//m代表跳過的記錄數(shù)，n代表取的記錄數(shù)
select * from emp order by sal desc limit 4,4;//從第4個(gè)下一個(gè)也就是跳到第五個(gè) 然后再數(shù)四個(gè)  5到8名的工資