mysql-(一)

標籤：

- 建立指定字元編碼的資料庫         create database sysdb         default character set utf8;- 刪除資料庫          drop database sysdb;-  查看資料庫的預設字元集        show create database sysdb;- 修改資料庫        alter database sysdb default character set gbk;#### 表管理        use sysdb;        show tables; //查看所有表        //建立資料表        create table student (        sid int,        sname varchar(20),        sage int        );        desc student; //查看錶的結構           drop table student; //刪除資料表- 添加欄位        alter table student add column sgender varchar(2);        - 刪除欄位         alter table student drop column sgender;- 修改表的欄位類型              alter table student modify column sname varchar(100);- 修改資料表的名字         alter table student change sname(原名) userName(新名) varchar(20);- 修改表的名稱        alter table student rename to teacher;###增加資料- 插入資料        insert into student values(1 , ‘cx‘, ‘man‘ , 20);- 插入部分欄位        insert into student(id,name) values(2,‘eric‘);###修改資料- 1.修改單個欄位        update student set gender ="女"  //修改所有        update student set gender ="女" where id = 1;  //修改指定- 2.修改多個欄位           update student set gender = ‘男‘ , age = 30 where id = 1;//逗分隔- 3.刪除表中所有資料        delete from student;- 4.帶條件的刪除        delete from student where id = 2;#######delete from  和  truncate table 區別 - delete from 可以待條件刪除，truncate table 不能帶條件刪除- delete from 只能刪除表的資料，不能刪除表的約束，truncate既可以刪除表的資料，也可以刪除表的約束##查詢資料- 查詢所有列                    select * from student;-  查詢指定列           select id,name from student;- 查詢時指定別名（多表查詢，別名非常有用）         select id as ‘編號‘ , name as ‘姓名‘ from student s;//s為表的別名- 查詢時添加常量列        select id , name , gender , age , ‘年級一‘ as  ‘年級‘  from student;        查詢時加的列為常量列- 查詢時合并列          select id , name , (成績一+成績二) as ‘總成績’ from student         合并列只能合并數實值型別的列- 查詢時去除重複記錄        select distinct sex from stuent;        //拿到男，女兩個結果- 條件查詢（where） -  邏輯查詢   and  ,  or                             //查詢id為2，姓名為李四的學生               select * from  student   where id = 2  and name = ‘李四‘             //查詢id為2， 或者姓名為張三的學生                select * from  student   where id = 2  and name = ‘張三‘ -  比較條件   > < >= <= <>(不等於)  between and                            select * from student where jsp between 75 and 90                        select * from student where sex <> ‘女‘ -  判空  is null / is not null  /  = ‘ ‘ / <> ‘ ‘             //查詢地址為空白的學生               select * from student where address is null; //判斷null             select * from student where address = ‘‘; //判斷空             select * from student where address is null or address = ‘ ‘;                       //查詢有地址的學生             select * from student where address is not null and address <> ‘ ‘;####重點 -  模糊條件查詢                          %表示匹配n個任一字元             //查詢姓張的學生             select * from name where name like ‘張%‘             //查詢姓‘李‘,且姓名只有兩個字的學生             ­­_匹配一個任一字元              select * from name where name like ‘李_‘

mysql-(一)