MySQL数据库初识

数据库语句的分类

1、DDL:数据定义语言

create：创建（数据库对象）
create table
create index
alter:修改（增删列）
alter table 修改表
drop：删除（结构）
rename：重命名表（改名）
truncate：清空（表数据）

2、DML：数据操作语言（增/删/改/查_添加/删除记录）

insert:添加
delete：删除（记录）
update:修改（记录）
select：查询操作

3、DCL：数据控制语言（控制增/删/改/查的操作）

commit：数据提交（修改永久性）
rollback：回滚/撤销
savepoint:保存点
grant：赋予（权限）
revoke：回收（权限）

数据库语句的基础操作：

登录操作

mysql -uroot (-Port) (-host) -p  //本机127.1.1.1或locallhost
输入密码mysql -uroot -P13306 -p  //登录5.7
输入密码mysql -uroot -P3306 -p  //默认登录80，-P3306 可以省略，环境变量配置80（自动获取80）
输入密码

查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建一个数据库叫dbtest1

mysql> create database dbtest1;
Query OK, 1 row affected (0.00 sec)查看创建结果：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbtest1            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

创建表employees

mysql> create table employees(id int,name varchar(15));
ERROR 1046 (3D000): No database selected
说明：创建表之前一定要指定使用哪一个表
mysql> use dbtest1;
Database changed
mysql> show tables;
Empty set (2.24 sec)创建employees的表：包含表名、表的各个元素
mysql> create table employees(id int,name varchar(15));
Query OK, 0 rows affected (0.83 sec)mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

查询表

查询employees表

mysql> select*from employees;
Empty set (0.01 sec)

给表建立数据

mysql> insert into employees values(1001,'Tom');
Query OK, 1 row affected (0.53 sec)
mysql> insert into employees values(1002,'tim');

查询表中的数据

mysql> select*from employees;
+------+------+
| id   | name |
+------+------+
| 1001 | Tom  |
| 1002 | tim  |
+------+------+
2 rows in set (0.00 sec)

注意（中文不能添加成功）

5.7不行，8.0就可以

mysql> insert into employees values(1003,'姐姐');
ERROR 1366 (HY000): Incorrect string value: '\xBD\xE3\xBD\xE3' for column 'name' at row 1
//显示错误，添加数据失败

解决不能加中文数据的问题

mysql> show create table employees;

5.7默认拉丁（直接装80）

mysql> show variables like 'character_%';字符集

字符集都是拉丁，要使中文数据正常插入，需改位UTF-8

mysql> show variables like 'collation_%';规则

在Data下找到并修改配置文件my.int

退出并重启服务并登录

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbtest1            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql> use dbtest1;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)mysql> select*from employees;
+------+------+
| id   | name |
+------+------+
| 1001 | Tom  |
| 1002 | tim  |
+------+------+
2 rows in set (0.00 sec)

删除数据库

drop database 数据库
mysql> drop database dbtest1;
Query OK, 1 row affected (0.94 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

小结：

举例：

mysql> create database dbtest1;
mysql> show create database dbtest1;    //查看创建的dbtest1数据库的语言
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| dbtest1  | CREATE DATABASE `dbtest1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> use dbtest1;
Database changed
mysql> create table employees(id int,name varchar(15));
Query OK, 0 rows affected (0.86 sec)mysql> show create table employees;
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                   |
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (`id` int(11) DEFAULT NULL,`name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> insert into employees values(1001,'小蓝');
Query OK, 1 row affected (0.58 sec)mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)mysql> select*from employees;
+------+------+
| id   | name |
+------+------+
| 1001 | 小蓝 |
+------+------+
1 row in set (0.00 sec)

查看编码的命令

show variables like 'character_%';
show variables like 'collation_%';

navicat使用

服务必须开启

出现以下的情况：

C:\Users\Administrator>mysql -uroot -P3306 -p
Enter password: ******mysql> use mysql;
Database changedmysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';（这里是重置的密码）
Query OK, 0 rows affected (0.15 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)退出80。重启服务
密码会被初始化修改了