Java基础学习笔记二十五 MySQL
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 它必须放到最后面
