????DDL(Data Definition Language)????????????
????DML(Data Manipulation Language)????????????????????????
????DCL(Data Control Language)?????????????/????????????????????
????TCL(Transaction Control Language)??????????????й???
?????????????
????win+R????cmd?????? ???????????????"mysql -uroot -proot"?????root????????????
??????????win???????????“;”???????mysql??????????“;”
????????????
????1.???????"mydb1"?????:
????create database mydb1;
????2.????????????:
????show databases;
????3.???????"mydb1"????????????????:
????create database mydb2 CHARACTER SET=utf8;
????4.????????mydb2???????:
????show create database mydb2;
????5.????????????mydb2???????mydb2???????????if exits?ж??????????????????????????:
????drop database if exits mydb2;
????6.?????????????????:
????alter database mydb2 character set gb2312;
????7.???????(root????????????????win????(????cmd?????????)????????????c????):
????mysqldump -u root -p mydb2>c://test.sql;
????8.?????????????????:
????(1)???????win????:mysql -u root -p mydb2<c://test.sql
????(2)source c://test.sql;
?????????????
????1.?????????:
????create table student(
????/* ?????????????????????*/
????sno varchar(4) primary key??
????sname varchar(10) not null??
????sage int??
????ssex char(2)??
????email varchar(20) unique??
????constraint ck_student_ssex_sage check(ssex in('??'??'?')and sage between 10 and 50)
????)
????2.??????(???):
????alter table tableName add columnName varchar(30);
????3.????У??????:
????alter table tableName modify cloumnName varchar(60);
????4.?????:
????alter table tableName drop column cloumnName;
????5.??????:
????rename table tableName_old to tableName_new;
????6.??????????:
????alter table user character set gbk;
????7.???????:
????alter table tableName change column cloumnName columnName_new varchar(60);
????8.?????:
????drop table tableName;
??????????????
?????????
????create table employee
????(
????id int??
????name varchar(40)??
????sex varchar(4)??
????birthday date??
????entry_date date??
????salary decimal(8??2)??
????resume text
????);
????1.????????:
????insert into employee(id??name??sex??birthday??entry_date??salary??resume) values(1??'zhangsan'??'male'??'1993-03-04'??'2016-11-10'??'1000'??'i am a developer');
????/*??????????Σ?????????????????*/
????insert into employee values(1??'zhangsan'??'male'??'1993-03-04'??'2016-11-10'??'1000'??'i am a developer');
????2.????Щ?в???????:
????insert into employee(id) values(6);
????3.?????????????:
????insert into employee(id??name) values(6??'????');
????/*????mysql???????gb2312????*/
????show variables like 'chara%';
????set character_set_client=gb2312;
????insert into employee(id??username) values('3'??'????');
????/*?????????????*/
????show variables like 'chara%';
????set character_set_results=gb2312;
????select * from employee;
????4.?????????:
????/*????????????’zs’????*/
????delete from employee where name='zs';
????/*??????????м??*/
????delete from employee;
????/*???truncate??????м??(???????????????????????????????)*/
????truncate table employee;
????5.??????????:
????/*?????????н?????5000?*/
????update employee set salary=5000;
????/*???????’zs’?????н?????3000?*/
????update employee set salary = 3000 where name='zs';
????/*???????’aaa’?????н?????4000???job???ccc*/
????update employee set salary = 4000??job='ccc' where name='aaa';
????/*??lisi??н?????л?????????1000?*/
????update employee set salary = salary+1000 where name='lisi';
????6.???????????:
????/*???????????????????*/
????select id??name??chinese??english??math from student;
????/*???????????????????????????????*/
????select name??english from student;
????/*??????????????*/
????select distinct english from student;
????????????????????10???????*/
????select name??(chinese+english+math)+10 from student;
????/*??????????????*/
????select name??(chinese+english+math) from student;
????/*????????????????*/
????select name??(chinese+english+math) as ??? from student;
????/*?????????????????????????as*/
????select name??(chinese+english+math) ??? from  student;
????/*????????????????????*/
????select * from student where name='????';
????/*????????????90?????*/
????select * from student where english>'90';
????/*?????????200?????????*/
????select name??(chinese+english+math) ??? from student where chinese+english+math>200;
????/*??????????? 80??90??????*/
????select * from student where english>=80 and english=<90;
????select * from student where english between 80 and 90;
????/*???????????89??90??91????*/
????select * from student where math=89 or math=90 or math=91;
????select * from student where math in(89??90??91);
????/*??????????????????*/
????select * from student where name like '??%';
????select * from student where name like '??_';
????/*????????>80???????>80????*/
????select * from student where math>80 and chinese>80;
????select * from student where chinese is null;
????/*???????????????????????desc?????????*/
????select name??math from student order by math desc;
????/*???????????????????????asc?????????*/
????select name??math from student order by math asc;
????/*??????????????????????????????????*/
????select name??math+english+chinese from student order  by math+english+chinese desc;
????/*????????????????????*/
????select name??math+english+chinese from student where name like '??%' order by math+english+chinese desc;
????/*???????????ж??????*/
????select count(*) from student;
????select count(id) from student;
????/*?????????????80?????????*/
????select count(*) from student where math>80;
????/*?????????250??????*/
????select count(*) from student where math+english+chinese>250;
????/*??? null?????count*/
????select count(chinese) from student;
????/*???????????????*/
????select sum(math) from student;
????/*????????????????????????????s*/
????elect sum(math)??sum(english)??sum(chinese) from student;
????/*??????????????????????????*/
????select sum(math+english+chinese) from student;
????/*???????????????????*/
????select sum(chinese)/count(*) from student;
????/*???????????????*/
????select avg(math) from student;
????/*???????????????*/
????select avg(math+english+chinese) from student;
????/*???????????????Χ???????????????*/
????select max(math+english+chinese) from student;
????select min(math+english+chinese) from student;
????/*????????????????????????????????*/
????select product??sum(price) from orders group by product;
????/*??????????????????????????????100?????*/
????select product??sum(price) from orders group by product having sum(price)>100;