javaweb_day10 mysql总结

SQL语句
shell>>window命令
mysql>>MySQL命令
一、数据库简介
1、SQL：Structured Query Language (结构化查询语言)
工业标准。
MySQL:支持标准的SQL。在此基础上进行了扩展。方言
Oracle：支持标准的SQL。在此基础上进行了扩展。方言。PLSQL
2、验证安装是否成功
shell>>mysql -u root -p


显示所有的数据库
mysql>>SHOW DATABASES;    //查看数据库
除了test，其他两个不要乱动


二、DDL：数据定义语言 Data Definition Language  针对数据库及表本身的结构
CREATE创建 ALTER修改 DROP删除
----------------------------------------------------------------------
创建一个名称为mydb1的数据库。
mysql>>CREATE DATABASE mydb1;
查看数据库的创建细节
mysql>>SHOW CREATE DATABASE mydb1;
创建一个使用utf8字符集的mydb2数据库。
mysql>>CREATE DATABASE mydb1 CHARACTER SET utf8;
创建一个使用gbk字符集，并带校对规则的mydb3数据库。
mysql>>CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
删除前面创建的mydb3数据库
mysql>>DROP DATABASE mydb3;
查看服务器中的数据库，并把其中某一个库mydb2的字符集修改为gbk;
mysql>>ALTER DATABASE mydb2 CHARACTER SET gbk;


备份数据库：只会备份数据库中的表结构和表数据
选择test数据库
mysql>>USE test;
备份test数据库到文件中
shell>>mysqldump -u root -psorry test>c:/test.sql
恢复数据库:备份时不会备份数据库名本身
方式一：
mysql>>CREATE DATABASE test;
mysql>>USE test;
mysql>>SOURCE c:/test.sql;
方式二：
mysql>>CREATE DATABASE test;
shell>>mysql -u root -psorry test<c:/test.sql
*********************************************************************
创建一个员工表
mysql>>CREATE TABLE employee (id int,name varchar(100),gender varchar(10),birthday date,entry_date date,job varchar(100),salary float(8,2),resume text);
查看数据库中的表格
mysql>>SHOW TABLES;
查看表结构
mysql>>DESC employee;
在上面员工表的基本上增加一个image列。
mysql>>ALTER TABLE employee ADD image BLOB;
修改job列，使其长度为60。
mysql>>ALTER TABLE employee CHANGE job job varchar(60);
删除gender列。
mysql>>ALTER TABLE employee DROP gender;
表名改为user。
mysql>>RENAME TABLE employee TO user;
查看表的创建信息
mysql>>SHOW CREATE TABLE user;
修改表的字符集为utf-8
mysql>>ALTER TABLE user CHARACTER SET utf8;
列名name修改为username
mysql>>ALTER TABLE user CHANGE name username varchar(100);
----------------------------------------------------------------------
三、DML：数据操作语言 Data Manipulation Language 针对表中的记录的
INSERT增加 DELETE 删除 UPDATE 更改
----------------------------------------------------------------------
日期和字符串类型的值：应该用单引号引起来。
向user表中插入三条数据
mysql>>INSERT INTO user (id,username,birthday,entry_date,job,salary) VALUES (1,'liumingyue','1990-09-08','2012-10-15','CEO',10000.00);建议使用
mysql>>INSERT INTO user VALUES (2,'wangguili','1992-09-08','2012-10-15','CTO',9000.00);
查看表中的所有记录
mysql>>SELECT * FROM user;
插入中文数据
mysql>>INSERT INTO user (id,username,birthday,entry_date,job,salary) VALUES (3,'位丹丹','1993-09-08','2012-10-15','CMO',9000.00);
编码有关
数据库的编码：
mysql>>SHOW VARIABLES LIKE 'character%';
character_set_client:是客户端使用的字符集。
character_set_connection:是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
character_set_database:是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。 
character_set_results:是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。       
character_set_server:是服务器安装时指定的默认字符集设定
character_set_system:是数据库系统使用的字符集设定。


告知数据库服务器，客户端传递数据用的编码
mysql>>SET character_set_client=gbk;
查看中文数据时的乱码问题
mysql>>SET character_set_results=gbk;


将所有员工薪水修改为20000元。
mysql>>UPDATE user SET salary=20000;
将姓名为’wangguili’的员工薪水修改为30000元。
mysql>>UPDATE user SET salary=30000 WHERE username='wangguili';
将姓名为’liumingyue’的员工薪水修改为40000元,job改为UFO。
mysql>>UPDATE user SET salary=40000,job='UFO' WHERE username='liumingyue';
将位丹丹的薪水在原有基础上增加1000元。
mysql>>UPDATE user SET salary=salary+10000 WHERE username='位丹丹';


删除表中名称为’位丹丹’的记录。
mysql>>DELETE FROM user WHERE username='位丹丹';
删除表中所有记录。
mysql>>DELETE FROM user;  删除效率低。一条一条地删除。
使用truncate删除表中记录
mysql>>TRUNCATE user;  删除效率高。摧毁整张表格，然后重新建立表结构。
----------------------------------------------------------------------
四、DQL：数据查询语言 Data Query Language 
SELECT查询
----------------------------------------------------------------------
查询表中所有学生的信息。
mysql>>SELECT id,name,chinese,math,english FROM student;
mysql>>SELECT * FROM student; //不建议使用
查询表中所有学生的姓名和对应的英语成绩。
mysql>>SELECT name,english FROM student;
过滤表中重复数据。
mysql>>SELECT DISTINCT english FROM student;
在所有学生语文分数上加10分特长分。
mysql>>SELECT name,chinese+10 FROM student;
统计每个学生的总分。
mysql>>SELECT name,(chinese+english+math) FROM student;
使用别名表示学生分数。
mysql>>SELECT name AS 姓名,(chinese+english+math) 总分 FROM student;
查询姓名为wu的学生成绩
mysql>>SELECT * FROM student WHERE name='王五';
查询英语成绩大于90分的同学
mysql>>SELECT * FROM student WHERE english>90;
查询总分大于200分的所有同学
mysql>>SELECT name,(chinese+english+math) FROM student WHERE (chinese+english+math)>200;
查询英语分数在 80－90之间的同学。
mysql>>SELECT * FROM student WHERE english BETWEEN 80 AND 90;  (含80和90)
查询数学分数为89,90,91的同学。
mysql>>SELECT * FROM student WHERE math IN (89,90,91);
查询所有姓李的学生成绩。
mysql>>SELECT * FROM student WHERE name LIKE '李%';
查询数学分>80，语文分>80的同学。
mysql>>SELECT * FROM student WHERE math>80 AND chinese>80;
对数学成绩排序后输出。
mysql>>SELECT * FROM student ORDER BY math;  //默认是升序
对总分排序后输出，然后再按从高到低的顺序输出
mysql>>SELECT name,(chinese+english+math) FROM student ORDER BY (chinese+english+math) DESC;
对姓李的学生总成绩排序输出
mysql>>SELECT name,(chinese+english+math) FROM student WHERE name LIKE '李%' ORDER BY (chinese+english+math);
统计一个班级共有多少学生？
mysql>>SELECT COUNT(*) FROM student;
统计数学成绩大于等于90的学生有多少个？
mysql>>SELECT COUNT(*) FROM student WHERE math>=90;
统计总分大于250的人数有多少？
mysql>>SELECT COUNT(*) FROM student WHERE (chinese+english+math)>250;
统计一个班级数学总成绩？
mysql>>SELECT SUM(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
mysql>>SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
统计一个班级语文、英语、数学的成绩总和
mysql>>SELECT SUM(math+chinese+english) FROM student;
统计一个班级语文成绩平均分
mysql>>SELECT SUM(chinese)/COUNT(*) FROM student;
求一个班级数学平均分？
mysql>>SELECT AVG(math) FROM student;
求一个班级总分平均分
mysql>>SELECT AVG(math+chinese+english) FROM student;
求班级语文最高分和最低分（数值范围在统计中特别有用）
mysql>>SELECT MAX(chinese) FROM student;


特别注意：用关键字作为表名，要使用``（Esc按键下面）包围起来。


对订单表中商品归类后，显示每一类商品的总价
mysql>>SELECT id,product,price,SUM(price) FROM orders GROUP BY product;
查询购买了几类商品，并且每类总价大于100的商品
mysql>>SELECT id,product,SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;
----------------------------------------------------------------------
（选修）DCL：数据控制语言
GRANT授权
五、约束
1、定义主键约束
主键：唯一标识一条记录。必须输入且唯一。
逻辑主键：与业务无关   id
业务主键：与业务有关  


PRIMARY KEY定义主键
2、主键自动增长：不是所有的数据库都支持.实际开发中，主键最好由自己的应用程序维护。
MySql:  auto_increment
3、非空约束：NOT NULL
4、唯一约束：UNIQUE
CREATE TABLE employee(
id int PRIMARY KEY auto_increment,
gender varchar(10) not null,
username varchar(100) UNIQUE not null
);


INSERT INTO employee (gender,username) VALUES('male','admin'); 


六、多表标准设计
1、一对多或多对一
CREATE TABLE department(
id int primary key auto_increment,
name varchar(100)
);
CREATE TABLE employee(
id int primary key auto_increment,
name varchar(100),
salary float(8,2),
dept_id int,
CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
2、多对多
CREATE TABLE teacher(
id int primary key auto_increment,
name varchar(100),
salary float(8,2)
);
CREATE TABLE student(
id int primary key auto_increment,
name varchar(100),
grade varchar(10)
);
CREATE TABLE teacher_student(
t_id int,
s_id int,
PRIMARY KEY(t_id,s_id),
CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
);
3、一对一
CREATE TABLE person(
id int primary key auto_increment,
name varchar(100)
);
CREATE TABLE idcard(
id int primary key auto_increment,
num varchar(100),
CONSTRAINT p_id_fk FOREIGN KEY(id) REFERENCES person(id)
);
========================================================================
1一对一的关系:  在任意一方保存另一方的主键
2一对多的关系:  在多的一方保存一的主键
3多对多的关系:创建一个关系表保存两张表的主键对应关系
selcet * from ta,tb; 笛卡尔基
selcet * from ta.tb_id=tb.id; 内连接  (左边有 右边也有的)
selcet * from  inner join tb on ta.tb_id=tb.id;  内连接
select * from ta left join  tb on ta.tb_id=tb.id  左连接(左边有, 右边没有的连接)
select * from ta right join  tb on ta.tb_id=tb.id  右连接(左边没有,右边有的连接)
select * from ta full join  tb on ta.tb_id=tb.id  全连接(sql里有,但是mysql不支持)
==================================================================================================
select * from ta left join  tb on ta.tb_id=tb.id  
union
select * from ta right join  tb on ta.tb_id=tb.id  ;  全连接(把两个查询结果 连接在一起 使用union的方式)
==============================================================================