web框架学习前复习——mysql命令

1数据库库操作

创建一个数据库分库mytestcreate database MYTEST;查看当前数据库有多少分库show databases;查看数据库的创建细节show create database MYTEST;创建一个使用gbk字符集的分库mytest1create database MYTEST1 character set gbk;创建一个使用gbk字符集并带有效验规则的分库mytest2create database MYTEST2 character set gbk collate gbk_chinese_ci;删除前面的分库mytest2drop database MYTEST2;查看前面的分库mytest1并修改字符集为utf8(utf——8）alter database MYTEST1 character set utf8

2数据库表操作

查看当前使用的分库select database();选择当前使用的分库 mytestuse MYTEST;创建一个人员表（包括 ID name birthday job salary gender)create table PERSON（    ID int,    NAME varchar(100),    BIRTHDAY date,    JOB varchar(100),    SALARY float(8,2),    GENDER varchar(100));查看表结构desc PERSON；在人员表中增加一个image信息列alter table PERSON add(IMAGE longblob);修改job列的存储长度为60alter table PERSON modify JOB varchar(60);删除image列alter table PERSON drop IMAGE;表名改为USERSrename table PENSON to USERS;查看当前分库中所有表show tables;修改表的字符集为UTF-8alter table USERS character set utf8;列名那么改为username;alter table USERS change NAME USERNAME varchar(100);

数据操作

向USERS表中插入数据insert into USERS (ID,USERNAME,BIRTHDAY,JOB,SALARY,GENDER) values (1, 'zzz'，'2000-12-12','it','10000','cto'）;也可直接插入insert into USERS values(1,'zzz','2000-12-12','it','10000','cto');将所有员工工资修改为500update USERS set SALARY=500;将姓名为zzz的员工工资修改为600update USERS set SALARY=600 where USERNAME='zzz';将姓名为zzz的员工工资修改为700，job为cccupdate USERS set SALARY=700,JOB='ccc' where USERNAME='zzz';将姓名为zzz的员工工资在基础上加100；update USERS set SALARY=SALARY+100 where USERNAME='zzz';删除表中姓名为zzz的记录delete from USERS where USERNAME='zzz';删除表所有记录delete from USERS;摧毁表格然后重建表格truncate table USERS;

单表查询

查询表中所有用户的信息select * from USERS;查询表中所有姓名对应的工资信息select USERNAME,SALARY from USERS;显示所有的工资过滤重复的工资select distinct SALARY from USERS;查询所有人的工资并显示的时候加100select USERNAME,SALARY+100 from USERS;使用姓名别名name 表示用户的工资select USERNAME as name from USERS;查询姓名为zzz的员工工资select SALARY from USERS where USERNAME='zzz';查询工资大于500的员工信息select * from USERS where SALARY>500;查询工资在500到1000之间的员工姓名和工作select USERNAME,JOB from USERS where SALARY between 500 and 1000;查询工资为500 600 100的员工姓名和工作select USERNAME,JOB from USERS where SALARY in(500,600,100);查询所有姓名为z开头的员工信息select * from USERS where USERNAME like 'z%';查询员工的工资和姓名并按高低顺序排select USERNAME,SALARY from USERS order by SALARY desc;

完整性约束语句

创建一个表mytest1并指定主键为ID(3种写法）create table MYTEST1(    ID int primary key,    NAME varchar(100)    );create table MYTEST1(    ID int ,    NAME varchar(100),    primary key(ID)    );create table MYTEST1(    ID int ,    NAME varchar(100),    );alter table MYTEST1 add primary key(ID);创建NAME时增加非空约束 not null和唯一约束 uniquecreate table MYTEST1(    ID int ,    NAME varchar(100) not null unique,    primary key(ID)    );定义在一个表orders中添加另一个表 customers的主键为外键create table CUSTOMERS(    ID int primary key,    NAME varchar(100),    ADDRESS varchar(100)    );create table ORDERS(    ID int primary key,    ORDER_NUM varchar(100),    AMOUNT float(10,2),    CUSTOMER_ID int,constraint CUSTOMER_ID_FK foreign key(CUSTOMER_ID) references CUSTOERS(id);查询2张表记录的笛卡尔积select * from CUSTOERS,ORDERS;隐式内查询CUSTOMERS中对应ORDERS中CUSTOMER_ID的数据select * from CUSTOERS c,ORDERS o where c.ID=o.CUSTOMER_ID;显示内查询CUSTOMERS中对应ORDERS中CUSTOMER_ID的数据select * from CUSTOERS c inner join ORDERS o on c.ID = o.CUSTOMER_ID;左外查询CUSTOMERS所有客户信息，同时有ORDERS的显示订单的详细信息select * from CUSTOMER c left outer join ORDERS o on c.ID= o.CUSTOMER_ID;右外查询ORDERS订单信息，同时返回下单的CUSTOMERS信息select * from CUSTOMER o right outer join ORDERSon o on c.ID=o.CUSTOMER_ID;子查询姓名为zzz的客户所有订单select * from ORDERS where CUSTOMER_ID=(select ID from CUSTOMER where NAME='zzz');

MYSQL常用的函数

SUM():求和COUNT()：计数AVG()：求平均MAX()：求最大MIN()：求最小统计客户人数select count(*) from CUSTOMERS;统计所有订单金额select sun(AMOUNT) from ORDERS;