MYSQL数据库基本操作

DDL:定义数据库或者表结构用的。

//创建一个数据库create database people;//查看创建细节show create database people;//查看当前所有数据库show databases;//创建一个使用gbk字符集的数据库create database people character set gbk;//创建一个使用utf-8字符集并带校对规则的数据库create database people character set utf8 collate utf8_general_ci;//删除数据库drop database people;

表结构操作

//创建表，首先选择数据库mysql>use people;mysql>create table stu(    id int,    name varchar(20),    gender varchar(20),    job varchar(20));//向表中添加列（一般不采用，数据库应该是事先设定好）mysql>alter table stu add image blob;//删除表中的列mysql>alter table stu drop image;//修改job列的长度mysql>alter table stu modify job varchar(60);//修改表中列的名字mysql>alter table stu change name username varchar(100);

DML:数据操作语言
作用：操作表中的记录

//插入语句mysql>insert into stu(id,name,gender,job) values(1,'张三','男','CTO');//更新语句mysql>update stu set name='李四' where id = 1;mysql>update stu set salary = salary + 100 where name = '李四';//删除数据mysql>delete from stu;//删除全部数据mysql>delete from stu where id = 1;//删除一条数据//查询语句mysql>select *from stu;//查询stu表中全部数据mysql>select name from stu;//查询表中所有的名字mysql>selsect name 姓名 from stu;//查询表中所有名字并将列输出为名字而不是name;mysql>select *from stu where id = 1;//查询id为1的所有数据mysql>select *from stu where id>3;//查询id大于3的所有数据mysql>select *from stu where id between 5 and 10;//查询id在5-10中的数据mysql>select *from stu where id in(1,5,7);//查询id为1，5，7的数据//模糊查询mysql>select *from stu where name = '李%'//查询所有姓李的数据mysql>select *from stu where name = '%李%'//查询名字中含有李的数据mysql>selsect *from stu where name = '_李%'//查询姓名中第二个字为李的数据//排序查询mysql>select *from stu order by math desc;//对数学成绩降序查询mysql>select *from stu order by math asc;//对数学成绩升序查询

链接查询

//交叉查询stu中三条数据worker中四条数据，查询结果3*4=12条数据mysql>select *from stu cross join worker;//外链接left out join = left join;mysql>select *from customer c left join orders o on c.id=o.customer_id;//查询前表中id和后表中customer_id相同的数据

//子查询

mysql>select *from orders where customer_id=(select id from customer where name = '张三');

//联合查询

mysql>select *from orders where age>20 union selsect orders where customer_id = 1; 

//报表查询，使用数据库提供的函数

//统计班级人数mysql>select count(*) from stu;//统计数学成绩大于90的学生个数mysql>select count(*) from stu where math>90;//统计表中数学成绩的总和mysql>select sum(math) from stu;//统计表中数学,语文，英语成绩的总和mysql>select sum(math),sum(chinese),sum(english) from stu;//统计语文平均分mysql>select avg(chinese) from stu;//统计语文最高分和最低分mysql>select max(chinese),min(chinese) from stu;