-- 查询语句select class from stu_info where sid=1000000102;select * from stu_info t where t.age=88; -- t是表的别名,多表查询时比较方便select * from atable a, btable b where a.aID = b.bID;select * from stu_info t where t.age=99 or (t.age>20 and t.age <90);select * from stu_info t where t.age between 10 and 99;select * from stu_info t where t.hometown like ‘110%‘ ; -- %通配符,表示0到多个任意...
连接数据:mysql -h host -u root -p password 修改密码:mysqladmin -u root -p old_password password new_password 增加用户:create user ‘user1‘@‘%‘ identified by ‘password‘ 用户授权:grant select,update,insert,delete on database.table to ‘user1‘@‘%‘ 2.数据库操作 查所有库:show databases 切换数库:use database_name 新数据库:create database database_name 删数据库:drop database dat...
Explain语法:explain select … from … [where ...] 例如:explain select * from news; 输出:+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ 下面对各个属性进行了解...
CREATE DATABASE IF NOT EXISTS $database DEFAULT CHARSET utf8 COLLATE utf8_general_ci; CREATE DATABASE $database DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 创建用户并且添加权限 # 只是创建用户 CREATE USER phpergao@‘localhost‘ IDENTIFIED BY ‘yourpasswd‘;# 赋予权限 GRANT select,update on phpergao.* to phpergao@‘localhost‘;GRANT index ON phpergao.* TO phpergao@‘192.168.0.%‘;# 创建用户并...
零、用户管理:1、新建用户:>CREATE USER name IDENTIFIED BY ‘ssapdrow‘;2、更改密码:>SET PASSWORD FOR name=PASSWORD(‘fdddfd‘);3、权限管理>SHOW GRANTS FOR name; //查看name用户权限>GRANT SELECT ON db_name.* TO name; //给name用户db_name数据库的所有权限>REVOKE SELECT ON db_name.* TO name; //GRANT的反操作,去除权限; 一、数据库操作: 1、查看数据库:>SHOW DATABASES;2、创建数据库:>C...
:分号‘;‘ SqlServer :字符‘GO‘ 自增列:MySQL必须要索引,不能设置增量; 默认值:MySQL默认值不用加括号;SqlServer可加或不加; --MySQL CREATE TABLE tabName( ID INT PRIMARY KEY AUTO_INCREMENT, Value INT DEFAULT 12 ) AUTO_INCREMENT = 100 ENGINE=MyISAM DEFAULT CHARSET=utf8; --SqlServer CREATE TABLE tabName( ID INT IDENTITY(100,1) PRIMARY KEY, Value INT DEFAULT(12) ) 查看表结构定义: --Sql...
一、SQL优化的一般步骤 (1)、通过show status命令了解各种SQL的执行频率。 (2)、定位执行效率较低的SQL语句-(重点select) (3)、通过explain分析低效率的SQL语句的执行情况 (4)、确定问题并采取相应的优化措施 (1) 通过showstatus命令了解各种SQL的执行频率 MySQL客户端连接成功后,通过使用show[session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果,global来表示自数据库上次...
-- 演示将多条记录数据组合成一条sql插入语句(for mysql)function getTpl0(tname) -- 获取表各个字段local t = {tpl_pack = {"packId","itemId","`group`","num","rate","rateType"},}for k, v in pairs(t) doif tname == k thenreturn t[k]endend end--tpl = {3813,10,0,2,0,1,1,0,350,5,220,6,0,0,0,0,154,0,0,0,210,80,29} tpl9122 = { -- "packId","itemId","`group`","num","rate","rateType"{9122, 3294, ‘‘, 1, 1, 2},{9...
------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | ...
建表:(not null ,auto_increment, unique , primary key) create database balfish;use balfish;create table mytable1( id int not null, name varchar(20), grade int);insert into mytable1 values(‘1‘,‘yang‘,97);insert into mytable1 values(‘2‘,‘li‘,94);insert into mytable1 values(‘3‘,‘chen‘,88);insert into mytable1 values(‘4‘,‘zhang‘,57);insert into mytable1 values(‘5‘,‘dai‘...
1.连接 MYSQL 服务器的函数 mysql_connect();<?php $con = mysql_connect("localhost","root","123456");if (!$con){ die(‘Could not connect: ‘ . mysql_error());}?> 2.选择数据库 <?php mysql_select_db("david", $con);?> 3.执行SQL语句,create,insert into,delete<?php //$sql = "CREATE TABLE Persons (FirstName varchar(15),LastName varchar(15),Age int)";$sql="insert into Persons(FirstName,LastName,Age)VALUE...
如果数据表tbl的某字段name的字符编码是latin1_swedish_ci select * from `tbl` order by birary(name) asc ; 如果数据表tbl的某字段name的字符编码是utf8_general_ci SELECT name FROM `tbl` WHERE 1 ORDER BY CONVERT( name USING gbk ) COLLATE gbk_chinese_ci ASCmysql 中文字段排序( 按拼音首字母排序) 的查询语句标签:本文系统来源:http://www.cnblogs.com/weilantiankong/p/4685374.html
mysql基本语句 1.显示可用数据库 show databases; 2.创建一个library数据库 create databaselibrary; 3.使用数据库 use library; 4.查看表 show tables; 5.建表语句 mysql> uselibrary; Database changed mysql> createtable viedos( -> video_id int(11) not nullauto_increment, -> title varchar(255) not null, -> director varchar(255) not null, -> primary key(video_id)); Query OK, 0 rowsaffected (0....
方法1:适用于 SQL Server 2000/2005SELECT TOP 页大小 *FROM table1WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id )ORDER BY id方法2:适用于 SQL Server 2000/2005SELECT TOP 页大小 *FROM table1WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ...
如下图,添加项目目录文件:test_db,(如果文件不存在,会在该目录下新建一个文件夹) 设置app名称:testdb,在新的窗口打开项目,如图所示其中,MVC模式中,models.py用于处理M,views.py用于处理V,urls.py用于映射视图。 1.下载pymysql:pip install pymysql 2.在项目__init__.py里添加(不是): import pymysqlpymysql.install_as_MySQLdb()如图所示: models.py中定义数据库表的字段: from django.db import models class Pu...