数据库操作语句
1、创建数据库:
crete database study;
create database if not exists study;
2、显示数据库:
show databases;
3、使用数据库:
use study;
4、删除数据库:
drop database study;
表的操作语句
1、显示有多少张表:
show tables;
2、查询这张表:
select *from study;
3、删除这张表:
drop tables if exists study;
4、创建表结构:
create table if not exists study(
id int primary key auto_increment,
name varchar(18),
sex varchar(2),
age int,
address varchar(200),
email varchar(100)
);
5、给表插入数据
insert into study (id,Name,sex) values (1,‘张三’,‘男’);
insert into study values (2,‘张三’,‘男’,10,‘中国’,‘xxxxxxxx@qq.com’);
数据内容操作语句
1、更新表内容设置name等于张三的id为100
UPDATE teacher set t_id=100 where t_name=“张三”
2、通过子查询批量更新字段的值,不能先select出同一表中的某些值,再update这个表(在同一语句中),所以 in里面多了一层查询。
UPDATE teacher
SET t_name = “马六”
WHERE
t_id IN ( SELECT * FROM ( SELECT t_id FROM teacher WHERE t_name IN ( “张三”, “李四” ) ) AS a )
3、通过子查询批量更新字段的值,这里是作为两张表进行批量更新
UPDATE teacher as a ,(SELECT t_id from teacher where t_id in (“01”,“02”)) as b set t_name=“哇哈哈” where a.t_id=b.t_id
4、删除数据
DELETE from teacher where t_id =“01”