当前位置: 澳门新濠3559 > 数据库 > 正文

只需要学习简单的sql语句以及客户端命令即可

时间:2019-11-29 07:15来源:数据库
准备 ubantu系统,navicat for mysql软件 一、JSTL中的EL函数 字符串操作有关 二、MVC+三层架构 三、用户的注册和登陆案例:必须做 注册:校验:填充模型:类型转换;回显。 登陆: SQL简介

准备

  ubantu系统,navicat for mysql软件

一、JSTL中的EL函数
字符串操作有关
二、MVC+三层架构
三、用户的注册和登陆案例:必须做
注册:校验:填充模型:类型转换;回显。
登陆:
SQL简介:
Structeured Query Language
作用:是一种定义、操作、管理关系数据库的句法。大多数关系数据库都支持
结构化查询语句的工业标准由ANSI维护。
二、SQL语句的组成
*DQL:数据查询语言
*DML:数据操作语言
*DDL:数据定义语言
DCL:数据控制语言
TPL:事务处理语言
CCL:指针控制语言
三、常用数据库
Oracle
DB2
*MySQL:Linux
四、MySQL的安装与配置

引言

  mysql是基于c/s通信架构设计的,用户通过客户端和服务器端进行交互,服务器端对数据库进行操作。简单地来说,我们把sql语句传达给客户端,客户端把sql语句翻译成服务器端可以理解和可以执行的指令,服务器端按照这些指令对数据库进行查询操作,把操作结果传给客户端,客户端再把结果翻译成用户也就是我们可以看懂的内容。c/s架构的好处之一就是用户不需要和服务器端打交道,也就是意味着用户不需要学习复杂的服务器端命令,只需要学习简单的sql语句以及客户端命令即可,客户端会帮我们进行指令的翻译等工作。mysql的c/s架构是一个服务器端可对应多个客户端,对于mysql怎么区分不同用户这里暂不需要了解。

  mysql是关系型数据库的一种,所以我们在设计数据库的时候一定要抓住实物之间的关系。我们以后设计网站的时候,不同的业务板块都应该有一个单独的架构或者数据库,我们可以把单独的板块理解为单独的网站。

图片 1

安装mysql

  命令:sudo apt install mysql-server

image.png

配置mysql

  修改/etc/mysql/my.cnf文件,在文件末尾加上下述几条语句([]表示组)

  [client]

  default-character-set = utf8           # 指定客户端编码,因为默认的latin1不支持中文,所以我们在这里选择utf8

 

  [mysqld]

  character-set-server = utf8            # 指定服务器端编码,通常设定为与客户端编码一致

  collation-server = utf8_general_ci         # 同一种编码也有不同的排序规则

  bind_address = 0.0.0.0                     # 第一层守护,监听所有IP

  配置文件更改时应该先做备份,更改完成之后需要重新启动来重新读取配置文件

  设置完成后查看编码信息与排序信息

  1. SHOW VARIABLES LIKE '%char%';
  2. SHOW VARIABLES LIKE '%colla%';       

验证是否安装配置成功:
MySQL -u root -p

启动mysql

  命令:sudo service mysql restart 或者  sudo systemctl restart mysql

  注意:mysql的服务进程名是mysql,所以命令不是sudo service mysqld restart,我们通常不是直接使用mysqld的程序名来启动程序,因为这个程序可能需要一些复杂的参数,我们借助systemctl 与 service 可以轻松处理相关命令

总结:Java类和表结构对应的。
java对象和表中的一条记录
五、DDL:数据定义语言
作用:用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构。
常用关键字:CREATE ALTER DROP TRUNCATE

navicat连接mysql

  mysql客户端输入下述三条命令:

  1. CREATE USER 'jack'@'%' IDENTIFIED BY '123456'   # jack是用户名,%是任意主机,123456是密码

  2. GRANT ALL ON *.* TO 'jack'@'%'

  3. FLUSH PRIVILEGES

  也可以直接用下面两条命令:

  1. grant all on 数据库.* to 用户名@登录主机 identified by "密码"
  2. flush PRIVILEGES
-----------------------------库操作
显示所有的数据库
SHOW DATABASES;
创建一个名称为mydb1的数据库。
CREATE DATABASE mydb1;
查看数据库的创建细节,可以看到使用的字符集。
SHOW CREATE DATABASE mydb1;
创建一个使用gbk字符集的mydb2数据库。
CREATE DATABASE mydb2 CHARACTER SET gbk;
创建一个使用gbk字符集,并带校对规则的mydb3数据库。
CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
删除前面创建的mydb3数据库
DROP DATABASE mydb3;
查看服务器中的数据库,并把mydb2的字符集修改为utf8;
ALTER DATABASE mydb2 CHARACTER SET utf8;

建库

  查看已有数据库:SHOW DATABASES;

  创建数据库: CREATE DATABASE `test` charset utf8(character set utf8);

  查看数据库创建过程: SHOW CREATE DATABASE `test`;

  删除数据库:DROP DATABASE `test`

  修改数据库编码:ALTER DATABASE `test` charset utf8

  查看数据库版本号:status/select version()

显示当前的数据库
SELECT DATABASES();
常用数据类型
BIT(M)位类型。M指定位数,默认值1,范围1-64
TINYINT带符号的范围是-128到127.无符号0到255.
BOOL使用0或1表示真或假
2的16次方
创建一个员工表:
CREATE TABLE employee{
id int,
name vachar(100),
gender varchar(10),
birthday date,
entry_date date,
job varchar(100),
salary float(8,2),
resume text
};
查看库中的所有表格
SHOW TABLES;
查看表的结构
DESC employee;
在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image blob;
修改job列,使其长度为60。
ALTER TABLE employee MODIFY job varchar(60);
删除image列。
ALTER TABLE employee DROP image;
表名改为user。
只需要学习简单的sql语句以及客户端命令即可。RENAME TABLE employee TO user;
修改表的字符集为utf8
ALTER TABLE user CHARACTER SET utf8;
大小问题:MySQL在window系统下不区分大小写的。
在其他系统严格区分大小写。

备份与还原数据库

  1. 备份整个数据库   mysqldump -uroot -p123456 database>new_database.sql
  2. 备份数据库某张表   mysqldump -uroot -p123456 database table>new_table.sql
  3. 还原数据库   mysqldump -uroot -p123456 database<new_database.sql                # 恢复时数据库必须存在,可以是空数据库
  4. 还原数据库表  登录进入数据库,然后USE选定数据库,用source导入                         

DML:数据操作语言
作用:用于向数据库表中插入、删除、修改数据
常用关键字:INSERT UPDATE DELETE
特别注意:MySQL,字符串或日期要包含在单引号中。
空值:NULL
使用insert语句向表中插入三个员工的信息。
INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (3,'杜小文','male','1935-12-02','2014-09-16','CTO',100000,'杜总');
插入中文数据:
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+------------
-----------+
| Variable_name | Value
|
+--------------------------+------------
-----------+
| character_set_client | utf8 *客户端使用的编码
|
| character_set_connection | utf8 数据库连接时使用的编码
|
| character_set_database | utf8 数据库用的编码
|
| character_set_results | utf8 *查询结果集用的编码
|
| character_set_server | utf8 服务器使用的编码
|
| character_set_system | utf8 系统使用的编码
告知服务器客户端使用的编码:
SET character_set_client=gbk;
将所有员工薪水修改为5000元。
将姓名为’zxn’的员工薪水修改为3000元。
将姓名为’lyx’的员工薪水修改为4000元,job改为ccc。
UPDATE user SET salary=4000,job='OOO' WHERE username='lyx';
将杜小文的薪水在原有基础上增加1000元。
UPDATE user SET salary=salary+1000 WHERE username='杜小文';
删除表中所有记录。
DELETE FROM user;(一条一条的删除)
使用truncate删除表中记录
TRUNCATE TABLE user;(摧毁整张表格,重新建立表结构)
七、DQL:数据查询语言
DQL:Data Query Language
作用:查询数据,返回结果集。
常用关键字:
SELECT
查询表中所有学生的姓名和对应的英语成绩。
SELECT name,english FROM student;
过滤表中重复数据。
SELECT DISTINCT english FROM student;
过滤表中重复数据。
SELECT DISTINCT english FROM student;

建表

  数据库中的表在用户层相当于二维的excel表格

  查看表的创建过程:SHOW CREATE TABLE `mysql`.`user`;

  创建表:CREATE TABLE `test`.`user`(column1 type1 option, colum2 type2 option);

  查看数据库表(已选定数据库情况下):SHOW TABLES;

  移动并重名数据库表:RENAME TABLE `python`.`user` TO `test`.`user`

  删除表:DROP TABLE `test`

  注:

  •  创建表的最后一行不要加逗号;排序 collate=utf8_general_ci不区分大小写,查询快  collate=utf8_genaral_bin区    分大小写,排序速度慢;每个表度需要一个primary key(主键唯一且为非空);char()分配固定长度,    varchar()是最大长度可变的,性能相对较差
  •  数据库引擎:1、myisam   非事务型,查询快  2、innodb   事务型,修改快,默认引擎(功能强大)
  •  数据库表、列和数据库一样,可以单独地设置字符集与排序规则
  •  不指定列的default时默认会default null。
  •  关于字符集编码如果不指定就存在继承现象:列寻找表的编码,表寻找库的编码,库寻找配置文件编码
  •  数据库的每一个字段都必须有固定类型
  •  mysql的其中一个规则是名字(字段名也是名字)要加反引号
  •    c  取消命令(cancle)    G 把列数据逐行显示
  •    主键唯一且非空,但是唯一且非空不一定是主键
  •    建表有个套路就是用没有实际含义的id当作主键
  •    注册的时候哪些字段可以为空,哪些可以为空根据实际需求来定
  •    AUTO_INCREMENT 需要和INT结合使用,有了AUTO_INCREMENT可以不指定id进行插入
  •    只有ALTER、RENAME、CREATE等关键字要接DATABASE 以及TABLE
  •    实体之间的关系分为三种:一对一;一对多;多对多。区分这三种关系可以通过画图来说明。
  •    一对多:使用外键约束来实现
  •    一对一:使用外键 + UNIQUE KEY来实现
  •    多对多:使用外键 + 第三方表来实现

图片 2图片 3

 1 CREATE TABLE `users` (
 2     `id` int PRIMARY KEY AUTO_INCREMENT,
 3     `email` char(50) UNIQUE KEY NOT NULL,
 4     `user_name` varchar(20) NOT NULL,
 5     `telphone` char(11)    
 6 );
 7 
 8 CREATE TABLE `sellers` (
 9     `id` int PRIMARY KEY AUTO_INCREMENT,
10     `user_id` int UNIQUE KEY NOT NULL,
11     FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
12 );
13 
14 CREATE TABLE `houses` (
15     `id` int PRIMARY KEY AUTO_INCREMENT,
16     `summary` varchar(100) NOT NULL,
17     `seller_id` int NOT NULL,
18     FOREIGN KEY (`seller_id`) REFERENCES `seller`(`id`)
19 );
20 
21 CREATE TABLE `sellers_tags`(
22     `sellers_id` int NOT NULL,
23     `tag_id` int NOT NULL,
24     FOREIGN KEY (`sellers_id`) REFERANCES sellers(`id`),
25     FOREIGN KEY (`tag_id`) REFERANCES tags(`id`),
26     UNIQOE KEY(`sellers_id`, `tag_id`)  # PRIMARY KEY(`sellers_id`, 
27 
28 `tag_id`)
29 );
30 
31 CREATE TABLE `tags` (
32     `id` int PRIMARY KEY AUTO_INCREMENT,
33     `name` varchar(20) UNIQUE KEY NOT NULL
34 );

View Code

在所有学生数学分数上加10分特长分。
SELECT name,math+10 FROM student;
统计每个学生的总分。
SELECT name,chinese+english+math FROM student;
使用别名表示学生分数
SELECT name AS 姓名,chinese+english+math 总分 FROM student;

表信息

  1. 查看表的创建语句:SHOW CREATE TABLE `mysql`.`user`;
  2. 删除表:DROP TABLE `mysql`.`user`;
  3. 重命名或移动表:RENAME TABLE FROM `mysql`.`user` TO `test`.`user`
  4. 查看表结构:DESC `mysql`.`user`;
  5. 查看列信息:SHOW COLUMNS FROM  `mysql`.`user` LIKE Pattern
  6. 添加列:ALTER TABLE `mysql`.`user` ADD COLUMN `gender` enum('M','F'),ADD COLUMN `age` int
  7. 删除列:ALTER TABLE `mysql`.`user` DROP COLUMN `gender`(暂不支持删除多列)
  8. 修改现有列的类型和选项:ALTER TABLE `mysql`.`user` MODIFY `gender` char(1) default 1;     注:一旦修改的列已经存在了违反约束的数据,那么修改失败
  9. 完全修改列信息: ALTER TABLE `mysql`.`user` CHANGE `gender` `sex` enum('M', 'F')  注:无法只修改列名字。如果只想修改名字,那么请带上原来的列类型选项

查询姓名为王五的学生成绩
SELECT * FROM student WHERE name='王五';
查询英语成绩大于90分的同学
SELECT * FROM student WHERE english>90;
查询总分大于200分的所有同学
SELECT * FROM student WHERE (chinese+english+math)>200;

数据类型

  MySQL支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。

  1. 数值数据类型:包括整数类型TINYINT(1byte)、SMALLINT(2bytes)、MEDIUMINT(3bytes)、INT(4bytes)、BIGINT(8bytes)、浮点小数数据类型FLOAT(4bytes)和DOUBLE(8bytes)、定点小数类型DECIMAL。# 默认是有符号的。参数是可以显示的最大位数
  2. 日期/时间类型:包括YEAR(1byte)、TIME(3bytes)、DATE(3bytes)、DATETIME(8bytes)和TIMESTAMP(4bytes)
  3. 字符串类型:包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等

  YEAR的日期格式是YYYY, TIME的日期格式是HH:MM:SS, DATE的日期格式是YYYY-MM-DD, DATETIME的日期格式是YYYY-MM-DD HH:MM:SS,TIMESTAMP的日期格式是YYYY-MM-DD HH:MM:SS。

  字符串类型括号里的参数意义就是字节数

查询英语分数在 80-90之间的同学。
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
查询数学分数为89,90,91的同学。
SELECT * FROM student WHERE math IN (89,90,91);
查询所有姓李的学生成绩。
SELECT * FROM student WHERE name LIKE '李%';
查询数学分>80,语文分>80的同学。
SELECT * FROM student WHERE math>80 AND chinese>80;

键和索引

  mysql中可以把键就理解为索引,索引就是为某些列,花费额外的空间,来维持一个高效的数据结构,加快查找速度。注:加速只对指定的列有效

  功能键(约束键):1、主键     2、唯一键      3、外键

    非功能键(索引)

  1. 添加索引   ALTER TABLE `mysql`.`user` ADD INDEX(`age`)
  2. 删除索引   ALTER TABLE `mysql`.`user` DROP index `age`

对数学成绩排序后输出。
SELECT name,math FROM student ORDER by math;
对总分排序后输出,然后再按从高到低的顺序输出
SELECT name AS 姓名,chinese+english+math 总分 FROM student ORDER BY 总分 DESC;
对姓李的学生成绩排序输出
SELECT * FROM student WHERE name LIKE '李%' ORDER BY chinese;

记录(行)之增加

  1. 普通插入: INSERT INTO `users` VALUES ();                 # 默认为空的也必须插入,否则这条语句语法就无法通过
  2. 指定列插入:INSERT INTO  `users`  () VALUES ()                         # 插入指定数据需要知道哪些不需要指定值
  3. 一次插入多行:INSERT INTO `users` VALUES (),()

八、数据完整性(很重要)
数据完整性是为了保证插入到数据中的数据是正确的,它防止了用户可能的输入错误。
1、实体(行)完整性
规定表的一行(即每一条记录)在表中是唯一的实体。
通过定义主键约束来实现。
主键:PRIMARY KEY
特点:不能为null,且唯一。
主键:
逻辑主键:比如ID,不代表实际的业务意义,只是用来标识一条记录
业务主键:比如username作为主键。

记录(行)之删除

  1. 清空表:DELETE FROM `users` WHERE 1
  2. 删除指定行:DELETE FROM `users` WHERE pattern

  注:通常在删除之前,我们要注意我们的WHERE,同时,我们建议在删除前,使用相同的WHERESELECT先查询一遍

方式一:
    CREATE TABLE t1(
        id int PRIMARY KEY,
        name varchar(100)
    );
    方式二:可以定义联合主键
    CREATE TABLE t2(
        id int,
        name varchar(100),
        PRIMARY KEY(id)
    );
方式三:(推荐)
    CREATE TABLE t3(
        id int,
        name varchar(100)
    );
    ALTER TABLE t3 ADD PRIMARY KEY (id);

记录(行)之改

  UPDATE `users` SET `name`='jack' WHERE pattern

  注:通常在删除之前,我们要注意我们的WHERE,同时,我们建议在删除前,使用相同的WHERESELECT先查询一遍

自动增长的主键:(Oracle没有的)
CREATE TABLE t4(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(100)
);
2、域(列)完整性
指数据库的列必须符合某种特定的数据类型或约束
数据类型
长度
非空约束:NOT NULL
唯一约束:UNIQUE
CREATE TABLE t5(
username varchar(100) NOT NULL UNIQUE,
gender varchar(100) NOT NULL,
phonenum varchar(100) UNIQUE
);
3、参考完整性:多表,外键约束
多表设计:
一对多

记录(行)之查

  1. 完整查询:  SELECT * FROM `users` WHERE 1
  2. 指定列查询: SELECT Column1,Column2 FROM `users` WHERE 1
  3. 指定行查询: SELECT * FROM `users` WHERE pattern
  4. 查询排序:SELECT * FROM `users` WHERE pattern ORDER BY `u_id` `name` DESC
  5. 分组查询:SELECT `gender`,COUNT(*) AS num FROM WHERE 1 GROUP BY `gender`

  注:分组查询只有和聚合函数结合使用才有意义

图片 4

多表查询

  •  内联接(交叉连接)

    SELECT `s_name`, `c_flower`  FROM `classes`   INNER JOIN `students`** ON `students`.`c_id` = `classes`.`c_id` WHERE `students`.`c_id` = 9;

    注:on是连接条件,where是筛选条件。也就是说,ON用来连接出有意义数据行的条件,WHERE是已经连接了以后,再筛选出当时有需要的数据

  •  外连接(左连接与右连接)

    SELECT * FROM FROM `classes`   LEFT JOIN `students`** ON `students`.`c_id` = `classes`.`c_id`

    注:左连接以左表为基准,右连接以右表为基准。区别于内连接的是外连接必须要用ON连接条件

image.png

 

  

     

 

  

  

  

 

 

 

CREATE TABLE customers(
id int,
name varchar(100),
address varchar(255),
PRIMARY KEY(id)
);
CREATE TABLE orders(
id int PRIMARY KEY,
order_num varchar(100),
price float(8,2),
status int,
customer_id int,
CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)
);
类和数据库表结构对应,对象和数据库表中的记录对应。
类的设计有方向
public class Customer{
private int id;
private String name;
private String address;
private List<Order> orders = new ArrayList<Order>();
}
public class Order{
private int id;
private String orderNum;
private float price;
private int status;
private Customer customer;
}
多对多
CREATE TABLE teachers(
id int PRIMARY KEY,
name varchar(100),
salary float(8,2)
);
CREATE TABLE students(
id int PRIMARY KEY,
name varchar(100),
grade varchar(100)
);
CREATE TABLE teacher_student(
t_id int,
s_id int,
PRIMARY KEY(t_id,s_id),
CONSTRAINT teacher_id_fk FOREIGN KEY(t_id) REFERENCES teachers(id),
CONSTRAINT student_id_fk FOREIGN KEY(s_id) REFERENCES students(id)
);

 

  

        类如何设计:
        public class Teacher{//多对多
            private int id;
            private String name;
            private float salary;
            private List<Student> students;
        }
        public class Student{
            private int id;
            private String name;
            private String grade;
            private List<Teacher> teachers;
        }

一对一
按外键关联,按主键关联(实际开发基本不用)
九、多表查询
a、交叉链接:
SELECT * FROM customers,orders;
b、内连接:
SELECT * FROM customers c,orders o WHERE c.id=o.customer_id;
c、外连接:
查询所有的客户信息,同时把对应的订单查询出来。
左外:返回符合链接条件的记录,同时返回左表中不满足链接条件的剩余记录
SELECT * FROM customers c LEFT OUTER JOIN orders o ON c.id=o.customer_id;
右外:
SELECT * FROM customers c RIGHT JOIN orders o ON c.id=o.customer_id;
2、几个简单的子查询
嵌套查询,子查询的语句放到小括号之内。 ID=1的这个老师教过哪些学员(语句的查询形式:多条SQL语句)
SELECT s_id FROM teacher_student WHERE t_id=1;
SELECT * FROM students WHERE id IN (1,2);
子查询:
SELECT * FROM students WHERE id IN (SELECT s_id FROM teacher_student WHERE t_id=1);
十、分组统计
统计一个班级共有多少学生?
SELECT COUNT() FROM student;
统计数学成绩大于90的学生有多少个?
SELECT COUNT(
) FROM student WHERE math>90;
统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student WHERE (chinese+english+math)>250;

统计一个班级数学总成绩?
SELECT SUM(math) FROM student;
统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese),SUM(english),SUM(math) FROM student;
统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese+english+math) FROM student;
统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

求一个班级数学平均分?
SELECT AVG(math) FROM student;//不会包含为null
求一个班级总分平均分
SELECT AVG(chinese+english+math) FROM student;
注意:可以使用` 反引号把关键字包含起来,当做普通字符串对待。
对订单表中商品归类后,显示每一类商品的总价
SELECT product,SUM(price) FROM t_order GROUP BY product;
查询购买了几类商品,并且每类总价大于100的商品
SELECT product,SUM(price) FROM t_order GROUP BY product HAVING SUM(price)>100;
十一、MySQL备份与恢复
备份:
c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql

恢复:
先创建数据库名,并选择数据库
方式一:mysql>SOURCE d:/mydb1.sql;
方式二:c:/>mysql -u root -p test<d:/mydb1.sql

编辑:数据库 本文来源:只需要学习简单的sql语句以及客户端命令即可

关键词:

  • 上一篇:没有了
  • 下一篇:没有了