###数据库
####数据库相关SQL语句
show databases;
create database db1;
create database db2 character set utf8;
create database db3 character set gbk;
show create database db1;
drop database db4;
use db1;
###数据库相关练习:
create database mydb1 character set utf8;
create database mydb2 character set gbk;
show databases;
show create database mydb1;
show create database mydb2;
use mydb1;
use mydb2;
drop database mydb1;
drop database mydb2;
###表相关的SQL
create table student(name varchar(10),age int);
create table person(name varchar(10),gender varchar(5))charset=gbk;
show create table person;
desc student;
drop table student;
rename table person to t_person;
alter table t_person add salary int;
alter table t_person add id int first;
alter table t_person add age int after name;
alter table t_person drop salary;
alter table t_person change age salary int;
###表相关SQL语句回顾
create table t1(name varchar(10),age int)charset=utf8;
show tables;
show create table t1;
desc t1
drop table t1;
rename table t1 to t2;
alter table t1 add salary int first/ after xxx;
alter table t1 drop salary;
alter table t1 change
原名 新名 新类型;
####表相关练习题: create database mydb1 character set utf8;
use mydb1;
create table emp(name varchar(10)) charset=utf8;
alter table emp add age int;
alter table emp add id int first;
alter table emp add gender varchar(5) after name;
alter table emp change gender sal int;
alter table emp drop age;
rename table emp to t_emp;
drop table t_emp;
drop database mydb1;
###数据相关SQL
create database mydb2 character set utf8;
use mydb2;
create table person(name varchar(10),age int)charset=utf8;
insert into person values('Tom',18);
insert into person(name)values('Jerry');
insert into person(name)values('zhangsan'),('lisi');
insert into person values('刘德华',30);
如果执行以上代码出现错误提示,提示里面包含16进制的错误信息 执行以下SQL
set names gbk;
select name from person;
查询person表中年龄大于20的名字和年龄
select name,age from person where age>20;
查询person表中所有数据的所有字段信息
select * from person;
update person set age=8 where name='Tom';
update person set age=10 where age is null;
delete from person where name='Tom';
2. 删除年龄小于20岁的
delete from person where age<20;
3. 删除所有数据
delete from person;
###增删改查回顾:
create table t1(name varchar(10),age int(10) zerofill);
insert into t1 values('Tom',18);
select * from t1;
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values('2020-1-18',null,null,null);
insert into t_date values(null,'17:35:18','2020-3-17 12:30:23',null);