查看 数据库用户，可操作主机，加密结果，加密机制 select user,host,authentication_string,plugin from mysql.user; 数据库登陆 mysql -uroot -proot -h192.168.0.102 查看mysql版本 select version(); 查看有哪些数据库 select database(); 查看时间 select user(),current_user(); select curtime(),current_time(); select curdate(),current_date(); select now(),sysdate(),current_timestamp(); 删除数据库 drop database if exists ad; 创建数据库 create database db default charset utf8; 创建表 create table stu( id int unsigned auto_increment primary key , sname varchar(5) not null, age tinyint(2) unsigned zerofill, birth date )engine=innodb default character set utf8 auto_increment=111111; 显示上一段代码执行之后的警告内容 show warnings; 删除表 drop table stu; 表中插入数据 insert into stu(sname,age,birth) value(‘李一’ ,8,‘2020-2-5’),(‘李二’ ,7,‘2020-3-5’),(‘李三’,12,‘2020-4-5’); 查看表中所有数据 select * from stu; 创建表t create table t(db1 json); insert into t values (’{“id”:“001”,“name”:“李一”}’); select * from t; 创建数据库 create database my; 使用数据库 use db; 创建teacher表 create table teacher( tid tinyint(3) zerofill unsigned auto_increment, tname varchar(10) not null primary key(tid) )engine=innodb charset=utf8 auto_increment=1; 插入数据 insert into teacher(tname) values(‘李老师’),(‘张老师’),(‘周老师’); 查看表中所有数据 select * from teacher; 创建student表 create table student( sid tinyint(3) zerofill unsigned auto_increment, sname varchar(10) not null, age tinyint not null, sex enum(‘男’,‘女’) default ‘男’, favorite set(‘java’,‘basketball’,‘music’,‘piano’,‘read’), money decimal(2,1) , stid tinyint unsigned, primary key(sid), 创建外键方法一 foreign key(stid) references teacher(tid) 创建外键方法二 /*constraint fk foreign key(sid) references teacher(tid) on delete cascade */ )engine=innodb charset=utf8 auto_increment=1; – 建立外键方法三 删除时设置为null 更新数据时级联更新 alter table student add foreign key(stid) references tecaher(tid) on delete set null on update cascade;
insert into student(sname,age,sex,favorite,money,stid) values(‘李一’,12,‘男’,‘java,piano,music’,‘9.9’,2); insert into student(sname,age,sex,favorite,money,stid) values(‘李二’,8,‘女’,‘java,basketball,music’,‘8.1’,1); insert into student(sname,age,sex,favorite,money,stid) values(‘李三’,17,‘男’,‘java,basketball,music’,‘7.5’,2); insert into student(sname,age,sex,favorite,money,stid) values(‘李四’,9,‘女’,‘java,read,music’,‘6.8’,3); insert into student(sname,age,sex,favorite,money,stid) values(‘李五’,15,‘男’,‘java,piano,read’,‘7.7’,3); insert into student(sname,age,sex,favorite,money,stid) values(‘李六’,14,‘女’,‘java,basketball,music’,‘9.2’,1); select * from student; drop table student; 条件删除数据库中记录 delete from teacher where tid=1;
显示当前数据库中所有表 show tables;
