Java基础学习笔记二十五 MySQL

mate10pro 2018年06月05日数据库 1298 0

MySQL

在dos中操作mysql

连接mysql命令： mysql -uroot -p密码，连接OK，会出现mysql>

对数据库的操作

创建一个库

create database 库名 
create database 库名 character set 编码

mysql> create database mybase; 
Query OK, 1 row affected (0.00 sec) 
 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mybase             | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+ 
5 rows in set (0.00 sec)

创建带有编码的

create database mybase01 character set gbk;

mysql> create database mybase01 character set gbk; 
Query OK, 1 row affected (0.00 sec) 
 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mybase             | 
| mybase01           | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+ 
6 rows in set (0.00 sec)

查看编码：

mysql> show create database mybase01; 
+----------+------------------------------------------------------------------+ 
| Database | Create Database                                                  | 
+----------+------------------------------------------------------------------+ 
| mybase01 | CREATE DATABASE `mybase01` /*!40100 DEFAULT CHARACTER SET gbk */ | 
+----------+------------------------------------------------------------------+ 
1 row in set (0.00 sec)

查看数据库

mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mybase             | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+ 
5 rows in set (0.00 sec)

删除一个库

drop database 库名

mysql> drop database mybase; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+ 
4 rows in set (0.00 sec)

使用库 use 库名

mysql> use mybase 
Database changed

查看当前正在操作的库 select database();

mysql> select database(); 
+------------+ 
| database() | 
+------------+ 
| mybase     | 
+------------+ 
1 row in set (0.00 sec)

对数据库表的操作

创建一张表

 创建数据表的格式 
       
create table 表名( 
       列名1 数据类型 约束, 
       列名2 数据类型 约束, 
       列名3 数据类型 约束 
      );

/* 
      创建用户表,用户编号,姓名,用户的地址 
      将编号列,设置为主键约束,保证列的数据唯一性,非空性 
      primary key AUTO_INCREMENT 
      让主键列数据,实现自动增长 
*/ 
 CREATE TABLE users ( 
     uid INT PRIMARY KEY AUTO_INCREMENT , 
     uname VARCHAR(20), 
     uaddress VARCHAR(200) 
 );

mysql>  CREATE TABLE users ( 
    ->      uid INT PRIMARY KEY AUTO_INCREMENT , 
    ->      uname VARCHAR(20), 
    ->      uaddress VARCHAR(200) 
    ->  ); 
Query OK, 0 rows affected (0.02 sec)

查看数据库表 show tables;

mysql> show tables; 
+------------------+ 
| Tables_in_mybase | 
+------------------+ 
| users            | 
+------------------+ 
1 row in set (0.00 sec)

查看表的结构 desc 表名

mysql> desc users; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| uid      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| uname    | varchar(20)  | YES  |     | NULL    |                | 
| uaddress | varchar(200) | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec)

删除一张表 drop table 表名

mysql> drop table users；  
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show tables; 
Empty set (0.00 sec)

修改表

添加一列 alter table 表名 add 字段名类型(长度) [约束]

mysql> alter table users add tel int; 
Query OK, 0 rows affected (0.00 sec) 
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users ; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| uid      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| uname    | varchar(20)  | YES  |     | NULL    |                | 
| uaddress | varchar(200) | YES  |     | NULL    |                | 
| tel      | int(11)      | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+ 
4 rows in set (0.01 sec)

修改列的类型(长度、约束) alter table 表名 modify 要修改的字段名类型(长度) [约束]

mysql> alter table users modify tel varchar(50); 
Query OK, 0 rows affected (0.27 sec) 
Records: 0  Duplicates: 0  Warnings: 0

修改列的列名 alter table 表名 change 旧列名新列名数据类型约束

mysql> alter table users change tel newtel double; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users ; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| uid      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| uname    | varchar(20)  | YES  |     | NULL    |                | 
| uaddress | varchar(200) | YES  |     | NULL    |                | 
| newtel   | double       | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+ 
4 rows in set (0.00 sec)

删除表的列 alter table 表名 drop 列名

mysql> alter table users drop newtel; 
Query OK, 0 rows affected (0.02 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> desc users; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| uid      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| uname    | varchar(20)  | YES  |     | NULL    |                | 
| uaddress | varchar(200) | YES  |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec)

修改表名 rename table 表名 to 新表名

mysql> rename table users to newusers; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> show tables; 
+------------------+ 
| Tables_in_mybase | 
+------------------+ 
| newusers         | 
+------------------+ 
1 row in set (0.00 sec)

查看表的字符集编码 show create table newusers;

mysql> show create table newusers; 
+----------+-------------------------------------------------------------- 
-------------------------------------------------------------------------- 
-------------------------------------------------------+ 
| Table    | Create Table 
 
                                                       | 
+----------+-------------------------------------------------------------- 
-------------------------------------------------------------------------- 
-------------------------------------------------------+ 
| newusers | CREATE TABLE `newusers` ( 
  `uid` int(11) NOT NULL AUTO_INCREMENT, 
  `uname` varchar(20) DEFAULT NULL, 
  `uaddress` varchar(200) DEFAULT NULL, 
  PRIMARY KEY (`uid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+----------+-------------------------------------------------------------- 
-------------------------------------------------------------------------- 
-------------------------------------------------------+ 
1 row in set (0.00 sec)

修改表的字符集 alter table 表名 character set 编码

mysql> alter table newusers character set gbk; 
Query OK, 0 rows affected (0.03 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> show create table newusers; 
+----------+------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------+ 
| Table    | Create Table 
 
 
            | 
+----------+------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------+ 
| newusers | CREATE TABLE `newusers` ( 
  `uid` int(11) NOT NULL AUTO_INCREMENT, 
  `uname` varchar(20) CHARACTER SET utf8 DEFAULT NULL, 
  `uaddress` varchar(200) CHARACTER SET utf8 DEFAULT NULL, 
  PRIMARY KEY (`uid`) 
) ENGINE=InnoDB DEFAULT CHARSET=gbk | 
+----------+------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------------------------------------------------------------------------- 
------------+ 
1 row in set (0.00 sec)

对数据库表记录进行操作(修改)

插入记录

insert into 表名(列名1,列名2,列名3……) values(值1,值2,值3……) 
insert into 表名 values(值1,值2,值3……)
insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3)

先创建product表，然后添加数据。

CREATE TABLE product( 
   -- 主键列,自动增长 
   id INT PRIMARY KEY AUTO_INCREMENT, 
   -- 商品名字,可变字符,非空 
   pname VARCHAR(100) NOT NULL, 
   -- 商品的价格,double 
   price DOUBLE 
);

mysql> INSERT INTO product (id,pname,price) VALUES (1,'笔记本',5555.99); 
ERROR 1366 (HY000): Incorrect string value: '\xB1\xCA\xBC\xC7\xB1\xBE' for column 'pname' at row 1 
mysql> INSERT INTO product (id,pname,price) VALUES (2,'智能手机',9999); 
ERROR 1366 (HY000): Incorrect string value: '\xD6\xC7\xC4\xDC\xCA\xD6...' for column 'pname' at row 1 
mysql> INSERT INTO product (id,pname,price) VALUES (1,'computer',5555.99); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> INSERT INTO product (id,pname,price) VALUES (1,'phone',5555.99); 
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 
mysql> INSERT INTO product (id,pname,price) VALUES (2,'phone',5555.99); 
Query OK, 1 row affected (0.00 sec)

插入数据中文乱码问题解决办法

乱码原因：客户端和服务器之间传递字符的编码规则不一样。

方式一：直接修改数据库安装目录里面的my.ini文件的第57行【不建议】

方式二： set names gbk;

mysql> set names gbk; 
Query OK, 0 rows affected (0.00 sec)

再次插入中文，可以看到，不报错了。

mysql> INSERT INTO product (id,pname,price) VALUES (3,'笔记本',5555.99); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> INSERT INTO product (id,pname,price) VALUES (4,'智能手机',9999); 
Query OK, 1 row affected (0.00 sec)

添加数据格式,不考虑主键 insert into 表名 (列名) values (值)

mysql> INSERT INTO product (pname,price) VALUES('洗衣机',800); 
Query OK, 1 row affected (0.00 sec)

添加数据格式,所有值全给出 insert into 表名 values (全列值)

mysql> INSERT INTO product VALUES (6,'微波炉',300.25); 
Query OK, 1 row affected (0.00 sec)

添加数据格式,批量写入 insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3)

mysql> INSERT INTO product (pname,price) VALUES 
    -> ('智能机器人',25999.22), 
    -> ('彩色电视',1250.36), 
    -> ('沙发',5899.02); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0

修改表记录

先看看表中的所有数据：

mysql> select * from product; 
+----+------------+----------+ 
| id | pname      | price    | 
+----+------------+----------+ 
|  1 | computer   |  5555.99 | 
|  2 | phone      |  5555.99 | 
|  3 | 笔记本         |  5555.99 | 
|  4 | 智能手机          |     9999 | 
|  5 | 洗衣机         |      800 | 
|  6 | 微波炉         |   300.25 | 
|  7 | 智能机器人          | 25999.22 | 
|  8 | 彩色电视          |  1250.36 | 
|  9 | 沙发          |  5899.02 | 
+----+------------+----------+ 
9 rows in set (0.00 sec)

带条件的 update 表名 set字段名=值, 字段名=值, 字段名=值…… where 条件

-- 修改智能手机,价格上调到15999 
mysql> UPDATE product SET price = 15999 WHERE id=2; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

-- 修改彩色电视,名字改为黑白电视机,价格,100 
mysql> UPDATE product SET pname='黑白电视机', price=100 WHERE id = 6; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

 修改条件的写法 
    id=6 
    id<>6 
    id<=6   
    与或非  && || ! 
    && and 
    || or  
    ! not 
     
    id in (1,3,4,5,6) 包含

-- 将笔记本的价格,和空调的价格,全部修改为2000 
UPDATE product SET price = 2000 WHERE id = 1 OR id = 7;

不带条件的，它会将该列的所有记录都更改 update 表名 set 字段名=值, 字段名=值, 字段名=值……

mysql> update product set price=999.999; 
Query OK, 9 rows affected (0.01 sec) 
Rows matched: 9  Changed: 9  Warnings: 0

删除表记录

带条件的 delete from 表名 where 条件

不带条件的，会删除该表的所有记录 delete from 表名;

-- 删除热水器 
DELETE FROM product WHERE id=8;

delete与truncate的区别

delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。
truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表。它删除的数据无法找回。

Delete操作演示：

mysql> start transaction; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> delete from product; 
Query OK, 9 rows affected (0.00 sec) 
 
mysql> select * from product; 
Empty set (0.00 sec) 
 
mysql> rollback; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from product; 
+----+------------+---------+ 
| id | pname      | price   | 
+----+------------+---------+ 
|  1 | computer   | 999.999 | 
|  2 | phone      | 999.999 | 
|  3 | 笔记本         | 999.999 | 
|  4 | 智能手机          | 999.999 | 
|  5 | 洗衣机         | 999.999 | 
|  6 | 黑白电视机        | 999.999 | 
|  7 | 智能机器人          | 999.999 | 
|  8 | 彩色电视          | 999.999 | 
|  9 | 沙发          | 999.999 | 
+----+------------+---------+ 
9 rows in set (0.00 sec)

Truncate操作演示:

mysql> start transaction; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> truncate table product; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from product; 
Empty set (0.00 sec) 
 
mysql> rollback; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from product; 
Empty set (0.00 sec)

注意：delete删除，uid不会重置！而使用truncate操作，uid会重置[因为它删除了表结构，然后再创建一张一模一样的表，所以再次插入数据的数据的时候从1开始。

查询操作

先创建表并添加数据

CREATE TABLE zhangwu ( 
  id INT PRIMARY KEY AUTO_INCREMENT, -- 账务ID 
  zname VARCHAR(200), -- 账务名称 
  zmoney DOUBLE -- 金额 
); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (1,'吃饭支出',247); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (2,'工资收入',12345); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (3,'服装支出',1000); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (4,'吃饭支出',325); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (5,'股票收入',8000); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (6,'打麻将支出',8000); 
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (7,NULL,5000);

语法：

select [distinct] *| 列名，列名 from 表名 [where条件]

/* 
   查询指定列的数据 
   格式:select 列名1,列名2 from 表名 
*/ 
SELECT zname,zmoney FROM zhangwu; 
 
/* 
  查询所有列的数据 
  格式:select * from 表名 
*/ 
SELECT * FROM zhangwu; 
 
/* 
   查询去掉重复记录 
   DISTINCT 关键字 跟随列名 
*/ 
SELECT  DISTINCT zname FROM zhangwu 
 
/* 
  查询重新命名列 
  as 关键字 
*/ 
SELECT zname AS 'name' FROM zhangwu  
 
/* 
  查询数据中,直接进行数学计算 
  列对数字进行计算 
*/ 
SELECT zname,zmoney+1000 AS'sum' FROM zhangwu 
 
-- 查询所有的吃饭支出 
SELECT * FROM zhangwu WHERE zname='吃饭支出' 
 
-- 查询金额大于1000 
SELECT * FROM zhangwu WHERE zmoney>1000 
 
-- 查询金额在2000到5000之间  
SELECT * FROM zhangwu WHERE zmoney >= 2000 AND zmoney <= 5000 
-- 改造成between and 方式 
SELECT * FROM zhangwu WHERE zmoney BETWEEN 2000 AND 5000 
 
-- 查询金额是 1000,3500,5000 其中一个 
SELECT * FROM zhangwu WHERE zmoney = 1000 OR zmoney=3500 OR zmoney=5000 
-- 改造成in方式 
SELECT * FROM zhangwu WHERE zmoney  IN (1000,3500,5000) 
 
-- like 模糊查询 配合通配符 
-- 查询所有的支出 
SELECT * FROM zhangwu WHERE zname LIKE '%支出%' 
 
-- 查询账务名字,五个字符的 
SELECT * FROM zhangwu WHERE zname LIKE'_____' 
 
-- 查询账务名,不为空的 
SELECT * FROM zhangwu WHERE zname IS NOT NULL 
 
SELECT * FROM zhangwu WHERE   NOT ( zname IS NULL)

排序

/* 
  查询,对结果集进行排序 
  升序,降序,对指定列排序 
  order by 列名 [desc][asc] 
  desc 降序 
  asc  升序排列,可以不写 
*/ 
-- 查询账务表,价格进行升序 
SELECT * FROM zhangwu ORDER BY zmoney ASC 
 
-- 查询账务表,价格进行降序 
SELECT * FROM zhangwu ORDER BY zmoney DESC 
 
-- 查询账务表,查询所有的支出,对金额降序排列 
-- 先过滤条件 where 查询的结果再排序 
SELECT * FROM zhangwu WHERE zname LIKE'%支出%' ORDER BY zmoney DESC

聚合函数

/* 
   使用聚合函数查询计算 
*/ 
 
-- count 求和,对表中的数据的个数求和  count(列名) 
-- 查询统计账务表中,一共有多少条数据 
SELECT COUNT(*)AS'count' FROM zhangwu 
 
-- sum求和,对一列中数据进行求和计算 sum(列名) 
-- 对账务表查询,对所有的金额求和计算 
SELECT SUM(zmoney) FROM zhangwu 
-- 求和,统计所有支出的总金额 
SELECT SUM(zname) FROM zhangwu WHERE zname LIKE'%收入%' 
 
INSERT INTO zhangwu (zname) VALUES ('彩票收入') 
 
-- max 函数,对某列数据,获取最大值 
SELECT MAX(zmoney) FROM zhangwu 
 
-- avg 函数,计算一个列所有数据的平均数 
SELECT AVG(zmoney)FROM zhangwu

分组操作

/* 
    查询所有的数据 
    吃饭支出 共计多少 
    工资收入 共计多少 
    服装支出 共计多少 
    股票收入 共计多少 
    打麻将支出 共计多少钱 
     
    分组查询:  group by 被分组的列名 
    必须跟随聚合函数 
    select 查询的时候,被分组的列,要出现在select 选择列的后面 
*/ 
  SELECT SUM(zmoney),zname FROM zhangwu GROUP BY zname 
   
-- 对zname内容进行分组查询求和,但是只要支出 
SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%支出%' 
GROUP BY zname 
ORDER BY getsum DESC 
 
-- 对zname内容进行分组查询求和,但是只要支出, 显示金额大于5000 
-- 结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having 
SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%支出%' 
GROUP BY zname HAVING getsum>5000

查询总结

select 一般在后面的内容都是要查询的字段
from 要查询到表
where
group by
having 分组后带有条件只能使用having
order by 它必须放到最后面

更多请参考：《MySQL 必知必会》读书总结、《MySQL必知必会》读书笔记

IT虾米网 mysql

微信公众号号：IT虾米（左侧二维码扫一扫）欢迎添加！

Q : 823053937

热贴推荐

Java基础学习笔记二十五 MySQL

MySQL

在dos中操作mysql

对数据库的操作

对数据库表的操作

修改表

对数据库表记录进行操作(修改)

插入数据中文乱码问题解决办法

修改表记录

删除表记录

注意：delete删除，uid不会重置！而使用truncate操作，uid会重置[因为它删除了表结构，然后再创建一张一模一样的表，所以再次插入数据的数据的时候从1开始。

查询操作

排序

聚合函数

分组操作

查询总结

Q : 823053937

热贴推荐

Java基础学习笔记二十五 MySQL

MySQL

在dos中操作mysql

对数据库的操作

对数据库表的操作

修改表

对数据库表记录进行操作(修改)

插入数据中文乱码问题解决办法

修改表记录

删除表记录

注意：delete删除，uid不会重置！而使用truncate操作，uid会重置[因为它删除了表结构，然后再创建一张一模一样的表，所以再次插入数据的数据的时候从1开始。

查询操作

排序

聚合函数

分组操作

查询总结

mysql之如何使用 Rails 优化此查询

mysql之有哪些方法可以屏蔽 mysqldump

mysql之用windows安装mysql2出错

mysql之Rails 获取插入行的 id(不是最后插入的)

mysql之获取 rails 中每个不同记录的所有列

mysql之多对多与一对多

mysql之MySQL 和 Rails 中的外键建议

mysql2 gem 安装失败

Mysql2::错误: '6' 中的未知列 'where clause':SELECT

mysql之如何使用 `exec_insert`方法

mysql之Mac Os rake 数据库 :create through error with qt and mysql dependence

mysql之如何将 sql 时间戳值插入到 Rails 中的日期时间类型