学习笔记（三）MySQL

MySQL的SQL语句

• DDL：数据定义语言> 定义数据库/表的结构[create、drop、alter]
• DML：数据操纵语言> 操作数据[insert、update、delete]
• DCL：数据控制语言> 定义访问权限 grant 撤销权限 revoke
• DQL：数据查询语言> select 列名1，列名2，… from 表名 [where 条件]

MySQL数据库卸载

· 控制面板卸载软件
· 删除MySQL安装目录下的所有文件
· 删除MySQL数据文件：默认 C:\ProgramData\MySQL

设置数据库字符集

set character_set_database =utf8;

命令行进入MySQL：

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 104
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

//查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
注意：这些数据库不要删了，有涉及到数据库的配置信息

//创建数据库users
mysql> create database users;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| users              |
+--------------------+
5 rows in set (0.28 sec)

//查看数据库定义语句
mysql> show create database users;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| users    | CREATE DATABASE `users` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

//删除数据库
drop database users;

//切换数据库
mysql> use users
Database changed

//查看当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| users      |
+------------+
1 row in set (0.00 sec)

//在users数据库创建user表
mysql> create table user(
  id int not null AUTO_INCREMENT,
  name varchar(10) not null,
  age int
);

//查看表定义语句
mysql>  show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//查看表结构
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

//插入信息
mysql>insert into user values(null,'王五','28');
OR
insert into user  (name,age) valuse('李四','28');

//解释查询过程
mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

//更改数据库字符集
mysql> ALTER DATABASE users DEFAULT CHARACTER SET='utf8';
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(null,'王五','28');
Query OK, 1 row affected, 1 warning (0.30 sec)

mysql> insert into user (name,age) values('admin',18);
Query OK, 1 row affected (0.08 sec)

mysql> select * from user;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | 王五  |   28 |
|  2 | admin |   18 |
+----+-------+------+
2 rows in set (0.00 sec)
mysql>

字符集问题
mysql> insert into user values(null,‘王鹏’,‘24’);
Query OK, 1 row affected, 1 warning (0.30 sec)

mysql> select * from user;
±—±------±-----+
| id | name | age |
±—±------±-----+
| 1 | ??? | 24 |
±—±------±-----+
1 rows in set (0.00 sec)

• 由于插入中文导致乱码
  //更改数据库字符集
  mysql> ALTER DATABASE users DEFAULT CHARACTER SET=‘utf8’;

MySQL数据类型

Java数据类型	MySQL数据类型
int	int
char/string	char [固定长度]/varchar [可变长度]
double	double
float	float
boolean	boolean
date	date [YYYY-MM-DD]
	time [ hh:mm:ss]
	datetime [YYYY-MM-DD hh:mm:ss] 默认值NULL
	timestamp [YYYY-MM-DD hh:mm:ss] 默认当前时间
	text 存放文本
	blob 存放二进制

---