mysql常见操作之-增、删、改、查

MySQL安装

MySQL启动

MySQL停止

mysqladmin -uroot -S/var/lib/mysql/sock/mysql_3308.sock shutdown -p 

1、Mysql库常见操作

1.1创建数据库sqoopDB

create dadabase sqoopDB

• 创建bear用户,密码为'123456'

create user 'bear' identified by '123456'

• 授权用户bear拥有数据库sqoopDB的所有权限

grant all privileges on sqoopDB.* to 'bear'@'%' identified by '123456'
flush privileges

1.2删除数据库

drop database sqoopDB

2、Mysql表常见操作

2.1、创建表test

create table `test`
(
`id` int(10) not null auto_increment ,
`name` varchar(64),
PRIMARY key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

2.2、删除表

drop table test

2.3、插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如："value"。

-- 单条插入
insert into  test(name, age) values("xr",100);

--多条插入
insert into test(name,age) values
("xr", 100),
("cc",'100')

2.4、查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

select * from test;
select * from  test where ......

2.5、修改数据

UPDATE table_name SET field1=new-value1, field2=new-value2 
[WHERE Clause]

--单字段更新
update  test set age=98;
 
--多字段同时更新
update  test set age=98,  sex=1;

2.6、删除数据

DELETE FROM table_name [WHERE Clause]

-- 删除指定数据
delete from test where name='xr';

--删除所有数据
delete from test;

--清空表
truncate table test;