mysql数据库的基本操作增删改查

利用docker安装mysql

docker run -d --name mysql -v mysql:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=true  mysql

（root密码为空）

直接命令进入：mysql

创建表

CREATE TABLE info(
id int,
name VARCHAR(40) NOT NULL, 
sex VARCHAR(4) NOT NULL,
birthday date NOT NULL,
job VARCHAR(100) NOT NULL,
PRIMARY KEY ( id )
);

 

注意这样的语句会失败，因为字段用到了系统变量“desc”

MariaDB [bastion]> create table tmp (
    -> basdb varchar(20),
    -> desc varchar(40),
    -> vip int(10),
    -> phyip int(10),
    -> name varchar(10),
    -> branch varchar(20)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'desc varchar(40),
vip int(10),
phyip int(10),
name varchar(10),
branch varchar(2' at line 3

 

 插入数据

insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1993-03-04','2016-11-10','1000','i am a developer');

insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(2,'lisi','male','1993-03-04','2016-11-10','1000','i am a developer');

insert into employee(id,name,sex,birthday,entry_date,salary,resume) values(3,'zhaowu','male','1993-03-04','2016-11-10','1000','i am a developer');

insert into employee(id,name) values(4,'houliu');

insert into employee(id,name) values(5,'xiaqi');

insert into employee(id,sex) values(6,'man');

 

插入多条记录

INSERT INTO others() VALUES
(5,'houqi',18,'运营部'),
(6,'changba',18,'财务部'), 
(7,'changjiu',30,'研发中心');

 插入记录时主键存在则更新（想要更新哪些字段就在UPDATE后添加哪些字段）

INSERT INTO others() VALUES(4,'houqi',19,'运营部') ON DUPLICATE KEY UPDATE name=VALUES(name),age=VALUES(age),department=VALUES(department);

插入时存在则忽略

INSERT IGNORE INTO others() VALUES(4,'houqi',19,'运营部');

将查询到的结果插入表中

INSERT INTO boss SELECT * FROM leaders;

更新数据

-- 很危险 很危险 很危险！记得一样增加where条件
UPDATE others SET name='zhangsan',age=38 WHERE name='zhangsansi';

忽略错误更新

UPDATE IGNORE others SET name='zhangsan',age=38 WHERE name='zhangsansi';

删除数据

-- 很危险 很危险 很危险！记得一样增加where条件
DELETE FROM others WHERE name='zhangsan';

is_delete 有时候我们并不会删除数据，为了留记录，而是将表的is_delete字段设置为1（默认0为False，1位真）

-- 很危险 很危险 很危险！记得一样增加where条件
UPDATE others SET is_delete=1 WHERE name='zhangsansi';