MySQL 数据库的基本操作

1、mysql数据库的操作

1.1 创建数据库

直接在命令行创建

mysql -uroot  -p'password'   -e "create database db2 default charset 'utf8' "

进入数据库创建库

mysql> create  database db2  default   charset 'utf8';

1.2 查看数据库

查看所有数据库  show  databases;

查看创建库的信息  show create database  database_name;

查看当前库  show  database();

切换数据库  use  database_name;

删除数据库  drop  database  database_name;

2、数据库表的操作

创建表   create  table  table_name;

查看表结构  desc  table_name;

修改表   alter  table

复制表   create  table

删除表  drop  table

单表查询

创建一个表：

DROP TABLE  IF EXISTS employee;
CREATE TABLE employee (
    `id` int PRIMARY KEY auto_increment NOT NULL COMMENT "雇员编号",
    `name` VARCHAR(30) NOT NULL COMMENT "雇员姓名",
    `sex` enum('male','female') NOT NULL DEFAULT 'female',
    `hire_date` date NOT NULL,
    `post` VARCHAR(50),
    `job_description` VARCHAR(100),
    `salary` DOUBLE (15,2),
    `office` int,
    `dep_id` int
    );

插入数据：

insert into employee(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
    ('jack','male','20180202','instructor','teach',5000,501,100),
    ('tom','male','20180203','instructor','teach',5500,501,100),
    ('robin','male','20180202','instructor','teach',8000,501,100),
    ('alice','female','20180202','instructor','teach',7200,501,100),
    ('tianyun','male','20180202','hr','hrcc',600,502,101),
    ('harry','male','20180202','hr',NULL,6000,502,101),
    ('emma','female','20180206','sale','salecc',20000,503,102),
    ('christine','female','20180205','sale','salecc',2200,503,102),
  ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
  ('gougou','male','20180205','sale','',2200,503,102);

查看这个表中有多少条数据

SELECT COUNT(*) FROM employee    &    SELECT COUNT(1) FROM employee 

条件查询

select  name,salary from employee

避免重复，去重

select  distinct  office  from  employee

计算

select  salary *12  as  salary   from   employee

格式自定义

select concat(name,'-',salary)  from employee

查询一个月五千的人

select  name,salary  from employee  where salary=5000

查询一个月八千到两万的人

select  name,salary  from employee  where  salary >= 8000 and salary <=2000

select  name,salary  from employee where salary between 8000 and 2000

查询工资5000和10000的

select name,salary  from  employee where salary =5000 or salart = 1000

select  name,salary  from employee where salary in(5000,10000)

select  name,salary  from employee where salary not  in(5000,10000)

查询2017年入职的员工

select  name from employee where left(hire_date,4) ="2017"

select name from employee where hire_date like '2017%'

select name from  employee where hire_date > 2016-12-31 and hire_dare < 2018-1-1

select name from employee where hrie_date regexp(2017)

查询职位位null的

SELECT `NAME` FROM employee WHERE job_description IS NULL

查询职位描述为not null的

SELECT `NAME` FROM employee WHERE job_description IS NOT NULL

SELECT `NAME` FROM employee WHERE job_description = ''

查询工资大于2000的有多少人

select   count(name)   from   zjl   where   salary > 2000

查询排序

按工资排序 select name,salary  from employee order by salary

按工资倒序  select name,salary  from employee order by salary desc

限制查询的次数   SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5;

                             SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5;

分组查询  select  dep_id,group_concat(name)  from employee group by dep_id;

模糊查询  SELECT * from employee5 WHERE salary like '%20%';