MySQL数据库CRUD语句快速入门_MySQL

bitsCN.com
MySQL数据库CRUD语句快速入门 SQL语句sql语句，一种用于操作数据库的语言 数据库， 简单地理解为硬盘上的文件， 可以存放数据 sql 语句大致可以分类两大类 进入数据库的方法： 1. 命令行代码： 进入数据库C:/mysql -hlocalhost -P3306 -uroot -p123456 启动数据库 net start mysql 停止数据库net stop mysql 一、针对数据库database和表table的操作1、database和table的常见操作创建create 查看show 修改alter 删除drop // 创建一个数据库 create database mydb; // 查看所有的数据库库 show databases; // 删除 mydb drop database mydb; // 删除 user 表 drop table user; 针对表中的记录的操作 增 insert 删 delete 改 update 查 select 2 操作数据库创建一个名称为mydb1的数据库 create database mydb1; 创建一个使用utf-8字符集的mydb2数据库。 create database mydb2 character set utf8; 创建一个使用utf-8字符集，并带校对规则的mydb3数据库。 create database mydb3 character set utf8 collate utf8_general_ci; 查看当前数据库服务器中的所有数据库 show databases; 查看前面创建的mydb2数据库的定义信息，字符信息 show create database mydb2; 删除前面创建的mydb3数据库 drop database mydb3; 将mydb2的字符集修改为gbk alter database mydb2 character set gbk; // 备份数据库 use mydb2; create table a ( name varchar(20) ); // 插入数据 insert into a (name) values("zhangsan"); insert into a (name) values("wangwu"); // 备份数据库 mysqldump -uroot -proot mydb1 > d:/a.sql 这条命令跟mysql是一个级别的，所以不能在mysql下面使用。 // 删除 mydb2 drop database mydb2; // 恢复数据库 create database mydb22; source d:/a.sql // source 命令 指定一个sql脚本 关闭报错声音 Mysql –uroot –p123456 -b 3、操作表1. 创建表 创建员工表 Id 整形 name 字符型 sex 字符型或bit型 brithday 日期型 Entry_date 日期型 job 字符型 Salary 小数型 resume 大文本型 创建表： create table employee ( id int, name varchar(20), gender varchar(6), brithday date, entry_date date, job varchar(20), salary double, reshme text ); 2. 修改表 在上面员工表的基础上增加一个image列 alter table employee add image blob; 修改job列，使其长度为60 alter table employee modify job varchar(60); 删除sex列。 alter table employee drop sex; 表名改为users。 rename table employee to user; 修改表的字符集为utf-8 alter table user character set utf8; 列名name修改为username alter table user change column name username varchar(20); 查看所有表 show tables; 查看表的创建语句 show create table user; 查看表结构 desc user; 删除 user 表 drop table user; 4、针对表中数据的增删改查针对表中的数据操作无外乎四个：增删改查 crud create 1. insert语句 employee.sql create table employee ( id int, name varchar(20), sex varchar(10), birthday date, salary float, entry_date date, resume text ); // 向员工表插入三条记录 Insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,&＃39;zhangsan&＃39;,&＃39;male&＃39;,&＃39;1987-11-23&＃39;,1500,&＃39;2010-2-18&＃39;,&＃39;good boy&＃39;); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,&＃39;wangwu&＃39;,&＃39;male&＃39;,&＃39;1988-11-23&＃39;,1200,&＃39;2010-2-18&＃39;,&＃39;good boy&＃39;); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,&＃39;xiaohong&＃39;,&＃39;female&＃39;,&＃39;1983-11-23&＃39;,1800,&＃39;2010-2-18&＃39;,&＃39;good girl&＃39;); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(4,&＃39;赵楠&＃39;,&＃39;男&＃39;,&＃39;1986-11-23&＃39;,3800,&＃39;2011-2-18&＃39;,&＃39;绝种好男人&＃39;); // 查看表的所有记录 select * from employee; // 查看数据使用的所有编码 show variables like &＃39;character%&＃39;; // 修改客户端的编码 为 gbk set character_set_client=gbk; // 修改结果集的编码为gbk set character_set_results=gbk; // insert 语句的其他写法 create table a ( username varchar(20), password varchar(20) ); insert into a values(&＃39;zs&＃39;,&＃39;111&＃39;); insert into a values(&＃39;wangwu&＃39;,&＃39;2222&＃39;),(&＃39;lisi&＃39;,&＃39;3333&＃39;); insert a values(&＃39;aaa&＃39;,&＃39;bbb&＃39;); insert a (username) values(&＃39;aaa&＃39;); 2. update语句 将所有员工薪水修改为5000元 update employee set salary=5000; 将姓名为’zhangsan’的员工薪水修改为3000元。 update employee set salary=3000 where name=&＃39;zhangsan&＃39;; 将姓名为’lisi’的员工薪水修改为4000元,sex改为female。 update employee set salary=4000,sex=&＃39;female&＃39; where name=&＃39;wangwu&＃39;; 将xiaohong的薪水在原有基础上增加1000元 update employee set salary=salary+1000 where name=&＃39;xiaohong&＃39;; 3. delete语句 删除表中name为’赵楠’的记录。 delete from employee where name=&＃39;赵楠&＃39;; 删除表中所有记录。Delete是一条条的删，如果条目很多的话，会效率很低 delete from employee; 使用truncate删除表中记录。 摧毁表再创建表 尽量用这个 truncate employee; 4. select语句 student.sql create table student( id int, name varchar(20), chinese float, english float, math float ); insert into student(id,name,chinese,english,math) values(1,&＃39;张小明&＃39;,89,78,90); insert into student(id,name,chinese,english,math) values(2,&＃39;李进&＃39;,67,53,95); insert into student(id,name,chinese,english,math) values(3,&＃39;王五&＃39;,87,78,77); insert into student(id,name,chinese,english,math) values(4,&＃39;李一&＃39;,88,98,92); insert into student(id,name,chinese,english,math) values(5,&＃39;李来财&＃39;,82,84,67); insert into student(id,name,chinese,english,math) values(6,&＃39;张进宝&＃39;,55,85,45); insert into student(id,name,chinese,english,math) values(7,&＃39;黄蓉&＃39;,75,65,30); 查询表中所有学生的信息。 select * from student; 查询表中所有学生的姓名和对应的英语成绩。 select name,english from student; 过滤表中重复数据。 select distinct english from student; 在所有学生分数上加10分特长分。 select name,english+10,chinese+10,math+10 from student; 统计每个学生的总分。 select name,english+chinese+math as sum from student; 使用别名表示学生分数。 where 子句 查询姓名为李一的学生成绩 select * from student where name=&＃39;李一&＃39;; 查询英语成绩大于90分的同学 select * from student where english>90; 查询总分大于200分的所有同学 select name,english+chinese+math sum from student where english+chinese+math>200; 此处可以不用as 运算符 查询英语分数在 80－90之间的同学。 select * from student where english between 65 and 85; 查询数学分数为89,90,91的同学。 select name,math from student where math in(89,90,91); 查询所有姓李的学生成绩。 select * from student where name like &＃39;李%&＃39;; // 查询姓李的两个字的学生 select * from student where name like &＃39;李_&＃39;; 查询数学分>80，语文分>80的同学。 select * from student where math>80 and chinese>80; 查询英语>80或者总分>200的同学 select *,chinese+math+english from student where english>80 or chinese+english+math>200; order by 子句 对数学成绩排序后输出。 select * from student order by math; 对总分排序后输出，然后再按从高到低的顺序输出 select *,chinese+math+english from student order by chinese+math+english desc; 对姓李的学生成绩排序输出 order从句是需要放在where从句的后面 select *,chinese+math+english from student where name like &＃39;李%&＃39; order by chinese+math+english; 合计函数 count 统计一个班级共有多少学生？ select count(*) from student; 统计数学成绩大于90的学生有多少个？ select count(*) from student where math>90; 统计总分大于230的人数有多少？ select count(*) from student where chinese+math+english>230; sum 统计一个班级数学总成绩？ select sum(math) from student; 统计一个班级语文、英语、数学各科的总成绩 select sum(math),sum(chinese),sum(english) from student; 统计一个班级语文、英语、数学的成绩总和 select sum(math+chinese+english) from student; 统计一个班级语文成绩平均分 select sum(chinese)/count(*) from student; 缺考的不参与计算 select sum(chinese)/count(chinese) from student; avg 语文平均分 select avg(chinese) from student; max/min 语文最 高分 select max(chinese) from student; select语句（6） group by 订单表 create table orders( id int, product varchar(20), price float ); insert into orders(id,product,price) values(1,&＃39;电视&＃39;,900); insert into orders(id,product,price) values(2,&＃39;洗衣机&＃39;,100); insert into orders(id,product,price) values(3,&＃39;洗衣粉&＃39;,90); insert into orders(id,product,price) values(4,&＃39;桔子&＃39;,9); insert into orders(id,product,price) values(5,&＃39;洗衣粉&＃39;,90); 将商品归类 select * from orders group by product; 显示单类商品总结 select *,sum(price) from orders group by product; 商品分类 显示单类商品总价大于100的 select *,sum(price) from orders group by product having sum(price)>100; // 将单价大于20 的商品进行归类显示 按照价格排序 select * from orders where price>20 group by product order by price; 5、表的约束表的约束就是在定义表时，我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定 一、表的约束 表的约束就是在定义表时，为表中的字段加一些约束条件，对将来插入的数据做一些限定 1. 唯一约束 unique create table a ( id int, username varchar(20) unique, password varchar(20) ); insert into a (id,username,password) values(1,&＃39;zhangsan&＃39;,&＃39;1111&＃39;); insert into a (id,username,password) values(2,&＃39;wangwu&＃39;,&＃39;1111&＃39;); 2. 非空约束 not null create table b ( id int not null, name varchar(20) ); insert into b (id,name) values (1,&＃39;aaaa&＃39;); insert into b (id,name) values (1,&＃39;bbbb&＃39;); 3. 主键约束 相当于 唯一约束+非空约束 数据库中的每张表都应该至少有一个主键，通常是id create table c ( id int primary key, name varchar(20) ); insert into c(id,name) values (1,&＃39;aaaa&＃39;); insert into c(id,name) values (2,&＃39;bbbb&＃39;); create table d ( firstname varchar(20), lastname varchar(20), primary key(firstname, lastname) ); insert into d (firstname, lastname) values (&＃39;tom&＃39;, &＃39;cat&＃39;); insert into d (firstname, lastname) values (&＃39;tom&＃39;, &＃39;hks&＃39;); create table e ( id int , name varchar(20) ); insert into e (id,name) values(1,&＃39;aaa&＃39;); 为表加上主键约束 alter table e add primary key(id); 此处修改的命令： 修改job列，使其长度为60。 alter table employee modify job varchar(60); 删除主键约束 alter table e drop primary key; 4. 定义主键自动增长 这个的作用就是让id键随着条目的增加，自动往上增长。 注意主键类型必须是int，只有int类型的才可以自增 create table f ( id int primary key auto_increment, name varchar(20) ); insert into f(name) values (&＃39;aaa&＃39;); insert into f(id,name) values (11,&＃39;bbb&＃39;); mysql> select * from f; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | aaa | | 3 | aaa | | 4 | aaa | | 11 | bbb | | 12 | aaa | | 13 | aaa | | 14 | aaa | | 15 | aaa | | 16 | aaa | +----+------+ 外键约束 约束力: 插入的外键值必须为被参照列存在的值 被参照表中被参照的数据不允许删除 注意：外键约束并没有非空和唯一的约束力 创建丈夫表和妻子表 create table husband ( id int primary key auto_increment, name varchar(20) ); create table wife ( id int primary key auto_increment, name varchar(20), husbandid int, constraint husbandid_FK foreign key(husbandid) references husband(id) ); 外界约束：Constraint是定义外键约束的，foreign key是外键的名称 参照某张表的主键 分别添加两条记录 insert into husband(name) values (&＃39;laobi&＃39;); insert into husband(name) values (&＃39;laoyu&＃39;); insert into wife(name,husbandid) values (&＃39;fengjie&＃39;,2); insert into wife(name,husbandid) values (&＃39;furongjie&＃39;,1); fengjie 找老公 select * from husband where id=(select husbandid from wife where name=&＃39;fengjie&＃39;); delete from husband where name=&＃39;laoyu&＃39;; 6 表的关系1 多对一 创建部门表 create table department ( id int primary key auto_increment, name varchar(20) ); 添加三个部门信息 insert into department(name) values(&＃39;开发部&＃39;); insert into department(name) values(&＃39;销售部&＃39;); insert into department(name) values(&＃39;人事部&＃39;); 创建员工表 create table employee ( id int primary key auto_increment, name varchar(20), departmentid int, constraint departmentid_FK foreign key(departmentid) references department(id) ); 添加六个员工信息 insert into employee(name,departmentid) values (&＃39;张三&＃39;,1); insert into employee(name,departmentid) values (&＃39;李四&＃39;,1); insert into employee(name,departmentid) values (&＃39;王五&＃39;,2); insert into employee(name,departmentid) values (&＃39;赵六&＃39;,3); insert into employee(name,departmentid) values (&＃39;田七&＃39;,3); insert into employee(name,departmentid) values (&＃39;周八&＃39;,null); 多表的查询 查出1号部门所有的员工 select * from employee where departmentid=1; 查出开发部所有的员工 select * from employee where departmentid=(select id from department where name=&＃39;开发部&＃39;); 查出赵六在那个部门 select * from department where id=(select departmentid from employee where name=&＃39;赵六&＃39;); -------------------------------------- 联合查询 查出开发部所有的员工 select * from department,employee; +----+--------+----+------+--------------+ | id | name | id | name | departmentid | +----+--------+----+------+--------------+ | 1 | 开发部 | 1 | 张三 | 1 | | 2 | 销售部 | 1 | 张三 | 1 | | 3 | 人事部 | 1 | 张三 | 1 | | 1 | 开发部 | 2 | 李四 | 1 | | 2 | 销售部 | 2 | 李四 | 1 | | 3 | 人事部 | 2 | 李四 | 1 | | 1 | 开发部 | 3 | 王五 | 2 | | 2 | 销售部 | 3 | 王五 | 2 | | 3 | 人事部 | 3 | 王五 | 2 | | 1 | 开发部 | 4 | 赵六 | 3 | | 2 | 销售部 | 4 | 赵六 | 3 | | 3 | 人事部 | 4 | 赵六 | 3 | | 1 | 开发部 | 5 | 田七 | 3 | | 2 | 销售部 | 5 | 田七 | 3 | | 3 | 人事部 | 5 | 田七 | 3 | | 1 | 开发部 | 6 | 周八 | NULL | | 2 | 销售部 | 6 | 周八 | NULL | | 3 | 人事部 | 6 | 周八 | NULL | +----+--------+----+------+--------------+ 笛卡尔集 多张表的所有记录的排列组合 笛卡尔集当中有很多部匹配的数据(参照表外键列的值和被参照表主键的值不一致) 为了方便 以下称为废数据 加条件去掉废数据 select * from department,employee where employee.departmentid=department.id; 加条件做查询 select * from department,employee where employee.departmentid=department.id and department.name=&＃39;开发部&＃39;; 优化 给表加别名 只保留需要的数据 select e.* from department d,employee e where e.departmentid=d.id and d.name=&＃39;开发部&＃39;; 查出赵六在那个部门 select d.* from department d,employee e where e.departmentid=d.id and e.name=&＃39;赵六&＃39;; 2. 多对多 创建老师表 create table teacher ( id int primary key auto_increment, name varchar(20) ); 添加三个老师 insert into teacher(name) values (&＃39;老方&＃39;),(&＃39;老余&＃39;),(&＃39;老毕&＃39;); create table student ( id int primary key auto_increment, name varchar(20) ); 添加三个学生 insert into student(name) values(&＃39;大毛&＃39;),(&＃39;二毛&＃39;),(&＃39;三毛&＃39;),(&＃39;四毛&＃39;),(&＃39;五毛&＃39;),(&＃39;六毛&＃39;),(&＃39;七毛&＃39;),(&＃39;八毛&＃39;),(&＃39;小毛&＃39;); 创建中间表描述关系 create table tea_stu ( teaid int, stuid int, primary key(teaid,stuid), constraint teaid_FK foreign key(teaid) references teacher(id), constraint stuid_FK foreign key(stuid) references student(id) ); 添加数据 insert into tea_stu (teaid,stuid) values (1,1),(1,2),(1,4),(1,5),(1,6),(1,8),(2,1),(2,3),(2,5),(2,7),(2,9),(3,2),(3,3),(3,4), (3,5),(3,6),(3,7),(3,8); 查询2号老师教过的学生 select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2; 查询老余教过的所有的学生 select s.* from student s,tea_stu ts,teacher t where ts.stuid=s.id and ts.teaid=t.id and t.name=&＃39;老余&＃39;; 多表联合查询 查询的结果为笛卡尔集 n张表联合查需要n-1个条件来去掉废数据 去掉废数据的条件 参照表的外键列=被参照表的主键列 再加上查询条件即可得到结果 3. 一对一 create table person ( id int primary key auto_increment, name varchar(20) ); create table idcard ( id int primary key, location varchar(20), constraint personid_FK foreign key(id) references person(id) ); insert into person (name) values(&＃39;zhangsan&＃39;); insert into person (name) values(&＃39;lisi&＃39;); insert into idcard (id,location) values(2,&＃39;天津&＃39;); insert into idcard (id,location) values(1,&＃39;上海&＃39;); 查李四的身份证 select idcard.* from person,idcard where idcard.id=person.id and person.name=&＃39;lisi&＃39;; bitsCN.com