Mysql命令行练习(1)

注意：修改数据库的字符集编码，可以修改\Mysql server 5.0文件下my,ini文件，未修改此文件，一般在81行，有一个设置charset的默认字符集，修改即可。

---

###SQL语法地址
[语法地址：http://www.w3school.com.cn/sql/sql_unique.asp]
(http://www.w3school.com.cn/sql/sql_unique.asp)
[语法地址：http://www.runoob.com/mysql/mysql-tutorial.html]
(http://www.runoob.com/mysql/mysql-tutorial.html)

---

###java和mysql的数据类型比较
String ------char(n) varchar(n) 255 65535
byte short int long float double ------ tinyint smallint int bigint float double
boolean ------bit 0/1
Date ------ Date、Time、DateTime、timestamp、
FileInputStream FileReader ------ Blob Text

---

###一、数据库
####1.创建数据库
create database [if not exists] db_name [character set xxx] [collate xxx];
*创建一个名称为mydb1的数据库
~>create database mydb1;
*创建一份使用utf8字符集的mydb2数据库
~>create database mydb2 character set utf8;
*创建一个使用utf8字符集，并带校对规则的mydb3数据库
~>crete databse mydb3 character set utf8 collate utf8_bin;
####2.查看数据
*显示数据库
~>show databases;
*查看数据库的创建方式
~>show create database db_name;
####3.修改数据库
~>alter database db_name [character set xxx] [collate xxx]
####4.删除数据库
~>drop database [if exists] db_name;
####5.使用数据库
*切换数据库
~>use db_name;

---

###二、表
####1.创建表
create table tb_name(
field1 type,
field2 type,
…
fieldn type
)[character set xx] [collate xxx];
约束：
primary key
unique
not null
auto increment 主键字段必须是数字类型
外键约束

*示例1：创建一个员工表employee
~>create table employee(
	id int primary key auto_increment,
	name varchar(20) not null,
	identity_card_id varchar(30),
	gender bit default 1,
	birthday date,
	entry_date date,
	job varchar(20),
	salary double,
	bonus double,
	pay_date date,
	other_salary double,
	resume text
	UNIQUE(identity_card_id)
);
*示例2：创建考试成绩表
~>create table exam(
	id int primary key auto increment,
	name varchar(20) not null,
	chinese double,
	math double,
	english double
);

####2.查看表信息
*查看表结构
~>desc tab_name;
*查看当前数据库中的所有表
~>show tables;
*查看当前数据库表建表语句
~>show create table tab_name;
####3.修改表结构
(1) 增加一列
~>alter table tab_name add [column] 列名 类型;
示例:
a.在表employee最后一列添加一列
~>alter table employee add column addr varchar(20) not null;
b.在表employee第一列添加一列
~>alter table employee add column addr varchar(20) not null first;
c.在表employee列名为：birthday的后边添加一列
~>alter table employee add column addr varchar(20) not null after birthday;

(2)修改一列类型
~>alter table tab_name modify 列名 类型;
 
(3)修改列名
~>alter table tab_name change [column] 列名 新列名;

(4)删除一列
~>alter table tab_name drop [column] 列名;

(5)修改表名
~>rename table 表名 to 新表名;

(6)修改表所用的字符集
~>alter table ta_name character set utf8;

####4.删除表
~>drop table tab_name;

---

###三、表记录
####1.增加记录 insert
~>insert into tab_name (field1,field2,…) values (value1,value2,…);
要点：
*插入的数据应与字段的数据类型相同
*数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中
*字符和日期类型数据应包含在单引号中’zhang’ ‘2013-04-20’
*插入空值:不指定某列的值或insert into table value(null),则该列取空值
*如果要插入所有字段可以省略列表，直接按表中字段顺序写值 insert into tab_name values(value1,value2,…);

*示例：使用insert语句向表中插入三个员工的信息。
a.单条数据插入
~>inser into employee (name,birthday,entry_date,job,salary) values ('张三','1990-01-01','2000-09-01','码农',999);
b.多条数据插入
~>inser into employee (name,birthday,entry_date,job,salary) values ('李四','1990-01-01','2000-09-01','码农',999),('王五','1990-01-01','2000-09-01','码农',999);

####2.修改表记录update
update tab_name set field1=value1,field2=value2,…[where 语句]
要点:
*update语法可以用新值更新原有表中的各列
*set子句指示要修改哪些列和要给予哪些值
*where子句指示应更新哪些行。如果没有shere子句，则更新子句

示例：
a.将所有员工的薪水更改为5000元
~>update employee set salary=9999;
b.将姓名为'张三'的员工薪水修改为8888
~>update employee set salary=8888 where name='张三';
c.将姓名为'张三'的员工薪水修改为19999,job改为PM
~>update employee set salary=19999,job='PM' where name='张三';
d.将姓名为'张三'的薪水在原有的基础上增加1000元
~>update employee set salary=salary+1000 where name='张三';

####3.删除表操作
delete from tab_name [where …]
要点:
*如果不跟shere语句则删除整张表的数据
*delete只能用删除一行记录，不能删除一行记录中的某一列
*delete语句只能删除表中的内容，不能删除表本身，要想删除表，用drop
*同insert和update 一样，从一个表中删除记录将引起其他表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题
*TRUNCATE TABLE 也可以删除表中的所有数据，此语句先摧毁表，再新建表。此种方式删除的数据不能在事物中恢复。
示例：
a.删除表中名称为’张三’的记录；
~>delete from employee where name=‘张三’;
b.删除表中所有数据
~>delete from employee;
c.使用truncate删除表中记录
~>truncate table employee;
####4.select操作
#####(1)
~>select [distinct] *|field1,field2… from tab_name;
其中from指定从哪张表筛选，*标识查找所有列，也可以指定一个列，表明指定要查找的列，distinct 用来剔除重复行。
示例：
a.查找表中所有员工的信息
~>select * from employee;
b.查找表中所有员工的姓名和对应的薪水
~>select name,salaryvfrom employee;
c.过滤重复信息
~>select distinct name from employee;
#####(2)select 也可以使用表达式，并且可以使用 as 别名
a.在所有员工的薪水上加上1000项目奖金显示
~>select name,salary+1000 from employee;
b.查询所有员工除奖金之外的其余薪水部分
~>select name,salary-bonus from employee;
c.查询所有员工除奖金之外的其余薪水部分,用别名base_salary
~>select name,salary-bonus as base_salary from employee;
#####(3)使用where子句，进行过滤查询
示例：
a.查询姓名为XXX的员工薪水
~>select salary from employee where name=‘张三’;
b.查询薪水大于999的员工姓名和薪水
~>select name,salary from employee where salary>999;
c.查询基础薪水大于3000的所有员工
~>select name,salary-bonus as base_salary from employee where salary-bonus>3000;

*where子句中可以使用，比较运算符：
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于

between 数值1 and 数值2 在数值1和数值2之间
例如:between 10 and 20  值在10到20之间

in
例如：in(10,30,20) 值是10或20或30
pattern pattern可以使%或者_,如果是%则表示任意多字符，
举例：
张三，张三三，张一一，如果表示一个字符 张_,张三符合

*逻辑运算符 and or in not
有多个条件直接可以使用以上逻辑运算符
*is null
举例：
a.查询薪水在999-9999之间的员工
~>select name,salary from employee where salary between 999 and 9999;
b.查询薪水为7777，8888，8080的员工姓名
~>select name from employee where salary in(7777,8888,8080);
c.查询所有姓张的员工的姓名和生日
~>select name,birthday from employee where name like '张%';
d.查询薪水大于9999，项目奖金大于6666的员工姓名
~>select name from employee where salary>9999 and bonus>6666;
e.查询没有奖金的员工姓名
~>select name from employee where bonus is null;

#####(4)
(1)order by 指定排序的列，排序的列即是表中的列，也可以是select 语句后指定的列
(2)Asc 升序、Desc 降序、其中Asc为默认值
(3)order by 子句应位于select语句的结尾

示例：
a.对薪水排序后显示
~>select * from employee ORDER BY salary;  
b.对基础薪水排序，按从高到低的顺序输出
~>select name,(ifnull(salary,0)+ifnull(bonus,0)) as base_salary from employee ORDER BY base_salary DESC;
c.对姓张的员工基础薪水排序输出
~>select name,(ifnull(salary,0)+ifnull(bonus,0)) as base_salary from employee like '张%' ORDER BY base_salary DESC;

#####(5) 聚合函数
技巧：先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。

(1)count(列名)
示例：
a.统计一个公司共有多少员工？先查出所有员工，再用count包上。
~>select count(*) from employee;
b.统计员工薪水大于6666的员工人数。
~>select count(salary) from employee where salary>6666;
c.统计基础工资大于4000的员工数量
~>select count(name) from employee where (ifnull(salary,0)-ifnull(bonus,0))>4000;

(2)sum(列名)
示例：
a.统计一个公司员工薪水总和，先查出基础工资，再包上sum
~>select sum(salary) from employee;
b.分别统计一个公司薪水、奖金的总和
~>select sum(salary),sum(bonus) from employee;
c.统计一个公司员工的平均薪水
~>select sum(salary)/count(*) from employee;

注意：
sum仅对数值起作用，否则会报错。

(3)AVG(列名)
a.求一个公司员工的平均工资
~>select avg(ifnull(salary,0)) from employee;
b.求一个公司员工包含正常薪水和额外收入在内的薪水总额的平均薪水
~>select avg(ifnull(salary,0)+ifnull(other_salary,0)) from employee;

(4)Max 、Min
示例：
a.求公司最工薪水和最低薪水
~>select Max(ifnull(salary,0)) from employee;
~>select Min(ifnull(salary,0)) from employee;

(5)group by字句，其后可以接多个列名，也可以跟having子句对group by的结果进行筛选。
示例：
a.对员工的薪水按姓名归类，显示员工的姓名、薪水，和发放日期
~>select name,salary,pay_date from employee group by pay_date;
b.对员工的薪水按姓名归类，最近发放的日期排列，显示员工的姓名、薪水，和发放日期
~>select name,salary,pay_date from employee group by pay_date order by DESC;
c.除'张三'外，对员工的薪水按姓名归类,显示员工的姓名、薪水，和发放日期,且发放日期在'2016.01.01'到'2017-01-01'之间的薪水
~>select name,salary,pay_date from employee where name <> '张三' group by name having Date(pay_date) between '2016.01.01' and '2017.01.01';
(6)重点：Select from where froup by having order by
执行顺序：from where select group by having order by
示例分析：
a.查询失败的语句
~>select math+english+chiese as total_score from exam where total_score>270;
b.查询成功的语句
~>select math+english+chiese as total_score from exam having total_score>270;
c.查询成功的语句
~>select math+english+chiese as total_score from exam group by total_score having total_score>270;
d.查询成功的语句
~>select math+english+chiese as total_score from exam order by total_score;
e.查询成功的语句
~>select * from exam as score where score.math>90;

---

###四约束
####1.创建表时指定约束
create table tb(
id int primary key auto_increment,
name varchar(20) unique not null,
ref_id int,
foreign key(ref_id) references tb2(id)
);
create table tb2(
id int primary key auto_increment
);
####2.外检约束
(1)增加外键:
可以明确指定外键的名称，如果不指定外键的名称，mysql会自定为你创建一个外键名称。
RESTRICT:只要本表格里面右指向主表的数据，在主表里面就无法删除相关记录。
CASCADE:如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。
~>alter table book add [consstraint FK_BOOK] foreign key (pubid) reference pub_com(id) [on delete restrict] [on update restrict];
(2)删除外键:
~>alter table 表名 drop foreign key 外键(区分大小写、外键名可以desc 表名查看);
(3)主键约束:
<1>增加外键(自动增长，只有主键可以自动增长)
~>alter table tb add primary key(id) [auto_increment];
<2>删除主键
~>alter table 表名 frop primary key;
<3>增加自动增长
~>alter table employee modify id int auto_increment;
<4>删除自动增长
~>alter table tb modify id int;
###五、多表设计
一对一(111教室和20170101班级，两方都是一)：在任意一方保存另一方的主键
一对多、多对一(班级和学生，其中班级为1，学生为多)：在多的一方保存一对一的主键
多对多(教师和学生，两方都是多)：使用中间表，保存对应关系
###六多表查询
~>create table tb(
id int primary key auto_increment，
name varchar(20)
);
~>create table ta(
id int primary key,
name varchar(20),
tb_in int
);
~>insert into tb values(1,‘财务部’);
~>insert into tb values(2,‘人事部’);
~>insert into tb values(3,‘科技部’);

~>insert into ta values(1,'财务部');
~>insert into ta values(2,'人事部');
~>insert into ta values(3,'科技部');

~>select * from ta;
+----+------+------+
| id |name  | tb_id|
+----+------+------+
| 1 |  aaa  |     1|
| 2 |  bbb  |     2|
| 3 |  ccc  |     4|
+----+------+------+

~>select * from tb;
+----+------+
| id |name  |
+----+------+
| 1 |  xxx  |
| 2 |  yyy  |
| 3 |  yyy  |
+----+------+

1.笛卡尔积查询，两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录 其中喝多错误数据
select * from ta,tb;
~>select * from ta,tb;
+----+------+-----+------+------+
|id	 | name |tb_id|  id  | name |
+----+------+-----+------+------+
|1   |aaa   |1    |1     |xxx   |
|2   |bbb   |2    |1     |xxx   |
|3   |bbb   |4    |1     |xxx   |
|1   |aaa   |1    |2     |yyy   |
|2   |bbb   |2    |2     |yyy   |
|3   |bbb   |4    |2     |yyy   |
|1   |aaa   |1    |3     |yyy   |
|2   |bbb   |2    |3     |yyy   |
|3   |bbb   |4    |3     |yyy   |
+----+------+-----+------+------+

2.内连接：查询两张表中都有的相关数据，相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from ta,tb where ta.tb_id = tb.id;
select * from ta inner join tb on ta.ta_id = tb.id;
~>select * from ta inner join tb on ta.tb_id = tb.id;
+----+------+-----+------+------+
|id	 | name |tb_id|  id  | name |
+----+------+-----+------+------+
|1   |aaa   |1    |1     |yyy   |
|2   |bbb   |2    |2     |yyy   |
+----+------+-----+------+------+

2.外连接
<1>左外连接：在内连接的基础上增加左边有右边没有的结果
select * from ta left join tb on ta.tb_id = tb.id;
~>select * from ta left join tb on ta.tb_id = tb.id;
+----+------+-----+------+------+
|id	 | name |tb_id|  id  | name |
+----+------+-----+------+------+
|1   |aaa   |1    |1     |xxx   |
|2   |bbb   |2    |2     |yyy   |
|3   |bbb   |4    |NULL  |NULL  |
+----+------+-----+------+------+

<2>.右外连接：在内连接的基础上增加右边有左边没有的结果
select * from ta right join tb on tb on ta.tb_id = tb.id;
~>select * from ta right join tb on ta.tb_id = tb.id;
+----+------+-----+------+------+
|id	 | name |tb_id|  id  | name |
+----+------+-----+------+------+
|1   |aaa   |1    |1     |xxx   |
|2   |bbb   |2    |2     |yyy   |
|NULL|NULL  |NULL |3     |yyy   |
+----+------+-----+------+------+

<3>全外连接：在内连接的基础上增加左边有右边没有和右边有左边没有的结果
select * from ta full join tb on ta.tb_id = tb.id;
---mysql不支持全外连接
select * from tb_left join tb on ta.tb_id = tb.id
union
select * from ta right join tb on ta.tb_id = tb.id;

~>select * from ta left join tb on ta.tb_id = ta.id
~>union
~>select * from ta right join tb on ta.tb_id = tb.id;
---mysql可以使用此种方式间接实现全外连接
+----+------+-----+------+------+
|id	 | name |tb_id|  id  | name |
+----+------+-----+------+------+
|1   |aaa   |1    |1     |xxx   |
|2   |bbb   |2    |2     |yyy   |
|3   |bbb   |4    |NULL  |NULL  |
|NULL|NULL  |NULL |3     |yyy   |
+----+------+-----+------+------+