数据库二 DDL&DML

Navicat连接mysql报错：1251 client does not support ..

解决：cmd连接mysql，查看用户信息 select host,user,plugin,authentication_string from mysql.user;

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| localhost | mysql.session    | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| localhost | mysql.sys        | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| localhost | root             | caching_sha2_password | $A$005$Wy<("jc tMF)ZlolZYMvHv0TgHlOODTk1nR8pp6viwKt9O1sFAkKrN8 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.04 sec)

host是限制的连接ip，%为不限制连接，localhost表示本机使用

plugin非mysql_native_password 则需要修改密码，如图的root用户需要修改密码

执行：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword'; #更新一下用户的密码 root用户密码为newpassword

即可navicat连接成功。

怀疑是root用户初始设置的密码问题，太短或者不安全，换了个复杂点的密码就修改成功。

 

DDL

对表操作

create 、 alte 、 drop

数据库

          create database student；

          create database if not exists student；

          create database if not exists TZDB default charset utf8;

          drop database student；

表格

        create table if not exists 表名(

                                                   属性1 类型1 约束条件，

                                                  属性2 类型2 约束条件2，

                                                    ......

                                                    )

create table if not exists student(i_no int, s_name char(20) );

drop table student;

查看表结构：

desc student；

show columns from student;

修改表结构：

alter table student add s_birchday date;

alter table student modify column s_name varchar(20);

alter table student drop column s_birchday;

 

DML

对数据操作

insert update delete

insert into student(i_no, s_name) values(1,"zhangsan");

insert into student values(2,"李明","1998-01-10");

insert into student values (3,"王大","2000-12-21"),(4,"张华","1999-03-25");

 

update student set s_name='小花', s_birchday="2001-11-23";

update student set s_name="李磊",s_birchday="1998-10-21" where i_no=1;

 

delete from student where i_no=4;