Java基础学习笔记二十五 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 |

                  

	 	
                    

                        

                            

                                
< 上一篇
InnoDB--------独立表空间平滑迁移
                            
                            

                                 
下一篇 >
mysql 备份