Mysql数据库管理
  

1、基本信息的查看
 mysql> select version();
 mysql> select database();
 mysql> show variables like '%character%';
 mysql> set names utf8;
 mysql> show engines;
 mysql> show create table test;
 mysql> show create database sxjy;
 desc test;
2、创建数据库
  mysql> create database sxjy default character set utf8;
  mysql>set char set 'gpk';
  mysql> show databases;
  mysql> drop database sxjy;
 
3、创建数据表
  mysql> use sxjy;
  mysql> create table stu_info(id int,sno varchar(10),name varchar(30),sex enum('男','女'),age int,phone varchar(15),addr varchar(60));
  mysql> show tables;
  mysql> drop table stu_info;
 
  mysql> create table stu_info(id int primary key auto_increment,sno varchar(10) not null,name varchar(30) not null,sex enum('男','女'),age int,phone varchar(15),addr varchar(60))auto_increment=1;
 
 
4、表的自增设置
  mysql> show variables like 'auto_increment%';
  | auto_increment_increment | 1     |
  | auto_increment_offset    | 1     |
 
  auto_increment_increment 字段每次增加的值
  auto_increment_offset    自增字段的起始值

5、mysql的数据类型

（1）日期时间类型
date  3字节,格式:年-月-日,2015-07-15
time  3字节,格式:时:分:秒,21:30:20
datetime 8字节,格式:年-月-日 时:分:秒 2015-07-15 21:30:20
timestamp 4字节,自动存储记录修改时间
year  1字节,存储年份
     
例:创建一个数据表,包含上述字段类型
mysql> create table test(id int primary key,name varchar(30),birthday date,birthtime time,birthdt datetime,birthyear year,record timestamp);
     
(2)整数型
tinyint    1字节,范围(-128-->127)
smallint   2字节,范围(-32768-->32767)
mediumint  3字节,范围(-8388608-->8388607)
int 4字节,范围(-2147483648-->2147483647)
bigint     8字节,范围(+-9.22*10的18次方)
如果声明为unsigned类型,则数值为0-->2的n(字段长度)次方-1
     
(3)浮点型
float(n,m)  4字节,单精度浮点型(n表示总数位数,m表示小数位数)
double(n,m) 8字节,双精度浮点型(n表示总数位数,m表示小数位数)
decimal(n,m) 存储为字符串的浮点数

练习:创建一个表,包含a1,a2,a3三个列,分别声明为上述3种类型,指定n值为5,m值为3,然后插入123.45678,然后使用select查询显示的值为多少?


(4)字符串数据类型
char(n)      固定长度,最多255个字符
varchar(n)   可变长度,最多65565个字符
tinytext     可变长度,最多255个字符
text         可变长度,最多65535个字符
mediumtext   可变长度,最多2的24次方-1个字符
longtext     可变长度,最多2的32次方-1个字符
     
(5)blog数据类型(以二进制存储类型)
tinyblob   最大255字节
blob       最大65K字节
mediumblob 最大16M字节
longblob   最大4GB
   
(6)其它类型
enum  枚举类型,enum('man','woman')
set   为列举数据中的零个或多个可选值,最多可有64个成员,favorate('篮球','足球','乒乓球','棒球')
     
(7)数据类型的属性
auto_increment  
binary  只用于char和varchar列,以启用大小写的方式进行排序
default 设置默认值,且列必须是常量;enum ('man','woman') not null default 'man'
index  索引,index 索引名（列名）=====有四种索引
primary key 主键索引,值必须唯一且不能为空，一个表只能有一个主键列
not null 不允许为空值
unique  唯一索引,可以允许为空值,可设置多个列

练习:创建一个数据表,包含一个主键,一个唯一索引,一个set类型的列,not null列    
insert into test(name) values('李四');
select * from test
set @@auto_increment_increment=5;    

6、修改表alter table
  

  (1)增加列
    mysql> alter table add column age int;   =====alter table test add column cnum int(2) zerofill;
默认在最后面添加
    mysql> desc test;
    mysql> alter table test add column sex enum('man','woman') not null default 'man' after name;
 alter table test add column phone varchar(15) not null after name;  
   
    mysql> alter table test add column sno varchar(10) first;
   
  (2)删除列
    mysql> alter table test drop id;
    mysql> alter table test drop column age;
    alter table test drop phone;
   
  (3)修改列
    mysql> alter table test modify age tinyint not null; =====修改列的类型
    alter table test modify cnum tinyint;
    alter table test modify cnum tinyint(1) zerofill unsigned;
    mysql> alter table test change name sname varchar(30) not null;
   
    alter table test change cnum cnumber tinyint(1) zerofill unsigned not null;
===============
     Invalid use of NULL value
     
  (4)重命名表
    mysql> alter table test rename to mytest;
    mysql> rename table mytest to test;
   
7、约束
  (1)主键约束(primary key)
  (2)唯一约束(unique)
  (3)非空约束(not null)
  (4)外键约束
  mysql> create table sinfo(id int primary key auto_increment,sno varchar(10) not null unique,name varchar(20) not null,cno varchar(10),age tinyint unsigned,addr varchar(60),foreign key (cno) references class(cno))auto_increment=1,engine=innodb;
   
   mysql> alter table stu_info add constraint CNO_FK foreign key(cno) references class(cno) on delete restrict on update restrict;
   on delete cascade:级联更新和删除
   on delete set null:更新或删除时将子表的相应记录设置为null
   on delete restrict:如果子表有匹配的记录则不允许进行delete/update操作     
   mysql> alter table sinfo drop foreign key sinfo_ibfk_1;
   
8、mysql语句类型
  

     (1)DDL语句
    create database
    drop database
    create table
    alter table
    drop table
    create index
    drop index
     (2)DML语句
    insert(插入数据)
    mysql> insert into sinfo values(2,'s000002','赵芳','c0002',33,'湖南省长沙市');
    mysql> insert into sinfo set id=3,sno='s000003',name='张因',cno='c0003',age=36,addr="湖南省长沙市定王台";
    mysql> insert into sinfo select id,sno,name,cno,age,addr from stu_info;
   
    update(更新数据)
    mysql> update sinfo set age=48 where id=4;
   
    delete(删除数据)
    mysql> delete from sinfo where id>4;
   
    tuncate(清空表)
    mysql> truncate sinfo;
   
    select(数据查询)
   
  where条件表达式:
    and  逻辑与  salary > 5000 and salary<10000
    or   逻辑或  salary < 5000 or salary>10000
    not  逻辑非  not sex="man"
    between     salary between 5000 and 10000
   
    =  等于
    >  大于
    <  小于
    >= 大于等于
    <= 小于等于
    != 不等于
    <> 不等于
   
    +,-,*,/   加、减、乘、除
   
    is null  值为空
    like     字符匹配  name like '%apple%'
      group by和having  分组和条件设置
    limit  指定记录的输出个数
   
    order by 字段名 ASC | DESC
    mysql -u root -paixocm < employees.sql
  mysql -uroot -p123456
use books
source D:/bookorama.sql

select * from salaries where salary>=60000 and salary<=70000;
 
 select * from salaries where salary between 60000 and 70000;

 select * from employees where first_name like '%Mary%';
 
 select * from employees where first_name is not null;

select * from salaries where salary in (50000,60000,70000);
= 等于

mysql> select * from salaries where salary in (50000,60000,70000) order by emp_no ASC;
    mysql> select * from salaries where salary in (50000,60000,70000) order by emp_no desc;


练习:在employees表中统计从1960年(包括1960)后每一年出生的员工数
    

mysql> select year(birth_date) as 出生年份,count(year(birth_date)) as 人数 from employees group by 出生年份 having 出生年份>=1960;
   
    mysql> select year(birth_date) as 出生年份,count(year(birth_date)) as 人数 from employees group by 出生年份 having not 出生年份>=1960;
    函数 =====http://10.0.0.254/functions.html#func-op-summary-ref
   
 例:求员工的平均工资,大于平均工资和小于平均工资的人数
    mysql> select avg(salary) as 平均工资 from salaries;
    select count(*) as 小于平均工资人数,avg(salary) as 平均工资 from salaries where salary<(select avg(salary) from salaries);
    http://blog.youkuaiyun.com/wwxtu24/article/details/3974444
       
   
9、多表查询
  查询所有学生的编号、姓名、成绩和住址
  mysql> select infoa.sno,infoa.name,infoa.score,infob.addr from infoa,infob where infoa.sno=infob.sno;
 
  inner join:取所有查询表的交集
  select infoa.sno,infoa.name,infoa.score,infob.addr from infoa  inner join,infob where infoa.sno=infob.sno;
 
  where=====
  on=======
 
  left join: 取左边表的所有记录和右边表的交集
  right join:取右边边表的所有记录和左边表的交集   

 练习:查询所有住在长沙市的学生编号、姓名、成绩和住址
   
   mysql> select infoa.sno,infoa.name,infoa.score,infob.addr from infoa left join infob on infoa.sno=infob.sno where addr="湖南省长沙市";
   
   mysql> select a.sno,a.name,a.score,b.addr from infoa as a left join infob as b on a.sno=b.sno where addr="湖南省长沙市";
 
 
  练习:查询employees数据库中每个部门的员工人数,然后给出部门的名称和人数

  练习:查询男女员工的人数
  mysql> select gender 性别,count(*) as 人数 from employees group by gender;
 
  练习:查询男女员工的平均工资
  mysql> select employees.gender,avg(salaries.salary) from employees,salaries where employees.emp_no=salaries.emp_no group by gender;
 
  
  练习:列出各部门的平均工资,按照降序进行排列
  mysql> select dept_emp.dept_no as 部门编号,avg(salaries.salary) as 平均工资 from dept_emp inner join salaries on dept_emp.emp_no=salaries.emp_no group by dept_no order by 平均工资 desc;
 
  mysql> select a.dept_name,b.dept_no,avg(c.salary) as 平均工资 from departments a,dept_emp b,salaries c where a.dept_no=b.dept_no and b.emp_no=c.emp_no group by dept_no order by 平均工资 desc;
 
  mysql> select a.dept_name as 部门名称,b.dept_no as 部门编号,avg(c.salary) as 平均工资 from departments a,dept_emp b,salaries c where a.dept_no=b.dept_no and b.emp_no=c.emp_no group by b.dept_no order by 平均工资 desc;
 
  mysql> select a.dept_name as 部门名称,b.dept_no as 部门编号,avg(c.salary) as 平均工资,max(c.salary) as 最高工资,min(c.salary) as 最低工资 from departments a,dept_emp b,salaries c where a.dept_no=b.dept_no and b.emp_no=c.emp_no group by b.dept_no order by 平均工资 desc;
 
  练习:列出每个部门的部门名称名称、部门编号、部门的管理者姓名(包括first_name和last_name)和性别
 
  mysql> select a.dept_no as 部门编号,a.dept_name as 部门名称,concat(c.first_name,' ',c.last_name) as 部门管理者,c.gender as 性别 from departments a,dept_manager b,employees c where a.dept_no=b.dept_no and b.emp_no=c.emp_no order by 部门编号;
 
  练习:将员工编号、员工姓名(first_name+last_name)、所属部门、1990年后加入公司的员工及薪水等信息
      建立一个新表einfo。
     
       mysql> create table einfo(select employees.emp_no,concat(employees.first_name,' ',employees.last_name) as 员工姓名,employees.hire_date,departments.dept_name,salaries.salary from employees left join (departments,salaries,dept_emp) on employees.emp_no=salaries.emp_no and dept_emp.dept_no=departments.dept_no and employees.emp_no=dept_emp.emp_no where year(employees.hire_date)>=1990);