taifyang的博客

查看用户列表use mysql;select user from user;创建用户create user 用户名 indentified by ‘密码’;重命名用户rename user 原用户名 to 新用户名;删除用户drop user 用户名;查看用户权限show grants for 用户名；设置用户权限grant 权限 on 数据库/表 to 用户;撤销用户权限revoke 权限 on 数据库/表 to 用户;更改口令set password for

查看所支持的字符集列表show character set;查看所支持校对的列表show collation;确定所用的字符集和校对show variables like 'character%';show variables like 'collation%';给表指定字符集和校对create table mytable( columnn1 int; columnn2 varchar(10))default character set hebrew collate hebr

事务开始start transaction;回退rollback;提交commit;使用保留点savepoint delete1;rollback to delete1;设置手动提交set autocommit = 0;

创建触发器create trigger newproduct after insert on productsfor each row select 'product added';删除触发器drop trigger newproduct;insert触发器create trigger neworder after insert on ordersfor each row select new.order_num;delete触发器create trigger deleteorder

创建游标create procedure processorders()begin declare ordernumbers cursor for select order_num from orders;end;打开游标open ordernumbers;关闭游标close ordernumbers;使用游标从游标中检索单个行create procedure processorders()begin declare ordernumbers cursor for s

创建存储过程create procedure productpricing()begin select avg(prod_price) as priceaverage from products;end;执行存储过程call productpricing();删除存储过程drop procedure productpricing;使用参数create procedure productpricing( out pl decimal(8,2), out ph decimal(8

利用视图简化复杂的联结create view productcustomers as select cust_name, cust_contact,prod_id from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num =orders.order_num;select cust_name,cust_contactfrom productcustom

创建表create table customers( cust_id int NOT NULL ATUO_INCREMENT, //自动对该列增量 cust_name char(50) NOT NULL DEFAULT 'xiaoming', //默认值 cust_email char(255) NOT NULL, //不可以为NULL cust address char(50) NULL, //可以为NULL PRIMARY KEY (cust_id) //主键，有多个时写在括号中用逗号隔开

插入完整的行insert into customers values(NULL, 'Pep E .LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);insert into customers(cust_name,cust_contact,cust_email,cust_address,cust_city,cust_state,cust_zip,cust_country)values('Pep E

启用全文本搜索支持create table productnotes( note_id int NOT NULL AUTO_INCREMENT, prod_id char(10) NOTNULL, note_date datetime NOT NULL, note_text text NULL, FULLTEXT(note_text))ENGINE = MyISAM;进行全文本搜索：match()指定搜索列，against()指定搜索的文本select note_text from

使用unionselect vend_id, prod_id, prod_price from products where prod_price <=5 union select vend_id, prod_id, prod_price from products where vend_id in (1001, 1002);包含重复的行select vend_id, prod_id, prod_price from products where prod_price &l

创建联结select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;内部联结=where 会过滤掉空数据select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_i

利用子查询进行过滤：列出订购物品TNT2的所有客户检索包含物品TNT2的所有订单的编号select order_numfrom orderitemswhere prod_id = 'TNT2';检索前一步列出的订单编号的所有客户的IDselect cust_idfrom orderswhere order_num in (select order_num from orderitems where prod_id = 'TNT2');列出订购物品TNT2的所有客户

创建分组select vend_id, count(*) as num_prods from products group by vend_id;过滤分组：过滤两个以上订单的分组select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;过滤具有2个（含）以上产品、价格为10（含）以上的供应商select vend_id, count(*) as num_prods fr

avg()函数：返回products表中所有产品的平均价格select avg(prod_price) as avg_price from products;返回特定供应商所提供产品的平均价格select avg(prod_price) as avg_price from products where vend_id = 1003;count()函数：返回customers表中客户的总数select count(*) as num_cust from customers;只对具有电子邮件地

文本处理函数：//upper转换大写select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;//匹配所有发音类似于Y.Lie的联系名select cust_name, cust_contact from customers where soundex(cust_contact) = soundex( 'Y .Lie');日期和时间处理函数：select cust_id, or

拼接两个列select concat (vend_name, '(', vend_country,')') from vendors order by vend_name;使用别名///as别名，rtrim 右边空格去除 ltrim左边空格去除 trim两边空格去除select concat (rtrim(vend_name), ' (', rtrim(vend_country),')') as vend_title from vendors order by vend_name;算数计算S

使用where子句select prod_price, prod_name from products where prod_price = 2.50;

检索单个列select 列名 from 表名;检索多个列select 列名1,列名2,... from 表名;检索所有列select * from 表名;检索不同的行select distinct 列名 from 表名;检索前3行select 列名 from 表名 limit 3;从第3行开始检索4行select 列名 from 表名 limit3,4;使用完全限定的列名select 表名.列名 from 表名;使用完全限定的列名和表名select 表名.列名 f

本地root登录mysql -uroot -p显示数据库show databases;选择数据库use 数据库名;显示表show tables;显示表列show columns from 表名;