1、內(nèi)容第一堂:一熟悉mysql基本界而和命令1所有命令以“；"作為結(jié)朿符。2 命令行中登錄 mysql： mysql -h localhost -u root -p3常用數(shù)據(jù)庫命令：3.1使用show語句找岀在服務(wù)器上當(dāng)前存在什么數(shù)據(jù)庫：mysql> show databases;3.2創(chuàng)建一個數(shù)據(jù)庫mysqldatamysql> create database mysqldata;3.3:選擇你所創(chuàng)建的數(shù)據(jù)庫mysql> use mysqldata;(按回車鍵岀現(xiàn)database changed時說明操作成功！)3.4:查看現(xiàn)在的數(shù)據(jù)庫中存在什么表mysql>

2、 show tables;3.5:創(chuàng)建一個數(shù)據(jù)庫表mysql> create table mytable (name varchar(20), sex char(l);3.6:顯示表的結(jié)構(gòu)：mysql> describe mytable; / desc mytable3.7修改表的結(jié)構(gòu)3.7修改字段的數(shù)據(jù)類型mysql> alter table mytable modify 屬性名 數(shù)據(jù)類型；3.7.2修改字段名mysqlalter table mytable change舊屬性名新屬性名新數(shù)據(jù)類型；3.7.3增加字段mysql> alter table mytable

3、 add屬性名1數(shù)據(jù)類型完整性約束條 件firstiafter 屬性名 2;如:alter table mytable add num int(8) primary key first;alter table mytable add address varchar(30) not null afterphone;3.7.4刪除字段mysql> alter table drop 屬性名3.7.5修改字段的排列位置mysql> alter table mytable modify 屬性名 1 數(shù)據(jù)類型 first|after 屬性名3.&刪除表mysql>drop tab

4、le mytable;第二堂一、表的數(shù)據(jù)的插入、更新和刪除1:往表中加入記錄1.1：所有字段增加記錄mysql> insert into mytable values ("hyq'',''m");1.2：特定字段增加記錄mysql> insert into mytable(屬性名 1,屬性名 2) values ("hyq't'm'');1.3同時插入多條記錄mysql> insert into mytable（屬性名 1,屬性名 2） values （"hyq'&

5、#39;,"m''）,（“das'',''weq"）;*1.4將查詢結(jié)果插入到表中mysql> insert into mytable（屬性列表 1） select 屬性列表 2 from 表名 2 where 條件表 達(dá)式；2:用文本方式將數(shù)據(jù)裝入數(shù)據(jù)庫表中（例如d:/mysql.txt）mysql> load data local infile ud:/mysql.txf, into table mytable option;option:fields terminated by，字符串,，設(shè)置存符串'

6、為分隔符，默認(rèn)是“l(fā)3:導(dǎo)入.sql文件命令（例如d:/mysql.sql）mysql>use database;mysql>source d:/mysql.sql;4:清空表mysql>delete from mytable where condition;5:更新表中數(shù)據(jù)mysql>update mytable set sex=,f, where name=,hyq,;第三堂一、查詢數(shù)據(jù)1:單表查詢1.1 ：列出表的所有字段mysql>select * from mytable;1.2：列出表的某些字段mysql> select al, a2 from

7、mytable;1.3：查詢指泄記錄mysql> select al, a2 from mytable where condition;1.4：帶in關(guān)鍵字的查詢mysql> select al, a2 from mytable where name not in ('張三李四')；1.5 ：帶between and的范圍查詢mysql> select al, a2 from mytable where age not between 15 and 25;1.6：帶like的字符匹配查詢(like可以直接用二代替)mysql> select al, a2

8、 from mytable where name like 'adc_add%'1.7：查詢空值mysql> select al, a2 from mytable where iq is not null;1.8：帶and的多條件查詢mysql> select al, a2 from mytable wherea=,as, and sex='m'1.9：帶or的多條件查詢mysql> select al, a2 from mytable where a=,as, or sex='m'1.10：查詢結(jié)果不重復(fù)mysql> s

9、elect distinct al, a2 from mytable where a='as' and sex=,m,;1.11：排序mysql> select al, a2 from mytable where a=,as, and sex=，m' order by a;1.12： group by with 聚集函數(shù)、havingmysql> select al, count(a2) from mytable group by al having count(a2)>3;1.13：五個聚集函數(shù)count, max, min, avg, sum;2:

10、多表查詢2.1內(nèi)連接用“，”連接，表示笛卡爾積操作。2.2左外連接mysql>select al from employee left join department on employee.id=department.id;2.3右外連接mysql>select al from employee right join department on employee, id 二 department, id;3:子查詢3.1 inmysql>select al from employee where djd not in (select djd from department)

11、;3.2比較操作符=,! > >, >=, <, v=, v>3.3 existsmysql>select al from employee where |not exists (select * from department);3.4 anymysql>select al from employee where d_id>= any (select d_id from department);3.5 allmysql>select al from employee where d_id>= all (select djd from

12、 department);4: union allmysql>select al from employee union all (select al from department);5:視圖5創(chuàng)建視圖mysql>create view a_view as select * from department;mysql>desc a_view5.2修改視圖mysql>alter view a_view(dl,d2,d3,d4) as select dl,da,db,dv from c;(假設(shè)原來 的view只有3個屬性，通過該命令可以變?yōu)?個)5.3更新視圖mysql&

13、gt;update a.view set5.4刪除視圖mysql>drop view if exists a_view6觸發(fā)器6創(chuàng)建觸發(fā)器6.1.1只有一個執(zhí)行語句的觸發(fā)器mysql>create trigger 觸發(fā)器名 before | after 觸發(fā)事件 on 表名 for each row執(zhí)行語句其中觸發(fā)事件有insert|update|deletemysql>create trigger a_trigl before insert on depart for each row insert into b_trig vaules(now();6丄2有多個執(zhí)行語句的觸發(fā)器mysql>create trigger 觸發(fā)器名 before | after 觸發(fā)事件 on 表名 for each row beign執(zhí)行語句列表end通常情況下mysql使用；作為結(jié)束執(zhí)行語句，但是在多執(zhí)行語句之間需要用；所以我們 可以用delimiter &&來聲明結(jié)束執(zhí)行語句符號為&&。結(jié)束后可以再用delemiter;改 回來mysql>delimlter &&>create trig