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

利用docker安装mysql

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

（root密码为空）

直接命令进入：mysql

创建表

?

1 2 3 4 5 6 7 8

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”

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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

 

 插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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';