mysql数据库基础（一）

查看 数据库用户，可操作主机，加密结果，加密机制
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;