欢迎来到5分享! 微信公众号   新浪微博

5分享

文章类别:
当前位置: 主页 > 文章 > 数据库 >

Mysql数据库的常用操作整理

时间:2018-08-29 13:55来源:原创 作者:5分享 点击:
常见的数据库有:MySQL、SQLserver、oracle等,这里主要介绍一下MySQL数据库的基本操作。

一、MySQL字段类型


  常用的有:数值(int, float)、字符串(varchar, text,char)、日期(date, datetime)


二、MySQL约束


  主键:primary key


  外键:foreign key


  默认值:default


  唯一:unique


  非空:not null


三、物理删除和逻辑删除


  在使用数据库的时候,一定会遇到删除数据库中的内容,这个时候一定要小心操作,不然就会被老板直接开除,网络上不是有个很火的命令操作叫"程序员如何执行一条命令让老板开除",所以操作数据库时一定要小心谨慎。


  说一下什么是物理删除和逻辑删除,物理删除就是把一条数据从数据库中永远删除,就是再也找不到也不能恢复;逻辑删除就是给你设计的表再增加一列(比如:isDelete,设默认值为0),当你想删除数据时将这个字段的值设为1,当你再操作数据时把isDelete值为1的筛选掉就可以了,这样数据也不会丢失。对于重要的数据一定要设置这个字段,对于不重要的数据自己想怎么操作就怎么操作,大不了全部删除再重新建数据库。


四、数据库基本操作(增删改查)


  1、创建数据库


    create database [if not exists] db_name [character set utf8];


    注意:[if not exists]:最好加上,这样可以避免麻烦,每操作一次数据库就多一次风险


         [character set utf8]就是设置编码格式,也可以不指定


  2、查看数据


    show databases;


  3、查看数据库创建方式


    show create database db_name;


  4、修改数据库


    alter database db_name [character set xxx];


  5、删除数据库


    drop database [if exists] db_name;


    注意: [if exists]:如果你不确定数据库是否存在最好加上,存在就删除,不存在也不会报错


  6、使用数据库


    use db_name;


  7、查看当前使用数据库


    select database();


五、数据库管理


  1、通过grant命令,给test数据库,添加用户***,密码123456


    grant select,insert,update,delete,create,drop on test123 to '***'@'localhost' identified by '123456';


  2、修改用户密码


    grant select,insert,update,delete,create,drop on test123 to '***'@'localhost' identified by 'asdfasdf';


  3、显示用户


    select user from mysql.user;


  4、删除用户


    drop user '***'@'localhost';


六、数据表的基本操作(增删改查)


  1、查看数据表


    show tables;


  2、创建数据表


    create table student(


      id int primary key auto_increment,


      name varchar(20) not null,


      age int not null,


      gender tinyint(1) default 1,


       birthday date,


      hobby varchar(20)


     );


  3、查看数据表结构


    desc student;


  4、查看创建数据表语句


    show create table student;


  5、增加数据(列,字段)


    alter table student add address varchar(30);


  6、增加多个数据


    alter table student add address varchar(30),


             add age int not null,


             add height int not null;


  7、修改一列列名


    alter table student change address addr varchar(20);


  8、修改一列类型


    alter table student modify age tinyint default 20;


  9、删除一列


    alter table student drop height;


  10、修改表名


    rename table student to stu;


  11、修改表所用的字符集


    alter table student character set utf8;


  12、删除表


    drop table student;


  create table users(id int not null,


            name varchar(10),


            age int,


             height int


  );


  13、添加主键


    alter table users add primary key (id);


  14、删除主键


    alter table users drop primary key;


  15、添加唯一索引


    alter table users add unique (name);


  16、添加唯一索引设置索引名


    alter table users add unique key user_name(name);


  17、添加联合索引


    alter table users add unique index name_age(name,age);


  18、删除索引


    alter table users drop index name;


   create table student( id int primary key auto_increment,


             name varchar(20),


             birthday varchar(20),


             age int


  );


  19、插入一条数据


    insert into student(name,birthday,age) values('学生1','2001-1-1',11);


  20、插入多条数据


    insert into student(name,birthday,age) values('学生2','2001-1-2',11),


                        ('学生3','2001-1-3',11),


                        ('学生4','2001-1-4',11);


  21、修改数据


    update student set birthday='2001-1-10' where id=1;


  22、数据表中的数据可以直接运算


    update student set age = age + 5;


  23、删除数据


    delete from student where id = 1;


    注意:删除数据时一定要加条件限制,不然整张数据表都被删除了


  24、删除表中的所有数据


    delete from student;


七、查询数据


  create table grade( id int primary key auto_increment,


             name varchar(20),


           js double,


           java double,


           python double );


  insert into grade (name,js,java,python) values ('Tom',68,89,87),('Jim',70,91,92),('Jake',71,73,74),('Mike',80,84,85),('Jame',85,88,83);


  1、select查询


    查询所有数据: select * from grade;


    查询姓名和js成绩: select name,js from grade;


    过滤表中重复数据: select distinct js from grade;


    给所有js成绩+5,并使用as别名: select name,js+5 as 'js成绩' from grade;


  2、where过滤查询


    查询姓名XX的学生信息: select * from grade where name='Tom';


    查询总分大于250分的所有学生: select name,js+java+python as '总成绩' from grade where js+java+python>250;


    查询js分数和java分数大于90的学生: select * from grade where js>90 and java>90;


    查询java分数80~90的学生: select * from grade where java between 80 and 90;


    查询java分数在80或90的学生: select * from grade where java in (80,90);


    /* 模糊查询,%表示多个字符,_表示一个字符 */


    查询姓名j开头的学生: select * from grade where name like 'j%';


八、order by 排序


  /* asc:升序,默认值 desc:降序 */


  根据js分数升序排序: select * from grade order by js;


  更加总分数从高到低排序:select name,(ifnull(js,0)+ifnull(java,0)+ifnull(python,0)) as '总分数' from grade order by '总分数' desc;


九、group by分组查询


  create table product_tab( id int primary key auto_increment,


               product_name varchar(20),


               price float(6,2),


               product_date date,


               class varchar(20) );


  insert into product_tab (product_name,price,product_date,class) values ('苹果',10,'20180812','水果'),


                                  ('香蕉',20,'20180826','水果'),


                                  ("水壶",120,'20170612',"电器"),


                                  ("被罩",70,'20170612',"家具"),


                                  ("音响",420,'20171012',"电器"),


                                  ("电视",2000,'20170912',"电器"),


                                  ("床单",55,'20171112',"家具"),


                                  ("草莓",34,'20170512',"水果");


  按位置分组: select * from product_tab group by 5;


  按产品类别分类并显示平均价格: select class,avg(price) from product_tab group by class;


  按产品分类显示每一种商品价格总和超过200的商品: select class,sum(price) from product_tab group by class having sum(price)>200;


  分组显示所有产品:group_concat select id,group_concat(product_name) from product_tab group by class;


十、where和having之间的区别


  首先,明确一点就是能使用where的地方都能使用having;where只能用于分组之前数据的筛选;having只能用于分组之后数据的筛选,


而且having中可以使用聚合函数。


十一、聚合函数


  COUNT(列名):统计行的个数


    统计学生个数: select count(id) from grade;


  SUM(列名):统计总量


    统计所有js总成绩: select sum(js) as 'js总成绩' from grade;


  AVG(列名):平均数


     统计所有js平均分: select avg(js) as 'js平均分' from grade;


  MAX,MIN (最高,最低)


    select max(js) as 'js最高分' from grade;


    select min(js) as 'js最低分' from grade;


十二、SQL语句执行顺序


  from----where----select----group by----having----order by


十三、limit和正则表达式


  limit


    查询前3条数据: select * from grade limit 3;


    跳过1条,查询3条数据: select * from grade limit 1,3;


  正则表达式


    查询j开头的学生: select * from grade where name regexp '^j';


    查询名字中m出现2次的学生: select * from grade where name regexp 'm{2}';


十四、多表操作


  1、外键约束


    /* 一个班级对应多个学生,一个学生只能对应一个班级 */


    主表:创建班级表 create table class( id int primary key auto_increment, name varchar(20), stu_nums int );


    子表:创建学生表 create table student( id int primary key auto_increment, name varchar(20), class_id int, foreign key(class_id) references class(id) );


    注意:作为外键一定要和关联主键的数据类型保持一致


    插入数据: insert into class (name,stu_nums) values ('班级一',10),('班级二',12),('班级三',13),('班级四',14),('班级五',15);


             insert into student (name,class_id) values ('Tom',1),('Jim',1),('Jake',2),('Mike',3),('Jane',4);


    增加外键: alter table student add constraint student_fk_class foreign key(class_id) references class(id);


    删除外键: alter table student drop foreign key student_ibfk_1;


  2、INNODB支持的on语句


    外键约束对子表的含义:如果在父表中找不到候选键,则不允许在子表进行insert/update


    外键约束对父表的含义:在父表上进行update/delete以更新或删除在子表中的一条或多条对应数据,


    父表的行为取决于:在定义子表的外键指定的on update/on delete语句


    ON DELETE CASCADE 级联删除:父表记录被删除,子表对应的记录自动被删除


      foreign key(class_id) references class(id) on delete cascade;


    ON DELETE SET NULL 置空:父表update/delete记录时,子表设为null


      foreign key(class_id) references class(id) on set null;


    RESTRICT:拒绝对父表进行删除操作


    NO ACTION:在mysql中同RESTRICT,如果子表中有匹配的记录,则不允许对父表对应候选键


  3、多表查询


    /* 创建2张表:员工表和部门表 并插入相关数据 */


    create table employee( emp_id int primary key auto_increment, emp_name varchar(20), age int, dept_id int );


    create table department( dept_id int primary key auto_increment, dept_name varchar(100) );


    insert into employee(emp_name,age,dept_id) values ('A',19,200), ('B',26,201), ('C',30,201), ('D',24,202), ('E',20,200), ('F',38,204);


    insert into department values (200,'人事部'), (201,'技术部'), (202,'销售部'), (203,'财政部');


    笛卡尔积查询:查询结果是m*n


      select * from employee,department;


    内连接:查询两张表中都有的关联数据


      select * from employee,department where employee.dept_id=department.dept_ id;


    外连接:


      左外连接:在内连接的基础上增加左边有右边没有的结果


        select * from employee left join department on employee.dept_id=departmen t.dept_id;


      右外连接:在内连接的基础上增加右边有左边没有的结果


        select * from employee right join department on employee.dept_id=departme nt.dept_id;


  4、多条件查询


    查询员工大于25岁的信息:


      select employee.emp_name,department.dept_name


       -> from employee,department


       -> where employee.dept_id=department.dept_id and age>25;


    以内连接方式查询employee和department表,并以age字段升序显示:


      select employee.emp_id,employee.emp_name,employee.age,department.dept_name


      -> from employee,department


      -> where employee.dept_id=department.dept_id


      -> order by age asc;


    子查询:查询employee表,dept_id在department表中的所有信息


      select * from employee where dept_id in (select dept_id from department);


    子查询:


      select * from employee  where dept_id in (select dept_id from department where age>25);


    使用EXISTS关键字


    EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。


    而是返回一个真假值。Ture或False


    当返回Ture时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询


      select * from employee where EXISTS (SELECT dept_name from department where dept_id=205);


      select * from employee where EXISTS (SELECT dept_name from department where dept_id=203); 


服务器,数据库问题系统问题,硬件报价,虚拟化,服务部署可以扫描下方二维码,添加技术支持,7*12小时在线解答或是QQ联系我们。

欢迎关注【5分享】(fivsha)   

5分享公众号.jpg

顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 验证码:点击我更换图片
最新评论
遇到困难可以给我发消息 遇到困难可以给我发消息