mysql中的state是不是关键字_MySQL必知必会
1.数据库，表，列,⾏，模式，每⼀列有唯⼀的数据类型，模式是数据库和表的布局及特性
linux服务器操作系统功能2.满⾜主键的两个条件：任意两⾏都不具有相同的主键值，每⾏都必须具有主键值
3.SQL(Structured Query Languge)结构化查询语⾔
4.DBMS数据库管理系统，MySQL是⼀种DBMS，即他是⼀种数据库软件
DBMS可以分为两类：⼀类为基于共享⽂件系统的DBMS,⼀类为基于客户机-服务器的DBMS。MySQL为第⼆类
5.MySQL Administrator(MySQL管理器)是⼀个图形交互客户机,⽤来简化MySQL服务器的管理
MySQL Query Browser 为⼀个图形交互客户机，⽤来编写和执⾏MySQL命令
6.USE crashcourse 打开数据库  SHOW DATABASES 显⽰数据库的内部信息 SHOW TABLES 获得⼀个数据库内的表的列表
SHOW COLUMNS FROM customers == DESCRIBE customers 从表customers返回每列的字段名、数
据类型、是否可以为Null等所有信息
7.SELECT prod_name FROM products; 从products表中检索⼀个名为prod_name的列，和C语⾔⼀样，语句之间⽤ ; 隔开，对关键字⼤写，列和表名⼩写
SELECT prod_id, prod_name, prod_prices FROM products; 从products表中检索三列，列名之间⽤ , 隔开
SELECT * FROM products;  从products表中检索所有的列，* 称作通配符
SELECT DISTINCT vend_id FROM products; DISTINCT关键字两个特点：1.返回列中不重复的值所在的⾏ 2.关键字应⽤于所有列，⽽⾮前置他的列
SELECT pro_name FROM products LIMIT 3,4; 从products表中检索pro_name列的第三⾏到第七⾏, LIMIT x,y; x指开始位置，y指要检索的⾏数
SELECT products.pro_name FROM crashcourse.products; == SELECT pro_name FROM products;  前者称为完全限定名
8.SELECT pro_id, pro_price, pro_name FROM products ORDER BY prod_price, prod_name; 将检索的数据按照prod_price升序，如果prod_price相同，按照prod_name排序
SELECT pro_id, pro_price, pro_name FROM products ORDER BY prod_price DESC, prod_name; DESC关键字(descend)两个特点：1.实现降序 2.只应⽤于其前⾯的列名
SELECT pro_price FROM products ORDER BY prod_price DESC LIMIT 1; 出最贵的物品
9.SELECT pro_name FROM products WHERE pro_price = 2.5; 出买的产品价格为2.5的⼈, 这⾥⼀个等号即可判别，范围⽤between and表⽰，还有!=,=,>这些符号
SELECT pro_name FROM products WHERE pro_price IS NULL; 出价格为空字段的姓名，空字段不是价格为0
10.SELECT pro_name, pro_price FROM products WHERE (ven_id = 1002 or ven_id = 1003) and pro_prices >= 10; 价格为10以上且由2或3控制的产品。and优先级⼤于or
SELECT pro_name FROM products WHERE ven_id IN (1002, 1003) ORDER BY pro_name; 供应商2和3的所有产品，IN指定条件范围，合法值由 , 分割
SELECT pro_name FROM products WHERE ven_id NOT IN (1002, 1003) ORDER BY pro_name;  NOT⽀持对IN, BETWEEN, EXIST句⼦取反
c语言编程软件win711.SELECT prod_name FROM products WHERE prod_name LIKE '%anvil%'; 搜索包含⽂本anvil的值 %称作通配符
mysql语句顺序
SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; _也是通配符，与%功能⼀样，但下划线只匹配单个字符⽽不是多个字符jsp include指令
通配符的搜索很慢，如果其他操作符能达到相同⽬的，应该优先使⽤其他操作符
12. SELECT Concat(RTrim(vend_name), '(' , RTrim(vend_country), ')' ) AS ven_title FROM vendors ORDER BY
vend_name;Contact(,)拼接字段，RTrim去掉字段右边的空格 AS赋予别名
13. SELECT cust_id, roder_num FROM orders WHEREDate(order_date) BETWEEM '2005-09-01' AND '2005-09-30'; 检索2005年9⽉下的所有订单，⽇期处理函数Date函数
14. SELECT SUM(item_price*quantity) AS total_price FROM ordertimes WHERE order_num = 20005；聚集函数SUM
15. SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING
SUM(quantity*item_price)>=50 ORDER BY ordertotal;
SELECT⼦句及其顺序：SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
16.SELECT cust_name, cust_state,(SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name; 返回三列，有⼦查询
17.SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.ven_id = products.ven_id ORDER BY vend_name, prod_name; 关系型数据库，联结表，内部联结
18.SELECT ven_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION SELECT ven_id, prod_id, prod_price FROM products WHERE ven_id IN (1001,1002);
组合函数UNION组合两条SELECT语句js获取字符串倒数第二个字符
19.SELECT note_text FROM productnotes WHERE Match(note_text) Aganist('anvils' WITH QUERY EXPANSION); 全⽂搜索，查询扩展
20.INSERT INTO customers(cust_name,...)VALUES('pep.E'...) ; 插⼊数据
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd' WHERE cust_id = 10005; 更新数据
DELETE FROM customers WHERE cust_id = 10006; 删除数据
创建型设计模式有哪些21.CREATE TABLE customers 创建表  ALTER TABLE customers  更新表  DROP TABLE customers  删除表  RENAME TABLE customers 重命名表
22.CREAT VIEW customeremaillist AS SELECT cust_id, cust_name, cust_eamil FROM customers WHERE cust_email IS NOT NULL; 视图作为虚拟的表
23.CREATE PROCEDURE productpricing() 创建存储过程 CALL productpricing() 执⾏存储过程 DROP PROCEDURE productpricing() 删除存储过程
24.游标：在检索出来的⾏中前进或后退⼀⾏或多⾏
25.触发器是MySQL响应DELETE,INSERT,UPDATE语句⾃动执⾏的⼀条MySQL语句CREATE TRIGGER 创建触发器
26.事务处理： COMMIT 隐含提交 ROLLBACK 撤回 SAVEPOINT 保留点
27.访问控制： GRANT 授予权限 REVOKE 撤销权限 SET PASSWORD 更改⼝令

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。