MySQL语法
创建用户:
create user zxh identified by 'czs';
create user zxh@localhost identified by 'czs';
授权:
grant all privileges on *.* to 'zxh';
*.* 是代表任何数据库的任何表
数据库名.* 某某数据的任何操作
grant insert, select on mysql.* to username;
flush privileges; // 刷新权限表
用户登录与切换:一定要在dos的run中打开
找到mysql.exe的路径
C:\wamp\mysql\bin\mysql -h localhost -u czs -p #-h前面为打开mysql, localhost可以换为ip
删除用户:
drop user username1, username2;
删除用户权限:
mysql> revoke all privileges on *.* from czs;
修改用户密码:
GRANT USAGE ON *.* TO username@"%" IDENTIFIED BY 'password';
mysql注释符有三种:...代表任意内容 1,3比较常用
1、#...
2、"-- ..."
3、/*...*/
创建数据库:
CREATE DATABASE `sql_study` ;
CREATE DATABASE `databaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `databaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
进入数据库:
use 数据库名;
显示所有表:
show tables;
打印表列名:
desc 表名;
创建表:
方式一:
CREATE TABLE `collect` (
`cId` bigint(20) NOT NULL auto_increment,
`jId` bigint(20) NOT NULL,
`uId` int(11) NOT NULL,
`cTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`cId`),
KEY `jId` (`jId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
`cId` bigint(20) NOT NULL auto_increment,
`jId` bigint(20) NOT NULL,
`uId` int(11) NOT NULL,
`cTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`cId`),
KEY `jId` (`jId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
还包含自增由1开始
方式二:(标准)
create table two
(
name char(20) not null,
id int(11) not null auto_increment,
primary key (id)
);
(
name char(20) not null,
id int(11) not null auto_increment,
primary key (id)
);
方式三:
CREATE TABLE `newwe1e`.`tableName` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL DEFAULT '默认',
`time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = INNODB
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL DEFAULT '默认',
`time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = INNODB
通过上面上种创建,可以发现列的not null, primary key, on update current_timestamp等的次序是可以随意改变的
另外primary可以马上说明,也可列出所有列之后在指明。列名可以不用添加单引号
不可以在主键为多个列的情况下,让某列自增。
插入数据:
方式一:
insert into two (name,id)vaules('name', null); #可以去掉id和null,
方式二:
insert into two values('name', null); #必须一个个对应,必须列出所有值,不予赋值置null
insert into seven values(null, now()); #使用时间函数
insert into seven values(null, now()); #使用时间函数
删除:
删除表中所有元组:
delete from r; #表还在
drop table r; /*表不存在*/
删除列,增加列:
ALTER TABLE `two` ADD `num1` VARCHAR( 11 ) NOT NULL ,
ADD `num2` INT NOT NULL ; #增加列
ADD `num2` INT NOT NULL ; #增加列
alter table two drop num1, drop num2; #删除列
(由下面可知,可以删除一个列就要一个drop,增加一个列就要一个add,同时也说明可以嵌套,同时删除,同时增加)
alter table two drop city, add num1 int(11);
添加属性:
ALTER TABLE `tablename` ADD UNIQUE ( `time`) /*增加唯一属性*/
ALTER TABLE `tablename` ADD INDEX ( `time` ) /*增加索引属性*/
ALTER TABLE `tablename` CHANGE `time` `time` VARCHAR( 20 ) NOT NULL /*改变某一列的属性*/
ALTER TABLE `tablename` ADD INDEX ( `time` ) /*增加索引属性*/
ALTER TABLE `tablename` CHANGE `time` `time` VARCHAR( 20 ) NOT NULL /*改变某一列的属性*/
上面这些修改,都是可以修改嵌套的,增加后面紧跟改变等
外键:
ALTER TABLE `tablenamer` ADD FOREIGN KEY ( `本地id` ) REFERENCES `one`.`tablename` (`外部id`);
更新:
UPDATE `imu`.`user` SET `qq` = 'QQ',
`uTime` = NOW( ) ,
`resume` = '简历',
`mood` = '签名' WHERE `user`.`uId` =16 LIMIT 1 ;
`uTime` = NOW( ) ,
`resume` = '简历',
`mood` = '签名' WHERE `user`.`uId` =16 LIMIT 1 ;
查询:
不重复:结果元组集中不出现相同元组distinct
select distinct num, shu from eight; // distinct只能一个,作用于所有列, distinct还用于count, sum等函数
范围查询:between 11 and 200
select * from eight where shu between 11 and 200; 等价于 select * from eight where shu <= 200 and shu >= 11;
between...and 包含了等号, 还有not between...and
字符串匹配查询: _代表任意的一个字符, %代表任意子串,长度无限
like 'ab\%c\\d%'; // 转义字符\,让字符串可以匹配%, 匹配\
排序查询:
放在select语句最后,且最后作用,order by 列名 desc,列名 asc, 默认asc为升序,desc为降序
结果集运算:
select id from eight union (select id from seven); // 并运算
intersect // 交运算
except // 差运算
参加运算的关系,对应的列数据类型要相容,n个select语句就n-1个运算关键字
函数查询:avg,min,max,sum,count
select name, avg(distinct balance) from account group by name having avg(distinct balance) > 1200;
// 先分组,然后查询,之后用having过滤
空值查询:
如果算术运算的输入有一个是空,则该算术表达式(+-*/)的结果是空,如果空值参与比较运算,运算结果看成unknown(既不是is null,也不是is not null)
where amount is null/is not null/ is unknown/ is not unknown
嵌套子查询:in, not in
![](http://images0.cnblogs.com/blog/354329/201309/12232355-73a9f36fbc5a40b493d53cc7a7457d14.png)
select * from eight where name in (select name from eight);
集合的比较:
select * from eight where shu >= some (select shu from eight); // shu只要大于或等于子查询结果中的一个就为真
select * from eight where shu >= all (select shu from eight); // shu要大于或等于子查询结果中的全部才为真
select * from eight where shu >= any (select shu from eight); // any同some
<some, <=some, >=some, =some, <>some等,all也类推
测试关系是否为空:
select * from eight where shu exists (select shu from eight); // 这种只有两种结果,相当于where true 或者 where false; 子查询为空则为false
select * from eight where shu not exists (select shu from eight);
测试关系是否存在重复元组:
select * from eight where shu unique (select shu from eight);
select * from eight where shu not unique (select shu from eight);
别名as的另一种用法:
(select name, avg(balance) from account group by name) as branch_avg(branch_name, avg_balance); // 对整个查询结果进行命名
select max(tot_balance) from (select name, sum(balance) from account group by branch_name) as branch_total (branch_name, tot_balance);
// 对整个查询结果进行命名,让后马上投入max计算。
临时视图:with
delete from account where balance < (select avg(balance) from account); // <后面搜寻结果是一个数字
插入
insert into account where...
更新
update account set balance = balance * 4 where balance >= 1000;
case结构:更新顺序很重要,eight表名,shu<2,加5,不然加10
update eight set shu =
case
when shu < 2 then shu + 5
else shu + 10
end;
创建数据类型:
create type newType as int(11) final;
断言:create assertion <断言名> check <谓词>;